【4】. Mapper映射文件【 输入类型映射和输出类型映射:parameterType传递包装类型、resultType的使用、resultMap入门、动态SQL(where if、sql片段(sql if、foreach))】
【resultType】:将 SQL查询结果映射为 JAVA对象。要求数据库中字段与类中对应属性相同,不同则不赋值;
【resulMap】:将 SQL查询结果映射为 JAVA对象。可定义别名;
XxMapper.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.iotek.mapper.UserMapper">
<!-- 开启mapper映射文件的二级缓存 (可省) -->
<cache></cache>
<!-- resultType -->
<select id="findUserList" parameterType="userQueryVo" resultType="user">
select * from Users where username like '%${userCustom.username}%'
</select>
<select id="findUserCount" parameterType="userQueryVo" resultType="int">
select count(*) from Users where username like '%${userCustom.username}%'
</select>
<!-- resultMap -->
<resultMap id="userListMap" type="user">
<id colum="u_id" property="id"/> //主键
<result colum="u_username property="username"/> //普通列
<result colum="u_age property="age"/>
</resultMap>
<select id="findUserList" parameterType="userQueryVo" resultMap="userListMap">
select id u_id,username u_username ,age u_age from Users where username like '%${userCustom.username}%'
</select>
</mapper>
XxMapper.java
public interface userMapper{
public List<User> findUserList(UserQueryVo userQueryVo) throws Exception;
public int findUserCount(UserQueryVo userQueryVo) throws Exception;
}
XxTest.java
public class AppTest {
SqlSessionFactory sqlSessionFactory=null;
@Before
public void init() throws IOException {
InputStream inputStream=Resources.getResourcesAsStream("SqlMapConfig.xml");
sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
}
@Test
public void testFindUserList() throws Exception(){
SqlSession sqlSession=sqlSessionFactory.openSession();
UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
UserQueryVo userQueryVo=new UserQueryVo();
UserCustom userCustom=new UserCustom();
userCustom.setUsername("张");
userQueryVo.setUserCustom(userCustom);
List<User> users=userMapper.findUserList(userQueryVo);
sqlSession.close();
for (User user:users) {
System.out.println(user.getId()+","+user.getUsername());
}
}
@Test
public void testFindUserList() throws Exception(){
SqlSession sqlSession=sqlSessionFactory.openSession();
UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
UserQueryVo userQueryVo=new UserQueryVo();
UserCustom userCustom=new UserCustom();
userCustom.setUsername("张");
userQueryVo.setUserCustom(userCustom);
int count=userMapper.findUserCount(userQueryVo);
sqlSession.close();
for (User user:users) {
System.out.println("姓张的人数:"+count);
}
}
}
1. parameterType传递包装类型
输入映射类型和输出映射类型:
parameterType:java基本数据类型、pojo类型;
parameterType传递 pojo的包装类型;
(若希望传递一些组合起来的类型,包括 java基本数据类型和 pojo类型,但使用 mpper.xml定义内容时,parameterType只能是一个参数,此时用 parameterType来传递 pojo的包装类型。)
需求例如:查询时输入条件,条件可能是用户、商品
解决:
1. 包装类型
2. Mapper.xml
3. Mapper.java
- 包装类型: com.iotek.po包下的 UserQueryVo.java:将 User、UserCustom (用户扩展类,继承 User)包装进去;
目录:
UserQueryVo.java
public class UserQueryVo {
private User user; //用户信息
private UserCustom userCustom; //自定义User扩展对象
set()、get()
}
UserCustom .java
public class UserCustom extends User {
//可添加新内容
}
2. Mapper.xml
UserMapper.xml
查询的类型 parameterType是 UserQueryVo,因为【3】全局文件中定义了 com.iotek.po包下的所有类都有别名,所以 parameterType可以直接写 userQueryVo;resultType返回类型是用户;sql语句中在 UserQueryVo类型中通过getUserCustom()方法拿 userCustom;UserCustom类 中有 User类的内容,User类型中有 getUsername(),所以直接写成 ${userCustom.username}。
<mapper namespace="com.iotek.mapper.UserMapper">
<select id="findUserList" parameterType="userQueryVo" resultType="user">
select * from Users where username like '%${userCustom.username}%'
</select>
</mapper>
3. Mapper.java
UserMapper.java
方法名是 UserMapper.xml中 statement的 id即findUserList,参数类型是UserQueryVo ,返回值是 List< User>;
public interface userMapper{
public List<User> findUserList(UserQueryVo userQueryVo) throws Exception;
}
AppTest.java:
userMapper是通过 sqlsession.getMapper(UserMapper.class)得到;userMapper.findUserList(userQueryVo) 查询,需要 userQueryVo,所以 new;userQueryVo再 userQueryVo.setUserCustom(userCustom),需要 userCustom,所以 new;userCustom中 userCustom.setUsername(“张”),即再设置 username;再查询,返回 List< User> 。
public class AppTest {
SqlSessionFactory sqlSessionFactory=null;
@Before
public void init() throws IOException {
InputStream inputStream=Resources.getResourcesAsStream("SqlMapConfig.xml");
sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
}
@Test
public void testFindUserList() throws Exception(){
SqlSession sqlSession=sqlSessionFactory.openSession();
UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
UserQueryVo userQueryVo=new UserQueryVo();
UserCustom userCustom=new UserCustom();
userCustom.setUsername("张");
userQueryVo.setUserCustom(userCustom);
List<User> users=userMapper.findUserList(userQueryVo);
sqlSession.close();
for (User user:users) {
System.out.println(user.getId()+","+user.getUsername());
}
}
}
2. 输出类型映射 resultType
resultType 返回:
1. 简单类型: SQL 语句一定是返回一行一列
2. pojo类型:selectOne返回一条记录、selectList返回多条记录
1. 返回简单类型:
UserMapper.xml
<mapper namespace="com.iotek.mapper.UserMapper">
<select id="findUserCount" parameterType="userQueryVo" resultType="int">
select count(*) from Users where username like '%${userCustom.username}%'
</select>
</mapper>
UserMapper.java
public interface userMapper{
public int findUserCount(UserQueryVo userQueryVo) throws Exception;
}
AppTest.java:
public class AppTest {
SqlSessionFactory sqlSessionFactory=null;
@Before
public void init() throws IOException {
InputStream inputStream=Resources.getResourcesAsStream("SqlMapConfig.xml");
sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
}
@Test
public void testFindUserList() throws Exception(){
SqlSession sqlSession=sqlSessionFactory.openSession();
UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
UserQueryVo userQueryVo=new UserQueryVo();
UserCustom userCustom=new UserCustom();
userCustom.setUsername("张");
userQueryVo.setUserCustom(userCustom);
int count=userMapper.findUserCount(userQueryVo);
sqlSession.close();
for (User user:users) {
System.out.println("姓张的人数:"+count);
}
}
}
3. 输出类型映射 resultMap
resultMap是配置输出类型,完成复杂数据类型映射(一对多,多对多映射)
【resultType】:指定输出结果的类型,将 SQL查询结果映射为 JAVA对象;(sql查询的列名与 pojo类的属性一致,如果有不同时,不同的不赋值(eg:②);如果列名与 pojo类的属性名全部不一致,不创建 pojo对象(eg:③));
【resultMap】:将 SQL查询结果映射为 JAVA对象;(id:唯一标识;type:映射的 pojo类型);
(注:1. 如果定义的 resultMap与使用在同一个 mapper.xml中,直接使用;
如果定义的 resultMapper与使用在不在同一个 mapper.xml中,要加命名空间(即要把命名空间加在 resultMap="userListMap"前,即resultMap=“命名空间.userListMap”)
2. 接口的返回类型与 resultMapper中映射的 type类型相一致; (即 Mapper.java中方法的返回类型与下方 < resultMap type=“user” id=“userListMap”>中 type值相同))
① select u_id,u_username,u_age from …运行报错,本身 sql语句出错,因为数据表中没有这些字段;
② select id,username,age u_age from …(即 age别名)运行不报错,但查到的 age值不是表中的值,age为 0;
(所以 resultType查询时,sql查询的列名与 pojo类的属性一致)
③ select id u_id,username u_username,age u_age from … (所有的别名全部都与 pojo类属性不一致)运行不报错,但不创建 pojo对象。
<!-- resultType -->
<select id="findUserList" parameterType="userQueryVo" resultType="user">
select id,username,age from Users where username like '%${userCustom.username}%'
</select>
<!-- resultMap -->
<resultMap id="userListMap" type="user">
<id colum="u_id" property="id"/> //主键
<result colum="u_username property="username"/> //普通列
<result colum="u_age property="age"/>
</resultMap>
<select id="findUserList" parameterType="userQueryVo" resultMap="userListMap">
select id u_id,username u_username ,age u_age from Users where username like '%${userCustom.username}%'
</select>
4. 动态 SQL:where if和 sql片段(sql if)
需求:自定义条件查询用户信息
where和 if:
where 标签相当于 where关键字,可以自动去除第一个 and;
if 相当于判断,test=“判断条件”;
Sql片段:
通用的 sql片段抽取出来,单独定义,方便其他的 statement引用;
通过 sql语句,where条件(即下面的 where if片段);
(定义 sql片段是写在< sql>标签中,< where>标签不写在片段中;使用时:用< include refid=" ">)
<sql id="query_user_where">
<if test="userCustom!=null">
<if test="userCustom.username!=null and userCustom.username!='' ">
and username like'%${userCustom.username}%'
</if>
<if test="userCustom.age!=0">
and age>#{userCustom.age}
</if>
</if>
</sql>
<include refid="query_user_where"/>
userCustom不为空,userCustom.username不为空(即有 username这个查询条件)且不为空字符,userCustom.age不为0;下图 Test中:
① 如果只是:userCustom.setUsername(“张”);
运行时 sql语句:select id u_id,username u_uaername,age u_age from Users where username like ‘%张%’
② 如果是:userCustom.setUsername(“张”);
userCustom.setAge(10);
运行时 sql语句:select id u_id,username u_uaername,age u_age from Users where username like ’ %$张% ’ and age>?
查询名字、年龄时与查询用户个数时的 where if条件片段一样,可以将其提取出来,封装成 sql片段,进行多次利用:
where 条件抽取:< sql>标签是用来定义 sql语句的,id 为其标识,where 条件(即提取出来的 sql片段)中有基于用户的、下面< if>其他条件< /if>可能基于订单等其他的,并不是把下面< where>片段中的< if>都抽取出来,可以分开抽取,建议对单表抽取,提高公用性。
UserMapper.xml (提取 sql片段后)
<mapper namespace="com.mdd.mapper.UserMapper">
<!-- where条件抽取(建议对单表抽取,提高公用性)-->
<sql id="query_user_where">
<if test="userCustom!=null">
<if test="userCustom.username!=null and userCustom.username!='' ">
and username like'%${userCustom.username}%'
</if>
<if test="userCustom.age!=0">
and age>#{userCustom.age}
</if>
</if>
</sql>
<!-- 查询用户名字、年龄 -->
<resultMap id="userListMap" type="user">
<id column="u_id" property="id"/> //主键
<result column="u_username" property="username"/> //普通列
<result column="u_age" property="age"/>
</resultMap>
<select id="findUserList" parameterType="userQueryVo" resultMap="userListMap">
//select id u_id,username u_uaername,age u_age from Users where username like '%${userCustom.username}%'
select id u_id,username u_uaername,age u_age from Users
<where>
<include refid="query_user_where"/>
<include refid="其他条件"/>
</where>
</select>
<!-- 查询个数 -->
<select id="findUserCount" parameterType="userQueryVo" resultType="int">
//select count(*) from Users where username like '%${userCustom.username}%'
select count(*) from Users
<where>
<include refid="query_user_where"/>
</where>
</select>
</mapper>
UserMapper.xml (提取 sql片段前)
<mapper namespace="com.mdd.mapper.UserMapper">
<!-- 查询用户名字、年龄 -->
<resultMap id="userListMap" type="user">
<id column="u_id" property="id"/> //主键
<result column="u_username" property="username"/> //普通列
<result column="u_age" property="age"/>
</resultMap>
<select id="findUserList" parameterType="userQueryVo" resultMap="userListMap">
//select id u_id,username u_uaername,age u_age from Users where username like '%${userCustom.username}%'
select id u_id,username u_uaername,age u_age from Users
<where>
<if test="userCustom!=null">
<if test="userCustom.username!=null and userCustom.username!='' ">
and username like'%${userCustom.username}%'
</if>
<if test="userCustom.age!=0">
and age>#{userCustom.age}
</if>
<if>
//其他if条件
</if>
</if>
</where>
</select>
<!-- 查询个数 -->
<select id="findUserCount" parameterType="userQueryVo" resultType="int">
//select count(*) from Users where username like '%${userCustom.username}%'
select count(*) from Users
<where>
<if test="userCustom!=null">
<if test="userCustom.username!=null and userCustom.username!='' ">
and username like'%${userCustom.username}%'
</if>
<if test="userCustom.age!=0">
and age>#{userCustom.age}
</if>
<if>
//其他if条件
</if>
</if>
</where>
</select>
</mapper>
AppTest.java
@Test
public void testFindUserList() throws Exception(){
SqlSession sqlSession=sqlSessionFactory.openSession();
UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
UserQueryVo userQueryVo=new UserQueryVo();
UserCustom userCustom=new UserCustom();
userCustom.setUsername("张");
//userCustom.setAge(10);
userQueryVo.setUserCustom(userCustom);
List<User> users=userMapper.findUserList(userQueryVo);
sqlSession.close();
for (User user:users) {
System.out.println(user.getId()+","+user.getUsername());
}
}
5. sql片段(foreach的使用)
需求:根据多个用户 id查询用户信息
select * from Users where age>10 and id in(1,3,5,7) 或
select * from Users where age>10 and (id=1 or id=3 or id=5 or id=7)
(即在 statement中 parameterType传集合)
解决:在包装类中定义一个集合(使用< foreach>标签进行遍历)。
① 包装类中定义 ids集合;② UserMapper.xml 添加< foreach>标签:collection:集合属性; open:开始循环时要拼接的字符串; close:结束循环时的字符串; item:每次循环的对象; seperator:分隔符。
③ AppTest.java中创建一个 ids集合,再set进去。运行的sql语句:
select id u_id,username u_uaername,age u_age from Users where username like ‘%张%’ and id in (?,?,?)
select id u_id,username u_uaername,age u_age from Users where username like ‘%张%’ and (id=? or id=? or id=?)
<!-- select * from Users where age>10 and id in(1,3,5,7) -->
<foreach collection="ids" open="and id in(" close=")" item="id" seperator=",">
#{id}
</foreach>
<!-- select * from Users where age>10 and (id=1 or id=3 or id=5 or id=7) -->
<foreach collection="ids" open="and(" close=")" item="id" seperator="or">
#{id}
</foreach>
UserQueryVo.java
public class UserQueryVo {
private User user; //用户信息
private UserCustom userCustom; //自定义User扩展对象
private List<Integer> ids; //定义集合
set()、get()
}
UserMapper.xml (提取 sql片段后 + foreach遍历)
<mapper namespace="com.mdd.mapper.UserMapper">
<!-- 【sql片段】 -->
<!-- where条件抽取(建议对单表抽取,提高公用性)-->
<sql id="query_user_where">
<if test="userCustom!=null">
<if test="userCustom.username!=null and userCustom.username!='' ">
and username like'%${userCustom.username}%'
</if>
<if test="userCustom.age!=0">
and age>#{userCustom.age}
</if>
</if>
<!-- 【foreach遍历】(eg:遍历指定的id) -->
<!-- select * from Users where age>10 and id in(1,3,5,7) -->
<foreach collection="ids" open="and id in(" close=")" seperator=",">
#{id}
</foreach>
<!-- select * from Users where age>10 and (id=1 or id=3 or id=5 or id=7) -->
<foreach collection="ids" open="and(" item="id" seperator="or">
#{id}
</foreach>
</sql>
<!-- 查询用户名字、年龄 -->
<resultMap id="userListMap" type="user">
<id column="u_id" property="id"/> //主键
<result column="u_username" property="username"/> //普通列
<result column="u_age" property="age"/>
</resultMap>
<select id="findUserList" parameterType="userQueryVo" resultMap="userListMap">
//select id u_id,username u_uaername,age u_age from Users where username like '%${userCustom.username}%'
select id u_id,username u_uaername,age u_age from Users
<where>
<include refid="query_user_where"/>
<include refid="其他条件"/>
</where>
</select>
<!-- 查询个数 -->
<select id="findUserCount" parameterType="userQueryVo" resultType="int">
//select count(*) from Users where username like '%${userCustom.username}%'
select count(*) from Users
<where>
<include refid="query_user_where"/>
</where>
</select>
</mapper>
AppTest.java
@Test
public void testFindUserList() throws Exception(){
SqlSession sqlSession=sqlSessionFactory.openSession();
UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
UserQueryVo userQueryVo=new UserQueryVo();
List<Integer> ids=new ArrayList<>();
ids.add(1); ids.add(3); ids.add(5);
UserCustom userCustom=new UserCustom();
userCustom.setUsername("张");
userQueryVo.setUserCustom(userCustom);
userQueryVo.setIds(ids);
List<User> users=userMapper.findUserList(userQueryVo);
sqlSession.close();
for (User user:users) {
System.out.println(user.getId()+","+user.getUsername());
}
}
(首页网址: https://blog.csdn.net/qq_41029923/article/details/83472411 )