Mapper.xml映射文件中定义了操作数据库的sql,每个sql是一个statement,映射文件是mybatis的核心。注意:要将mapper映射文件放入主配置文件SqlMapConfig.xml中
1 parameterType
1.1 传递简单类型
1.1.1 mapper文件
<select id="getUserById" parameterType="Integer" resultType="com.itykd.domain.User">
select * from `user` where id=#{id}
</select>
1.1.2 测试
public void testQueryUserById() {
SqlSession sqlSession = MyBatisUtils.getSqlSessionFactory().openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = mapper.getUserById(1);
System.out.println(user);
sqlSession.close();
}
1.2 传递pojo对象
1.2.1 mapper文件
注意:下面的parameterType和resultType用的是别名
<select id="queryUserByCondition" parameterType="user" resultType="user">
select * from user where sex=#{sex}
and username like '%${username}%'
</select>
1.2.2 测试
void testqueryUserByCondition() {
SqlSession sqlSession = MyBatisUtils.getSqlSessionFactory().openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = new User();
user.setSex("1");
user.setUsername("张");
List<User> userList= mapper.queryUserByCondition(user);
for(User user2:userList)
System.out.println(user2);
sqlSession.close();
}
1.3 传递pojo包装对象
pojo包装对象也就是说一个对象中包含一个对象的情况
1.3.1 编写QueryVo(包含一个pojo对象User)
package com.itykd.domain;
import java.util.List;
public class QueryVo {
//包含其他的pojo
private User user;
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
}
1.3.2 mapper文件编写
<select id="getUserByQueryVo" parameterType="com.itykd.domain.QueryVo" resultType="com.itykd.domain.User">
select * from user where username like '%${user.username}%'
</select>
1.3.3 接口
package com.itykd.mapper;
import java.util.List;
import com.itykd.domain.QueryVo;
import com.itykd.domain.User;
public interface UserMapper {
List<User> getUserByQueryVo(QueryVo queryVo);
}
1.3.4 测试
void testgetUserByQueryVo() {
SqlSession sqlSession = MyBatisUtils.getSqlSessionFactory().openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
QueryVo queryVo = new QueryVo();
User user = new User();
user.setUsername("五");
queryVo.setUser(user);
List<User> userList=mapper.getUserByQueryVo(queryVo);
for(User user1 : userList) {
System.out.println(user1);
}
sqlSession.close();
}
2 resultType
2.1 输出简单类型
2.1.1 mapper.xml
<select id="queryUserCount" resultType="int">
select count(*) from user
</select>
2.1.2 测试
void testQueryUserCount() {
SqlSession sqlSession = MyBatisUtils.getSqlSessionFactory().openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
Integer count= mapper.queryUserCount();
System.out.println(count);
sqlSession.close();
}
2.2 输出pojo对象
见上述1.2部分
2.3 输出pojo列表
见上述1.2部分
3 resultMap
resultType可以将查询结果映射为pojo,但需要pojo的属性名和sql查询的列名一致方可映射成功。也就是说当pojo对象的属性与数据表的字段没有一一对应时,就可以使用resultMap使得二者相对应。
3.1 示例
假设有一个订单表orders与我们所创建的order中的字段并未一一对应;
3.1.1 mapper.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;还有一个重要的作用,Mapper动态代理开发的时候使用,需要指定mapper的类路径 -->
<mapper namespace="com.itykd.mapper.OrderMapper">
<!-- resultMap最终还是要将结果映射到pojo上,type就是指定映射到哪一个pojo,id就是设置ResultMap的id-->
<resultMap type="order" id="orderMapping">
<!-- 主键配置用id标签, 其他属性用result标签-->
<!-- property属性代表的是order类的属性,column代表orders表中的字段 -->
<id property="id" column="id"/>
<result property="userId" column="user_id"/>
<result property="number" column="number"/>
<result property="createtime" column="createtime"/>
<result property="note" column="note"/>
</resultMap>
<select id="queryOrderAll" resultMap="orderMapping">
select * from `orders`
</select>
</mapper>
3.1.2 测试
package com.itykd.junit;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import org.junit.jupiter.api.Test;
import com.itykd.domain.Order;
import com.itykd.domain.OrderUser;
import com.itykd.mapper.OrderMapper;
import com.itykd.util.MyBatisUtils;
class OrderMapperTest {
@Test
void testQueryOrderAll() {
SqlSession session = MyBatisUtils.getSqlSessionFactory().openSession();
OrderMapper orderMapper = session.getMapper(OrderMapper.class);
List<Order> list = orderMapper.queryOrderAll();
for(Order order : list) {
System.out.println(order);
}
}
}