JDBC介绍及操作-CSDN博客

JDBC(Java DataBase Connectivity,java数据库连接)是一种用于执行SQL语句的Java API，可以为多种关系数据库提供统一访问，它由一组用Java语言编写的类和接口组成。JDBC提供了一种基准，据此可以构建更高级的工具和接口，使数据库开发人员能够编写数据库应用程序
Java 具有坚固、安全、易于使用、易于理解和可从网络上自动下载等特性，是编写数据库应用程序的杰出语言。所需要的只是 Java应用程序与各种不同数据库之间进行对话的方法。
JDBC可以在各种平台上使用Java，如Windows，Mac OS和各种版本的UNIX。 JDBC库包括通常与数据库使用相关的下面提到的每个任务的API。
- 连接数据库。
- 创建SQL或MySQL语句。
- 在数据库中执行SQL或MySQL查询。 - 查看和修改生成的记录。

2. 使用步骤

构建JDBC应用程序涉及以下六个步骤:

- 导入包:需要包含包含数据库编程所需的JDBC类的包。大多数情况下，使用import java.sql.*就足够了。
- 注册JDBC驱动程序:要求您初始化驱动程序，以便您可以打开与数据库的通信通道。
- 打开连接:需要使用DriverManager.getConnection()方法创建一个Connection对象，该对象表示与数据库的物理连接。
- 执行查询:需要使用类型为Statement的对象来构建和提交SQL语句到数据库。
- 从结果集中提取数据:需要使用相应的ResultSet.getXXX()方法从结果集中检索数据。
- 释放资源:需要明确地关闭所有数据库资源，而不依赖于JVM的垃圾收集

3. JDBC连接步骤

- 导入JDBC包:将Java语言的*import*语句添加到Java代码中导入所需的类。
- 注册JDBC驱动程序:此步骤将使JVM将所需的驱动程序实现加载到内存中，以便它可以满足您的JDBC 请求。
- 数据库URL配置:这是为了创建一个格式正确的地址，指向要连接到的数据库。
- 创建连接对象:最后，调用DriverManager对象的getConnection()方法来建立实际的数据库连
接。Class.forName();

- 数据库URL配置

- 创建数据库连接对象

String URL = "jdbc:mysql://localhost:3306/databasename?serverTimezone=UTC";
String USER = "username";
String PASS = "password"
Connection conn = DriverManager.getConnection(URL, USER, PASS);

- 关闭数据库连接(conn.close())

4. 案例Demo

Demo1: SQL查询语句实现
Demo2: SQL更新语句实现
Demo3: SQL查询语句预状态实现(？代替查询值)
Demo4: SQL多表查询1对多查询语句实现(包括Teacher，Student类，TeacherDao，TeacherDaoImpl)
Demo5: SQL多表查询多对1查询语句实现(包括Teacher1，Student1类，TeacherDao1，TeacherDaoImpl1)
Demo6: SQL多表查询1对1查询语句实现(包括Husband，Wife类，WifeDao，WifeDaoImpl)
Demo7: SQL多表查询多对多查询语句实现(包括Subject，Student2类，SubjectDao，SubjectDaoImpl)

Demo1-Demo3数据文件

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for department
-- ----------------------------
DROP TABLE IF EXISTS `department`;
CREATE TABLE `department` (
  `depid` varchar(4) DEFAULT NULL,
  `depname` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of department
-- ----------------------------
BEGIN;
INSERT INTO `department` VALUES ('111', '生产部');
INSERT INTO `department` VALUES ('222', '销售部');
INSERT INTO `department` VALUES ('333', '人事部');
COMMIT;

-- ----------------------------
-- Table structure for employee
-- ----------------------------
DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee` (
  `empid` varchar(5) DEFAULT NULL,
  `name` varchar(10) DEFAULT NULL,
  `sex` char(3) DEFAULT NULL,
  `title` varchar(20) DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  `depid` varchar(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of employee
-- ----------------------------
BEGIN;
INSERT INTO `employee` VALUES ('1001', '张三', '男', '高级工程师', '1975-01-01', '111');
INSERT INTO `employee` VALUES ('1002', '李四', '女', '助工', '1985-01-01', '111');
INSERT INTO `employee` VALUES ('1003', '王五', '男', '工程师', '1978-11-11', '222');
INSERT INTO `employee` VALUES ('1004', '赵六', '男', '工程师', '1979-01-01', '222');
COMMIT;

-- ----------------------------
-- Table structure for salary
-- ----------------------------
DROP TABLE IF EXISTS `salary`;
CREATE TABLE `salary` (
  `empid` varchar(5) DEFAULT NULL,
  `basesalary` int(11) DEFAULT NULL,
  `titlesalary` int(11) DEFAULT NULL,
  `deduction` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of salary
-- ----------------------------
BEGIN;
INSERT INTO `salary` VALUES ('1001', 2200, 1100, 200);
INSERT INTO `salary` VALUES ('1002', 1200, 200, 100);
INSERT INTO `salary` VALUES ('1003', 1900, 700, 200);
INSERT INTO `salary` VALUES ('1004', 1950, 700, 150);
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

Demo1: SQL查询语句实现

import java.sql.*;

import static java.lang.Class.forName;

/**
 * SQL查询语句
 */

public class Demo1 {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            //1.加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获得链接
            String username="root";
            String password="12345678";
            String url="jdbc:mysql://localhost:3306/JavaTest?serverTimezone=UTC";
            connection = DriverManager.getConnection(url, username, password);
            //3.定义sql，创建状态通道(进行sql语句的发送)
            statement = connection.createStatement();
            resultSet  = statement.executeQuery("SELECT * FROM employee");
            //4.去除结果集信息
            //判断是否有下一下数据
            while(resultSet.next()){
                System.out.println("姓名："+resultSet.getString("name")+
                        "职称："+resultSet.getString("title")+
                        "生日："+resultSet.getString("birthday"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            try{
                //5.关闭资源
                if(resultSet != null){
                    resultSet.close();
                }
                if(statement != null){
                    statement.close();
                }
                if(connection != null){
                    connection.close();
                }

            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

Demo2: SQL更新语句实现

import java.sql.*;

import static java.lang.Class.forName;
/**
 * SQL更新UPDATE语句
 */
public class Demo2 {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            //1.加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获得链接
            String username="javatest";
            String password="123456";
            String url="jdbc:mysql://47.112.110.144:3306/JAVATest?serverTimezone=UTC";
            connection = DriverManager.getConnection(url, username, password);
            //3.定义sql，创建状态通道(进行sql语句的发送)
            statement = connection.createStatement();
            int result  = statement.executeUpdate("UPDATE employee SET title = '工程师'");
            if(0 < result){
                System.out.println("执行成功");
            }else{
                System.out.println("执行失败");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            try{
                //5.关闭资源
                if(statement != null){
                    statement.close();
                }
                if(connection != null){
                    connection.close();
                }

            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

Demo3: SQL查询语句预状态实现(？代替查询值)

import java.sql.*;

import static java.lang.Class.forName;

/**
 * 预状态通道
 * SQL查询语句
 */

public class Demo3 {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement pps= null;
        ResultSet resultSet = null;
        try {
            //1.加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获得链接
            String username="javatest";
            String password="123456";
            String url="jdbc:mysql://47.112.110.144:3306/JAVATest?serverTimezone=UTC";
            connection = DriverManager.getConnection(url, username, password);
            //3.定义sql，创建预状态通道(进行sql语句的发送)
            String sql = "SELECT * FROM employee WHERE name = ? AND title = ?";
            pps = connection.prepareStatement(sql);
            //给占位符？进行赋值（下标，内容）下标是从1开始
            pps.setString(1,"张三");
            pps.setString(2,"工程师");
            resultSet  = pps.executeQuery();
            //4.去除结果集信息
            //判断是否有下一下数据
            while(resultSet.next()){
                System.out.println("姓名："+resultSet.getString("name")+
                        "职称："+resultSet.getString("title")+
                        "生日："+resultSet.getString("birthday"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            try{
                //5.关闭资源
                if(resultSet != null){
                    resultSet.close();
                }
                if(pps != null){
                    pps.close();
                }
                if(connection != null){
                    connection.close();
                }

            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

Demo4：SQL多表查询1对多查询语句实现(包括Teacher，Student类，TeacherDao，TeacherDaoImpl)

数据文件：

CREATE TABLE `student` (
  `stuid` int(11) NOT NULL AUTO_INCREMENT,
  `stuname` varchar(255) DEFAULT NULL,
  `teacherid` int(11) DEFAULT NULL,
  PRIMARY KEY (`stuid`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
INSERT INTO `student` VALUES ('1', 'aaa', '3');
INSERT INTO `student` VALUES ('2', 'bb', '1');
INSERT INTO `student` VALUES ('3', 'cc', '3');
INSERT INTO `student` VALUES ('4', 'dd', '1');
INSERT INTO `student` VALUES ('5', 'ee', '1');
INSERT INTO `student` VALUES ('6', 'ff', '2');
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `tid` int(11) NOT NULL AUTO_INCREMENT,
  `tname` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
INSERT INTO `teacher` VALUES ('1', '张三老师'); 
INSERT INTO `teacher` VALUES ('2', '李四老师'); 
INSERT INTO `teacher` VALUES ('3', '王五');

实体类Teacher：

import java.util.ArrayList;
import java.util.List;

//一个 老师对应多个学生
public class Teacher {
    private int tid;
    private String tname;
    //创建多方数据的集合
    private List<Student> list = new ArrayList<Student>();
    public List<Student> getList() {
        return list;
    }
    public void setList(List<Student> list) {
        this.list = list;
    }
    public int getTid() {
        return tid; }
    public void setTid(int tid) {
        this.tid = tid;
    }
    public String getTname() {
        return tname;
    }
    public void setTname(String tname) {
        this.tname = tname;
    }
}

实体类Student：

public class Student {
    private int stuid;
    private String stuname;
    //外键列一般不生成属性
    // private int teacherid;
    public int getStuid() {
        return stuid;
    }
    public void setStuid(int stuid) {
        this.stuid = stuid;
    }
    public String getStuname() {
        return stuname;
    }
    public void setStuname(String stuname) {
        this.stuname = stuname;
    } }

Dao接口：

public interface TeacherDao { //定义操作方法
    //1.定义一个根据老师id查询老师信息(学生的信息)
    public Teacher getById(int tid);
}

实现类：

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class TeacherDaoImpl implements TeacherDao {
    @Override
    public Teacher getById(int tid) { //操作数据库
        Connection connection =null;
        PreparedStatement pps =null;
        ResultSet resultSet =null;
        try {
            //1.加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获得链接
            String userName="root";
            String passWord="12345678";
            String url="jdbc:mysql://localhost:3306/JavaTest?serverTimezone=UTC";
            connection = DriverManager.getConnection(url, userName, passWord);
            //3.定义sql,创建预状态通道(进行sql语句的发送)
            String sql="select * from student s join teacher t on s.teacherid=t.tid where t.tid=?";
            pps = connection.prepareStatement(sql);
            //给占位符赋值 (下标，内容) 从1开始
            pps.setInt(1,tid);
            //执行sql
            resultSet = pps.executeQuery();
            // 一个老师对应多个学生，创建一个新老师对象存储老师
            Teacher teacher = new Teacher();
            //创建list存储学生，循环存入学生信息
            List<Student> students = new ArrayList<Student>();
            while (resultSet.next()){
                //1.取出各自的信息
                teacher.setTid(resultSet.getInt("tid"));
                teacher.setTname(resultSet.getString("tname"));
                //2. 每次循环都把学生单独取出来
                Student student = new Student();
                student.setStuid(resultSet.getInt("stuid"));
                student.setStuname(resultSet.getString("stuname"));
                //3. 存入list中
                students.add(student);
            }
            // 把students赋值进teacher
            teacher.setList(students);
            return  teacher;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                //5.关闭资源
                if (resultSet != null) {
                    resultSet.close();
                }
                if (pps != null) {
                    pps.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            } }
        return null;
    }
}

测试类：

import java.util.List;

public class Demo4 {
    public static void main(String[] args) {
    TeacherDao dao= new TeacherDaoImpl();
    Teacher teacher = dao.getById(1);
    System.out.println("老师姓名:"+teacher.getTname());
    List<Student> studentList = teacher.getList();
    for (Student student : studentList) {
        System.out.println("\t studentname="+student.getStuname());
        }
    }
}

Demo5: SQL多表查询多对1查询语句实现(包括Teacher1，Student1类，TeacherDao1，TeacherDaoImpl1)

实体类Teacher1：

import java.util.ArrayList;
import java.util.List;

//一个 老师对应多个学生
public class Teacher1 {
    private int tid;
    private String tname;
    public int getTid() {
        return tid; }
    public void setTid(int tid) {
        this.tid = tid;
    }
    public String getTname() {
        return tname;
    }
    public void setTname(String tname) {
        this.tname = tname;
    }
}

实体类Student1：

public class Student1 {
    private int stuid;
    private String stuname;
    //外键列一般不生成属性
    // private int teacherid;
    private Teacher1 teacher;
    public int getStuid() {
        return stuid;
    }
    public void setStuid(int stuid) {
        this.stuid = stuid;
    }
    public String getStuname() {
        return stuname;
    }
    public void setStuname(String stuname) {
        this.stuname = stuname;
    }
    public Teacher1 getTeacher() {
        return teacher;
    }
    public void setTeacher(Teacher1 teacher) {
        this.teacher = teacher;
    }
}

Dao接口类：

import java.util.List;

public interface TeacherDao1 { //定义操作方法
    //1.定义一个根据老师id查询老师信息(学生的信息)
    public List<Student1> getAll();
}

实现类：

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class TeacherDaoImpl1 implements TeacherDao1 {
    @Override
    public List<Student1> getAll() { //操作数据库
        Connection connection =null;
        PreparedStatement pps =null;
        ResultSet resultSet =null;
        try {
            //1.加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获得链接
            String userName="root";
            String passWord="12345678";
            String url="jdbc:mysql://localhost:3306/JavaTest?serverTimezone=UTC";
            connection = DriverManager.getConnection(url, userName, passWord);
            //3.定义sql,创建预状态通道(进行sql语句的发送)
            String sql="select * from student s join teacher t on s.teacherid=t.tid";
            pps = connection.prepareStatement(sql);
            //执行sql
            resultSet = pps.executeQuery();
            //创建list存储学生，循环存入学生信息
            List<Student1> students = new ArrayList<>();
            while (resultSet.next()){
                //1.取出各自的信息
                Student1 student = new Student1();
                student.setStuid(resultSet.getInt("stuid"));
                student.setStuname(resultSet.getString("stuname"));

                Teacher1 teacher = new Teacher1();
                teacher.setTid(resultSet.getInt("tid"));
                teacher.setTname(resultSet.getString("tname"));

                student.setTeacher(teacher);
                students.add(student);
            }
            return  students;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                //5.关闭资源
                if (resultSet != null) {
                    resultSet.close();
                }
                if (pps != null) {
                    pps.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            } }
        return null;
    }
}

测试类：

import java.util.List;

public class Demo5 {
    public static void main(String[] args) {
        TeacherDao1 dao= new TeacherDaoImpl1();
        List<Student1> students = dao.getAll();
        for (Student1 student : students) {
            System.out.println(student.getStuname()+","+student.getTeacher().getTname());
        }
        System.out.println("-----------------------------------------------------");
        for (int i = 0;i<students.size();i++) {
            System.out.println(students.get(i).getStuname()+","+students.get(i).getTeacher().getTname());
        }
    }
}

Demo6: SQL多表查询1对1查询语句实现(包括Husband，Wife类，WifeDao，WifeDaoImpl)

数据文件：

CREATE TABLE `husband` (
  `husid` int(11) NOT NULL AUTO_INCREMENT,
  `husname` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`husid`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
INSERT INTO `husband` VALUES ('1', '邓超');
DROP TABLE IF EXISTS `wife`;
CREATE TABLE `wife` (
  `wifeid` int(11) NOT NULL AUTO_INCREMENT,
  `wifename` varchar(255) DEFAULT NULL,
  `hid` int(11) DEFAULT NULL,
  PRIMARY KEY (`wifeid`),
  UNIQUE KEY `uq_wife_hid` (`hid`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
INSERT INTO `wife` VALUES ('1', '孙俪', '1');

实体类Husband：

public class Husband {
    private int husid;
    private String husname;
    private Wife wife;
    public int getHusid() {
        return husid;
    }
    public void setHusid(int husid) {
        this.husid = husid;
    }
    public String getHusname() {
        return husname;
    }
    public void setHusname(String husname) {
        this.husname = husname;
    }
    public Wife getWife() {
        return wife;
    }
    public void setWife(Wife wife) {
        this.wife = wife;
    }
}

实体类Wife：

public class Wife {
    private int wifeid;
    private String wifeName;
    private Husband husband;
    public int getWifeid() {
        return wifeid;
    }
    public void setWifeid(int wifeid) {
        this.wifeid = wifeid;
    }
    public String getWifeName() {
        return wifeName;
    }
    public void setWifeName(String wifename) {
        this.wifeName = wifename;
    }
    public Husband getHusband() {
        return husband;
    }
    public void setHusband(Husband husband) {
        this.husband = husband;
    }
}

Dao接口类：

public interface WifeDao {
    //查询妻子信息(包含丈夫信息)
    public Wife getWife(int wid); //查询丈夫信息(包含妻子信息)
    public Husband getHus(int hid) throws ClassNotFoundException;
}

实现类：

import java.sql.*;

public class WifeDaoImpl implements WifeDao {
    @Override
    public Wife getWife(int wid) {
        //操作数据库
        Connection connection =null;
        PreparedStatement pps =null;
        ResultSet resultSet =null;
        try {
            //1.加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获得链接
            String userName="root";
            String passWord="12345678";
            String url="jdbc:mysql://localhost:3306/JavaTest?serverTimezone=UTC";
            connection = DriverManager.getConnection(url, userName, passWord);
            //3.定义sql,创建预状态通道(进行sql语句的发送)
            String sql="select * from wife w,husband h where w.hid=h.husid and w.wifeid=?";
            pps = connection.prepareStatement(sql);
            pps.setInt(1,wid);
            //执行sql
            resultSet = pps.executeQuery();
            Wife wife = new Wife();
            while (resultSet.next()){
            //1.取出各自的信息
                wife.setWifeid(resultSet.getInt("wifeid"));
                wife.setWifeName(resultSet.getString("wifename"));
                Husband husband = new Husband();
                husband.setHusid(resultSet.getInt("husid"));
                husband.setHusname(resultSet.getString("husname"));
                //2.建立妻子和丈夫之间的关系
                wife.setHusband(husband);
            }
            return wife;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try { //5.关闭资源
                if (resultSet != null) {
                    resultSet.close();
                }
                if (pps != null) {
                    pps.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            } }
        return null;
    }
    @Override
    public Husband getHus(int hid) {
        //操作数据库
        Connection connection =null;
        PreparedStatement pps =null;
        ResultSet resultSet =null;
        //1.加载驱动
        try {
            //1.加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获得链接
            String userName="root";
            String passWord="12345678";
            String url="jdbc:mysql://localhost:3306/JavaTest?serverTimezone=UTC";
            connection = DriverManager.getConnection(url, userName, passWord);
            //3.定义sql,创建预状态通道(进行sql语句的发送)
            String sql="select * from wife w,husband h where w.hid=h.husid and h.husid=?";
            pps = connection.prepareStatement(sql);
            pps.setInt(1,hid);
            //执行sql
            resultSet = pps.executeQuery();
            Husband husband = new Husband();
            while (resultSet.next()){
                //1.取出各自的信息
                Wife wife = new Wife();
                wife.setWifeid(resultSet.getInt("wifeid"));
                wife.setWifeName(resultSet.getString("wifename"));
                husband.setHusid(resultSet.getInt("husid"));
                husband.setHusname(resultSet.getString("husname"));
                //2.建立妻子和丈夫之间的关系
                husband.setWife(wife);
            }
            return husband;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try { //5.关闭资源
                if (resultSet != null) {
                    resultSet.close();
                }
                if (pps != null) {
                    pps.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            } }
        return null;
    }
}

测试类：

public class Demo6 {
    public static void main(String[] args) {
        WifeDaoImpl wifeDao = new WifeDaoImpl();
        Wife wife = wifeDao.getWife(1);
        System.out.println(wife.getWifeName()+","+wife.getHusband().getHusname());
        Husband hus = wifeDao.getHus(1);
        System.out.println(hus.getHusname()+","+hus.getWife().getWifeName());
    }
}

Demo7: SQL多表查询多对多查询语句实现(包括Subject，Student2类，SubjectDao，SubjectDaoImpl)

数据文件：

CREATE TABLE `middle` (
  `middleid` int(11) NOT NULL AUTO_INCREMENT,
  `stuid` int(11) DEFAULT NULL,
  `subid` int(11) DEFAULT NULL,
  PRIMARY KEY (`middleid`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of middle
-- ----------------------------
INSERT INTO `middle` VALUES ('1', '1', '1');
INSERT INTO `middle` VALUES ('2', '1', '2');
INSERT INTO `middle` VALUES ('3', '1', '3');
INSERT INTO `middle` VALUES ('4', '1', '5');
INSERT INTO `middle` VALUES ('5', '2', '2');
INSERT INTO `middle` VALUES ('6', '3', '2');
INSERT INTO `middle` VALUES ('7', '4', '2');
INSERT INTO `middle` VALUES ('8', '5', '2');
INSERT INTO `middle` VALUES ('9', '6', '2');
-- ----------------------------
-- Table structure for `student`
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `stuid` int(11) NOT NULL AUTO_INCREMENT,
  `stuname` varchar(255) DEFAULT NULL,
  `teacherid` int(11) DEFAULT NULL,
  PRIMARY KEY (`stuid`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', '张三', '3'); INSERT INTO `student` VALUES ('2', '李四', '1'); INSERT INTO `student` VALUES ('3', '王五', '3'); INSERT INTO `student` VALUES ('4', '赵六', '1'); INSERT INTO `student` VALUES ('5', '花花', '1'); INSERT INTO `student` VALUES ('6', '潇潇', '2');
-- ----------------------------
-- Table structure for `subject`
-- ----------------------------
DROP TABLE IF EXISTS `subject`;
CREATE TABLE `subject` (
  `subid` int(11) NOT NULL AUTO_INCREMENT,
  `subname` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`subid`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of subject
-- ----------------------------
INSERT INTO `subject` VALUES ('1', 'java');
INSERT INTO `subject` VALUES ('2', 'ui');
INSERT INTO `subject` VALUES ('3', 'h5');
INSERT INTO `subject` VALUES ('4', 'c');
INSERT INTO `subject` VALUES ('5', 'c++');
INSERT INTO `subject` VALUES ('6', 'c#');

实体类Subject：

import java.util.List;

public class Subject {
    private int subid;
    private String subname;
    private List stulist;
    public int getSubid() {
        return subid;
    }
    public void setSubid(int subid) {
        this.subid = subid;
    }
    public String getSubname() {
        return subname;
    }
    public void setSubname(String subname) {
        this.subname = subname;
    }
    public List getStulist() {
        return stulist;
    }
    public void setStulist(List stulist) {
        this.stulist = stulist;
    }
}

实体类Student2：

import java.util.List;

public class Student2 {
    private int stuid;
    private String stuname;
    //外键列一般不生成属性
    private List<Subject> subjects;
    public List<Subject> getSubjects() {
        return subjects;
    }
    public void setSubjects(List<Subject> subjects) {
        this.subjects = subjects;
    }
    public int getStuid() {
        return stuid;
    }
    public void setStuid(int stuid) {
        this.stuid = stuid;
    }
    public String getStuname() {
        return stuname;
    }
    public void setStuname(String stuname) {
        this.stuname = stuname;
    }
}

Dao接口类：

public interface SubjectDao {
    //查询某个学生信息(查询出所学科目)
    public Student2 findById(int id);
    //查询某个科目以及对应的学生姓名
    public Subject findBySubId(int subId);
}

实现类：

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class SubjectDaoImpl implements SubjectDao {
    @Override
    public Student2 findById(int id) { //操作数据库
        Connection connection =null;
        PreparedStatement pps =null;
        ResultSet resultSet =null;
        try {
            //1.加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获得链接
            String userName="root";
            String passWord="12345678";
            String url="jdbc:mysql://localhost:3306/JavaTest?serverTimezone=UTC";
            connection = DriverManager.getConnection(url, userName, passWord);
            //3.定义sql,创建预状态通道(进行sql语句的发送)
            String sql="select * from student s,subject su,middle m where s.stuid=m.stuid and su.subid=m.subid and  s.stuid=?";
            pps = connection.prepareStatement(sql);
            pps.setInt(1,id);
            //执行sql
            resultSet = pps.executeQuery();
            Student2 student = new Student2();
            List<Subject> subjects=new ArrayList<>();
            while (resultSet.next()){
                //1.取出各自的信息
                student.setStuid(resultSet.getInt("stuid"));
                student.setStuname(resultSet.getString("stuname"));
                Subject subject = new Subject();
                subject.setSubid(resultSet.getInt("subid"));
                subject.setSubname(resultSet.getString("subname"));
                subjects.add(subject);
            } //2.建立学生和科目之间的关系
            student.setSubjects(subjects);
            return student;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                //5.关闭资源
                if (resultSet != null) {
                    resultSet.close();
                }
                if (pps != null) {
                    pps.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();} }
        return null;
    }
    @Override
    public Subject findBySubId(int subId) {
        //操作数据库
        Connection connection =null;
        PreparedStatement pps =null;
        ResultSet resultSet =null; try {
            //1.加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获得链接
            String userName="root";
            String passWord="12345678";
            String url="jdbc:mysql://localhost:3306/JavaTest?serverTimezone=UTC";
            connection = DriverManager.getConnection(url, userName, passWord);
            //3.定义sql,创建预状态通道(进行sql语句的发送)
            String sql="select * from student s,subject su,middle m where s.stuid=m.stuid and su.subid=m.subid and su.subid=?";
            pps = connection.prepareStatement(sql);
            pps.setInt(1,subId);
            //执行sql
            resultSet = pps.executeQuery();
            Subject subject = new Subject();
            List<Student2> studentList=new ArrayList<>();
            while (resultSet.next()){
                //1.取出各自的信息
                Student2 student = new Student2();
                student.setStuid(resultSet.getInt("stuid"));
                student.setStuname(resultSet.getString("stuname"));
                studentList.add(student);
                subject.setSubid(resultSet.getInt("subid"));
                subject.setSubname(resultSet.getString("subname"));
            }
//2.建立学生和科目之间的关系
        subject.setStulist(studentList);
            return subject;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
//5.关闭资源
                if (resultSet != null) {
                    resultSet.close();
                }
                if (pps != null) {
                    pps.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return null;
    }
}

测试类：

import java.util.List;

public class Demo7 {
    public static void main(String[] args) {
        SubjectDaoImpl subjectDao = new SubjectDaoImpl();
       /* Student2 student = subjectDao.findById(1);
        System.out.println(student.getStuName());
        List<Subject> subjects = student.getSubjects();
        for (Subject subject : subjects) {
            System.out.println("\t"+subject.getSubName());
        }*/
        Subject subject = subjectDao.findBySubId(2);
        System.out.println(subject.getSubname());
        List<Student2> studentList = subject.getStulist();
        for (Student2 student : studentList) {
            System.out.println("\t"+student.getStuname());
        }
    }
}