高级应用--JDBC（二）

7. 封装JDBC抽取DBUtils
8. JDBC事务
9. 数据库连接池讲解

---

7.封装JDBC抽取DBUtils

• 为了使得编辑参数更加方便，首先将数据库连接参数抽取出来放在配置文件jdcb.properties中

jdbc.driver = com.mysql.cj.jdbc.Driver
jdbc.url = jdbc:mysql://localhost:3306/test?useSSL=false&serverTimezone=UTC
jdbc.user = root
jdbc.password = 123456

• 类加载的时候加载该配置文件，并且注册驱动类信息

    private static Properties properties;
    // 使用静态代码块保证在类加载的时候立即加载对应的配置文件
    static {
        properties = new Properties();
        try {
            InputStream ins = JDBCUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
            properties.load(ins);
            System.out.println("load config file finish");

            Class.forName(properties.getProperty("jdbc.driver"));

        } catch (FileNotFoundException e) {
            System.out.println("jdbc config file not found " + e.getMessage());
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            System.out.println("jdbc class not found " + e.getMessage());
        }
    }

完整抽取代码

import org.apache.commons.beanutils.BeanUtils;

import java.io.*;
import java.lang.reflect.InvocationTargetException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

/**
 * Created by Huanfeng.Xu on 2017-07-09.
 */
public class JDBCUtils {

    private static Properties properties;
    static {
        properties = new Properties();
        try {
            InputStream inputStream = JDBCUtils.class.getResourceAsStream("jdbc.properties");
            InputStream ins = JDBCUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
            properties.load(ins);
            System.out.println("load config file finish");

            Class.forName(properties.getProperty("jdbc.driver"));

        } catch (FileNotFoundException e) {
            System.out.println("jdbc config file not found " + e.getMessage());
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            System.out.println("jdbc class not found " + e.getMessage());
        }
    }

    /**
     * 获得数据库连接Connection
     * @return Connection 数据库连接
     */
    private static Connection getConnection(){

        Connection connection = null;
        try {
          connection =  DriverManager.getConnection(
                  properties.getProperty("jdbc.url"),
                  properties.getProperty("jdbc.user"),
                  properties.getProperty("jdbc.password"));
        } catch (SQLException e) {
            System.out.println("cannot get connection " + e.getMessage());
        }
        return connection;
    }


    /**
     * 设置参数
     * @param preparedStatement Statement对象
     * @param param 参数列表
     * @return
     * @throws SQLException
     */
    private static boolean settingParams(PreparedStatement preparedStatement, Object[] param) throws SQLException {

        if (param != null && param.length > 0){
            ParameterMetaData parameterMetaData = preparedStatement.getParameterMetaData();
            int paramCount = parameterMetaData.getParameterCount();

            if (paramCount != param.length){
                return false;
            }

            for (int i = 0; i < paramCount; i++){
                preparedStatement.setObject(i+1, param[i]);
            }
        }
        return true;
    }


    /**
     * 更新操作
     * @param sql 执行的SQL语句
     * @param param 对应的参数列表
     * @return
     */
    public static boolean update(String sql, Object[] param){

        PreparedStatement preparedStatement = null;
        Connection connection = getConnection();
        try {
            preparedStatement = connection.prepareStatement(sql);

            if (settingParams(preparedStatement, param) == false){
                return false;
            }

            int result = preparedStatement.executeUpdate();
            if (result > 0){
                return true;
            }
            return false;

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            close(connection, preparedStatement);
        }
        return false;
    }

    /**
     * 获取单个Bean
     * @param sql 执行SQL语句
     * @param param 对应的参数列表
     * @param clazz 所要获取的对象的类型
     * @param <T>  对象的类型
     * @return bean
     */
    public static <T> T queryForBean(String sql, Object[] param, Class<T> clazz){

        Connection connection = getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {

            preparedStatement = connection.prepareStatement(sql);

            if (settingParams(preparedStatement, param) == false){
                return null;
            }

            resultSet = preparedStatement.executeQuery();
            if (resultSet == null){
                return null;
            }

            if (resultSet.next()){

                T data = clazz.newInstance();
                ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
                int columnCount = resultSetMetaData.getColumnCount();
                for (int i = 0; i < columnCount; i++){
                    String name = resultSetMetaData.getColumnName(i + 1);
                    Object rData = resultSet.getObject(name);
                    BeanUtils.copyProperty(data, name, rData);
                }
             return data;

            }else {
                return null;
            }

        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } finally {
            close(connection, preparedStatement, resultSet);
        }
        return null;
    }

    /**
     * 获取Bean并且封装成List
     * @param sql 执行SQL语句
     * @param param 对应的参数列表
     * @param clazz 所要获取的对象的类型
     * @param <T>  对象的类型
     * @return list
     */
    public static <T> List<T> queryForList(String sql, Object[] param, Class<T> clazz){

        Connection connection = getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {

            preparedStatement = connection.prepareStatement(sql);

            if (settingParams(preparedStatement, param) == false){
                return null;
            }

            resultSet = preparedStatement.executeQuery();
            if (resultSet == null){
                return null;
            }
            List<T> results = new ArrayList<>();

            while (resultSet.next()){

                T data = clazz.newInstance();
                ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
                int columnCount = resultSetMetaData.getColumnCount();
                for (int i = 0; i < columnCount; i++){
                    String name = resultSetMetaData.getColumnName(i + 1);
                    Object rData = resultSet.getObject(name);
                    BeanUtils.copyProperty(data, name, rData);
                }
                results.add(data);

            }
            return results;

        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } finally {
            close(connection, preparedStatement, resultSet);
        }
        return null;
    }

    /**
     * 关闭connection
     * @param connection 连接池对象
     */
    private static void close(Connection connection){
        if (connection != null){
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 关闭Statement
     * @param statement
     */
    private static void close(Statement statement){
        if (statement != null){
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 关闭ResultSet
     * @param resultSet
     */
    private static void close(ResultSet resultSet){
        if (resultSet != null){
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 关闭Connection 以及Statement
     * @param connection
     * @param statement
     */
    private static void close(Connection connection, Statement statement){
        close(connection);
        close(statement);
    }

    /**
     * 关闭Connection，Statement以及ResultSet
     * @param connection
     * @param statement
     * @param resultSet
     */
    private static void close(Connection connection, Statement statement, ResultSet resultSet){
        close(connection, statement);
        close(resultSet);
    }
}

8JDBC事务

8.1 为什么要使用事务？

答：如果不使用事务，在一个逻辑下添加的代码如果出现错误则全部不生效，全部成功才能生效写入数据库

• 比如说我要插入两条数据，但是其中有一条写错了，不开启事务的话第一条生效第二条不生效
• 如果开启了事务，如果有一条语句出错则都不生效

示例代码（不开启事务）

try {

            Class.forName(DRIVER);
            Connection connection = DriverManager.getConnection(URL,USERNAME,PASSWORD);

            String trueSQL = "insert into test1 values(null,?)";
            String falseSQL = "inser into test1 values(null,?)"; //这段代码的插入拼写错误少了个t
            PreparedStatement ps = connection.prepareStatement(trueSQL);
            PreparedStatement ps1 = connection.prepareStatement(falseSQL);
            ps.setString(1,"011");
            ps.execute();

            ps1.setString(1,"013");
            ps1.execute();
       
            ps1.close();
            ps.close();
            connection.close();

        } catch (SQLException | ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

结果演示

显示已经有一条数据插入进去了

image.png image.png

8.2 如何开启事务？

在代码中添加两行代码即可

• connection.setAutoCommit(false);关闭自动提交事务
• connection.commit();手动提交事务

示例程序（开启事务）

try {
            Class.forName(DRIVER);
            Connection connection = DriverManager.getConnection(URL,USERNAME,PASSWORD);
            //开启手动提交事务
            connection.setAutoCommit(false);

            String addSQL = "insert into test1 values(null,?)";
            String addSQL1 = "inser into test1 values(null,?)";
            PreparedStatement ps = connection.prepareStatement(addSQL);
            PreparedStatement ps1 = connection.prepareStatement(addSQL1);
            ps.setString(1,"011");
            ps.execute();

            ps1.setString(1,"013");
            ps1.execute();
            //手动提交
            connection.commit();

            ps1.close();
            ps.close();
            connection.close();

        } catch (SQLException | ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

结果演示

数据显示没有添加成功

image.png

程序显示报错

image.png

---

9.数据库连接池

9.1 为什么要使用数据库连接池？

image.png image.png

9.2 数据库连接池推荐

基本上使用Druid连接池进行项目准备