JDBC与数据库交互详解：连接、事务、DAO实现-优快云博客

本文链接：https://blog.youkuaiyun.com/m0_61922004/article/details/121478431

一、概述

1.1 简介

JDBC(Java Database Connectivity)是一个独立于特定数据库管理系统、通用的SQL数据库存取和操作的公共接口（一组API），定义了用来访问数据库的标准Java类库，（java.sql,javax.sql）使用这些类库可以以一种标准的方法、方便地访问数据库资源。
JDBC为访问不同的数据库提供了一种统一的途径，为开发者屏蔽了一些细节问题。
JDBC的目标是使Java程序员使用JDBC可以连接任何提供了JDBC驱动程序的数据库系统，程序员无需对特定的数据库系统的特点有过多的了解，从而大大简化和加快了开发过程

1.2 JDBC体系结构

JDBC接口（API）包括两个层次：

面向应用的API：Java API，抽象接口，供应用程序开发人员使用（连接数据库，执行SQL语句，获得结果）。
面向数据库的API：Java Driver API，供开发商开发数据库驱动程序用。

1.3 编写步骤

1.4 连接数据库方式举例（依次递进到最终版）

//8.0.26
import org.junit.Test;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

public class ConnectionTest {
    //方式1
    @org.junit.Test
    public void testConnection1() throws SQLException {
        Driver driver = new com.mysql.cj.jdbc.Driver();//获取Driver的实现类对象
        String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8";
        Properties info = new Properties();//封装用户名和密码
        info.setProperty("user","root");
        info.setProperty("password","12345");

        Connection connect = driver.connect(url, info);
        System.out.println(connect);
    }

    //方式2：对方式1的迭代
    //不出现第三方API，使程序具有更好的可移植性。如：new com.mysql.cj.jdbc.Driver();
    @Test
    public void testConnection2() throws Exception {
        //获取Driver的实现类对象【使用反射】
        Class aClass = Class.forName("com.mysql.cj.jdbc.Driver");
        Driver driver = (Driver) aClass.getDeclaredConstructor().newInstance();

        String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8";
        Properties info = new Properties();//封装用户名和密码
        info.setProperty("user","root");
        info.setProperty("password","12345");

        Connection connect = driver.connect(url, info);
        System.out.println(connect);
    }

    //方式3：使用DriverManager替换Driver
    @Test
    public void testConnection3() throws Exception {
        //获取Driver的实现类对象【使用反射】
        Class aClass = Class.forName("com.mysql.cj.jdbc.Driver");
        Driver driver = (Driver) aClass.getDeclaredConstructor().newInstance();

        String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8";
        String user = "root";
        String password = "12345";
        //注册驱动
        DriverManager.registerDriver(driver);
        //获取连接
        Connection connect = DriverManager.getConnection(url,user,password);
        System.out.println(connect);
    }

    //方式4：优化3，不用显式地注册驱动
    @Test
    public void testConnection4() throws Exception {
        String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8";
        String user = "root";
        String password = "12345";

        //获取Driver的实现类对象【使用反射】
        //加载Driver
        Class.forName("com.mysql.cj.jdbc.Driver");//静态代码块，自动注册驱动
//        Driver driver = (Driver) aClass.getDeclaredConstructor().newInstance();
//        //注册驱动
//        DriverManager.registerDriver(driver);
        //获取连接
        Connection connect = DriverManager.getConnection(url,user,password);
        System.out.println(connect);
    }

    //方式5【最终版】：将数据库连接需要的4个信息放在配置文件中，通过读取配置文件获取连接
    //实现数据和代码分离，解耦！
    //修改连接信息后避免程序重新打包
    @Test
    public void testConnection5() throws Exception {
        //读取配置文件中的信息
        InputStream is = ConnectionTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
        Properties pros = new Properties();
        pros.load(is);
        String user = pros.getProperty("user");
        String password = pros.getProperty("password");
        String url = pros.getProperty("url");
        String driverClass = pros.getProperty("driverClass");

        //加载驱动
        Class.forName(driverClass);

        //获取连接
        Connection connect = DriverManager.getConnection(url,user,password);
        System.out.println(connect);
    }
}

jdbc.properties文件内容：
user=root
password=12345
url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8
driverClass=com.mysql.cj.jdbc.Driver

二、使用PreparedStatement实现CRUD操作

2.1 操作和访问数据库

数据库连接被用于向数据库服务器发送命令和 SQL 语句，并接受数据库服务器返回的结果。其实一个数据库连接就是一个Socket连接。
在 java.sql 包中有 3 个接口分别定义了对数据库的调用的不同方式：

Statement：用于执行静态 SQL 语句并返回它所生成结果的对象。
PrepatedStatement：SQL 语句被预编译并存储在此对象中，可以使用此对象多次高效地执行该语句。
CallableStatement：用于执行 SQL 存储过程

2.2 使用Statement操作数据表的弊端

1. 通过调用 Connection 对象的 createStatement() 方法创建该对象。该对象用于执行静态的 SQL 语句，并且返回执行结果。
2. Statement 接口中定义了下列方法用于执行 SQL 语句

int excuteUpdate(String sql)：执行更新操作INSERT、UPDATE、DELETE
ResultSet executeQuery(String sql)：执行查询操作SELECT

3. 使用Statement操作数据表存在弊端：

存在拼串操作，繁琐
存在SQL注入问题

SQL 注入是利用某些系统没有对用户输入的数据进行充分的检查，而在用户输入数据中注入非法的 SQL 语句段或命令(如：SELECT user, password FROM user_table WHERE user='a' OR 1 = ' AND password = ' OR '1' ='1') ，从而利用系统的 SQL 引擎完成恶意行为的做法。
对于 Java 而言，要防范 SQL 注入，只要用 PreparedStatement(从Statement扩展而来) 取代 Statement 就可以了。

4. 代码演示

public class User {

	private String user;
	private String password;

	public User() {
	}

	public User(String user, String password) {
		super();
		this.user = user;
		this.password = password;
	}

	@Override
	public String toString() {
		return "User [user=" + user + ", password=" + password + "]";
	}

	public String getUser() {
		return user;
	}

	public void setUser(String user) {
		this.user = user;
	}

	public String getPassword() {
		return password;
	}

	public void setPassword(String password) {
		this.password = password;
	}

}



import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import java.util.Scanner;


public class StatementTest {
	public static void main(String[] args) {
		testLogin();
	}

	// 使用Statement的弊端：需要拼写sql语句，并且存在SQL注入的问题
	public static void testLogin() {
		Scanner scan = new Scanner(System.in);

		System.out.print("用户名：");
		String userName = scan.nextLine();
		System.out.print("密   码：");
		String password = scan.nextLine();

		// SELECT user,password FROM user_table WHERE USER = '1' or ' AND PASSWORD = '
		// ='1' or '1' = '1';
		String sql = "SELECT user,password FROM user_table WHERE USER = '" + userName + "' AND PASSWORD = '"
				+ password + "'";
		User user = get(sql, User.class);
		if (user != null) {
			System.out.println("登录成功!");
		} else {
			System.out.println("用户名或密码错误！");
		}
	}

	// 使用Statement实现对数据表的查询操作
	public static <T> T get(String sql, Class<T> clazz) {
		T t = null;

		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		try {
			// 1.加载配置文件
			InputStream is = StatementTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
			Properties pros = new Properties();
			pros.load(is);

			// 2.读取配置信息
			String user = pros.getProperty("user");
			String password = pros.getProperty("password");
			String url = pros.getProperty("url");
			String driverClass = pros.getProperty("driverClass");

			// 3.加载驱动
			Class.forName(driverClass);

			// 4.获取连接
			conn = DriverManager.getConnection(url, user, password);

			st = conn.createStatement();

			rs = st.executeQuery(sql);

			// 获取结果集的元数据
			ResultSetMetaData rsmd = rs.getMetaData();

			// 获取结果集的列数
			int columnCount = rsmd.getColumnCount();

			if (rs.next()) {
				t = clazz.newInstance();
				for (int i = 0; i < columnCount; i++) {
					// //1. 获取列的名称
					// String columnName = rsmd.getColumnName(i+1);

					// 1. 获取列的别名
					String columnName = rsmd.getColumnLabel(i + 1);

					// 2. 根据列名获取对应数据表中的数据
					Object columnVal = rs.getObject(columnName);

					// 3. 将数据表中得到的数据，封装进对象
					Field field = clazz.getDeclaredField(columnName);
					field.setAccessible(true);
					field.set(t, columnVal);
				}
				return t;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			// 关闭资源
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (st != null) {
				try {
					st.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return null;
	}
}

2.3 PreparedStatemrnt的使用

2.3.1 PreparedStatement介绍

可以通过调用 Connection 对象的 preparedStatement(String sql) 方法获取 PreparedStatement 对象
PreparedStatement 接口是 Statement 的子接口，它表示一条预编译过的 SQL 语句
PreparedStatement 对象所代表的 SQL 语句中的参数用问号(?)来表示，调用 PreparedStatement 对象的setXxx() 方法来设置这些参数. setXxx() 方法有两个参数，第一个参数是要设置的 SQL 语句中的参数的索引(从 1开始)，第二个是设置的 SQL 语句中的参数的值

2.3.2 PreparedStatement VS Statement

1. 代码的可读性和可维护性。
2. PreparedStatement 能最大可能提高性能

DBServer会对预编译语句提供性能优化。因为预编译语句有可能被重复调用，所以语句在被DBServer的编译器编译后的执行代码被缓存下来，那么下次调用时只要是相同的预编译语句就不需要编译，只要将参数直接传入编译过的语句执行代码中就会得到执行。
在statement语句中,即使是相同操作但因为数据内容不一样,所以整个语句本身不能匹配,没有缓存语句的意义.事实是没有数据库会对普通语句编译后的执行代码缓存。这样每执行一次都要对传入的语句编译一次。
(语法检查，语义检查，翻译成二进制命令，缓存)

3. PreparedStatement 可以防止 SQL 注入

2.3.3 Java与SQL对应数据类型转换表

2.3.4 使用PreparedStatement实现增、删、改操作

（version1.0 未考虑数据库事务）

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class JDBCUtils {
    /**
     * 获取数据库连接
     * @return
     * @throws Exception
     */
    public static Connection getConnection() throws Exception {
        //读取配置文件中的信息
        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
        Properties pros = new Properties();
        pros.load(is);
        String user = pros.getProperty("user");
        String password = pros.getProperty("password");
        String url = pros.getProperty("url");
        String driverClass = pros.getProperty("driverClass");

        //加载驱动
        Class.forName(driverClass);

        //获取连接
        Connection connect = DriverManager.getConnection(url,user,password);
        return connect;
    }

    /**
     * 关闭资源
     * @param conn
     * @param ps   PreparedStatement是Statement的子接口
     */
    public static void closeResource(Connection conn, Statement ps){
        try {
            if (ps != null) {
                ps.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if (conn != null) {
                conn.close();
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    //关闭资源，方法重载，查询需要关闭结果集ResultSet
    public static void closeResource(Connection conn, Statement ps, ResultSet rs){
        try {
            if (ps != null) {
                ps.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if (conn != null) {
                conn.close();
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if (rs != null) {
                rs.close();
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}




import org.junit.Test;
import tx.connection.ConnectionTest;
import tx.util.JDBCUtils;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Properties;

public class PreparedStatementUpdateTest {
    //通用增删改操作
    //sql中占位符的个数应该与可变形参的长度一致
    public void update(String sql,Object ...args) {
        Connection connection = null;
        PreparedStatement ps = null;
        try {
        //1.获取数据库连接
        connection = JDBCUtils.getConnection();
        //2.预编译SQL语句，返回PreparedStatement的实例
        ps = connection.prepareStatement(sql);
        //3.填充占位符
        for (int i = 0; i < args.length; i++) {
        ps.setObject(i+1,args[i]);
        }
        //4.执行
        ps.execute();
        } catch (Exception e) {
        e.printStackTrace();
        } finally {
        //5.关闭资源
        JDBCUtils.closeResource(connection, ps);
        }
    }



    @Test
    public void testCommonUpdate(){
        String sql = "delete from customers where id = ?";
        update(sql,3);
        //order是关键字，要加``
        String sql1 = "update `order` set order_name = ? where order_id = ?";
        update(sql1,"QQ","4");
    }
}

2.3.5 使用PreparedStatement实现查询操作

import org.junit.Test;
import tx.bean.Customers;
import tx.bean.Order;
import tx.util.JDBCUtils;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.List;

//针对不同表的通用查询，返回集合
public class PreparedStatementQueryTest {
    @Test
    public void testGetList(){
        String sql = "select order_id orderId,order_name orderName,order_date orderDate from `order` where order_id < ?";
        //getList(Order.class,sql,4).forEach(System.out::println);  lambda表达式方法引用，实际就是增强for
        List<Order> list = getList(Order.class,sql,4);
        for (Order order : list) {
            System.out.println(order);
        }
        String sql1 = "select id,name,email,birth from customers where name = ? or name = ?";
        getList(Customers.class,sql1,"周涛","zt").forEach(System.out::println);
    }

    public <T> List<T> getList(Class<T> clazz, String sql, Object... args){
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            connection = JDBCUtils.getConnection();
            ps = connection.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            rs = ps.executeQuery();
            //获取结果集的元数据
            ResultSetMetaData rsmd = rs.getMetaData();
            //通过ResultSetMetaData获取结果集中的列数
            int columnCount = rsmd.getColumnCount();
            //创建集合对象
            ArrayList<T> list = new ArrayList<>();
            while (rs.next()) {
                T t = clazz.getDeclaredConstructor().newInstance();
                //处理结果集一行数据中的每一个列:给t对象指定的属性赋值
                for (int i = 0; i < columnCount; i++) {
                    Object columnValue = rs.getObject(i + 1);
                    //获取每个列的列名
                    String columnLabel = rsmd.getColumnLabel(i + 1);
                    //【通过反射】给cus对象指定的order属性赋值为columnValue
                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t, columnValue);//给order这个对象的columnLabel属性赋值columnValue
                }
                list.add(t);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(connection, ps, rs);
        }
        return null;
    }
}

2.3.6 防止SQL注入

import tx.util.JDBCUtils;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.Scanner;

public class PreparedStatementTest {
    public static void main(String[] args) {
        testLogin();
    }

    // 使用PreparedStatement：不用拼串，预编译sql语句,防止SQL注入的问题，
    //                          可以操作Blob数据，
    //                          可以实现更高效的批量操作，
    public static void testLogin() {
        Scanner scan = new Scanner(System.in);

        System.out.print("用户名：");
        String userName = scan.nextLine();
        System.out.print("密   码：");
        String password = scan.nextLine();

        // SELECT user,password FROM user_table WHERE USER = '1' or ' AND PASSWORD = '
        // ='1' or '1' = '1';
        String sql = "SELECT user,password FROM user_table WHERE USER = ? AND PASSWORD = ?";
        User user = getInstance(User.class, sql, userName, password);
        if (user != null) {
            System.out.println("登录成功!");
        } else {
            System.out.println("用户名或密码错误！");
        }
    }

    //针对不同表的通用查询，返回一条记录
    public static <T> T getInstance(Class<T> clazz, String sql, Object... args) {
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            connection = JDBCUtils.getConnection();
            ps = connection.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            rs = ps.executeQuery();
            //获取结果集的元数据
            ResultSetMetaData rsmd = rs.getMetaData();
            //通过ResultSetMetaData获取结果集中的列数
            int columnCount = rsmd.getColumnCount();
            if (rs.next()) {
                T t = clazz.getDeclaredConstructor().newInstance();
                //处理结果集一行数据中的每一个列
                for (int i = 0; i < columnCount; i++) {
                    Object columnValue = rs.getObject(i + 1);
                    //获取每个列的列名
                    String columnLabel = rsmd.getColumnLabel(i + 1);
                    //【通过反射】给cus对象指定的order属性赋值为columnValue
                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t, columnValue);//给order这个对象的columnLabel属性赋值columnValue
                }
                return t;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(connection, ps, rs);
        }
        return null;
    }
}

2.4 ResultSet与ResultSetMetaData

2.4.1 ResultSet

查询需要调用PreparedStatement 的 executeQuery() 方法，查询结果是一个ResultSet 对象
ResultSet 对象以逻辑表格的形式封装了执行数据库操作的结果集，ResultSet 接口由数据库厂商提供实现
ResultSet 返回的实际上就是一张数据表。有一个指针指向数据表的第一条记录的前面。
ResultSet 对象维护了一个指向当前数据行的游标，初始的时候，游标在第一行之前，可以通过 ResultSet 对象的 next() 方法移动到下一行。调用 next()方法检测下一行是否有效。若有效，该方法返回 true，且指针下移。相当于Iterator对象的 hasNext() 和 next() 方法的结合体。
当指针指向一行时, 可以通过调用 getXxx(int index) 或 getXxx(int columnName) 获取每一列的值。例如: getInt(1), getString("name")。Java与数据库交互涉及到的相关Java API中的索引都从1开始。
ResultSet 接口的常用方法：boolean next() getString() ……

2.4.2 ResultSetMetaData

可用于获取关于 ResultSet 对象中列的类型和属性信息的对象
ResultSetMetaData meta = rs.getMetaData();

getColumnName(int column)：获取指定列的名称
getColumnLabel(int column)：获取指定列的别名
getColumnCount()：返回当前 ResultSet 对象中的列数。
getColumnTypeName(int column)：检索指定列的数据库特定的类型名称。
getColumnDisplaySize(int column)：指示指定列的最大标准宽度，以字符为单位。
isNullable(int column)：指示指定列中的值是否可以为 null。
isAutoIncrement(int column)：指示是否自动为指定列进行编号，这样这些列仍然是只读的。

问题1：得到结果集后, 如何知道该结果集中有哪些列？列名是什么？
需要使用一个描述 ResultSet 的对象，即 ResultSetMetaData
问题2：关于ResultSetMetaData
1. 如何获取 ResultSetMetaData：调用 ResultSet 的 getMetaData() 方法即可
2. 获取 ResultSet 中有多少列：调用 ResultSetMetaData 的 getColumnCount() 方法
3. 获取 ResultSet 每一列的列的别名是什么：调用 ResultSetMetaData 的getColumnLabel() 方法

2.5 资源释放

释放ResultSet, Statement,Connection。
数据库连接（Connection）是非常稀有的资源，用完后必须马上释放，如果Connection不能及时正确的关闭将导致系统宕机。Connection的使用原则是尽量晚创建，尽量早的释放。
可以在finally中关闭，保证及时其他代码出现异常，资源也一定能被关闭

2.6 JDBC API小结

2.6.1 两种思想

面向接口编程的思想
ORM思想(object relational mapping)

一个数据表对应一个java类
表中的一条记录对应java类的一个对象
表中的一个字段对应java类的一个属性

sql是需要结合列名和表的属性名来写。注意起别名。

2.6.2 两种技术

JDBC结果集的元数据：ResultSetMetaData

获取列数：getColumnCount()
获取列的别名：getColumnLabel()

通过反射，创建指定类的对象，获取指定的属性并赋值

三、 PreparedStatement操作BLOB类型字段

3.1 插入核心代码

    FileInputStream is = new FileInputStream(new File("zt.png"));
    String sql = "insert into customers(name,email,birth,photo) values(?,?,?,?)";
    JDBCUtils.update(sql,"周涛涛","323@cctv.com","1998-03-23",is);

3.2 查询核心代码

    //其他类型查询正常用相应的实体类封装
    //将Blob类型的字段下载下来，以文件的方式保存在本地
	Blob photo = rs.getBlob("photo");
	InputStream is = photo.getBinaryStream();
	FileOutputStream fos = new FileOutputStream("zhangyuhao.jpg");
	byte[] buffer = new byte[1024];
	int len;
	while((len = is.read(buffer)) != -1){
		fos.write(buffer, 0, len);
	}

    //记得关闭InputStream和FileOutputStream

四、批量插入

4.1 批量执行SQL语句

JDBC的批量处理语句包括下面三个方法：

addBatch(String)：添加需要批量处理的SQL语句或是参数；
executeBatch()：执行批量处理语句；
clearBatch():清空缓存的数据

通常我们会遇到两种批量执行SQL语句的情况：

多条SQL语句的批量处理；
一个SQL语句的批量传参；

4.2 高效地批量插入

创建一个goods表：

CREATE TABLE goods(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
);

修改配置文件：（mysql 8.0.26）

user=root
password=12345
url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&&rewriteBatchedStatements=true
driverClass=com.mysql.cj.jdbc.Driver

代码实现：（使用Connection 的 setAutoCommit(false) / commit() ）

import org.junit.Test;
import tx.util.JDBCUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;

public class InsertTest {
    @Test
    public void testInsert2() throws Exception {
        long start = System.currentTimeMillis();
        Connection conn = JDBCUtils.getConnection();
        //1.设置为不自动提交数据
        conn.setAutoCommit(false);
        String sql = "insert into goods(name)values(?)";
        PreparedStatement ps = conn.prepareStatement(sql);
        for (int i = 1; i <= 20000; i++) {
            ps.setString(1, "name_" + i);
            //1.“攒”sql
            ps.addBatch();
            if (i % 500 == 0) {
                //2.执行
                ps.executeBatch();
                //3.清空
                ps.clearBatch();
            }
        }
        //2.提交数据
        conn.commit();
        long end = System.currentTimeMillis();
        System.out.println("花费的时间为：" + (end - start));
        JDBCUtils.closeResource(conn, ps);
    }
}

五、事务

5.1 介绍

事务：一组逻辑操作单元,使数据从一种状态变换到另一种状态。
事务处理（事务操作）：保证所有事务都作为一个工作单元来执行，即使出现了故障，都不能改变这种执行方式。当在一个事务中执行多个操作时，要么所有的事务都被提交(commit)，那么这些修改就永久地保存下来；要么数据库管理系统将放弃所作的所有修改，整个事务回滚(rollback)到最初状态。
为确保数据库中数据的一致性，数据的操纵应当是离散的成组的逻辑单元：当它全部完成时，数据的一致性可以保持，而当这个单元中的一部分操作失败，整个事务应全部视为错误，所有从起始点以后的操作应全部回退到开始状态。

5.2 JDBC事务处理

数据一旦提交，就不可回滚。
数据什么时候意味着提交？

当一个连接对象被创建时，默认情况下是自动提交事务：每次执行一个 SQL 语句时，如果执行成功，就会向数据库自动提交，而不能回滚。
关闭数据库连接，数据就会自动的提交。如果多个操作，每个操作使用的是自己单独的连接，则无法保证事务。即同一个事务的多个操作必须在同一个连接下。

JDBC程序中为了让多个 SQL 语句作为一个事务执行：

调用 Connection 对象的 setAutoCommit(false); 以取消自动提交事务
在所有的 SQL 语句都成功执行后，调用 commit(); 方法提交事务
在出现异常时，调用 rollback(); 方法回滚事务

（若此时 Connection 没有被关闭，还可能被重复使用，则需要恢复其自动提交状态
setAutoCommit(true)。尤其是在使用数据库连接池技术时，执行close()方法前，建议恢复自动提交状态。）

5.3 案例演示（AA向BB转账1000元）

考虑事务的通用的增删改操作（version 2.0）

public class TransactionTest {

    @Test
    public void testUpdateWithTx() {
        Connection connection = null;
        try {
            connection = JDBCUtils.getConnection();
            //取消数据自动提交功能
            connection.setAutoCommit(false);
            String sql1 = "update user_table set balance = balance - 100 where user = ?";
            update(connection, sql1, "AA");

            //模拟网络异常
            System.out.println(10 / 0);

            String sql2 = "update user_table set balance = balance + 100 where user = ?";
            update(connection, sql2, "BB");
            System.out.println("√");
            //提交数据
            connection.commit();
        } catch (Exception e) {
            e.printStackTrace();
            //出现异常，回滚数据
            try {
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        } finally {
            //修改其为自动提交数据【主要针对使用数据库连接池】
            try {
                connection.setAutoCommit(true);
            } catch (SQLException e) {
                e.printStackTrace();
            }
            JDBCUtils.closeResource(connection, null);
        }
    }

    //通用增删改  考虑数据库事务
    public int update(Connection conn, String sql, Object... args) {
        PreparedStatement ps = null;
        try {
            //1.预编译SQL语句，返回PreparedStatement的实例
            ps = conn.prepareStatement(sql);
            //2.填充占位符
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            //3.执行
            return ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //4.关闭资源
            JDBCUtils.closeResource(null, ps);
        }
        return 0;
    }
}

5.4 ACID

原子性（Atomicity） 原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
一致性（Consistency） 事务必须使数据库从一个一致性状态变换到另外一个一致性状态。
隔离性（Isolation） 事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
持久性（Durability） 持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响。

5.4.1 数据库并发问题

对于同时运行的多个事务, 当这些事务访问数据库中相同的数据时, 如果没有采取必要的隔离机制, 就会导致各种并发问题:

脏读: 对于两个事务 T1, T2, T1 读取了已经被 T2 更新但还没有被提交的字段。之后, 若 T2 回滚, T1读取的内容就是临时且无效的。
不可重复读: 对于两个事务T1, T2, T1 读取了一个字段, 然后 T2 更新并提交了该字段。之后, T1再次读取同一个字段, 值就不同了。
幻读: 对于两个事务T1, T2, T1 从一个表中读取了一个字段, 然后 T2 在该表中插入了一些新的行。之后, 如果 T1 再次读取同一个表, 就会多出几行。

数据库事务的隔离性: 数据库系统必须具有隔离并发运行各个事务的能力, 使它们不会相互影响, 避免各种并发问题。
一个事务与其他事务隔离的程度称为隔离级别。数据库规定了多种事务隔离级别, 不同隔离级别对应不同的干扰程度, 隔离级别越高, 数据一致性就越好, 但并发性越弱。

5.4.2 四种隔离级别

数据库提供的4种事务隔离级别

隔离级别	描述
READ UNCOMMITTED （读未提交数据）	允许事务读取未被其他事务提交的变更。脏读、不可重复读和幻读问题都会出现。
READ COMMITTED （读已提交数据）	只允许事务读取已经被其他事务提交的变更。可以避免脏读，但不可重复读和幻读问题仍然可能出现。
REPEATABLE READ （可重复读）	确保事务可以多次从一个字段中读取相同的值。在这个事务持续期间，禁止其他事务对这个字段进行更新。可以避免脏读和不可重复读，但幻读的问题仍然存在。
SERIALIZABLE （串行化）	确保事务可以从一个表中读取相同的行。在这个事务持续期间，禁止其他事务对该表执行插入、更新和删除操作。所有并发问题都可以避免，但性能很低。

Oracle 支持的 2 种事务隔离级别：READ COMMITED, SERIALIZABLE。 Oracle 默认的事务隔离级别为: READ COMMITED 。
Mysql 支持 4 种事务隔离级别。Mysql 默认的事务隔离级别为: REPEATABLE READ。

5.4.3 设置MySQL隔离级别

每启动一个 mysql 程序, 就会获得一个单独的数据库连接. 每个数据库连接都有一个全局变量 @@transaction_isolation,表示当前的事务隔离级别。
查看当前的隔离级别:

select @@transaction_isolation;

设置当前 mySQL 连接的隔离级别:

set global transaction isolation level read committed;

补充操作：
创建mysql数据库用户：

create user zt identified by '12345';

授予权限

grant select,insert,update,delete on test.* to zt;

六、DAO及其实现类

6.1 确定类

【BaseDAO】

/*
* 封装针对数据库表的通用操作
* DAO: database  access  object
* */
public abstract class BaseDAO {
    //通用增删改  考虑数据库事务  V-2.0
    public int update(Connection conn, String sql, Object... args) {
        PreparedStatement ps = null;
        try {
            //1.预编译SQL语句，返回PreparedStatement的实例
            ps = conn.prepareStatement(sql);
            //2.填充占位符
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            //3.执行
            return ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //4.关闭资源
            JDBCUtils.closeResource(null, ps);
        }
        return 0;
    }

    //针对不同表的通用查询，返回记录集合  V-2.0
    public <T> List<T> getList(Connection conn,Class<T> clazz, String sql, Object... args){
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            rs = ps.executeQuery();
            //获取结果集的元数据
            ResultSetMetaData rsmd = rs.getMetaData();
            //通过ResultSetMetaData获取结果集中的列数
            int columnCount = rsmd.getColumnCount();
            //创建集合对象
            ArrayList<T> list = new ArrayList<>();
            while (rs.next()) {
                T t = clazz.getDeclaredConstructor().newInstance();
                //处理结果集一行数据中的每一个列:给t对象指定的属性赋值
                for (int i = 0; i < columnCount; i++) {
                    Object columnValue = rs.getObject(i + 1);
                    //获取每个列的列名
                    String columnLabel = rsmd.getColumnLabel(i + 1);
                    //【通过反射】给cus对象指定的order属性赋值为columnValue
                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t, columnValue);//给order这个对象的columnLabel属性赋值columnValue
                }
                list.add(t);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(null, ps, rs);
        }
        return null;
    }

    //考虑到【事务】通用查询，用于返回一条记录  V-2.0
    public <T> T getInstance(Connection conn,Class<T> clazz, String sql, Object... args) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getConnection();
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            rs = ps.executeQuery();
            //获取结果集的元数据
            ResultSetMetaData rsmd = rs.getMetaData();
            //通过ResultSetMetaData获取结果集中的列数
            int columnCount = rsmd.getColumnCount();
            if (rs.next()) {
                T t = clazz.getDeclaredConstructor().newInstance();
                //处理结果集一行数据中的每一个列
                for (int i = 0; i < columnCount; i++) {
                    Object columnValue = rs.getObject(i + 1);
                    //获取每个列的列名
                    String columnLabel = rsmd.getColumnLabel(i + 1);
                    //【通过反射】给cus对象指定的order属性赋值为columnValue
                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t, columnValue);//给order这个对象的columnLabel属性赋值columnValue
                }
                return t;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(null, ps, rs);
        }
        return null;
    }

    //用于查询特殊值的通用方法
    public <E> E getValue(Connection conn,String sql,Object ...args) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i+1,args[i]);
            }
            rs = ps.executeQuery();
            if(rs.next()){
                return (E) rs.getObject(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(null,ps,rs);
        }
        return null;
    }
}

【CustomersDAO】

import java.sql.Connection;
import java.sql.Date;
import java.util.List;

/*
* 此接口用于规范针对于customer表的常用操作
* */
public interface CustomerDAO {
    /**
     * 将cust对象添加到数据库中
     * @param conn
     * @param cust
     */
    public abstract void insert (Connection conn, Customers cust);

    /**
     * 根据id删除记录
     * @param conn
     * @param id
     */
    public abstract void deleteById(Connection conn,int id);

    /**
     * 根据id修改记录
     * @param conn
     * @param cust
     */
    public abstract void updatById(Connection conn,Customers cust);

    /**
     * 根据id查询记录
     * @param conn
     * @param id
     */
    public abstract Customers getCustomerById(Connection conn,int id);

    /**
     * 查询所有
     * @param conn
     * @return
     */
    public abstract List<Customers> getAll(Connection conn);

    /**
     * 获取数据表中数据的条目数
     * @param conn
     * @return
     */
    public abstract Long getCount(Connection conn);

    /**
     * 返回数据表中最大生日（年龄最小）
     * @param conn
     * @return
     */
    public abstract Date getMaxBirth(Connection conn);
}

【CustomersDAOImpl】

import txx.bean.Customers;

import java.sql.Connection;
import java.sql.Date;
import java.util.List;

/*
*
* */
public class CustomerDAOImpl extends BaseDAO implements CustomerDAO {
    @Override
    public void insert(Connection conn, Customers cust) {
        String sql = "insert into customers(name,email,birth) values(?,?,?)";
        update(conn,sql,cust.getName(),cust.getEmail(),cust.getBirth());
    }

    @Override
    public void deleteById(Connection conn, int id) {
        String sql = "delete from customers where id = ?";
        update(conn,sql,id);
    }

    @Override
    public void updatById(Connection conn, Customers cust) {
        String sql = "update customers set name = ?,email = ?,birth = ? where id = ?";
        update(conn,sql,cust.getName(),cust.getEmail(),cust.getBirth(),cust.getId());
    }

    @Override
    public Customers getCustomerById(Connection conn, int id) {
        String sql = "select name,email,birth from customers where id = ?";
        Customers cust = getInstance(conn, Customers.class, sql, id);
        return cust;
    }

    @Override
    public List<Customers> getAll(Connection conn) {
        String sql = "select id,name,email,birth from customers ";
        List<Customers> list = getList(conn, Customers.class, sql);
        return list;
    }

    @Override
    public Long getCount(Connection conn) {
        String sql = "select count(*) from customers ";
        return getValue(conn, sql);
    }

    @Override
    public Date getMaxBirth(Connection conn) {
        String sql = "select max(birth) from customers ";
        return getValue(conn, sql);
    }
}

【CustomersDAOImplTest】测试类（idea：Ctrl+shift+t）

import org.junit.Test;
import txx.bean.Customers;
import txx.dao.CustomerDAOImpl;
import txx.util.JDBCUtils;

import java.sql.Connection;
import java.sql.Date;
import java.util.List;


public class CustomerDAOImplTest {

    private CustomerDAOImpl dao = new CustomerDAOImpl();

    @Test
    public void insert() {
        Connection connection = null;
        try {
            connection = JDBCUtils.getConnection();
            Customers customers = new Customers(1,"dq","1117@cctv.com",new Date(234243242342L));
            dao.insert(connection,customers);
            System.out.println("添加成功");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(connection,null);
        }
    }

    @Test
    public void deleteById() {
        Connection connection = null;
        try {
            connection = JDBCUtils.getConnection();
            dao.deleteById(connection,19);
            System.out.println("删除成功");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(connection,null);
        }
    }

    @Test
    public void updatById() {
        Connection connection = null;
        try {
            connection = JDBCUtils.getConnection();
            Customers customers = new Customers(12,"曾黎","zengli@cctv.com",new Date(234243242342L));
            dao.updatById(connection,customers);
            System.out.println("修改成功");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(connection,null);
        }
    }

    @Test
    public void getCustomerById() {
        Connection connection = null;
        try {
            connection = JDBCUtils.getConnection();
            Customers customer = dao.getCustomerById(connection, 20);
            System.out.println(customer);
            System.out.println("getCustomerById成功");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(connection,null);
        }
    }

    @Test
    public void getAll() {
        Connection connection = null;
        try {
            connection = JDBCUtils.getConnection();
            List<Customers> customers = dao.getAll(connection);
            customers.forEach(System.out::println);
            System.out.println("getAll成功");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(connection,null);
        }
    }

    @Test
    public void getCount() {
        Connection connection = null;
        try {
            connection = JDBCUtils.getConnection();
            Long count = dao.getCount(connection);
            System.out.println(count);
            System.out.println("getCount成功");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(connection,null);
        }
    }

    @Test
    public void getMaxBirth() {
        Connection connection = null;
        try {
            connection = JDBCUtils.getConnection();
            Date maxBirth = dao.getMaxBirth(connection);
            System.out.println(maxBirth);
            System.out.println("getMaxBirth成功");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(connection,null);
        }
    }
}

6.2 优化（给BaseDAO加上泛型参数）

【BaseDAO】：将clazz声明为私有变量，代码块获取当前的泛型，查询中不需要传入类.Class 参数，查询方法也由泛型方法变为一般方法，其他一样

public abstract class BaseDAO<T> {
    private Class<T> clazz = null;


    {
        //获取当前BaseDAOI的子类继承的父类中的泛型
        Type genericSuperclass = this.getClass().getGenericSuperclass();
        ParameterizedType paramType = (ParameterizedType) genericSuperclass;
        //获取父类的泛型参数
        Type[] actualTypeArguments = paramType.getActualTypeArguments();
        clazz = (Class<T>) actualTypeArguments[0];//泛型的第一个参数，也就是Customers
    }


    //针对不同表的通用查询，返回记录集合  V-2.0
    public List<T> getList(Connection conn, String sql, Object... args) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            rs = ps.executeQuery();
            //获取结果集的元数据
            ResultSetMetaData rsmd = rs.getMetaData();
            //通过ResultSetMetaData获取结果集中的列数
            int columnCount = rsmd.getColumnCount();
            //创建集合对象
            ArrayList<T> list = new ArrayList<>();
            while (rs.next()) {
                T t = clazz.getDeclaredConstructor().newInstance();
                //处理结果集一行数据中的每一个列:给t对象指定的属性赋值
                for (int i = 0; i < columnCount; i++) {
                    Object columnValue = rs.getObject(i + 1);
                    //获取每个列的列名
                    String columnLabel = rsmd.getColumnLabel(i + 1);
                    //【通过反射】给cus对象指定的order属性赋值为columnValue
                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t, columnValue);//给order这个对象的columnLabel属性赋值columnValue
                }
                list.add(t);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(null, ps, rs);
        }
        return null;
    }


    //考虑到【事务】通用查询，用于返回一条记录  V-2.0
    public T getInstance(Connection conn, String sql, Object... args) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getConnection();
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            rs = ps.executeQuery();
            //获取结果集的元数据
            ResultSetMetaData rsmd = rs.getMetaData();
            //通过ResultSetMetaData获取结果集中的列数
            int columnCount = rsmd.getColumnCount();
            if (rs.next()) {
                T t = clazz.getDeclaredConstructor().newInstance();
                //处理结果集一行数据中的每一个列
                for (int i = 0; i < columnCount; i++) {
                    Object columnValue = rs.getObject(i + 1);
                    //获取每个列的列名
                    String columnLabel = rsmd.getColumnLabel(i + 1);
                    //【通过反射】给cus对象指定的order属性赋值为columnValue
                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t, columnValue);//给order这个对象的columnLabel属性赋值columnValue
                }
                return t;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(null, ps, rs);
        }
        return null;
    }

}

【CustomersDAO】一样

【CustomersDAOImpl】（extends BaseDAO<Customers>其他一样，只是查询参数中不用传入Customers.Class），因为BaseDAO中将其声明为私有变量并直接获取到了

public class CustomerDAOImpl extends BaseDAO<Customers> implements CustomerDAO {
     @Override
    public List<Customers> getAll(Connection conn) {
        String sql = "select id,name,email,birth from customers ";
        List<Customers> list = getList(conn, sql);
        return list;
    }

    @Override
    public Long getCount(Connection conn) {
        String sql = "select count(*) from customers ";
        return getValue(conn, sql);
    }
}

【CustomersDAOImplTest】一样

七、数据库连接池

7.1 JDBC数据库连接池的必要性

在使用开发基于数据库的web程序时，传统的模式基本是按以下步骤

在主程序（如servlet、beans）中建立数据库连接
进行sql操作
断开数据库连接

这种开发模式存在以下问题：

普通的JDBC数据库连接使用 DriverManager 来获取，每次向数据库建立连接的时候都要将 Connection加载到内存中，再验证用户名和密码(得花费0.05s～1s的时间)。需要数据库连接的时候，就向数据库要求一个，执行完成后再断开连接。这样的方式将会消耗大量的资源和时间。数据库的连接资源并没有得到很好的重复利用。若同时有几百人甚至几千人在线，频繁的进行数据库连接操作将占用很多的系统资源，严重的甚至会造成服务器的崩溃。
对于每一次数据库连接，使用完后都得断开。否则，如果程序出现异常而未能关闭，将会导致数据库系统中的内存泄漏，最终将导致重启数据库。（回忆：何为Java的内存泄漏？）
这种开发不能控制被创建的连接对象数，系统资源会被毫无顾及的分配出去，如连接过多，也可能导致内存泄漏，服务器崩溃

7.2 数据库连接池技术

数据库连接池的基本思想：就是为数据库连接建立一个“缓冲池”。预先在缓冲池中放入一定数量的连接，当需要建立数据库连接时，只需从“缓冲池”中取出一个，使用完毕之后再放回去。
数据库连接池负责分配、管理和释放数据库连接，它允许应用程序重复使用一个现有的数据库连接，而不是重新建立一个。
数据库连接池在初始化时将创建一定数量的数据库连接放到连接池中，这些数据库连接的数量是由最小数据库连接数来设定的。无论这些数据库连接是否被使用，连接池都将一直保证至少拥有这么多的连接数量。连接池的最大数据库连接数量限定了这个连接池能占有的最大连接数，当应用程序向连接池请求的连接数超过最大连接数量时，这些请求将被加入到等待队列中。
工作原理

数据库连接池技术的优点

1. 资源重用
由于数据库连接得以重用，避免了频繁创建，释放连接引起的大量性能开销。在减少系统消耗的基础上，另一方面也增加了系统运行环境的平稳性。
2. 更快的系统反应速度
数据库连接池在初始化过程中，往往已经创建了若干数据库连接置于连接池中备用。此时连接的初始化工作均已完成。对于业务请求处理而言，直接利用现有可用连接，避免了数据库连接初始化和释放过程的时间开销，从而减少了系统的响应时间
3. 新的资源分配手段
对于多应用共享同一数据库的系统而言，可在应用层通过数据库连接池的配置，实现某一应用最大可用数据库连接数的限制，避免某一应用独占所有的数据库资源
4. 统一的连接管理，避免数据库连接泄漏
在较为完善的数据库连接池实现中，可根据预先的占用超时设定，强制回收被占用连接，从而避免了常规数据库连接操作中可能出现的资源泄露

7.3 多种开源的数据库连接池

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

DBCP 是Apache提供的数据库连接池。tomcat 服务器自带dbcp数据库连接池。速度相对c3p0较快，但因自身存在BUG，Hibernate3已不再提供支持。
C3P0 是一个开源组织提供的一个数据库连接池，速度相对较慢，稳定性还可以。hibernate官方推荐使用Proxool 是sourceforge下的一个开源项目数据库连接池，有监控连接池状态的功能，稳定性较c3p0差一点
BoneCP 是一个开源组织提供的数据库连接池，速度快
Druid 是阿里提供的数据库连接池，据说是集DBCP 、C3P0 、Proxool 优点于一身的数据库连接池，但是速度不确定是否有BoneCP快

DataSource 通常被称为数据源，它包含连接池和连接池管理两个部分，习惯上也经常把 DataSource 称为连接池
DataSource用来取代DriverManager来获取Connection，获取速度快，同时可以大幅度提高数据库访问速度
注意！

数据源和数据库连接不同，数据源无需创建多个，它是产生数据库连接的工厂，因此整个应用只需要一个数据源即可。
当数据库访问结束后，程序还是像以前一样关闭数据库连接：conn.close(); 但conn.close()并没有关闭数据库的物理连接，它仅仅把数据库连接释放，归还给了数据库连接池。

7.3.1 C3P0数据库连接池

src下的配置文件：c3p0-config.xml

<?xml version="1.0" encoding="UTF-8" ?>
<c3p0-config>
    <named-config name="helloc3p0">
        <!-- 提供获取连接的4个基本信息 -->
        <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/test?useUnicode=true&amp;characterEncoding=utf-8&amp;&amp;rewriteBatchedStatements=true</property>
        <property name="user">root</property>
        <property name="password">12345</property>

        <!-- 进行数据库连接池管理的基本信息 -->
        <!--当数据库连接池中的连接数不够时，c3p0一次性向数据库服务器申请的连接数-->
        <property name="acquireIncrement">5</property>
        <!--c3p0数据库连接池中初始化时的连接数-->
        <property name="initialPoolSize">10</property>
        <!--c3p0数据库连接池中最小连接数-->
        <property name="minPoolSize">10</property>
        <!--c3p0数据库连接池中最多连接数-->
        <property name="maxPoolSize">100</property>
        <!--c3p0数据库连接池最多维护的Statement的个数-->
        <property name="maxStatements">50</property>
        <!--每个连接中可以最多使用的Statement的个数-->
        <property name="maxStatementsPerConnection">5</property>
    </named-config>
</c3p0-config>

import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.junit.Test;
import java.sql.Connection;


public class C3P0Test {
    @Test
    public void testGetConnection() throws Exception {
        //方式一：
        //获取C3P0数据库连接池
        ComboPooledDataSource cpds = new ComboPooledDataSource();
        cpds.setDriverClass( "com.mysql.cj.jdbc.Driver" ); //loads the jdbc driver
        cpds.setJdbcUrl( "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&&rewriteBatchedStatements=true" );
        cpds.setUser("root");
        cpds.setPassword("12345");
        //设置初始时数据库连接池中的连接数
        cpds.setInitialPoolSize(10);

        Connection connection = cpds.getConnection();
        System.out.println(connection);
//        DataSources.destroy(cpds);//销毁连接池

    }
    //方式二：【推荐使用】配置文件
    @Test
    public void testFetConnection1() throws Exception {
        ComboPooledDataSource cpds = new ComboPooledDataSource("helloc3p0");
        Connection connection = cpds.getConnection();
        System.out.println(connection);
    }
}

7.3.2 dbcp数据库连接池

DBCP 是 Apache 软件基金组织下的开源连接池实现，该连接池依赖该组织下的另一个开源系统：Common-pool。如需使用该连接池实现，应在系统中增加如下两个 jar 文件

Commons-dbcp.jar：连接池的实现
Commons-pool.jar：连接池实现的依赖库

Tomcat 的连接池正是采用该连接池来实现的。该数据库连接池既可以与应用服务器整合使用，也可由应用程序独立使用。
数据源和数据库连接不同，数据源无需创建多个，它是产生数据库连接的工厂，因此整个应用只需要一个数据源即可。
当数据库访问结束后，程序还是像以前一样关闭数据库连接：conn.close(); 但上面的代码并没有关闭数据库的物理连接，它仅仅把数据库连接释放，归还给了数据库连接池。

src下的配置文件为：dbcp.properties

driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&&rewriteBatchedStatements=true
username=root
password=12345

initialSize=10
maxSize=10

import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;
import org.junit.Test;

import javax.sql.DataSource;
import java.io.File;
import java.io.FileInputStream;
import java.sql.Connection;
import java.util.Properties;

public class DBCPTest {
    //方式一：不推荐
    @Test
    public void testGetConnection() throws Exception {
        //创建了DBCP的数据库连接池
        BasicDataSource source = new BasicDataSource();
        //设置基本信息
        source.setDriverClassName("com.mysql.cj.jdbc.Driver");
        source.setUrl("jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&&rewriteBatchedStatements=true");
        source.setUsername("root");
        source.setPassword("12345");
        //还可以设置其他设计数据库管理的属性
        source.setInitialSize(10);
        source.setMaxActive(10);

        Connection connection = source.getConnection();
        System.out.println(connection);
    }

    //方式二：【推荐使用】配置文件
    @Test
    public void testGetConnection1() throws Exception {
        Properties pros = new Properties();
        //方式一：
//        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("dhcp.properties");
        //方式二：
        FileInputStream is = new FileInputStream(new File("src/dbcp.properties"));
        pros.load(is);
        DataSource dataSource = BasicDataSourceFactory.createDataSource(pros);
        Connection connection = dataSource.getConnection();
        System.out.println(connection);
    }

}

7.3.3 Druid（德鲁伊）数据库连接池

Druid是阿里巴巴开源平台上一个数据库连接池实现，它结合了C3P0、DBCP、Proxool等DB池的优点，同时加入了日志监控，可以很好的监控DB池连接和SQL的执行情况，可以说是针对监控而生的DB连接池，可以说是目前最好的连接池之一。

src下的配置文件为：druid.properties

url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&&rewriteBatchedStatements=true
username = root
password=12345
driverClassName=com.mysql.cj.jdbc.Driver

initialSize=10
maxActive=10

import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.junit.Test;
import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.util.Properties;

public class DruidTest {
    @Test
    public void getConnection() throws Exception {
        Properties pros = new Properties();
        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");
        pros.load(is);
        DataSource dataSource = DruidDataSourceFactory.createDataSource(pros);
        Connection connection = dataSource.getConnection();
        System.out.println(connection);
    }
}

八、Apache-DBUtils实现CRUD操作

8.1 Apache-DBUtils简介

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

org.apache.commons.dbutils.QueryRunner
org.apache.commons.dbutils.ResultSetHandler
工具类：org.apache.commons.dbutils.DbUtils

API包说明

8.2 主要API使用

8.2.1 DbUtils

提供如关闭连接、装载JDBC驱动程序等常规工作的工具类，里面的所有方法都是静态的。

public static void close(…) throws java.sql.SQLException： DbUtils类提供了三个重载的关闭方
法。这些方法检查所提供的参数是不是NULL，如果不是的话，它们就关闭Connection、Statement和ResultSet。
public static void closeQuietly(…): 这一类方法不仅能在Connection、Statement和ResultSet为NULL情况下避免关闭，还能隐藏一些在程序中抛出的SQLEeception。
public static void commitAndClose(Connection conn)throws SQLException：用来提交连接的事务，然后关闭连接
public static void commitAndCloseQuietly(Connection conn)：用来提交连接，然后关闭连接，并且在关闭连接时不抛出SQL异常。
public static void rollback(Connection conn)throws SQLException：允许conn为null，因为方法内部做了判断
public static void rollbackAndClose(Connection conn)throws SQLException
rollbackAndCloseQuietly(Connection)
public static boolean loadDriver(java.lang.String driverClassName)：这一方装载并注册JDBC驱动程序，如果成功就返回true。使用该方法，不需要捕捉这个ClassNotFoundException。

8.2.2 QueryRunner类

该类简单化了SQL查询，它与ResultSetHandler组合在一起使用可以完成大部分的数据库操作，能够大大减少编码量。
QueryRunner类提供了两个构造器

默认的构造器
需要一个 javax.sql.DataSource 来作参数的构造器

QueryRunner类的主要方法

更新

public int update(Connection conn, String sql, Object... params) throws SQLException:用来执行一个更新（插入、更新或删除）操作。
......

插入

public T insert(Connection conn,String sql,ResultSetHandler rsh, Object... params) throws SQLException：只支持INSERT语句，其中 rsh - The handler used to create the result object from the ResultSet of auto-generated keys. 返回值: An object generated by the handler.即自动生成的键值
....

批处理

public int[] batch(Connection conn,String sql,Object[][] params)throws SQLException： INSERT,UPDATE, or DELETE语句
public T insertBatch(Connection conn,String sql,ResultSetHandler rsh,Object[][] params)throws SQLException：只支持INSERT语句
.....

查询

public Object query(Connection conn, String sql, ResultSetHandler rsh,Object... params) throws SQLException：执行一个查询操作，在这个查询中，对象数组中的每个元素值被用来作为查询语句的置换参数。该方法会自行处理 PreparedStatement 和 ResultSet 的创建和关闭。
......

8.2.3 ResultSetHandler接口及实现类

该接口用于处理 java.sql.ResultSet，将数据按要求转换为另一种形式。
ResultSetHandler 接口提供了一个单独的方法：Object handle (java.sql.ResultSet .rs)。
接口的主要实现类

ArrayHandler：把结果集中的第一行数据转成对象数组。
ArrayListHandler：把结果集中的每一行数据都转成一个数组，再存放到List中。
BeanHandler：将结果集中的第一行数据封装到一个对应的JavaBean实例中。
BeanListHandler：将结果集中的每一行数据都封装到一个对应的JavaBean实例中，存放到List里。
ColumnListHandler：将结果集中某一列的数据存放到List中。
KeyedHandler(name)：将结果集中的每一行数据都封装到一个Map里，再把这些map再存到一个map里，其key为指定的key。
MapHandler：将结果集中的第一行数据封装到一个Map里，key是列名，value就是对应的值。
MapListHandler：将结果集中的每一行数据都封装到一个Map里，然后再存放到List
ScalarHandler：查询单个值对象

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.*;
import org.junit.Test;
import tx.util.JDBCUtils;
import txx.bean.Customers;

import java.sql.*;
import java.util.List;
import java.util.Map;

public class QueryRunnerTest {
    //测试插入
    @Test
    public void testInsert()  {
        Connection connection = null;
        try {
            QueryRunner queryRunner = new QueryRunner();
            connection = JDBCUtils.getConnection3();
            String sql = "insert into customers(name,email,birth)values(?,?,?)";
            int i = queryRunner.update(connection, sql, "tm", "tm@163.com", "1990-11-11");
            System.out.println("共添加"+i+"条数据");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(connection,null);
        }
    }

    //测试查询
    //BeanHandler是ResultSetHandler接口的实现类，用来封装表中的一条记录（Bean）
    @Test
    public void testQuery1(){
        Connection connection = null;
        try {
            QueryRunner queryRunner = new QueryRunner();
            connection = JDBCUtils.getConnection3();
            String sql = "select id,name,email,birth from customers where id = ?";
            BeanHandler<Customers> handler = new BeanHandler<Customers>(Customers.class);
            Customers customer = queryRunner.query(connection, sql, handler, 23);
            System.out.println(customer);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(connection,null);
        }
    }

    @Test
    //BeanListHandler是ResultSetHandler接口的实现类,用来封装多条返回值数据
    public void testQuery2(){
        Connection connection = null;
        try {
            QueryRunner queryRunner = new QueryRunner();
            connection = JDBCUtils.getConnection3();
            String sql = "select id,name,email,birth from customers where id > ?";
            BeanListHandler<Customers> handlers = new BeanListHandler<Customers>(Customers.class);
            List<Customers> customersList = queryRunner.query(connection, sql, handlers, 22);
            customersList.forEach(System.out::println);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(connection,null);
        }
    }

    //MapHandler是ResultSetHandler接口的实现类，对应表中的一条记录
    //将字段和相应字段的值作为map中的key和value
    @Test
    public void testQuery3(){
        Connection connection = null;
        try {
            QueryRunner queryRunner = new QueryRunner();
            connection = JDBCUtils.getConnection3();
            String sql = "select id,name,email,birth from customers where id = ?";
            MapHandler mapHandler = new MapHandler();
            Map<String, Object> map = queryRunner.query(connection, sql, mapHandler, 23);
            System.out.println(map);//以键=值的方式呈现
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(connection,null);
        }
    }


    //MapListHandler是ResultSetHandler接口的实现类，对应表中的多条记录
    //将字段和相应字段的值作为map中的key和value,将这些map添加到List中
    @Test
    public void testQuery4(){
        Connection connection = null;
        try {
            QueryRunner queryRunner = new QueryRunner();
            connection = JDBCUtils.getConnection3();
            String sql = "select id,name,email,birth from customers where id > ?";
            MapListHandler mapListHandler = new MapListHandler();
            List<Map<String, Object>> mapList = queryRunner.query(connection, sql, mapListHandler, 22);
            mapList.forEach(System.out::println);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(connection,null);
        }
    }

    //ScalarHandler是ResultSetHandler接口的实现类,可以查询特殊需求
    //查询表中有多少条数据
    @Test
    public void testQuery5(){
        Connection connection = null;
        try {
            QueryRunner queryRunner = new QueryRunner();
            connection = JDBCUtils.getConnection3();
            String sql = "select count(*) from customers";
            ScalarHandler scalarHandler = new ScalarHandler();
            Long count = (Long) queryRunner.query(connection, sql, scalarHandler);
            System.out.println(count);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(connection,null);
        }
    }

    //查询表中最大生日日期（最小年龄）
    @Test
    public void testQuery6(){
        Connection connection = null;
        try {
            QueryRunner queryRunner = new QueryRunner();
            connection = JDBCUtils.getConnection3();
            String sql = "select max(birth) from customers";
            ScalarHandler scalarHandler = new ScalarHandler();
            Date maxDate = (Date) queryRunner.query(connection, sql, scalarHandler);
            System.out.println(maxDate);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(connection,null);
        }
    }

    /**
     * 自定义一个ResultSetHandler的实现类
     */
    @Test
    public void testQuery7(){
        Connection connection = null;
        try {
            QueryRunner queryRunner = new QueryRunner();
            connection = JDBCUtils.getConnection3();
            String sql = "select id,name,email,birth from customers where id = ?";
            //匿名实现类
            ResultSetHandler<Customers> handler = new ResultSetHandler<Customers>() {
                @Override
                public Customers handle(ResultSet resultSet) throws SQLException {
//                    System.out.println("handler ");
//                    return null;
//                    return new Customers(24,"tm","tm@163.com",new Date(435325235L));
                    if(resultSet.next()){
                        int id = resultSet.getInt("id");
                        String name = resultSet.getString("name");
                        String email = resultSet.getString("email");
                        Date birth = resultSet.getDate("birth");
                        Customers cust = new Customers(id,name,email,birth);
                        return cust;
                    }
                    return null;
                }
            };
            Customers cust = queryRunner.query(connection, sql, handler, 24);
            System.out.println(cust);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(connection,null);
        }
    }
}