一对多级联查询
在实际开发中经常会遇到一对多查询,比如说一个班级对应多个学生,在需求中我们又需要查询出每个班的学生。
首先先准备数据表
直接运行下面SQL语句即可
create database boss;
use boss;
create table meeting
(
id int auto_increment primary key,
theme varchar(255)
);
create table boss
(
id int auto_increment primary key,
name varchar(255),
company varchar(255),
meeting_id int
);
create table car
(
id int auto_increment primary key,
brand varchar(255),
color varchar(255),
parameter varchar(255)
);
insert into meeting(theme)
values ('四大家族会议');
insert into car(brand, color, parameter)
values ('兰博基尼', '蓝色', '2000万-5000万');
insert into car(brand, color, parameter)
values ('法拉利','红色','500万-2000万');
insert into car(brand, color, parameter)
values('迈凯伦','黑色','500万-2000万');
insert into car(brand, color, parameter)
values ('布加迪','灰色','500万-2000万');
insert into boss(name, company, meeting_id)
values ('小赵','赵氏家族',1);
insert into boss(name, company, meeting_id)
values ('小王','王氏家族',1);
insert into boss(name, company, meeting_id)
values ('小李','李氏家族',1);
insert into boss(name, company, meeting_id)
values ('小陆','陆氏家族',1);
实体类
Meeting类
package cn.zl.jdbc3.entity;
import java.util.List;
public class Meeting {
private long id;
private String theme;
private List<Boss> bossList;
public List<Boss> getBossList() {
return bossList;
}
public void setBossList(List<Boss> bossList) {
this.bossList = bossList;
}
public long getId() {
return id;
}
public void setId(long id) {
this.id = id;
}
public String getTheme() {
return theme;
}
public void setTheme(String theme) {
this.theme = theme;
}
@Override
public String toString() {
return "Meeting{" +
"id=" + id +
", theme='" + theme + '\'' +
"\n, bossList=" + bossList +
'}';
}
}
Boss类
package cn.zl.jdbc3.entity;
public class Boss {
private long id;
private String name;
private String company;
private Car car;
public Car getCar() {
return car;
}
public void setCar(Car car) {
this.car = car;
}
public long getId() {
return id;
}
public void setId(long id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getCompany() {
return company;
}
public void setCompany(String company) {
this.company = company;
}
@Override
public String toString() {
return "Boss{" +
"id=" + id +
", name='" + name + '\'' +
", company='" + company + '\'' +
", car=" + car +
'}';
}
}
Car类
package cn.zl.jdbc3.entity;
public class Car {
private long id;
private String brand;
private String color;
private String parameter;
public long getId() {
return id;
}
public void setId(long id) {
this.id = id;
}
public String getBrand() {
return brand;
}
public void setBrand(String brand) {
this.brand = brand;
}
public String getColor() {
return color;
}
public void setColor(String color) {
this.color = color;
}
public String getParameter() {
return parameter;
}
public void setParameter(String parameter) {
this.parameter = parameter;
}
@Override
public String toString() {
return "Car{" +
"id=" + id +
", brand='" + brand + '\'' +
", color='" + color + '\'' +
", parameter='" + parameter + '\'' +
'}' + "\n";
}
}
Dao类
MeetingDao类
package cn.zl.jdbc3.dao;
import cn.zl.jdbc3.entity.Boss;
import cn.zl.jdbc3.entity.Meeting;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.List;
public class MeetingDao {
public Meeting findMeeting(int id)throws Exception{
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/boss?useSSL=false",
"root", "123456");
PreparedStatement statement = connection.prepareStatement("select *from meeting where id=?");
statement.setObject(1,id);
ResultSet resultSet = statement.executeQuery();
Meeting meeting=null;
if (resultSet.next()){
BossDao bossDao=new BossDao();
int id1 = resultSet.getInt("id");
List<Boss> list = bossDao.findBoss(id1);
meeting=new Meeting();
meeting.setId(id1);
meeting.setTheme(resultSet.getString("theme"));
meeting.setBossList(list);
}
resultSet.close();
statement.close();
connection.close();
return meeting;
}
}
BossDao类
package cn.zl.jdbc3.dao;
import cn.zl.jdbc3.entity.Boss;
import cn.zl.jdbc3.entity.Car;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
public class BossDao {
public List<Boss> findBoss(int id) throws Exception {
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/boss?useSSL=false",
"root", "123456");
PreparedStatement statement = connection.prepareStatement("select *from boss where meeting_id=?");
statement.setObject(1, id);
ResultSet resultSet = statement.executeQuery();
Boss boss = null;
List<Boss> list = new ArrayList<>();
while (resultSet.next()) {
CarDao carDao = new CarDao();
boss = new Boss();
int id1 = resultSet.getInt("id");
Car car = carDao.findCar(id1);
boss.setId(id1);
boss.setName(resultSet.getString("name"));
boss.setCompany(resultSet.getString("company"));
boss.setCar(car);
list.add(boss);
}
return list;
}
}
CarDao类
package cn.zl.jdbc3.dao;
import cn.zl.jdbc3.entity.Car;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class CarDao {
public Car findCar(int id) throws Exception {
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/boss?useSSL=false",
"root", "123456");
PreparedStatement statement = connection.prepareStatement("select *from car where id=?");
statement.setObject(1, id);
ResultSet resultSet = statement.executeQuery();
Car car = null;
if (resultSet.next()) {
car = new Car();
car.setId(resultSet.getInt("id"));
car.setBrand(resultSet.getString("brand"));
car.setColor(resultSet.getString("color"));
car.setParameter(resultSet.getString("parameter"));
}
resultSet.close();
statement.close();
connection.close();
return car;
}
}
主类
DbTest01
public static void main(String[] args) throws Exception {
MeetingDao meetingDao=new MeetingDao();
Meeting meeting = meetingDao.findMeeting(1);
System.out.println(meeting);
}