mybatis collection 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

 

 

实体类：

import java.io.Serializable;
import java.util.List;

public class People implements Serializable{
	
	private static final long serialVersionUID = -5935066186174346694L;
	private Long id;
	private String name;
	private List<String> qqs;
	public Long getId() {
		return id;
	}
	public void setId(Long id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public List<String> getQqs() {
		return qqs;
	}
	public void setQqs(List<String> qqs) {
		this.qqs = qqs;
	}
	@Override
	public String toString() {
		return "People [id=" + id + ", name=" + name + ", qqs=" + qqs + "]";
	}
}

 

mapper接口：

import com.hnpicheng.mybatisissue.domain.People;

public interface PeopleMapper {
	People selectPeopleById( Long id);
}

 

测试代码：

import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.hnpicheng.mybatisissue.domain.People;
import com.hnpicheng.mybatisissue.mapper.PeopleMapper;

public class App 
{
    public static void main( String[] args )
    {
       ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("spring-mybatis.xml");
       PeopleMapper peopleMapper = context.getBean(PeopleMapper.class);
       People p = peopleMapper.selectPeopleById(1L);
       System.out.println(p);
       
    }
}

 

 

PeopleMapper.xml

 

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

那么可以使用

<?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.hnpicheng.mybatisissue.mapper.PeopleMapper">

	<resultMap id="peopleResultMap" type="People">
   		<id property="id" column="id" />
   		<result property="name" column="name" /> 
   		<collection property="qqs" ofType="string" javaType="list">
     		<result column="qq" />
   		</collection>
 	</resultMap>
	
	<select id="selectPeopleById" resultMap="peopleResultMap">
		select p.*,pq.qq from
		people p left join  people_qq pq on p.id = pq.people_id 
		where p.id = #{id}
	</select>
</mapper>

 

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

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

 

 

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

<?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.hnpicheng.mybatisissue.mapper.PeopleMapper">
<resultMap id="peopleResultMap" type="People">
   		<id property="id" column="id" />
   		<result property="name" column="name" /> 
   		<collection property="qqs" column="id" select="selectQQByPeopleId">
     		<result column="qq" />
   		</collection>
 	</resultMap>
	
	<select id="selectPeopleById" resultMap="peopleResultMap">
		select * from people where id = #{id}
	</select>
	
	<select id="selectQQByPeopleId" resultType="string">
		select qq from people_qq where people_id = #{id}
	</select>
</mapper>

 

测试结果：

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

 

附件中有项目源码