【mybatis动态sql】

动态SQL

1. 基于OGNL表达式
2. 完成多条件查询等逻辑实现
3. 用于实现动态SQL的元素主要有
   • if
   • trim
   • where
   • set
   • choose（when、otherwise）
   • foreach

if

• 改造查询用户信息列表的演示示例，增加查询条件 用户角色（根据角色id查询） 用户名称（模糊查询）

<!-- 当数据库中的字段信息与对象的属性不一致时需要通过resultMap来映射 -->

<resultMap type="User" id="userList">

<result property="id" column="id"/>

<result property="userCode" column="userCode"/>

<result property="userName" column="userName"/>

<result property="phone" column="phone"/>

<result property="birthday" column="birthday"/>

<result property="gender" column="gender"/>

<result property="userRole" column="userRole"/>

<result property="userRoleName" column="roleName"/>

</resultMap>

<!-- 查询用户列表 -->

<select id="getUserList" resultMap="userList">

select u.*,r.roleName from smbms_user u,smbms_role r where u.userRole = r.id

<if test="userRole != null">

and u.userRole = #{userRole}

</if>

<if test="userName != null and userName != ''">

and u.userName like CONCAT ('%',#{userName},'%')

</if>

</select>

where

1. 简化SQL语句中where条件判断
2. 智能处理and和or

<select id="getUserList" resultType="User">

select * from smbms_user

<where>

<if test="userName != null and userName != ''">

and userName like CONCAT ('%',#{userName},'%')

</if>

<if test="userRole != null">

and userRole = #{userRole}

</if>

</where>

</select>

where:自动加where,自动去掉第一个and

set

• 若某个参数为null，则不需要更新，保持数据库原值

<!-- 修改用户信息 -->

<update id="modify" parameterType="User">

update smbms_user

<set>

<if test="userCode != null">userCode=#{userCode},</if>

<if test="userName != null">userName=#{userName},</if>

<if test="userPassword != null">userPassword=#{userPassword},</if>

<if test="gender != null">gender=#{gender},</if>

<if test="birthday != null">birthday=#{birthday},</if>

<if test="phone != null">phone=#{phone},</if>

<if test="address != null">address=#{address},</if>

<if test="userRole != null">userRole=#{userRole},</if>

<if test="modifyBy != null">modifyBy=#{modifyBy},</if>

<if test="modifyDate != null">modifyDate=#{modifyDate}</if>

</set>

where id = #{id}

</update>

trim

1. 属性 prefix suffix prefixOverrides suffixOverrides
2. 更灵活地去除多余关键字
3. 替代where和set

<select id="getUserList" resultType="User">

select * from smbms_user

<trim prefix="where" prefixOverrides="and | or">

<if test="userName != null and userName != ''">

and userName like CONCAT ('%',#{userName},'%')

</if>

<if test="userRole != null">

and userRole = #{userRole}

</if>

</trim>

</select>

使用if+trim替代if+set进行更新用户表数据

<update id ="modify" parameterType="User">

update smbms_user

<trim prefix="set" suffixOverrides="," suffix="where id = #{id}">

<if test="userCode != null">userCode = #{userCode},</if>

<if test="userName!= null">userCode = #{userName },</if>

<if test="userPassword!= null">userPassword=#{userPassword },</if>

</trim>

</update>

foreach

1. 迭代一个集合，通常用于in条件
2. 属性
   • item
   • index
   • collection:必须指定
     • list
     • array
     • map-key
     • open
     • separator
     • close

public List<User> getUserByRoleId_foreach_array(Integer[] roleIds);

<!-- 根据用户角色列表，获取该角色列表下用户列表信息-foreach_array -->

<select id="getUserByRoleId_foreach_array" resultMap="userMapByRole">

select * from smbms_user where userRole in

<foreach collection="array" item="roleIds" open="(" separator="," close=")">

#{roleIds}

</foreach>

</select>

• 改造上一个演示示例，使用foreach实现，参数类型改为List

public List<User> getUserByRoleId_foreach_list(List<Integer> roleList);

<!-- 根据用户角色列表，获取该角色列表下用户列表信息-foreach_list -->

<select id="getUserByRoleId_foreach_list" resultMap="userMapByRole">

select * from smbms_user where userRole in

<foreach collection="list" item="roleList" open="(" separator="," close=")">

#{roleList}

</foreach>

</select>

• 在上一个演示示例中，增加一个参数：gender，要求查询出指定性别和用户角色列表下的用户列表

public List<User> getUserByRoleId_foreach_map(Map<String,Object> roleMap);

<!-- 根据用户角色列表(单参数)，获取该角色列表下用户列表信息-foreach_map -->

<select id="getUserByRoleId_foreach_map" resultMap="userMapByRole">

select * from smbms_user where userRole in

<foreach collection="rKey" item="roleMap" open="(" separator="," close=")">

#{roleMap}

</foreach>

</select>

choose

1. 相当于Java中switch语句
2. 当when有条件满足的时候，就跳出choose

<!-- 查询用户列表(choose) -->

<select id="getUserList_choose" resultType="User">

select * from smbms_user where 1=1

<choose>

<when test="userName != null and userName != ''">

and userName like CONCAT ('%',#{userName},'%')

</when>

<when test="userCode != null and userCode != ''">

and userCode like CONCAT ('%',#{userCode},'%')

</when>

<when test="userRole != null">

and userRole=#{userRole}

</when>

<otherwise>

<!-- and YEAR(creationDate) = YEAR(NOW()) -->

and YEAR(creationDate) = YEAR(#{creationDate})

</otherwise>

</choose>

</select>

日志

pom.xml导入依赖

<properties>

<log4j2.version>2.9.1</log4j2.version>

</properties>

<!--日志 -->

<dependency>

<groupId>org.apache.logging.log4j</groupId>

<artifactId>log4j-api</artifactId>

<version>${log4j2.version}</version>

</dependency>

<dependency>

<groupId>org.apache.logging.log4j</groupId>

<artifactId>log4j-core</artifactId>

<version>${log4j2.version}</version>

</dependency>

配置文件

log4j2.xml

分页

• 为用户管理之查询用户列表功能增加分页实现 列表结果按照创建时间降序排列

添加分页jar

<!--pageHelper分页-->

<dependency>

<groupId>com.github.pagehelper</groupId>

<artifactId>pagehelper</artifactId>

<version>5.1.4</version>

</dependency>

配置

在myatis配置文件中添加如下配置

<plugins>

<plugin interceptor="com.github.pagehelper.PageInterceptor">

<!-- 设置数据库类型 Oracle,Mysql,MariaDB,SQLite,Hsqldb,PostgreSQL六种数据库-->

<property name="helperDialect" value="mysql"/>

</plugin>

</plugins>

使用测试

@Test

public void testGetUserList(){

SqlSession sqlSession = MyBatisUtil.createSqlSession();

//startPage(第几页,每页显示几条记录)

PageHelper.startPage(2,5);

UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

List<User> list = userMapper.getUserList();

PageInfo<User> pageInfo = new PageInfo<>(list);

sqlSession.close();

for(User user : list){

System.out.println(user.getId()+"\t"+user.getUserName());

}

System.out.println("总记录数="+pageInfo.getTotal());

System.out.println("总页数="+pageInfo.getPages());

}