Java利用JDBC对数据库多表查询之使用工具类获取数据库连接和释放资源

将每次操作数据库的重复获取连接和释放资源的代码整合到一个工具类中

工具类代码:

import java.sql.*;

/*
创建工具类,用来获取连接和释放资源
 */
public class Utils11 {
    static{//加载驱动
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    public static Connection getConnection(){//获取连接
        Connection connection=null;
        try {
             connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/db3", "root", "root");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }
    public static void closeResource(ResultSet resultSet, Statement st,Connection ct){//释放资源
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (st != null) {
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (ct != null) {
            try {
                ct.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

测试类代码:

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class Test01 {
    public static void main(String[] args) throws Exception {
        //利用工具类获取连接和释放资源,进行数据库的查询操作
        Connection connection = Utils11.getConnection();//获取数据库连接
        //查询出部门编号、部门名称、部门位置、每个部门的员工人数
        String sql1 = " SELECT d.id,d.dname,d.loc,COUNT(*) FROM emp e JOIN dept d ON e.`dept_id`=d.`id` GROUP BY d.`dname`;";
        //查询经理的信息。显示员工姓名，员工工资，职务名称，职务描述，部门名称，部门位置，工资等级
        String sql2 = "SELECT e.ename,e.salary,j.jname,j.description,d.dname,d.loc,s.grade FROM emp e JOIN job j JOIN\n" +
                "dept d JOIN salarygrade s ON e.`dept_id`=d.`id` AND e.`job_id`=j.`id` WHERE j.`jname`=?  AND e.`salary` BETWEEN\n" +
                " s.`losalary`  AND s.`hisalary`;";
        //查询所有员工信息。显示员工姓名，员工工资，职务名称，职务描述，部门名称，部门位置，工资等级
        String sql3 = " SELECT e.ename,e.salary,j.jname,j.description,d.dname,d.loc,s.grade FROM emp e JOIN  job j JOIN\n" +
                " dept d  JOIN salarygrade s ON  e.`job_id`=j.`id` AND e.`dept_id`= d.`id` WHERE e.`salary` BETWEEN\n" +
                " s.`losalary` AND s.`hisalary`;";
        PreparedStatement ps = connection.prepareStatement(sql1);
        PreparedStatement ps1 = connection.prepareStatement(sql2);
        PreparedStatement ps2 = connection.prepareStatement(sql3);
        ps1.setObject(1,"经理");//设置参数
        ResultSet resultSet = ps.executeQuery();
        ResultSet resultSet1 = ps1.executeQuery();
        ResultSet resultSet2 = ps2.executeQuery();
        while (resultSet.next()) {
            int id = resultSet.getInt("id");
            String dname = resultSet.getString("dname");
            String loc = resultSet.getString("loc");
            int count = resultSet.getInt("count(*)");
            System.out.println("id="+id+" dname="+dname+" loc="+loc+" count="+count );

        }
        System.out.println("===================================================================");
        while (resultSet1.next()) {
            String ename = resultSet1.getString("ename");
            int salary = resultSet1.getInt("salary");
            String jname = resultSet1.getString("jname");
            String description = resultSet1.getString("description");
            String dname = resultSet1.getString("dname");
            String loc = resultSet1.getString("loc");
            int grade = resultSet1.getInt("grade");
            System.out.println("ename="+ename+" salary="+salary+" jname="+jname+" description="+description
                    +" dname="+dname+" loc="+loc+" grade="+grade);

        }
        System.out.println("===================================================================");
        while (resultSet2.next()) {
            String ename = resultSet2.getString("ename");
            int salary = resultSet2.getInt("salary");
            String jname = resultSet2.getString("jname");
            String description = resultSet2.getString("description");
            String dname = resultSet2.getString("dname");
            String loc = resultSet2.getString("loc");
            int grade = resultSet2.getInt("grade");
            System.out.println("ename="+ename+" salary="+salary+" jname="+jname+" description="+description
                    +" dname="+dname+" loc="+loc+" grade="+grade);

        }
        //释放资源
        Utils11.closeResource(resultSet,ps,connection);
        Utils11.closeResource(resultSet1,ps1,null);
        Utils11.closeResource(resultSet2,ps2,null);
    }
}

控制它打印效果;