06-JavaWEB_多表操作

一，一对多

1，数据表

2，创建实体类

3，建立两表之间的属性关系

4，创建Dao层接口代码和实现类，操作数据库

5，测试类

二，多对一

1，修改实体类

2，在Dao层添加接口方法:

3，添加实现类:实现类中主要考虑如何建立两者关联

4，测试类

三，一对一

1，创建数据表

2，创建实体类

3，添加Dao和实现类

4，测试类

四，多对多

1，创建数据表

2，定义实体类：中间表不需要生成实体类

3，定义接口和实现类

4，测试类

一，一对多

1，数据表

比较经典的一对多的关系就是学生表与年级表，两张表中，学生是多方，年级是一方。因为:一个年级可以有多名学生，但反过来一名学生只属于一个年级。先创建数据表

create table student ( 
  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 student values(1,'张三',18,1); 
insert into student values(2,'李四',14,2); 
insert into student values(3,'富贵',13,3); 
insert into student values(4,'王芳',17,1); 
insert into student values(5,'甜甜',15,2);

2，创建实体类

要求：类名=表名，列名=属性名(外键列也添加属性)

Student:

package bean;

public class Student {
    private int stuId;
    private String stuName;
    private int  stuAge;
    private int gid;

    public Grade getGrade() {
        return grade;
    }

    public void setGrade(Grade grade) {
        this.grade = grade;
    }

    private Grade grade;
    @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;
    }
}

Grade:

package bean;

import java.util.List;

public class Grade {
    private int gradeId;
    private String gname;


    @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;
    }


}

3，建立两表之间的属性关系

数据表是通过外键列来维系两表关系。实体类是通过属性来维系两表关系。在建立一对多关系时，我们分析到年级是一方，学生是多方。一对多，是以一方为主，所以我们在一方添加多方的一个属性。那这个属性是对象还是集合呢？这里记住一句话:一方存多方的集合，多方存一方的对象。所以需要在年级表中添加下列属性：

Grade新增代码:

private List<Student> studentList; 
public List<Student> getStudentList() { 
  return studentList; 
}
public void setStudentList(List<Student> studentList) { 
  this.studentList = studentList; 
}

4，创建Dao层接口代码和实现类，操作数据库

Dao层

package dao;

import bean.Grade;
import bean.Student;

import java.util.List;

public interface GradeDao {
    //查询某个年级信息(要求同时查询出学生的信息)
    public Grade findById(int gid);
}

实现类:

在实现类中需要连接数据库，并且查询结果来自于多张表。此时如何存储数据呢？给大家一个思路:1.在不考虑两表的情况下，先存储各自表中的数据 2.结合上面步骤中添加属性的问题，考虑应该把哪个类添加到另外一个类的属性中。代码如下：

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;
import java.util.List;

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 throwables) {
            throwables.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 Demo1 {
    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());
        }
    }
}

二，多对一

1，修改实体类

在上一步的基础上，完成多对一。学生是多方，秉持着“一方存多方的集合，多方存一方的对象”，那么我们就需要在多的一方，添加一方的一个对象。此时学生类中需要添加下列代码

private Grade grade;    
    
    public Grade getGrade() {
        return grade;
    }

    public void setGrade(Grade grade) {
        this.grade = grade;
    }

2，在Dao层添加接口方法:

package dao;

import bean.Grade;
import bean.Student;

import java.util.List;

public interface GradeDao {
    //查询某个年级信息(要求同时查询出学生的信息)
    public Grade findById(int gid);
    //查询学生的信息(包含年级信息)
    public List<Student> findAll();
}

3，添加实现类:实现类中主要考虑如何建立两者关联

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.Grade;
import bean.Student;
import dao.impl.GradeDaoImpl;

import java.util.List;

public class Demo2 {
    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());
        }
    }
}

三，一对一

一对一在多表关系中存在场景不是很多，现在以妻子和丈夫的关系，模拟一对一的实现过程。

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);

2，创建实体类

建立实体类之间的一对一关系，还是依据“一方存多方的集合，多方存一方的对象”的原则，但是现在的问题是双方都是一方数据，此时记住原则“一方存另一方的对象”。

package bean;

public class Husband {
    private int husId;
    private String husName;
    private int wid;
    private Wife wife;

    // getter和setter方法
}

package bean;

public class Wife {
    private int wifeId;
    private String wifeName;

    private Husband husband;
    // getter和setter方法
}

3，添加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;
    }
}

4，测试类

package test;

import bean.Husband;
import bean.Student;
import bean.Wife;
import dao.impl.GradeDaoImpl;
import dao.impl.WifeDaoImpl;

import java.util.List;

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());

    }
}

四，多对多

多对多在现实场景中也是不很多，比较特殊的就是权限列表的三表关系。菜单表和角色表之间属于多对多。某个功能菜单可以分配给多个角色，某个角色也可以拥有多个菜单，在这个分配过程中就是典型的多对多。在多对多中，表的创建也比较有特点，必须是基于三张表来实现。

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);

2，定义实体类：中间表不需要生成实体类

建立实体类之间的多对多关系，还是依据“一方存多方的集合，多方存一方的对象”的原则，但是现在的问题是双方都是多方数据，此时记住原则“多方存另一方的集合”。代码如下:

public class Menu { 
    private int menuId; 
    private String menuName; 
    private List<Role> roleList;
    //getter and setter 
}

Role

public class Role { 
    private int roleId; 
    private String roleName; 
    private List<Menu> menuList;
    //getter and setter 
}

3，定义接口和实现类

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;
    }
}

4，测试类

package test;

import bean.Husband;
import bean.Menu;
import bean.Role;
import bean.Wife;
import dao.impl.RoleDaoImpl;
import dao.impl.WifeDaoImpl;

import java.util.List;

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 menu : menuList) {
            System.out.println(menu.getMenuName());
        }

    }
}

06-JavaWEB_多表操作

一，一对多

1，数据表

2，创建实体类

3，建立两表之间的属性关系

4，创建Dao层接口代码和实现类，操作数据库

5，测试类

二，多对一

1，修改实体类

2，在Dao层添加接口方法:

3，添加实现类:实现类中主要考虑如何建立两者关联

4，测试类

三，一对一

1，创建数据表

2，创建实体类

3，添加Dao和实现类

4，测试类

四，多对多

1，创建数据表

2，定义实体类：中间表不需要生成实体类

3，定义接口和实现类

4，测试类

继续阅读

27 Best Free Eclipse Plug-ins for Java Developer to be ProductiveCode Quality PluginsText Editor PluginsDependency ManagementVersion Control Integration PluginsFramework Development Continuous Integration Related PluginsOther Utility Plugins

Java String.format方法的简单使用

neo4j之cypher使用文档

GitHub连夜封杀！这份阿里 10W 字内部 Java 字面试手册到底有多强？

spark/scala关于【资源文件】加载方法概述外部文件加载方案测试资源文件打包入jar包中小结

NOSQL安全攻击

mybatis_入门程序Mybatis入门

AOP编程_Android优雅权限框架(1)概念基础，2021金三银四前言正文大纲正文

登录plsql 报错 the account is locked --用户被锁

Effective Java 8:通用程序设计

SequoiaDB巨杉数据库C++驱动概述

OOM三种类型

工厂模式-三种类型

【递归】高效率求2的n次幂

win10本地scala和spark安装安装scala安装spark

scala (3) Function 和 Method