欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

mybatis collection list string  

程序员文章站 2022-04-04 22:52:38
...

 记录下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]]

 

附件中有项目源码