Java中利用JDBC进行多表查询练习之基础篇

最新推荐文章于 2024-01-10 23:20:21 发布

xilin6664

最新推荐文章于 2024-01-10 23:20:21 发布

阅读量4.6k

点赞数 4

本文链接：https://blog.csdn.net/xilin6664/article/details/89449802

版权

表数据如下:

代码如下:

import java.sql.*;

public class Test00 {
    /*
    基本方法
     */
    public static void main(String[] args) throws Exception {
        /*
        操作步骤:
        获取连接
        获取语句执行者
        编写SQL语句
        执行语句
        处理查询结果
        释放资源
         */
        //加载驱动
        Class.forName("com.mysql.jdbc.Driver");//抛出异常
        //获取连接
        Connection connection = DriverManager.getConnection
                ("jdbc:mysql://localhost:3306/db3", "root", "root");//抛出异常
        //编写SQL语句
                //1,查询出部门编号、部门名称、部门位置、每个部门的员工人数
                        /*
                        要查哪些表
                        表之间的关系
                        筛选条件
                        要显示哪些信息
                         */
        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.id";
                     //查询经理的信息。显示员工姓名，员工工资，职务名称，职务描述，部门名称，部门位置，工资等级
        String sql2 = "SELECT e.ename,e.salary,j.jname,j.description,d.dname,d.loc,s.grade FROM emp e JOIN dept d JOIN job j JOIN salarygrade s ON e.`dept_id`=d.`id` AND e.`job_id`=j.`id`\n" +
                "        WHERE j.`jname`=?  AND e.`salary` BETWEEN 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);

        }
        //释放资源
        resultSet.close();
        ps.close();
        connection.close();

    }
}

控制台输出效果: