Java jdbc实现多表查询

数据库中的一张表对应Java中的一个类

我这里示例的是学生类，老师类，成绩类，还有一个用于存储多表查询结果后的SelectAll类

public class Student {//学生表
    private Integer id;//学生编号
    private String number;//学生学号
    private String name;//学生姓名
    private String sex;//学生性别

    public Student(Integer id, String number, String name, String sex) {
        this.id = id;
        this.number = number;
        this.name = name;
        this.sex = sex;
    }

    public Student() {
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getNumber() {
        return number;
    }

    public void setNumber(String number) {
        this.number = number;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", number='" + number + '\'' +
                ", name='" + name + '\'' +
                ", sex='" + sex + '\'' +
                '}';
    }
}

public class Teacher {//老师表
    private Integer tid;//老师编号
    private String teaname;//老师姓名

    public Teacher() {
    }

    public Teacher(Integer tid, String teaname) {
        this.tid = tid;
        this.teaname = teaname;
    }

    public Integer getTid() {
        return tid;
    }

    public void setTid(Integer tid) {
        this.tid = tid;
    }

    public String getTeaname() {
        return teaname;
    }

    public void setTeaname(String teaname) {
        this.teaname = teaname;
    }

    @Override
    public String toString() {
        return "Teacher{" +
                "tid=" + tid +
                ", teaname='" + teaname + '\'' +
                '}';
    }
}

public class Cj {//成绩表
    private Integer id;//学生编号
    private Integer tid;//老师编号
    private String subject;//科目
    private double score;//成绩

    public Cj() {
    }

    public Cj(Integer id, Integer tid, String subject, double score) {
        this.id = id;
        this.tid = tid;
        this.subject = subject;
        this.score = score;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public Integer getTid() {
        return tid;
    }

    public void setTid(Integer tid) {
        this.tid = tid;
    }

    public String getSubject() {
        return subject;
    }

    public void setSubject(String subject) {
        this.subject = subject;
    }

    public double getScore() {
        return score;
    }

    public void setScore(double score) {
        this.score = score;
    }

    @Override
    public String toString() {
        return "Cj{" +
                "id=" + id +
                ", tid=" + tid +
                ", subject='" + subject + '\'' +
                ", score='" + score + '\'' +
                '}';
    }
}

public class SelectAll {//用于保存多表查询结果后的类
    private Integer sid;//学生编号
    private String snumber;//学生学号
    private String sname;//学生姓名
    private String ssex;//学生性别
    private Integer laoid;//老师编号
    private String laoname;//老师姓名
    private String csubject;//科目
    private String cscore;//成绩

    public SelectAll() {
    }

    public Integer getSid() {
        return sid;
    }

    public void setSid(Integer sid) {
        this.sid = sid;
    }

    public String getSnumber() {
        return snumber;
    }

    public void setSnumber(String snumber) {
        this.snumber = snumber;
    }

    public String getSname() {
        return sname;
    }

    public void setSname(String sname) {
        this.sname = sname;
    }

    public String getSsex() {
        return ssex;
    }

    public void setSsex(String ssex) {
        this.ssex = ssex;
    }

    public Integer getLaoid() {
        return laoid;
    }

    public void setLaoid(Integer laoid) {
        this.laoid = laoid;
    }

    public String getLaoname() {
        return laoname;
    }

    public void setLaoname(String laoname) {
        this.laoname = laoname;
    }

    public String getCsubject() {
        return csubject;
    }

    public void setCsubject(String csubject) {
        this.csubject = csubject;
    }

    public String getCscore() {
        return cscore;
    }

    public void setCscore(String cscore) {
        this.cscore = cscore;
    }

    @Override
    public String toString() {
        return "SelectAll{" +
                "sid=" + sid +
                ", snumber='" + snumber + '\'' +
                ", sname='" + sname + '\'' +
                ", ssex='" + ssex + '\'' +
                ", laoid=" + laoid +
                ", laoname='" + laoname + '\'' +
                ", csubject='" + csubject + '\'' +
                ", cscore='" + cscore + '\'' +
                '}';
    }
}

编写一个List<SelectAll>类型的查询方法，将查询数据存入集合中，返回集合

public List<SelectAll> selectAll(){
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet res = null;
        String sql = "SELECT t_student.s_id,t_student.s_name,t_student.s_number,t_student.s_sex,tea.t_id,tea.teaname,cj.score,cj.`subject` FROM t_student,tea,cj WHERE t_student.s_id = cj.s_id AND tea.t_id = cj.t_id";
        List<SelectAll> sa = new ArrayList<SelectAll>();
        try {
            conn = DBUtil.getConn();
            stmt = conn.prepareStatement(sql);
            res = stmt.executeQuery();
            while (res.next()){
                SelectAll selectAll = new SelectAll();
                selectAll.setLaoid(res.getInt("tea.t_id"));
                selectAll.setLaoname(res.getString("tea.teaname"));
                selectAll.setSid(res.getInt("t_student.s_id"));
                selectAll.setSname(res.getString("t_student.s_name"));
                selectAll.setCsubject(res.getString("cj.subject"));
                selectAll.setCscore(res.getString("cj.score"));
                sa.add(selectAll);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            DBUtil.close(res,stmt,conn);
        }
        return sa;
    }