MySQL_Java:JDBC 工具类

抽取JDBC的工具类：JDBCUtils
目的：简化书写


分析
1、抽取一个注册驱动

2、抽取一个方法获取连接对象
需求：不想传递参数，还得保证工具类的通用性
解决：配置文件
jdbc.properties
url=
user=
password=

3、抽取一个方法释放资源

代码示例

配置文件
jdbc.properties

url=jdbc:mysql://localhost:3306/db3?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC
user=root
password=*****
driver=com.mysql.cj.jdbc.Driver

jdbc.properties文件要放在JDBCUtils2的上一级目录


创建一个数据库连接类JDBCUtils2

import java.io.FileReader;
import java.io.IOException;
import java.net.URL;
import java.sql.*;
import java.util.Properties;

public class JDBCUtils2 {
    /**
     * 声明三个类型(url,user,password)的成员变量
     */
    private static String url;
    private static String user;
    private static String password;
    private static String driver;


    /**
     * 文件的读取：只需要读取一次即可拿到properties中的这些值
     * 使用静态代码块来完成
     */
    static {
        //读取资源文件，获取值
        try {
            //1、创建properties集合类对象
            Properties pro = new Properties();
            //获取src路径下的文件的方式----->ClassLoader类加载器
            ClassLoader classLoader = JDBCUtils2.class.getClassLoader();
            URL res = classLoader.getResource("jdbc.properties");//URL是统一资源标识定位符
            String path = res.getPath();
            System.out.println(path);
            //2、加载文件
            pro.load(new FileReader(path));
            //pro.load(new FileReader("D:\\code\\java\\javaweb\\JDBC01\\src\\package01\\jdbc.properties"));
            //3、获取数据、赋值
            JDBCUtils2.url = pro.getProperty("url");
            user = pro.getProperty("user");
            password = pro.getProperty("password");
            driver = pro.getProperty("driver");
            //4、注册驱动
            Class.forName(driver);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }


    /**
     * 获取连接
     *
     * @return 连接对象
     */
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url,user,password);
    }

    /**
     * 释放资源的方法
     *
     * @param stmt
     * @param conn
     * @param rs
     */
    public static void close(ResultSet rs, Statement stmt, Connection conn) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

查询所有数据库中emp表的对象，调用工具及简化代码

/*
     *查询所有emp对象
     */
    public List<Emp> findAll() {

        //变量抽取
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        //声明集合
        List<Emp> list = null;

        try {
            //调用工具集
            conn = JDBCUtils2.getConnection();

            String sql = "select * from emp";
            //获取执行sql的对象
            stmt = conn.createStatement();
            //5、执行sql
            rs = stmt.executeQuery(sql);
            //6、遍历结果集，封装对象，装载集合
            Emp emp = null;
            list = new ArrayList<Emp>();
            while (rs.next()) {
                //获取数据
                int id = rs.getInt("id");
                String ename = rs.getString("ename");
                int job_id = rs.getInt("job_id");
                int mgr = rs.getInt("mgr");
                Date joindate = rs.getDate("joindate");
                double salary = rs.getDouble("salary");
                double bonus = rs.getDouble("bonus");
                int dept_id = rs.getInt("dept_id");

                //创建emp对象并赋值
                emp = new Emp();
                emp.setId(id);
                emp.setEname(ename);
                emp.setJob_id(job_id);
                emp.setMgr(mgr);
                emp.setJoindate(joindate);
                emp.setSalary(salary);
                emp.setBonus(bonus);
                emp.setDept_id(dept_id);

                //装载集合
                list.add(emp);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //调用工具集
            JDBCUtils2.close(rs, stmt, conn);

        }
        return list;
    }

在main（）函数中测试

public static void main(String[] args) {
        List<Emp> list = new JDBCDemo3().findAll();
        //System.out.println(list);
        for (int i = 0; i < list.size(); i++) {
            System.out.println(list.get(i));
        }
    }

其中main() 函数和 findAll() 在同一个工程项目JDBCDemo3里面

附录

对应的Emp类

import java.util.Date;

/*
* 对应封装EMP表数据的JavaBean
*/
public class Emp {
        private int id;
        private String ename;
        private int job_id;
        private int mgr;
        private Date joindate;
        private double salary;
        private double bonus;
        private int dept_id;

        public int getId() {
                return id;
        }

        public void setId(int id) {
                this.id = id;
        }

        public String getEname() {
                return ename;
        }

        public void setEname(String ename) {
                this.ename = ename;
        }

        public int getJob_id() {
                return job_id;
        }

        public void setJob_id(int job_id) {
                this.job_id = job_id;
        }

        public int getMgr() {
                return mgr;
        }

        public void setMgr(int mgr) {
                this.mgr = mgr;
        }

        public Date getJoindate() {
                return joindate;
        }

        public void setJoindate(Date joindate) {
                this.joindate = joindate;
        }

        public double getSalary() {
                return salary;
        }

        public void setSalary(double salary) {
                this.salary = salary;
        }

        public double getBonus() {
                return bonus;
        }

        public void setBonus(double bonus) {
                this.bonus = bonus;
        }

        public int getDept_id() {
                return dept_id;
        }

        public void setDept_id(int dept_id) {
                this.dept_id = dept_id;
        }

        @Override
        public String toString() {
                return "Emp{" +
                        "id=" + id +
                        ", ename='" + ename + '\'' +
                        ", job_id=" + job_id +
                        ", mgr=" + mgr +
                        ", joindate=" + joindate +
                        ", salary=" + salary +
                        ", bonus=" + bonus +
                        ", dept_id=" + dept_id +
                        '}';
        }
}

对应的emp的SQL语句

CREATE TABLE emp(
	id INT PRIMARY KEY,
	ename VARCHAR(50),
	job_id INT,
	mgr INT,
	joindate DATE,
	salary DECIMAL(7,2),
	bonus DECIMAL(7,2),
	dept_id INT,
	FOREIGN KEY(job_id) REFERENCES job(id),
	FOREIGN KEY(dept_id) REFERENCES dept(id)
);
INSERT INTO emp VALUES
(1001,'孙悟空',4,1004,'2000-12-17','8000.00',NULL,20),
(1002,'卢俊义',3,1006,'2001-02-20','16000.00','3000.00',30),
(100,'林冲',3,1006,'2001-02-22','12500.00','5000.00',30),
(1004,'唐僧',2,1009,'2001-04-02','29750.00',NULL,20),
(1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',30),
(1006,'宋江',2,1009, '2001-05-01','28500.00',NULL,30),
(1007,'刘备',2,1009,'2001-09-01','24500.00',NULL,10),
(1008,'猪八戒',4,1004,'2007-04-19','30000.00',NULL,20),
(1009,'罗贯中',1,NULL,'2001-11-17','50000.00',NULL,10),
(1010,'吴用',3,1006,'2001-09-08','15000.00','0.00',30),
(1011,'沙僧',4,1004,'2007-05-23','11000.00',NULL,20),
(1012,'李逵',4,1006,'2001-12-03','9500.00',NULL,30),
(1013,'小白龙',4,1004,'2001-12-03','30000.00',NULL,20),
(1014,'关羽',4,1007,'2002-01-23','13000.00',NULL,10);


SELECT *FROM emp;