目录
1.多表关系
我们在学习数据库的时候,了解到数据库中多表之前是存在关系的,而这种关系也是固定的,分为:一对多,多对 一,一对一和多对多。那么学习完JDBC,JSP,Servlet后。如何在WEB项目中操作数据库呢?这就是我们今天研究的重点:如何使用JAVA代码实现多表关系操作。
2.多表操作之一对多
2.1 数据表
比较经典的一对多的关系就是学生表与年级表,两张表中,学生是多方,年级是一方。因为:一个年级可以有多名学生,但反过来一名学生只属于一个年级。先创建数据表
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);
2.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; } }
2.3 建立两表之间的属性关系
数据表是通过外键列来维系两表关系。实体类是通过属性来维系两表关系。在建立一对多关系时,我们分析到年级是一方,学生是多方。一对多,是以一方为主,所以我们在一方添加多方的一个属性。那这个属性是对象还是集合呢?这里记住一句话:一方存多方的集合,多方存一方的对象。所以需要在年级表中添加下列属性:
Grade新增代码:
private List<Student> studentList; public List<Student> getStudentList() { return studentList; } public void setStudentList(List<Student> studentList) { this.studentList = studentList; }
2.4 创建Dao层接口代码和实现类,操作数据库
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; } }
说明:这里比较难理解的是关于对象的创建以及属性赋值
2.5 测试类
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()); } } }
运行结果:
因为我们每次都是查询某一个班级的学生,所以创建班级对象的时候,应该在循环的外部。而学生信息每条都是不同的,索引创建学生对象的时候,应该在循环的内部。
3.多表操作之多对一
3.1 在上一步的基础上,完成多对一。
学生是多方,秉持着“一方存多方的集合,多方存一方的对象”,那么我们就 需要在多的一方,添加一方的一个对象。此时学生类中需要添加下列代码。
private Grade grade; public Grade getGrade() { return grade; } public void setGrade(Grade grade) { this.grade = grade; }
3.2 在Dao层添加接口方法:
public interface StudentDao { //查询所有学生的信息(要求包含年级信息) public List<Student> getAllStudent(); }
3.3 添加实现类:实现类中主要考虑如何建立两者关联
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; } }
3.4 测试类:
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()); } } }
运行结果
同理,因为我们每次都是查询所有学生的信息(带班级),每条学生信息中的班级都可能是不同的。所以创建班级对象的时候,应该在循环的外部。而学生信息每条都是不同的,索引创建学生对象的时候,应该在循环的内部。
4.多表操作之一对一
一对一在多表关系中存在场景不是很多,现在以妻子和丈夫的关系,模拟一对一的实现过程。
4.1 创建数据表
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);
4.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 }
4.3 添加Dao和实现类
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; } }
4.4 测试类
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()); } }
运行结果
5.多表操作之多对多
多对多在现实场景中也是不很多,比较特殊的就是权限列表的三表关系。菜单表和角色表之间属于多对多。某个功能菜单可以分配给多个角色,某个角色也可以拥有多个菜单,在这个分配过程中就是典型的多对多。在多对多中, 表的创建也比较有特点,必须是基于三张表来实现。
5.1 创建数据表
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);
5.2 定义实体类:中间表不需要生成实体类
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 }
5.3 定义接口和实现类
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; } }
5.4 测试类
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()); } } }
运行结果: