JDBC工具类

DBUtil.java

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import java.sql.*;
import java.util.List;
import java.util.Properties;

/**
 * 加载数据库驱动程序
 * 获得数据库链接
 * 单例模式
 * 满足开闭原则
 */
public class DBUtil {

    private String driver;
    private String url;
    private String username;
    private String password;

    private Connection conn;
    private PreparedStatement ps;
    private ResultSet rs;

    private static DBUtil util = null;

    /**
     * 构造器私有
     */
    private DBUtil() {
        try {
            /*
             * 加载配置文件
             */
            Properties pro = new Properties();
            pro.load(this.getClass().getClassLoader().getResourceAsStream("db.properties"));
            /*
             * 获取配置信息
             */
            driver = pro.getProperty("driver");
            url = pro.getProperty("url");
            username = pro.getProperty("username");
            password = pro.getProperty("password");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 懒汉模式
     */
    public synchronized static DBUtil getInstance() {
        if (util == null) {
            util = new DBUtil();
        }
        return util;
    }

    /**
     * 加载驱动程序并获取数据库连接
     * @return 数据库连接对象
     * @throws Exception
     */
    public Connection getConnection() throws Exception {
        // 获取连接数据库驱动程序，并加载至JVM内存
        // Class.forName()方法将参数指定类加载至方法区
        Class.forName(driver);
        conn = DriverManager.getConnection(url, username, password);
        return conn;
    }

    /**
     * 通过连接池获取数据库连接
     * @return 数据库连接对象
     */
    public Connection getPoolConnection() {
        try {
            Context context = new InitialContext();
            DataSource ds = (DataSource) context.lookup("java:/comp/env/jdbc");
            conn = ds.getConnection();
        } catch (NamingException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

    /**
     * 开始事务
     */
    public void beginTranaction() {
        try {
            conn.setAutoCommit(false);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 提交事务
     */
    public void commit() {
        try {
            conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 回滚事务
     */
    public void rollback() {
        try {
            conn.rollback();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 提交更新
     * @param sql
     * @return 影响记录数
     */
    public int executeUpdate(String sql) {
        int count = 0;
        try {
            ps = conn.prepareStatement(sql);
            count = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }

    /**
     * 提交更新
     * @param sql
     * @param obj
     * @return 影响记录数
     */
    public int executeUpdate(String sql, Object[] obj) {
        int count = 0;
        try {
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < obj.length; i++) {
                ps.setObject(i + 1, obj[i]);
            }
            count = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }

    /**
     * 提交查询
     * @param sql
     * @return 结果集
     */
    public ResultSet executeQuery(String sql) {
        try {
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rs;
    }

    /**
     * 提交查询
     * @param sql
     * @param obj
     * @return 结果集
     */
    public ResultSet executeQuery(String sql, Object[] obj) {
        try {
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < obj.length; i++) {
                ps.setObject(i + 1, obj[i]);
            }
            rs = ps.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rs;
    }

    /**
     * 批处理
     * @param sql
     * @param list
     */
    public void executeBatch(String sql, List<Object[]> list) {
        try {
            ps = conn.prepareStatement(sql);
            for (Object[] obj : list) {
                for (int i = 0; i < obj.length; i++) {
                    ps.setObject(i + 1, obj[i]);
                }
                ps.addBatch();
            }
            ps.executeBatch();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 关闭连接对象
     */
    public void close(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 关闭语句对象
     */
    public void close(Statement stat) {
        if (stat != null) {
            try {
                stat.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 关闭结果集
     */
    public void close(ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

配置文件db.properties：

# 配置信息采取name = value的形式
# 1.配置驱动程序
driver = com.mysql.cj.jdbc.Driver
# 2.配置数据库url
url = jdbc:mysql://localhost:3306/scott?useUnicode=true&characterEncoding=UTF-8
# 3.配置用户名
username = root
# 4.配置用户密码
password = root