JDBC练习

数据准备

代码实现

1、请查出每个导师所带研究生的姓名

import test.JDBCUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @AUTHOR ZHANG
 * @data 2021/3/24 21:48
 */
public class JDBCDemo_1 {
    public static void main(String[] args) throws SQLException, ClassNotFoundException {

        System.out.println(JDBCDemo_1.tutor3AndGraduate3());

    }

    public static Map<String, List<String>> tutor3AndGraduate3() throws SQLException, ClassNotFoundException {
        Map<String, List<String>> result = new HashMap<>();

        Connection connection = JDBCUtils.getConnection();
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery("select tutor3.name as tutor3_name,graduate3.name as graduate3_name " +
                "from graduate3,tutor3 \n" +
                "where graduate3.tutor_number = tutor3.id");
        while (resultSet.next()) {
//        resultSet.next();
            String tutor3_name = resultSet.getString("tutor3_name");
            String graduate3_name = resultSet.getString("graduate3_name");
//            List<String> l = new ArrayList<>();
			//返回 key 相映射的的 value，如果给定的 key 在映射关系中找不到，则返回指定的默认值:空的集合
            List<String> l = result.getOrDefault(tutor3_name, new ArrayList<>());

            l.add(graduate3_name);
            result.put(tutor3_name, l);
        }
        ref.JDBCUtils.closeSource(connection, statement, resultSet);
        return result;
    }
}

错误

正确

import test.JDBCUtils;

import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @AUTHOR ZHANG
 * @data 2021/3/24 21:48
 */
public class JDBCDemo_1 {
    public static void main(String[] args) throws SQLException, ClassNotFoundException, IOException {

//        System.out.println(JDBCDemo_1.tutor3AndGraduate3());
//        System.out.println(JDBCDemo_1.fromTutorToGraduate("李四"));
        System.out.println(JDBCDemo_1.count1());

    }

    //1
    public static Map<String, List<String>> tutor3AndGraduate3() throws SQLException, ClassNotFoundException {
        Map<String, List<String>> result = new HashMap<>();

        Connection connection = JDBCUtils.getConnection();
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery("select tutor3.name as tutor3_name,graduate3.name as graduate3_name " +
                "from graduate3,tutor3 \n" +
                "where graduate3.tutor_number = tutor3.id");
        while (resultSet.next()) {
//        resultSet.next();
            String tutor3_name = resultSet.getString("tutor3_name");
            String graduate3_name = resultSet.getString("graduate3_name");
//            List<String> l = new ArrayList<>();
            List<String> l = result.getOrDefault(tutor3_name, new ArrayList<>());

            l.add(graduate3_name);
            result.put(tutor3_name, l);
        }
        ref.JDBCUtils.closeSource(connection, statement, resultSet);
        return result;
    }

    //2
    //有问题
    public static  List<String> fromTutorToGraduate(String tutorName) throws SQLException, ClassNotFoundException {
        Connection connection = JDBCUtils.getConnection();
        Statement statement = connection.createStatement();
        String sql = "SELECT name FROM graduate3 WHERE tutor_number = 2";//OK
//        String sql = "SELECT name,gender FROM graduate3 WHERE tutor_number = (SELECT id FROM tutor3 WHERE name = '李四')";

//        String sql = String.format("SELECT name FROM graduate3 WHERE tutor_number = (SELECT id FROM tutor3 WHERE name = '%s')", tutorName);
        System.out.println(sql);
        ResultSet resultSet = statement.executeQuery(sql);

        ArrayList<String> list = new ArrayList<>();


        while (resultSet.next()){
            System.out.println("测试进入了");
            String name2 = resultSet.getString("name");
            System.out.println();
            list.add(name2);
        }
        ref.JDBCUtils.closeSource(connection, statement, resultSet);
        return list;
    }

    //3:请查出每个导师所带研究生的数量
    static Map<String, Integer> count1() throws SQLException, IOException, ClassNotFoundException {
        Connection connection = ref.JDBCUtils.getConnection();
        Statement statement = connection.createStatement();
        String sql = "SELECT t.name as name, COUNT(s.id) as count " +
                "FROM tutor3 as t left JOIN graduate3 as s " +
                "on t.id = s.tutor_number GROUP BY t.id";
        ResultSet resultSet = statement.executeQuery(sql);

        Map<String, Integer> result = new HashMap<>();

        while (resultSet.next()) {
            String name = resultSet.getString("name");
            int count = resultSet.getInt("count");
            result.put(name, count);
        }
        ref.JDBCUtils.closeSource(connection, statement, resultSet);
        return result;
    }

    //4:请查出每个导师所带的男研究生的数量








}

参考


package ref;

import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class day03_03 {


    public static void main(String[] args) throws SQLException, IOException, ClassNotFoundException {

        System.out.println(p1());
//        System.out.println(p2("霍金"));
//        System.out.println(p3());
//        System.out.println(p4());
//        System.out.println(p5());
//        System.out.println(p6());
    }


    //        -- 1.请查出每个导师所带研究生的姓名。
//        SELECT t.name as 导师, s.name as 学生 FROM teacher as t left join student as s on t.id = s.teacher_id;
    static Map<String, List<String>> p1() throws SQLException, IOException, ClassNotFoundException {

        Map<String, List<String>> result = new HashMap<>();
        Connection connection = JDBCUtils.getConnection();

        Statement statement = connection.createStatement();
//     SQL查询语句   select tutor3.name as tutor3_name,graduate3.name as graduate3_name from graduate3,tutor3 where graduate3.tutor_number = tutor3.id;
        ResultSet resultSet = statement.executeQuery("select tutor3.name as tutor3_name,graduate3.name as graduate3_name " +
                "from graduate3,tutor3 " +
                "where graduate3.tutor_number = tutor3.id");

//      终极参考，不可删除  ResultSet resultSet = statement.executeQuery("SELECT t.name as teacher, s.name as student FROM tutor3 as t left join graduate1 as s on t.id = s.tutor_number");
        while (resultSet.next()) {
            String tutor3 = resultSet.getString("tutor3_name");
            String graduate3 = resultSet.getString("graduate3_name");
//            List<String> l = result.get(tutor3);
//            返回 key 相映射的的 value，如果给定的 key 在映射关系中找不到，则返回指定的默认值:空的集合
            List<String> l = result.getOrDefault(tutor3, new ArrayList<>());
            System.out.println(l);
            l.add(graduate3);
            result.put(tutor3, l);
        }

        JDBCUtils.closeSource(connection, statement, resultSet);
        return result;
    }

    //    -- 2.清查出特定姓名的导师所带研究生的姓名。
//    SELECT name as 学生 FROM  student WHERE teacher_id = (SELECT id FROM teacher WHERE name = '霍金');
    static List<String> p2(String teacherName) throws SQLException, IOException, ClassNotFoundException {
        List<String> result = new ArrayList<>();

        Connection connection = JDBCUtils.getConnection();
        Statement statement = connection.createStatement();


        String sql = String.format("SELECT name FROM  student WHERE teacher_id = (SELECT id FROM teacher WHERE name = '%s')", teacherName);
        System.out.println(sql);
        ResultSet resultSet = statement.executeQuery(sql);


        while (resultSet.next()) {
            String name = resultSet.getString("name");
            result.add(name);
        }

        JDBCUtils.closeSource(connection, statement, resultSet);
        return result;
    }

    //    -- 3.请查出每个导师所带研究生的数量。
//    SELECT t.name as 导师, COUNT(s.id) FROM teacher as t left JOIN student as s on t.id = s.teacher_id GROUP BY t.id;
    static Map<String, Integer> p3() throws SQLException, IOException, ClassNotFoundException {


        Map<String, Integer> result = new HashMap<>();

        Connection connection = JDBCUtils.getConnection();
        Statement statement = connection.createStatement();
        String sql = "SELECT t.name as name, COUNT(s.id) as count FROM teacher as t left JOIN student as s on t.id = s.teacher_id GROUP BY t.id";
        ResultSet resultSet = statement.executeQuery(sql);
        while (resultSet.next()) {
            String name = resultSet.getString("name");
            int count = resultSet.getInt("count");
            result.put(name, count);
        }

        JDBCUtils.closeSource(connection, statement, resultSet);
        return result;
    }

//    -- 4.请查出每个导师所带的男研究生的数量。
//    	SELECT t.name as 导师, COUNT(s.id) FROM teacher as t left  join student as s on t.id = s.teacher_id and s.gender = '男'  GROUP BY t.id; ;

    static Map<String, Integer> p4() throws SQLException, IOException, ClassNotFoundException {


        Map<String, Integer> result = new HashMap<>();

        Connection connection = JDBCUtils.getConnection();
        Statement statement = connection.createStatement();
        String sql = "SELECT t.name as name, COUNT(s.id) as count FROM teacher as t left  join student as s  on (t.id = s.teacher_id and s.gender = '男')  GROUP BY t.id";
        ResultSet resultSet = statement.executeQuery(sql);
        while (resultSet.next()) {
            String name = resultSet.getString("name");
            int count = resultSet.getInt("count");
            result.put(name, count);
        }

        JDBCUtils.closeSource(connection, statement, resultSet);
        return result;
    }


//-- 5.请找出选择哪个研究方向的导师最多。
//    SELECT research_field, COUNT(*) as number FROM teacher GROUP BY research_field ORDER BY number DESC LIMIT 1;

    static Map<String, Integer> p5() throws SQLException, IOException, ClassNotFoundException {


        Map<String, Integer> result = new HashMap<>();

        Connection connection = JDBCUtils.getConnection();
        Statement statement = connection.createStatement();
        String sql = "SELECT research_field, COUNT(*) as number FROM teacher GROUP BY research_field ORDER BY number DESC LIMIT 1";
        ResultSet resultSet = statement.executeQuery(sql);
        resultSet.next();
        String name = resultSet.getString("research_field");
        int count = resultSet.getInt("number");
        result.put(name, count);

        JDBCUtils.closeSource(connection, statement, resultSet);
        return result;
    }

    //    -- 6.请统计不同职称的导师的个数。
//
//    SELECT title, COUNT(*) as number FROM teacher GROUP BY title;
    static Map<String, Integer> p6() throws SQLException, IOException, ClassNotFoundException {


        Map<String, Integer> result = new HashMap<>();

        Connection connection = JDBCUtils.getConnection();
        Statement statement = connection.createStatement();
        String sql = "SELECT title, COUNT(*) as number FROM teacher GROUP BY title";
        ResultSet resultSet = statement.executeQuery(sql);
        while (resultSet.next()) {
            String name = resultSet.getString("title");
            int count = resultSet.getInt("number");
            result.put(name, count);
        }

        JDBCUtils.closeSource(connection, statement, resultSet);
        return result;
    }
}