catalogue

1, One to many

1. Data sheet

2. Create entity class

3. Establish the attribute relationship between the two tables

4. Create Dao layer interface code and implementation class to operate the database

5. Test

2, Many to one

1. Modify entity class

2. Add interface method in Dao layer:

3. Add an implementation class: the implementation class mainly considers how to establish an association between the two

4. Test

3, One on one

1. Create data table

2. Create entity class

3. Add Dao and implementation classes

4. Test

4, Many to many

1. Create data table

2. Define entity class: the intermediate table does not need to generate entity classes

3. Define interfaces and implementation classes

4. Test

1, One to many

1. Data sheet

The classic one to many relationship is the student table and grade table. In the two tables, students are many and grade is one. Because: a grade can have more than one student, but conversely, a student belongs to only one grade. Create a data table first

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,'first grade'); 
insert into grade values(2,'second grade'); 
insert into grade values(3,'Third grade'); 
insert into student values(1,'Zhang San',18,1); 
insert into student values(2,'Li Si',14,2); 
insert into student values(3,'Wealth',13,3); 
insert into student values(4,'Wang Fang',17,1); 
insert into student values(5,'Sweet',15,2);

2. Create entity class

Requirements: class name = table name, column name = attribute name (attributes are also added to foreign key columns)

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. Establish the attribute relationship between the two tables

The data table maintains the relationship between the two tables through foreign key columns. Entity classes maintain the relationship between two tables through attributes. When establishing a one to many relationship, we analyze that the grade is one party and the students are many parties. One to many is dominated by one party, so we add an attribute of multiple parties to one party. Is this property an object or a collection? Here remember a sentence: one party saves the collection of many parties, and many parties save the object of one party. Therefore, you need to add the following attributes to the grade table:

Grade new code:

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

4. Create Dao layer interface code and implementation class to operate the database

Dao layer

package dao;

import bean.Grade;
import bean.Student;

import java.util.List;

public interface GradeDao {
    //Query the information of a grade (it is required to query the information of students at the same time)
    public Grade findById(int gid);
}

Implementation class:

In the implementation class, you need to connect to the database, and the query results come from multiple tables. How to store data at this time? Give you an idea: 1 Without considering the two tables, first store the data in each table 2 Considering the problem of adding attributes in the above steps, consider which class should be added to the attributes of another class. The code is as follows:

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. Store the respective data information
                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. Associate student information with attributes in grade
                //Put the students into a collection
                students.add(student);
            }
            //3. Establish a relationship between the two
            grade.setStudentList(students);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close(connection,preparedStatement,resultSet);
        }
        return grade;
    }
}

5. Test

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

2, Many to one

1. Modify entity class

Based on the previous step, complete many to one. Students are multi-party, adhering to the principle of "one party saves the collection of many parties, and many parties save the object of one party", then we need to add an object of one party to the multi-party. At this point, you need to add the following code to the student class

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

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

2. Add interface method in Dao layer:

package dao;

import bean.Grade;
import bean.Student;

import java.util.List;

public interface GradeDao {
    //Query the information of a grade (it is required to query the information of students at the same time)
    public Grade findById(int gid);
    //Query student information (including grade information)
    public List<Student> findAll();
}

3. Add an implementation class: the implementation class mainly considers how to establish an association between the two

    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. Save their own data
                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. Related information (put the grade in the students, and then put the students in the collection)
                student.setGrade(grade);
                students.add(student);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close(connection,preparedStatement,resultSet);
        }

        return students;
    }

4. Test

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

3, One on one

There are not many one-to-one scenarios in multi table relationships. Now, the relationship between wife and husband is used to simulate the one-to-one implementation process.

1. Create data table

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,'Huang Xiaoming'); 
insert into wife values(2,'Deng Chao'); 
insert into husband values(1,'baby',1); 
insert into husband values(2,'Sun Li',2);

2. Create entity class

The establishment of one-to-one relationship between entity classes is still based on the principle of "one party saves the collection of multiple parties and multiple parties saves the object of one party". However, the problem now is that both parties are data of one party. At this time, remember the principle of "one party saves the object of the other party".

package bean;

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

    // getter and setter methods
}

package bean;

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

    private Husband husband;
    // getter and setter methods
}

3. Add Dao and implementation classes

package dao;

import bean.Husband;
import bean.Wife;

public interface WifeDao {
    //Query a wife (including husband information)
    public Wife findByWifeId(int wid);
    //Query a husband (including information about his wife)
    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. Save their own information
                wife.setWifeName(resultSet.getString("wifename"));
                Husband husband = new Husband();
                husband.setHusName(resultSet.getString("husname"));
                //2. Establish a relationship between the two (encapsulating the husband into the object of the wife)
                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. Save their own information
                Wife wife = new Wife();
                wife.setWifeName(resultSet.getString("wifename"));

                husband.setHusName(resultSet.getString("husname"));
                //2. Establish a relationship between the two (encapsulating the wife into the object of the husband)
               husband.setWife(wife);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close(connection,preparedStatement,resultSet);
        }
        return husband;
    }
}

4. Test

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

    }
}

4, Many to many

Many to many is not much in real scenarios. The special one is the three table relationship of permission list. The menu table and role table belong to many to many. A function menu can be assigned to multiple roles, and a role can also have multiple menus. In this allocation process, it is a typical many to many. In many to many, the creation of tables is also characteristic, which must be based on three tables.

1. Create data table

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,'user management '); 
insert into menu values(2,'Menu management'); 
insert into menu values(3,'Role management'); 
insert into role values(1,'Super administrator'); 
insert into role values(2,'administrators'); 
insert into role values(3,'general manager'); 
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. Define entity class: the intermediate table does not need to generate entity classes

The establishment of many to many relationships between entity classes is still based on the principle of "one party saves the collection of multiple parties and multiple parties saves the object of one party". However, the problem now is that both parties are multi-party data. At this time, remember the principle of "multiple parties save the collection of the other party". The code is as follows:

Menu

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. Define interfaces and implementation classes

package dao;

import bean.Menu;
import bean.Role;

public interface RoleDao {
    //Query a menu information (including roles)
    public Menu findByMenuId(int mid);
    //Query a role information (menu required)
    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. Save data separately first
                menu.setMenuName(resultSet.getString("menuname"));
                Role role = new Role();
                role.setRoleName(resultSet.getString("rolename"));
                //2. Establish the relationship between the two
                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. Save data separately first
                Menu menu = new Menu();
                menu.setMenuName(resultSet.getString("menuname"));
                role.setRoleName(resultSet.getString("rolename"));
                //2. Establish the relationship between the two
                menuArrayList.add(menu);
            }
            role.setMenuList(menuArrayList);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close(connection,preparedStatement,resultSet);
        }
        return role;
    }
}

4. Test

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

    }
}

The chapters are summarized here ❤～ @&Goodbye firefly & [06 JavaWeb]

Students interested in learning Java are welcome to join QQ learning exchange group: 1126298731

Welcome to ask questions if you have any questions. Let's play strange and upgrade on the way to learn Java! (o゜▽゜)o☆[BINGO!]