文章目录
1 输入映射和输出映射
1.1 parameterType(输入类型)
1.1.1 传递简单类型
参考之前的Mybatis博客
1.1.2 传递pojo对象
参考之前的Mybatis博客
1.1.3 传递pojo包装对象
- 新建包装pojo对象QueryVo
/**
* 包装pojo
* @author lpp
* @date 2020/3/1
*/
public class QueryVo {
//用户对象
private User user;
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
}
- 映射文件和sql语句
<!-- 1、resultType:如果要返回数据集合,只需设定为每一个元素的数据类型
2、 包装的pojo取值通过 "."来获取
-->
<select id="getUserByQueryVo" parameterType="queryvo" resultType="com.lpp.mybatis.pojo.User">
<!-- SELECT * FROM USER WHERE username LIKE #{name} -->
SELECT * FROM USER WHERE username LIKE #{user.username}
</select>
- 新增接口方法
在UserMapper接口里添加下面的方法
/**
* @Description: 传递包装pojo
* @Param: queryVo
* @return:
* @Author: LPP
* @Date:
*/
List<User> getUserByQueryVo(QueryVo vo);
- 增加测试方法,完成测试
在UserMapperTest.java中添加测试方法
@org.junit.jupiter.api.Test
void getUserByQueryVo() {
SqlSession sqlSession = SqlSessionFactoryUtils.getSqlSessionFactory().openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
QueryVo vo = new QueryVo();
User user = new User();
user.setUsername("%张%");
vo.setUser(user);
List<User> list = userMapper.getUserByQueryVo(vo);
for (User user1 : list) {
System.out.println(user1);
}
sqlSession.close();
}
- 测试结果截图
1.2 resultType(输出类型)
1.2.1 输出简单类型
- UserMapper.xml中添加sql语句
<!-- 查询总记录-->
<select id="getUserCount" resultType="int">
select count(*) from user
</select>
- 新增接口方法
/**
* @Description: 查询用户总记录数
* @Param:
* @return:
* @Author: LPP
* @Date:
*/
Integer getUserCount();
- 测试
@org.junit.jupiter.api.Test
void getUserCount() {
SqlSession sqlSession = SqlSessionFactoryUtils.getSqlSessionFactory().openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
Integer count = userMapper.getUserCount();
System.out.println("总记录数:"+count);
sqlSession.close();
}
- 测试结果
1.2.2 输出pojo对象(参考前面博客)
1.2.3 输出pojo列表(参考前面博客)
1.2.4 输出resultMap
使用order表的查询
<!-- ResultMap入门
id用于主键的映射
result用于普通字段映射-->
<resultMap id="order_list_map" type="order">
<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="getListOrderMap" resultMap="order_list_map">
select *
from `order`;
</select>
其它步骤跟前面类似,添加接口方法与测试方法,完成测试。
2 动态SQL
2.1 If
目标:完成用户列表查询功能,由多查询条件拼装引出if标签。
- 在UserMapper.xml中添加sql语句
<!-- 演示动态sql-if标签的使用 -->
<select id="getUserByPojo" parameterType="user" resultType="com.lpp.mybatis.pojo.User">
<!-- SELECT * FROM USER WHERE username LIKE '%${username}%' and id = #{id} -->
SELECT * FROM USER where 1 = 1
<!-- if标签的使用 -->
<if test="id != null">
and id = #{id}
</if>
<if test="username != null and username != ''">
and username LIKE #{username}
</if>
</select>
- 在UserMapper接口中添加接口方法
/**
* @Description: 演示if
* @Param: user
* @return:
* @Author: LPP
* @Date:
*/
List<User> getUserByPojo(User user);
- 在UserMapperTest类中添加测试代码
@org.junit.jupiter.api.Test
void getUserByPojo() {
SqlSession sqlSession = SqlSessionFactoryUtils.getSqlSessionFactory().openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
User user = new User();
user.setId(36);
user.setUsername("%蓝%");
List<User> list = userMapper.getUserByPojo(user);
for (User user1 : list) {
System.out.println(user1);
}
sqlSession.close();
}
- 测试结果
2.2 Where
- UserMapper.xml
<select id="getUserByPojo" parameterType="user" resultType="com.lpp.mybatis.pojo.User">
select
<include refid="user_sql"/>
from user
# // where标签自动补全where关键字,同时处理多余and
<where>
<if test="username !=null and username!=''">
and username like #{username}
</if>
<if test="sex!=null and sex!=''">
and sex=#{sex}
</if>
</where>
</select>
其他的不用添加,上面的if例子已经添加过了
- 测试代码
@org.junit.jupiter.api.Test
void getUserByPojo() {
SqlSession sqlSession = SqlSessionFactoryUtils.getSqlSessionFactory().openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
User user = new User();
user.setUsername("%张%");
user.setSex("1");
List<User> list = userMapper.getUserByPojo(user);
for (User user1 : list) {
System.out.println(user1);
}
sqlSession.close();
}
- 测试结果:
2.3 Foreach
- UserMapper.xml
<!--演示foreach-->
<select id="getUserByIds" parameterType="queryvo" resultType="user">
select
<include refid="user_sql"/>
from user
<where>
# foreach循环标签,collection要遍历的集合,目的,id in {1,25,29,30,35}
# open:循环开始执行前输出的内容
# item:设置循环变量
# separator:分隔符
# close:循环结束之后输出的内容
<foreach collection="ids" open="id in(" item="uId" separator="," close=")">
#{uId}
</foreach>
</where>
</select>
- UserMapper接口中添加如下方法
/**
* @Description: 演示foreach标签的使用 ,更具用户id列表查询用户
* @Param: vo
* @return:
* @Author: LPP
* @Date:
*/
List<User> getUserByIds(QueryVo vo);
- QueryVo.java类中添加如下代码
private List<Integer> ids;
public List<Integer> getIds() {
return ids;
}
public void setIds(List<Integer> ids) {
this.ids = ids;
}
- 测试
@org.junit.jupiter.api.Test
void getUserByIds() {
SqlSession sqlSession = SqlSessionFactoryUtils.getSqlSessionFactory().openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
QueryVo vo =new QueryVo();
//构建Id列表
vo.setIds(Arrays.asList(1,25,29,30,35));
List<User> list = userMapper.getUserByIds(vo);
for (User user1 : list) {
System.out.println(user1);
}
sqlSession.close();
}
- 测试结果
3 SQL片段
通过select * 不好引出查询字段名,抽取共用sql片段
- UserMapper.xml
<sql id="user_sql">
id,
username,
birthday,
sex,
address
</sql>
- 使用例子
<select id="getUserById" parameterType="int" resultType="user">
SELECT
<include refid="user_sql"/>
FROM user where id=#{id};
</select>
- 测试方法如上面一样。
Nothing is diffcult if you put your heart into it!