JDBC

第一章 JDBC概述

我们学习了数据库管理软件MySQL，可以方便的管理数据。

那么如何将它俩结合起来呢？即Java程序<==>MySQL，实现数据的存储和处理。

答案：使用JDBC技术，后期可以使用MyBatis等持久层框架（底层仍然使用了JDBC）。

1. JDBC概述

JDBC：Java Database Connectivity，它是代表一组独立于任何数据库管理系统（DBMS）的API，声明在java.sql与javax.sql包中，是SUN(现在Oracle)提供的一组接口规范。由各个数据库厂商来提供实现类，这些实现类的集合构成了数据库驱动jar。

即JDBC技术包含两个部分：

（1）java.sql包和javax.sql包中的API

（2）各个数据库厂商提供的jar

1、之前学习了Java，又学习了MySQL数据库，JDBC 把 Java程序 和 MySQL数据库连起来，Java程序是负责数据的输入，业务的处理，数据的显示，MySQL负责数据的存储和管理。

2、JDBC：Java Database Connectivity

JDBC = JDK核心类库中的一套API（接口+部分工具类） + 数据库厂商提供的驱动jar

Java程序不仅仅能够连接MySQL数据库，可以连接很多数据库（Oracle，SQL Server，DB2，…）。

这就有一个问题：

​ 数据库不同，它们的操作方式会有所不同，因为它们的底层实现方式，实现的语言等都是不同的。那么Java去连接不同的数据库时，就会有不同的API。这样的话，就会导致：

​ （1）程序员的学习成本增加

​ （2）如果发生数据库迁移，Java代码就需要“重写”

​ 如果是这样的话，就非常麻烦，可移植性、可维护性等非常差。SUN公司（现在Oracle）就说，必须统一一套API，可以操作各种数据库，但是SUN公司又不同知道所有数据库内部是如何实现的，也无法要求所有的数据库厂商按照统一的标准来开发他们的数据库软件。SUN公司（现在Oracle）就设计了一套接口 + 部分类。然后各个数据库厂商，来提供这些接口的实现类。

​ ==>Java程序中面向接口编程，在程序运行时，又需要引入这些接口的实现类，这些实现类就是数据库驱动jar。

2. Java程序连接MySQL数据库

1 .引入mysql驱动jar

方式一：单独某个模块使用mysql驱动

（1）在模块路径下建一个文件夹“jdbclibs”，把mysql的驱动jar放到里面
MySQL5.7：mysql-connector-java-5.1.36-bin.jar
MySQL8.0：mysql-connector-java-8.0.19.jar
（2）在jdbclibs文件夹上右键-->Add as Library...
（3）填写库名称、选择这个库应用范围（模块）-->选择使用的具体模块

方式二：项目下多个模块使用mysql驱动

（1）在项目路径下建一个文件夹“jdbclibrary”，把mysql的驱动jar放到里面
mysql-connector-java-5.1.36-bin.jar
MySQL8.0：mysql-connector-java-8.0.19.jar
（2）项目设置-->libraries--> + ->java-->文件夹“jdbclibs”
（3）选择需要这个jar的模块
项目设置-->modules-->模块名-->dependencies--> + - >library->Java -> 库

后期其他模块也要使用mysql驱动，可以直接添加项目的jdbclibrary库即可：

方式三：使用Maven仓库

2. Java代码连接MySQL数据库的步骤

1、模块添加了依赖的mysql驱动相关库

2、在内存中加载驱动类（可选）
最近版本：com.mysql.jdbc.Driver
MySQL8.0版本：com.mysql.cj.jdbc.Driver

新版的mysql驱动jar可以省略这步，旧版的mysql驱动jar必须加这一步。
后期使用数据库连接池，或者MyBatis等框架时，在配置文件中加这个驱动类的配置即可
Class.forName(“com.mysql.cj.jdbc.Driver”);

3、连接数据库：通过DriverManager工具类获取数据库连接Connection的对象。
此时的Java程序是MySQL的一个客户端
连接数据库：
MySQL服务器主机的IP地址：
端口号
用户名
密码

//Connection   ==> 网络编程的Socket
String url = "jdbc:mysql://localhost:3306/test?serverTimezone=UTC";
Connection conn = DriverManager.getConnection(url, "root", "123456");

MySQL8使用时，url需要加参数：serverTimezone=UTC，否则会报错

4、断开连接：使用close方法。

数据库连接完整代码

import java.sql.Connection;
import java.sql.DriverManager;
/**
 * @Author yupengtao
 * @Date 2023/6/25 23:51
 **/
public class TestConn {
    public static void main(String[] args) throws Exception {

        //        mysql连接测试

        //        1、模块添加了依赖的mysql驱动相关库

        //        2、通过反射，在内存中加载驱动类（可选）
        //新版的mysql驱动jar可以省略这步，旧版的mysql驱动jar必须加这一步。
        Class.forName("com.mysql.cj.jdbc.Driver");

        //        3、连接数据库
        String url = "jdbc:mysql://localhost:3306/jdbc?serverTimezone=UTC";
        String user = "root";
        String passwd = "root";
        Connection conn = DriverManager.getConnection(url, user, passwd);
        System.out.println("mysql连接成功：conn = " + conn);

        //        4、操作数据库

        //5、释放资源
        conn.close();
    }
}

3 .实现增删改查

预先创建了一个表结构

mysql> desc jdbctest;
+——–+———–+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+——–+———–+——+—–+———+——-+
| id | int | YES | | NULL | |
| name | char(255) | YES | | NULL | |
| passwd | char(255) | YES | | NULL | |
+——–+———–+——+—–+———+——-+

步骤：
1、模块添加依赖的mysql驱动相关库
2、在内存中加载驱动类（可选）
3、连接数据库
4、操作数据库

通过Statement或PreparedStatement对象执行SQL：

Statement用于执行静态SQL语句，而PreparedStatement用于执行预编译的SQL语句，通常对于执行多次相似的语句更有效。

（1）通过Connection对象获取Statement或PreparedStatement对象

（2）通过Statement或PreparedStatement对象执行sql

​ 执行增、删、改，使用 int executeUpate()方法，接收执行条数。

​ 执行查询，使用ResultSet executeQuery()方法，接收返回结果集。

5、释放资源（close）

1 .添加数据完整代码

package com.penghu.jdbc.part1;

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

/**
 * @Author yupengtao
 * @Date 2023/6/26 00:56
 **/
public class TestC {
    public static void main(String[] args) throws Exception {

        Class.forName("com.mysql.cj.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/jdbc?serverTimezone=UTC";
        String user = "root";
        String passwd = "root";
        Connection conn = DriverManager.getConnection(url, user, passwd);

//        4、操作数据库
//        通过Statement或PreparedStatement对象执行SQL

//        Statement对象执行SQL
        Statement statement = conn.createStatement();
        int row1 = statement.executeUpdate("insert into jdbctest values (1,'张三','abc123')");

//        PreparedStatement对象执行SQL
        String sql = "insert into jdbctest values (2,'李四','abc123')";//发给服务器的sql语句
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        int row2 = preparedStatement.executeUpdate();

        //返回sql影响的记录数
        System.out.println(row1 > 0 ? "Statement对象执行SQL添加成功" : "添加失败");
        System.out.println(row2 > 0 ? "PreparedStatement对象执行SQL添加成功" : "添加失败");

        //释放执行sql的资源
        statement.close();
        preparedStatement.close();

        //5、释放sql连接资源
        conn.close();
    }
}

2 .修改数据

String sql = "update jdbctest set name ='张三改名' where name='张三'";//发给服务器的sql
PreparedStatement pst = conn.prepareStatement(sql);

int len = pst.executeUpdate();

3. 删除数据

String sql = "delete from jdbctest where name='张三改名' || name= '李四'";//发给服务器的sql
PreparedStatement pst = conn.prepareStatement(sql);

int len = pst.executeUpdate();

4. 特殊的，查询数据

对于查询结果：
遍历结果集的方法：
boolean next()：判断是否还有下一行
getString(字段名或序号),getInt(字段名或序号),getObject(字段名或序号)

package com.penghu.jdbc.part1;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

/**
 * @Author yupengtao
 * @Date 2023/6/26 00:56
 **/
public class TestR {
    public static void main(String[] args) throws Exception {

        Class.forName("com.mysql.cj.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/jdbc?serverTimezone=UTC";
        String user = "root";
        String passwd = "root";
        Connection conn = DriverManager.getConnection(url, user, passwd);

//        4、操作数据库
        String sql = "select * from jdbctest where name='张三'";//发给服务器的sql
        PreparedStatement pst = conn.prepareStatement(sql);

        ResultSet resultSet = pst.executeQuery();
        while (resultSet.next()){
            String uname = resultSet.getString("name");
            String upasswd = resultSet.getString("passwd");
            System.out.println(uname+"的密码是"+upasswd);
        }

        //5、释放资源
        resultSet.close();
        pst.close();
        conn.close();
    }
}

第二章 轻松处理各种问题

1. sql拼接问题

1.准备代码和sql

2.问题演示代码

package com.penghu.jdbc.part2;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

/**
 * @Author yupengtao
 * @Date 2023/6/25 17:44
 **/
public class TestSQLConcat {
    public static void main(String[] args) throws Exception {

        Class.forName("com.mysql.cj.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/jdbc?serverTimezone=UTC";
        String user = "root";
        String passwd = "root";
        Connection conn = DriverManager.getConnection(url, user, passwd);

        //4、操作数据库
        String sql = "insert into jdbctest(id,name,passwd) values (?,?,?)";
        PreparedStatement pst = conn.prepareStatement(sql);

        //占位符1：
        //要给每一个？指定具体的值
        //PreparedStatement支持给每一个字段指定值时，确定数据类型，例如：
        pst.setInt(1, 3);//这里的1，表示第1个？
        pst.setString(2, "王五");//这里的2，表示第2个？
        pst.setString(3, "王五的密码");


        //占位符2：
        //但是这样有点麻烦，还要一一去确定数据类型
        //PreparedStatement支持用Object统一处理
        String sql2 = "insert into jdbctest(id,name,passwd) values (?,?,?)";
        PreparedStatement pst2 = conn.prepareStatement(sql);
        pst2.setObject(1, 4);
        pst2.setObject(2, "赵六");
        pst2.setObject(3, "赵六的密码");

        //文件类型用new FileInputStream(path)
        //pst.setObject(3, new FileInputStream(path)); //用字节IO流来表示二进制数据

        //每一个？与你要赋值的字段对应，不能对错了
        pst.executeUpdate();
        pst2.executeUpdate();

		//取法2：
        Object getObject(字段名)
        Object getObject(字段的序号)

        //5、释放资源
        pst.close();
        pst2.close();
        conn.close();
    }
}

2. 避免sql注入问题

1.准备代码和sql

2.问题演示代码

package com.penghu.jdbc.part2;

import org.junit.Test;

import java.sql.*;
import java.util.Scanner;

/**
 * @Author yupengtao
 * @Date 2024/1/15 01:30
 **/
public class TestSQLInject {
    public static void main(String[] args) throws Exception {
        Scanner input = new Scanner(System.in);
        System.out.print("请输入你要查询的用户编号：");
        String id = input.nextLine();
        // 正常输入：1
        // 恶意输入：10 or 1=1  第一个1表示用户编号， 后面 or 1= 1表示条件，而1=1是永远成立，其他条件全部失效

        String url = "jdbc:mysql://localhost:3306/jdbc?serverTimezone=UTC";
        String user = "root";
        String passwd = "root";
        Connection conn = DriverManager.getConnection(url, user, passwd);

        String sql = "select * from jdbctest where id = " + id;
        System.out.println("sql = " + sql);
        PreparedStatement pst = conn.prepareStatement(sql);

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

        while (rs.next()) {
            for (int i = 1; i < 3; i++) {
                Object object = rs.getObject(i);
                if (object instanceof Integer) {
                    System.out.print((Integer) object + " ");
                } else {
                    System.out.print(object + " ");
                }
            }
        }

        rs.close();
        pst.close();
        conn.close();
        input.close();
    }
}

3 .二进制类型数据赋值

1.准备代码和sql

2.问题演示代码

/*
CREATE TABLE `t_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL,
  `password` varchar(50) NOT NULL,
  `photo` blob,
  PRIMARY KEY (`id`)
);

mysql> desc t_user;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int         | NO   | PRI | NULL    | auto_increment |
| username | varchar(20) | NO   |     | NULL    |                |
| password | varchar(50) | NO   |     | NULL    |                |
| photo    | blob        | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
 */
........

        String sql = "insert into t_user values(null,?,?,?)";
        PreparedStatement pst = connection.prepareStatement(sql);

        //设置？的值
        pst.setObject(1, username);
        pst.setObject(2, password);
//        pst.setObject(3, path);//不对，因为path是一个路径
        pst.setObject(3, new FileInputStream(path)); //用字节IO流来表示二进制数据

        //执行sql
        int len = pst.executeUpdate();
        System.out.println(len >0 ? "添加成功" : "添加失败");

        pst.close();
        connection.close();

        input.close();
/*
当存储的图片特别大时：
（1）com.mysql.cj.jdbc.exceptions.PacketTooBigException: Packet for query is too large (6,638,795 > 4,194,304).
You can change this value on the server by setting the 'max_allowed_packet' variable.
解决方案：修改my.ini配置文件      max_allowed_packet变量的值
先停止服务，然后修改my.ini文件，再重启服务

（2）com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Data too long for column 'photo' at row 1
问题是：blob类型的数据，存不下这么大的图片
解决方案：修改字段的数据类型

4 .获取自增长键值

1.准备代码和sql

2.问题演示代码

String sql = "insert into jdbctest(id,name,passwd) values (?,?,?)";

//执行sql后，返回自增长键值
PreparedStatement pst = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
pst.setObject(1, 0);
pst.setObject(2, "冯七");
pst.setObject(3, "冯七的密码");

pst.executeUpdate();

//获取自增长键值
ResultSet generatedKeys = pst.getGeneratedKeys();
if (generatedKeys.next()) {
    
    //因为自增长键值只有一个，所以这里直接getObject(1)即可
    System.out.println("自增id值" + generatedKeys.getObject(1));
}

5. 批处理

1.准备代码和sql问题演示代码

// 批量执行一组sql。大多数情况下都是批量执行insert语句。

//演示：添加1000条部门测试信息。
String sql = "insert into jdbctest(id,name,passwd) values (?,?,?)";

//Statement.RETURN_GENERATED_KEYS - 这是一个常量，表示希望获取数据库自动生成的主键值
PreparedStatement pst = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
for (int i = 1; i <= 1000; i++) {
    //设置1000次？的值
    pst.setObject(1, 0);
    pst.setObject(2, "冯七");
    pst.setObject(3, "冯七的密码");
    //先攒着这些数据，设置完，sql会重新编译一下，生成一条新的完整的sql
    pst.addBatch();
}
//最后一口气执行
pst.executeBatch();

6 .事务处理

1.准备代码和sql

演示：
	update t_department set description = 'xx' where did = 2;
	update t_department set description = 'yy' where did = 3;

	故意把其中一条sql语句写错。

    update t_department set description = 'xx' where did = 2;
	update t_department set description = 'yy' what did = 3;  #what是错误的

2.问题演示代码

/*
如果多条sql要组成一个事务，要么一起成功，要么一起失败。
例如：订单
    （1）修改商品表的商品库存和销量
    （2）订单表新建订单数据
    （3）订单明细表新建订单明细记录（多条）
    ....
    这些sql要么一起成功，要么都还原到最初。

演示：
	update t_department set description = 'xx' where did = 2;
	update t_department set description = 'yy' where did = 3;

	故意把其中一条sql语句写错。

    update t_department set description = 'xx' where did = 2;
	update t_department set description = 'yy' what did = 3;  #what是错误的，故意制造错误

JDBC如何管理事务？
（1）mysql默认是自动提交事务，每执行一条语句成功后，自动提交。
需要开启手动提交模式。

Connection连接对象.setAutoCommit(false);//取消自动提交模式，开始手动提交模式

(2)sql执行成功，别忘了提交事务
Connection连接对象.commit();

（3）sql执行失败，回滚事务
Connection连接对象.rollback();
 */
........
        connection.setAutoCommit(false);//取消自动提交模式，开始手动提交模式

        String s1 = "update t_department set description = 'xx' where did = 2";
        String s2 = "update t_department set description = 'yy' what did = 3";

        try(PreparedStatement p1 = connection.prepareStatement(s1);
            PreparedStatement p2 = connection.prepareStatement(s2);) {

            p1.executeUpdate();
            p2.executeUpdate();
            System.out.println("两条更新成功");
            connection.commit();//提交事务
        }catch(SQLException e){
            e.printStackTrace();
            System.out.println("失败");
            connection.rollback();//回滚事务
        }finally {
            connection.close();
        }
    }
}

第三章 数据库连接池

1.什么是数据库连池

连接对象的缓冲区。负责申请，分配管理，释放连接的操作。

2.为什么要使用数据库连接池

（1）不使用数据库连接池，每次都通过DriverManager获取新连接，用完直接抛弃断开，连接的利用率太低，太浪费。
（2）对于数据库服务器来说，压力太大了。我们数据库服务器和Java程序对连接数也无法控制，很容易导致数据库服务器崩溃。

我们就希望能管理连接。

• 我们可以建立一个连接池，这个池中可以容纳一定数量的连接对象，一开始，我们可以先替用户先创建好一些连接对象，等用户要拿连接对象时，就直接从池中拿，不用新建了，这样也可以节省时间。然后用户用完后，放回去，别人可以接着用。
• 可以提高连接的使用率。当池中的现有的连接都用完了，那么连接池可以向服务器申请新的连接放到池中。
  直到池中的连接达到“最大连接数”，就不能在申请新的连接了，如果没有拿到连接的用户只能等待。

3.市面上有很多现成的数据库连接池技术

• JDBC 的数据库连接池使用 javax.sql.DataSource 来表示，DataSource 只是一个接口（通常被称为数据源），该接口通常由服务器(Weblogic, WebSphere, Tomcat)提供实现，也有一些开源组织提供实现：
  • DBCP 是Apache提供的数据库连接池，速度相对c3p0较快，但因自身存在BUG，Hibernate3已不再提供支持
  • C3P0 是一个开源组织提供的一个数据库连接池，速度相对较慢，稳定性还可以
  • Proxool 是sourceforge下的一个开源项目数据库连接池，有监控连接池状态的功能，稳定性较c3p0差一点
  • BoneCP 是一个开源组织提供的数据库连接池，速度快
  • Druid 是阿里提供的数据库连接池，据说是集DBCP 、C3P0 、Proxool 优点于一身的数据库连接池

4、如何使用德鲁伊数据库连接池

1.步骤

(1)引入jar包
和引入mysql驱动jar方式一样
(2)编写配置文件
src下加一个druid.properties文件

2.druid.properties文件

driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbc?serverTimezone=UTC
username=root
password=root
initialSize=5
maxActive=10
maxWait=1000

配置	缺省	说明
name		配置这个属性的意义在于，如果存在多个数据源，监控的时候可以通过名字来区分开来。 如果没有配置，将会生成一个名字，格式是：”DataSource-” + System.identityHashCode(this)
jdbcUrl		连接数据库的url，不同数据库不一样。例如：mysql : jdbc:mysql://10.20.153.104:3306/druid2 oracle : jdbc:oracle:thin:@10.20.149.85:1521:ocnauto
username		连接数据库的用户名
password		连接数据库的密码。如果你不希望密码直接写在配置文件中，可以使用ConfigFilter。详细看这里：https://github.com/alibaba/druid/wiki/%E4%BD%BF%E7%94%A8ConfigFilter
driverClassName		根据url自动识别 这一项可配可不配，如果不配置druid会根据url自动识别dbType，然后选择相应的driverClassName(建议配置下)
initialSize	0	初始化时建立物理连接的个数。初始化发生在显示调用init方法，或者第一次getConnection时
maxActive	8	最大连接池数量
maxIdle	8	已经不再使用，配置了也没效果
minIdle		最小连接池数量
maxWait		获取连接时最大等待时间，单位毫秒。配置了maxWait之后，缺省启用公平锁，并发效率会有所下降，如果需要可以通过配置useUnfairLock属性为true使用非公平锁。
poolPreparedStatements	false	是否缓存preparedStatement，也就是PSCache。PSCache对支持游标的数据库性能提升巨大，比如说oracle。在mysql下建议关闭。
maxOpenPreparedStatements	-1	要启用PSCache，必须配置大于0，当大于0时，poolPreparedStatements自动触发修改为true。在Druid中，不会存在Oracle下PSCache占用内存过多的问题，可以把这个数值配置大一些，比如说100
validationQuery		用来检测连接是否有效的sql，要求是一个查询语句。如果validationQuery为null，testOnBorrow、testOnReturn、testWhileIdle都不会其作用。
testOnBorrow	true	申请连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能。
testOnReturn	false	归还连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能
testWhileIdle	false	建议配置为true，不影响性能，并且保证安全性。申请连接的时候检测，如果空闲时间大于timeBetweenEvictionRunsMillis，执行validationQuery检测连接是否有效。
timeBetweenEvictionRunsMillis		有两个含义： 1)Destroy线程会检测连接的间隔时间2)testWhileIdle的判断依据，详细看testWhileIdle属性的说明
numTestsPerEvictionRun		不再使用，一个DruidDataSource只支持一个EvictionRun
minEvictableIdleTimeMillis
connectionInitSqls		物理连接初始化的时候执行的sql
exceptionSorter		根据dbType自动识别 当数据库抛出一些不可恢复的异常时，抛弃连接
filters		属性类型是字符串，通过别名的方式配置扩展插件，常用的插件有： 监控统计用的filter:stat日志用的filter:log4j防御sql注入的filter:wall
proxyFilters		类型是List，如果同时配置了filters和proxyFilters，是组合关系，并非替换关系

(3)创建数据库连接池对象
(4)获取连接

3.使用代码

public class TestPool {
    public static void main(String[] args) throws Exception {
        Properties pro = new Properties();
        //因为druid.properties文件是在src下，最后会随着.java文件一起编译到类路径下（class）
        //可以通过类加载器帮我们加载资源配置文件
        pro.load(TestPool.class.getClassLoader().getResourceAsStream("druid.properties"));
        //数据连接池工厂，创建数据连接池
        DataSource ds = DruidDataSourceFactory.createDataSource(pro);

        Connection conn = ds.getConnection();
        System.out.println("使用数据库连接池连接成功：conn = " + conn);

        conn.close();

    }
}