Many-to-one or one-to-many implementation of Mybatis relational association mapping

Create tables for two experiments before the experiment

• T_role

role_id	role_name	role_level
1	Chairman	1
2	Chief inspector	2
3	manager	2
4	HR Specialist	5
5	Reception	5
6	Salesperson	4

• T_user

user_id	user_name	user_salary	user_age	user_sex	user_role
1	Zhang San	100000	58	male	1
2	Li Si	18000	58	female	2
3	King Five	6000	58	male	4
4	Xiao Zhang	3500	58	female	5
5	pony	3500	58	female	5
6	Xiao Liu	7000	58	male	6

Experimentation requirements:

• Page-by-page query of employee tables, showing 2 pieces of data each (many-to-one)
• Fuzzy query for all employees with keywords (many-to-one)
• According to the id query for the corresponding role, the role is required to contain information about all employees (one-to-many)

Realization:

1. Create the corresponding table


2. Create corresponding entity classes


The RoleListUser entity class is a necessary entity class for one-to-many queries. It has a Tuser collection in addition to all the attributes of Trole.

package cn.edu.fjut.entity;

public class Trole {

	private Integer role_id;
	private String role_name;
	private Integer role_level;

package cn.edu.fjut.entity;

public class Tuser {

	private Integer user_id;
	private String user_name;
	private Integer user_salary;
	private Integer user_age;
	private String user_sex;
	private Trole user_role;

package cn.edu.fjut.entity;

import java.util.ArrayList;

public class RoleListUser {

	private Integer role_id;
	private String role_name;
	private Integer role_level;
	private ArrayList<Tuser> data;

3. Create corresponding dao classes

package cn.edu.fjut.dao;

import java.util.ArrayList;

import cn.edu.fjut.entity.Tuser;
import cn.edu.fjut.entity.User;

public interface TuserDao {

	
	abstract public ArrayList<Tuser> getUserPageOne(int page,int pagesize);
	
	abstract public ArrayList<Tuser> getUserPageTwo(int page,int pagesize);
	
	abstract public ArrayList<Tuser> getTuserByKey(String like);
	
	abstract public ArrayList<Tuser> getTuserByUserid(Integer user_id);
}

package cn.edu.fjut.dao;

import java.util.ArrayList;

import cn.edu.fjut.entity.RoleListUser;
import cn.edu.fjut.entity.Trole;

public interface TroleDao {

	abstract public Trole getRoleByRoleid(Integer role_id);
	
	abstract public ArrayList<RoleListUser> getRoleAll();
}

4. Configuration Mapping File
This is TuserDaoMapper.xml

<resultMap type="cn.edu.fjut.entity.Tuser" id="myuser">
<id property="user_id" column="user_id"/>
<result property="user_name" column="user_name"/>
<result property="user_salary" column="user_salary"/>
<result property="user_age" column="user_age"/>
<result property="user_sex" column="user_sex"/>
<association property="user_role" javaType="cn.edu.fjut.entity.Trole" >
<id property="role_id" column="role_id"/>
<result property="role_name" column="role_name"/>
<result property="role_level" column="role_level"/>
</association>
</resultMap>
 
 <!-- Query non-primary key fields and entity class fields are consistent,Configuration may not be required,however id Be sure to show the configuration!!!-->
<resultMap type="cn.edu.fjut.entity.Tuser" id="myusernew">
<id property="user_id" column="user_id"/>
<association property="user_role" javaType="cn.edu.fjut.entity.Trole" select="cn.edu.fjut.dao.TroleDao.getRoleByRoleid" column="user_role">
</association>
</resultMap>

 <!-- Even if the query for non-primary key fields is consistent with the entity class fields,Be sure to show configuration if this field is needed -->
 <resultMap type="cn.edu.fjut.entity.Tuser" id="myuserlike">
<id property="user_id" column="user_id"/>
<result property="user_name" column="user_name"/>
<result property="user_salary" column="user_salary"/>
<result property="user_age" column="user_age"/>
<result property="user_sex" column="user_sex"/>
<association property="user_role" javaType="cn.edu.fjut.entity.Trole" >
<id property="role_id" column="role_id"/>
<result property="role_name" column="role_name"/>
<result property="role_level" column="role_level"/>
</association>
</resultMap>

<!-- getUserPageOne -->
<select id="getUserPageOne" resultMap="myuser">
Select * from T_user,T_role where T_user.user_role = T_role.role_id limit #{0},#{1}
</select>

<!-- getUserPageTwo -->
<select id="getUserPageTwo" resultMap="myusernew">
Select * from T_user limit #{0},#{1}
</select>

<!-- getTuserByKey -->
<select id="getTuserByKey" resultMap="myusernew" parameterType="java.lang.String">
select * from T_user where user_name like  #{user_name}
</select>

<!-- getTuserByUserid -->
<select id="getTuserByUserid" resultType="cn.edu.fjut.entity.Tuser" parameterType="java.lang.Integer">
Select * from T_user where user_role = #{user_role}
</select>

This is TroleDaoMapper.xml

<resultMap type="cn.edu.fjut.entity.RoleListUser" id="myrole">
<id property="role_id" column="role_id"/>
<!-- collection column Refers to the primary key of the current table -->
<collection property="data" column="role_id" select="cn.edu.fjut.dao.TuserDao.getTuserByUserid">
</collection>
</resultMap>
 
<!-- getRoleByRoleid -->
<select id="getRoleByRoleid" resultType="cn.edu.fjut.entity.Trole" parameterType="java.lang.Integer">
Select * from T_role where role_id = #{role_id}
</select>

<select id="getRoleAll" resultMap="myrole">
select * from T_role
</select>	

5. Create the corresponding dbutil class

public SqlSession creatSession(){
		String path = "SqlMapConfig.xml";
		InputStream inputStream = Test.class.getClassLoader().getResourceAsStream(path);
		SqlSessionFactory sqlSessionFactory =new SqlSessionFactoryBuilder().build(inputStream);
		SqlSession sqlSession = sqlSessionFactory.openSession();
		return sqlSession;
	}
	
	public void closeAll(SqlSession sqlSession){
		sqlSession.commit();
		sqlSession.close();
	}
	public ArrayList<Tuser> getUserPageOne(int page,int pagesize){
		SqlSession sqlSession = creatSession();
		TuserDao tuserDao = (TuserDao) sqlSession.getMapper(TuserDao.class);
		return tuserDao.getUserPageOne((page-1)*pagesize, pagesize);
	}
	
	public ArrayList<Tuser> getUserPageTwo(int page,int pagesize){
		SqlSession sqlSession = creatSession();
		TuserDao tuserDao = (TuserDao) sqlSession.getMapper(TuserDao.class);
		return tuserDao.getUserPageTwo((page-1)*pagesize, pagesize);
	}
	
	public Trole getRoleByRoleid(Integer role_id){
		SqlSession sqlSession = creatSession();
		TroleDao troleDao = (TroleDao) sqlSession.getMapper(TroleDao.class);
		return troleDao.getRoleByRoleid(role_id);
	}
	
	public ArrayList<Tuser> getTuserByKey(String like){
		SqlSession sqlSession = creatSession();
		TuserDao tuserDao = (TuserDao) sqlSession.getMapper(TuserDao.class);
		return tuserDao.getTuserByKey("%"+like+"%");
	}
	
	public ArrayList<Tuser> getTuserByUserid(Integer user_id){
		SqlSession sqlSession = creatSession();
		TuserDao tuserDao = (TuserDao) sqlSession.getMapper(TuserDao.class);
		return tuserDao.getTuserByUserid(user_id);
	}
	
	public ArrayList<RoleListUser> getRoleAll(){
		SqlSession sqlSession = creatSession();
		TroleDao troleDao = (TroleDao) sqlSession.getMapper(TroleDao.class);
		return troleDao.getRoleAll();
	}

6. Implement dao classes to create daoimpl classes

package cn.edu.fjut.daoimpl;

import java.util.ArrayList;

import cn.edu.fjut.DBUtil.DBUtil;
import cn.edu.fjut.dao.TuserDao;
import cn.edu.fjut.entity.Tuser;

public class TuserDaoImple implements TuserDao{

	private DBUtil dBUtil = new DBUtil();
	
	 @Override
	public ArrayList<Tuser> getUserPageOne(int page, int pagesize) {
		// TODO Auto-generated method stub
		return dBUtil.getUserPageOne(page, pagesize);
	}
	 
	 @Override
	public ArrayList<Tuser> getUserPageTwo(int page, int pagesize) {
		// TODO Auto-generated method stub
		return dBUtil.getUserPageTwo(page, pagesize);
	}
	 
	 @Override
	public ArrayList<Tuser> getTuserByKey(String like) {
		// TODO Auto-generated method stub
		return dBUtil.getTuserByKey(like);
	}
	 
	 @Override
	public ArrayList<Tuser> getTuserByUserid(Integer user_id) {
		// TODO Auto-generated method stub
		return dBUtil.getTuserByUserid(user_id);
	}
}

package cn.edu.fjut.daoimpl;

import java.util.ArrayList;

import cn.edu.fjut.DBUtil.DBUtil;
import cn.edu.fjut.dao.TroleDao;
import cn.edu.fjut.entity.RoleListUser;
import cn.edu.fjut.entity.Trole;

public class TroleDaoImple implements TroleDao{

	private DBUtil dBUtil = new DBUtil();
	@Override
	public Trole getRoleByRoleid(Integer role_id) {
		// TODO Auto-generated method stub
		return dBUtil.getRoleByRoleid(role_id);
	}
	
@Override
	public ArrayList<RoleListUser> getRoleAll() {
		// TODO Auto-generated method stub
		return dBUtil.getRoleAll();
	}	
}

7. Write test classes

package cn.edu.fjut.test;

import cn.edu.fjut.dao.TroleDao;
import cn.edu.fjut.daoimpl.TroleDaoImple;
import cn.edu.fjut.daoimpl.TuserDaoImple;
import cn.edu.fjut.entity.Tuser;

public class Ttest {

	public static void main(String[] args) {
		// TODO Auto-generated method stub

		TuserDaoImple tuserDaoImple = new TuserDaoImple();
		TroleDaoImple troleDaoImple = new TroleDaoImple();
		System.out.println("*************getUserPageOne**************");
		tuserDaoImple.getUserPageOne(1, 2).forEach(System.out::println);
		System.out.println("*************getUserPageTwo**************");
		tuserDaoImple.getUserPageTwo(1, 2).forEach(System.out::println);
		System.out.println("*************getTuserByKey**************");
		tuserDaoImple.getTuserByKey("Small").forEach(System.out::println);
		System.out.println("*************getRoleAll**************");
		troleDaoImple.getRoleAll().forEach(System.out::println);
	}

}

Experimental summary

experimental result

Implementation Instructions
There are many related tables in sql. One attribute of one table is the primary key of another table. When writing entity classes, the primary key should be written as the corresponding entity class type, which results in the result of the final query being null on that attribute because there is no such data type in sql.It is especially important to solve this problem.

1. Many-to-one query
In many-to-one queries, we have two ideas: one is to encapsulate missing attributes into entity classes of the main table by joining tables, the other is to encapsulate query results into entity classes of the main table by second query.

• Join Table Method

 <resultMap type="cn.edu.fjut.entity.Tuser" id="myuser">
<id property="user_id" column="user_id"/>
<result property="user_name" column="user_name"/>
<result property="user_salary" column="user_salary"/>
<result property="user_age" column="user_age"/>
<result property="user_sex" column="user_sex"/>
<association property="user_role" javaType="cn.edu.fjut.entity.Trole" >
<id property="role_id" column="role_id"/>
<result property="role_name" column="role_name"/>
<result property="role_level" column="role_level"/>
</association>
</resultMap>

This is a custom type, the primary key is declared with id, the others are result, property corresponds to the attribute name in the entity class, column corresponds to the result set attribute, association is the declaration of the main table entity class, inside and outside declaration are the same, note that Association Declaration is used in the secondary table, collection declaration is used in the main table, andThe symposium is on display.Note that this method must complete all entity class properties in the custom declaration and cannot be omitted.

• Secondary Query Method
  This method first looks up the secondary table and queries the primary table through its join key to the primary table.

<resultMap type="cn.edu.fjut.entity.Tuser" id="myusernew">
<id property="user_id" column="user_id"/>
<association property="user_role" javaType="cn.edu.fjut.entity.Trole" select="cn.edu.fjut.dao.TroleDao.getRoleByRoleid" column="user_role">
</association>
</resultMap>

This is a custom type declaration. You can see that there are no declarations other than primary and connection keys, so it is short. Note the JavaType and select attributes in connection keys. JavaType is the main table entity class declaration. Select calls the declared dao's calling method, and the second query opens from this place.First, let's look at the method of calling dao.

package cn.edu.fjut.dao;

import java.util.ArrayList;

import cn.edu.fjut.entity.RoleListUser;
import cn.edu.fjut.entity.Trole;

public interface TroleDao {

	abstract public Trole getRoleByRoleid(Integer role_id);//Method used in declaration
	
	abstract public ArrayList<RoleListUser> getRoleAll();
}

Second query, query the main table through the join key of the secondary table, and return the query results to one query.
2. One-to-many queries
One-to-many queries can only be implemented by a second query, and join tables cannot satisfy the requirement that we put a collection into a field because they violate the first paradigm of the database.
Secondary queries require an attribute that can store multiple tables of data, so we want to create a new entity class that has all the attributes of the main table plus a collection of secondary table entity classes.

package cn.edu.fjut.entity;

import java.util.ArrayList;

public class RoleListUser {

	private Integer role_id;
	private String role_name;
	private Integer role_level;
	private ArrayList<Tuser> data; //That collection

The following is a custom implementation

<resultMap type="cn.edu.fjut.entity.RoleListUser" id="myrole">
<id property="role_id" column="role_id"/>
<!-- collection column Refers to the primary key of the current table -->
<collection property="data" column="role_id" select="cn.edu.fjut.dao.TuserDao.getTuserByUserid">
</collection>
</resultMap>

You can see that there is not much difference between a two-to-one query. The only difference is that our newly defined entity class and declaration field are replaced by a collection, noting the difference between the fields in the primary and secondary tables.

abstract public ArrayList<Tuser> getUserPageTwo(int page,int pagesize);

This is the method of calling dao.