一、输入输出映射
Mapper.xml映射文件中定义了操作数据库的sql,每个sql是一个statement,映射文件是mybatis的核心。
1、parameterType(输入类型)
1.1、传递简答类型
参考第一天内容
使用#{}占位符,或者${}进行SQL拼接
1.2、传递pojo对象
参考第一天的内容
Mybatis使用OGNL表达式解析对象字段的值,#{}或者${}括号当中的值为pojo属性名称
1.3、传递pojo包装对象:创建包装的pojo对象QueryVo
package com.itzheng.mybatis.pojo;
/*
* 包装的pojo
*/
public class QueryVo {
private User user;
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
}
1.4、在UserMapper当中创建getUserByQueryVo方法
/*
* 传递包装pojo
*/
List<User> getUserByQueryVo(QueryVo vo);
1.5、在UserMapperTest类当中创建testGetUserByQueryVo方法
@Test
public void testGetUserByQueryVo() {
SqlSession openSession = SqlSessionFactoryUtils.getSqlSessionFactory().openSession();
// 获取接口的代理实现类
UserMapper userMapper = openSession.getMapper(UserMapper.class);
QueryVo vo = new QueryVo();
User user = new User();
user.setUsername("张");
vo.setUser(user);
List<User> list = userMapper.getUserByQueryVo(vo);
for (User user2 : list) {
System.out.println(user2);
}
openSession.close();
}
运行测试
2、统计个数
(1)修改UserMapper.xml创建对应的内容
<select id="getUserCount" resultType="int" >
SELECT COUNT(1) FROM `user`
</select>
(2)创建对应的接口当中的方法
/*
* 查询用户总记录数
*/
Integer getUserCount();
(3)在UserMapperTest类当中创建对应的方法
@Test
public void testGetUserByCount() {
SqlSession openSession = SqlSessionFactoryUtils.getSqlSessionFactory().openSession();
// 获取接口的代理实现类
UserMapper userMapper = openSession.getMapper(UserMapper.class);
System.out.println("用户总记录数为:"+userCount);
System.out.println(userCount);
openSession.close();
}
运行测试结果
3、查询订单列表
(1)创建OrderMapper接口当中的getOrderList方法
package com.itzheng.mybatis.mapper;
import java.util.List;
import com.itzheng.mybatis.pojo.Order;
/*
* 订单的持久化接口
*/
public interface OrderMapper {
/*
* 获取订单列表
*/
List<Order> getOrderList();
}
(2)在OrderMapper.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">
<!-- namespace:命名空间,用于隔离SQL语句,后继有重要 #{}:是一个占位符,相当于jdbc的?号 用字符串拼接指令${}:字符串拼接指令,如果入参为普通数据类型{}内部只能写value -->
<!-- 动态代理开发规则:
1、namespace:必须是接口的全路径名
2、接口的方法名必须与SQL的id一致
3、接口的入参与parameterType类型一致
4、接口的返回值必须与resultType类型一致
-->
<mapper namespace="com.itzheng.mybatis.mapper.OrderMapper">
<select id="getOrderList" resultType="order">
SELECT
`id`,
`user_id` userId,
`number`,
`createtime`,
`note`
FROM `order`
</select>
</mapper>
(3)创建单元测试包OrderMapperTest
package com.itzheng.mybatis.test;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.junit.Test;
import com.itzheng.mybatis.mapper.OrderMapper;
import com.itzheng.mybatis.pojo.Order;
import com.itzheng.mybatis.utils.SqlSessionFactoryUtils;
public class OrderMapperTest {
@Test
public void testGetOrderList() {
SqlSession openSession = SqlSessionFactoryUtils.getSqlSessionFactory().openSession();
//获取OrderMapper代理实现
OrderMapper ordermapper = openSession.getMapper(OrderMapper.class);
List<Order> list = ordermapper.getOrderList();
for (Order order : list) {
System.out.println(order);
}
openSession.close();
}
}
运行测试
4、resultMap
(1)修改OrderMapper.xml添加
<!-- resultMap入门,定义 -->
<resultMap type="order" id="order_list_map">
<!-- <id>用于映射主键 -->
<id property="id" column="id"/>
<!-- 普通字段用<result>映射 -->
<result property="userId" column="user_id"/>
<result property="number" column="number"/>
<result property="createtime" column="createtime"/>
<result property="note" column="note"/>
</resultMap>
<!-- 使用resultMap -->
<select id="getOrderListMap" resultMap="order_list_map">
SELECT
`id`,
`user_id`,
`number`,
`createtime`,
`note`
FROM `order`
</select>
(2)在OrderMapper接口当中创建getOrderListMap方法
List<Order> getOrderListMap();
测试运行
5、动态sql-if标签的使用(条件查询)
(1)修改UserMapper.xml
<select id="getUserByPojo" parameterType="user" resultType="user">
SELECT
id,
username,
birthday,
sex,
address
FROM `user`
<!-- WHERE username LIKE #{name} -->
WHERE 1 = 1
<if test="username != null and username != ''">
and username LIKE '%${username}%'
</if>
<if test="sex != null and sex != '' " >
and sex = #{sex}
</if>
</select>
(2)在接口当中创建对应的方法
List<User> getUserByPojo(User user);
(3)UserMapperTest当中创建对应的testGetUserByPojo方法
@Test
public void testGetUserByPojo() {
SqlSession openSession = SqlSessionFactoryUtils.getSqlSessionFactory().openSession();
// 获取接口的代理实现类
UserMapper userMapper = openSession.getMapper(UserMapper.class);
User user = new User();
user.setSex("1");
user.setUsername("张");
List<User> userByPojo = userMapper.getUserByPojo(user);
for (User user2 : userByPojo) {
System.out.println(user2);
}
openSession.close();
}
运行测试
6、动态sql-sql片段的使用
(1)抽取SQL片段
<sql id="user_sql">
id,
username,
birthday,
sex,
address
</sql>
(2)引用sql片段的id
<!-- id:sql语句的唯一标识 parameterType:入参的数据类型 resultType:返回结果的数据类型 -->
<select id="getUserByid" parameterType="int" resultType="com.itzheng.mybatis.pojo.User">
SELECT
<!-- SQL片段的使用:refid引用定义好的sql片段id -->
<include refid="user_sql"></include>
FROM
USER WHERE id = #{id1}
</select>
(3)运行测试代码
7、sql-foreach标签的使用:
(1)修改QueryVo类
(2)修改UserMapper.xml
<select id="getUserByIds">
SELECT
<include refid="user_sql"></include>
FROM `user`
WHERE id IN(1,25,29,30,35)
</select>
运行测试代码
8、(方式一)一对一关联查询
(1)创建OrderUser
package com.itzheng.mybatis.pojo;
/*
* 订单关联用户信息的pojo
*/
public class OrderUser extends Order {
private String username;// 用户姓名
private String address;// 地址
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
@Override
public String toString() {
return "OrderUser [username=" + username + ", address=" + address + ", getUsername()=" + getUsername()
+ ", getAddress()=" + getAddress() + ", getId()=" + getId() + ", getUserId()=" + getUserId()
+ ", getNumber()=" + getNumber() + ", getCreatetime()=" + getCreatetime() + ", getNote()=" + getNote()
+ ", toString()=" + super.toString() + ", getClass()=" + getClass() + ", hashCode()=" + hashCode()
+ "]";
}
}
(2)修改OrderMapper.xml
<!-- 一对一关联查询:resultType使用 -->
<select id="getOrderUser" resultType="orderuser" >
SELECT
o.`id`,
o.`user_id` userId,
o.`number`,
o.`createtime`,
o.`note`,
u.`username`,
u.`address`
FROM
`order` o
LEFT JOIN `user` u
ON u.id = o.user_id
</select>
(3)OrderMapper接口当中创建getOrderUser方法
/*
* 一对一关联:resultType使用
*/
List<OrderUser> getOrderUser();
(4)OrderMapperTest当中编写测试方法testGetOrderUser
@Test
public void testGetOrderUser() {
SqlSession openSession = SqlSessionFactoryUtils.getSqlSessionFactory().openSession();
//获取OrderMapper代理实现
OrderMapper ordermapper = openSession.getMapper(OrderMapper.class);
List<OrderUser> orderUser = ordermapper.getOrderUser();
for (OrderUser orderUser2 : orderUser) {
System.out.println(orderUser2);
}
openSession.close();
}
运行测试代码
9、(方式一)一对一关联查询:ResultMap:一个订单只有一个人
(1)修改Order
private User user;
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
(2)修改OrderMapper.xml
<resultMap type="order" id="order_user_map">
<!-- <id>用于映射主键 -->
<id property="id" column="id"/>
<!-- 普通字段用<result>映射 -->
<result property="userId" column="user_id"/>
<result property="number" column="number"/>
<result property="createtime" column="createtime"/>
<result property="note" column="note"/>
<!--
association:用于配置一对一关系
property:order里面的user属性
javaType:user的数据类型,支持别名
-->
<association property="user" javaType="com.itzheng.mybatis.pojo.User" >
<id property="id" column="user_id"/>
<result property="username" column="username"/>
<result property="address" column="address"/>
<result property="birthday" column="birthday"/>
<result property="sex" column="sex"/>
</association>
</resultMap>
<!-- 一对一关联查询:resultType使用 -->
<select id="getOrderUserMap" resultMap="order_user_map">
SELECT
o.`id`,
o.`user_id` userId,
o.`number`,
o.`createtime`,
o.`note`,
u.`username`,
u.`address`,
u.`birthday`,
u.`sex`
FROM
`order` o
LEFT JOIN `user` u
ON u.id = o.user_id
</select>
(3)在OrderMapper接口当中编写getOrderUserMap方法
/*
* 一对一关联:ResultMap使用
*/
List<Order> getOrderUserMap();
(4)编写测试方法,然后运行
@Test
public void testGetOrderUserMap() {
SqlSession openSession = SqlSessionFactoryUtils.getSqlSessionFactory().openSession();
//获取OrderMapper代理实现
OrderMapper ordermapper = openSession.getMapper(OrderMapper.class);
List<Order> orderUserMap = ordermapper.getOrderUserMap();
for (Order order : orderUserMap) {
System.out.println(order);
System.out.println(" 此订单的用户为:" + order.getUser());
}
openSession.close();
}
运行测试
10、(方式二)一对多关联查询:一个人有多个订单
(1)修改User
private List<Order> orders;
public List<Order> getOrders() {
return orders;
}
public void setOrders(List<Order> orders) {
this.orders = orders;
}
(2)修改UserMapper.xml
<resultMap type="user" id="user_order_map">
<id property="id" column="id"/>
<!-- 普通字段用<result>映射 -->
<result property="username" column="username"/>
<result property="address" column="address"/>
<result property="birthday" column="birthday"/>
<result property="sex" column="sex"/>
<!--
collection用于配置一对多关联
property :User当中的Order属性
ofType:orders的数据类型。支持别名
-->
<collection property="orders" ofType="com.itzheng.mybatis.pojo.Order" >
<!-- <id>用于映射主键 -->
<id property="id" column="oid"/>
<!-- 普通字段用<result>映射 -->
<result property="userId" column="user_id"/>
<result property="number" column="number"/>
<result property="createtime" column="createtime"/>
<result property="note" column="note"/>
</collection>
</resultMap>
<select id="getUserOrderMap" resultMap="user_order_map" >
SELECT
u.`id`,
u.`username`,
u.`birthday`,
u.`sex`,
u.`address`,
u.`uuid2`,
o.`id` oid,
o.`number`,
o.`createtime`,
o.`note`
FROM
`user` u
LEFT JOIN `order` o
ON o.`user_id` = u.`id`
</select>
(3)在UserMapper接口当中创建getUserOrderMap方法
(4)编写测试方法
@Test
public void testGetUserOrderMap() {
SqlSession openSession = SqlSessionFactoryUtils.getSqlSessionFactory().openSession();
// 获取接口的代理实现类
UserMapper userMapper = openSession.getMapper(UserMapper.class);
List<User> list = userMapper.getUserOrderMap();
for (User user2 : list) {
System.out.println(user2);
for (Order order : user2.getOrders()) {
System.out.println(" 此用户的订单有:"+order);
}
}
openSession.close();
}
总结