JDBC多表联查

在单一表进行查询时，只需要对表中的单个字段进行解析即可；例如下面代码：

@Overridepublic List<ClassBean> selectAllDao() {List list = new ArrayList();try {String sql = "select * from class";rs = select(sql);while(rs.next()) {ClassBean cb = new ClassBean();cb.setClassId(rs.getInt("classid"));cb.setClassName(rs.getString("classname"));list.add(cb);}} catch (Exception e) {e.printStackTrace();}finally {DaoUtil.closeResource(conn, statement, rs);}return list;}

在进行多表联查时会遇到第一个问题就是在实体类中不存在从表的字段，紧接着第二个问题在就是主表与从表的对应关系是一对一还是一对多关系。

以学生和班级表为例：从图中可以看出表对应关系

在这里插入图片描述

两个表的结构如下：

在这里插入图片描述

创建两个实体类：

student的实体类，加入外部属性，也就是引入class的属性，以为学生和班级是一对一的关系，因此只需要一个班级对象即可：

public class Student {private int sid;private String sname;private Date birthday;private String ssex;private int classId;	//外部属性private Banji bj;//get、set、构造器、toString方法省略
}

Banji表（class是Java关键字，所以用拼音）分析可知，一个班级里有多个学生，因此需要引入一个集合外部属性，用来存储班级中的多个学生：

public class Banji {private int classId;private String className;//外部属性private List<Student> stu;//get、set、构造器、toString方法省略
}

在这里将部分的代码进行了封装，形成了两个类：

DaoUtil工具类：

package com.li.dao;import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
import javax.sql.DataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;public class DaoUtil {private static DataSource ds = null;static {try {FileInputStream stream = new FileInputStream("./src/druid.properties");		Properties pro = new Properties();pro.load(stream);			//创建连接			ds = DruidDataSourceFactory.createDataSource(pro);	}catch(Exception e) {e.printStackTrace();}}//创建连接对象方法public static Connection getConn() {Connection conn = null;try {conn = ds.getConnection();} catch (SQLException e) {e.printStackTrace();}return conn;}//关闭资源操作public static void closeResource(Connection conn,PreparedStatement prestatm, ResultSet rs) {if(conn != null) {try {conn.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}if(prestatm != null) {try {prestatm.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}if(rs != null) {try {rs.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}}
}

BaseDao工具类：

package com.li.dao;import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;public class BaseDao {protected Connection conn;protected PreparedStatement prestatm;protected ResultSet rs;protected ResultSet query(String sql, Object ... arge) {ResultSet rs = null;try {conn = DaoUtil.getConn();prestatm  = conn.prepareStatement(sql);if(arge != null) {for(int i = 0; i< arge.length; i++) {prestatm.setObject(i+1, arge[i]);}}rs = prestatm.executeQuery();} catch (Exception e) {e.printStackTrace();}return rs;}
}

学生的实现类（接口省略）：

package com.li.dao.impl;import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;import com.li.bean.Banji;
import com.li.bean.Student;
import com.li.dao.BaseDao;
import com.li.dao.DaoUtil;
import com.li.dao.IStudentDao;public class StudentDaoImpl extends BaseDao implements IStudentDao{@Overridepublic List<Student> findAllStudent() {List<Student> list = new ArrayList();try {String sql = "select * from student left join class on student.classid = class.classid";rs = query(sql);while (rs.next()) {Student stu = new Student();Banji bj = new Banji();// 学生的数据stu.setBirthday(rs.getDate("birthday"));stu.setClassId(rs.getInt("classid"));stu.setSid(rs.getInt("sid"));stu.setSname(rs.getString("sname"));stu.setSsex(rs.getString("ssex"));//给班级属性赋值bj.setClassId(rs.getInt("classid"));bj.setClassName(rs.getString("classname"));//将班级对象赋值给班级中定义的实体类stu.setBj(bj);list.add(stu);}} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}finally {DaoUtil.closeResource(conn, prestatm, rs);}return list;}}

班级的集合中存放班级编号等信息，班级编号的集合中又存放学生，就是集合中套集合：

在这里插入图片描述

Banji实现类（接口省略）：

package com.li.dao.impl;import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;import com.li.bean.Banji;
import com.li.bean.Student;
import com.li.dao.BaseDao;
import com.li.dao.DaoUtil;
import com.li.dao.IBanjiDao;public class BanjiDaoImpl extends BaseDao implements IBanjiDao{@Overridepublic List<Banji> findAllBanji() {List<Banji> blist = new ArrayList(); String sql = "select * from class left join student on class.classid = student.classid";rs = query(sql);try {while(rs.next()) {boolean falg = false;int index = -1;for(int i = 0; i <blist.size(); i++) {//判断班级集合中是否存在某字段的班级编号if(rs.getInt("classid") == blist.get(i).getClassId()) {falg =true;index = i;break;}}//已经存在班级集合if(falg) {Student stu =  new Student();stu.setBirthday(rs.getDate("birthday"));stu.setClassId(rs.getInt("classid"));stu.setSid(rs.getInt("sid"));stu.setSname(rs.getString("sname"));stu.setSsex(rs.getString("ssex"));Banji banji = blist.get(index);banji.getStu().add(stu);}//不存在班级集合else {//新建班级集合并放入List<Student> slist = new ArrayList();Banji bj = new Banji();bj.setClassId(rs.getInt("classId"));bj.setClassName(rs.getString("classname"));bj.setStu(slist);//新建学生类放入集合中Student stu =  new Student();stu.setBirthday(rs.getDate("birthday"));stu.setClassId(rs.getInt("classid"));stu.setSid(rs.getInt("sid"));stu.setSname(rs.getString("sname"));stu.setSsex(rs.getString("ssex"));slist.add(stu);blist.add(bj);}}} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}finally {DaoUtil.closeResource(conn, prestatm, rs);}return blist;}
}

测试类：

public static void main(String[] args) {IBanjiDao bjd = new BanjiDaoImpl();List<Banji> list = bjd.findAllBanji();IStudentDao sdi = new StudentDaoImpl();List<Student> list = sdi.findAllStudent();list.forEach(System.out::println);}