-
基于OGNL表达式
-
完成多条件查询等逻辑实现
-
用于实现动态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
-
简化SQL语句中where条件判断
-
智能处理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
-
属性 prefix suffix prefixOverrides suffixOverrides
-
更灵活地去除多余关键字
-
替代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
-
迭代一个集合,通常用于in条件
-
属性
-
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
-
相当于Java中switch语句
-
当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()); }