mybaties list<String> 返回重复数据

记录下mybatis的集合查询中碰到的问题

 描述下场景，比如一个人有多个qq号（假设一个人可以有重复的qq号）

 

数据库结构，有两张表：

people表

id	name
1	jack

people_qq表

id	people_id	qq
1	1	123456
2	1	234567
3	1	456789
4	1	123456

 

 

实体类：

Java代码  

1. import java.io.Serializable;  
2. import java.util.List;  
3.   
4. public class People implements Serializable{  
5.       
6.     private static final long serialVersionUID = -5935066186174346694L;  
7.     private Long id;  
8.     private String name;  
9.     private List<String> qqs;  
10.     public Long getId() {  
11.         return id;  
12.     }  
13.     public void setId(Long id) {  
14.         this.id = id;  
15.     }  
16.     public String getName() {  
17.         return name;  
18.     }  
19.     public void setName(String name) {  
20.         this.name = name;  
21.     }  
22.     public List<String> getQqs() {  
23.         return qqs;  
24.     }  
25.     public void setQqs(List<String> qqs) {  
26.         this.qqs = qqs;  
27.     }  
28.     @Override  
29.     public String toString() {  
30.         return "People [id=" + id + ", name=" + name + ", qqs=" + qqs + "]";  
31.     }  
32. }  

1. import java.io.Serializable;

2. import java.util.List;

3.  

4. public class People implements Serializable{

5.  

6. private static final long serialVersionUID = -5935066186174346694L;

7. private Long id;

8. private String name;

9. private List<String> qqs;

10. public Long getId() {

11. return id;

12. }

13. public void setId(Long id) {

14. this.id = id;

15. }

16. public String getName() {

17. return name;

18. }

19. public void setName(String name) {

20. this.name = name;

21. }

22. public List<String> getQqs() {

23. return qqs;

24. }

25. public void setQqs(List<String> qqs) {

26. this.qqs = qqs;

27. }

28. @Override

29. public String toString() {

30. return "People [id=" + id + ", name=" + name + ", qqs=" + qqs + "]";

31. }

32. }

 

mapper接口：

Java代码  

1. import com.hnpicheng.mybatisissue.domain.People;  
2.   
3. public interface PeopleMapper {  
4.     People selectPeopleById( Long id);  
5. }  

1. import com.hnpicheng.mybatisissue.domain.People;

2.  

3. public interface PeopleMapper {

4. People selectPeopleById( Long id);

5. }

 

测试代码：

Java代码  

1. import org.springframework.context.support.ClassPathXmlApplicationContext;  
2. import com.hnpicheng.mybatisissue.domain.People;  
3. import com.hnpicheng.mybatisissue.mapper.PeopleMapper;  
4.   
5. public class App   
6. {  
7.     public static void main( String[] args )  
8.     {  
9.        ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("spring-mybatis.xml");  
10.        PeopleMapper peopleMapper = context.getBean(PeopleMapper.class);  
11.        People p = peopleMapper.selectPeopleById(1L);  
12.        System.out.println(p);  
13.          
14.     }  
15. }  

1. import org.springframework.context.support.ClassPathXmlApplicationContext;

2. import com.hnpicheng.mybatisissue.domain.People;

3. import com.hnpicheng.mybatisissue.mapper.PeopleMapper;

4.  

5. public class App

6. {

7. public static void main( String[] args )

8. {

9. ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("spring-mybatis.xml");

10. PeopleMapper peopleMapper = context.getBean(PeopleMapper.class);

11. People p = peopleMapper.selectPeopleById(1L);

12. System.out.println(p);

13.  

14. }

15. }

 

 

PeopleMapper.xml

 

根据业务需要，如果不需要将重复数据查出来：

那么可以使用

Xml代码  

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.hnpicheng.mybatisissue.mapper.PeopleMapper">  
4.   
5.     <resultMap id="peopleResultMap" type="People">  
6.         <id property="id" column="id" />  
7.         <result property="name" column="name" />   
8.         <collection property="qqs" ofType="string" javaType="list">  
9.             <result column="qq" />  
10.         </collection>  
11.     </resultMap>  
12.       
13.     <select id="selectPeopleById" resultMap="peopleResultMap">  
14.         select p.*,pq.qq from  
15.         people p left join  people_qq pq on p.id = pq.people_id   
16.         where p.id = #{id}  
17.     </select>  
18. </mapper>  

[xml] view plain copy

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.hnpicheng.mybatisissue.mapper.PeopleMapper">  
4.   
5.     <resultMap id="peopleResultMap" type="People">  
6.         <id property="id" column="id" />  
7.         <result property="name" column="name" />   
8.         <collection property="qqs" ofType="string" javaType="list">  
9.             <result column="qq" />  
10.         </collection>  
11.     </resultMap>  
12.       
13.     <select id="selectPeopleById" resultMap="peopleResultMap">  
14.         select p.*,pq.qq from  
15.         people p left join  people_qq pq on p.id = pq.people_id   
16.         where p.id = #{id}  
17.     </select>  
18. </mapper>  

 

测试结果，优点只要查一次，对于需要排重的查询业务，可以用这个方法：

Java代码  

1. DEBUG [main] - ==>  Preparing: select p.*,pq.qq from people p left join people_qq pq on p.id = pq.people_id where p.id = ?   
2. DEBUG [main] - ==> Parameters: 1(Long)  
3. DEBUG [main] - <==      Total: 4  
4. People [id=1, name=jack, qqs=[123456, 234567, 456789]]  

1. DEBUG [main] - ==> Preparing: select p.*,pq.qq from people p left join people_qq pq on p.id = pq.people_id where p.id = ?

2. DEBUG [main] - ==> Parameters: 1(Long)

3. DEBUG [main] - <== Total: 4

4. People [id=1, name=jack, qqs=[123456, 234567, 456789]]

 

 

若果需要将重复数据查询出来，那么可以使用以下配置

Xml代码  

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.hnpicheng.mybatisissue.mapper.PeopleMapper">  
4. <resultMap id="peopleResultMap" type="People">  
5.         <id property="id" column="id" />  
6.         <result property="name" column="name" />   
7.         <collection property="qqs" column="id" select="selectQQByPeopleId">  
8.             <result column="qq" />  
9.         </collection>  
10.     </resultMap>  
11.       
12.     <select id="selectPeopleById" resultMap="peopleResultMap">  
13.         select * from people where id = #{id}  
14.     </select>  
15.       
16.     <select id="selectQQByPeopleId" resultType="string">  
17.         select qq from people_qq where people_id = #{id}  
18.     </select>  
19. </mapper>  

[xml] view plain copy

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.hnpicheng.mybatisissue.mapper.PeopleMapper">  
4. <resultMap id="peopleResultMap" type="People">  
5.         <id property="id" column="id" />  
6.         <result property="name" column="name" />   
7.         <collection property="qqs" column="id" select="selectQQByPeopleId">  
8.             <result column="qq" />  
9.         </collection>  
10.     </resultMap>  
11.       
12.     <select id="selectPeopleById" resultMap="peopleResultMap">  
13.         select * from people where id = #{id}  
14.     </select>  
15.       
16.     <select id="selectQQByPeopleId" resultType="string">  
17.         select qq from people_qq where people_id = #{id}  
18.     </select>  
19. </mapper>  

 

测试结果：

Java代码  

1. DEBUG [main] - ==>  Preparing: select * from people where id = ?   
2. DEBUG [main] - ==> Parameters: 1(Long)  
3. DEBUG [main] - <==      Total: 1  
4. DEBUG [main] - ==>  Preparing: select qq from people_qq where people_id = ?   
5. DEBUG [main] - ==> Parameters: 1(Long)  
6. DEBUG [main] - <==      Total: 4  
7. People [id=1, name=jack, qqs=[123456, 234567, 456789, 123456]]  

   转自: http://jaychang.iteye.com/blog/2357143

   1. DEBUG [main] - ==> Preparing: select * from people where id = ?

   2. DEBUG [main] - ==> Parameters: 1(Long)

   3. DEBUG [main] - <== Total: 1

   4. DEBUG [main] - ==> Preparing: select qq from people_qq where people_id = ?

   5. DEBUG [main] - ==> Parameters: 1(Long)

   6. DEBUG [main] - <== Total: 4

   7. People [id=1, name=jack, qqs=[123456, 234567, 456789, 123456]]