多表操作实例

我们在学习数据库的时候，了解到数据库中多表之前是存在关系的，而这种关系也是固定的，分为:一对多，多对 一，一对一和多对多。那么学习完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());
        }

    }
}