JDBC(数据库连接池,封装JDBCUtil,Apache的DBUtils,DTO,JDBC工具类事务问题,ThreadLocal)

JDBC概述

之前我们学习了JavaSE，编写了Java程序，数据保存在变量、数组、集合等中，无法持久化，后来学习了IO流可以将数据写入文件，但不方便管理数据以及维护数据的关系；

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

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

那么就可以使用JDBC技术。

1.1 JDBC概述

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

即JDBC技术包含两个部分：

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

因为为了项目代码的可移植性，可维护性，SUN公司从最初就制定了Java程序连接各种数据库的统一接口规范。这样的话，不管是连接哪一种DBMS软件，Java代码可以保持一致性。

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

因为各个数据库厂商的DBMS软件各有不同，那么内部如何通过sql实现增、删、改、查等管理数据，只有这个数据库厂商自己更清楚，因此把接口规范的实现交给各个数据库厂商自己实现。

1.2 Java程序连接MySQL数据库

1.2.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库即可：

12.2.2 Java代码连接MySQL数据库

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

2、在内存中加载驱动类（可选）
        更早版本mysql驱动类：org.gjt.mm.mysql.Driver
        最近版本：com.mysql.jdbc.Driver
        MySQL8.0版本：com.mysql.cj.jdbc.Driver
 
  //新版的mysql驱动jar可以省略这步，旧版的mysql驱动jar必须加这一步。
  // 因为新版mysql驱动jar包下有一个META-INF/services/java.sql.Driver文件
  //里面有填写mysql驱动类的全名称，DriverManager会自动读取这个文件，并加载对应的驱动类
  //后期使用数据库连接池，或者MyBatis等框架时，在配置文件中加这个驱动类的配置即可
 Class.forName("com.mysql.cj.jdbc.Driver"); 

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

    String url = "jdbc:mysql://localhost:3306/test?serverTimezone=UTC";
    Connection conn = DriverManager.getConnection(url, "root", "123456");
    
    MySQL8使用时，url需要加参数：serverTimezone=UTC，否则会报错：
    Exception in thread "main" java.sql.SQLException: The server time zone value '�й���׼ʱ��' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the 'serverTimezone' configuration property) to use a more specifc time zone value if you want to utilize time zone support.


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

package com.atguigu.jdbc;

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

public class TestJDBC {
    public static void main(String[] args)throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection conn =  DriverManager.getConnection("jdbc:mysql://localhost:3306/atguigu?serverTimezone=UTC","root","123456");
        System.out.println("conn = " + conn);
        conn.close();
    }
}

1.3 实现增删改查

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

2、在内存中加载驱动类（可选）
 Class.forName("com.mysql.cj.jdbc.Driver"); 

3、连接数据库
通过DriverManager工具类获取数据库连接Connection的对象。
    String url = "jdbc:mysql://localhost:3306/test?serverTimezone=UTC";
    Connection conn = DriverManager.getConnection(url, "root", "123456");

 4、操作数据库
（1）通过Connection对象获取Statement或PreparedStatement对象
（2）通过Statement或PreparedStatement对象执行sql
执行增、删、改：int executeUpate()
执行查询：ResultSet executeQuery()
（3）如果服务器有查询结果返回，需要用ResultSet接收
遍历结果集的方法：
boolean next()：判断是否还有下一行
getString(字段名或序号),getInt(字段名或序号),getObject(字段名或序号)

5、释放资源（close）
 */

1.3.1 添加数据

package com.atguigu.crud;

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

/*
用JDBC实现添加一条记录到atguigu数据库的t_department表中。

mysql> desc t_department;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| did         | int          | NO   | PRI | NULL    | auto_increment |
| dname       | varchar(20)  | NO   | UNI | NULL    |                |
| description | varchar(200) | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)


mysql> select * from t_department;
+-----+--------+------------------+
| did | dname  | description      |
+-----+--------+------------------+
|   1 | 研发部 | 负责研发工作     |
|   2 | 人事部 | 负责人事管理工作 |
|   3 | 市场部 | 负责市场推广工作 |
|   4 | 财务部 | 负责财务管理工作 |
|   5 | 后勤部 | 负责后勤保障工作 |
|   6 | 测试部 | 负责测试工作     |
+-----+--------+------------------+
6 rows in set (0.00 sec)

步骤：
1、一个项目引入一次数据库驱动jar就可以
2、建立数据库连接
（1）加载驱动类：通过Class类的forName方法注册驱动
（2）获取数据库连接
通过DriverManager类的静态方法获取数据库连接对象
3、通过数据库连接对象获取Statement或PreparedStatement对象，用来执行sql
4、通过Statement或PreparedStatement对象调用
（1）int executeUpdate()：执行insert,update,delete等更新数据库数据的sql

5、关闭，释放各种资源
 */
public class TestInsert {
    public static void main(String[] args)throws Exception {
        //把驱动类加载到内存中
        Class.forName("com.mysql.cj.jdbc.Driver");
        //B：获取数据库连接对象
        String url = "jdbc:mysql://localhost:3306/atguigu?serverTimezone=UTC";
        Connection connection = DriverManager.getConnection(url,"root","123456");
        //Connection   ==> 网络编程的Socket

        String sql = "insert into t_department values(null,'测试数据部门','测试数据部门简介')";//发给服务器的sql
        PreparedStatement pst = connection.prepareStatement(sql);
        //PreparedStatement ==> IO流  网络编程的socket.getOutputStream()发生数据用的
        int len = pst.executeUpdate();
        //返回sql影响的记录数
        System.out.println(len>0 ? "添加成功" : "添加失败");

        pst.close();
        connection.close();
        /*
mysql> select * from t_department;
+-----+--------------+------------------+
| did | dname        | description      |
+-----+--------------+------------------+
|   1 | 研发部       | 负责研发工作     |
|   2 | 人事部       | 负责人事管理工作 |
|   3 | 市场部       | 负责市场推广工作 |
|   4 | 财务部       | 负责财务管理工作 |
|   5 | 后勤部       | 负责后勤保障工作 |
|   6 | 测试部       | 负责测试工作     |
|   7 | 测试数据部门 | 测试数据部门简介 |
+-----+--------------+------------------+
7 rows in set (0.00 sec)
         */
    }
}

1.3.2 修改数据

package com.atguigu.crud;

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

public class TestUpdate {
    public static void main(String[] args)throws Exception {
        //把驱动类加载到内存中
        Class.forName("com.mysql.cj.jdbc.Driver");
        //B：获取数据库连接对象
        String url = "jdbc:mysql://localhost:3306/atguigu?serverTimezone=UTC";
        Connection connection = DriverManager.getConnection(url, "root", "123456");
        //Connection   ==> 网络编程的Socket

        String sql = "update t_department set description = 'xx' where did = 7";//发给服务器的sql
        PreparedStatement pst = connection.prepareStatement(sql);
        //PreparedStatement ==> IO流  网络编程的socket.getOutputStream()发生数据用的
        int len = pst.executeUpdate();
        //返回sql影响的记录数
        System.out.println(len > 0 ? "修改成功" : "修改失败");

        pst.close();
        connection.close();
    }
}
/*
mysql> select * from t_department;
+-----+--------------+------------------+
| did | dname        | description      |
+-----+--------------+------------------+
|   1 | 研发部       | 负责研发工作     |
|   2 | 人事部       | 负责人事管理工作 |
|   3 | 市场部       | 负责市场推广工作 |
|   4 | 财务部       | 负责财务管理工作 |
|   5 | 后勤部       | 负责后勤保障工作 |
|   6 | 测试部       | 负责测试工作     |
|   7 | 测试数据部门 | xx               |
+-----+--------------+------------------+
7 rows in set (0.00 sec)
 */

1.3.3 删除数据

package com.atguigu.crud;

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

public class TestDelete {
    public static void main(String[] args)throws Exception {
        //把驱动类加载到内存中
        Class.forName("com.mysql.cj.jdbc.Driver");
        //B：获取数据库连接对象
        String url = "jdbc:mysql://localhost:3306/atguigu?serverTimezone=UTC";
        Connection connection = DriverManager.getConnection(url, "root", "123456");
        //Connection   ==> 网络编程的Socket

        String sql = "delete from t_department where did = 7";//发给服务器的sql
        PreparedStatement pst = connection.prepareStatement(sql);
        //PreparedStatement ==> IO流  网络编程的socket.getOutputStream()发生数据用的
        int len = pst.executeUpdate();
        //返回sql影响的记录数
        System.out.println(len > 0 ? "删除成功" : "删除失败");

        pst.close();
        connection.close();
    }
}
/*
mysql> select * from t_department;
+-----+--------+------------------+
| did | dname  | description      |
+-----+--------+------------------+
|   1 | 研发部 | 负责研发工作     |
|   2 | 人事部 | 负责人事管理工作 |
|   3 | 市场部 | 负责市场推广工作 |
|   4 | 财务部 | 负责财务管理工作 |
|   5 | 后勤部 | 负责后勤保障工作 |
|   6 | 测试部 | 负责测试工作     |
+-----+--------+------------------+
6 rows in set (0.00 sec)
 */

1.3.4 查询数据

package com.atguigu.crud;

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

/*
步骤：
1、一个项目引入一次数据库驱动jar就可以
2、建立数据库连接
（1）加载驱动类：通过Class类的forName方法注册驱动
（2）获取数据库连接
通过DriverManager类的静态方法获取数据库连接对象
3、通过数据库连接对象获取Statement或PreparedStatement对象，用来执行sql
4、通过Statement或PreparedStatement对象调用
（1）int executeUpdate()：执行insert,update,delete等更新数据库数据的sql
（2）ResultSet executeQuery()：执行select查询的sql，返回一个结果集
（3）boolean execute()：可以用来执行DDL语句


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

5、关闭，释放各种资源
 */
public class TestSelect {
    public static void main(String[] args)throws Exception {
        //把驱动类加载到内存中
        Class.forName("com.mysql.cj.jdbc.Driver");
        //B：获取数据库连接对象
        String url = "jdbc:mysql://localhost:3306/atguigu?serverTimezone=UTC";
        Connection connection = DriverManager.getConnection(url, "root", "123456");
        //Connection   ==> 网络编程的Socket

        String sql = "select * from t_department";//发给服务器的sql
        PreparedStatement pst = connection.prepareStatement(sql);
        //PreparedStatement ==> IO流  网络编程的socket.getOutputStream()发生数据用的

        ResultSet resultSet = pst.executeQuery();//==>IO流  输入流，又像是集合和迭代器的集成
        while(resultSet.next()){ //while循环一次，迭代一行，遍历一行
            int did = resultSet.getInt("did");//get一次得到一个单元格的数据
            String dname = resultSet.getString("dname");
            String decription = resultSet.getString("description");
            System.out.println(did +"\t" + dname +"\t" + decription);
        }

        resultSet.close();
        pst.close();
        connection.close();
    }
}

第二章 轻松处理各种问题

2.1 避免sql拼接问题

准备代码和sql

Scanner input = new Scanner(System.in);
System.out.print("请输入姓名：");
String ename = input.next();//李四

System.out.print("请输入薪资：");
double salary = input.nextDouble();//15000

System.out.print("请输入出生日期：");
String birthday = input.next();//1990-1-1

System.out.print("请输入性别：");
char gender = input.next().charAt(0);//男

System.out.print("请输入手机号码：");
String tel = input.next();//13578595685

System.out.print("请输入邮箱：");
String email = input.next();//zhangsan@atguigu.com

input.close();

INSERT INTO t_employee(ename,salary,birthday,gender,tel,email,hiredate)VALUES(值列表);

问题演示代码

package com.atguigu.problem;

import org.junit.Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.Date;
import java.util.Scanner;

/*
避免sql拼接问题

演示：从键盘输入数据，存储到atguigu数据库的t_employee表中。

mysql> desc t_employee;
+----------------+----------------------------------+------+-----+---------+----------------+
| Field          | Type                             | Null | Key | Default | Extra          |
+----------------+----------------------------------+------+-----+---------+----------------+
| eid            | int                              | NO   | PRI | NULL    | auto_increment |
| ename          | varchar(20)                      | NO   |     | NULL    |                |
| salary         | double                           | NO   |     | NULL    |                |
| commission_pct | decimal(3,2)                     | YES  |     | NULL    |                |
| birthday       | date                             | NO   |     | NULL    |                |
| gender         | enum('男','女')                  | NO   |     | 男      |                |
| tel            | char(11)                         | NO   |     | NULL    |                |
| email          | varchar(32)                      | NO   |     | NULL    |                |
| address        | varchar(150)                     | YES  |     | NULL    |                |
| work_place     | set('北京','深圳','上海','武汉') | NO   |     | 北京    |                |
| hiredate       | date                             | NO   |     | NULL    |                |
| job_id         | int                              | YES  | MUL | NULL    |                |
| mid            | int                              | YES  | MUL | NULL    |                |
| did            | int                              | YES  | MUL | NULL    |                |
+----------------+----------------------------------+------+-----+---------+----------------+
14 rows in set (0.00 sec)


PreparedStatement接口是Statement接口的子接口的。
Statement接口是不支持 ？ 形式的sql，只能拼接。
 */
public class TestSQLConcat {
    @Test
    public void test01()throws Exception{
        Scanner input = new Scanner(System.in);
        System.out.print("请输入姓名：");
        String ename = input.next();//李四

        System.out.print("请输入薪资：");
        double salary = input.nextDouble();//15000

        System.out.print("请输入出生日期：");
        String birthday = input.next();//1990-1-1

        System.out.print("请输入性别：");
        char gender = input.next().charAt(0);//男

        System.out.print("请输入手机号码：");
        String tel = input.next();//13578595685

        System.out.print("请输入邮箱：");
        String email = input.next();//zhangsan@atguigu.com

        //A:把驱动类加载到内存中
        Class.forName("com.mysql.cj.jdbc.Driver");
        //B：获取数据库连接对象
        String url = "jdbc:mysql://localhost:3306/atguigu?serverTimezone=UTC";
        Connection connection = DriverManager.getConnection(url,"root","123456");
        //Connection   ==> 网络编程的Socket

        //给t_employee表中的有非空约束的字段赋值，因为work_place有默认值，这里就没有管它
        String sql = "INSERT INTO t_employee(ename,salary,birthday,gender,tel,email,hiredate)VALUES(" +
                "'" + ename +"'," + //这里的'，是mysql中表示字符串和日期的单引号
                salary + "," +
                "'" + birthday +"'," +
                "'" + gender +"'," +
                "'" + tel +"'," +
                "'" + email +"'," +
                "curdate()" +   //mysql中日期函数，获取当前系统时间
                ")";
        //C：通过连接对象，获取PreparedStatement对象
        PreparedStatement pst = connection.prepareStatement(sql);

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

        //E：释放连接等资源
        pst.close();
        connection.close();
         input.close();
    }

    @Test
    public void test02()throws Exception{
        Scanner input = new Scanner(System.in);
        System.out.print("请输入姓名：");
        String ename = input.next();//李四

        System.out.print("请输入薪资：");
        double salary = input.nextDouble();//15000

        System.out.print("请输入出生日期：");
        String birthday = input.next();//1990-1-1

        System.out.print("请输入性别：");
        char gender = input.next().charAt(0);//男

        System.out.print("请输入手机号码：");
        String tel = input.next();//13578595685

        System.out.print("请输入邮箱：");
        String email = input.next();//zhangsan@atguigu.com

        //A:把驱动类加载到内存中
        Class.forName("com.mysql.cj.jdbc.Driver");
        //B：获取数据库连接对象
        String url = "jdbc:mysql://localhost:3306/atguigu?serverTimezone=UTC";
        Connection connection = DriverManager.getConnection(url,"root","123456");
        //Connection   ==> 网络编程的Socket

        //给t_employee表中的有非空约束的字段赋值，因为work_place有默认值，这里就没有管它
        String sql = "INSERT INTO t_employee(ename,salary,birthday,gender,tel,email,hiredate)VALUES(?,?,?,?,?,?,?)";
        //这里的？就像占位符，表示要给几个字段赋值
        //C：通过连接对象，获取PreparedStatement对象
        PreparedStatement pst = connection.prepareStatement(sql);//此时对sql进行预编译，里面是带？的

        //要给每一个？指定具体的值
        /*
        PreparedStatement支持给每一个字段指定值时，确定数据类型，例如：
        pst.setString(1,ename);
        pst.setDouble(2,salary);
        ...
        但是这样有点麻烦，还要一一去确定数据类型
        PreparedStatement支持用Object统一处理
        pst.setObject(1,ename);
        pst.setObject(2,salary);
         */
        pst.setObject(1,ename);  //这里的1，表示第1个？
        pst.setObject(2,salary);  //这里的2，表示第2个？
        pst.setObject(3,birthday);  //这里的3，表示第3个？
        pst.setObject(4,gender);  //这里的4，表示第4个？
        pst.setObject(5,tel);  //这里的5，表示第5个？
        pst.setObject(6,email);  //这里的6，表示第6个？
        pst.setObject(7,"curdate()");  //这里的7，表示第7个？
        //每一个？与你要赋值的字段对应，不能对错了

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

        /*
        (1)上面的代码执行出错了：java.sql.SQLException: Data truncated for column 'gender' at row 1
        原因： pst.setObject(4,gender); 把gender自动包装为Character对象
        解决方案：把char类型用String类型表示。

        (2)com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Incorrect date value: 'curdate()' for column 'hiredate' at row 1
         pst.setObject(7,"curdate()");  "curdate()"代表字符串，setObject把curdate()识别为一个字符串，而不是mysql函数， 这句代码给hiredate字段赋值的是一个String
        hiredate需要的是一个日期。

        解决方案：pst.setObject(7, new Date());
         */

        //E：释放连接等资源
        pst.close();
        connection.close();
        input.close();
    }

    @Test
    public void test03()throws Exception{
        Scanner input = new Scanner(System.in);
        System.out.print("请输入姓名：");
        String ename = input.next();//李四

        System.out.print("请输入薪资：");
        double salary = input.nextDouble();//15000

        System.out.print("请输入出生日期：");
        String birthday = input.next();//1990-1-1

        System.out.print("请输入性别：");
//        char gender = input.next().charAt(0);//男
        String gender = input.next();//男

        System.out.print("请输入手机号码：");
        String tel = input.next();//13578595685

        System.out.print("请输入邮箱：");
        String email = input.next();//zhangsan@atguigu.com

        //A:把驱动类加载到内存中
        Class.forName("com.mysql.cj.jdbc.Driver");
        //B：获取数据库连接对象
        String url = "jdbc:mysql://localhost:3306/atguigu?serverTimezone=UTC";
        Connection connection = DriverManager.getConnection(url,"root","123456");
        //Connection   ==> 网络编程的Socket

        //给t_employee表中的有非空约束的字段赋值，因为work_place有默认值，这里就没有管它
        String sql = "INSERT INTO t_employee(ename,salary,birthday,gender,tel,email,hiredate)VALUES(?,?,?,?,?,?,?)";
        //这里的？就像占位符，表示要给几个字段赋值
        //C：通过连接对象，获取PreparedStatement对象
        PreparedStatement pst = connection.prepareStatement(sql);//此时对sql进行预编译，里面是带？的

        //要给每一个？指定具体的值
        /*
        PreparedStatement支持给每一个字段指定值时，确定数据类型，例如：
        pst.setString(1,ename);
        pst.setDouble(2,salary);
        ...
        但是这样有点麻烦，还要一一去确定数据类型
        PreparedStatement支持用Object统一处理
        pst.setObject(1,ename);
        pst.setObject(2,salary);
         */
        pst.setObject(1,ename);  //这里的1，表示第1个？
        pst.setObject(2,salary);  //这里的2，表示第2个？
        pst.setObject(3,birthday);  //这里的3，表示第3个？
        pst.setObject(4,gender);  //这里的4，表示第4个？
        pst.setObject(5,tel);  //这里的5，表示第5个？
        pst.setObject(6,email);  //这里的6，表示第6个？
        pst.setObject(7, new Date());  //这里的7，表示第7个？
        //每一个？与你要赋值的字段对应，不能对错了

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

        /*
        上面的代码执行出错了：java.sql.SQLException: Data truncated for column 'gender' at row 1
        原因： pst.setObject(4,gender); 把gender自动包装为Character对象
        解决方案：把char类型用String类型表示。
         */

        //E：释放连接等资源
        pst.close();
        connection.close();
         input.close();
    }
}

2.2 避免sql注入问题

准备代码和sql

Scanner input = new Scanner(System.in);
System.out.print("请输入你要查询的员工的编号：");
String id = input.nextLine();
input.close();

select * from t_employee where eid = 值;

问题演示代码

package com.atguigu.problem;

import org.junit.Test;

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

/*
避免sql注入问题

演示：从键盘输入员工编号，查询员工信息。每一个员工都可以输入自己的编号，查看自己的信息。
 */
public class TestSQLInject {
    @Test
    public void test01()throws Exception{
        Scanner input = new Scanner(System.in);
        System.out.print("请输入你要查询的员工的编号：");
        String id = input.nextLine();
        // 第一种正常输入：1
        // 第二种恶意输入：1 or 1=1  第一个1表示员工编号， 后面 or 1= 1表示条件，而1=1是永远成立，其他条件全部失效

        //把驱动类加载到内存中
        Class.forName("com.mysql.cj.jdbc.Driver");
        //B：获取数据库连接对象
        String url = "jdbc:mysql://localhost:3306/atguigu?serverTimezone=UTC";
        Connection connection = DriverManager.getConnection(url, "root", "123456");
        //Connection   ==> 网络编程的Socket

        String sql = "select * from t_employee where eid = " + id;
        System.out.println("sql = " + sql);//select * from t_employee where eid = 1 or 1=1
        PreparedStatement pst = connection.prepareStatement(sql);

        //执行查询
        ResultSet rs = pst.executeQuery();
        /*
        ResultSet接口提供了
        (1)boolean next()：判断是否有下一条记录
        (2)获取某个单元格的数据
        String getString(字段名)
        int getInt(字段名)
        double getDouble(字段名)
        ...

        有点麻烦，需要一一去对应字段名
        Object getObject(字段名)
        Object getObject(字段的序号)  从1开始。
         */
        while(rs.next()){//while循环一次，代表一行
            //t_employee有14个字段
            for(int i=1; i<=14; i++){//for循环一次，代表一行中的一个单元格
                System.out.print(rs.getObject(i)+"\t");
            }
            System.out.println();
        }

        rs.close();
        pst.close();
        connection.close();
        input.close();
/*
用户输入 1 or 1=1，盗取了表中的所有数据。
1	孙洪亮	28000.0	0.65	1980-10-08	男	13789098765	shl@atguigu.com	白庙村西街	北京,深圳	2011-07-28	1	1	1
2	何进	7001.0	0.10	1984-08-03	男	13456732145	hj@atguigu.com	半截塔存	深圳,上海	2015-07-03	2	1	1
3	邓超远	8000.0	null	1985-04-09	男	18678973456	dcy666@atguigu.com	宏福苑	北京,深圳,上海,武汉	2014-07-01	3	7	1
4	黄熙萌	9456.0	null	1986-09-07	女	13609876789	hxm@atguigu.com	白庙村东街	深圳,上海,武汉	2015-08-08	8	22	3
5	陈浩	8567.0	null	1978-08-02	男	13409876545	ch888@atguigu.com	回龙观	北京,深圳,上海	2015-01-01	3	7	1
6	韩庚年	12000.0	null	1985-04-03	男	18945678986	hgn@atguigu.com	龙泽	深圳,上海	2015-02-02	3	2	1
7	贾宝玉	15700.0	0.24	1982-08-02	男	15490876789	jby@atguigu.com	霍营	北京,武汉	2015-03-03	2	1	1
8	李晨熙	9000.0	0.40	1983-03-02	女	13587689098	lc@atguigu.com	东三旗	深圳,上海,武汉	2015-01-06	4	1	1
9	李易峰	7897.0	null	1984-09-01	男	13467676789	lyf@atguigu.com	西山旗	武汉	2015-04-01	3	7	1
10	陆风	8789.0	null	1989-04-02	男	13689876789	lf@atguigu.com	天通苑一区	北京	2014-09-03	2	1	1
11	黄冰茹	15678.0	null	1983-05-07	女	13787876565	hbr@atguigu.com	立水桥	深圳	2014-04-04	4	1	1
12	孙红梅	9000.0	null	1986-04-02	女	13576234554	shm@atguigu.com	立城苑	上海	2014-02-08	3	7	1
13	李冰冰	18760.0	null	1987-04-09	女	13790909887	lbb@atguigu.com	王府温馨公寓	北京	2015-06-07	3	2	1
14	谢吉娜	18978.0	0.25	1990-01-01	女	13234543245	xjn@atguigu.com	园中园	上海,武汉	2015-09-05	5	14	2
15	董吉祥	8978.0	null	1987-05-05	男	13876544333	djx@atguigu.com	小辛庄	北京,上海	2015-08-04	6	14	2
16	彭超越	9878.0	null	1988-03-06	男	18264578930	pcy@atguigu.com	西二旗	深圳,武汉	2015-03-06	8	22	3
17	李诗雨	9000.0	null	1990-08-09	女	18567899098	lsy@atguigu.com	清河	北京,深圳,武汉	2013-06-09	8	22	3
18	舒淇格	16788.0	0.10	1978-09-04	女	18654565634	sqg@atguigu.com	名流花园	北京,深圳,武汉	2013-04-05	9	18	4
19	周旭飞	7876.0	null	1988-06-13	女	13589893434	sxf@atguigu.com	小汤山	北京,深圳	2014-04-07	10	18	4
20	章嘉怡	15099.0	0.10	1989-12-11	女	15634238979	zjy@atguigu.com	望都家园	北京	2015-08-04	11	20	5
21	白露	9787.0	null	1989-09-04	女	18909876789	bl@atguigu.com	西湖新村	上海	2014-06-05	12	20	5
22	刘烨	13099.0	0.32	1990-11-09	男	18890980989	ly@atguigu.com	多彩公寓	北京,上海	2016-08-09	7	22	3
23	陈纲	13090.0	null	1990-02-04	男	18712345632	cg@atguigu.com	天通苑二区	深圳	2016-05-09	3	2	1
24	吉日格勒	10289.0	null	1990-04-01	男	17290876543	jrgl@163.com	北苑	北京	2017-02-06	12	20	5
25	额日古那	9087.0	null	1989-08-01	女	18709675645	ergn@atguigu.com	望京	北京,上海	2017-09-01	3	2	1
26	李红	5000.0	null	1995-02-15	女	15985759663	lihong@atguigu.com	冠雅苑	北京	2021-09-01	null	null	null
27	周洲	8000.0	null	1990-01-01	男	13574528569	zhouzhou@atguigu.com	冠华苑	北京,深圳	2020-08-15	3	2	null
28	张三	15000.0	null	1999-05-05	男	10086	zhangsan@atguigu.com	null	北京	2021-12-03	null	null	null
29	李四	14000.0	null	2000-09-09	女	10010	lisi@atguigu.com	null	北京	2021-12-03	null	null	null

 */
    }

    @Test
    public void test02()throws Exception {
        Scanner input = new Scanner(System.in);
        System.out.print("请输入你要查询的员工的编号：");
        String id = input.nextLine();
        // 第一种正常输入：1
        // 第二种恶意输入：1 or 1=1  第一个1表示员工编号， 后面 or 1= 1表示条件，而1=1是永远成立，其他条件全部失效

        //把驱动类加载到内存中
        Class.forName("com.mysql.cj.jdbc.Driver");
        //B：获取数据库连接对象
        String url = "jdbc:mysql://localhost:3306/atguigu?serverTimezone=UTC";
        Connection connection = DriverManager.getConnection(url, "root", "123456");
        //Connection   ==> 网络编程的Socket

        String sql = "select * from t_employee where eid = ? ";//不用拼接了
        PreparedStatement pst = connection.prepareStatement(sql);

        //给？指定值
        pst.setObject(1, id);
        //因为这里把 "2 or 1=1"当成一个整体赋值给eid
        //select * from t_employee where eid = '2 or 1=1';
        /*
        eid字段是int类型，mysql在解析时，发现给eid赋值了字符串，会把字符串尽量转为int类型的值。
        SELECT '2 or 1=1'+0;  得到结果是2。
        select * from t_employee where eid = '2 or 1=1';
        等价于
        select * from t_employee where eid = 2;
         */

        //执行查询
        ResultSet rs = pst.executeQuery();
        /*
        ResultSet接口提供了
        (1)boolean next()：判断是否有下一条记录
        (2)获取某个单元格的数据
        String getString(字段名)
        int getInt(字段名)
        double getDouble(字段名)
        ...

        有点麻烦，需要一一去对应字段名
        Object getObject(字段名)
        Object getObject(字段的序号)  从1开始。
         */
        while (rs.next()) {//while循环一次，代表一行
            //t_employee有14个字段
            for (int i = 1; i <= 14; i++) {//for循环一次，代表一行中的一个单元格
                System.out.print(rs.getObject(i) + "\t");
            }
            System.out.println();
        }

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

2.3 使用字节IO流读取图片等给blob等二进制类型数据赋值

准备代码和sql

        Scanner input = new Scanner(System.in);
        System.out.print("请输入用户名：");
        String username = input.next();

        System.out.print("请输入密码：");
        String password = input.next();

        System.out.print("请选择照片：");
        String path = input.next();//这里没有图形化界面，只能输入路径，通过IO流读取图片的内容
        
		input.close();

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`)
);

添加记录：insert into t_user values(null,?,?,?);

修改表结构：ALTER TABLE t_user MODIFY photo MEDIUMBLOB;

问题演示代码

package com.atguigu.problem;

import org.junit.Test;

import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.Scanner;

/*
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)
 */
public class TestBlob {
    @Test
    public void test01()throws Exception{
        Scanner input = new Scanner(System.in);
        System.out.print("请输入用户名：");
        String username = input.next();

        System.out.print("请输入密码：");
        String password = input.next();

        System.out.print("请选择照片：");
        String path = input.next();//这里没有图形化界面，只能输入路径，通过IO流读取图片的内容

        //把驱动类加载到内存中
        Class.forName("com.mysql.cj.jdbc.Driver");
        //B：获取数据库连接对象
        String url = "jdbc:mysql://localhost:3306/atguigu?serverTimezone=UTC";
        Connection connection = DriverManager.getConnection(url, "root", "123456");
        //Connection   ==> 网络编程的Socket

        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类型的数据，存不下这么大的图片
解决方案：修改字段的数据类型

alter table t_user modify photo mediumblob;

mysql> alter table t_user modify photo mediumblob;
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
Connection id:    9
Current database: atguigu

Query OK, 1 row affected (0.08 sec)
Records: 1  Duplicates: 0  Warnings: 0

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    | mediumblob  | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
 */
    }
}

2.4 获取自增长键值

准备代码和sql

INSERT INTO t_employee(ename,salary,birthday,gender,tel,email,hiredate)VALUES(?,?,?,?,?,?,?)

		Scanner input = new Scanner(System.in);
        System.out.print("请输入姓名：");
        String ename = input.next();//李四

        System.out.print("请输入薪资：");
        double salary = input.nextDouble();//15000

        System.out.print("请输入出生日期：");
        String birthday = input.next();//1990-1-1

        System.out.print("请输入性别：");
        String gender = input.next();//男  mysql的gender是枚举类型，这里用String处理

        System.out.print("请输入手机号码：");
        String tel = input.next();//13578595685

        System.out.print("请输入邮箱：");
        String email = input.next();//zhangsan@atguigu.com

		input.close();

问题演示代码

package com.atguigu.problem;

import org.junit.Test;

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

/*
获取自增长键值：
mysql中很多表都是有自增长字段，特别是id。
当我们添加了一个员工、部门，添加成功后，需要立刻返回该员工、部门的自增长的id值。

（1）在用Connection数据库连接对象获取PreparedStatement对象时，要加一个参数
PreparedStatement pst = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
这里Statement.RETURN_GENERATED_KEYS表示，执行sql后，返回自增长键值

（2）执行完成之后，需要从PreparedStatement对象中获取自增长键值
 ResultSet rs = pst.getGeneratedKeys(); //方法别调错
if(rs.next()){ //因为只有一条记录，所以只有一个自增长键值，用if即可
    System.out.println("新员工编号是：" + rs.getObject(1));//因为自增长键值只有一个，所以这里直接getObject(1)即可
}
 */
public class TestGenerateKey {
    @Test
    public void test03()throws Exception{
        Scanner input = new Scanner(System.in);
        System.out.print("请输入姓名：");
        String ename = input.next();//李四

        System.out.print("请输入薪资：");
        double salary = input.nextDouble();//15000

        System.out.print("请输入出生日期：");
        String birthday = input.next();//1990-1-1

        System.out.print("请输入性别：");
        String gender = input.next();//男  mysql的gender是枚举类型，这里用String处理

        System.out.print("请输入手机号码：");
        String tel = input.next();//13578595685

        System.out.print("请输入邮箱：");
        String email = input.next();//zhangsan@atguigu.com

        //A:把驱动类加载到内存中
        Class.forName("com.mysql.cj.jdbc.Driver");
        //B：获取数据库连接对象
        String url = "jdbc:mysql://localhost:3306/atguigu?serverTimezone=UTC";
        Connection connection = DriverManager.getConnection(url,"root","123456");
        //Connection   ==> 网络编程的Socket

        //给t_employee表中的有非空约束的字段赋值，因为work_place有默认值，这里就没有管它
        String sql = "INSERT INTO t_employee(ename,salary,birthday,gender,tel,email,hiredate)VALUES(?,?,?,?,?,?,?)";
        //这里的？就像占位符，表示要给几个字段赋值
        //C：通过连接对象，获取PreparedStatement对象
        PreparedStatement pst = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);//此时对sql进行预编译，里面是带？的
        //Statement.RETURN_GENERATED_KEYS表示，执行sql后，返回自增长键值

        //要给每一个？指定具体的值
        /*
        PreparedStatement支持给每一个字段指定值时，确定数据类型，例如：
        pst.setString(1,ename);
        pst.setDouble(2,salary);
        ...
        但是这样有点麻烦，还要一一去确定数据类型
        PreparedStatement支持用Object统一处理
        pst.setObject(1,ename);
        pst.setObject(2,salary);
         */
        pst.setObject(1,ename);  //这里的1，表示第1个？
        pst.setObject(2,salary);  //这里的2，表示第2个？
        pst.setObject(3,birthday);  //这里的3，表示第3个？
        pst.setObject(4,gender);  //这里的4，表示第4个？
        pst.setObject(5,tel);  //这里的5，表示第5个？
        pst.setObject(6,email);  //这里的6，表示第6个？
        pst.setObject(7, new Date());  //这里的7，表示第7个？
        //每一个？与你要赋值的字段对应，不能对错了

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

        ResultSet rs = pst.getGeneratedKeys();
        if(rs.next()){
            System.out.println("新员工编号是：" + rs.getObject(1));//因为自增长键值只有一个，所以这里直接getObject(1)即可
        }

        //E：释放连接等资源
        rs.close();
        pst.close();
        connection.close();
        input.close();
    }
}

2.5 批处理

准备代码和sql

insert into t_department values(null,?,?)

问题演示代码

package com.atguigu.problem;

import org.junit.Test;

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

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

    演示：给部门表批量添加1000条部门测试信息。

MySQL服务器端，默认批处理功能没有开启。需要通过参数告知mysql服务器，开启批处理功能。
在url后面再加一个参数 rewriteBatchedStatements=true

url的格式：
    jdbc:mysql://localhost:3306/atguigu
    如果要加参数，需要用一个?，表示后面是参数

    jdbc:mysql://localhost:3306/atguigu?serverTimezone=UTC

    如果有多个参数，参数之间使用&连接，
    每一个参数都是key=value的格式。

 jdbc:mysql://localhost:3306/atguigu?serverTimezone=UTC&rewriteBatchedStatements=true

 如何实现批处理？
 （1）url中加rewriteBatchedStatements=true
jdbc:mysql://localhost:3306/atguigu?serverTimezone=UTC&rewriteBatchedStatements=true

（2）PreparedStatement对象调用
A：addBatch()
B：executeBatch()

（3）不要把values写错value（学生问题）
 */
public class TestBatch {
    @Test
    public void test01()throws Exception{
        long start = System.currentTimeMillis();

        //把驱动类加载到内存中
        Class.forName("com.mysql.cj.jdbc.Driver");
        //B：获取数据库连接对象
        String url = "jdbc:mysql://localhost:3306/atguigu?serverTimezone=UTC";
        Connection connection = DriverManager.getConnection(url, "root", "123456");
        //Connection   ==> 网络编程的Socket

        String sql = "insert into t_department values(null,?,?)";
        PreparedStatement pst = connection.prepareStatement(sql);
        //上面的sql是重复使用的，PreparedStatement对象也重复使用，不用new1000个对象。
        //mysql服务器端也可以优化，提高性能

        for(int i=1; i<=1000; i++){
            //设置1000次？的值
            pst.setObject(1,"测试"+i);
            pst.setObject(2,"测试简介"+i);

            pst.executeUpdate();//这里就不接收返回值了
            //逐条添加，每设置一次？，就立刻添加一条
            //设置完？是在内存中，添加到表中，是要用IO流写到文件中，
            // 添加1000条意味着要用IO流与文件通信1000次
        }

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

        long end = System.currentTimeMillis();
        System.out.println("耗时：" + (end-start));//耗时：4700
    }

    @Test
    public void test02()throws Exception{
        long start = System.currentTimeMillis();

        //把驱动类加载到内存中
        Class.forName("com.mysql.cj.jdbc.Driver");
        //B：获取数据库连接对象
        String url = "jdbc:mysql://localhost:3306/atguigu?serverTimezone=UTC";
        Connection connection = DriverManager.getConnection(url, "root", "123456");
        //Connection   ==> 网络编程的Socket

        String sql = "insert into t_department values(null,?,?)";
        PreparedStatement pst = connection.prepareStatement(sql);
        //上面的sql是重复使用的，PreparedStatement对象也重复使用，不用new1000个对象。
        //mysql服务器端也可以优化，提高性能

        for(int i=1001; i<=2000; i++){
            //设置1000次？的值
            pst.setObject(1,"测试"+i);
            pst.setObject(2,"测试简介"+i);

//            pst.executeUpdate();//不用设置一次？，就执行一次
            pst.addBatch();//先攒着这些数据，设置完？，sql会重新编译一下，生成一条新的完整的sql
        }
        pst.executeBatch();//最后一口气执行

//        这里虽然调用了批处理执行sql的方法，但是url中没有告诉mysql开启批处理的功能，仍然是一条一条添加的

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

        long end = System.currentTimeMillis();
        System.out.println("耗时：" + (end-start));//耗时：4714
    }

    @Test
    public void test03()throws Exception{
        long start = System.currentTimeMillis();

        //把驱动类加载到内存中
        Class.forName("com.mysql.cj.jdbc.Driver");
        //B：获取数据库连接对象
        String url = "jdbc:mysql://localhost:3306/atguigu?serverTimezone=UTC&rewriteBatchedStatements=true";
        Connection connection = DriverManager.getConnection(url, "root", "123456");
        //Connection   ==> 网络编程的Socket

        String sql = "insert into t_department values(null,?,?)";
        PreparedStatement pst = connection.prepareStatement(sql);
        //上面的sql是重复使用的，PreparedStatement对象也重复使用，不用new1000个对象。
        //mysql服务器端也可以优化，提高性能

        for(int i=2001; i<=3000; i++){
            //设置1000次？的值
            pst.setObject(1,"测试"+i);
            pst.setObject(2,"测试简介"+i);

//            pst.executeUpdate();//不用设置一次？，就执行一次
            pst.addBatch();//先攒着这些数据，设置完？，sql会重新编译一下，生成一条新的完整的sql
        }
        pst.executeBatch();//最后一口气执行

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

        long end = System.currentTimeMillis();
        System.out.println("耗时：" + (end-start));//耗时：1625
    }
    
        @Test
    public void test04()throws Exception{
        long start = System.currentTimeMillis();

        //把驱动类加载到内存中
        Class.forName("com.mysql.cj.jdbc.Driver");
        //B：获取数据库连接对象
        String url = "jdbc:mysql://localhost:3306/atguigu?serverTimezone=UTC&rewriteBatchedStatements=true";
        Connection connection = DriverManager.getConnection(url, "root", "123456");
        //Connection   ==> 网络编程的Socket

        String sql = "insert into t_department values(null,?,?)";//如果把values写错value，批处理不起作用
        PreparedStatement pst = connection.prepareStatement(sql);
        //上面的sql是重复使用的，PreparedStatement对象也重复使用，不用new1000个对象。
        //mysql服务器端也可以优化，提高性能

        for(int i=2001; i<=3000; i++){
            //设置1000次？的值
            pst.setObject(1,"测试"+i);
            pst.setObject(2,"测试简介"+i);

//            pst.executeUpdate();//不用设置一次？，就执行一次
            pst.addBatch();//先攒着这些数据，设置完？，sql会重新编译一下，生成一条新的完整的sql
        }
        pst.executeBatch();//最后一口气执行

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

        long end = System.currentTimeMillis();
        System.out.println("耗时：" + (end-start));//耗时：4625
    }
}

2.6 事务处理

准备代码和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是错误的

问题演示代码

package com.atguigu.problem;

import org.junit.Test;

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

/*
如果多条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();
 */
public class TestTransaction {
    @Test
    public void test01()throws Exception{
        //把驱动类加载到内存中
        Class.forName("com.mysql.cj.jdbc.Driver");
        //B：获取数据库连接对象
        String url = "jdbc:mysql://localhost:3306/atguigu?serverTimezone=UTC";
        Connection connection = DriverManager.getConnection(url, "root", "123456");
        //Connection   ==> 网络编程的Socket

        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();
        }
    }
}

第四章 数据库连接池

4.1 什么是数据库连接池

连接池是connection对象的缓冲区，它里面会存放一些connection，当我们Java程序需要使用connection的时候，如果连接池中有则直接从连接池获取，不需要去新创建connection了。连接池让Java程序能够复用连接、管理连接

4.2 为什么要使用连接池

• 1.因为每次创建和销毁连接都会带来较大的系统开销
• 2.可以防止大量用户并发访问数据库服务器。

4.3 连接池的优势

1. 资源重用

由于数据库连接得到重用，避免了频繁创建、释放连接引起的大量性能开销。在减少系统消耗的基础上，另一方面也增进了系统运行环境的平稳性（减少内存碎片以及数据库临时进程/线程的数量）。

2. 更快的系统响应速度

数据库连接池在初始化过程中，往往已经创建了若干数据库连接置于池中备用。此时连接的初始化工作均已完成。对于业务请求处理而言，直接利用现有可用连接，避免了数据库连接初始化和释放过程的时间开销，从而缩减了系统整体响应时间。

4.4 连接池的原理

1. 连接池维护着两个容器空闲池和活动池
2. 空闲池用于存放未使用的连接，活动池用于存放正在使用的连接，活动池中的连接使用完之后要归还回空闲池
3. 当Java程序需要连接时，先判断空闲池中是否有连接，如果空闲池中有连接则取出一个连接放置到活动池供Java程序使用
4. Java程序需要连接时，如果空闲池中没有连接了，则先判断活动池的连接数是否已经达到了最大连接数，如果未达到最大连接数，则会新创建一个连接放置到活动池，供Java程序使用
5. 如果空闲池中没有连接了，活动池中的连接也已经达到了最大连接数，则不能新创建连接了，那么此时会判断是否等待超时，如果没有等待超时则需要等待活动池中的连接归还回空闲池
6. 如果等待超时了，则可以采取多种处理方式，例如:直接抛出超时异常，或者将活动池中使用最久的连接移除掉归还回空闲池以供Java程序使用

4.5 连接池的实现

4.5.1 DataSource接口

JDBC 的数据库连接池使用 javax.sql.DataSource 来表示，DataSource 只是一个接口（通常被称为数据源），所有的Java数据库连接池都需要实现该接口。该接口通常由服务器(Weblogic, WebSphere, Tomcat)提供实现，也有一些开源组织提供实现

4.5.2 常见的数据库连接池

• DBCP 是Apache提供的数据库连接池，速度相对c3p0较快，但因自身存在BUG，Hibernate3已不再提供支持
• C3P0 是一个开源组织提供的一个数据库连接池，速度相对较慢，稳定性还可以
• Proxool 是sourceforge下的一个开源项目数据库连接池，有监控连接池状态的功能，稳定性较c3p0差一点
• HikariCP 俗称光连接池,是目前速度最快的连接池
• Druid 是阿里提供的数据库连接池，据说是集DBCP 、C3P0 、Proxool 优点于一身的数据库连接池

4.5.3 Druid连接池的使用

（1）加入jar包

例如：druid-1.1.10.jar

（2）代码步骤

第一步：创建druid连接池的配置文件druid.properties文件，放置到类路径下

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test
username=root 用户名
password=123456 密码
initialSize=5 初始的连接数量
maxActive=10 最大连接数
maxWait=1000 等待时间

第二步：使用工厂模式创建DruidDataSource对象

//1. 创建一个Properties对象，让其去读取druid.properties文件
Properties properties = new Properties();
//1.1 将druid.properties配置文件转成字节输入流
//FileInputStream is = new FileInputStream("D:\\讲课资料\\尚硅谷\\210323JavaEE（深圳）\\atguigu0323\\day04_JDBC_01\\resources\\druid.properties");

//使用相对路径来将配置文件转成字节输入流,我们可以使用类加载器来读取类路径下文件
//TestDataSource.class.getClassLoader() 表示获取ClassLoader对象
InputStream is = TestDataSource.class.getClassLoader().getResourceAsStream("druid.properties");

//1.2 使用properties对象加载流
properties.load(is);

//2. 使用DruidDataSourceFactory创建Druid连接池对象
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);

第三步：使用连接池对象获取连接

Connection connection = dataSource.getConnection();

4.5.4 Druid连接池的配置参数列表

配置	缺省	说明
name		配置这个属性的意义在于，如果存在多个数据源，监控的时候可以通过名字来区分开来。 如果没有配置，将会生成一个名字，格式是：”DataSource-” + System.identityHashCode(this)
url		连接数据库的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/使用ConfigFilter
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，是组合关系，并非替换关系

第四章 封装JDBCUtil

4.1基础版

配置文件：src/jdbc.properties 或者resources/jdbc.properties

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbc_test
username=root
password=123456
initialSize=5
maxActive=10
maxWait=1000

JDBCUtil工具类：

package com.atguigu.utils;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;


public class JDBCUtil {
    private static DataSource dataSource;
    static {
        try {
            //读取配置文件，创建连接池
            InputStream inputStream = JDBCUtil.class.getClassLoader().getResourceAsStream("druid.properties");
            Properties properties = new Properties();
            properties.load(inputStream);

            //使用DruidDataSourceFactory创建连接池
            dataSource = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取连接池
     * @return
     */
    public static DataSource getDataSource(){
        return dataSource;
    }

    /**
     * 获取连接
     * @return
     */
    public static Connection getConnection(){
        try {
            return dataSource.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        }
    }

    /**
     * 归还连接的方法
     * @param connection
     */
    public static void releaseConnection(Connection connection){
        try {
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        }
    }
}

第五章 Apache的DBUtils

5.1 DBUtils的概述

commons-dbutils 是 Apache 组织提供的一个开源 JDBC工具类库，它是对JDBC的简单封装，学习成本极低，并且使用dbutils能极大简化jdbc编码的工作量，同时也不会影响程序的性能。

其中QueryRunner类封装了SQL的执行，是线程安全的。

（1）可以实现增、删、改、查、

（2）考虑了事务处理需要共用Connection。

（3）该类最主要的就是简单化了SQL查询，它与ResultSetHandler组合在一起使用可以完成大部分的数据库操作，能够大大减少编码量。

5.2 DBUtils执行增删改的SQL语句

5.2.1 API介绍

1. QueryRunner() ,创建QueryRunner对象，用于执行SQL语句
2. QueryRunner的update(Connection conn, String sql, Object… params)方法，用于执行增删改的SQL语句

5.2.2 代码实现

@Test
public void testAddUser() throws SQLException {
    //目标:往user表中添加一行数据
    //1. 创建QueryRunner
    QueryRunner queryRunner = new QueryRunner();
    //2. 执行SQL语句
    String sql = "insert into user values (null,?,?,?)";
    Connection conn = JDBCUtil.getConnection();

    int i = queryRunner.update(conn, sql, "aolafu", "123456", "狂战士");

    //关闭连接
    JDBCUtil.releaseConnection(conn);
}

@Test
public void testAddUserAnother() throws SQLException {
    //1. 创建QueryRunner,并且传入连接池对象
    QueryRunner queryRunner = new QueryRunner(JDBCUtil.getDataSource());

    //2. 执行SQL语句
    String sql = "insert into user values (null,?,?,?)";
    queryRunner.update(sql,"neisesi","123456","狗头");

    //这种方式的缺点是用不了事务
}

@Test
public void testDeleteUser() throws SQLException {
    //1. 创建QueryRunner,并且传入连接池对象
    QueryRunner queryRunner = new QueryRunner(JDBCUtil.getDataSource());
    //2. 执行SQL语句
    String sql = "delete from user where id=?";
    queryRunner.update(sql,1);
}

5.3 使用QueryRunner类实现查询

5.3.1 API介绍

1. query(String sql, ResultSetHandler rsh, Object… params) ，执行查询 select
2. ResultSetHandler结果集处理类

Handler类型	说明
ArrayHandler	将结果集中的第一条记录封装到一个Object[]数组中，数组中的每一个元素就是这条记录中的每一个字段的值
ArrayListHandler	将结果集中的每一条记录都封装到一个Object[]数组中，将这些数组在封装到List集合中。
BeanHandler	将结果集中第一条记录封装到一个指定的javaBean中。
BeanListHandler	将结果集中每一条记录封装到指定的javaBean中，将这些javaBean在封装到List集合中
ColumnListHandler	将结果集中指定的列的字段值，封装到一个List集合中
KeyedHandler	将结果集中每一条记录封装到Map<String,Object>,在将这个map集合做为另一个Map的value,另一个Map集合的key是指定的字段的值。
MapHandler	将结果集中第一条记录封装到了Map<String,Object>集合中，key就是字段名称，value就是字段值
MapListHandler	将结果集中每一条记录封装到了Map<String,Object>集合中，key就是字段名称，value就是字段值，在将这些Map封装到List集合中。
ScalarHandler	它是用于单个数据。例如select count(*) from 表。

5.3.2 代码实现

@Test
public void testFindById() throws SQLException {
    //使用DBUtils执行查询的SQL语句，将查询到的一条数据封装到User对象中
    //1. 创建QueryRunner,并且传入连接池对象
    QueryRunner queryRunner = new QueryRunner(JDBCUtil.getDataSource());
    //2. 执行SQL语句
    String sql = "select * from user where id=?";
    User user = queryRunner.query(sql, new BeanHandler<>(User.class), 2);
    System.out.println(user);
}

@Test
public void testFindAll() throws SQLException {
    //查询多条数据，封装到List<JavaBean>
    //1. 创建QueryRunner,并且传入连接池对象
    QueryRunner queryRunner = new QueryRunner(JDBCUtil.getDataSource());

    //2. 执行SQL语句
    String sql = "select * from user";
    List<User> userList = queryRunner.query(sql, new BeanListHandler<>(User.class));
    System.out.println(userList);
}

@Test
public void testFindCount() throws SQLException {
    //查询数据条数
    //1. 创建QueryRunner,并且传入连接池对象
    QueryRunner queryRunner = new QueryRunner(JDBCUtil.getDataSource());

    //2. 执行SQL语句
    String sql = "select count(id) from user";
    long count = queryRunner.query(sql, new ScalarHandler<>());

    System.out.println(count);
}

第六章 DAO

Java是面向对象语言，数据在Java中通常以对象的形式存在。

把数据库中的记录<---->Java的对象对应起来。

我们把访问数据库的代码封装起来，这些类称为DAO（Data Access Object）。它相当于是一个数据访问接口，夹在业务逻辑与数据库资源中间。

6.1、部门类和DAO接口

部门类1.0版（有mysql<–>Java类型问题）

package com.atguigu.bean;

public class Department {
    private int did;
    private String dname;
    private String description;

    public Department(int did, String dname, String description) {
        this.did = did;
        this.dname = dname;
        this.description = description;
    }

    public Department() {
    }

    public int getDid() {
        return did;
    }

    public void setDid(int did) {
        this.did = did;
    }

    public String getDname() {
        return dname;
    }

    public void setDname(String dname) {
        this.dname = dname;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    @Override
    public String toString() {
        return "Department{" +
                "did=" + did +
                ", dname='" + dname + '\'' +
                ", description='" + description + '\'' +
                '}';
    }
}

部门类2.0版

package com.atguigu.bean;

public class Department {
    private Integer did;//把int修改为Integer
    private String dname;
    private String description;

    public Department(String dname, String description) {
        this.dname = dname;
        this.description = description;
    }

    public Department() {
    }

    public Integer getDid() {
        return did;
    }

    public void setDid(Integer did) {
        this.did = did;
    }

    public String getDname() {
        return dname;
    }

    public void setDname(String dname) {
        this.dname = dname;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    @Override
    public String toString() {
        return "Department{" +
                "did=" + did +
                ", dname='" + dname + '\'' +
                ", description='" + description + '\'' +
                '}';
    }
}

部门DAO接口

package com.atguigu.dao;

import com.atguigu.bean.Department;

import java.util.List;

public interface DepartmentDAO {
    //添加一个部门
    boolean addDepartment(Department department);
    //修改部分信息
    boolean updateDepartment(Department department);
    //根据部门编号，查询一个部门对象
    Department getByDid(int did);
    //查询所有部门对象
    List<Department> getAll();
    //根据主键did删除一个部门
    boolean removeByDid(int did);
    //其他方法略
}

6.2、员工类和DAO接口

员工类1.0版（有mysql<–>Java类型问题）

package com.atguigu.bean;

import java.util.Date;

public class Employee {
    private int eid;
    private String ename;
    private double salary;
    private double commissionPct;
    private Date birthday;
    private char gender;
    private String tel;
    private String email;
    private String address;
    private String workPlace;
    private Date hiredate;
    private int jobId;
    private int mid;
    private int did;

    public int getEid() {
        return eid;
    }

    public void setEid(int eid) {
        this.eid = eid;
    }

    public String getEname() {
        return ename;
    }

    public void setEname(String ename) {
        this.ename = ename;
    }

    public double getSalary() {
        return salary;
    }

    public void setSalary(double salary) {
        this.salary = salary;
    }

    public double getCommissionPct() {
        return commissionPct;
    }

    public void setCommissionPct(double commissionPct) {
        this.commissionPct = commissionPct;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    public char getGender() {
        return gender;
    }

    public void setGender(char gender) {
        this.gender = gender;
    }

    public String getTel() {
        return tel;
    }

    public void setTel(String tel) {
        this.tel = tel;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public String getWorkPlace() {
        return workPlace;
    }

    public void setWorkPlace(String workPlace) {
        this.workPlace = workPlace;
    }

    public Date getHiredate() {
        return hiredate;
    }

    public void setHiredate(Date hiredate) {
        this.hiredate = hiredate;
    }

    public int getJobId() {
        return jobId;
    }

    public void setJobId(int jobId) {
        this.jobId = jobId;
    }

    public int getMid() {
        return mid;
    }

    public void setMid(int mid) {
        this.mid = mid;
    }

    public int getDid() {
        return did;
    }

    public void setDid(int did) {
        this.did = did;
    }

    @Override
    public String toString() {
        return "Employee{" +
                "eid=" + eid +
                ", ename='" + ename + '\'' +
                ", salary=" + salary +
                ", commissionPct=" + commissionPct +
                ", birthday=" + birthday +
                ", gender=" + gender +
                ", tel='" + tel + '\'' +
                ", email='" + email + '\'' +
                ", address='" + address + '\'' +
                ", workPlace='" + workPlace + '\'' +
                ", hiredate=" + hiredate +
                ", jobId=" + jobId +
                ", mid=" + mid +
                ", did=" + did +
                '}';
    }
}

员工类2.0版

package com.atguigu.bean;

import java.math.BigDecimal;
import java.util.Date;

public class Employee {
    private Integer eid;
    private String ename;
    private Double salary;
    private Double commissionPct;
    private Date birthday;
    private String gender;
    private String tel;
    private String email;
    private String address;
    private String workPlace;
    private Date hiredate;
    private Integer jobId;
    private Integer mid;
    private Integer did;

    public Integer getEid() {
        return eid;
    }

    public void setEid(Integer eid) {
        this.eid = eid;
    }

    public String getEname() {
        return ename;
    }

    public void setEname(String ename) {
        this.ename = ename;
    }

    public Double getSalary() {
        return salary;
    }

    public void setSalary(Double salary) {
        this.salary = salary;
    }

    public BigDecimal getCommissionPct() {
        return commissionPct;
    }

    public void setCommissionPct(BigDecimal commissionPct) {
        this.commissionPct = commissionPct;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }

    public String getTel() {
        return tel;
    }

    public void setTel(String tel) {
        this.tel = tel;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public String getWorkPlace() {
        return workPlace;
    }

    public void setWorkPlace(String workPlace) {
        this.workPlace = workPlace;
    }

    public Date getHiredate() {
        return hiredate;
    }

    public void setHiredate(Date hiredate) {
        this.hiredate = hiredate;
    }

    public Integer getJobId() {
        return jobId;
    }

    public void setJobId(Integer jobId) {
        this.jobId = jobId;
    }

    public Integer getMid() {
        return mid;
    }

    public void setMid(Integer mid) {
        this.mid = mid;
    }

    public Integer getDid() {
        return did;
    }

    public void setDid(Integer did) {
        this.did = did;
    }

    @Override
    public String toString() {
        return "Employee{" +
                "eid=" + eid +
                ", ename='" + ename + '\'' +
                ", salary=" + salary +
                ", commissionPct=" + commissionPct +
                ", birthday=" + birthday +
                ", gender=" + gender +
                ", tel='" + tel + '\'' +
                ", email='" + email + '\'' +
                ", address='" + address + '\'' +
                ", workPlace='" + workPlace + '\'' +
                ", hiredate=" + hiredate +
                ", jobId=" + jobId +
                ", mid=" + mid +
                ", did=" + did +
                '}';
    }
}

员工DAO接口

package com.atguigu.dao;

import com.atguigu.bean.Employee;

import java.util.List;

public interface EmployeeDAO {
    //添加一个员工
    boolean addEmployee(Employee employee);
    //根据员工编号查询一个员工对象
    Employee getByEid(int eid);
    //根据员工编号删除一个员工对象，即删除一条记录
    boolean removeEmployee(int eid);
    //查询所有的员工对象
    List<Employee> getAll();
    //其他方法略
}

6.3.封装BaseDAOImpl类

public abstract class BaseDAOImpl {
    private QueryRunner queryRunner = new QueryRunner();

    /**
     * 通用的增删改的方法
     * @param sql String 要执行的sql
     * @param args Object... 如果sql中有？，就传入对应个数的？要设置值
     * @return int 执行的结果
     */
    protected int update(String sql,Object... args) {
        try {
            return queryRunner.update(JDBCTools.getConnection(),sql,args);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 查询单个对象的方法
     * @param clazz Class 记录对应的类类型
     * @param sql String 查询语句
     * @param args Object... 如果sql中有？，即根据条件查询，可以设置？的值
     * @param <T> 泛型方法声明的泛型类型
     * @return  T 一个对象
     */
    protected <T> T getBean(Class<T> clazz, String sql, Object... args){
        return getList(clazz,sql,args).get(0);
    }

    /**
     * 通用查询多个对象的方法
     * @param clazz Class 记录对应的类类型
     * @param sql String 查询语句
     * @param args Object... 如果sql中有？，即根据条件查询，可以设置？的值
     * @param <T> 泛型方法声明的泛型类型
     * @return List<T> 把多个对象放到了List集合
     */
    protected <T> List<T> getList(Class<T> clazz, String sql, Object... args){
        try {
            return queryRunner.query(JDBCTools.getConnection(),sql,new BeanListHandler<T>(clazz),args);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    protected Object getValue(String sql,Object... args){
        try {
            return queryRunner.query(JDBCTools.getConnection(),sql,new ScalarHandler<>(),args);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

}

6.4 DAO实现类

Data Access Object

6.4.1部门DAO实现类

sql

insert into t_department values(null,?,?);
select * from t_department;
update t_department set dname=?,description=? where did=?;
select * from t_department where did = ?;
delete from t_department where did = ?;

部门DAO实现类

package com.atguigu.dao.impl;

import com.atguigu.bean.Department;
import com.atguigu.dao.DepartmentDAO;

import java.util.List;

public class DepartmentDAOImpl extends BaseDAOImpl implements DepartmentDAO {
    @Override
    public boolean addDepartment(Department department) {
        String sql = "insert into t_department values(null,?,?)";//第一个null是因为部门表的第一个字段did是自增的
        return update(sql,department.getDname(),department.getDescription())>0;
    }

    @Override
    public boolean updateDepartment(Department department) {
        String sql = "update t_department set dname=? , description=? where did =?";//根据did可以定位到一条记录，主键
        return update(sql,department.getDname(),department.getDescription(),department.getDid())>0;
    }

    @Override
    public Department getByDid(int did) {
        String sql ="select * from t_department where did =?";
        return getBean(Department.class,sql,did);
    }

    @Override
    public List<Department> getAll() {
        String sql = "select * from t_department";
        return getList(Department.class,sql);
    }

    @Override
    public boolean removeByDid(int did) {
        String sql = "delete from t_department where did = ?";
        return update(sql,did)>0;
    }
}

测试部门DAO实现类

package com.atguigu.test;

import com.atguigu.bean.Department;
import com.atguigu.dao.impl.DepartmentDAOImpl;
import org.junit.Test;

import java.util.List;

public class TestDepartmentDAOImpl {
    @Test
    public void test01(){
        DepartmentDAOImpl dao = new DepartmentDAOImpl();
        List<Department> all = dao.getAll();
        all.forEach(System.out::println);
    }

    @Test
    public void test02(){
        Department dept = new Department(0,"测试部门3","测试数据");//因为did是自增长’
        DepartmentDAOImpl dao = new DepartmentDAOImpl();
        System.out.println(dao.addDepartment(dept));
    }

    @Test
    public void test03(){
        //打算修改did为1的部门
        DepartmentDAOImpl dao = new DepartmentDAOImpl();
        Department dept = dao.getByDid(1);
        System.out.println("原来的数据：" + dept);

        //这里就简化了，不从键盘输入了
        dept.setDescription("研发部门的描述信息xxxx");
        //这里没有修改部门的名称

        System.out.println(dao.updateDepartment(dept));

        System.out.println("-----------------");
        List<Department> all = dao.getAll();
        all.forEach(System.out::println);
    }

    @Test
    public void test04(){
        //删除3008部门
        DepartmentDAOImpl dao = new DepartmentDAOImpl();
        System.out.println(dao.removeByDid(3008));
    }
}

6.4.2、员工DAO实现类

sql

INSERT INTO t_employee(`eid`,`ename`,`salary`,`commission_pct`,`birthday`,`gender`,`tel`,`email`,`address`,`work_place`,`hiredate`,`job_id`,`mid`,`did`)VALUES(NULL,?,?,?,?,?,?,?,?,?,?,?,?,?);

delete from t_employee where eid = ?;

SELECT * FROM t_employee;

SELECT `eid`,`ename`,`salary`,`commission_pct` AS commissionPct,`birthday`,`gender`,`tel`,`email`,`address`,`work_place` AS workPlace,`hiredate`,`job_id` AS jobId,`mid`,`did` FROM t_employee;


SELECT `eid`,`ename`,`salary`,`commission_pct` AS commissionPct,`birthday`,`gender`,`tel`,`email`,`address`,`work_place` AS workPlace,`hiredate`,`job_id` AS jobId,`mid`,`did` FROM t_employee where eid=?

员工DAO实现类

package com.atguigu.dao.impl;

import com.atguigu.bean.Employee;
import com.atguigu.dao.EmployeeDAO;

import java.util.List;

public class EmployeeDAOImpl extends BaseDAOImpl implements EmployeeDAO {
    @Override
    public boolean addEmployee(Employee employee) {
        String sql = "insert into t_employee(`eid`,`ename`,`salary`,`commission_pct`,`birthday`," +
                "`gender`,`tel`,`email`,`address`,`work_place`,`hiredate`,`job_id`,`mid`,`did`)" +
                "values(null,?,?,?,?,?,?,?,?,?,?,?,?,?)";//null表示eid是自增的
        return update(sql,employee.getEname(),
                employee.getSalary(),
                employee.getCommissionPct(),
                employee.getBirthday(),
                employee.getGender(),
                employee.getTel(),
                employee.getEmail(),
                employee.getAddress(),
                employee.getWorkPlace(),
                employee.getHiredate(),
                employee.getJobId(),
                employee.getMid(),
                employee.getDid()
        )>0;
    }

    @Override
    public Employee getByEid(int eid) {
//        String sql = "select * from t_employee where eid = ?";
        String sql = "SELECT `eid`,`ename`,`salary`,`commission_pct` AS commissionPct,`birthday`,`gender`,`tel`,`email`,`address`,`work_place` AS workPlace,`hiredate`,`job_id` AS jobId,`mid`,`did` FROM t_employee where eid = ?";
        return getBean(Employee.class,sql,eid);
    }

    @Override
    public boolean removeEmployee(int eid) {
        String sql = "delete from t_employee where eid = ?";
        return update(sql,eid)>0;
    }

    @Override
    public List<Employee> getAll() {
//        String sql = "select * from t_employee";
        String sql = "SELECT `eid`,`ename`,`salary`,`commission_pct` AS commissionPct,`birthday`,`gender`,`tel`,`email`,`address`,`work_place` AS workPlace,`hiredate`,`job_id` AS jobId,`mid`,`did` FROM t_employee";
        return getList(Employee.class,sql);
    }
}

测试员工DAO实现类

package com.atguigu.test;

import com.atguigu.bean.Employee;
import com.atguigu.dao.impl.EmployeeDAOImpl;
import org.junit.Test;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Scanner;

public class TestEmployeeDAOImpl {
    @Test
    public void test01(){
        //先测试查询所有员工对象
        EmployeeDAOImpl dao = new EmployeeDAOImpl();
        List<Employee> all = dao.getAll();
        all.forEach(System.out::println);
        /*
        （1）java.lang.RuntimeException: java.lang.NoSuchFieldException: commission_pct
            原因：mysql的atguigu库，t_employee表中 奖金比例字段名是  commission_pct
        而Javabean Employee类中奖金比例是commissionPct
        如何解决？
        编写特殊的sql，在select语句中给表的字段取别名，
            select commission_pct as commissionPct

        公共的查询方法getList和getBean方法中，反射获取Field对象是根据ResultSet结果集中的表头来确定属性名。
        String columnName = metaData.getColumnName(i);
        换成
        String columnName = metaData.getColumnLabel(i);

        （2）java.lang.RuntimeException: java.lang.IllegalArgumentException:
        Can not set double field com.atguigu.bean.Employee.commissionPct to java.math.BigDecimal。
        原因：mysql的atguigu库，t_employee表中 奖金比例字段数据类型是decimal，对应Java的java.math.BigDecimal，不是double。

        （3）java.lang.RuntimeException: java.lang.IllegalArgumentException:
        Can not set char field com.atguigu.bean.Employee.gender to java.lang.String
        原因：mysql的atguigu库，t_employee表中 gender是enum('男','女')，对应Java的String类型
        其实就算是  t_employee表中 gender是char类型，也是对应Java的String类型，因为mysql的char可以存储多个字符char(M)，必须用String类型

        （4）java.lang.RuntimeException: java.lang.IllegalArgumentException:
        Can not set int field com.atguigu.bean.Employee.jobId to null value
        原因：mysql的atguigu库，t_employee表中 job_id是int类型，但是mysql中的int类型可以设置NULL中，
        而Java中的int类型是不能设置为null，所以的mysql中的int要用Java的Integer对应

        类似的还有：double等
         */
    }

    @Test
    public void test02(){
        //测试查询一个员工的方法
        EmployeeDAOImpl dao = new EmployeeDAOImpl();

        Scanner input = new Scanner(System.in);
        System.out.print("请输入要查询的员工的编号：");
        int eid = input.nextInt();
        Employee employee = dao.getByEid(eid);
        System.out.println(employee);

        input.close();
    }

    @Test
    public void test03(){
        //测试删除一个员工的方法
        EmployeeDAOImpl dao = new EmployeeDAOImpl();

        Scanner input = new Scanner(System.in);
        System.out.print("请输入要删除的员工的编号：");
        int eid = input.nextInt();

        System.out.println(dao.removeEmployee(eid));
        input.close();
    }

    @Test
    public void test04()throws Exception{
        //测试添加员工方法
        Scanner input = new Scanner(System.in);
        System.out.print("请输入姓名：");
        String ename = input.next();//李四

        System.out.print("请输入薪资：");
        double salary = input.nextDouble();//15000

        System.out.print("请输入出生日期：");
        String birthday = input.next();//1990-1-1
        SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd");

        System.out.print("请输入性别：");
        String gender = input.next();

        System.out.print("请输入手机号码：");
        String tel = input.next();//13578595685

        System.out.print("请输入邮箱：");
        String email = input.next();//zhangsan@atguigu.com

        EmployeeDAOImpl dao = new EmployeeDAOImpl();
        //sf.parse(birthday)把字符串的日期转为Date类型的日期
        Employee emp = new Employee(ename,salary,sf.parse(birthday),gender,tel,email,"北京",new Date());

        System.out.println(dao.addEmployee(emp));

        input.close();
    }
}

第七章 JDBC工具类事务问题

ThreadLocal

演示事务问题

需求：
    （1）修改did=1的部门简介为“部门1的简介”
    （2）修改did=2的部门名称  = 部门1的名称（故意制造错误，因为t_department表的dname有唯一键元素，这条修改会失败）
    要求（1）（2）同时成功或同时失败，即在一个事务中完成。

package com.atguigu.test;

import com.atguigu.bean.Department;
import com.atguigu.dao.impl.DepartmentDAOImpl;
import com.atguigu.tools.JDBCTools;
import org.junit.Test;

import java.sql.Connection;

public class TestTransaction {
    @Test
    public void test01()throws Exception{
        DepartmentDAOImpl dao = new DepartmentDAOImpl();
       

        try {
            Department dept1 = dao.getByDid(1);
            dept1.setDescription("部门1的简介");
            System.out.println("第一条记录修改：" +dao.updateDepartment(dept1));

            Department dept2 = dao.getByDid(2);
            dept2.setDname(dept1.getDname());//故意修改为和部门1的名称一样，制造异常
            System.out.println("第一条记录修改：" +dao.updateDepartment(dept2));

            System.out.println("都成功");
           
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("失败");
           
        }

        
    }
}

使用ThreadLocal类

其中ThreadLocal的介绍如下：

JDK 1.2的版本中就提供java.lang.ThreadLocal，为解决多线程程序的并发问题提供了一种新的思路。使用这个工具类可以很简洁地编写出优美的多线程程序。通常用来在在多线程中管理共享数据库连接、Session等

ThreadLocal用于保存某个线程共享变量，原因是在Java中，每一个线程对象中都有一个ThreadLocalMap<ThreadLocal, Object>，其key就是一个ThreadLocal，而Object即为该线程的共享变量。而这个map是通过ThreadLocal的set和get方法操作的。对于同一个static ThreadLocal，不同线程只能从中get，set，remove自己的变量，而不会影响其他线程的变量。

1、ThreadLocal对象.get: 获取ThreadLocal中当前线程共享变量的值。

2、ThreadLocal对象.set: 设置ThreadLocal中当前线程共享变量的值。

3、ThreadLocal对象.remove: 移除ThreadLocal中当前线程共享变量的值。

线程（事务）结束后别忘了移除共享对象。

public class JDBCUtils {
    static DataSource dataSource;

    static ThreadLocal<Connection> local = new ThreadLocal<>();

    static {
        Properties properties = new Properties();
        try {
            properties.load(JDBCUtils.class.getClassLoader().getResourceAsStream("druid.properties"));

            dataSource = DruidDataSourceFactory.createDataSource(properties);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() throws SQLException {
        Connection connection = local.get();
        if (connection == null) {
            connection = dataSource.getConnection();
            local.set(connection);
        }
        return local.get();
    }

    public static void commit() throws SQLException {

        Connection connection = getConnection();

        connection.commit();
    }

    public static void startTransaction() throws SQLException {

        Connection connection = getConnection();

        connection.setAutoCommit(false);

    }

    public static void rollback() throws SQLException {

        Connection connection = getConnection();

        connection.rollback();
    }

    public static void closeResourse(Connection connection) {
        if (connection != null) {
            try {
                connection.setAutoCommit(true);
                local.remove();
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

经典错误

1、jar包版本不兼容

Exception in thread "main" com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server.
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:408)
	at com.mysql.jdbc.Util.getInstance(Util.java:383)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1023)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:997)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:983)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:928)
	at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2576)
	at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2309)
	at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:834)
	at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:46)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:408)
	at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:419)
	at com.mysql.jdbc.NonRegisteringDriver.connect(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.
Exception in thread "main" java.sql.SQLException: The server time zone value '�й���׼ʱ��' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:73)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:76)
	at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:835)
	at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:455)
	at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:240)
	at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:199)
	at java.sql.DriverManager.getConnection(DriverManager.java:664)
	at java.sql.DriverManager.getConnection(DriverManager.java:247)
.java:344)
	at java.sql.DriverManager.getConnection(DriverManager.java:664)
	at java.sql.DriverManager.getConnection(DriverManager.java:247)

看异常好像是无事务连接异常，无法创建连接。将MySQL驱动改为了最新的8.0版本的MySQL驱动。显示那个驱动类已经过时了，新的驱动类是“com.mysql.cj.jdbc.Driver”，而不是“com.mysql.jdbc.Driver”了，并且还说我没有配置时区，查了一下，原来从JDBC6.0开始驱动类使用了新的，并且url中必须要设置时区，否侧会报错。

第一步：使用最新的MySQL驱动jar包。
第二步：把驱动的类名改为:
static String driver="com.mysql.cj.jdbc.Driver";
第三步：在访问mysql的url后加入时区设置：
static String url="jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF8&serverTimezone=UTC"

MySQL和JDBC阶段必须掌握的内容

1. mysql服务器和sqlyog的安装和使用
2. 会使用sqlyog对数据库、表进行创建、修改、删除
3. 对于单表的数据的增删改查语句要非常熟练
4. 理解多表关系，理解外键，会使用sqlyog创建和删除外键，设置外键的等级
5. 难点:熟练使用内连接查询、外连接查询、子查询
6. 会使用连接池+DBUtils，执行sql语句:增删改的sql语句，查询的sql语句
7. 会使用JDBC执行添加的SQL语句，获取自增长的主键