我们在学习数据库的时候,了解到数据库中多表之前是存在关系的,而这种关系也是固定的,分为:一对多,多对 一,一对一和多对多。那么学习完JDBC,JSP,Servlet后。如何在WEB项目中操作数据库呢?这就是我们今天研究的重点:如何使用JAVA代码实现多表关系操作。
1.多表操作之一对多
比较经典的一对多的关系就是学生表与年级表,两张表中,学生是多方,年级是一方。因为:一个年级可以有多名学生,但反过来一名学生只属于一个年级。
1.创建数据表

2.创建实体类
年级类
package bean;
import java.util.List;
public class Grade {
private int gradeId;
private String gname;
//数据库中使用外键列保证两表关系,实体类中使用属性保证两表关系
private List<Student> studentList;
@Override
public String toString() {
return "Grade{" +
"gradeId=" + gradeId +
", gname='" + gname + '\'' +
", studentList=" + studentList +
'}';
}
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;
}
public List<Student> getStudentList() {
return studentList;
}
public void setStudentList(List<Student> studentList) {
this.studentList = studentList;
}
}
学生类
package bean;
public class Student {
private int stuId;
private String stuName;
private int stuAge;
private int gid;
@Override
public String toString() {
return "Student{" +
"stuId=" + stuId +
", stuName='" + stuName + '\'' +
", stuAge=" + stuAge +
", gid=" + gid +
'}';
}
public int getStuId() {
return stuId;
}
public void setStuId(int stuId) {
this.stuId = stuId;
}
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;
}
}
两表之间的属性关系
数据表是通过外键列来维系两表关系。实体类是通过属性来维系两表关系。在建立一对多关系时,我们分析到年级 是一方,学生是多方。一对多,是以一方为主,所以我们在一方添加多方的一个属性。那这个属性是对象还是集合 呢?这里记住一句话:一方存多方的集合,多方存一方的对象。所以需要在年级表中添加下列属性
3. 修改Grade代码建立表关系
private List<Student> studentList; public List<Student> getStudentList() {
return studentList;
}
public void setStudentList(List<Student> studentList) {
this.studentList = studentList; }
4.创建Dao层接口代码和实现类,操作数据库
接口类
package dao;
import bean.Grade;
public interface GradeDao {
//查询某个年级信息(要求同时查询出学生信息)
public Grade findById(int gid);
}
实现类
package dao.impl;
import bean.Grade;
import bean.Student;
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;
public class GradeDaoImpl extends DruidUtil implements GradeDao {
@Override
public Grade findById(int gid) {
Grade grade = new Grade();
ArrayList<Student> students = new ArrayList<>();
Connection connection = null;
PreparedStatement preparedStatement =null;
ResultSet resultSet = null;
try {
connection = getConnection();
preparedStatement = connection.prepareStatement("select * from student 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"));
Student student = new Student();
student.setStuName(resultSet.getString("stuname"));
student.setStuAge(resultSet.getInt("stuage"));
//2.将学生信息和年级中的属性进行关联
//将学生放到一个集合中
students.add(student);
}
//3.建立两者关系
grade.setStudentList(students);
} catch (SQLException e) {
e.printStackTrace();
} finally {
close(connection,preparedStatement,resultSet);
}
return grade;
}
5.测试类
package test;
import bean.Grade;
import bean.Student;
import dao.impl.GradeDaoImpl;
import java.util.List;
public class Demo01 {
public static void main(String[] args) {
GradeDaoImpl gradeDao = new GradeDaoImpl();
Grade grade= gradeDao.findById(1);
System.out.println(grade.getGname());
List<Student> studentList = grade.getStudentList();
for (Student student : studentList) {
System.out.println("\t"+student.getStuName());
}
}
}
测试结果
2.多表操作之多对一
在上一步的基础上,完成多对一。学生是多方,秉持着“一方存多方的集合,多方存一方的对象”,那么我们就 需要在多的一方,添加一方的一个对象。
1.此时学生类中需要添加下列代码
private Grade grade;
public Grade getGrade() {
return grade;
}
public void setGrade(Grade grade) {
this.grade = grade;
}
2 在Dao层添加接口方法:
public interface StudentDao {
//查询所有学生的信息(要求包含年级信息)
public List<Student> getAllStudent();
}
3 添加实现类:实现类中主要考虑如何建立两者关联
@Override
public List<Student> findAll() {
ArrayList<Student> students = new ArrayList<>();
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = getConnection();
preparedStatement = connection.prepareStatement("select * from student 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"));
Student student = new Student();
student.setStuName(resultSet.getString("stuname"));
student.setStuAge(resultSet.getInt("stuage"));
//2.关联信息(将年级放在学生中,再将学生放在集合中)
student.setGrade(grade);
students.add(student);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
close(connection,preparedStatement,resultSet);
}
return students;
}
4.测试类
package test;
import bean.Student;
import dao.impl.GradeDaoImpl;
import java.util.List;
public class Demo02 {
public static void main(String[] args) {
GradeDaoImpl gradeDao = new GradeDaoImpl();
List<Student> studentList = gradeDao.findAll();
for (Student student : studentList) {
System.out.println(student.getStuName()+"\t"+student.getGrade().getGname());
}
}
}
测试结果
3..多表操作之一对一
一对一在多表关系中存在场景不是很多,现在以妻子和丈夫的关系,模拟一对一的实现过程。
1 创建数据表
2 创建实体类
package bean;
//妻子类
public class Wife {
private int wifeId;
private String wifeName;
}
package bean;
//丈夫类
public class Husband {
private int husId;
private String husName;
private int wid;
}
建立实体类之间的一对一关系,还是依据“一方存多方的集合,多方存一方的对象”的原则,但是现在的问题是双方 都是一方数据,此时记住原则“一方存另一方的对象”。所以代码要增加对象。
3.修改代码建立两表关系
1.妻子一方添加丈夫的对象
package bean;
public class Wife {
private int wifeId;
private String wifeName;
//妻子一方添加丈夫的对象
private Husband husband;
@Override
public String toString() {
return "Wife{" +
"wifeId=" + wifeId +
", wifeName='" + wifeName + '\'' +
", husband=" + husband +
'}';
}
public int getWifeId() {
return wifeId;
}
public void setWifeId(int wifeId) {
this.wifeId = wifeId;
}
public String getWifeName() {
return wifeName;
}
public void setWifeName(String wifeName) {
this.wifeName = wifeName;
}
public Husband getHusband() {
return husband;
}
public void setHusband(Husband husband) {
this.husband = husband;
}
}
2.丈夫一方添加妻子的对象
package bean;
public class Husband {
private int husId;
private String husName;
private int wid;
//丈夫一方添加妻子的对象
private Wife wife;
@Override
public String toString() {
return "Husband{" +
"husId=" + husId +
", husName='" + husName + '\'' +
", wid=" + wid +
", wife=" + wife +
'}';
}
public int getHusId() {
return husId;
}
public void setHusId(int husId) {
this.husId = husId;
}
public String getHusName() {
return husName;
}
public void setHusName(String husName) {
this.husName = husName;
}
public int getWid() {
return wid;
}
public void setWid(int wid) {
this.wid = wid;
}
public Wife getWife() {
return wife;
}
public void setWife(Wife wife) {
this.wife = wife;
}
}
4.创建Dao层接口代码和实现类,操作数据库
接口类
package dao;
import bean.Husband;
import bean.Wife;
public interface WifeDao {
//查询某位妻子(包含丈夫信息)
public Wife findByWifeId(int wid);
//查询某位丈夫(包含妻子信息)
public Husband findByHid(int hid);
}
实现类
package dao.impl;
import bean.Husband;
import bean.Wife;
import dao.WifeDao;
import util.DruidUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
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;
}
}
5.测试类
package test;
import bean.Husband;
import bean.Wife;
import dao.impl.WifeDaoImpl;
public class Demo03 {
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());
}
}
测试结果
4.多表操作之多对多
多对多在现实场景中也是不很多,比较特殊的就是权限列表的三表关系。菜单表和角色表之间属于多对多。某个功能菜单可以分配给多个角色,某个角色也可以拥有多个菜单,在这个分配过程中就是典型的多对多。在多对多中,表的创建也比较有特点,必须是基于三张表来实现。
1.创建数据表
2.创建实体类(中间表不需要生成实体类)
package bean;
//菜单类
public class Menu {
private int menuId;
private String menuName;
}
package bean;
//功能类
public class Role {
private int roleId;
private String roleName;
}
3.修改代码建立两表关系
建立实体类之间的多对多关系,还是依据“一方存多方的集合,多方存一方的对象”的原则,但是现在的问题是双方都是多方数据,此时记住原则“多方存另一方的集合”。
1.在菜单里添加角色集合
package bean;
import java.util.List;
//菜单类
public class Menu {
private int menuId;
private String menuName;
//在菜单里添加角色集合
private List<Role> roleList;
@Override
public String toString() {
return "Menu{" +
"menuId=" + menuId +
", menuName='" + menuName + '\'' +
", roleList=" + roleList +
'}';
}
public int getMenuId() {
return menuId;
}
public void setMenuId(int menuId) {
this.menuId = menuId;
}
public String getMenuName() {
return menuName;
}
public void setMenuName(String menuName) {
this.menuName = menuName;
}
public List<Role> getRoleList() {
return roleList;
}
public void setRoleList(List<Role> roleList) {
this.roleList = roleList;
}
}
2.在角色里添加菜单集合
package bean;
import java.util.List;
//角色类
public class Role {
private int roleId;
private String roleName;
//在角色类添加菜单的集合
private List<Menu> menuList;
@Override
public String toString() {
return "Role{" +
"roleId=" + roleId +
", roleName='" + roleName + '\'' +
", menuList=" + menuList +
'}';
}
public int getRoleId() {
return roleId;
}
public void setRoleId(int roleId) {
this.roleId = roleId;
}
public String getRoleName() {
return roleName;
}
public void setRoleName(String roleName) {
this.roleName = roleName;
}
public List<Menu> getMenuList() {
return menuList;
}
public void setMenuList(List<Menu> menuList) {
this.menuList = menuList;
}
}
4.创建Dao层接口代码和实现类,操作数据库
接口类
package dao;
import bean.Menu;
import bean.Role;
public interface RoleDao {
//查询某个菜单信息(包含角色)
public Menu findByMenuId(int mid);
//查询某个角色信息(包含菜单)
public Role findByRoleId(int roleid);
}
实现类
package dao.impl;
import bean.Menu;
import bean.Role;
import dao.RoleDao;
import util.DruidUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
public class RoleDaoImpl extends DruidUtil implements RoleDao {
@Override
public Menu findByMenuId(int mid) {
Menu menu = new Menu();
ArrayList<Role> roles = 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.建立二者关系
roles.add(role);
}
menu.setRoleList(roles);
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
close(connection,preparedStatement,resultSet);
}
return menu;
}
@Override
public Role findByRoleId(int roleid) {
Role role = new Role();
ArrayList<Menu> menuArrayList = new ArrayList<Menu>();
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.建立二者关系
menuArrayList.add(menu);
}
role.setMenuList(menuArrayList);
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
close(connection,preparedStatement,resultSet);
}
return role;
}
}
5.测试类
1.查询角色
package test;
import bean.Menu;
import bean.Role;
import dao.impl.RoleDaoImpl;
import java.util.List;
public class Demo04{
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());
}
}
}
2.查询菜单
package test;
import bean.Menu;
import bean.Role;
import dao.impl.RoleDaoImpl;
import java.util.List;
public class Demo04{
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 menu : menuList) {
System.out.println(menu.getMenuName());
}
}
}