JDBC工具类

1、jdbc.properties

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

2、JDBCUtils(未考虑事物)

public class JdbcUtils {

    /**
     * 获取一个连接
     * @return
     * @throws Exception
     */
    public static Connection getConnection() throws Exception {

        // 1.获取配置文件信息
        InputStream resource = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");

        Properties properties = new Properties();
        properties.load(resource);
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String url = properties.getProperty("url");
        String driverClass = properties.getProperty("driverClass");

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

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

    /**
     * 关闭连接
     * @param connection
     * @param statement
     */
    public static void closeConnection(Connection connection, Statement statement) {
        if (connection !=  null){
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (statement != null){
            try {
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    /**
     * 关闭连接
     * @param connection
     * @param statement
     */
    public static void closeConnection(Connection connection, Statement statement, ResultSet resultSet) {
        if (resultSet != null){
            try {
                resultSet.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (connection !=  null){
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (statement != null){
            try {
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }


    /**
     * @auther: zqq
     * @date: 21/1/12 11:49
     * @Description: query
     * @param sql 执行的sql
     * @param clazz 查询的结果的类型
     * @param args sql的参数
     * @return: List<T>
     */
    public static  <T> List<T> query(String sql, Class<T> clazz, Object ...args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        ArrayList<T> list = null;
        try {
            // 1.获取一个连接
            connection = getConnection();

            // 2.预编译一个sql语句，返回一个PrepareStatement对象
            preparedStatement = connection.prepareStatement(sql);
            // 3.填充占位符
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i+1, args[i]);
            }
            // 4。执行sql,得到结果集
            resultSet = preparedStatement.executeQuery();
            // 5.获取元数据
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            // 6.获取一个类的反射实例
            T t = clazz.newInstance();
            list = new ArrayList();
            // 7.遍历得到每一行数据
            while (resultSet.next()){
                for (int i = 0; i < columnCount; i++) {
                    // 7.1获取列值
                    Object object = resultSet.getObject(i + 1);
                    // 7.2获取列别名
                    String columnLabel = metaData.getColumnLabel(i + 1);
                    // 7.3获取属性并设置属性的值
                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t, object);
                }
                list.add(t);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeConnection(connection, preparedStatement, resultSet);
        }

        return list;
    }

    /**
     * @auther: zqq
     * @date: 21/1/12 11:52
     * @Description: update 更新
     * @param sql 执行的sql
     * @param obj 可变参数
     * @return: int 改变的条数
     */
    public static int update(String sql, Object ...obj){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            // 1.获取连接
            connection = getConnection();
            // 2.预编译sql，返回一个PrepareStatement实例
            preparedStatement = connection.prepareStatement(sql);
            // 3.填充占位符
            for (int i = 0; i < obj.length; i++) {
                preparedStatement.setObject(i+1, obj[i]);
            }
            // 4.执行
            return preparedStatement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 5.关闭资源
            closeConnection(connection, preparedStatement);
        }
        return 0;
    }
}

测试使用

public class PreparedStatementTest {
    
    @Test
    public void testUpdate(){
        String sql = "update `order` set order_name = ? where order_id = ?";
        JdbcUtils.update(sql, "BB", "2");
    }



    
    @Test
    public void testQuery(){
        String sql = "select order_id orderId, order_name orderName, order_date orderDate from `order` where order_id = ?";


        List<Order> query = JdbcUtils.query(sql, Order.class, 1);

        System.out.println("query = " + query.toString());
    }


}

3、JdbcUtils（考虑事物）

public class JdbcUtils {

    /**
     * 获取一个连接
     * @return
     * @throws Exception
     */
    public static Connection getConnection() throws Exception {

        // 1.获取配置文件信息
        InputStream resource = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");

        Properties properties = new Properties();
        properties.load(resource);
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String url = properties.getProperty("url");
        String driverClass = properties.getProperty("driverClass");

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

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

    /**
     * 关闭连接
     * @param connection
     * @param statement
     */
    public static void closeConnection(Connection connection, Statement statement) {
        if (connection !=  null){
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (statement != null){
            try {
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    /**
     * 关闭连接
     * @param connection
     * @param statement
     */
    public static void closeConnection(Connection connection, Statement statement, ResultSet resultSet) {
        if (resultSet != null){
            try {
                resultSet.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (connection !=  null){
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (statement != null){
            try {
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }


    public static  <T> List<T> query(Connection connection, String sql, Class<T> clazz, Object ...args) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        ArrayList<T> list = null;
        try {
            // 2.预编译一个sql语句，返回一个PrepareStatement对象
            preparedStatement = connection.prepareStatement(sql);
            // 3.填充占位符
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i+1, args[i]);
            }
            // 4。执行sql,得到结果集
            resultSet = preparedStatement.executeQuery();
            // 5.获取元数据
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            // 6.获取一个类的反射实例
            T t = clazz.newInstance();
            list = new ArrayList();
            // 7.遍历得到每一行数据
            while (resultSet.next()){
                for (int i = 0; i < columnCount; i++) {
                    // 7.1获取列值
                    Object object = resultSet.getObject(i + 1);
                    // 7.2获取列别名
                    String columnLabel = metaData.getColumnLabel(i + 1);
                    // 7.3获取属性并设置属性的值
                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t, object);
                }
                list.add(t);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeConnection(null, preparedStatement, resultSet);
        }
        return list;
    }

    public static int update(Connection connection, String sql, Object ...obj){
        PreparedStatement preparedStatement = null;
        try {
            // 2.预编译sql，返回一个PrepareStatement实例
            preparedStatement = connection.prepareStatement(sql);
            // 3.填充占位符
            for (int i = 0; i < obj.length; i++) {
                preparedStatement.setObject(i+1, obj[i]);
            }
            // 4.执行
            return preparedStatement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 5.关闭资源
            closeConnection(null, preparedStatement);
        }
        return 0;
    }
}

事物发生：

• DDL操作一旦执行，都会自动提交。
• DML默认情况下，一旦执行，就会自动提交。（可以通过set autocommit = false的方式取消DML操作的自动提交。）
• 默认在关闭连接时，会自动的提交数据

在之前的代码中 每次执行sql语句，最后都会去关闭一次连接，导致数据被提交，考虑到事务，则关闭连接操作自行关闭，例如：

 public void testJDBCTransaction() {
        Connection conn = null;
        try {
            // 1.获取数据库连接
            conn = JdbcUtils.getConnection();
            // 2.开启事务
            conn.setAutoCommit(false);
            // 3.进行数据库操作
            String sql1 = "update user_table set balance = balance - 100 where user = ?";
            update(conn, sql1, "AA");

            // 模拟网络异常
            //throw new Exception()

            String sql2 = "update user_table set balance = balance + 100 where user = ?";
            update(conn, sql2, "BB");
            // 4.若没有异常，则提交事务
            conn.commit();
        } catch (Exception e) {
            e.printStackTrace();
            // 5.若有异常，则回滚事务
            try {
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        } finally {
            try {
                //6.恢复每次DML操作的自动提交功能
                conn.setAutoCommit(true);
            } catch (SQLException e) {
                e.printStackTrace();
            }
            //7.关闭连接
            JdbcUtils.closeConnection(conn, null, null);
        }
    }