JDBC
概念
JDBC(Java DataBase Connectivity java数据库连接)是一种用于执行SQL语句的Java API,可以为多种关系型数据库提供统一访问,它是由一组用Java语言编写的类和接口组成的。
本质
java官方提供的一套规范(接口)。用于帮助开发人员快速实现不同关系型数据库的连接!
快速入门
入门案例
JDBC入门Demo
数据库准备
-- 创建测试数据库
CREATE DATABASE IF NOT EXISTS jdbc;
-- 使用数据库
USE jdbc;
-- 创建测试表
CREATE TABLE IF NOT EXISTS user(
id INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
`name` VARCHAR(20) NOT NULL COMMENT '姓名'
);
-- 插入测试数据
INSERT INTO user VALUES(NULL,'zhanngsan'),(NULL,'lisi');
-- 查询测试数据
SELECT
*
FROM
user;
pom依赖
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>JDBCDemo</groupId>
<artifactId>JDBCDemo</artifactId>
<version>1.0-SNAPSHOT</version>
<dependencies>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.27</version>
</dependency>
</dependencies>
</project>
java代码
package com.test;
import java.sql.*;
/**
* @author zhangzengxiu
* @date 2022/8/27
*/
public class JDBCDemo01 {
public static void main(String[] args) throws Exception {
//1.注册驱动
Class.forName("com.mysql.jdbc.Driver");
//2.获取连接
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc", "root", "root");
//3.获取执行者对象
Statement statement = connection.createStatement();
//4.执行SQL语句,返回结果
String sql = "SELECT * FROM user";
ResultSet resultSet = statement.executeQuery(sql);
//5.结果处理
while (resultSet.next()) {
System.out.println(resultSet.getInt("id") + " " + resultSet.getString("name"));
}
//6.资源释放
resultSet.close();
statement.close();
connection.close();
}
}
功能类详解
DriverManager
① 注册驱动
注册给定的驱动程序:static void registerDriver(Driver driver);
写代码使用:Class.forName(“com.mysql.jdbc.Driver”);
在com.mysql.jdbc.Driver类中存在静态代码块
Driver源代码:
//
// Source code recreated from a .class file by IntelliJ IDEA
// (powered by Fernflower decompiler)
//
package com.mysql.jdbc;
import java.sql.SQLException;
public class Driver extends com.mysql.cj.jdbc.Driver {
public Driver() throws SQLException {
}
static {
System.err.println("Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.");
}
}
//
// Source code recreated from a .class file by IntelliJ IDEA
// (powered by Fernflower decompiler)
//
package com.mysql.jdbc;
import java.sql.DriverManager;
import java.sql.SQLException;
public class Driver extends NonRegisteringDriver implements java.sql.Driver {
public Driver() throws SQLException {
}
static {
try {
DriverManager.registerDriver(new Driver());
} catch (SQLException var1) {
throw new RuntimeException("Can't register driver!");
}
}
}
因为静态代码块已经实现注册驱动,所以不需要单独通过registerDriver来注册驱动。否则会导致重复注册驱动。
可以通过 Class.forName(“com.mysql.jdbc.Driver”); 来进行驱动注册,从而初始化静态代码块。
注意:
- 我们不需要通过DriverManager调用静态方法registerDriver(),因为只要Driver类被使用,则会执行其静态代码块完成注册驱动
- mysql5之后可以省略注册驱动的步骤。在jar包中,存在一个java.sql.Driver配置文件,文件中指定了com.mysql.jdbc.Driver
java.sql.Driver配置文件
com.mysql.cj.jdbc.Driver
② 获取数据库连接
获取数据库连接对象:static Connection getConnection(String url, String user, String password);
返回值:Connection 数据库连接对象
参数
url:指定连接的路径。语法:jdbc:mysql://ip地址(域名):端口号/数据库名称
user:用户名
password:密码
Connection
与特定的数据的连接(会话)。执行SQL语句并在连接的上下文中返回结果。
① 获取执行者对象
获取普通执行者对象:Statement createStatement();
获取预编译执行者对象:PreparedStatement prepareStatement(String sql);
② 管理事务
开启事务:setAutoCommit(boolean autoCommit); 参数为false,则开启事务。
提交事务:commit();
回滚事务:rollback();
③ 释放资源
立即将数据库连接对象释放:void close();
Statement
① 执行DML语句:int executeUpdate(String sql);
返回值int:返回影响的行数。
参数sql:可以执行insert、update、delete语句。
② 执行DQL语句:ResultSet executeQuery(String sql);
返回值ResultSet:封装查询的结果。
参数sql:可以执行select语句。
③ 释放资源
立即将执行者对象释放:void close();
ResultSet
① 判断结果集中是否还有数据:boolean next();
有数据返回true,并将索引向下移动一行。
没有数据返回false。
② 获取结果集中的数据:XXX getXxx(“列名”);
XXX代表数据类型(要获取某列数据,这一列的数据类型)。
例如:String getString(“name”); int getInt(“age”);
③ 释放资源
立即将结果集对象释放:void close();
JDBC案例CRUD
环境
数据库环境准备
-- 插入测试数据
INSERT INTO student VALUES
(NULL,'张三',23,'1999-09-23'),
(NULL,'李四',24,'1998-08-1'),
(NULL,'王五',25,'1996-06-06'),
(NULL,'赵六',26,'1994-10-20');
-- 查询
SELECT
*
FROM
student;
java代码
pom依赖
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>JDBCDemo</groupId>
<artifactId>JDBCDemo</artifactId>
<version>1.0-SNAPSHOT</version>
<dependencies>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.27</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.22</version>
</dependency>
</dependencies>
</project>
java代码
package com.test01.demo;
import com.test01.pojos.Student;
import org.junit.Test;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
/**
* @author zhangzengxiu
* @date 2022/8/27
*/
public class JDBCTest {
/**
* 查询全部
*/
@Test
public void testFindAll() {
ResultSet resultSet = null;
Statement statement = null;
Connection connection = null;
try {
//注册驱动
Class.forName("com.mysql.jdbc.Driver");
//获取连接
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc", "root", "root");
//获取执行者对象
statement = connection.createStatement();
//执行SQL,返回结果
String sql = "SELECT * FROM student";
resultSet = statement.executeQuery(sql);
ArrayList<Student> list = new ArrayList<Student>();
//结果处理
while (resultSet.next()) {
Integer sid = resultSet.getInt("sid");
String name = resultSet.getString("name");
Integer age = resultSet.getInt("age");
Date birthday = resultSet.getDate("birthday");
Student student = new Student(sid, name, age, birthday);
list.add(student);
}
for (Student student : list) {
System.out.println(student);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
//资源释放
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
/**
* 根据sid查询学生
*/
@Test
public void testFindStuBySid() {
//模拟传入sid
Integer stuId = 1;
ResultSet resultSet = null;
Statement statement = null;
Connection connection = null;
Student student = null;
try {
//注册驱动
Class.forName("com.mysql.jdbc.Driver");
//获取连接
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc", "root", "root");
//获取执行者对象
statement = connection.createStatement();
//执行SQL,返回结果
String sql = "SELECT * FROM student WHERE sid ='" + stuId + "'";
resultSet = statement.executeQuery(sql);
//结果处理
while (resultSet.next()) {
Integer sid = resultSet.getInt("sid");
String name = resultSet.getString("name");
Integer age = resultSet.getInt("age");
Date birthday = resultSet.getDate("birthday");
student = new Student(sid, name, age, birthday);
}
System.out.println(student);
} catch (Exception e) {
e.printStackTrace();
} finally {
//资源释放
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
/**
* 新增学生
*/
@Test
public void testInsertStu() {
//模拟传入student
Student student = new Student();
student.setName("马七");
student.setAge(27);
student.setBirthday(new Date());
Statement statement = null;
Connection connection = null;
try {