19.resultMap和sql片段

Posted on Edited on In

 

resultMap

UserMapper.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
29
30
31
32
33
34
35
36
37
38
39
40
41
<?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">


<mapper namespace="com.course.dao.UserDao">
	
	<!-- 处理实体类的属性名与查询结果的字段名不相同的情况 -->
	<resultMap id="userIdentityMap" type="User">
		<!-- 
			id: 主键
		 -->
		<id property="id" column="userId"/>
		<result property="name" column="userName"/>
	</resultMap>
	
	<!-- 
		id: resultMap的唯一标识
		type: resultMap的返回类型
		extends: 继承的父resultMap的id，继承父resultMap的所有属性
	 -->
	<resultMap id="userInfoMap" type="User" extends="userIdentityMap">
		<result property="age" column="userAge"/>
		<result property="address" column="userAddress"/>
		<result property="birthday" column="userBirth"/>
	</resultMap>
	
	<select id="queryUserResultMap" parameterType="User" resultMap="userInfoMap">
		select 
			id as userId, 
			name as userName, 
			age as userAge, 
			address as userAddress, 
			birthday as userBirth
		from user
		where name like "%"#{name}"%" and address like "%"#{address}"%"
	</select>
	
</mapper>

UserDao.java

1
2
3
4
5
6
7
8
9
10
11
package com.course.dao;

import java.util.List;

import com.course.vo.User;

public interface UserDao {
	
	public List<User> queryUserResultMap(User user);
	
}

测试类: 

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
package com.course.test;

import java.util.List;

import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import com.course.dao.UserDao;
import com.course.util.MyBatisUtil;
import com.course.vo.User;

public class MainTest {

	private SqlSession sqlSession = MyBatisUtil.openSqlSession();
	private UserDao userDao = sqlSession.getMapper(UserDao.class);
	
	@Test
	public void queryUserResultMap() {
		String name = "user1";
		String address = "address1";
		
		User user =new User();
		user.setName(name);
		user.setAddress(address);
		
		List<User> userList = userDao.queryUserResultMap(user);
		for (User userVO : userList) {
			System.out.println(userVO.toString());
		}
		
		MyBatisUtil.closeSqlSession(sqlSession);
	}
	
}
1
2
3
DEBUG [main] - ==>  Preparing: select id as userId, name as userName, age as userAge, address as userAddress, birthday as userBirth from user where name like "%"?"%" and address like "%"?"%" 
DEBUG [main] - ==> Parameters: user1(String), address1(String)
DEBUG [main] - <==      Total: 69

sql片段

UserMapper.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
29
30
31
32
33
34
<?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">


<mapper namespace="com.course.dao.UserDao">

	<sql id="userColumn">
		id, 
		name, 
		age, 
		address, 
		birthday
	</sql>
	
	<sql id="userWhere">
		where name like "%"#{name}"%"
		<include refid="userWhereAddress"></include>
	</sql>
	
	<sql id="userWhereAddress">
		 and address like "%"#{address}"%"
	</sql>
	
	<select id="queryUserSqlFragment" parameterType="User" resultType="User">
		select 
			<include refid="userColumn"></include>
		from user
		<include refid="userWhere"></include>
	</select>
	
</mapper>

UserDao.java

1
2
3
4
5
6
7
8
9
10
11
package com.course.dao;

import java.util.List;

import com.course.vo.User;

public interface UserDao {
	
	public List<User> queryUserSqlFragment(User user);
	
}

测试类: 

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
package com.course.test;

import java.util.List;

import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import com.course.dao.UserDao;
import com.course.util.MyBatisUtil;
import com.course.vo.User;

public class MainTest {

	private SqlSession sqlSession = MyBatisUtil.openSqlSession();
	private UserDao userDao = sqlSession.getMapper(UserDao.class);
	
	@Test
	public void queryUserSqlFragment() {
		String name = "user1";
		String address = "address1";
		
		User user =new User();
		user.setName(name);
		user.setAddress(address);
		
		List<User> userList = userDao.queryUserSqlFragment(user);
		for (User userVO : userList) {
			System.out.println(userVO.toString());
		}
		
		MyBatisUtil.closeSqlSession(sqlSession);
	}	
}
1
2
3
DEBUG [main] - ==>  Preparing: select id, name, age, address, birthday from user where name like "%"?"%" and address like "%"?"%" 
DEBUG [main] - ==> Parameters: user1(String), address1(String)
DEBUG [main] - <==      Total: 69