关于mybatis中mapper文件resultMap中collection和association的使用
mybatis mapper配置文件结果集映射resultMap中collection属性(一对多关系结果集映射)和association属性(多对一关系结果集映射)理解：
collection的使用有两种resultMap和select，必须手动指定一种
association的使用和collection使用类似，只是right join需换成left join
1. 实体类：
1 package com.mrlu.mybatis.domain; 2 3 import java.util.List; 4 5 /** 6 * Created by stefan on 15-12-31. 7 */ 8 public class User { 9 private Integer id; 10 private String name; 11 private List<Account> accounts; //user (1)-->(*) account 12 13 public Integer getId() { 14 return id; 15 } 16 17 public void setId(Integer id) { 18 this.id = id; 19 } 20 21 public String getName() { 22 return name; 23 } 24 25 public void setName(String name) { 26 this.name = name; 27 } 28 29 @Override 30 public String toString() { 31 return "User{" + 32 "id=" + id + 33 ", name='" + name + '\'' + 34 ", accounts=" + accounts + 35 '}'; 36 } 37 38 public List<Account> getAccounts() { 39 return accounts; 40 } 41 42 public void setAccounts(List<Account> accounts) { 43 this.accounts = accounts; 44 } 45 46 }
package com.mrlu.mybatis.domain; /** * Created by stefan on 15-12-31. */ public class Account { private Integer id; private Integer userId; private String num; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public Integer getUserId() { return userId; } public void setUserId(Integer userId) { this.userId = userId; } public String getNum() { return num; } public void setNum(String num) { this.num = num; } @Override public String toString() { return "Account{" + "id=" + id + ", userId=" + userId + ", num='" + num + '\'' + '}'; } }
2. DAO:
package com.mrlu.mybatis.dao; import com.mrlu.mybatis.domain.User; import java.util.List; /** * Created by stefan on 15-12-31. */ public interface UserDao { public void insert(User user); public List<User> selectAll(); }
package com.mrlu.mybatis.dao; import com.mrlu.mybatis.domain.Account; /** * Created by stefan on 15-12-31. */ public interface AccountDao { public void insert(Account account); }
3. mapper.xml
AccountDao:
<?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.mrlu.mybatis.dao.AccountDao" >
<insert id="insert" useGeneratedKeys="true" keyProperty="id">
INSERT INTO account(user_id, num) VALUES(
#{userId}, #{num}
)
</insert>
</mapper>
UserDao: (collection中标出的属性字段都是必须的，没标出的都是可选的)
resultMap:(若有同名属性，需指定不同的名称)
1 <?xml version="1.0" encoding="UTF-8" ?> 2 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" > 3 <mapper namespace="com.mrlu.mybatis.dao.UserDao" > 4 <resultMap id="BaseResultMap" type="com.mrlu.mybatis.domain.User"> 5 <result column="tid" property="id" /> 6 <result column="name" property="name" /> 7 <collection property="accounts" resultMap="accountMap" /> 8 </resultMap> 9 <resultMap id="accountMap" type="com.mrlu.mybatis.domain.Account"> 10 <result column="aid" property="id" /> 11 <result column="user_id" property="userId" /> 12 <result column="num" property="num" /> 13 </resultMap> 14 <insert id="insert" keyProperty="id" useGeneratedKeys="true"> 15 INSERT INTO user(name) VALUES( 16 #{name} 17 ) 18 </insert> 19 20 <select id="selectAll" resultMap="BaseResultMap"> 21 SELECT t.id as tid, t.name as name, a.id as aid,a.user_id,a.num FROM user t 22 RIGHT JOIN account a ON t.id=a.user_id 23 </select> 24 </mapper>
select:(select查询的结果需要和java bean属性名称相同)
1 <?xml version="1.0" encoding="UTF-8" ?> 2 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" > 3 <mapper namespace="com.mrlu.mybatis.dao.UserDao" > 4 <resultMap id="BaseResultMap" type="com.mrlu.mybatis.domain.User"> 5 <result column="id" property="id" /> 6 <result column="name" property="name" /> 7 <collection property="accounts" column="id" select="selectAccount" /> 8 </resultMap> 9 <insert id="insert" keyProperty="id" useGeneratedKeys="true"> 10 INSERT INTO user(name) VALUES( 11 #{name} 12 ) 13 </insert> 14 15 <select id="selectAll" resultMap="BaseResultMap"> 16 SELECT * FROM user t 17 </select> 18 19 <select id="selectAccount" resultType="com.mrlu.mybatis.domain.Account"> 20 SELECT id as id, user_id as userId, num as num FROM account 21 WHERE user_id = #{id} 22 </select> 23 </mapper>
4. mybatis-config.xml
1 <?xml version="1.0" encoding="UTF-8"?> 2 <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> 3 <configuration> 4 <!-- 5 properties属性只能有一个，有三种方式指定：(源码： XMLConfigBuilder.#propertiesElement()) 6 1. 通过resource引入外部配置文件(优先级最高) 7 2. 通过url指定网络文件(次之) 8 3. 通过property来内部指定(优先级最低) 9 --> 10 <properties resource="jdbc.properties" > 11 <property name="jdbc.url" value="jdbc:mysql://127.0.0.1:3306/user" /> 12 </properties> 13 <settings> 14 <setting name="cacheEnabled" value="false"/> 15 </settings> 16 <environments default="development"> 17 <environment id="development"> 18 <transactionManager type="JDBC"></transactionManager> 19 <dataSource type="POOLED"> 20 <property name="driver" value="${jdbc.driver}" /> 21 <property name="url" value="${jdbc.url}" /> 22 <property name="username" value="${jdbc.username}" /> 23 <property name="password" value="${jdbc.password}" /> 24 </dataSource> 25 </environment> 26 </environments> 27 28 <mappers> 29 <mapper resource="com/mrlu/mybatis/dao/UserDao-select.xml" /> 30 <mapper class="com.mrlu.mybatis.dao.AccountDao" /> 31 </mappers> 32 </configuration>
5. 测试代码
1 public class Main { 2 public static SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder(); 3 4 public static void main(String[] args){ 5 String resource = "mybatis-config.xml"; 6 try { 7 Reader reader = Resources.getResourceAsReader(resource); 8 SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(reader); 9 SqlSession sqlSession = sqlSessionFactory.openSession(); 10 UserDao userDao = sqlSession.getMapper(UserDao.class); 11 AccountDao accountDao = sqlSession.getMapper(AccountDao.class); 12 13 User user = new User(); 14 user.setId(2); 15 user.setName("z3"); 16 userDao.insert(user); 17 18 for (int i = 0; i < 3; i++) { 19 Account account = new Account(); 20 account.setUserId(user.getId()); 21 account.setNum("123"); 22 accountDao.insert(account); 23 } 24 25 List<User> re = userDao.selectAll(); 26 System.out.println("result...."); 27 for(User user1: re){ 28 System.out.println(user1); 29 } 30 31 sqlSession.commit(); //这句很重要，因为SqlSessionFactory#openSession()方法默认设置的autoCommit为false，即不自动提交，所以如果不手动调用，则插入不成功 32 } catch (IOException e) { 33 e.printStackTrace(); 34 } 35 } 36 }
6. association(多对一结果集转换)的示例：
1 <?xml version="1.0" encoding="UTF-8" ?> 2 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" > 3 <mapper namespace="com.mrlu.mybatis.dao.AccountDao" > 4 <resultMap id="BaseResultMap" type="com.mrlu.mybatis.domain.Account"> 5 <result column="aid" property="id" /> 6 <result column="user_id" property="userId" /> 7 <result column="num" property="num" /> 8 <association property="user" resultMap="userMap" /> 9 </resultMap> 10 <resultMap id="userMap" type="com.mrlu.mybatis.domain.User"> 11 <result column="tid" property="id" /> 12 <result column="name" property="name" /> 13 </resultMap> 14 15 <insert id="insert" useGeneratedKeys="true" keyProperty="id"> 16 INSERT INTO account(user_id, num) VALUES( 17 #{userId}, #{num} 18 ) 19 </insert> 20 21 <select id="selectAll" resultMap="BaseResultMap"> 22 SELECT a.id as aid, t.id as tid, a.user_id as user_id, num as num, t.name as name FROM account a 23 LEFT JOIN user t on a.user_id = t.id 24 </select> 25 </mapper>
