mybatis的优势之一就是动态sql.
-.动态sql的主要元素有if,where,trim,set,choose(when,otherwise),foreach下面一一介绍:
数据库的脚本:
CREATE TABLE `smbms_user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`userCode` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '用户编码',
`userName` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '用户名称',
`userPassword` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '用户密码',
`gender` int(10) DEFAULT NULL COMMENT '性别(1:女、 2:男)',
`birthday` date DEFAULT NULL COMMENT '出生日期',
`phone` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '手机',
`address` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '地址',
`userRole` int(10) DEFAULT NULL COMMENT '用户角色(取自角色表-角色id)',
`createdBy` bigint(20) DEFAULT NULL COMMENT '创建者(userId)',
`creationDate` datetime DEFAULT NULL COMMENT '创建时间',
`modifyBy` bigint(20) DEFAULT NULL COMMENT '更新者(userId)',
`modifyDate` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*Data for the table `smbms_user` */
insert into `smbms_user`(`id`,`userCode`,`userName`,`userPassword`,`gender`,`birthday`,`phone`,`address`,`userRole`,`createdBy`,`creationDate`,`modifyBy`,`modifyDate`)
values (1,'admin','系统管理员','1234567',1,'1983-10-10','13688889999','北京市海淀区成府路207号',1,1,'2013-03-21 16:52:07',NULL,NULL),
(2,'liming','李明','0000000',2,'1983-12-10','13688884457','北京市东城区前门东大街9号',2,1,'0000-00-00 00:00:00',NULL,NULL),
(5,'hanlubiao','韩路彪','0000000',2,'1984-06-05','18567542321','北京市朝阳区北辰中心12号',2,1,'2014-12-31 19:52:09',NULL,NULL),
(6,'zhanghua','张华','0000000',1,'1983-06-15','13544561111','北京市海淀区学院路61号',3,1,'2013-02-11 10:51:17',NULL,NULL)
实体类:
package cn.smbms.pojo;
import java.util.Date;
import java.util.List;
public class User {
private Integer id; //id
private String userCode; //用户编码
private String userName; //用户名称
private String userPassword; //用户密码
private Integer gender; //性别
private Date birthday; //出生日期
private String phone; //电话
private String address; //地址
private Integer userRole; //用户角色ID
private Integer createdBy; //创建者
private Date creationDate; //创建时间
private Integer modifyBy; //更新者
private Date modifyDate; //更新时间
private Integer age;//年龄
private String userRoleName; //用户角色名称
public Integer getAge() {
/*long time = System.currentTimeMillis()-birthday.getTime();
Integer age = Long.valueOf(time/365/24/60/60/1000).IntegerValue();*/
Date date = new Date();
Integer age = date.getYear()-birthday.getYear();
return age;
}
public String getUserRoleName() {
return userRoleName;
}
public void setUserRoleName(String userRoleName) {
this.userRoleName = userRoleName;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUserCode() {
return userCode;
}
public void setUserCode(String userCode) {
this.userCode = userCode;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getUserPassword() {
return userPassword;
}
public void setUserPassword(String userPassword) {
this.userPassword = userPassword;
}
public Integer getGender() {
return gender;
}
public void setGender(Integer gender) {
this.gender = gender;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public Integer getUserRole() {
return userRole;
}
public void setUserRole(Integer userRole) {
this.userRole = userRole;
}
public Integer getCreatedBy() {
return createdBy;
}
public void setCreatedBy(Integer createdBy) {
this.createdBy = createdBy;
}
public Date getCreationDate() {
return creationDate;
}
public void setCreationDate(Date creationDate) {
this.creationDate = creationDate;
}
public Integer getModifyBy() {
return modifyBy;
}
public void setModifyBy(Integer modifyBy) {
this.modifyBy = modifyBy;
}
public Date getModifyDate() {
return modifyDate;
}
public void setModifyDate(Date modifyDate) {
this.modifyDate = modifyDate;
}
}
mybatis-config.xml配置文件如下:
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<!-- 通过这个配置文件完成mybatis与数据库的连接 -->
<configuration>
<!-- 引入 database.properties 文件-->
<properties resource="database.properties"/>
<settings>
<!-- 配置mybatis的log实现为LOG4J -->
<setting name="logImpl" value="LOG4J" />
<!--
设置resultMap的自动映射级别为:
NONE
PARTIAL(默认)
FULL
-->
<!-- <setting name="autoMappingBehavior" value="FULL" /> -->
<!-- <setting name="autoMappingBehavior" value="NONE" /> -->
</settings>
<!--类型别名-->
<typeAliases>
<!-- <typeAlias alias="User" type="cn.smbms.pojo.User"/> -->
<package name="cn.smbms.pojo"/>
</typeAliases>
<environments default="development">
<environment id="development">
<!--配置事务管理,采用JDBC的事务管理 -->
<transactionManager type="JDBC"></transactionManager>
<!-- POOLED:mybatis自带的数据源,JNDI:基于tomcat的数据源 -->
<dataSource type="POOLED">
<property name="driver" value="${driver}"/>
<property name="url" value="${url}"/>
<property name="username" value="${user}"/>
<property name="password" value="${password}"/>
</dataSource>
</environment>
</environments>
<!-- 将mapper文件加入到配置文件中 -->
<mappers>
<mapper resource="cn/smbms/dao/user/UserMapper.xml"/>
</mappers>
</configuration>
UserMapper.java接口如下:
package cn.smbms.dao.user;
import java.util.Date;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.annotations.Param;
import cn.smbms.pojo.User;
public interface UserMapper {
/**
* 查询用户表记录数
* @return
*/
public int count();
/**
* 查询用户列表
* @param userName
* @param roleId
* @return
*/
public List<User> getUserList(@Param("userName")String userName,@Param("userRole")Integer roleId);
/**
* 增加用户
* @param user
* @return
*/
public int add(User user);
/**
* 修改用户信息
* @param user
* @return
*/
public int modify(User user);
/**
* 修改当前用户密码
* @param id
* @param pwd
* @return
*/
public int updatePwd(@Param("id")Integer id, @Param("userPassword")String pwd);
/**
* 根据userId删除用户信息
* @param delId
* @return
*/
public int deleteUserById(@Param("id")Integer delId);
/**
* 根据用户角色列表,获取该角色列表下用户列表信息-foreach_array
* @param roleIds
* @return
*/
public List<User> getUserByRoleId_foreach_array(Integer[] roleIds);
/**
* 根据用户角色列表,获取该角色列表下用户列表信息-foreach_list
* @param roleList
* @return
*/
public List<User> getUserByRoleId_foreach_list(List<Integer> roleList);
/**
* 根据用户角色列表和性别(多参数),获取该角色列表下指定性别的用户列表信息-foreach_map
* @param conditionMap
* @return
*/
public List<User> getUserByConditionMap_foreach_map(Map<String,Object> conditionMap);
/**
* 根据用户角色列表,获取该角色列表下用户列表信息-foreach_map(单参数封装成map)
* @param roleMap
* @return
*/
public List<User> getUserByRoleId_foreach_map(Map<String,Object> roleMap);
/**
* 查询用户列表(choose)
* @param userName
* @param roleId
* @param userCode
* @param creationDate
* @return
*/
public List<User> getUserList_choose(@Param("userName")String userName,
@Param("userRole")Integer roleId,
@Param("userCode")String userCode,
@Param("creationDate")Date creationDate);
}
UserMapper.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="cn.smbms.dao.user.UserMapper">
<!-- 查询用户表记录数 -->
<select id="count" resultType="int">
select count(1) as count from smbms_user
</select>
<!-- 当数据库中的字段信息与对象的属性不一致时需要通过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> -->
<!--
简单改造getUserList:
若 只传入一个参数:userRole,而不传入参数:userName的时候,控制台报sql异常错误
查看日志中sql语句:select * from smbms_user where and userRole = ?
若 不传入任何参数:控制台报sql异常错误
查看日志中sql语句:select * from smbms_user where
<select id="getUserList" resultType="User">
select * from smbms_user where
<if test="userName != null and userName != ''">
userName like CONCAT ('%',#{userName},'%')
</if>
<if test="userRole != null">
and userRole = #{userRole}
</if>
</select>
演示如何灵活处理where and|or :
-->
<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>
<!-- 增加用户 -->
<insert id="add" parameterType="User">
insert into smbms_user (userCode,userName,userPassword,gender,birthday,phone,
address,userRole,createdBy,creationDate)
values (#{userCode},#{userName},#{userPassword},#{gender},#{birthday},#{phone},
#{address},#{userRole},#{createdBy},#{creationDate})
</insert>
<!-- 修改用户信息 -->
<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">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>
</trim>
</update>
<!-- 修改当前用户密码 -->
<update id="updatePwd" parameterType="Integer">
update smbms_user set userPassword=#{userPassword} where id=#{id}
</update>
<!-- 根据userId删除用户信息 -->
<delete id="deleteUserById" parameterType="Integer">
delete from smbms_user where id=#{id}
</delete>
<!-- 根据用户角色列表,获取该角色列表下用户列表信息-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>
<resultMap type="User" id="userMapByRole">
<id property="id" column="id"/>
<result property="userCode" column="userCode"/>
<result property="userName" column="userName"/>
</resultMap>
<!-- 根据用户角色列表,获取该角色列表下用户列表信息-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>
<!-- 根据用户角色列表和性别(多参数),获取该角色列表下并指定性别的用户列表信息-foreach_map -->
<select id="getUserByConditionMap_foreach_map" resultMap="userMapByRole">
select * from smbms_user where gender = #{gender} and userRole in
<foreach collection="roleIds" item="roleMap" open="(" separator="," close=")">
#{roleMap}
</foreach>
</select>
<!-- 根据用户角色列表(单参数),获取该角色列表下用户列表信息-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) -->
<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>
</mapper>
测试类:
package cn.smbms.dao.user;
import static org.junit.Assert.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.session.SqlSession;
import org.apache.log4j.Logger;
import org.junit.Before;
import org.junit.Test;
import cn.smbms.pojo.Address;
import cn.smbms.pojo.User;
import cn.smbms.utils.MyBatisUtil;
public class UserMapperTest {
private Logger logger = Logger.getLogger(UserMapperTest.class);
@Before
public void setUp() throws Exception {
}
@Test
public void testCount() {
SqlSession sqlSession = null;
int count = 0;
try {
sqlSession = MyBatisUtil.createSqlSession();
//第一种方式:调用selectOne方法执行查询操作
//count = sqlSession.selectOne("cn.smbms.dao.user.UserMapper.count");
//第二种方式:调用getMapper(Mapper.class)执行dao接口方法来实现对数据库的查询操作
count = sqlSession.getMapper(UserMapper.class).count();
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}finally{
MyBatisUtil.closeSqlSession(sqlSession);
}
logger.debug("UserDaoTest count---> " + count);
}
@Test
public void testGetUserList(){
SqlSession sqlSession = null;
List<User> userList = new ArrayList<User>();
try {
sqlSession = MyBatisUtil.createSqlSession();
String userName = "";
Integer roleId = 3;
userList = sqlSession.getMapper(UserMapper.class).getUserList(userName,roleId);
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}finally{
MyBatisUtil.closeSqlSession(sqlSession);
}
logger.debug("userlist.size ----> " + userList.size());
for(User user: userList){
logger.debug("testGetUserList=======> id: " + user.getId() +
" and userCode: " + user.getUserCode() +
" and userName: " + user.getUserName() +
" and userRole: " + user.getUserRole() +
" and userRoleName: " + user.getUserRoleName() +
" and age: " + user.getAge() +
" and phone: " + user.getPhone() +
" and gender: " + user.getGender());
}
}
@Test
public void testAdd(){
logger.debug("testAdd !===================");
SqlSession sqlSession = null;
int count = 0;
try {
sqlSession = MyBatisUtil.createSqlSession();
User user = new User();
user.setUserCode("test001");
user.setUserName("测试用户001");
user.setUserPassword("1234567");
Date birthday = new SimpleDateFormat("yyyy-MM-dd").parse("1984-12-12");
user.setBirthday(birthday);
user.setCreationDate(new Date());
user.setAddress("地址测试");
user.setGender(1);
user.setPhone("13688783697");
user.setUserRole(1);
user.setCreatedBy(1);
user.setCreationDate(new Date());
count = sqlSession.getMapper(UserMapper.class).add(user);
//模拟异常,进行回滚
//int i = 2/0;
sqlSession.commit();
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
sqlSession.rollback();
count = 0;
}finally{
MyBatisUtil.closeSqlSession(sqlSession);
}
logger.debug("testAdd count: " + count);
}
@Test
public void testModify(){
logger.debug("testModify !===================");
SqlSession sqlSession = null;
int count = 0;
try {
User user = new User();
user.setId(16);
//user.setUserCode("testmodify");
//user.setUserName("测试用户修改");
//user.setUserPassword("0000000");
//Date birthday = new SimpleDateFormat("yyyy-MM-dd").parse("1980-10-10");
//user.setBirthday(birthday);
//user.setAddress("地址测试修改");
//user.setGender(2);
//user.setPhone("13600002222");
//user.setUserRole(2);
user.setModifyBy(1);
user.setModifyDate(new Date());
sqlSession = MyBatisUtil.createSqlSession();
count = sqlSession.getMapper(UserMapper.class).modify(user);
//模拟异常,进行回滚
//int i = 2/0;
sqlSession.commit();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
sqlSession.rollback();
count = 0;
}finally{
MyBatisUtil.closeSqlSession(sqlSession);
}
logger.debug("testModify count: " + count);
}
@Test
public void testUpdatePwd() {
logger.debug("testUpdatePwd !===================");
SqlSession sqlSession = null;
String pwd = "8888888";
Integer id = 1;
int count = 0;
try {
sqlSession = MyBatisUtil.createSqlSession();
count = sqlSession.getMapper(UserMapper.class).updatePwd(id, pwd);
sqlSession.commit();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
sqlSession.rollback();
count = 0;
}finally{
MyBatisUtil.closeSqlSession(sqlSession);
}
logger.debug("testUpdatePwd count: " + count);
}
@Test
public void testDeleteUserById() {
logger.debug("testDeleteUserById !===================");
SqlSession sqlSession = null;
Integer delId = 25;
int count = 0;
try {
sqlSession = MyBatisUtil.createSqlSession();
count = sqlSession.getMapper(UserMapper.class).deleteUserById(delId);
sqlSession.commit();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
sqlSession.rollback();
count = 0;
}finally{
MyBatisUtil.closeSqlSession(sqlSession);
}
logger.debug("testDeleteUserById count: " + count);
}
@Test
public void testGetUserByRoleId_foreach_array(){
SqlSession sqlSession = null;
List<User> userList = new ArrayList<User>();
Integer[] roleIds = {2,3};
try {
sqlSession = MyBatisUtil.createSqlSession();
userList = sqlSession.getMapper(UserMapper.class).getUserByRoleId_foreach_array(roleIds);
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}finally{
MyBatisUtil.closeSqlSession(sqlSession);
}
logger.debug("userList.size ----> " + userList.size());
for(User user : userList){
logger.debug("user ===========> id: " + user.getId()+
", userCode: " + user.getUserCode() +
", userName: " + user.getUserName() +
", userRole: " + user.getUserRole());
}
}
@Test
public void testGetUserByRoleId_foreach_list(){
SqlSession sqlSession = null;
List<User> userList = new ArrayList<User>();
List<Integer> roleList = new ArrayList<Integer>();
roleList.add(2);
roleList.add(3);
try {
sqlSession = MyBatisUtil.createSqlSession();
userList = sqlSession.getMapper(UserMapper.class).getUserByRoleId_foreach_list(roleList);
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}finally{
MyBatisUtil.closeSqlSession(sqlSession);
}
logger.debug("userList.size ----> " + userList.size());
for(User user : userList){
logger.debug("user ===========> id: " + user.getId()+
", userCode: " + user.getUserCode() +
", userName: " + user.getUserName() +
", userRole: " + user.getUserRole());
}
}
@Test
public void testGetUserByConditionMap_foreach_map(){
SqlSession sqlSession = null;
List<User> userList = new ArrayList<User>();
Map<String, Object> conditionMap = new HashMap<String,Object>();
List<Integer> roleList = new ArrayList<Integer>();
roleList.add(2);
roleList.add(3);
conditionMap.put("gender", 1);
conditionMap.put("roleIds",roleList);
try {
sqlSession = MyBatisUtil.createSqlSession();
userList = sqlSession.getMapper(UserMapper.class).getUserByConditionMap_foreach_map(conditionMap);
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}finally{
MyBatisUtil.closeSqlSession(sqlSession);
}
logger.debug("userList.size ----> " + userList.size());
for(User user : userList){
logger.debug("user ===========> id: " + user.getId()+
", userCode: " + user.getUserCode() +
", userName: " + user.getUserName() +
", gender: " + user.getGender() +
", userRole: " + user.getUserRole());
}
}
@Test
public void testGetUserByRoleId_foreach_map(){
SqlSession sqlSession = null;
List<User> userList = new ArrayList<User>();
List<Integer> roleList = new ArrayList<Integer>();
roleList.add(2);
roleList.add(3);
Map<String, Object> roleMap = new HashMap<String,Object>();
roleMap.put("rKey", roleList);
try {
sqlSession = MyBatisUtil.createSqlSession();
userList = sqlSession.getMapper(UserMapper.class).getUserByRoleId_foreach_map(roleMap);
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}finally{
MyBatisUtil.closeSqlSession(sqlSession);
}
logger.debug("userList.size ----> " + userList.size());
for(User user : userList){
logger.debug("user ===========> id: " + user.getId()+
", userCode: " + user.getUserCode() +
", userName: " + user.getUserName() +
", userRole: " + user.getUserRole());
}
}
@Test
public void testGetUserList_choose(){
SqlSession sqlSession = null;
List<User> userList = new ArrayList<User>();
try {
sqlSession = MyBatisUtil.createSqlSession();
String userName = "";
Integer roleId = null;
String userCode = "";
Date creationDate = new SimpleDateFormat("yyyy-MM-dd").parse("2017-01-01");
userList = sqlSession.getMapper(UserMapper.class).getUserList_choose(userName,roleId,userCode,creationDate);
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}finally{
MyBatisUtil.closeSqlSession(sqlSession);
}
logger.debug("userlist.size ----> " + userList.size());
for(User user: userList){
logger.debug("testGetUserList_choose=======> id: " + user.getId() +
" and userCode: " + user.getUserCode() +
" and userName: " + user.getUserName() +
" and userRole: " + user.getUserRole() +
" and creationDate: " + new SimpleDateFormat("yyyy-MM-dd").format(user.getCreationDate()));
}
}
}
以上就是mybatis的动态sql