目录
我们在学习数据库的时候，了解到数据库中多表之前是存在关系的，而这种关系也是固定的，分为:一对多，多对 一，一对一和多对多。那么学习完JDBC,JSP,Servlet后。如何在WEB项目中操作数据库呢？这就是我们今天研究的重点:如何使用JAVA代码实现多表关系操作。
比较经典的一对多的关系就是学生表与年级表，两张表中，学生是多方，年级是一方。因为:一个年级可以有多名学生，但反过来一名学生只属于一个年级。先创建数据表
create table student2 ( stuid int primary key, stuname varchar(5), stuage int, gid int ); create table grade( gradeid int primary key , gname varchar(5) ); insert into grade values(1,'一年级'); insert into grade values(2,'二年级'); insert into grade values(3,'三年级'); insert into student2 values(1,'张三',18,1); insert into student2 values(2,'李四',14,2); insert into student2 values(3,'富贵',13,3); insert into student2 values(4,'王芳',17,1); insert into student2 values(5,'甜甜',15,2);
要求：类名=表名，列名=属性名(外键列也添加属性)
Student:
/
* @Author: 李梓豪
* @Description:
* @Date Created in 2020-11-15 13:24
*/
public class Student {
private int stuId;
private String stuName;
private int stuAge;
private int gid;
public Student() {
}
public Student(int studentId, String stuName, int stuAge, int gid) {
this.stuId = studentId;
this.stuName = stuName;
this.stuAge = stuAge;
this.gid = gid;
}
public int getStudentId() {
return stuId;
}
public void setStudentId(int studentId) {
this.stuId = studentId;
}
public String getStuName() {
return stuName;
}
public void setStuName(String stuName) {
this.stuName = stuName;
}
public int getStuAge() {
return stuAge;
}
public void setStuAge(int stuAge) {
this.stuAge = stuAge;
}
public int getGid() {
return gid;
}
public void setGid(int gid) {
this.gid = gid;
}
@Override
public String toString() {
return "Student{" +
"stuId=" + stuId +
", stuName='" + stuName + '\'' +
", stuAge=" + stuAge +
", gid=" + gid +
'}';
}
}
grade
package bean;
/
* @Author: 李梓豪
* @Description:
* @Date Created in 2020-11-15 13:41
*/
public class Grade {
private int gradeId;
private String gname;
public Grade() {
}
public Grade(int gradeId, String gname) {
this.gradeId = gradeId;
this.gname = gname;
}
public int getGradeId() {
return gradeId;
}
public void setGradeId(int gradeId) {
this.gradeId = gradeId;
}
public String getGname() {
return gname;
}
public void setGname(String gname) {
this.gname = gname;
}
}
数据表是通过外键列来维系两表关系。实体类是通过属性来维系两表关系。在建立一对多关系时，我们分析到年级是一方，学生是多方。一对多，是以一方为主，所以我们在一方添加多方的一个属性。那这个属性是对象还是集合呢？这里记住一句话:一方存多方的集合，多方存一方的对象。所以需要在年级表中添加下列属性：
Grade新增代码:
private List<Student> studentList;
public List<Student> getStudentList() {
return studentList;
}
public void setStudentList(List<Student> studentList) {
this.studentList = studentList;
}
Dao层
public interface GradeDao {
//查询某个年级信息(要求:展示年级名称和学生列表)
public Grade getGradeById(int id);
}
实现类:在实现类中需要连接数据库，并且查询结果来自于多张表。此时如何存储数据呢？给大家一个思路:1.在不考虑两表的情况下，先存储各自表中的数据 2.结合上面步骤中添加属性的问题，考虑应该把哪个类添加到另外一个类的属性中。代码如下：
package dao.impl;
import bean.Grade;
import bean.Student2;
import dao.GradeDao;
import util.DruidUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
/
* @Author: 李梓豪
* @Description:
* @Date Created in 2020-11-15 14:01
*/
public class GradeDaoImpl extends DruidUtil implements GradeDao {
@Override
public Grade findById(int gid) {
Grade grade = new Grade();
List<Student2> list = new ArrayList();
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = getConnection();
preparedStatement = connection.prepareStatement("select * from student2 s,grade g where s.gid=g.gradeid and g.gradeid=?");
preparedStatement.setInt(1,gid);
resultSet = preparedStatement.executeQuery();
while (resultSet.next()){
//1.将各自的数据信息进行存储
grade.setGradeId(resultSet.getInt("gradeid"));
grade.setGname(resultSet.getString("gname"));
Student2 student2 = new Student2();
student2.setStuName(resultSet.getString("stuname"));
student2.setStuAge(resultSet.getInt("stuage"));
//2.将学生信息和年级中的属性进行关联
//将学生放到一个集合中
list.add(student2);
}
//3.建立两者关系
grade.setStudent2List(list);
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
close(connection,preparedStatement,resultSet);
}
return grade;
}
}
说明:这里比较难理解的是关于对象的创建以及属性赋值
package test;
import bean.Grade;
import bean.Student2;
import dao.impl.GradeDaoImpl;
import java.util.List;
/
* @Author: 李梓豪
* @Description:
* @Date Created in 2020-11-15 14:23
*/
public class Demo1 {
public static void main(String[] args) {
GradeDaoImpl gradeDao = new GradeDaoImpl();
Grade grade = gradeDao.findById(1);
System.out.println(grade.getGname());
List<Student2> student2List = grade.getStudent2List();
for (Student2 student:student2List){
System.out.println("\t"+student.getStuName());
}
}
}
运行结果:
因为我们每次都是查询某一个班级的学生，所以创建班级对象的时候，应该在循环的外部。而学生信息每条都是不同的，索引创建学生对象的时候，应该在循环的内部。
学生是多方，秉持着“一方存多方的集合，多方存一方的对象”，那么我们就 需要在多的一方，添加一方的一个对象。此时学生类中需要添加下列代码。
private Grade grade;
public Grade getGrade() {
return grade;
}
public void setGrade(Grade grade) {
this.grade = grade;
}
public interface StudentDao {
//查询所有学生的信息(要求包含年级信息)
public List<Student> getAllStudent();
}
public class StudentDaoImpl extends DruidUtil implements StudentDao {
@Override
public List<Student2> findAll() {
ArrayList<Student2> studentList = new ArrayList<>();
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = getConnection();
preparedStatement = connection.prepareStatement("select * from student2 s,grade g where s.gid=g.gradeid ");
resultSet = preparedStatement.executeQuery();
while (resultSet.next()){
//1.各自存各自的数据
Grade grade = new Grade();
grade.setGradeId(resultSet.getInt("gradeid"));
grade.setGname(resultSet.getString("gname"));
Student2 student2 = new Student2();
student2.setStuName(resultSet.getString("stuname"));
student2.setStuAge(resultSet.getInt("stuage"));
//2.关联信息（在年级放在学生中，再将学生放在集合中）
student2.setGrade(grade);
studentList.add(student2);
}
} catch (SQLException throwable) {
throwable.printStackTrace();
} finally {
}
return studentList;
}
}
public class Demo2 {
public static void main(String[] args) {
GradeDaoImpl gradeDao = new GradeDaoImpl();
List<Student2> student2List = gradeDao.findAll();
for (Student2 student:student2List){
System.out.println(student.getStuName()+"\t"+student.getGrade().getGname());
}
}
}
同理，因为我们每次都是查询所有学生的信息（带班级），每条学生信息中的班级都可能是不同的。所以创建班级对象的时候，应该在循环的外部。而学生信息每条都是不同的，索引创建学生对象的时候，应该在循环的内部。
一对一在多表关系中存在场景不是很多，现在以妻子和丈夫的关系，模拟一对一的实现过程。
create table wife( wifeid int PRIMARY key, wifename varchar(5) ); create table husband( husid int PRIMARY KEY, husname varchar(5), wid int ); insert into wife values(1,'黄晓明'); insert into wife values(2,'邓超'); insert into husband values(1,'baby',1); insert into husband values(2,'孙俪',2);
public class Husband {
private int husId;
private String husName;
private int wid;
//setter and getter
}
public class Wife {
private int wifeId;
private String wifeName;
//setter and getter
}
建立实体类之间的一对一关系，还是依据“一方存多方的集合，多方存一方的对象”的原则，但是现在的问题是双方都是一方数据，此时记住原则“一方存另一方的对象”。所以代码改成：
妻子一方添加丈夫的对象
public class Wife {
private int wifeId;
private String wifeName;
private Husband husband;
//setter and getter
}
丈夫一方添加妻子的对象
public class Husband {
private int husId;
private String husName;
private int wid;
private Wife wife;
//setter and getter
}
Dao:
public interface WifeDao {
//查询某位妻子（包含丈夫的信息）
public Wife findByWifeId(int wid);
//查询某位丈夫（包含妻子的信息）
public Husband findByHid(int hid);
}
实现类:
public class WifeDaoImpl extends DruidUtil implements WifeDao {
@Override
public Wife findByWifeId(int wid) {
Wife wife = new Wife();
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = getConnection();
preparedStatement = connection.prepareStatement("SELECT * FROM wife w,husband h WHERE w.wifeid = h.wid AND w.wifeid=?");
preparedStatement.setInt(1, wid);
resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
//1.存各自的信息
wife.setWifeName(resultSet.getString("wifename"));
Husband husband = new Husband();
husband.setHusName(resultSet.getString("husname"));
//2.建立两者关系（将丈夫封装到妻子的对象中）
wife.setHusband(husband);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
close(connection,preparedStatement,resultSet);
}
return wife;
}
@Override
public Husband findByHid(int hid) {
Husband husband = new Husband();
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = getConnection();
preparedStatement = connection.prepareStatement("SELECT * FROM wife w,husband h WHERE w.wifeid = h.wid AND h.husid=?");
preparedStatement.setInt(1, hid);
resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
//1.存各自的信息
Wife wife = new Wife();
wife.setWifeName(resultSet.getString("wifename"));
husband.setHusName(resultSet.getString("husname"));
//2.建立两者关系（将妻子封装到丈夫的对象中）
husband.setWife(wife); }
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
close(connection,preparedStatement,resultSet);
}
return husband;
}
}
public class Demo3 {
public static void main(String[] args) {
WifeDaoImpl wifeDao = new WifeDaoImpl();
Wife wife = wifeDao.findByWifeId(1);
System.out.println(wife.getWifeName()+"\t"+wife.getHusband().getHusName());
Husband husband = wifeDao.findByHid(2);
System.out.println(husband.getHusName()+"\t"+husband.getWife().getWifeName());
}
}
运行结果
多对多在现实场景中也是不很多，比较特殊的就是权限列表的三表关系。菜单表和角色表之间属于多对多。某个功能菜单可以分配给多个角色，某个角色也可以拥有多个菜单，在这个分配过程中就是典型的多对多。在多对多中， 表的创建也比较有特点，必须是基于三张表来实现。
create table menu( menuid int primary key, menuname varchar(10) ); create table role( roleid int primary key, rolename varchar(10) ); create table middle( middleid int primary key, mid int, rid int ); insert into menu values(1,'用户管理'); insert into menu values(2,'菜单管理'); insert into menu values(3,'角色管理'); insert into role values(1,'超级管理员'); insert into role values(2,'管理员'); insert into role values(3,'总经理'); insert into middle values(1,1,1); insert into middle values(2,2,1); insert into middle values(3,3,1); insert into middle values(4,1,2); insert into middle values(5,2,2); insert into middle values(6,1,3);
Menu:
public class Menu {
private int menuId;
private String menuName;
//getter and setter
}
Role:
public class Role {
private int roleId;
private String roleName;
//getter and setter
}
建立实体类之间的多对多关系，还是依据“一方存多方的集合，多方存一方的对象”的原则，但是现在的问题是双方都是多方数据，此时记住原则“多方存另一方的集合”。代码如下:
Menu:
public class Menu {
private int menuId;
private String menuName;
private List<Role> roleList;
//getter and setter
}
Dao:
public interface RoleDao {
//查询某个菜单信息（包含角色）
public Menu findByMenuId(int mid);
//c查询某个角色信息（包含菜单）
public Role findByRoleId(int roleid);
}
实现类:
public class RoleDaoImpl extends DruidUtil implements RoleDao {
@Override
public Menu findByMenuId(int mid) {
Menu menu = new Menu();
List<Role> roleList = new ArrayList<>();
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = getConnection();
preparedStatement = connection.prepareStatement("SELECT * FROM menu m,role r,middle WHERE m.menuid=middle.mid AND r.roleid = middle.rid AND m.menuid=?");
preparedStatement.setInt(1, mid);
resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
//1.先各自存数据
menu.setMenuName(resultSet.getString("menuname"));
Role role = new Role();
role.setRoleName(resultSet.getString("rolename"));
//2.建立二者关系
roleList.add(role);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
close(connection,preparedStatement,resultSet);
}
menu.setRoleList(roleList);
return menu;
}
@Override
public Role findByRoleId(int roleid) {
Role role = new Role();
List<Menu> menuList = new ArrayList<>();
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = getConnection();
preparedStatement = connection.prepareStatement("SELECT * FROM menu m,role r,middle WHERE m.menuid=middle.mid AND r.roleid = middle.rid AND r.roleid=?");
preparedStatement.setInt(1, roleid);
resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
//1.先各自存数据
Menu menu = new Menu();
menu.setMenuName(resultSet.getString("menuname"));
role.setRoleName(resultSet.getString("rolename"));
//2.建立二者关系
menuList.add(menu);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
close(connection,preparedStatement,resultSet);
}
role.setMenuList(menuList);
return role;
}
}
public class Demo4 {
public static void main(String[] args) {
RoleDaoImpl roleDao = new RoleDaoImpl();
Menu menu =roleDao.findByMenuId(2);
System.out.println(menu.getMenuName()+"的权限管理人员：");
List<Role> roleList = menu.getRoleList();
for(Role role:roleList){
System.out.println(role.getRoleName());
}
Role role = roleDao.findByRoleId(2);
System.out.println(role.getRoleName()+"的菜单权限：");
List<Menu> menuList = role.getMenuList();
for (Menu menu1:menuList){
System.out.println(menu1.getMenuName());
}
}
}
运行结果：
