本文共 3144 字,大约阅读时间需要 10 分钟。
对于一对多查询,网上书上的例子大多都是传入一个条件的,比如有两个表,一个用户表,一个手机表,一个用户可以有多个手机,我们就可以在用户的实体类
中放一个手机的list,用一对多查询,就可以查询一个用户的所有手机信息,但是呢,如果手机有各种类型,如果查询用户特定类型的手机呢?这个时候就需要传入
两个参数了,如何解决呢?
看下面一个例子(对这个一对多查询我是有疑惑的,因为这看起来更是一个普通查询,应该是mybatis底层能进行很好的封装吧)
数据库:
Person表
mysql> desc person;
+-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(55) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+Phone表:
mysql> desc phone;
+------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(55) | YES | | NULL | | | phone_type | varchar(55) | YES | | NULL | | | person_id | int(11) | YES | MUL | NULL | | +------------+-------------+------+-----+---------+----------------+实体类:
public class Person {
private int id; private String name; private List<Phone> phones=new ArrayList<>(); public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public List<Phone> getPhones() { return phones; } public void setPhones(List<Phone> phones) { this.phones = phones; } public String toString(){ return "["+name+","+phones+"]"; } }public class Phone {
private int id; private String name; private String phone_type; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getPhone_type() { return phone_type; } public void setPhone_type(String phone_type) { this.phone_type = phone_type; } public String toString(){ return "["+name+","+phone_type+"]"; } }配置文件:
<mapper namespace="org.fkit.mapper.PersonMapper">
<resultMap type="org.fkit.domain.Person" id="personBean"> <id column="personid" property="id"/> <result column="personname" property="name"/> <collection property="phones" ofType="org.fkit.domain.Phone"> <id column="phoneid" property="id"/> <result column="phonename" property="name"/> <result column="phone_type" property="phone_type"/> </collection> </resultMap> <select id="selectPersonById" parameterType="hashmap" resultMap="personBean"> select p.id as personid,p.name as personname,o.id as phoneid, o.name as phonename,phone_type from person p, phone o where p.id=o.person_id and o.phone_type=#{phone_type} and p.id in(1,2); </select> </mapper>测试:
public class ToManyTest {
public static void main(String[] args) { // TODO Auto-generated method stub SqlSession session=FKSqlSessionFactory.getSqlSession(); PersonMapper mapper=session.getMapper(PersonMapper.class); Map<String, Object> map=new HashMap<>(); map.put("phone_type", "mi"); List<Person> persons=mapper.selectPersonById(map); System.out.println(persons); } }效果:
[[tom,[[mi6,mi], [mi4,mi], [Redmi,mi]]], [jack,[[RedMi,mi], [RedMi3,mi], [RedMi4,mi]]]]
转载地址:http://aljqi.baihongyu.com/