关于MyBatis的输入映射和输出映射,可输入的类型很多,输出类型亦是如此。如图所示:
一、输入类型是通过参数parameterType来设置的,参数类型有基本数据类型、包装类型pojo、Map
基础类型(String,int,long,double...)
pojo类型
pojo的包装类型
map
二、输出类型是通过参数resultType和resultMap来设置的,参数类型有基本类型、pojo类型、List类型、map
基础类型
pojo类型
List类型
map
三、具体举例:(使用的项目还是上一篇文章中的项目)
1.需求:根据用户id查询用户对象。
输入参数是用户id(Int类型),输出参数是User对象(pojo类型)
UserMapper接口如下:
package com.evil.mapper;
import com.evil.pojo.User;
public interface UserMapper {
User findUserById(Integer id);//按用户Id进行查询
}
映射文件UserMapper.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.evil.mapper.UserMapper">
<select id="findUserById" parameterType="int" resultType="User">
select * from user where id=#{id};
</select>
</mapper>
测试类:
package com.evil.test;
import org.junit.Before;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.evil.mapper.UserMapper;
import com.evil.pojo.User;
public class NewMapperTest {
UserMapper userMapper;
@Before
public void initUserMapper() throws Exception {
ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
userMapper=context.getBean(UserMapper.class);
}
//根据id进行查询
@Test
public void findUserById(){
User user =userMapper.findUserById(1);
System.out.println(user);
}
}
查询结果:User [id=1, username=王五, sex=2, birthday=null, address=null, orders=null]
2.需求:查询所有用户的总数
输入参数:无,输出参数是基本数据类型(int)
UserMapper接口中添加方法:
int findAllUser(); //查询用户总数
UserMapper.xml中添加语句:
<select id="findAllUser" resultType="int">
select count(id) from user;
</select>
测试类代码:
@Test
public void findAllUser(){
int i = userMapper.findAllUser();
System.out.println("用户的总数是"+i);
}
查询结果:用户的总数是8
3.需求:前台在复选框里勾选了多个用户,做用户信息的查询,此时传到后台的是一个String的字符串,里面是"1,3,5,11"类型
输入参数是基本类型(String),输出参数是List(List<User>)
userMapper接口添加方法:
List<User> findUserByIds2(String ids); //查询用户列表
userMapper.xml中添加sql语句,注意虽然输出结果是List<User>,但是在填写resultType的参数时,输出映射依然使用User对象
//查询所有的用户总数
<!-- 前台字符串拼接穿ids -->
<select id="findUserByIds2" parameterType="String" resultType="User">//类型依然是User,但是输出映射的结果是List
select * from user where id in(${value});
</select>
测试:
//前台拼接ids,传递到mapper中
@Test
public void findByUserIds2(){
String ids="1,10,22,25";
List<User> list = userMapper.findUserByIds2(ids);
for (User user : list) {
System.out.println(user);
}
}
查询结果:
User [id=1, username=王五, sex=2, birthday=null, address=null, orders=null]
User [id=10, username=张三, sex=1, birthday=Thu Jul 10 00:00:00 CST 2014, address=北京市, orders=null]
User [id=25, username=狗剩, sex=1, birthday=null, address=北海道, orders=null]
4.需求:做条件查询,查询条件一个是用户性别和用户姓名
输入参数是两个,可以用pojo来装,所以输入参数是pojo(User),输出参数类型是List<User>
UserMapper接口添加代码:
List<User> findUserByIf(User user); //条件查询
UserMapper.xml中添加sql语句:
使用if和where标签实现动态sql
<!-- 条件查询 -->
<select id="findUserByIf" parameterType="com.evil.pojo.User" resultType="com.evil.pojo.User">
select * from user
<!--where的两个作用:
1.如果where标签里的任何一个if成立,就动态添加一个where关键词
2.自动去除where关键字后的第一个and
-->
<where>
<if test="sex!=null and sex!=''">
and sex=#{sex}
</if>
<if test="username!=null and username!=''">
and username like '%${username}%';
</if>
</where>
</select>
测试:
//条件查询
@Test
public void findUserByIf(){
User user = new User();
user.setSex("1");
user.setUsername("张");
List<User> list = userMapper.findUserByIf(user);
for (User user2 : list) {
System.out.println(user2);
}
}
查询结果:
User [id=10, username=张三, sex=1, birthday=Thu Jul 10 00:00:00 CST 2014, address=北京市, orders=null]
User [id=16, username=张小明, sex=1, birthday=null, address=河南郑州, orders=null]
User [id=24, username=张三丰, sex=1, birthday=null, address=河南郑州, orders=null]
四、MyBatis的多表关联查询
现在有两张表,User表(id,username,birthday,sex,address),Orders订单表(id,user_id,number,createtime,note)
创建Orders的实体类:
package com.evil.pojo;
import java.util.Date;
public class Orders{
private Integer id; //订单标识
private Integer userId; //用户id
private String number; //购买数量
private Date createtime; //购买时间
private String note; //购买物品
private User user;
//..省略getXXX/setXXX.....
5.需求:查询条件是用户姓名,用户购买的物品,很显然来自于两张表,查询结果是该用户的订单数据,也是来源于两张表,怎么办呢?
方法一:当前台传过来这两个参数时,我们可以将其封装在QueryVo中,里面放置User和Orders的对象,这样就可以接受到参数。但是输出结果
来源于两张表,无法直接映射成User对象或者Orders对象,所以使用Map<String,Object>来接收,第一个参数代表列,第二个参数代表值。
输入参数是:包装类型(QueryVo),输出类型是List<Map<String,Object>>,输出映射参数填Map
创建QueryVo:
package com.evil.pojo;
import java.util.List;
public class QueryVo {
User user;
Orders orders;
......
Mapper接口添加方法:
List<Map<String,Object>> findUserAndOrders(QueryVo vo);//查询用户和订单数据
mapper.xml中添加sql:
<!-- 参数是包装类型,返回时List<map<String,object>> -->
<select id="findUserAndOrders" parameterType="com.evil.pojo.QueryVo" resultType="map">
SELECT u.*,o.id AS oid,o.user_id,o.number,o.createtime,o.note FROM USER u JOIN orders o
ON u.id=o.user_id WHERE u.username=#{user.username} AND o.note LIKE '%${orders.note}%';
</select>
测试:
//两表联查,输出映射是map,输入映射是包装类型
@Test
public void findUserAndOrders(){
QueryVo vo =new QueryVo();
User user=new User();
user.setUsername("王五");
vo.setUser(user);
Orders orders = new Orders();
orders.setNote("电脑");
vo.setOrders(orders);
List<Map<String,Object>> list = userMapper.findUserAndOrders(vo);
for (Map<String, Object> map : list) {
System.out.println(map);
}
}
查询结果:
{createtime=2015-02-04 13:22:35.0, id=1, sex=2, username=王五, oid=3, number=1000010, user_id=1, note=华硕电脑}
{createtime=2015-02-03 13:22:41.0, id=1, sex=2, username=王五, oid=4, number=1000011, user_id=1, note=宏碁电脑}
方法二:查询条件是用户姓名和用户购买的物品,我们可以将其封装在Map里面,返回结果依然是来源于用户表和订单表(用List<Map<String,Object>>封装)
输入参数类型是map,输出参数类型也是map
Mapper接口里添加方法:
List<Map<String,Object>> findUserAndOrders2(Map<String,Object> map);//查询用户和订单数据
mapper.xml里添加sql查询语句:
<!--参数是map类型,返回值也是包装类型 -->
<select id="findUserAndOrders2" parameterType="map" resultType="map">
SELECT u.*,o.id AS oid,o.user_id,o.number,o.createtime,o.note FROM USER u JOIN orders o
ON u.id=o.user_id WHERE u.username=#{username} AND o.note LIKE '%${note}%';
</select>
测试:
//两表联查,输出映射是Map,输出映射也是map
@Test
public void findUserAndOrders2(){
Map map = new HashMap<>();
map.put("username", "王五");
map.put("note", "电脑");
List<Map<String,Object>> list =userMapper.findUserAndOrders2(map);
for (Map<String, Object> map2 : list) {
System.out.println(map2);
}
}
五、MyBatis多表关联查询之resultMap的使用输出结果:
{createtime=2015-02-04 13:22:35.0, id=1, sex=2, username=王五, oid=3, number=1000010, user_id=1, note=华硕电脑}
{createtime=2015-02-03 13:22:41.0, id=1, sex=2, username=王五, oid=4, number=1000011, user_id=1, note=宏碁电脑}
mybatis一对一(多对一)关系映射:在Orders得实体类中存在一个User对象,代表的关系是每个订单都属于唯一的一个用户
1。需求:查询所有的订单(查询结果中有用户对象)
输入类型:无,输出类型list,但是resultMap的type填的是pojo类型
创建OrdersMapper接口:
package com.evil.mapper;
import java.util.List;
import com.evil.pojo.Orders;
public interface OrdersMapper {
public List<Orders> findUserByLink3(); //多表关联查询,手动映射
}
创建OrdersMapper.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.evil.mapper.OrdersMapper">
<!-- 一对一(多对一)的关联查询 -->
<select id="findUserByLink3" resultMap="map1">
SELECT u.*,o.id AS oid,o.user_id,o.number,o.createtime,o.note FROM USER u JOIN orders o
ON u.id=o.user_id
</select>
<!--resultMap:手动指定sql语句查询出的列名与pojo的属性值是意义对应的关系
type:输出映射的类型,
id标签:指定主键的映射关系
column:sql语句查询出的列名,不是原列名
property:pojo的属性名称
result:指定非主键的映射关系
association:指定单个对象的映射关系(因为Orders中村的是User对象)
property:对象的属性名称
javaType:该对象的类型
-->
<resultMap type="com.evil.pojo.Orders" id="map1">
<id column="oid" property="id"/>
<result column="user_id" property="userId"/><!-- 注意这里,查出来的列名和属性名不一样 -->
<result column="number" property="number"/>
<result column="createtime" property="createtime"/>
<result column="note" property="note"/>
<association property="user" javaType="com.evil.pojo.User">
<id column="id" property="id"/>
<result column="address" property="address"/>
<result column="birthday" property="birthday"/>
<result column="sex" property="sex"/>
<result column="username" property="username"/>
</association>
</resultMap>
</mapper>
创建测试类OrdersMapperTest.java:
package com.evil.test;
import java.util.List;
import org.junit.Before;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.evil.mapper.OrdersMapper;
import com.evil.pojo.Orders;
public class OrderMapperTest {
OrdersMapper ordersMapper;
@Before
public void initUserMapper() throws Exception {
ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
ordersMapper=context.getBean(OrdersMapper.class);
}
//第三种映射
@Test
public void findUserByLink3(){
List<Orders> list = ordersMapper.findUserByLink3();
for (Orders orders : list) {
System.out.println(orders);
}
}
}
查询结果:
Orders [id=3, userId=1, number=1000010, createtime=Wed Feb 04 13:22:35 CST 2015, note=华硕电脑,
user=User [id=1, username=王五, sex=2, birthday=null, address=null, orders=null]]
Orders [id=4, userId=1, number=1000011, createtime=Tue Feb 03 13:22:41 CST 2015, note=宏碁电脑,
user=User [id=1, username=王五, sex=2, birthday=null, address=null, orders=null]]
Orders [id=5, userId=10, number=1000012, createtime=Thu Feb 12 16:13:23 CST 2015, note=联想电脑,
user=User [id=10, username=张三, sex=1, birthday=Thu Jul 10 00:00:00 CST 2014, address=北京市, orders=null]]
Orders [id=3, userId=1, number=1000010, createtime=Wed Feb 04 13:22:35 CST 2015, note=华硕电脑, user=User [id=1, username=王五, sex=2, birthday=null, address=null, orders=null]]
Orders [id=4, userId=1, number=1000011, createtime=Tue Feb 03 13:22:41 CST 2015, note=宏碁电脑, user=User [id=1, username=王五, sex=2, birthday=null, address=null, orders=null]]
Orders [id=5, userId=10, number=1000012, createtime=Thu Feb 12 16:13:23 CST 2015, note=联想电脑, user=User [id=10, username=张三, sex=1, birthday=Thu Jul 10 00:00:00 CST 2014, address=北京市, orders=null]]
mybatis的一对多的关系映射:一个用户有多个订单,在User得实体里面有Orders的集合
2.需求,查询所有的用户,包含用户下的订单
输入参数无,输出参数List<User>
在UserMapper接口里添加方法:
List<User> findUserByLink4();//查询用户数据,包含用户下的订单
在UesrMapper.xml中添加sql语句:
<!-- 一对多关系的映射 -->
<select id="findUserByLink4" resultMap="map2">
SELECT u.*,o.id AS oid,o.user_id,o.number,o.createtime,o.note FROM USER u JOIN orders o
ON u.id=o.user_id
</select>
<resultMap id="map2" type="com.evil.pojo.User">
<id column="id" property="id"/>
<result column="address" property="address"/>
<result column="birthday" property="birthday"/>
<result column="sex" property="sex"/>
<result column="username" property="username"/>
<collection property="orders" ofType="com.evil.pojo.Orders"><!-- ofType是些集合里面的泛型的类型 -->
<id column="oid" property="id"/>
<result column="user_id" property="userId"/><!-- 注意这里,查出来的列名和属性名不一样 -->
<result column="number" property="number"/>
<result column="createtime" property="createtime"/>
<result column="note" property="note"/>
</collection>
</resultMap>
测试:
//关联查询的第四种方式:一对多,手动映射
@Test
public void findUserByLink4(){
List<User> list = userMapper.findUserByLink4();
for (User user : list) {
System.out.println(user);
}
}
查询结果:
User [id=1, username=王五, sex=2, birthday=null, address=null,
orders=[Orders [id=3, userId=1, number=1000010, createtime=Wed Feb 04 13:22:35 CST 2015, note=华硕电脑, user=null],
Orders [id=4, userId=1, number=1000011, createtime=Tue Feb 03 13:22:41 CST 2015, note=宏碁电脑, user=null]]]
User [id=10, username=张三, sex=1, birthday=Thu Jul 10 00:00:00 CST 2014, address=北京市,
orders=[Orders [id=5, userId=10, number=1000012, createtime=Thu Feb 12 16:13:23 CST 2015, note=联想电脑, user=null]]]
六,关于在sql语句中,#{}和${}的使用规范:
#{}占位符:
如果paramterType是基础类型,那么#{}里面的变量可以任意填写
如果parameterType是pojo类型,那么#{}里面的变量必须是属性名称
如果parameterType是pojo的包装类型,那么#{}里面的变量必须是属性.属性....
如果parameterType是map类型,那么#{}里面的变量必须是map的key
${}拼接符:
如果paramterType是基础类型,那么${}里面的变量必须填value
如果parameterType是pojo类型,那么${}里面的变量必须是属性名称
如果parameterType是pojo的包装类型,那么${}里面的变量必须是属性.属性....
如果parameterType是map类型,那么${}里面的变量必须是map的key