[大数据]JDBC

JDBC

JDBC程序步骤

1. 导入java.sql包
2. 加载MySQL驱动
3. 创建Connection对象 建立和数据库的连接
4. 创建Statement对象 操作数据库 增删改查
5. 创建ResultSet对象 获取查询结果集
6. 关闭Statement对象 关闭Connection对象

建立和数据库的连接

方式一

    public void testConnection() throws SQLException {
        // 加载驱动
        Driver driver = new com.mysql.cj.jdbc.Driver();

        // jdbc:mysql: 协议
        // localhost: ip地址
        // 3306: mysql端口号
        // test: 数据库名
        String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8";
        // 将用户名和密码封装在Properties中
        Properties info = new Properties();
        info.setProperty("user", "root");
        info.setProperty("password", "123456");

        Connection connect = driver.connect(url, info);

        System.out.println(connect);
    }

方式二

去除第三方的API

    public void testConnection() throws Exception {
        // 1.获取Driver实现类对象 通过反射的方式 更好的动态性
        Class cls = Class.forName("com.mysql.cj.jdbc.Driver");
        // 创建实例
        Driver driver = (Driver) cls.newInstance();

        // 2.提供要连接的数据库
        String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8";

        // 3.提供链接额需要的用户名和密码
        Properties info = new Properties();
        info.setProperty("user", "root");
        info.setProperty("password", "123456");

        // 4.获取连接
        Connection connect = driver.connect(url, info);

        System.out.println(connect);
    }

方式三

通过DriverManager获取连接

    public void testConnection() throws Exception {
        // 1.获取Driver实现类对象 通过反射的方式 更好的动态性
        Class cls = Class.forName("com.mysql.cj.jdbc.Driver");
        Driver driver = (Driver) cls.newInstance();

        // 2.提供另外三个连接的基本信息
        String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8";
        String user = "root";
        String password = "123456";

        // 3.注册驱动
        DriverManager.registerDriver(driver);

        // 4.获得连接
        Connection connection = DriverManager.getConnection(url, user, password);

        System.out.println(connection);
    }

方式四

省略驱动加载

    public void testConnection4() throws Exception {
        // 1.提供连接的基本信息
        String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8";
        String user = "root";
        String password = "123456";

        // 2.加载Driver
        Class.forName("com.mysql.cj.jdbc.Driver");

        // 3.获得连接
        Connection connection = DriverManager.getConnection(url, user, password);

        System.out.println(connection);
    }

原因在于Driver在类加载的时候 会执行静态代码块

public class Driver extends NonRegisteringDriver implements java.sql.Driver {
    public Driver() throws SQLException {
    }

    static {
        try {
            DriverManager.registerDriver(new Driver());
        } catch (SQLException var1) {
            throw new RuntimeException("Can't register driver!");
        }
    }
}

方式五

将数据库所需要的基本信息声明在配置文件中

user=root
password=123456
url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8
driverClass=com.mysql.cj.jdbc.Driver

    public void testConnection5() throws Exception {
        // 1.读取配置文件中的信息
        InputStream is = ConnectionTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
        Properties properties = new Properties();
        properties.load(is);

        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String url = properties.getProperty("url");
        String driverClass = properties.getProperty("driverClass");
        
        // 2.加载Driver
        Class.forName(driverClass);

        // 3.获得连接
        Connection connection = DriverManager.getConnection(url, user, password);

        System.out.println(connection);
    }

优点：实现了数据和代码的分离 耦合性低

操作数据库

Statement弊端

1. 存在拼串操作 繁琐

   String sql = "select * from users where `NAME` = '" + username + "' AND `password` = '" + password + "'";
   

2. 存在SQL注入问题

   什么是SQL注入问题？SQL 注入是利用某些系统没有对用户输入的数据进行充分的检查，而在用户输入数据中注入非法的 SQL 语句段或命令(如：SELECT user, password FROM user_table WHERE user=‘a’ OR 1 = ’ AND password = ’ OR ‘1’ = ‘1’) ，从而利用系统的 SQL 引擎完成恶意行为的做法。

   String user = " 'or '1=1";
   String password = "123456";
   

   此时sql语句为

   select * from users where `NAME` = ' 'or '1=1' AND `password` = '123456'
   

   由于 '1'='1' 始终为true 所以可以获取 password 为123456的所有用户的信息

   同理

   select user, password from user_table where user='a' OR 1 = ' AND password = ' OR '1' = '1'
   

   由于 '1'='1' 始终为true 所以判断条件为 user = ‘a’ or 1 = ’ AND password = ’ or true 始终为true

PreparedStatement

PreparedStatement vs Statement

• 代码的可读性和可维护性。
• PreparedStatement 能最大可能提高性能：
  • DBServer会对预编译语句提供性能优化。因为预编译语句有可能被重复调用，所以语句在被DBServer的编译器编译后的执行代码被缓存下来，那么下次调用时只要是相同的预编译语句就不需要编译，只要将参数直接传入编译过的语句执行代码中就会得到执行。
  • 在statement语句中,即使是相同操作但因为数据内容不一样,所以整个语句本身不能匹配,没有缓存语句的意义.事实是没有数据库会对普通语句编译后的执行代码缓存。这样每执行一次都要对传入的语句编译一次。
  • (语法检查，语义检查，翻译成二进制命令，缓存)
• PreparedStatement 可以防止 SQL 注入
• PreparedStatement 接口是 Statement 的子接口，它表示一条预编译过的 SQL 语句
• Statement无法操作Blob类型数据
• Statement批量插入效率低

实现增、删、改操作

工具类

public class JDBCUtils {
    /**
     * 获取数据库连接
     * @return
     * @throws Exception
     */
    public static Connection getConnection() throws Exception{
        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
        Properties properties = new Properties();
        properties.load(is);

        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String url = properties.getProperty("url");
        String driverClass = properties.getProperty("driverClass");

        Class.forName(driverClass);

        Connection connection = DriverManager.getConnection(url, user, password);
        return connection;
    }

    /**
     * 关闭资源
     * @param connection
     * @param ps
     * @throws Exception
     */
    public static void closeResource(Connection connection, PreparedStatement ps) throws Exception{
        connection.close();
        ps.close();
    }

    public static void closeResource(Connection connection, PreparedStatement ps, ResultSet rs) throws Exception{
        connection.close();
        ps.close();
        rs.close();
    }
}

    public void update(String sql, Object ...args) throws Exception {
        // 获取数据库连接
        Connection connection = JDBCUtils.getConnection();
        // 预编译sql语句 返回preparedStatement实例
        PreparedStatement ps = connection.prepareStatement(sql);
        // 填充占位符
        for (int i = 0; i < args.length; i++) {
            ps.setObject(i+1, args[i]);
        }
        // 执行
        ps.execute();
        //关闭连接
        JDBCUtils.closeResource(connection, ps);
    }

实现查询操作

返回单条数据

/**
     * 针对不同的表的通用查询记录
     * @param cls
     * @param sql
     * @param args
     * @param <T>
     * @return
     * @throws Exception
     */
    public <T> T getInstance(Class<T> cls, String sql, Object ...args) throws Exception {
        Connection connection = JDBCUtils.getConnection();
        PreparedStatement ps = connection.prepareStatement(sql);

        for (int i = 0; i < args.length; i++) {
            ps.setObject(i + 1, args[i]);
        }

        ResultSet rs = ps.executeQuery();

        //获取结果集的元数据
        ResultSetMetaData metaData = rs.getMetaData();
        //获取列数
        int columnCount = metaData.getColumnCount();
        if (rs.next()) {
            T t = cls.newInstance();
            for (int i = 0; i < columnCount; i++) {
                // 获取每一列的值 通过ResultSet
                Object columnValue = rs.getObject(i + 1);

                // 获取每一列的别名 通过ResultSetMetaData
                String columnLabel = metaData.getColumnLabel(i + 1);
                // 获取表的列名 不推荐使用
                //String columnName = metaData.getColumnName(i + 1);

                // 通过反射 将对象指定属性赋值
                Field field = cls.getDeclaredField(columnLabel);
                field.setAccessible(true);
                field.set(t, columnValue);
            }
            return t;
        }
        JDBCUtils.closeResource(connection,ps,rs);

        return null;
    }

返回多条数据

    /**
     * 针对不同的表获取多条记录
     * @param cls
     * @param sql
     * @param args
     * @param <T>
     * @return
     * @throws Exception
     */
    public <T> List<T> getForList(Class<T> cls, String sql, Object ...args) throws Exception {
        Connection connection = JDBCUtils.getConnection();
        PreparedStatement ps = connection.prepareStatement(sql);
        // 创建集合对象
        ArrayList<T> list = new ArrayList<T>();

        for (int i = 0; i < args.length; i++) {
            ps.setObject(i + 1, args[i]);
        }

        ResultSet rs = ps.executeQuery();

        //获取结果集的元数据
        ResultSetMetaData metaData = rs.getMetaData();
        //获取列数
        int columnCount = metaData.getColumnCount();
        while (rs.next()) {
            T t = cls.newInstance();
            for (int i = 0; i < columnCount; i++) {
                // 获取每一列的值 通过ResultSet
                Object columnValue = rs.getObject(i + 1);

                // 获取每一列的别名 通过ResultSetMetaData
                String columnLabel = metaData.getColumnLabel(i + 1);
                // 获取表的列名 不推荐使用
                //String columnName = metaData.getColumnName(i + 1);

                // 通过反射 将对象指定属性赋值
                Field field = cls.getDeclaredField(columnLabel);
                field.setAccessible(true);
                field.set(t, columnValue);

                list.add(t);
            }
        }
        JDBCUtils.closeResource(connection,ps,rs);

        return list;
    }

操作Blob类型

Blob类型

• MySQL中，BLOB是一个二进制大型对象，是一个可以存储大量数据的容器，它能容纳不同大小的数据。
• 插入BLOB类型的数据必须使用PreparedStatement，因为BLOB类型的数据无法使用字符串拼接写的。
• MySQL的四种BLOB类型(除了在存储的最大信息量上不同外，他们是等同的)
• 实际使用中根据需要存入的数据大小定义不同的BLOB类型。
• 需要注意的是：如果存储的文件过大，数据库的性能会下降。
• 如果在指定了相关的Blob类型以后，还报错：xxx too large，那么在mysql的安装目录下，找my.ini文件加上如下的配置参数： max_allowed_packet=16M。同时注意：修改了my.ini文件之后，需要重新启动mysql服务。

插入Blob类型字段

    public void testInsert() throws Exception {
        Connection connection = JDBCUtils.getConnection();
        String sql = "insert into customers(name,email,birth,photo) values(?,?,?,?)";
        PreparedStatement ps = connection.prepareStatement(sql);

        ps.setObject(1, "2次元");
        ps.setObject(2, "2ciyuan@qq.com");
        ps.setObject(3, "1999-09-09");
        FileInputStream is = new FileInputStream(new File("F:\\Saved Pictures\\风景图\\1.jpg"));
        ps.setBlob(4, is);

        ps.execute();

        JDBCUtils.closeResource(connection, ps);
    }

读取Blob类型字段

    public void testQuery() throws Exception {
        InputStream is = null;
        FileOutputStream fos = null;
        Connection connection = JDBCUtils.getConnection();
        String sql = "select id,name,email,birth,photo from customers where id=?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setObject(1, 24);

        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            int id = (int) rs.getObject(1);
            String name = (String) rs.getObject(2);
            String email = (String) rs.getObject(3);
            Date birth = (Date) rs.getObject(4);
            Customer customer = new Customer(id, name, email, birth);

            // 将Blob字段以文件方式保存在本地
            Blob photo = rs.getBlob("photo");
            is = photo.getBinaryStream(); //读入文件流
            fos = new FileOutputStream("1.jpg"); //写出文件流
            byte[] buffer = new byte[1024];
            int len = 0;
            while ((len = is.read(buffer)) != -1) {
                fos.write(buffer, 0, len);
            }
            System.out.println(customer);
        }

        is.close();
        fos.close();
        JDBCUtils.closeResource(connection, ps, rs);
    }

批量插入

批量执行sql语句

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

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

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

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

使用PreparedStatement批量插入

    /**
     * 1. 
     * addBatch() executeBatch() clearBatch()   速度极大提升
     * mysql服务器默认是关闭批处理的 我们需要通过一个参数 让mysql开启批处理的支持
     *      ?rewriteBatchedStatements=true 写在配置文件的url后面
     * 2.
     * 通过关闭自动提交数据来提升速度
     */
@Test
    public void batchTest0() throws Exception{
        Connection connection = JDBCUtils.getConnection();
        // 关闭自动提交数据
        connection.setAutoCommit(false);
        String sql = "insert into goods(name) values(?)";
        PreparedStatement ps = connection.prepareStatement(sql);

        long start = System.currentTimeMillis();
        for (int i = 0; i < 20000; i++) {
            ps.setObject(1, "name_" + i);
            // 1."攒"sql
            ps.addBatch();
            if (i % 500 == 0) {
                // 2.执行Batch
                ps.executeBatch();
                // 3.清空Batch
                ps.clearBatch();
            }
        }
        // 提交数据
        connection.commit();
        long end = System.currentTimeMillis();

        System.out.println(end - start);//

        JDBCUtils.closeResource(connection, ps);
    }

user=root
password=123456
url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&rewriteBatchedStatements=true
driverClass=com.mysql.jdbc.Driver

事务

事务介绍

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

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

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

JDBC操作事务

• 数据一旦提交，就不可回滚。

• 数据什么时候意味着提交？

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

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

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

由于一个连接对象被创建时，默认情况下是自动提交事务 之前采用的增删改查方法都是在方法体内创建连接 此时 调用一次方法相当于执行一个事务

为了避免这种情况 需要在增删改查的方法外创建连接 通过传参的方式将连接传入方法体 保证多个DML语句在一个连接操作中

    @Test
    public void testIsoLevelSelect() throws Exception {
        Connection connection = JDBCUtils.getConnection();
        // 获取当前连接的隔离级别
        System.out.println(connection.getTransactionIsolation());
        // 设置数据库的隔离级别
        connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
        // 取消自动提交数据
        connection.setAutoCommit(false);

        String sql = "select user,password,balance from user_table where user=?";
        User user = getInstance(connection, User.class, sql, "CC");
        System.out.println(user);
    }

    @Test
    public void testIsoLevelUpdate() throws Exception {
        Connection connection = JDBCUtils.getConnection();
        String sql = "update user_table set balance=? where user = ?";
        update(connection, sql, 5000, "CC");
        Thread.sleep(15000);
        System.out.println("修改结束");
    }
    public void update(Connection connection, String sql, Object ...args) throws Exception {
        // 预编译sql语句 返回preparedStatement实例
        PreparedStatement ps = connection.prepareStatement(sql);
        // 填充占位符
        for (int i = 0; i < args.length; i++) {
            ps.setObject(i+1, args[i]);
        }
        // 执行
        ps.execute();
        //关闭连接
        JDBCUtils.closeResource(null, ps);
    }
    public <T> T getInstance(Connection connection, Class<T> cls, String sql, Object ...args) throws Exception {
        connection = JDBCUtils.getConnection();
        PreparedStatement ps = connection.prepareStatement(sql);

        for (int i = 0; i < args.length; i++) {
            ps.setObject(i + 1, args[i]);
        }

        ResultSet rs = ps.executeQuery();

        //获取结果集的元数据
        ResultSetMetaData metaData = rs.getMetaData();
        //获取列数
        int columnCount = metaData.getColumnCount();
        if (rs.next()) {
            T t = cls.newInstance();
            for (int i = 0; i < columnCount; i++) {
                // 获取每一列的值 通过ResultSet
                Object columnValue = rs.getObject(i + 1);

                // 获取每一列的别名 通过ResultSetMetaData
                String columnLabel = metaData.getColumnLabel(i + 1);
                // 获取表的列名 不推荐使用
                //String columnName = metaData.getColumnName(i + 1);

                // 通过反射 将对象指定属性赋值
                Field field = cls.getDeclaredField(columnLabel);
                field.setAccessible(true);
                field.set(t, columnValue);
            }
            return t;
        }
        JDBCUtils.closeResource(null,ps,rs);

        return null;
    }

DAO以及实现类

DAO介绍

DAO： Data Access Object访问数据信息的类和接口，包括了对数据的CRUD（Create、Retrival、Update、Delete），而不包含任何业务相关的信息。有时也称作：BaseDAO

作用：为了实现功能的模块化，更有利于代码的维护和升级。

DAO实现类

public abstract class BaseDAO<T> {

    private Class<T> cls = null;

    {
        // 当子类创建实例时 会加载父类 此时代码块会被调用
        // 由于创建的是子类对象 所以这里的this指的是子类对象

        // 获取当前BaseDAO的子类继承的父类的泛型
        // getGenericSuperclass()用来获取当前类的父类的类型
        // ParameterizedType表示的是带泛型的类型
        Type genericSuperclass = this.getClass().getGenericSuperclass(); // 获取带泛型的父类
        ParameterizedType paramType = (ParameterizedType) genericSuperclass;
        // 获取具体的泛型类型 getActualTypeArguments获取具体的泛型的类型
        // 这个方法会返回一个Type的数组
        Type[] typeArguments = paramType.getActualTypeArguments();//获取父类的泛型操作
        cls = (Class<T>) typeArguments[0];//泛型的第一个参数
    }

    public void update(Connection connection, String sql, Object... args) throws Exception {
        // 预编译sql语句 返回preparedStatement实例
        PreparedStatement ps = connection.prepareStatement(sql);
        // 填充占位符
        for (int i = 0; i < args.length; i++) {
            ps.setObject(i + 1, args[i]);
        }
        // 执行
        ps.execute();
        //关闭连接
        JDBCUtils.closeResource(null, ps);
    }

    public T getInstance(Connection connection, String sql, Object... args) throws Exception {
        connection = JDBCUtils.getConnection();
        PreparedStatement ps = connection.prepareStatement(sql);

        for (int i = 0; i < args.length; i++) {
            ps.setObject(i + 1, args[i]);
        }

        ResultSet rs = ps.executeQuery();

        //获取结果集的元数据
        ResultSetMetaData metaData = rs.getMetaData();
        //获取列数
        int columnCount = metaData.getColumnCount();
        if (rs.next()) {
            T t = cls.newInstance();
            for (int i = 0; i < columnCount; i++) {
                // 获取每一列的值 通过ResultSet
                Object columnValue = rs.getObject(i + 1);

                // 获取每一列的别名 通过ResultSetMetaData
                String columnLabel = metaData.getColumnLabel(i + 1);
                // 获取表的列名 不推荐使用
                //String columnName = metaData.getColumnName(i + 1);

                // 通过反射 将对象指定属性赋值
                Field field = cls.getDeclaredField(columnLabel);
                field.setAccessible(true);
                field.set(t, columnValue);
            }
            return t;
        }
        JDBCUtils.closeResource(null, ps, rs);

        return null;
    }

    public List<T> getForList(Connection connection, String sql, Object... args) throws Exception {
        connection = JDBCUtils.getConnection();
        PreparedStatement ps = connection.prepareStatement(sql);
        // 创建集合对象
        ArrayList<T> list = new ArrayList<T>();

        for (int i = 0; i < args.length; i++) {
            ps.setObject(i + 1, args[i]);
        }

        ResultSet rs = ps.executeQuery();

        //获取结果集的元数据
        ResultSetMetaData metaData = rs.getMetaData();
        //获取列数
        int columnCount = metaData.getColumnCount();
        while (rs.next()) {
            T t = cls.newInstance();
            for (int i = 0; i < columnCount; i++) {
                // 获取每一列的值 通过ResultSet
                Object columnValue = rs.getObject(i + 1);

                // 获取每一列的别名 通过ResultSetMetaData
                String columnLabel = metaData.getColumnLabel(i + 1);
                // 获取表的列名 不推荐使用
                //String columnName = metaData.getColumnName(i + 1);

                // 通过反射 将对象指定属性赋值
                Field field = cls.getDeclaredField(columnLabel);
                field.setAccessible(true);
                field.set(t, columnValue);

                list.add(t);
            }
        }
        JDBCUtils.closeResource(null, ps, rs);

        return list;
    }

    public <E> E getValue(Connection connection, String sql, Object... args) throws SQLException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = connection.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 (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(null, ps, rs);
        }

        return null;
    }
}

定义一个接口来规范操作

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

    /**
     * 根据指定id删除表中记录
     * @param connection
     * @param id
     */
    void deleteById(Connection connection, int id);

    /**
     * 根据customer对象修改表中记录
     * @param connection
     * @param customer
     */
    void update(Connection connection, Customer customer);

    /**
     * 根据指定id查询customer
     * @param connection
     * @param id
     */
    Customer getCustomerById(Connection connection, int id);

    /**
     * 查询表中的所有记录构成的集合
     * @param connection
     * @return
     */
    List<Customer> getAll(Connection connection);

    /**
     * 返回数据表中数据条目数
     * @param connection
     * @return
     */
    Long getCount(Connection connection);

    /**
     * 返回数据表中最大的生日
     * @param connection
     * @return
     */
    Date getMaxBirth(Connection connection);
}

定义实现类

public class CustomerDaoImpl extends BaseDAO<Customer> implements CustomerDAO {
    @Override
    public void insert(Connection connection, Customer customer) {
        String sql = "insert into customers(name,email,birth) values(?,?,?)";
        try {
            update(connection, sql, customer.getName(), customer.getEmail(), customer.getBirth());
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public void deleteById(Connection connection, int id) {
        String sql = "delete from customers where id = ?";
        try {
            update(connection, sql, id);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public void update(Connection connection, Customer customer) {
        String sql = "update customers set name=?, email=?, birth=? where id = ?";
        try {
            update(connection, sql, customer.getName(), customer.getEmail(), customer.getBirth(), customer.getId());
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public Customer getCustomerById(Connection connection, int id) {
        String sql = "select id,name,email,birth from customers where id = ?";
        Customer customer = null;
        try {
            customer = getInstance(connection, sql, id);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return customer;
    }

    @Override
    public List<Customer> getAll(Connection connection) {
        String sql = "select id,name,email,birth from customers";
        List<Customer> customers = null;
        try {
             customers = getForList(connection, sql);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return customers;
    }

    @Override
    public Long getCount(Connection connection) {
        String sql = "select count(*) from customers";
        long value = 0;
        try {
            value = getValue(connection, sql);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return value;
    }

    @Override
    public Date getMaxBirth(Connection connection) {
        String sql = "select max(birth) from customers";
        Date value = null;
        try {
            value = getValue(connection, sql);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return value;
    }
}

此后 如果要对其他数据表实现DAO 则对该表再定义一个接口和实现类

数据库连接池

数据库连接池的必要性

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

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

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

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

数据库连接池技术

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

数据库连接池技术的优点

1. 资源重用

由于数据库连接得以重用，避免了频繁创建，释放连接引起的大量性能开销。在减少系统消耗的基础上，另一方面也增加了系统运行环境的平稳性。

2. 更快的系统反应速度

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

3. 新的资源分配手段

对于多应用共享同一数据库的系统而言，可在应用层通过数据库连接池的配置，实现某一应用最大可用数据库连接数的限制，避免某一应用独占所有的数据库资源

4. 统一的连接管理，避免数据库连接泄漏

在较为完善的数据库连接池实现中，可根据预先的占用超时设定，强制回收被占用连接，从而避免了常规数据库连接操作中可能出现的资源泄露

多种数据库连接池

• JDBC 的数据库连接池使用 javax.sql.DataSource 来表示，DataSource 只是一个接口，该接口通常由服务器(Weblogic, WebSphere, Tomcat)提供实现，也有一些开源组织提供实现：
  • DBCP 是Apache提供的数据库连接池。tomcat 服务器自带dbcp数据库连接池。速度相对c3p0较快，但因自身存在BUG，Hibernate3已不再提供支持。
  • C3P0 是一个开源组织提供的一个数据库连接池，**速度相对较慢，稳定性还可以。**hibernate官方推荐使用
  • Druid 是阿里提供的数据库连接池，据说是集DBCP 、C3P0 、Proxool 优点于一身的数据库连接池，但是速度不确定是否有BoneCP快
• DataSource 通常被称为数据源，它包含连接池和连接池管理两个部分，习惯上也经常把 DataSource 称为连接池
• DataSource用来取代DriverManager来获取Connection，获取速度快，同时可以大幅度提高数据库访问速度。
• 特别注意：
  • 数据源和数据库连接不同，数据源无需创建多个，它是产生数据库连接的工厂，因此整个应用只需要一个数据源即可。
  • 当数据库访问结束后，程序还是像以前一样关闭数据库连接：conn.close(); 但conn.close()并没有关闭数据库的物理连接，它仅仅把数据库连接释放，归还给了数据库连接池。

C3P0数据库连接池

    /**
     * 使用配置文件
     * @throws Exception
     */
    @Test
    public void testGetConnection2() throws Exception {
        // 获取C3P0数据库连接池
        ComboPooledDataSource cpds = new ComboPooledDataSource("helloc3p0"); //自定义配置名

        // 获取连接
        Connection connection = cpds.getConnection();
        System.out.println(connection);
    }

c3p0-config.xml

<?xml version="1.0" encoding="UTF-8"?>

<c3p0-config>
    <named-config name="helloc3p0">
        <!--获取连接的基本信息-->
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/test?useSSL=false</property>
        <property name="user">root</property>
        <property name="password">123456</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">2</property>
    </named-config>
</c3p0-config>

DBCP数据库连接池

• DBCP 是 Apache 软件基金组织下的开源连接池实现，该连接池依赖该组织下的另一个开源系统：Common-pool。如需使用该连接池实现，应在系统中增加如下两个 jar 文件：
  • Commons-dbcp.jar：连接池的实现
  • Commons-pool.jar：连接池实现的依赖库
• **Tomcat 的连接池正是采用该连接池来实现的。**该数据库连接池既可以与应用服务器整合使用，也可由应用程序独立使用。
• 数据源和数据库连接不同，数据源无需创建多个，它是产生数据库连接的工厂，因此整个应用只需要一个数据源即可。
• 当数据库访问结束后，程序还是像以前一样关闭数据库连接：conn.close(); 但上面的代码并没有关闭数据库的物理连接，它仅仅把数据库连接释放，归还给了数据库连接池。

    /**
     * 使用配置文件
     * @throws Exception
     */
    @Test
    public void testGetConnection2() throws Exception {
        Properties props = new Properties();
        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("dbcp.properties");
        props.load(is);

        DataSource dataSource = BasicDataSourceFactory.createDataSource(props);
        Connection connection = dataSource.getConnection();
        System.out.println(connection);
    }

dbcp.properties

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&rewriteBatchedStatements=true&useSSL=false
username=root
password=123456

initialSize=10

Druid数据库连接池

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

    @Test
    public void getConnection() throws Exception{
        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");
        Properties props = new Properties();
        props.load(is);
        DataSource source = DruidDataSourceFactory.createDataSource(props);

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

druid.properties

url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&rewriteBatchedStatements=true&useSSL=false
username=root
password=123456
driverClassName=com.mysql.jdbc.Driver

集成以上数据库连接池

public class JDBCUtils {
    // 获取C3P0数据库连接池
    private static ComboPooledDataSource cpds = new ComboPooledDataSource("helloc3p0"); //自定义配置名

    // 获取DBCP数据库连接池
    private static DataSource source;
    static {
        try {
            Properties props = new Properties();
            InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("dbcp.properties");
            props.load(is);
            source = BasicDataSourceFactory.createDataSource(props);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    // 获取Druid数据库连接池
    private static DataSource druidSource;
    static {
        try {
            InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");
            Properties props = new Properties();
            props.load(is);
            druidSource = DruidDataSourceFactory.createDataSource(props);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    /**
     * 使用C3P0的数据库连接池技术
     * @return
     * @throws SQLException
     */
    public static Connection getC3P0Connection() throws SQLException {
        // 获取连接
        Connection connection = cpds.getConnection();
        return connection;
    }

    /**
     * 使用DBCP的数据库连接池技术
     * @return
     * @throws Exception
     */
    public static Connection getDBCPConnection() throws Exception{
        Connection connection = source.getConnection();

        return connection;
    }

    /**
     * 使用Druid的数据库连接池技术
     * @return
     * @throws Exception
     */
    public static Connection getDruidConnection() throws Exception {
        Connection connection = druidSource.getConnection();
        return connection;
    }
}

DBUtils的使用

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

代码实现

    @Test
    public void testInsert() throws Exception {
        QueryRunner runner = new QueryRunner();

        Connection druidConnection = JDBCUtils.getDruidConnection();
        String sql = "insert into customers(name,email,birth) values(?,?,?)";
        int insertCount = runner.update(druidConnection, sql, "张三", "zhangsan@qq.com", "1999-09-09");
        System.out.println("添加了" + insertCount + "条记录");

        JDBCUtils.closeResource(druidConnection, null);
    }

    /**
     * BeanHandler:是ResultSetHandler接口的实现类 用于封装一条数据
     */
    @Test
    public void testQuery1() {
        Connection druidConnection = null;
        try {
            QueryRunner runner = new QueryRunner();
            druidConnection = JDBCUtils.getDruidConnection();
            String sql = "select id,name,birth from customers where id = ?";
            BeanHandler<Customer> beanHandler = new BeanHandler<>(Customer.class);
            Customer cus = runner.query(druidConnection, sql, beanHandler, 24);
            System.out.println(cus);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(druidConnection, null);
        }
    }

    /**
     * BeanListHandler:是ResultSetHandler接口的实现类 用于封装多条数据构成的集合
     */
    @Test
    public void testQuery2() {
        Connection druidConnection = null;
        try {
            QueryRunner runner = new QueryRunner();
            druidConnection = JDBCUtils.getDruidConnection();
            String sql = "select id,name,birth from customers where id < ?";
            BeanListHandler<Customer> beanListHandler = new BeanListHandler<>(Customer.class);
            List<Customer> list = runner.query(druidConnection, sql, beanListHandler, 24);
            list.forEach(System.out::println);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(druidConnection, null);
        }
    }

    /**
     * MapHandler:是ResultSetHandler接口的实现类 对应表中的一条记录 将字段及相应字段的值作为map中的key和value
     */
    @Test
    public void testQuery3() {
        Connection druidConnection = null;
        try {
            QueryRunner runner = new QueryRunner();
            druidConnection = JDBCUtils.getDruidConnection();
            String sql = "select id,name,birth from customers where id = ?";
            MapHandler handler = new MapHandler();
            Map<String, Object> map = runner.query(druidConnection, sql, handler, 24);
            System.out.println(map);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(druidConnection, null);
        }
    }

    /**
     * MapListHandler:是ResultSetHandler接口的实现类 对应表中的多条记录 将字段及相应字段的值作为map中的key和value
     */
    @Test
    public void testQuery4() {
        Connection druidConnection = null;
        try {
            QueryRunner runner = new QueryRunner();
            druidConnection = JDBCUtils.getDruidConnection();
            String sql = "select id,name,birth from customers where id < ?";
            MapListHandler handler = new MapListHandler();
            List<Map<String, Object>> list = runner.query(druidConnection, sql, handler, 24);
            list.forEach(System.out::println);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(druidConnection, null);
        }
    }

    /**
     * ScalarHandler:用于查询特殊值
     */
    @Test
    public void testQuery5() {
        Connection druidConnection = null;
        try {
            QueryRunner runner = new QueryRunner();
            druidConnection = JDBCUtils.getDruidConnection();
            String sql = "select count(*) from customers";
            ScalarHandler handler = new ScalarHandler<>();
            long count = (long) runner.query(druidConnection, sql, handler);
            System.out.println(count);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(druidConnection, null);
        }
    }

QueryRunner类

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

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 的创建和关闭。

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：**查询单个值对象