mybaties list<String> 返回重复数据

缺乏、安全感 2022-05-18 06:06 237阅读 0赞

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

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

数据库结构，有两张表：

people表

people\_qq表

<table> 
 <tbody> 
  <tr> 
   <td>id</td> 
   <td>people_id</td> 
   <td>qq</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>1</td> 
   <td>123456</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>1</td> 
   <td>234567</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>1</td> 
   <td>456789</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>1</td> 
   <td>123456</td> 
  </tr> 
 </tbody> 
</table>

实体类：

Java代码 [![复制代码][icon_copy.gif]][icon_copy.gif 1] [![收藏代码][icon_star.png]![spinner.gif][]][icon_star.png_spinner.gif]

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代码 [![复制代码][icon_copy.gif]][icon_copy.gif 1] [![收藏代码][icon_star.png]![spinner.gif][]][icon_star.png_spinner.gif]

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代码 [![复制代码][icon_copy.gif]][icon_copy.gif 1] [![收藏代码][icon_star.png]![spinner.gif][]][icon_star.png_spinner.gif]

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代码 [![复制代码][icon_copy.gif]][icon_copy.gif 1] [![收藏代码][icon_star.png]![spinner.gif][]][icon_star.png_spinner.gif]

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][view plain]

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代码 [![复制代码][icon_copy.gif]][icon_copy.gif 1] [![收藏代码][icon_star.png]![spinner.gif][]][icon_star.png_spinner.gif]

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代码 [![复制代码][icon_copy.gif]][icon_copy.gif 1] [![收藏代码][icon_star.png]![spinner.gif][]][icon_star.png_spinner.gif]

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][view plain]

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代码 [![复制代码][icon_copy.gif]][icon_copy.gif 1] [![收藏代码][icon_star.png]![spinner.gif][]][icon_star.png_spinner.gif]

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][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]]`

[icon_copy.gif]: http://jaychang.iteye.com/images/icon_copy.gif
[icon_copy.gif 1]: http://jaychang.iteye.com/blog/2357143#
[icon_star.png]: http://jaychang.iteye.com/images/icon_star.png
[spinner.gif]: /images/20220518/76f008e084d746be8976b3486c0467aa.png
[icon_star.png_spinner.gif]: https://blog.csdn.net/FLAYdragon/article/details/78517515
[view plain]: https://blog.csdn.net/FLAYdragon/article/details/78517515#
[http_jaychang.iteye.com_blog_2357143]: http://jaychang.iteye.com/blog/2357143