[Java知识库]手撕JDBC笔记(含源码)，进阶框架必备

JDBC是java访问数据库的基石，JDO、Hibernate、MyBatis等都封装了JDBC。如果只单纯使用持久层框架，可以不用学习JDBC,但从程序员发展前景来看则必须要掌握的，框架会一直迭代更新，但原理是不变的，掌握JDBC,才能以不变应完变，这样才能走的长久

一、JDBC概述

JDBC： 是sun公司提供一套用于不局限某种特定的数据库操作的接口，不同的数据库厂商，需要针对这套接口，提供不同实现(实现类)。不同的实现的集合，即为不同数据库的驱动。所以java程序员只需要面向这套接口编程即可(即面向接口编程，java代码中不应该出现第三方代码)。

例如，在标准类库中定义了java.sql,javax.sql用来访问数据库的标准Java类库，使用这些类库可以以一种标准的方法、方便地访问数据库资源。而不同的数据库厂商，需要根据这一标准实现自己的数据库操作方式(驱动)

JDBC的目的是方便操作不同的数据库，进行数据的持久化

持久化(persistence)： 把数据保存到可掉电式存储设备中以供之后使用，例如磁盘文件，XML文件，数据库

在没有JDBC时，由于不同的数据库的操作方式不同，这样每当切换数据库时，原有的Java代码就需要修改，可移植性很差，如下

有了JDBC，Java程序访问数据库时是这样的：

• 创建标准类库中的Driver接口的实现类(某个数据库厂商提供的驱动)对象，赋给接口对象，实现上转型
• 调用Driver接口的connect方法获取连接，由于多态和动态绑定，实际调用的是实现类的方法(某个数据库厂商驱动重写的connect方法)

JDBC程序编写步骤

补充：ODBC(Open Database Connectivity，开放式数据库连接)，是微软在Windows平台下推出的。使用者在程序中只需要调用ODBC API，由 ODBC 驱动程序将调用转换成为对特定的数据库的调用请求。

二、获取数据库连接

2.1 Driver接口及实现类

2.1.1 Driver接口介绍

java.sql.Driver 接口是所有 JDBC 驱动程序需要实现的接口。这个接口是提供给数据库厂商使用的，不同数据库厂商提供不同的实现。

• Oracle的驱动：oracle.jdbc.driver.OracleDriver
• mySql的驱动： com.mysql.jdbc.Driver

java连接数据库需现在加载数据库驱动Driver（标准类库中的Driver接口的实现类)，因此需要导入相关数据库的驱动jar包，这里已mysql为例，驱动下载地址

将上述jar包拷贝到Java工程的一个目录中，习惯上新建一个lib文件夹。

2.1.2 加载JDBC驱动(Driver实现类)

加载 JDBC 驱动需调用 Class 类的静态方法 forName()，向其传递要加载的 JDBC 驱动的类名

• Class.forName(“com.mysql.jdbc.Driver”);

2.2 数据库五种连接方式（迭代)

连接方式一

// 方式一：
@Test
public void testConnection1() throws SQLException {
    //1.创建java.sql.Driver接口实现类的对象，并赋给接口对象driver，实现上转型
    Driver driver = new com.mysql.jdbc.Driver();
    // 2.提供url，指明具体操作的数据
    String url = "jdbc:mysql://localhost:3306/test";
    //3.提供Properties的对象，指明用户名和密码
    Properties info = new Properties();
    info.setProperty("user", "root");
    info.setProperty("password", "12345");
    //4.调用driver的connect()，获取连接
    Connection connect = driver.connect(url, info);
    System.out.println(connect);
}

方式一中显式出现了第三方数据库的API，数据库相关数据硬编码在代码中，耦合性大，每当切换数据库驱动或修改数据时，就需要修改源代码

连接方式二

// 方式二：对方式一的迭代:在如下的程序中不出现第三方的api,使得程序具有更好的可移植性
@Test
public void testConnection2() throws Exception {
    // 1.使用反射，加载Driver实现类对象：
    Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
    // 2.创建驱动(实现类)对象
    Driver driver = (Driver) aClass.newInstance();
    String url = "jdbc:mysql://localhost:3306/test";
    String user= "root";
    String password = "12345";
    Properties info = new Properties();
    info.setProperty("user", user);
    info.setProperty("password", password);
    Connection connect = driver.connect(url, info);
    System.out.println(connect);
}

相较于方式一，这里仅仅使用反射实例化Driver，不在代码中体现第三方数据库的API。

连接方式三

// 方式三：使用DriverManager替换Driver
@Test
public void testConnection3() throws Exception {
    String url = "jdbc:mysql://localhost:3306/test";
    String user = "root";
    String password = "12345";
    Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
    Driver driver = (Driver) aClass.newInstance();
    // 注册驱动
    DriverManager.registerDriver(driver);
    Connection connection = DriverManager.getConnection(url, user, password);
    System.out.println(connection);
}

相较于方式二，这里不需要直接去访问实现了 Driver 接口的类（即通过Driver获取连接)，而是在驱动程序管理器类(java.sql.DriverManager)中注册驱动Driver，然后去调用这些Driver实现（即通过DriverManager获取数据库连接)。

连接方式四

// 方式四：可以只是加载驱动，不用显示的注册驱动。
@Test
public void testConnection4() throws Exception {
    String url = "jdbc:mysql://localhost:3306/test";
    String user = "root";
    String password = "12345";
    Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
    Connection connection = DriverManager.getConnection(url, user, password);
    System.out.println(connection);
}

不必显式的创建Driver对象和注册驱动了。因为在Driver的源码中已经存在静态代码块(静态代码快回随着类的加载而加载)，实现了Driver对象的创建以及对驱动的注册。

连接方式五(最终版)

上面四种方式，都是把数据库相关信息硬编码在代码中，这样每当需要修改数据库信息，就需要修改代码。

例如在打包项目部署时，如果修改的源码，就需要重新打包项目，项目大的情况下，是很费时的

下面采用的方式五，是把代码和数据的分离，使用配置文件的方式保存数据，在代码中加载配置文件，好处如下

• 如果需要修改配置信息，直接在配置文件中修改，不需要深入代码
• 如果修改了配置信息，省去重新编译的过程（即重新打包)。

在工程的src目录下创建配置文件：【jdbc.properties】

user=root
password=abc123
url=jdbc:mysql://localhost:3306/test
driverClass=com.mysql.jdbc.Driver

//方式五(final版)：将数据库连接需要的4个基本信息声明在配置文件中，通过读取配置文件的方式，获取连接
/*
* 此种方式的好处？
* 1.实现了数据与代码的分离。实现了解耦
* 2.如果需要修改配置文件信息，可以避免程序重新打包。
*/
@Test
public void testConnection5() throws Exception {
    //1.加载配置文件
    InputStream is = ConnectionTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
    Properties properties = new Properties();
    properties.load(is);
    //2.读取配置信息
    String classDriver = properties.getProperty("driverClass");
    String url = properties.getProperty("url");
    String user = properties.getProperty("user");
    String password = properties.getProperty("password");
    //3.加载驱动
    Class.forName(classDriver);
    //4.获取连接
    Connection connection = DriverManager.getConnection(url, user, password);
    System.out.println(connection);
}

相较于其他方式，方式五是通过系统类加载器的getResourceAsStream方法获取类路径下配置文件的输入流来加载配置文件信息的

2.3 URL

JDBC URL 用于标识一个被注册的驱动程序，通过这个 URL 选择正确的驱动程序，从而建立到数据库的连接。

JDBC URL的标准由三部分组成，各部分间用冒号分隔。

• 主协议：JDBC URL中的主协议总是jdbc
• 子协议：子协议用于标识一个数据库驱动程序
• 子名称：一种标识数据库的方法。子名称可以依不同的子协议而变化，用子名称的目的是为了定位数据库提供足够的信息。包含主机名(对应服务端的ip地址)，端口号，数据库名

举例：

几种常用数据库的 JDBC URL

MySQL的连接URL编写方式：jdbc:mysql://主机名称:mysql服务端口号/数据库名称?参数=值&参数=值

• jdbc:mysql://localhost:3306/zhuo(连接到指定得数据库zhuo)
• jdbc:mysql://localhost:3306/atguigu?useUnicode=true&characterEncoding=utf8（如果JDBC程序与服务器端的字符集不一致，会导致乱码，那么可以通过参数指定服务器端的字符集）
• jdbc:mysql://localhost:3306/atguigu?user=root&password=123456（连接数据库时指定用户和密码)

Oracle 9i的连接URL编写方式：jdbc:oracle:thin:@主机名称:oracle服务端口号:数据库名称

• jdbc:oracle:thin:@localhost:1521:zhuo

SQLServer的连接URL编写方式：jdbc:sqlserver://主机名称:sqlserver服务端口号:DatabaseName=数据库名称

• jdbc:sqlserver://localhost:1433:DatabaseName=zhuo

三、PreparedStatement vs Statement

3.1 使用Statement操作数据表的弊端及缺点

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

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

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

• 问题一：存在拼串操作，繁琐
• 问题二：存在SQL注入问题

SQL 注入是利用某些系统没有对用户输入的数据进行充分的检查，例如在用户登录的sql检索语句中，可以利用拼串来绕过用户登录检查

用户的登录检索语句
使用拼串绕过检查

代码演示

public class StatementTest {

    // 使用Statement的弊端：需要拼写sql语句，并且存在SQL注入的问题
    public static void main(String[] args) {
        Scanner scan = new Scanner(System.in);
        System.out.print("用户名：");
        String userName = scan.nextLine();
        System.out.print("密   码：");
        String password = scan.nextLine();
        String sql = "SELECT user,password FROM user_table WHERE USER = '1' or ' AND PASSWORD = '='1' or '1' = '1'";
        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.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;
    }
}

在Statement语句中，由于Statement是充当信使的，不能预编译，即每当有一条Statement语句,它便会送到数据库中执行，也就是说用户可以利用拼串来注入sql过滤条件来绕过检查。

使用Statement操作数据表的缺点：

不能操作Blog型数据

在Statement操作数据库中，Statement填充数据只能通过拼串，而BLOB类型的数据无法使用字符串拼接写的（blog数据需要填充IO流)

批量操作性能差

在Statement语句中，Statement是不能预编译的，即使是相同操作，但由于数据内容不一样,整个语句本身还是不能匹配,因此没有缓存语句的意义，批量操作性能差

事实上没有任何数据库会对普通语句编译后的执行代码缓存(即Statement送到数据库的sql语句)。这样每执行一次Statement语句(sql）都要对传入的语句编译一次。

3.2 PreparedStatement相比Statement的优点

PreparedStatement

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

PreparedStatement 可以防止 SQL 注入

PreparedStatement语句是预编译语句，那为什么可以防止SQL注入呢？

因为PreparedStatement不再通过拼串的方式注入数据，而是通过占位符的方式来填充数据，这样当sql被预编译后，其sql语句就可以被确定下来，不管后面如何填充数据，最终的数据都会被认为是数据，而不是关键字（sql过滤条件等)

PreparedStatement可以实现更高效的批量操作

DBServer会对预编译语句提供性能优化。因为预编译语句有可能被重复调用，所以语句在被DBServer的编译器编译后的执行代码被缓存下来，那么下次调用时只要是相同的预编译语句就不需要编译，只要将参数直接传入编译过的语句执行代码中就会得到执行。

而批量操作执行的预编译语句是相同的，只是填充的数据不同，因此会被缓存下来，这样就可以实现更高效的批量操作

PreparedStatement可以操作Blog型数据

PreparedStatement是通过占位符来填充数据的，因此可以给单独的blog字段通过io流的方式写入数据

四、使用PreparedStatement实现CRUD操作

4.1 操作和访问数据库

数据库连接被用于向数据库服务器发送命令和 SQL 语句，并接受数据库服务器返回的结果。其实一个数据库连接就是一个Socket连接。

在 java.sql 包中有 3 个接口分别定义了对数据库的调用的不同方式：

• Statement： 用于执行静态 SQL 语句并返回它所生成结果的对象（有sql注入风险，已被PrepatedStatement替代)。
• PrepatedStatement： SQL 语句被预编译并存储在此对象中，可以使用此对象多次高效地执行该语句。
• CallableStatement： 用于执行 SQL 存储过程

4.2 使用PreparedStatement实现增、删、改操作(迭代)

由于增删改操作基本是一样的，所有下面采用insert语句来演示增删改查的迭代

版本一

@Test
 public void testInsert()  {
     Connection connection = null;
     PreparedStatement preparedStatement = null;
     try {
         // 获取系统类加载器，调用系统类加载得器得getResourceAsStream方式加载类路径的文件获取输入流
         InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
         // 获取配置文件信息
         Properties properties = new Properties();
         properties.load(is);
         String driverClass = properties.getProperty("driverClass");
         String url = properties.getProperty("url");
         String user = properties.getProperty("user");
         String password = properties.getProperty("password");
         // 加载驱动
         Class.forName(driverClass);
         // 调用驱动管理器获取数据库连接
         connection = DriverManager.getConnection(url, user, password);
         String sql = "insert into customers(name,email,birth) value(?,?,?)";
         // 预编译sql
         preparedStatement = connection.prepareStatement(sql);
         // 填充占位符
         preparedStatement.setString(1, "迪丽热巴");
         preparedStatement.setString(2, "stormzhuo@163.com");
         SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
         Date date = simpleDateFormat.parse("1998-07-07");
         preparedStatement.setDate(3, new java.sql.Date(date.getTime()));
         // 执行sql语句
         preparedStatement.execute();
     } catch (Exception e) {
         e.printStackTrace();
     } finally {
         try {
             if (preparedStatement != null) {
                 preparedStatement.close();
             }
         } catch (SQLException throwables) {
             throwables.printStackTrace();
         }
         try {
             if (connection != null) {
                 connection.close();
             }
         } catch (SQLException throwables) {
             throwables.printStackTrace();
         }
     }
 }

版本一出现过多的冗余代码，例如对其他表进行增删改操作时，需要重复输入以上代码，因此需要把对其他表进行增删改的相同操作的代码抽取出来构造一个方法

版本二

通过版本一可以知道，无论对那个表进行增删改查，都需要先获取数据库连接，然后关闭资源，因此可以把这些相同的操作抽取成方法放在工具类中

JDBCUtils.java

public class JDBCUtils {
    
    // 获取数据库连接
    public static Connection getConnection() throws Exception {
        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
        Properties pros = new Properties();
        pros.load(is);
        String driverClass = pros.getProperty("driverClass");
        String url = pros.getProperty("url");
        String user = pros.getProperty("user");
        String password = pros.getProperty("password");
        Class.forName(driverClass);
        Connection conn = DriverManager.getConnection(url, user, password);
        return conn;
    }
 
    // 关闭资源
    public static void closeResource(Connection conn, Statement stat) {
        try {
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        try {
            if (stat != null) {
                stat.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}

测试

@Test
public void testUpdate() {
    Connection conn = null;
    PreparedStatement ps = null;
    try {
   		 //1.获取数据库的连接
        conn = JDBCUtils.getConnection();
        //2.预编译sql语句，返回PreparedStatement的实例
        String sql = "update customers set name=? where id=?";
        ps = conn.prepareStatement(sql);
        //3.填充占位符
        ps.setObject(1, "仓老师");
        ps.setObject(2, 18);
        //4.执行
        ps.execute();
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
    		 //5.资源的关闭
        JDBCUtils.closeResource(conn, ps);
    }
}

版本二经过优化后，减少了获取数据库连接和关闭资源的冗余代码

版本三（通用的增删改操作)

对数据库的增删改首先需要获取连接，然后预编译sql语句，最后填充占位符。这过程只有填充占位符的数量不同，而填充占位符可以使用可变形参来解决，因此可以这些操作抽取出来成为一个通用的增删改操作

//通用的增删改操作
public void update(String sql, Object ... args) {
    Connection conn = null;
    PreparedStatement ps = null;
    try {
        //1.获取数据库的连接
        conn = JDBCUtils.getConnection();
        //2.预编译sql语句，返回PreparedStatement的实例
        ps = conn.prepareStatement(sql);
        //3.填充占位符
        for (int i = 0; i < args.length; i++) {
            //小心参数声明错误！！数据库索引是从1开始的
            ps.setObject(i + 1, args[i]);
        }
        //4.执行
        ps.execute();
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        //5.资源的关闭
        JDBCUtils.closeResource(conn, ps);
    }
}

版本三是一个通用的增删改操作，即可以对任何一个表的任意字段进行操作。对于不同的字段填充问题，采用可变参数

4.3 使用PreparedStatement实现查询操作(迭代)

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

4.3.2 ResultSet与ResultSetMetaData

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开始。

ResultSetMetaData

得到结果集ResultSet后, 如何知道该结果集中有哪些列 ？ 列名是什么？

需要使用一个描述 ResultSet 的对象， 即 ResultSetMetaData(元数据)

关于ResultSetMetaData

1. 如何获取 ResultSetMetaData： 调用 ResultSet 的 getMetaData() 方法即可
2. 获取 ResultSet 中有多少列：调用 ResultSetMetaData 的 getColumnCount() 方法
3. 获取 ResultSet 每一列的列的列名或别名是什么：调用 ResultSetMetaData 的getColumnName或getColumnLabel() 方法

ResultSetMetaData其他方法

• getColumnTypeName(int column)：检索指定列的数据库特定的类型名称。

• getColumnDisplaySize(int column)：指示指定列的最大标准宽度，以字符为单位。

• isNullable(int column)：指示指定列中的值是否可以为 null。

• isAutoIncrement(int column)：指示是否自动为指定列进行编号，这样这些列仍然是只读的。

4.3.3 表的的查询操作(迭代)

对单个表的的查询操作(迭代)

版本一

@Test
public void testQuery()  {
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
        conn = JDBCUtils.getConnection();
        String sql = "SELECT id,name,email,birth FROM customers WHERE id=?";
        ps = conn.prepareStatement(sql);
        ps.setObject(1, "2");
        rs = ps.executeQuery();
        if (rs.next()) {
            Integer id = rs.getInt(1);
            String name = rs.getString(2);
            String email = rs.getString(3);
            Date birth = rs.getDate(4);
            Customers customers = new Customers(id, name, email, birth);
            System.out.println(customers);
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        JDBCUtils.closeResource(conn, ps, rs);
    }
}

版本一对单个表的查询不具有通用性，即如果改变了sql语句的查询字段，则结果集返回的字段也会改变，因此在结果集封装在实体类的代码也需要修改

版本二(单个表通用查询)

版本一查询操作包含很多冗余代码，即获取数据库连接，预编译sql，填充占位符，执行sql获取结果集，查询到的结果集封装在实体类中，因此可以把这些操作单独抽取出来成为一个对单个表的通用的查询方法

填充占位符可以通过可变形参来解决，但是把结果集封装在实体类较麻烦，因为对单个表的不同字段查询，返回的结果集是不同的。

对单个表的不同字段查询得到的结果集，可以通过getMetaData获取元数据ResultSetMetaData，再通过元数据获取结果集的包含的列数，列数知道了就可以遍历列数获取每一列的值

但是呢，每一列的值我们不知道对应的是那一列的列名，因此再遍历过程中还需要通过元数据获取当前列值对应的列名，最后通过反射把数据封装再实体类中

// 通用的针对于customers表的查询操作
public Customers customersForQuery(String sql, Object ... args)  {
    Connection conn = null;
    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();
        //获取列数
        if (rs.next()) {
            Customers customers = new Customers();
            //获取结果集的列数
            int columnCount = rsmd.getColumnCount();
            for (int i = 0; i < columnCount; i++) {
                //获取每个列的列值:通过ResultSet
                Object columnValue = rs.getObject(i + 1);
                //获取列的别名：getColumnLabel()
                String columnName = rsmd.getColumnLabel(i + 1);
                //通过反射，将对象指定名getColumnLabel的属性赋值为指定的值columnValue
                Field field = Customers.class.getDeclaredField(columnName);
                field.setAccessible(true);
                field.set(customers, columnValue);
            }
            return customers;
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        JDBCUtils.closeResource(conn, ps, rs);
    }
    return null;
}

测试

@Test
public void testQueryForCustomer() {
    String sql = "SELECT id,name,email,birth FROM customers WHERE id=?";
    Customers customers = customersForQuery(sql, 6);
    System.out.println(customers);
    sql = "SELECT email,birth FROM customers WHERE name=?";
    customers = customersForQuery(sql, "周杰伦");
    System.out.println(customers);
}

对不同表的的通用的查询操作（返回表中的一条记录）

对不同表的查询操作类似于对单个表的查询操作，主要在于封装的实体类不同，即查询不同的表，需要返回相应表的实体，因此可以使用泛型方法。

泛型方法返回的值是一个泛型实体，实际的类型由调用者决定。即需要在泛型方法中提供一个泛型型参，让调用方法者提供具体的实体类

// 针对于不同的表的通用的查询操作，返回表中的一条记录
public <T> T getInstance(Class<T> aClass, String sql, Object ... args) {
    Connection conn = null;
    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
        ResultSetMetaData rsmd = rs.getMetaData();
        if (rs.next()) {
            // 通过Class实例获取对象，即反射
            T t = aClass.newInstance();
            // 通过ResultSetMetaData获取结果集中的列数
            int columnCount = rsmd.getColumnCount();
            // 处理结果集一行数据中的每一个列
            for (int i = 0; i < columnCount; i++) {
                // 获取列值
                Object columnValue = rs.getObject(i + 1);
                // 获取每个列的列名
                String columnLabel = rsmd.getColumnLabel(i + 1);
                // 给t对象指定的columnLabel属性，赋值为columValue：通过反射
                Field field = aClass.getDeclaredField(columnLabel);
                field.setAccessible(true);
                field.set(t, columnValue);
            }
            return t;
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        JDBCUtils.closeResource(conn, ps, rs);
    }
    return null;
}

测试

@Test
public void testGetInstance() {
    String sql = "SELECT id,name,email,birth FROM customers WHERE id=?";
    Customers customers = getInstance(Customers.class, sql, 12);
    System.out.println(customers);
    sql = "SELECT order_id as orderId,order_name as orderName,order_date as orderDate FROM `order` WHERE order_id=?";
    Order order = getInstance(Order.class, sql, 1);
    System.out.println(order);
}

对不同表的的通用的查询操作（返回表中的多条记录）

如果返回多条记录，即查询返回的结果集包含多条记录，则需要把if( rs.next() )改成while( rs.next() )，最后把封装好的实体类添加到List集合中即可

public <T> List<T> getForList(Class<T> aClass, String sql, Object ... args)  {
    Connection conn = null;
    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();
        //创建集合对象
        List<T> list = new ArrayList<>();
        // 获取结果集的元数据 :ResultSetMetaData
        ResultSetMetaData rsmd = rs.getMetaData();
        while (rs.next()) {
            T t = aClass.newInstance();
            // 通过ResultSetMetaData获取结果集中的列数
            int columnCount = rsmd.getColumnCount();
            // 处理结果集一行数据中的每一个列:给t对象指定的属性赋值
            for (int i = 0; i < columnCount; i++) {
                // 获取列值
                Object columnValue = rs.getObject(i + 1);
                // 获取每个列的列名
                String columnLabel = rsmd.getColumnLabel(i + 1);
                // 给t对象指定的columnName属性，赋值为columValue：通过反射
                Field field = t.getClass().getDeclaredField(columnLabel);
                field.setAccessible(true);
                field.set(t, columnValue);
            }
            list.add(t);
        }
        return list;
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        JDBCUtils.closeResource(conn, ps, rs);
    }
    return null;
}

测试

@Test
public void testGetForList() {
    String sql = "SELECT id,name,email,birth FROM customers WHERE id<?";
    List<Customers> customersList = getForList(Customers.class, sql, 12);
    customersList.forEach(System.out::println);
}

4.4 资源的释放

目前需要释放的资源有三个ResultSet, Statement,Connection，因此在JDBCUtils可以写个通用的关闭资源的方法，如下

public static void closeResource(Connection conn, Statement stat, ResultSet rs) {
    try {
        if (conn != null) {
            conn.close();
        }
    } catch (SQLException throwables) {
        throwables.printStackTrace();
    }
    try {
        if (stat != null) {
            stat.close();
        }
    } catch (SQLException throwables) {
        throwables.printStackTrace();
    }
    try {
        if (rs != null) {
            rs.close();
        }
    } catch (SQLException throwables) {
        throwables.printStackTrace();
    }
}

数据库连接（Connection）是非常稀有的资源，用完后必须马上释放，如果Connection不能及时正确的关闭将导致系统宕机。Connection的使用原则是尽量晚创建，尽量早的释放。

可以在finally中关闭，保证及时其他代码出现异常，资源也一定能被关闭。

4.5 JDBC API小结

两种思想

• 面向接口编程的思想

• ORM思想(object relational mapping)

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

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

两种技术

• JDBC结果集的元数据：ResultSetMetaData
  • 获取列数：getColumnCount()
  • 获取列的别名：getColumnLabel()
• 通过反射，创建指定类的对象，获取指定的属性并赋值

五、操作BLOB类型字段

5.1 MySQL BLOB类型

MySQL中，BLOB是一个二进制大型对象，是一个可以存储大量数据的容器，它能容纳不同大小的数据。

插入BLOB类型的数据必须使用PreparedStatement，因为BLOB类型的数据无法使用字符串拼接写的。

MySQL的四种BLOB类型(除了在存储的最大信息量上不同外，他们是等同的),如下

需要注意的是：如果存储的文件过大，数据库的性能会下降。

如果在指定了相关的Blob类型以后，还报错：xxx too large，那么在mysql的安装目录下，找my.ini文件加上如下的配置参数： max_allowed_packet=16M。同时注意：修改了my.ini文件之后，需要重新启动mysql服务。

5.2 向数据表中插入大数据类型

@Test
public void testInsert()  {
    Connection conn = null;
    PreparedStatement ps = null;
    try {
        conn = JDBCUtils.getConnection();
        String sql = "INSERT INTO customers(name,email,birth,photo) values(?,?,?,?)";
        ps = conn.prepareStatement(sql);
        ps.setObject(1, "仓老师");
        ps.setObject(2, "stormzhuo@163.com");
        ps.setObject(3,"1998-07-07");
        // 操作Blob类型的变量
        FileInputStream fis = new FileInputStream(new File("thumb.png"));
        ps.setBlob(4, fis);
        int i = ps.executeUpdate();
        if (i > 0) {
            System.out.println("添加成功");
        } else {
            System.out.println("添加失败");
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
    JDBCUtils.closeResource(conn, ps);
}

首先通过字节节点输入流读取文件数据，然后通过PreparedStatement的setBlog方法把文件数据写入Blog字段

5.2 从数据表中读取大数据类型

@Test
public void testQuery() {
    InputStream is = null;
    FileOutputStream fos = null;
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
        conn = JDBCUtils.getConnection();
        String sql = "SELECT id,name,email,birth,photo FROM customers WHERE id=?";
        ps = conn.prepareStatement(sql);
        ps.setObject(1, 16);
        rs = ps.executeQuery();
        if (rs.next()) {
            int id = rs.getInt("id");
            String name = rs.getString("name");
            String email = rs.getString("email");
            Date birth = rs.getDate("birth");
            Customers customers = new Customers(id, name, email, birth);
            System.out.println(customers);
            //读取Blob类型的字段
            Blob photo = rs.getBlob("photo");
            // 通过Blob的getBinaryStream获取字节输入流
            is = photo.getBinaryStream();
            // 创建字节节点输出流
            fos = new FileOutputStream("zhuyin.jpg");
            byte[] buffer = new byte[1024];
            int len;
            while ((len = is.read(buffer)) != -1) {
                fos.write(buffer, 0, len);
            }
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        try {
            if (is != null) {
                is.close();
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        try {
            if (fos != null) {
                fos.close();
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        JDBCUtils.closeResource(conn, ps, rs);
    }
}

首先通过结果集ResultSet的getBlob方法获取Blog字段对象，然后调用Blog的getBinaryStream方法获取字节输入流，最后创建字节节点输出流输出Blog数据到指定的文件

六、批量操作

6.1 批量处理SQL语句

批处理

批处理就是将一批一批SQL语句的处理，而不是一条一条语句进行处理。如当你有多条SQL语句要执行时，一次向服务器发送一条语句，这样虽然也可以达到效果，但是效率很差，而处理这个问题就可以使用批处理(即是一次向服务器发送多条SQL语句，然后让服务器一次性处理)，批处理只针对更新语句(新增、删除、修改)，所有批处理跟查询没有关系。

当需要成批插入或者更新记录时，可以采用Java的批量更新机制，这一机制允许多条语句一次性提交给数据库批量处理。通常情况下比单独提交处理更有效率

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

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

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

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

6.2 高效的批量插入(迭代)

举例：向数据表中插入20000条数据

• 数据库中提供一个goods表。创建如下：

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

准备工作

mysql服务器默认是关闭批处理的，我们需要通过一个参数，让mysql开启批处理的支持。

?rewriteBatchedStatements=true 写在配置文件的url后面

需要使用更新的mysql 驱动：mysql-connector-java-5.1.37-bin.jar

实现层次一：使用Statement

@Test
public void testInsert1() throws Exception {
    Connection conn = null;
    Statement statement = null;
    try {
        long start = System.currentTimeMillis();
        conn = JDBCUtils.getConnection();
        statement = conn.createStatement();
        for (int i = 1; i <= 20000; i++) {
            String sql = "INSERT INTO goods(name) values('name_" + i + "')";
            //1.“攒”sql
            statement.addBatch(sql);
            if (i % 500 == 0) {
                //2.执行
                statement.executeBatch();
                //3.清空
                statement.clearBatch();
            }
        }
        long end = System.currentTimeMillis();
        System.out.println("执行耗时:" + (end - start));
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        JDBCUtils.closeResource(conn, statement);
    }
}

执行耗时

使用Statement对象虽然也可以实现批处理，但是这种方式效率太低，因为它所有的SQL语句都是没有预编译的，而数据库对没用预编译的sql语句是不提供缓存的

实现层次二：使用PreparedStatement

@Test
public void testInsert3() {
    Connection conn = null;
    PreparedStatement ps = null;
    try {
        long start = System.currentTimeMillis();
        conn = JDBCUtils.getConnection();
        String sql = "INSERT INTO goods(name) values(?)";
        ps = conn.prepareStatement(sql);
        for (int i = 1; i <= 20000; i++) {
            ps.setObject(1, "name_" + i);
            //1.“攒”sql
            ps.addBatch();
            if (i % 500 == 0) {
                //2.执行
               ps.executeBatch();
                //3.清空
               ps.clearBatch();
            }
        }
        long end = System.currentTimeMillis();
        System.out.println("执行耗时:" + (end - start));
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        JDBCUtils.closeResource(conn, ps);
    }
}

执行耗时

PreparedStatement能够预编译sql语句，而数据库对预编译sql支持缓存，因此效率较高

实现层次三：PreparedStatement（优化)

层次二是攒到一定的sql数量后送到数据库中去执行，由于使用的是PreparedStatement，数据库会对相同的sql进行缓存，因此批量操作效率较高

那么可不可以攒完全部sql语句后再送去sql执行呢?

由于我们是通过取余条件来攒sql的，当满足条件便会执行批量sql语句，那么优化空间就是在执行批量sql语句时，阻止它提交数据。

DML(增删改操作)默认时提交数据的，即每一次执行excute方法都会向数据库中提交，因此需要在执行excute方法之前关闭自动提交，在赞完全部sql语句后在提交数据

@Test
public void testInsert4() throws Exception {
    Connection conn = null;
    PreparedStatement ps = null;
    try {
        long start = System.currentTimeMillis();
        conn = JDBCUtils.getConnection();
        //1.设置为不自动提交数据
        conn.setAutoCommit(false);
        String sql = "INSERT INTO goods(name) values(?)";
        ps = conn.prepareStatement(sql);
        for (int i = 1; i <= 20000; i++) {
            ps.setObject(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));
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        JDBCUtils.closeResource(conn, ps);
    }
}

执行耗时

七、数据库事务

7.1 数据库事务介绍

事务： 一组逻辑操作单元,使数据从一种状态变换到另一种状态。

事务处理（事务操作）： 保证所有事务都作为一个工作单元来执行，即使出现了故障，都不能改变这种执行方式。当在一个事务中执行多个操作时，要么所有的操作都被提交(commit)，那么这些修改就永久地保存下来；要么数据库管理系统将放弃所作的所有修改，整个事务回滚(rollback)到最初状态。

为确保数据库中数据的一致性，数据的操纵应当是离散的成组的逻辑单元：当它全部完成时，数据的一致性可以保持，而当这个单元中的一部分操作失败，整个事务应全部视为错误，所有从起始点以后的操作应全部回退到开始状态。

7.2 JDBC事务处理

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

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

JDBC程序中为了让多个 SQL 语句作为一个事务执行，需要做如下几个事情

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

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

【案例：用户AA向用户BB转账100】

我们可以使用前面的通用的增删改操作update方法，但是update执行完时是会关闭数据库连接的，要想支持事务，需要对update进行修改

//使用事务以后的通用的增删改操作
public int update(Connection conn, String sql, Object ... args)  {
    PreparedStatement ps = null;
    try {
        // 1.获取PreparedStatement的实例 (或：预编译sql语句)
        ps = conn.prepareStatement(sql);
        // 2.填充占位符
        for (int i = 0; i < args.length; i++) {
            ps.setObject(i + 1, args[i]);
        }
        // 3.执行sql语句
        return ps.executeUpdate();
    } catch (SQLException throwables) {
        throwables.printStackTrace();
    } finally {
        // 4.关闭资源
        JDBCUtils.closeResource(null, ps);
    }
    return 0;
}

下面进行模拟用户AA向BB转账100快，若转账成功，则AA会减少100，而BB会增加100

不使用事务的情况

@Test
public void testUpdate() {
    String sql = "UPDATE user_table SET balance=balance-100 WHERE user=?";
    int i = update(sql, "AA");
    // 模拟网络异常
    System.out.println(10 / 0);
    sql = "UPDATE user_table SET balance=balance+100 WHERE user=?";
    int i2 = update(sql, "BB");
    System.out.println("转账成功");
}

AA向BB转账100操作会执行两条sql语句，一条是AA减100的操作，另一条是BB加100操作。当AA减100的操作执行完时，我们模拟了网络异常，此时BB加100的操作不会执行

使用事务的情况

让多个 SQL 语句作为一个事务执行,首先需要保证多个SQL在同一连接下执行，因为数据库连接关闭时，数据会自动提交。

其次需要关闭数据自动提交，因为DML(insert,delete,update)默认是自动提交数据的，即执行增删改操作后，数据会自动提交到数据库中去

使用了事务之后，当在执行过程中出现异常，由于数据没用提交的数据库，因此可以回滚到原来的初始状态

@Test
public void testUpdateWithTransaction()  {
    Connection conn = null;
    try {
        // 1.获取数据库连接
        conn = JDBCUtils.getConnection();
        // 2.关闭数据自动提交，开启事务
        conn.setAutoCommit(false);
        // 3.进行数据库操作
        String sql = "UPDATE user_table SET balance=balance-100 WHERE user=?";
        int i1 = update(conn, sql, "AA");
        // 模拟网络异常
        System.out.println(10 / 0);
        sql = "UPDATE user_table SET balance=balance+100 WHERE user=?";
        int i2 = update(conn, sql, "BB");
        // 4.若没有异常，则提交事务
        conn.commit();
        System.out.println("转账成功");
    } catch (Exception e) {
        e.printStackTrace();
        try {
            //6.恢复每次DML操作的自动提交功能
            conn.rollback();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    } finally {
        //7.关闭连接
        JDBCUtils.closeResource(conn, null);
    }
}

7.3 事务的ACID属性

1. 原子性（Atomicity）
   原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

2. 一致性（Consistency）
   事务必须使数据库从一个一致性状态变换到另外一个一致性状态。

3. 隔离性（Isolation）
   事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

4. 持久性（Durability）
   持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响。

7.3.1 数据库的并发问题

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

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

7.3.2 四种隔离级别

数据库事务的隔离性: 数据库系统必须具有隔离并发运行各个事务的能力, 使它们不会相互影响, 避免各种并发问题。

数据库提供的4种事务隔离级别来解决不同的并发问题：

一个事务与其他事务隔离的程度称为隔离级别。数据库规定了多种事务隔离级别, 不同隔离级别对应不同的干扰程度, 隔离级别越高, 数据一致性就越好, 但并发性越弱。

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

Mysql 支持 4 种事务隔离级别。Mysql 默认的事务隔离级别为: REPEATABLE READ。

7.3.3 在Java中设置隔离级别解决并发问题

READ UNCOMMITTED(读未提交数据)

三种并发问题都未解决，即脏读，不可重复读，幻读

下面通过一个查询事务和更新事务作为两个线程来演示脏读，即在一个用户在读表数据之前，另一个用户更新的表数据，但是没提交到数据库（即最终的数据库的数据是没改变的），此时读表用户读取的数据是更新表后的数据，但是更表用户的数据最后是没提交的数据库的，因此产生脏读。

在前面的查询语句中，当执行完语句时，数据库连接是会被关闭的，因此需要改成支持事务的，即不关闭连接。

public <T> T getInstance(Connection conn, Class<T> aClass, 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();
        if (rs.next()) {
            T t = aClass.newInstance();
            int columnCount = rsmd.getColumnCount();
            for (int i = 0; i < columnCount; i++) {
                Object columnValue = rs.getObject(i + 1);
                String columnLabel = rsmd.getColumnLabel(i + 1);
                Field field = aClass.getDeclaredField(columnLabel);
                field.setAccessible(true);
                field.set(t, columnValue);
            }
            return t;
        }
    } catch (Exception throwables) {
        throwables.printStackTrace();
    } finally {
        JDBCUtils.closeResource(null, ps, rs);
    }
    return null;
}

测试代码

首先设置隔离级别为READ UNCOMMITTED,然后关闭数据自动提交

其次当执行sql语句后，不要关闭数据库连接，因为连接关闭会自动提交数据

最后对于更新事务，为了在查询事务能看到明显的脏读，需要在执行完更新事务后使用sleep将该线程阻塞挂起

@Test
public void testTransactionSelect() throws Exception {
    Connection conn = JDBCUtils.getConnection();
    conn.setTransactionIsolation(1);
    conn.setAutoCommit(false);
    String sql = "SELECT user, password, balance FROM user_table WHERE user=?";
    User user = getInstance(conn, User.class, sql, "CC");
    System.out.println(user);
}

@Test
public void testTransactionUpdate() throws Exception {
    Connection conn = JDBCUtils.getConnection();
    conn.setTransactionIsolation(1);
    conn.setAutoCommit(false);
    String sql = "UPDATE user_table SET balance=? WHERE user=?";
    int cc = update(conn, sql, 5000, "CC");
    Thread.sleep(15000);
    System.out.println("更新成功");
}

首先执行select语句时，此时查询出来的balance为2000

之后执行update语句后在执行select语句时，此时查询出来的balance为5000

最后update操作完成后，再次查询出来的balance变为原来的2000

READ COMMITED(读已提交数据)

解决了脏读，但不可重复读和幻读未解决

只需要在上面代码修改隔离级别即可，如下

一般只需要解决脏读问题就行，而不可重复读和幻读问题的解决需要牺牲并发性能，因此后面两个级别REPEATABLE READ(可重复读)，SERIALIZABLE(串行化)不作演示

八、数据库连接池

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

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

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

这种模式开发，存在的问题:

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

8.2 数据库连接池技术

为解决传统开发中的数据库连接问题，可以采用数据库连接池技术。

数据库连接池的基本思想：就是为数据库连接建立一个“缓冲池”。预先在缓冲池中放入一定数量的连接，当需要建立数据库连接时，只需从“缓冲池”中取出一个，使用完毕之后再放回去。

数据库连接池负责分配、管理和释放数据库连接，它允许应用程序重复使用一个现有的数据库连接，而不是重新建立一个。

数据库连接池在初始化时将创建一定数量的数据库连接放到连接池中，这些数据库连接的数量是由最小数据库连接数来设定的。无论这些数据库连接是否被使用，连接池都将一直保证至少拥有这么多的连接数量。连接池的最大数据库连接数量限定了这个连接池能占有的最大连接数，当应用程序向连接池请求的连接数超过最大连接数量时，这些请求将被加入到等待队列中。

工作原理：

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()并没有关闭数据库的物理连接，它仅仅把数据库连接释放，归还给了数据库连接池。

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

由于数据库连接池的使用都是一个套路，因此这里使用最常用的德鲁伊数据库连接池

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

详细配置参数：

在src下创建配置文件为：【druid.properties】

url=jdbc:mysql://localhost:3306/test
username=root
password=12345
driverClassName=com.mysql.jdbc.Driver

initialSize=10
maxActive=10

通过DruidDataSourceFactory工厂类的静态方法获取数据源，静态方法需要传入一个Properties参数，Properties是用来加载配置信息的，包含了连接数据库信息以及初始化连接池信息等

@Test
public void testGetConnection() throws Exception {
    Properties pro = new Properties();
    // 使用系统加载器的getResourceAsStream加载配置文件
    InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");
    pro.load(is);
    // 调用工厂类获取数据源
    DataSource dataSource = DruidDataSourceFactory.createDataSource(pro);
    Connection conn = dataSource.getConnection();
    System.out.println(conn);
}

8.3 使用德鲁伊连接池替换DriverManager获取连接

在前面，我们是通过DriverManager来获取数据库连接，最主要的缺点就是每次使用时都要自己造一个连接

使用连接池后，每次使用的连接都是从池里获取的，当不需要连接时，程序还是像以前一样关闭数据库连接：conn.close(); 但conn.close()并没有关闭数据库的物理连接，它仅仅把数据库连接释放，归还给了数据库连接池。

使用德鲁伊连接池替换JDBCUtils.java获取数据库连接的操作，如下

//创建一个Druid数据库连接池
private static DataSource ds = null;

static {
    Properties pros = new Properties();
    InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");
    try {
        pros.load(is);
        ds = DruidDataSourceFactory.createDataSource(pros);
    } catch (Exception e) {
        e.printStackTrace();
    }
}


public static Connection getConnection() throws Exception {
    return ds.getConnection();
}

九、Apache-DBUtils实现CRUD

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

9.1 主要API介绍

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

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。

org.apache.commons.dbutils.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语句
• 批处理
  • 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 的创建和关闭。

org.apache.commons.dbutils.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：查询单个值对象

9.2 使用DbUtils替换JDBCUtils.java关闭资源

在之前的JDBCUtils工具类关闭资源的操作中，可以使用DbUtuils工具类来替换，如下

public static void closeResource(Connection conn, Statement stat, ResultSet rs) {
    DbUtils.closeQuietly(conn);
    DbUtils.closeQuietly(stat);
    DbUtils.closeQuietly(rs);
}

使用了DbUtils工具类后，不用捕获异常以及判断资源是否为null，查看源码知道，其实之前我们做的它都帮我做了

9.2 使用QueryRunner类替换通用的增删改查

前面写过的通用的增删改查操作，可以使用QueryRunner相关方法来替换增删改查

使用QueryRunner类的update方法替换通用的增删改操作，如下

@Test
public void testCommonUpdate() {
    Connection conn = null;
    try {
        QueryRunner qr = new QueryRunner();
        conn = JDBCUtils.getConnection();
        String sql = "update `order` set order_name=? where order_id=?";
        qr.update(conn, sql, "BB" , "2");
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        JDBCUtils.closeResource(conn, null, null);
    }
}

通过查看源码可以知道，update核心代码跟我们自己编写的是一样的，区别在于健壮性比较好

使用QueryRunner类的query方法替换通用的查询操作，如下

/*
 * BeanHander:是ResultSetHandler接口的实现类，用于封装表中的一条记录。
 */
@Test
public void testGetInstance() {
    Connection conn = null;
    try {
        QueryRunner qr = new QueryRunner();
        conn = JDBCUtils.getConnection();
        String sql = "SELECT id,name,email,birth FROM customers WHERE id=?";
        BeanHandler<Customers> bh = new BeanHandler<>(Customers.class);
        Customers customers = qr.query(conn, sql, bh, 1);
        System.out.println(customers);
        sql = "SELECT order_id as orderId,order_name as orderName,order_date as orderDate FROM `order` WHERE order_id=?";
        BeanHandler<Order> bh1 = new BeanHandler<>(Order.class);
        Order order = qr.query(conn, sql, bh1, 1);
        System.out.println(order);
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        JDBCUtils.closeResource(conn ,null, null);
    }
}

/*
 * BeanListHandler:是ResultSetHandler接口的实现类，用于封装表中的多条记录构成的集合。
 */
@Test
public void testGetForList() {
    Connection conn = null;
    try {
        QueryRunner qr = new QueryRunner();
        conn = JDBCUtils.getConnection();
        String sql = "SELECT id,name,email,birth FROM customers WHERE id<?";
        BeanListHandler<Customers> blh = new BeanListHandler<>(Customers.class);
        List<Customers> customersList = qr.query(conn, sql, blh, 12);
        customersList.forEach(System.out::println);
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        JDBCUtils.closeResource(conn, null, null);
    }
}

十、Dao设计模式

DAO(Data Access Object)称为数据访问对象，Dao设计模式可以在使用数据库的应用程序实现业务逻辑和数据访问逻辑的分离，从而使应用的维护变得简单。它通过将数据访问实现（通常使用JDBC技术)封装在Dao类中，提高应用程序的灵活性

在Dao模式有很多变体，这里介绍一种比较简单的形式，首先定义一个BaseDao抽象类并声明通用的增删改查方法，可以使用Apache-DBUtils

然后为每种实体的持久化操作定义一个接口，如CustomersDao接口负责Customers对象的持久化, UserDao接口负责User对象的持久化，最后定义这些接口的实现类，实现类继承BaseDao抽象类，因此可以使用BaseDao定义的通用的增删改查方法

在BaseDao通用的查询操作，需要传入具体类的Class实例，出现了具体实体类名称，因此可以进一步优化

首先把BaseDao声明为泛型类

然后在子类继承BaseDao时不保留泛型，而是指定具体的类型

而这个类型正是我们传入方法的参数，那么怎么获取这个类型呢？

可以使用反射，首先调用实现类的getClass方法获取Class实例，然后调用Class实例的getGenericSuperClass方法获取父类的泛型，最后调用泛型的getActualTypeArguments方法获取父类的泛型参数数组

这个类型存在与非静态方法参数中，因此调用方法之前这个类型就必须确认，因此可以把获取这个类型的操作声明在非静态代码块中

经过优化后，查询操作就不需要传入具体的类型，而是通过反射动态获取

完整代码

BaseDao.java

// 定义一个用来被继承的对数据库进行基本操作的Dao
public abstract class BaseDao<T> {

    // 定义一个变量来接收泛型的类型
    private Class<T> aClass = null;

    // 获取T的Class对象，获取泛型的类型，泛型是在被子类继承时才确定
    {
        //获取当前BaseDAO的子类继承的父类中的泛型
        Type gs = this.getClass().getGenericSuperclass();
        ParameterizedType pt = (ParameterizedType) gs;
        //获取了父类的泛型参数
        Type[] ata = pt.getActualTypeArguments();
        //泛型的第一个参数
        aClass = (Class<T>) ata[0];
    }


    // 通用的增删改操作 （考虑上事务）
    public int update(Connection conn, String sql, Object... args) { // sql中占位符的个数与可变形参的长度相同！
        int i = 0;
        try {
            QueryRunner qr = new QueryRunner();
            i = qr.update(conn, sql, args);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return i;
    }

    // 通用的查询操作，用于返回数据表中的一条记录（考虑上事务）
    public T getInstance(Connection conn,  String sql, Object... args)  {
        T t = null;
        try {
            QueryRunner qr = new QueryRunner();
            BeanHandler<T> bh = new BeanHandler<>(aClass);
            t = qr.query(conn, sql, bh, args);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return t;
    }

    // 通用的查询操作，用于返回数据表中的多条记录构成的集合（考虑上事务）
    public  List<T> getForList(Connection conn,  String sql, Object... args)  {
        List<T> list = null;
        try {
            QueryRunner qr = new QueryRunner();
            BeanListHandler<T> blh = new BeanListHandler<>(aClass);
            list = qr.query(conn, sql, blh, args);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return list;
    }

    //用于查询特殊值的通用的方法
    public <T> T getValue(Connection conn, String sql, Object... args)  {
        Object obj = null;
        try {
            QueryRunner qr = new QueryRunner();
            ScalarHandler sh = new ScalarHandler();
            obj = qr.query(conn, sql, sh, args);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return (T) obj;
    }
}

下面给出为Customers对象的持久化的接口以及实现类

CustomersDao.java

public interface CustomersDao {

    // 将cust对象添加到数据库中
    void addCus(Connection conn, Customers customers);

    // 针对指定的id，删除表cust中的一条记录
    int deleteCusById(Connection conn, Integer id);

    // 针对内存中的cust对象，去修改数据表中指定的记录
    int updateCus(Connection conn, Customers customers);

    // 针对指定的id查询得到对应的Customer对象
    Customers getCusById(Connection conn, Integer id);

    // 查询表cust中的所有记录构成的集合
    List<Customers> getAllCus(Connection conn);

    // 返回数据表cust中的数据的条目数
    Long getCount(Connection conn);

    // 返回数据表cust中最大的生日
    Date getMaxBirth(Connection conn);
}

实现类

CustomersDaoImpl.java

public class CustomersDaoImpl extends BaseDao<Customers> implements CustomersDao {

    @Override
    public void addCus(Connection conn, Customers customers) {
        String sql = "INSERT INTO customers(name,email,birth) value(?,?,?)";
        update(conn, sql, customers.getName(), customers.getEmail(), customers.getBirth());
    }

    @Override
    public int deleteCusById(Connection conn, Integer id) {
        String sql = "DELETE FROM customers WHERE id=?";
        return update(conn, sql, id);
    }

    @Override
    public int updateCus(Connection conn, Customers customers) {
        String sql = "UPDATE customers SET name=?,email=?,birth=? WHERE id=?";
        int i = update(conn, sql, customers.getName(), customers.getEmail(), customers.getBirth(), customers.getId());
        return i;
    }

    @Override
    public Customers getCusById(Connection conn, Integer id) {
        String sql = "SELECT id,name,email,birth FROM customers WHERE id=?";
        return getInstance(conn,  sql, id);
    }

    @Override
    public List<Customers> getAllCus(Connection conn) {
        String sql = "SELECT id,name,email,birth FROM customers";
        return getForList(conn,  sql);
    }

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

使用JUnit自动生成测试类

在项目下创建与src同级的test文件夹，右击test，执行如下操作

下载JUnitGenerator插件

右击要测试的类，执行如下操作

测试类如下

class CustomersDaoImplTest {

    CustomersDao customersDao = new CustomersDaoImpl();
    @Test
    void testAddCus() {
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection();
            Customers customers = new Customers(1, "路飞", "lufei@163.com", new Date(13213123213L));
            customersDao.addCus(conn, customers);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, null, null);
        }
    }

    @Test
    void deleteCusById()  {
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection();
            int i = customersDao.deleteCusById(conn, 1);
            System.out.println(i);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, null, null);
        }
    }

    @Test
    void updateCus() {
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection();
            Customers customers = new Customers(2, "迪丽热巴", "dilireba@163.com", new Date(1312134213L));
            int i = customersDao.updateCus(conn, customers);
            System.out.println(i);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, null, null);
        }
    }

    @Test
    void getCusById() throws Exception {
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection();
            Customers customers = customersDao.getCusById(conn, 2);
            System.out.println(customers);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, null, null);
        }
    }

    @Test
    void getAllCus() {
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection();
            List<Customers> list = customersDao.getAllCus(conn);
            list.forEach(System.out::println);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, null, null);
        }
    }

    @Test
    void getCount() {
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection();
            Long count = customersDao.getCount(conn);
            System.out.println(count);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, null, null);
        }
    }

    @Test
    void getMaxBirth() {
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection();
            Date maxBirth = customersDao.getMaxBirth(conn);
            System.out.println(maxBirth);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, null, null);
        }
    }
}