1. The interface of each associated query in the Dao layer
/ /One-to-many relational query
public SmbmsRoleEntity getAllByRoleId(Integer id);
//Many-to-one association query to query all user information including role information
public ListgetUserList();
//Many-to-many relational query
//Query all student information and instructors
public ListgetStudentInfo();
//self query
//Query all subsets in Henan Province
public City getCityAndChildCitys(Integer cid);
Second, xml file
<mapper namespace="com.marketsys.dao.ProviderTest">
<resultMap id="ById" type="com.marketsys.entity.SmbmsRoleEntity">
<id property="rid" column="rid">id>
<result property="roleName" column="roleName">result>
<collection property="getRoleEntitys" ofType="com.marketsys.entity.User" select="getUser " column="rid">
collection>
resultMap>
<select id="getAllByRoleId" parameterType="int" resultMap="ById"< span style="color: #0000ff;">>
select * from smbms.smbms_role where rid=#{id}
select>
<select id="getUser" resultType="com.marketsys.entity.User">
select * from smbms.smbms_user where userRole=#{rid};
select>
<resultMap id="userListAndRole" type="com.marketsys.entity.User">
<id column="id" property="id">id>
<result column="userName" property="userName">result>
<association property="role" javaType="com.marketsys.entity.SmbmsRoleEntity" select="getRole " column="userRole">
<id column="rid" property="rid">id>
<result column="roleName" property="roleName">result>
association>
resultMap>
<select id="getUserList" resultMap="userListAndRole">
select * from smbms.smbms_user
select>
<select id="getRole" resultType="com.marketsys.entity.SmbmsRoleEntity">
select * from smbms.smbms_role where rid=#{userRole}
select>
<resultMap id="studentInfoMapper" type="com.marketsys.entity.Student">
<id column="stuid" property="stuid">id>
<result column="stuname" property="stuname">result>
<collection property="teachers" ofType="com.marketsys.entity.Teacher">
<id column="tid" property="tid">id>
<result column="tname" property="tname">result>
collection>
resultMap>
<select id="getStudentInfo" resultMap="studentInfoMapper">
select * from student,teacher,stu_t where student.stuid=stu_t.stuid and teacher.tid=stu_t.tid;
select>
<resultMap id="cityMapper" type="com.marketsys.entity.City">
<id column="cid" property="cid">id>
<result column="cname" property="cname">result>
<result column="pid" property="pid">result>
<collection property="childCitys" ofType="com.marketsys.entity.City" select="getCity " column="cid">
<id column="cid" property="cid">id>
<result column="cname" property="cname">result>
<result column="pid" property="pid">result>
collection>
resultMap>
<select id="getCityAndChildCitys" resultMap="cityMapper">
select * from city where cid=#{cid}
select>
<select id="getCity" resultMap="cityMapper">
select * from city where pid=#{cid};
select>
mapper>
Three, each related query test class
//one to many
@Test
public void test1(){
SmbmsRoleEntity allByRoleId = providerTest.getAllByRoleId(3);
System.out.println("Role:"+allByRoleId.getRoleName());
for (User user: allByRoleId.getGetRoleEntitys()) {
System.out.print("Name:"+user.getUserName()+" ");
}
}
//Many to one
@Test
public void test2(){
ListuserList = providerTest.getUserList();
for (User user :userList)
System.out.println("User:"+user.getUserName()+" Role:"+user.getRole(). getRoleName());
}
//many to many
@Test
public void test3(){
ListstudentInfo = providerTest.getStudentInfo();
for (Student student:studentInfo){
System.out.println("student:"+student.getStuname());
for (Teacher teacher:student.getTeachers()){
System.out.println(" teacher:"+teacher.getTname());
}
}
}
//self query
@Test
public void test4(){
City cityAndChildCitys = providerTest.getCityAndChildCitys(410000);
System.out.println(cityAndChildCitys.toString());
}
four, Meaning of associated query
1. One-to-many associated query
< p> One-to-many relational query means that when querying an object, all the multiple objects associated with it are also queried at the same time.
2. Many-to-one association query
Here is the many-to-one association Query means that when querying multi-party objects, the related party objects are also queried at the same time.
Since multiple objects are inquired one by one, many-to-one associative queries are actually one-to-one associative queries. That is, the implementation of one-to-one association query is the same as that of many-to-one.
3. Many-to-many association query
many-to-many association relationship, For example, a student can choose multiple courses, and a course can be selected by multiple students. The many-to-many relationship is actually composed of two reciprocal one-to-many relationships. In general, many-to-many relationships are established through an intermediate table
4. Self-associated query
The so-called self-associated query means that oneself acts as one party and also acts as multiple parties, which is a variant of 1:n or n:1. For example, for the news section NewsLabel, it can act as one party, that is, the parent section, or it can act as multiple parties, that is, the child section. In the DB table, there is only one table, and this table has a foreign key to indicate the parent column of the column. The first-level column does not have a parent column, so its foreign key value can be set to 0, while the child column has a foreign key value.
Self-association is divided into two situations. One is as 1:n, that is, the current class as one party, which contains multi-party set domain attributes. One is treated as n:1, that is, the current class is multi-party, which contains the domain attributes of one party.
//One-to-many relational query< /span>
public SmbmsRoleEntity getAllByRoleId(Integer id);
//Many-to-one association query to query all user information including role information
public ListgetUserList();
//Many-to-many relational query
//Query all student information and instructors
public ListgetStudentInfo();
//self query
//Query all subsets in Henan Province
public City getCityAndChildCitys(Integer cid);
<mapper namespace="com.marketsys.dao.ProviderTest"< /span>>
<resultMap id="ById" type="com.marketsys.entity.SmbmsRoleEntity">
<id property="rid" column="rid">id>
<result property="roleName" column="roleName">result>
<collection property="getRoleEntitys" ofType="com.marketsys.entity.User" select="getUser " column="rid">
collection>
resultMap>
<select id="getAllByRoleId" parameterType="int" resultMap="ById"< span style="color: #0000ff;">>
select * from smbms.smbms_role where rid=#{id}
select>
<select id="getUser" resultType="com.marketsys.entity.User">
select * from smbms.smbms_user where userRole=#{rid};
select>
<resultMap id="userListAndRole" type="com.marketsys.entity.User">
<id column="id" property="id">id>
<result column="userName" property="userName">result>
<association property="role" javaType="com.marketsys.entity.SmbmsRoleEntity" select="getRole" column="userRole">
<id column="rid" property="rid">id>
<result column="roleName" property="roleName">result>
association>
resultMap>
<select id="getUserList" resultMap="userListAndRole">
select * from smbms.smbms_user
select>
<select id="getRole" resultType="com.marketsys.entity.SmbmsRoleEntity">
select * from smbms.smbms_role where rid=#{userRole}
select>
<resultMap id="studentInfoMapper" type="com.marketsys.entity.Student">
<id column="stuid" property="stuid">id>
<result column="stuname" property="stuname">result>
<collection property="teachers" ofType="com.marketsys.entity.Teacher">
<id column="tid" property="tid">id>
<result column="tname" property="tname">result>
collection>
resultMap>
<select id="getStudentInfo" resultMap="studentInfoMapper">
select * from student,teacher,stu_t where student.stuid=stu_t.stuid and teacher.tid=stu_t.tid;
select>
<resultMap id="cityMapper" type="com.marketsys.entity.City">
<id column="cid" property="cid">id>
<result column="cname" property="cname">result>
<result column="pid" property="pid">result>
<collection property="childCitys" ofType="com.marketsys.entity.City" select="getCity" column="cid">
<id column="cid" property="cid">id>
<result column="cname" property="cname">result>
<result column="pid" property="pid">result>
collection>
resultMap>
<select id="getCityAndChildCitys" resultMap="cityMapper">
select * from city where cid=#{cid}
select>
<select id="getCity" resultMap="cityMapper">
select * from city where pid=#{cid};
select>
mapper>
//一对多
@Test
public void test1(){
SmbmsRoleEntity allByRoleId = providerTest.getAllByRoleId(3);
System.out.println("角色："+allByRoleId.getRoleName());
for (User user: allByRoleId.getGetRoleEntitys()) {
System.out.print("姓名："+user.getUserName()+" ");
}
}
//多对一
@Test
public void test2(){
ListuserList = providerTest.getUserList();
for (User user :userList)
System.out.println("用户："+user.getUserName()+" 角色："+user.getRole().getRoleName());
}
//多对多
@Test
public void test3(){
ListstudentInfo = providerTest.getStudentInfo();
for (Student student:studentInfo){
System.out.println("学生："+student.getStuname());
for (Teacher teacher:student.getTeachers()){
System.out.println(" 教员："+teacher.getTname());
}
}
}
//自查询
@Test
public void test4(){
City cityAndChildCitys = providerTest.getCityAndChildCitys(410000);
System.out.println(cityAndChildCitys.toString());
}