p

    p

关注
发信
关注(28)粉丝(399)

oracle使用resultMap实现高级结果映射

resultMap的属性:

1.属性

id：resultMap的唯一标识。
type：resulMap的映射结果类型（一般为Java实体类）。
2.子节点

id：一般对应数据库的主键 id，设置此项可以提升数据库性能。
result：映射到JavaBean的某个 “ 简单类型 ”属性，如基础数据类型，包装类等。子节点 id 和 result均可以实现最基本的结果集映射，将列映射到简单数据类型的属性。。这两者唯一不同的是：在比较对象实例时 id将作为结果集的标识属性。这有助于提高总体性能，特别是应用缓存的嵌套结果映射的时候，若需要实现高级结果映射，就需要下面两个配置项：association 和 collection
association（仅处理一对一的关联关系）

1
2
3
4
5
6
7
<resultMap type="User" id="userRoleResult">
　　<id property="id" column="id"/>
　　<result property="userName" column="userName"/>
　　<association property="role" javaType="Role">
　　　　<id property="roleName" column="roleName"/>
　　</association>
</resultMap>

javaType：完整Java类名或者别名。若映射到一个JavaBean，则MyBatis通常会自行检测到其类型；若映射到一个HashMap，则应该明确指定javaType，类确保所需行为。此处为Role。
property：映射到数据库列的实体对象的属性，此处为在User实体类里定义的JavaBean对象属性（role）。association的子元素如下：
id。
result。
property：映射到数据库列的实体类对象的属性。此处为Role的属性。
column：数据库列名或者别名。
在做结果映射的过程中，要确保所有的列名都是唯一无歧义的。

collection（属性是一个集合列表）

1
2
3
4
5
6
7
8
<resultMap type="User" id="userRoleResult">
　　<id property="id" column="id"/>
　　<result property="userName" column="userName"/>
　　<collection property="List" ofType="Address">
　　　　<id property="id" column="id"/>
　　　　<result property="postCode" column="postCode"/>
　　</collection>
</resultMap>

ofType：完整Java类名或者别名，即集合所包含的类型，此处为Address。
property：映射数据库列的实体类对象的属性。此处为在User里定义的属性：List（可以理解为一个名为List，元素类型为Address的ArrayList集合）
collection的子元素与association基本一致。

一对多

1
<span style="color: #ff0000;"><strong><span style="font-size: 18pt;">实体</span></strong></span>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
package com.smbms.entity; import java.math.BigInteger; import java.util.Date; import java.util.List; /** * 角色 */<br><br>
public class SmbmsRole {　　　　　　　　　　　　　　
    private Integer rid;
    private String roleCode;
    private String roleName;
    private BigInteger createdBy;
    private Date creationDate;
    private BigInteger modifyBy;
    private Date modifyDate;
 
    //植入多的一方      集合
    private List<SmbmsUser> userList;
 
    public List<SmbmsUser> getUserList() {
        return userList;
    }
 
    public void setUserList(List<SmbmsUser> userList) {
        this.userList = userList;
    }
 
    public Integer getRid() {
        return rid;
    }
 
    public void setRid(Integer rid) {
        this.rid = rid;
    }
 
    public String getRoleCode() {
        return roleCode;
    }
 
    public void setRoleCode(String roleCode) {
        this.roleCode = roleCode;
    }
 
    public String getRoleName() {
        return roleName;
    }
 
    public void setRoleName(String roleName) {
        this.roleName = roleName;
    }
 
    public BigInteger getCreatedBy() {
        return createdBy;
    }
 
    public void setCreatedBy(BigInteger createdBy) {
        this.createdBy = createdBy;
    }
 
    public Date getCreationDate() {
        return creationDate;
    }
 
    public void setCreationDate(Date creationDate) {
        this.creationDate = creationDate;
    }
 
    public BigInteger getModifyBy() {
        return modifyBy;
    }
 
    public void setModifyBy(BigInteger modifyBy) {
        this.modifyBy = modifyBy;
    }
 
    public Date getModifyDate() {
        return modifyDate;
    }
 
    public void setModifyDate(Date modifyDate) {
        this.modifyDate = modifyDate;
    }
}

　DAO层

1
2
3
4
5
6
7
8
9
10
<br>package com.smbms.dao;
 
import com.smbms.entity.SmbmsUser;
 
import java.util.List;
 
public interface ISmbmsUserDao {
    //查询所有用户信息  包含角色信息
    public List<SmbmsUser> getUserList();
}

　DAO层XML

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--namespace需要指向接口全路径-->
<mapper namespace="com.smbms.dao.ISmbmsRoleDao">
    <resultMap id="roleAndUserMapper" type="SmbmsRole">
        <id column="rid" property="rid"></id>
        <result column="roleName" property="roleName"/>
        <!--映射多的一方 property代表实体当中多的一方的属性名      ofType代表集合当中泛型类型-->
        <!-- select 代表执行查询的ID   column所引用的条件列   -->
        <collection property="userList" ofType="SmbmsUser" select="getRoleAndUserMutilSQL" column="rid">
 
        </collection>
    </resultMap>
     
    <!--写成一条sql-->
    <select id="getRoleAndUser" resultMap="roleAndUserMapper">
        select u.id,u.userName,u.userRole,r.rid,r.roleName from smbms_user as u,smbms_role as r where u.userRole=r.rid and r.rid=#{id}
    </select>
    <!--写成两条sql-->
    <select id="getRoleAndUser" resultMap="roleAndUserMapper">
        select * from  smbms_role where rid=#{id}
    </select>
    <select id="getRoleAndUserMutilSQL" resultType="SmbmsUser">
        select * from  smbms_user where userRole=#{rid}
    </select>
</mapper>

测试

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
package com.smbms.test;
 
import com.smbms.dao.ISmbmsRoleDao;
import com.smbms.entity.SmbmsRole;
import com.smbms.entity.SmbmsUser;
import com.smbms.util.MybatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
 
public class CollectionTest {
    @Test
    public void getRoleAndUserTest(){
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        ISmbmsRoleDao mapper = sqlSession.getMapper(ISmbmsRoleDao.class);
 
 
        SmbmsRole role = mapper.getRoleAndUser(3);
        System.out.println("角色："+role.getRoleName());
        for(SmbmsUser user : role.getUserList()){
            System.out.print("\t用户："+user.getUserName());
        }
    }
}

多对一　

1
<span style="color: #ff0000;"><strong><span style="font-size: 18pt;">实体</span></strong></span>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
package com.smbms.entity;
 
 
import java.math.BigInteger;
import java.util.Date;
 
/**
 *
 */
public class SmbmsUser {
    private Integer id;
    private String userCode;
    private String userName;
    private String userPassword;
    private Integer gender;
    private Date birthday;
    private String phone;
    private String address;
    private Integer userRole;
    private BigInteger createdBy;
    private Date creationDate;
    private BigInteger modifyBy;
    private Date modifyDate;
 
    //关联一的一方
    private SmbmsRole role;
 
    public SmbmsRole getRole() {
        return role;
    }
 
    public void setRole(SmbmsRole role) {
        this.role = role;
    }
 
    public Integer getId() {
        return id;
    }
 
    public void setId(Integer id) {
        this.id = id;
    }
 
    public String getUserCode() {
        return userCode;
    }
 
    public void setUserCode(String userCode) {
        this.userCode = userCode;
    }
 
    public String getUserName() {
        return userName;
    }
 
    public void setUserName(String userName) {
        this.userName = userName;
    }
 
    public String getUserPassword() {
        return userPassword;
    }
 
    public void setUserPassword(String userPassword) {
        this.userPassword = userPassword;
    }
 
    public Integer getGender() {
        return gender;
    }
 
    public void setGender(Integer gender) {
        this.gender = gender;
    }
 
    public Date getBirthday() {
        return birthday;
    }
 
    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }
 
    public String getPhone() {
        return phone;
    }
 
    public void setPhone(String phone) {
        this.phone = phone;
    }
 
    public String getAddress() {
        return address;
    }
 
    public void setAddress(String address) {
        this.address = address;
    }
 
    public Integer getUserRole() {
        return userRole;
    }
 
    public void setUserRole(Integer userRole) {
        this.userRole = userRole;
    }
 
    public BigInteger getCreatedBy() {
        return createdBy;
    }
 
    public void setCreatedBy(BigInteger createdBy) {
        this.createdBy = createdBy;
    }
 
    public Date getCreationDate() {
        return creationDate;
    }
 
    public void setCreationDate(Date creationDate) {
        this.creationDate = creationDate;
    }
 
    public BigInteger getModifyBy() {
        return modifyBy;
    }
 
    public void setModifyBy(BigInteger modifyBy) {
        this.modifyBy = modifyBy;
    }
 
    public Date getModifyDate() {
        return modifyDate;
    }
 
    public void setModifyDate(Date modifyDate) {
        this.modifyDate = modifyDate;
    }
}<br><br><br><br><br><br><br>

　　

DAO层

1
2
3
4
5
6
7
8
9
10
package com.smbms.dao;
 
import com.smbms.entity.SmbmsUser;
 
import java.util.List;
 
public interface ISmbmsUserDao {
    //查询所有用户信息  包含角色信息
    public List<SmbmsUser> getUserList();
}

　　

DAO层XML

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--namespace需要指向接口全路径-->
<mapper namespace="com.smbms.dao.ISmbmsUserDao">
    <resultMap id="userListAndRole" type="SmbmsUser">
        <id column="id" property="id"></id>
        <result column="userName" property="userName"/>
        <association property="role" javaType="SmbmsRole" select="getRole" column="userRole">
            <id column="rid" property="rid"></id>
            <result column="roleName" property="roleName"/>
        </association>
    </resultMap>
 
    <!--<select id="getUserList" resultMap="userListAndRole">
        select u.id,u.userName,u.userRole,r.rid,r.roleName from smbms_user as u,smbms_role as r where u.userRole=r.rid
    </select>-->
 
 
    <select id="getUserList" resultMap="userListAndRole">
        select * from smbms_user
    </select>
    <select id="getRole" resultType="SmbmsRole">
        select * from smbms_role where rid=#{userRole}
    </select>
</mapper>

　　

测试

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
package com.smbms.test;
 
import com.smbms.dao.ISmbmsUserDao;
import com.smbms.entity.SmbmsUser;
import com.smbms.util.MybatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
 
import java.util.List;
 
public class AssociationTest {
    @Test
    public void getUserListTest(){
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        ISmbmsUserDao mapper = sqlSession.getMapper(ISmbmsUserDao.class);
 
 
        List<SmbmsUser> userList = mapper.getUserList();
        for(SmbmsUser user:userList){
            System.out.println("用户："+user.getUserName()+"\t角色："+user.getRole().getRoleName());
        }
    }
}

　　

多对多

1
<span style="color: #ff0000;"><strong><span style="font-size: 18pt;">实体</span></strong></span>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
package com.smbms.entity;
 
import java.util.List;
 
public class Teacher {
    private Integer tid;
    private String tname;
 
    //植入学员集合，代表一名教员可以教授多名学员
    private List<Student> students;
 
    public Integer getTid() {
        return tid;
    }
 
    public void setTid(Integer tid) {
        this.tid = tid;
    }
 
    public String getTname() {
        return tname;
    }
 
    public void setTname(String tname) {
        this.tname = tname;
    }
 
    public List<Student> getStudents() {
        return students;
    }
 
    public void setStudents(List<Student> students) {
        this.students = students;
    }
}
 
 
 
 
package com.smbms.entity;
 
import java.util.List;
 
public class Student {
    private Integer stuid;
    private String stuname;
    private String stuaddress;
 
    //植入Teacher集合，代表一名学员可以被多名教员教授
    private List<Teacher> teachers;
 
    public List<Teacher> getTeachers() {
        return teachers;
    }
 
    public void setTeachers(List<Teacher> teachers) {
        this.teachers = teachers;
    }
 
    public Integer getStuid() {
        return stuid;
    }
 
    public void setStuid(Integer stuid) {
        this.stuid = stuid;
    }
 
    public String getStuname() {
        return stuname;
    }
 
    public void setStuname(String stuname) {
        this.stuname = stuname;
    }
 
    public String getStuaddress() {
        return stuaddress;
    }
 
    public void setStuaddress(String stuaddress) {
        this.stuaddress = stuaddress;
    }
}

　　

DAO层

1
2
3
4
5
6
7
8
9
10
package com.smbms.dao;
 
import com.smbms.entity.Student;
 
import java.util.List;
 
public interface IStudentDao {
    //查询所有学生信息  以及授课教员
    public List<Student> getStudentInfo();
}

　　

DAO层XML

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--namespace需要指向接口全路径-->
<mapper namespace="com.smbms.dao.IStudentDao">
    <resultMap id="studentAndTeacherMapper" type="Student">
        <id column="stuid" property="stuid"/>
        <result column="stuname" property="stuname"/>
        <collection property="teachers" ofType="Teacher">
            <id column="tid" property="tid"></id>
            <result property="tname" column="tname"/>
        </collection>
    </resultMap>
    <select id="getStudentInfo" resultMap="studentAndTeacherMapper">
        select * from student,teacher,stu_t where student.stuid=stu_t.stuid and teacher.tid=stu_t.tid
    </select>
</mapper>

　　

测试

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
package com.smbms.test;
 
import com.smbms.dao.IStudentDao;
import com.smbms.entity.Student;
import com.smbms.entity.Teacher;
import com.smbms.util.MybatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
 
import java.util.List;
 
public class ManeyTooManey {
    @Test
    public void getStudentInfo(){
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        IStudentDao mapper = sqlSession.getMapper(IStudentDao.class);
 
        List<Student> studentInfo = mapper.getStudentInfo();
        for(Student stu:studentInfo){
            System.out.println("学生："+stu.getStuname());
            for (Teacher teacher:stu.getTeachers()){
                System.out.print("\t教员："+teacher.getTname());
            }
            System.out.println();
        }
 
    }
}

　　

自联接

1
<span style="color: #ff0000;"><strong><span style="font-size: 18pt;">实体</span></strong></span>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
package com.smbms.entity;
 
import java.util.List;
 
public class City {
    private Integer cid;
    private String cname;
    private Integer pid;
    //自关联集合     代表的是当前City对象的子集集合
    public List<City> childCitys;
 
    @Override
    public String toString() {
        return "City{" +
                "cid=" + cid +
                ", cname='" + cname + '\'' +
                ", pid=" + pid +
                ", childCitys=" + childCitys +
                '}';
    }
 
    public List<City> getChildCitys() {
        return childCitys;
    }
 
    public void setChildCitys(List<City> childCitys) {
        this.childCitys = childCitys;
    }
 
    public Integer getCid() {
        return cid;
    }
 
    public void setCid(Integer cid) {
        this.cid = cid;
    }
 
    public String getCname() {
        return cname;
    }
 
    public void setCname(String cname) {
        this.cname = cname;
    }
 
    public Integer getPid() {
        return pid;
    }
 
    public void setPid(Integer pid) {
        this.pid = pid;
    }
 
 
}

　　

DAO层

1
2
3
4
5
6
7
8
9
10
package com.smbms.dao;
 
import com.smbms.entity.City;
 
import java.util.List;
 
public interface ICityDao {
    //查询河南省  下的所有子集
    public City getCityAndChildCitys(Integer cid);
}

　　

DAO层XML

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--namespace需要指向接口全路径-->
<mapper namespace="com.smbms.dao.ICityDao">
    <resultMap id="CityAndChildCitysMapper" type="City">
        <id column="cid" property="cid"></id>
        <result column="cname" property="cname"/>
        <result column="pid" property="pid"/>
        <collection property="childCitys" ofType="City" select="getCityAndChildCitysMutilSQL" column="cid">
            <id column="cid" property="cid"></id>
            <result column="cname" property="cname"/>
            <result column="pid" property="pid"/>
        </collection>
    </resultMap>
 
   <select id="getCityAndChildCitys" resultMap="CityAndChildCitysMapper">
       select * from city where cid=#{cid}
   </select>
    <select id="getCityAndChildCitysMutilSQL" resultMap="CityAndChildCitysMapper">
       select * from city where pid=#{cid}
   </select>
</mapper>

　　

测试

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
package com.smbms.test;
 
import com.smbms.dao.ICityDao;
import com.smbms.entity.City;
import com.smbms.util.MybatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
 
public class DGTest {
    @Test
    public void getCityAndChildCitysTest(){
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        ICityDao mapper = sqlSession.getMapper(ICityDao.class);
 
        City city = mapper.getCityAndChildCitys(410000);
        System.out.println(city.toString());
    }
}
12-27 21:08
Powered by LMLPHP ©2024     p 0.056445