JDBC之PreparedStatement&两表查询

一、preparedStatement和statement的区别：
statement执行完整的sql语句，preparedStatement执行半成品sql语句
prepareStatement（sql）要执行sql语句，createStatement（）不需要执行sql，在查询时在执行sql

preparedStatement可以防止sql注入，更安全

String sql="select * from students where stu_id=? and stu_sex=? and stu_name=?";
preparedStatement = connection.prepareStatement(sql);
//preparedStatement.setInt（参数一，参数二）参数一代表要改sql语句中的第几个问号，参数二为修改后的值
preparedStatement.setInt(1,1);
preparedStatement.setInt(2,1);
preparedStatement.setString(3,"杨洋");
resultSet=preparedStatement.executeQuery();

String sql = "SELECT * FROM students s LEFT JOIN teacher t ON s.tea_id = t.tea_id";
resultSet = statement.executeQuery(sql);

二、当需要查询一对多关系的两表中的内容时，可以在少的一方的类中添加多的一方的集合

package week8_2021.dayone_0104;

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

public class Teachers {
    private Integer teaId;
    private String teaName;
    private String teaSubject;
    private Integer teaClass;
    //一个老师对应多个学生
    List<Students> studentsList = new ArrayList<>();

    public List<Students> getStudentsList() {
        return studentsList;
    }

    public void setStudentsList(List<Students> studentsList) {
        this.studentsList = studentsList;
    }

    public Integer getTeaId() {
        return teaId;
    }

    public void setTeaId(Integer teaId) {
        this.teaId = teaId;
    }

    public String getTeaName() {
        return teaName;
    }

    public void setTeaName(String teaName) {
        this.teaName = teaName;
    }

    public String getTeaSubject() {
        return teaSubject;
    }

    public void setTeaSubject(String teaSubject) {
        this.teaSubject = teaSubject;
    }

    public Integer getTeaClass() {
        return teaClass;
    }

    public void setTeaClass(Integer teaClass) {
        this.teaClass = teaClass;
    }

    @Override
    public String toString() {
        return "Teachers{" +
                "teaId=" + teaId +
                ", teaName='" + teaName + '\'' +
                ", teaSubject='" + teaSubject + '\'' +
                ", teaClass=" + teaClass +
                ", studentsList=" + studentsList +
                '}';
    }

    @Override
    public boolean equals(Object o) {
        //将object类型强制转换为teachers类型
        Teachers teachers = (Teachers) o;
        return teaId.equals(teachers.teaId);
    }

    @Override
    public int hashCode() {
        return this.getTeaId();
    }
}

package week8_2021.dayone_0104;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

public class YiDuiDuoSelect {

    private static void getStudents(){

        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            connection = JDBCUtils.getConnection();
            statement = connection.createStatement();
            String sql = "SELECT * FROM students s LEFT JOIN teacher t ON s.tea_id = t.tea_id";
            resultSet = statement.executeQuery(sql);
            Set<Teachers> teachersSet = new HashSet<Teachers>();
            List<Students> studentsList = new ArrayList<>();
            Teachers teachers = null;
            while (resultSet.next()){

                //从resultSet结果集获取老师信息
                int teaId = resultSet.getInt("tea_id");
                String teaName = resultSet.getString("tea_name");
                String teaSubject = resultSet.getString("tea_subject");
                int teaClass = resultSet.getInt("tea_class");
                //将老师信息赋值给对象
                teachers = new Teachers();
                teachers.setTeaId(teaId);
                teachers.setTeaName(teaName);
                teachers.setTeaSubject(teaSubject);
                teachers.setTeaClass(teaClass);
                teachersSet.add(teachers);

                //从resultSet结果集获取学生信息
                int studentId = resultSet.getInt("stu_id");
                String studentName = resultSet.getString("stu_name");
                int studentAge = resultSet.getInt("stu_age");
                int studentSex = resultSet.getInt("stu_sex");
                int teacherId = resultSet.getInt("tea_id");
                int isDel = resultSet.getInt("is_del");
                //将学生信息赋值给对象
                Students students = new Students();
                students.setStudentId(studentId);
                students.setStudentName(studentName);
                students.setStudentAge(studentAge);
                students.setStudentSex(studentSex);
                students.setTeacherId(teacherId);
                students.setIsDel(isDel);
                studentsList.add(students);
            }

            //循环去重后老师的集合
            for (Teachers t:teachersSet) {
                //创建一个新的学生集合用来归类
                List<Students> studentsList1 = new ArrayList<>();
                //循环原有未归类的学生集合
                for (Students s:studentsList) {
                    //如果老师类的TeaId和TeacherId相同那么将学生加入该集合，即通过两个表的联系
                    if (t.getTeaId() == s.getTeacherId()){
                        studentsList1.add(s);
                    }
                }
                //将归类后的学生集合设置为该老师的属性
                t.setStudentsList(studentsList1);
            }

            for (Teachers t:teachersSet) {
                System.out.println(t);
            }

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

    public static void main(String[] args) {
        getStudents();
    }

}