JDBC的应用

介绍

JDBC是java操作关系数据库的一套API

全称：JAVA DataBase Connectivity java数据库连接

市面上的数据库多种多样，每个数据库的底层逻辑都不一样，不同数据库需要学习不同的接口来连接数据库，为了减少学习工作量统一使用jdbc的接口代码，由数据库的各个厂商使用jar包来适配不一样的底层实现逻辑。

这时候我们只用学习jdbc就可以实现各类数据库的增删查改了。不管是什么数据库系统，都能保证java代码的一致性。

过去：

  graph TB
java--->mysql
java--->server
java--->DB2
java--->...各种各样的数据库

现在：

  graph TB
java--调用-->jdbc
jdbc-->mysql驱动
jdbc-->server驱动
jdbc-->DB2驱动
jdbc-->其他
mysql驱动-->mysql
server驱动-->server
DB2驱动-->DB2
其他-->其他数据库

本篇使用 mysql8.0 jdk 1.8 IDEA 2023 学习实践

mysql驱动下载地址：MySQL :: MySQL Community Downloads

选择无关平台

选择zip下载就可以了

如果想下载历史版本的话

在下图下拉框可以选择旧版本

不同版本之间的驱动有对应的MySQL数据库版本以及jdk版本

系统依旧选择无关平台

下载好驱动以后，在你的项目中添加到库里面

例如我存放驱动的文件目录为lib，点击右键添加到库

简单实例

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class test01 {
    void demo() throws ClassNotFoundException, SQLException {
        //创建驱动
        Class.forName("com.mysql.jdbc.Driver");
        
        //获取连接地址     IP地址+mysql一般使用3306端口 /数据库名
        String url = "jdbc:mysql://127.0.0.1:3306/test";
        //用户密码
        String username =  "root";
        String  password =  "123456";
        Connection conn =  DriverManager.getConnection(url,username,password);

        //sql语句
        String sql = "update student_inf set password = '201819' where id = '6' ";

        
        //获取执行对象
        Statement statement = conn.createStatement();

        //执行
        int count = statement.executeUpdate(sql);//返回值是有几行数据被修改
        //可以根据  count 的数值来判断 是否数据库更改成功


        System.out.println("受影响的行数"+count);

        //释放资源
        statement.close();
        conn.close();

    }
}

注意：com.mysql.jdbc.Driver为驱动版本8.0以下使用的，8.0以上可能会出问题不能运行

亦有可能会出现提示但是仍然可以运行，如下提示你该使用新的驱动注册

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.

驱动版本为8.0以上使用com.mysql.cj.jdbc.Driver

版本5以后的驱动也可以不写注册驱动，jar包自动获取

原来的表格：

修改成功后的表格：

JDBC 的API 简单应用

1. DriverManager 类

1.1 获取连接 getConnection方法

getConnection一般有三个参数，url，username，password。后俩个不用多说，就是数据库的用户名和密码

调用方式：

String url = "jdbc:mysql://127.0.0.1:3306/test";
String username =  "root";
String  password = "123456";
Connection conn =  DriverManager.getConnection(url,username,password);

其中url的格式为:jdbc:mysql://url地址:端口/数据库名?各种参数配置&各种参数配置

jdbc:mysql
url:端口：数据库所在的IP地址+端口号
数据库名
各种参数配置，在数据库名后面以?开始，以&连接多个配置，例如时区和字符编码等等

例：设置编码为UTF-8

jdbc:mysql://127.0.0.1:3306/test??useUnicode=true&characterEncoding=UTF-8

当使用本地地址127.0.0.1以及默认端口3306是可以省略，为：jdbc:mysql:///test

1.2 注册驱动

Class.forName("com.mysql.cj.jdbc.Driver");的底层方法其实是

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!");
        }
    }
}

注意：com.mysql.jdbc.Driver为驱动版本8.0以下使用的，8.0以上可能会出问题不能运行

亦有可能会出现提示但是仍然可以运行，如下提示你该使用新的驱动注册

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.

驱动版本为8.0以上使用com.mysql.cj.jdbc.Driver

版本5以后的驱动也可以不写注册驱动，jar包自动获取

~~又重复了一遍…~~

2. Connection 类

1. 获取执行sql的对象

1.1 普通获取Statement对象

1 2	Connection conn = DriverManager.getConnection(url,username,password); Statement statement = conn.createStatement();

1.2 获取预编译sql对象PreparedStatement获取

1.3 获取执行存储过程对象

2. mysql 事务控制

	mysql	JDBC
开启事务	BEGIN 或者 START TRANSACTION	setAutoCommit(bool autoCommit) 默认为true，自动提交事务改为false时，手动提交事物，即开启事务
提交事务	COMMIT	commit()
回滚事务	ROLLBACK	rollback()

初始数据：

现在是默认自动事务(即事务没开启)：

String sql1 = "update student_inf set password = '222' where id = '6' ";
String sql2 = "update student_inf set password = '222' where id = '5' ";

//获取执行对象
Statement statement = conn.createStatement();

//执行
try {
    int count1 = statement.executeUpdate(sql1);//返回值是有几行数据被修改
    System.out.println("受影响的行数"+count1);

    int i = 1/0  ; //手动制造的异常
    
    int count2 = statement.executeUpdate(sql2);//返回值是有几行数据被修改
    System.out.println("受影响的行数"+count2);
} catch (SQLException e) {
    throw new RuntimeException(e);
}

结果如下：运行了sql1以后出现异常跳出来了，sql1执行的代码还在

接下来是开启事务的实践：

String sql1 = "update student_inf set password = '333' where id = '6' ";
String sql2 = "update student_inf set password = '333' where id = '5' ";
//执行
try { 
    //开启事务
    conn.setAutoCommit(false);
    int count1 = statement.executeUpdate(sql1);//返回值是有几行数据被修改
    System.out.println("受影响的行数"+count1);

    int i = 1/0  ; //手动制造的异常

    int count2 = statement.executeUpdate(sql2);//返回值是有几行数据被修改
    System.out.println("受影响的行数"+count2);
    
    //提交事务
    conn.commit();
} catch (SQLException e) {
    //回滚事务
    conn.rollback();
    throw new RuntimeException(e);
}

结果如下：事务出错，数据回滚

如果把// int i = 1/0 ;注释掉了，执行的结果如下：

Statement 类

1 执行sql语句的方法

1.0 执行所有sql语句

甚至包括存储过程

execute(sql)

返回值为Boolean值：

true
执行后返回了一个ResultSet对象
也就是说sql语句是 DQL 查询语句

1
2
3

if(stmt.execute(sql)){//如果为true
	ResultSet rs = stmt.getResultSet();//获取ResultSet
}

false
没有返回ResultSet执行的是DML或者DDL语句

1	int updateCount = stmt.getUpdateCount();//获取受影响的行数

1.1 执行DDL、DML ，即表的修改，数据修改

executeUpdate(sql)

执行DML（数据）语句时返回值受影响的行数
返回值大于0即为执行成功
实例上面很详细
执行DDL(数据库)时，返回值均为0
不报异常就是成功了

1.2 执行DQL，查询语句

executeQuery(sql)

返回一个ResultSet对象

ResultSet 类

一个结果集合

使用executeQuery(sql)查询语句后，返回的ResultSet对象是一个游标，指向查询语句的上一行，例如student_inf表查询语句

使用select * from student_inf后，游标指向：

使用next(),可以指向下一行数据，返回值是Boolean，为true则下一行有数据，否则为false

使用get数据类型()来接收对应数据，例如StringgetString()，intgetInt(),doublegetDouble()

get数据类型()的参数分为俩种，一个是int型，按照数据在表中的位置来定位，第二种为String型，根据列名来提取数据

String sql = "select * from student_inf";

//执行
try {

    ResultSet resultSet = statement.executeQuery(sql);
    while (resultSet.next()){
        int id = resultSet.getInt(1);
        String student_id = resultSet.getString(2);
        String name =  resultSet.getString(3);
        String pas = resultSet.getString(4);
        String sex = resultSet.getString(5);
        int age = resultSet.getInt(6);

        //int id = resultSet.getInt("id");
        //String student_id = resultSet.getString("student_id");
        //String name =  resultSet.getString("name");
        //String pas = resultSet.getString("password");
        //String sex = resultSet.getString("sex");
        
        
        System.out.println(id+student_id+name+pas+sex+age);

    }

} catch (SQLException e) {

    throw new RuntimeException(e);
}

其他方法：

int getRow()获取当前行数 ——-先last(),再getRow()可以获得总共有多少行数据
boolean next(): 移动到下一行。
boolean previous(): 移动到前一行。
boolean first(): 移动到第一行。 //在可滚动的ResultSet启用,在获取PreparedStatement 或者Statement 对象时的参数加上ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE
boolean last(): 移动到最后一行。
void moveToFirst(): 移动到第一行。//不可滚动的数据启用
void moveToLast(): 移动到最后一行。
boolean absolute(int row): 移动到指定行。
boolean relative(int rows): 相对于当前行移动指定行数。
boolean isBeforeFirst(): 判断是否在第一行之前。
boolean isAfterLast(): 判断是否在最后一行之后。
boolean isFirst(): 判断是否在第一行。
boolean isLast(): 判断是否在最后一行。
void beforeFirst(): 移动到第一行之前。
void afterLast(): 移动到最后一行之后。
获取列名：

PreparedStatement pstmt = conn.prepareStatement(sql);

// 执行查询
ResultSet rs = pstmt.executeQuery();

// 获取 ResultSetMetaData 对象
ResultSetMetaData rsmd = rs.getMetaData();

// 获取列数
int columnCount = rsmd.getColumnCount();
System.out.println("列数：" + columnCount);

// 获取列名
for (int i = 1; i <= columnCount; i++) {
    String columnName = rsmd.getColumnName(i);
    String columnLabel = rsmd.getColumnLabel(i); // 列别名
    String columnType = rsmd.getColumnTypeName(i); // 列数据类型

    System.out.println("列名：" + columnName);
    System.out.println("列别名：" + columnLabel);
    System.out.println("列数据类型：" + columnType);
}

void insertRow(): 插入当前行。
void updateRow(): 更新当前行。
void deleteRow(): 删除当前行。
void refreshRow()：刷新当前行在resultSet的缓存
void update 数据类型更新数据
void updateNull(int columnIndex): 更新指定列为 NULL 值。

void updateNull(String columnLabel): 更新指定列名为 NULL 值。

例子：

//修改数据为可编辑
Statement statement = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);

String sql = "select * from student_inf";

try {
    ResultSet resultSet = statement.executeQuery(sql);
    while (resultSet.next()){
        //设置修改的列，修改后的数据
        resultSet.updateString("password","999");
        //提交更新请求
        resultSet.updateRow();
        int id = resultSet.getInt("id");
        String student_id = resultSet.getString("student_id");
        String name =  resultSet.getString("name");
        String pas = resultSet.getString("password");
        String sex = resultSet.getString("sex");
        int age = resultSet.getInt("age");
        System.out.println(id+student_id+name+pas+sex+age);
    }
} catch (SQLException e) {
    throw new RuntimeException(e);
}

结果：

PreparedStatement 类

一个

为什么要使用这个类呢

防止sql注入
性能高，使用预编译
预编译默认需要关闭，需要开启
1. useServerPrepStmts=true
2. 配置mysql执行日志(自行百度)，需要重启mysql
  一般执行sql需要：

检查sql语法
编译
执行
当PreparedStatement 执行sql时，如果是执行同一个sql语句，仅仅是参数不同
那么第一次执行sql时的步骤一模一样，第二次执行sql语句sql代码已经编译就只用执行就完成了

sql注入

使用Statement对象的时候，执行的sql代码是以String的形式发送给mysql的。如果在一些特定条件查询，比如账户密码查询的时候输入语句加入' or '1'='1,到了sql就变成

select * from student_inf where 'name' = '张三' OR '1'='1'
//因为1恒等于1
120190101张三999男19
220190102李四999女18
320200101小强999男20
420200101小强999男20
520200101小强999男20
620200101小强999男20
720200101小强999男20

就不费吹灰之力获取你的数据

基本使用

以？为占位符

查询操作：

        String sql = "select * from student_inf where password = ? and name= ? ";
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        preparedStatement.setString(1,"999");
        preparedStatement.setString(2,"张三");

        //执行
        try {

            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){

                int id = resultSet.getInt("id");
                String student_id = resultSet.getString("student_id");
                String name =  resultSet.getString("name");
                String pas = resultSet.getString("password");
                String sex = resultSet.getString("sex");
                int age = resultSet.getInt("age");
                System.out.println(id+student_id+name+pas+sex+age);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        //释放资源
        preparedStatement.close();
        conn.close();

//数据输出如下
120190101张三999男19

增、改、删：

//sql语句
String sql = "insert into student_inf values (?,?,?,?,?,?)";
PreparedStatement preparedStatement = conn.prepareStatement(sql);
preparedStatement.setInt(1,8);
preparedStatement.setString(2,"20240521");
preparedStatement.setString(3,"桐崎千棘");
preparedStatement.setString(4,"159");
preparedStatement.setString(5,"女");
preparedStatement.setInt(6,7);

//执行 返回受影响行数
int i = preparedStatement.executeUpdate();
System.out.println(i);


//释放资源
preparedStatement.close();
conn.close();

结果：

删改类似，不多赘述

封装数据进对象以及集合

新建一个Student_inf 类，设置好数据字段，Alt+Inesert快速重写toString()以及get和set

public class Student_inf {
    int id;
    String student_inf;
    String name;
    String password;
    String sex;
    int age;
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getStudent_inf() {
        return student_inf;
    }
    public void setStudent_inf(String student_inf) {
        this.student_inf = student_inf;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getPassword() {
        return password;
    }
    public void setPassword(String password) {
        this.password = password;
    }
    public String getSex() {
        return sex;
    }
    public void setSex(String sex) {
        this.sex = sex;
    }
    public int getAge() {
        return age;
    }
    public void setAge(int age) {
        this.age = age;
    }
    @Override
    public String toString() {
        return "Student_inf{" +
                "id=" + id +
                ", student_inf='" + student_inf + '\'' +
                ", name='" + name + '\'' +
                ", password='" + password + '\'' +
                ", sex='" + sex + '\'' +
                ", age=" + age +
                '}';
    }
}

数据查询：

String sql = "select * from student_inf ";

//创建列表
List<Student_inf> lists = new ArrayList<>();

try {
   
    ResultSet resultSet = statement.executeQuery(sql);
    while (resultSet.next()){
        Student_inf studentInf = new Student_inf();
        studentInf.id= resultSet.getInt("id");
        studentInf.student_inf= resultSet.getString("student_id");
        studentInf.name=  resultSet.getString("name");
        studentInf.password = resultSet.getString("password");
        studentInf.sex= resultSet.getString("sex");

        studentInf.age= resultSet.getInt("age");
        System.out.println(studentInf.toString());
        lists.add(studentInf);
    }

} catch (SQLException e) {

    throw new RuntimeException(e);
}
System.out.println(lists);

//释放资源
statement.close();
conn.close();

结果：

Student_inf{id=1, student_inf='20190101', name='张三', password='999', sex='男', age=19}
Student_inf{id=2, student_inf='20190102', name='李四', password='999', sex='女', age=18}
Student_inf{id=3, student_inf='20200101', name='小强', password='999', sex='男', age=20}
Student_inf{id=4, student_inf='20200101', name='小强', password='999', sex='男', age=20}
Student_inf{id=5, student_inf='20200101', name='小强', password='999', sex='男', age=20}
Student_inf{id=6, student_inf='20200101', name='小强', password='999', sex='男', age=20}
Student_inf{id=7, student_inf='20200101', name='小强', password='999', sex='男', age=20}
[Student_inf{id=1, student_inf='20190101', name='张三', password='999', sex='男', age=19}, Student_inf{id=2, student_inf='20190102', name='李四', password='999', sex='女', age=18}, Student_inf{id=3, student_inf='20200101', name='小强', password='999', sex='男', age=20}, Student_inf{id=4, student_inf='20200101', name='小强', password='999', sex='男', age=20}, Student_inf{id=5, student_inf='20200101', name='小强', password='999', sex='男', age=20}, Student_inf{id=6, student_inf='20200101', name='小强', password='999', sex='男', age=20}, Student_inf{id=7, student_inf='20200101', name='小强', password='999', sex='男', age=20}]

数据库连接池

使用以上方法，每次与数据库交互都要创建一个新连接，交互完就销毁释放资源，造成资源浪费

数据池解决这个问题，在缓冲区创建好多个连接对象，使用时由连接池分配数据库连接对象，不使用时就放回连接池。类似客服，有需求时打电话过去，没需求就挂掉电话，给下一个有需求的人接电话。避免了频繁销毁创建

目前druid和hikari是用的最多的俩个，前者是综合性，后者速度快

使用需要引入jar包

步骤：

创建连接池对象
配置连接池
由连接池对象创建连接对象
代码实现后回收连接对象

配置分为俩种：代码配置以及配置文件配置

主键回显

创建PreparedStatement对象时加入参数PreparedStatement.RETURN_GENERATED_KEYS
pstmt.getGeneratedKeys();

Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;

try {
    // 1. 获取数据库连接
    conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD);
    
    // 2. 创建SQL插入语句
    String sql = "INSERT INTO student_inf (name, age) VALUES (?, ?)";
    
    // 3. 创建PreparedStatement对象，并指定返回生成的键
    pstmt = conn.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
    
    // 4. 设置参数
    pstmt.setString(1, "John Doe");
    pstmt.setInt(2, 22);
    
    // 5. 执行插入操作
    int affectedRows = pstmt.executeUpdate();
    
    // 6. 检查是否插入成功
    if (affectedRows > 0) {
        // 7. 获取生成的键
        rs = pstmt.getGeneratedKeys();
        if (rs.next()) {
            long generatedId = rs.getLong(1);
            System.out.println("Inserted record's ID: " + generatedId);
        }
    }
} catch (SQLException e) {
    e.printStackTrace();
} finally {
    // 8. 关闭资源
    try {
        if (rs != null) rs.close();
        if (pstmt != null) pstmt.close();
        if (conn != null) conn.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

ThreadLocal

同一用户有多个需求打电话给客服，只需要一个客服接听电话就可以了，不需要多个客服来解决需求。

这就是ThreadLocal的作用。

简单例子看30高级篇-工具类封装V2.0_哔哩哔哩_bilibili

以下是gpt的示例

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class ThreadLocalConnectionManager {

    private static final String JDBC_URL = "jdbc:mysql://localhost:3306/mydatabase";
    private static final String JDBC_USER = "username";
    private static final String JDBC_PASSWORD = "password";

    private static final ThreadLocal<Connection> connectionThreadLocal = ThreadLocal.withInitial(() -> {
        try {
            return DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD);
        } catch (SQLException e) {
            throw new RuntimeException("Failed to create a connection", e);
        }
    });

    public static Connection getConnection() {
        return connectionThreadLocal.get();
    }

    public static void closeConnection() {
        try {
            Connection connection = connectionThreadLocal.get();
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            connectionThreadLocal.remove();
        }
    }

    public static void main(String[] args) {
        Runnable task = () -> {
            Connection conn = getConnection();
            // Perform database operations using conn
            System.out.println(Thread.currentThread().getName() + ": Got connection " + conn);
            closeConnection();
        };

        Thread thread1 = new Thread(task, "Thread 1");
        Thread thread2 = new Thread(task, "Thread 2");

        thread1.start();
        thread2.start();
    }
}

批量操作

执行相似且数量大SQL的时候，例如插入数据，使用上述方法会很慢，这个时候批量操作就派上用场了

驱动 url设置rewriteBatchedStatements=true

步骤：

把每行数据添加进bacth addbatch()
提交到数据库 executeBatch()

private static final String JDBC_URL = "jdbc:mysql://localhost:3306/mydatabase";
private static final String JDBC_USER = "username";
private static final String JDBC_PASSWORD = "password";

public static void main(String[] args) {
    Connection conn = null;
    PreparedStatement pstmt = null;

    try {
        // 1. 获取数据库连接
        conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD);
        
        // 2. 设置自动提交为 false
        conn.setAutoCommit(false);
        
        // 3. 准备 SQL 语句
        String sql = "INSERT INTO student_inf (name, age) VALUES (?, ?)";
        pstmt = conn.prepareStatement(sql);
        
        // 4. 设置批量操作的参数
        for (int i = 1; i <= 1000; i++) {
            pstmt.setString(1, "Student " + i);
            pstmt.setInt(2, 20 + (i % 10));
            
            pstmt.addBatch();  // 添加到批处理
            
            if (i % 100 == 0) {
                // 5. 执行批处理，每100条记录提交一次
                int[] result = pstmt.executeBatch();
                conn.commit();
                System.out.println("Inserted batch of 100 records: " + result.length);
            }
        }
        
        // 6. 执行剩余的批处理操作
        int[] result = pstmt.executeBatch();
        conn.commit();
        System.out.println("Inserted remaining records: " + result.length);

    } catch (SQLException e) {
        // 发生异常时回滚事务
        if (conn != null) {
            try {
                conn.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
        e.printStackTrace();
    } finally {
        // 7. 关闭资源
        try {
            if (pstmt != null) pstmt.close();
            if (conn != null) conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

相当于把多条insert()

变为insert values(),(),()

删改类似，不多赘述

DAO

数据访问对象（DAO，Data Access Object）模式是用于抽象和封装对数据源的访问逻辑的一种设计模式。通过使用 DAO 模式，可以将数据访问逻辑从业务逻辑中分离出来，使代码更具可维护性和可测试性。自此，逻辑层不用再管sql部分的代码，只需要调用函数，就可以返回结果。

dao一般由以下组成：

工具/数据类
数据库操作接口
接口的具体实现逻辑