动态SQL解释
MyBatis动态SQL
1、单条件
1.1、版本1
① queryAllUserByUserName
@Test
public void queryAllUserByUserName() {
// 获取SqlSession
SqlSession session = DBFactory.getSqlSession();
// 得到mybatis代理接口mapper
UserMapper mapper = session.getMapper(UserMapper.class);
// 业务
//String userName = "唐僧";
List<User> userList = mapper.queryAllUserByUserName(userName);
// 遍历
for (User user2 : userList) {
System.out.println(user2);
}
}
② 2UserMapper
/** 查询:所有用户通过用户名称 */
public List<User> queryAllUserByUserName(String userName);
③ 3User.xml
<!-- 查询:所有用户通过用户名称 -->
<select id="queryAllUserByUserName" resultMap="user"
parameterType="java.lang.String">
select * from user where user_name = #{userName}
</select>
④ 4条件//String userName = “唐僧”;
控制打印SQL:
- ==> Preparing: select * from user where user_name = ?
- ==> Parameters: 唐僧(String)
- <== Total: 3
⑤ 5条件//String userName = null;
控制打印SQL:
- ==> Preparing: select * from user where user_name = ?
- ==> Parameters: null
- <== Total: 0
1.2、版本2
① UserMapper
/** 查询:所有用户通过用户名称 */
public List<User> queryAllUserByUserName2(@Param("userName") String userName);
② User.xml
<!-- 查询:所有用户通过用户名称 -->
<select id="queryAllUserByUserName2" resultMap="user"
parameterType="java.lang.String">
select * from user
<if test="userName!=null">
where user_name = #{userName}
</if>
</select>
③ 条件//String userName = “唐僧”;
控制打印SQL:
- ==> Preparing: select * from user where user_name = ?
- ==> Parameters: 唐僧(String)
- <== Total: 3
④ 条件//String userName = null;
控制打印SQL:
- ==> Preparing: select * from user
- ==> Parameters:
- <== Total: 17
2、多条件
2.1、和
① UserMapper
/** 查询:所有用户通过用户名称 :多条件 */
public List<User> queryAllUserByUserName3(User user);
② User.xml
<!-- 查询:所有用户通过用户名称 -->
<!-- 查询:所有用户通过用户名称 -->
<select id="queryAllUserByUserNameAndUserSex" parameterType="com.model.system.User"
resultMap="user">
select * from user where 1=1
<if test="userName!=null">
and user_name = #{userName}
</if>
<if test="userAge!=null">
and user_age = #{userAge}
</if>
</select>
③ queryAllUserByUserNameAndUserSex
@Test
public void queryAllUserByUserNameAndUserSex() {
// 获取SqlSession
SqlSession session = DBFactory.getSqlSession();
// 得到mybatis代理接口mapper
UserMapper mapper = session.getMapper(UserMapper.class);
// 业务
User user = new User();
user.setUserName("唐僧");
user.setUserAge(32);
List<User> userList = mapper.queryAllUserByUserNameAndUserSex(user);
// 遍历
for (User user2 : userList) {
System.out.println(user2);
}
}
④ 控制打印SQL:
- ==> Preparing: select * from user where 1=1 and user_name = ? and user_age = ?
- ==> Parameters: 唐僧(String), 32(Integer)
- <== Total: 2
2.2、或
① Mapper接口:UserMapper
/** 查询:所有用户通过用户名称 :多条件 */
public List<User> queryAllUserBy_OR_(User user);
② 映射文件:User.xml
```kotlin
<!-- 查询:所有用户通过用户名称 -->
<select id="queryAllUserBy_OR_" parameterType="com.model.system.User"
resultMap="user">
select * from user where 1=1
<choose>
<when test="userName!=null">
and user_name = #{userName}
</when>
<when test="userAge!=null">
and user_age like #{userAge}
</when>
</choose>
</select>
③ 测试类:queryAllUserBy
```java
@Test
public void queryAllUserBy_OR_() {
// 获取SqlSession
SqlSession session = DBFactory.getSqlSession();
// 得到mybatis代理接口mapper
UserMapper mapper = session.getMapper(UserMapper.class);
// 业务
User user = new User();
// user.setUserName("唐僧");
// user.setUserAge(66);
List<User> userList = mapper.queryAllUserByUserNameAndUserSex(user);
// 遍历
for (User user2 : userList) {
System.out.println(user2);
}
}
2.3模糊查询
① 接口:
public List queryUserLikeByUserName(String userName);
② 映射xml文件
<!-- 模糊查询 -->
<select id="queryUserLikeByUserName" parameterType="java.lang.String"
resultMap="user">
select * from user where user_name like #{userName}
</select>
③ 测试类:
/**
* <p>
* 查询:通过用户名
* </p>
*
* @author zlf
* @Date 2018年9月18日
*/
@Test
public void queryUserLikeByUserName7() {
SqlSession session = DBFactory.getSqlSession();
UserMapper mapper = session.getMapper(UserMapper.class);
String userName = "%精%";
List<User> userList = mapper.queryUserLikeByUserName(userName);
session.commit();
if (userList != null) {
for (User user : userList) {
System.out.println("------------------" + user);
}
}
session.close();
}
④ 控制台打印:
- ==> Preparing: select * from user where user_name like ?
- ==> Parameters: %精%(String)
- <== Total: 2
------------------User [userId=19, userName=蜘蛛精, userAge=32, userSex=女, userBrithday=Mon Sep 17 00:00:00 CST 2018]
------------------User [userId=27, userName=白骨精2, userAge=100, userSex=保密, userBrithday=Mon Sep 17 00:00:00 CST 2018]
2.4、模糊查询-映射文件中模糊匹配
① 接口:
public List queryUserLikeByUserName8(String userName);
② 映射xml文件
<!-- 模糊查询 -->
<select id="queryUserLikeByUserName8" parameterType="java.lang.String"
resultMap="user">
select * from user where user_name like '%${value}%' limit
0,2
</select>
③ 测试类:
@Test
public void queryUserLikeByUserName8() {
SqlSession session = DBFactory.getSqlSession();
UserMapper mapper = session.getMapper(UserMapper.class);
String userName = "花";
List<User> userList = mapper.queryUserLikeByUserName8(userName);
session.commit();
if (userList != null) {
for (User user : userList) {
System.out.println("------------------" + user);
}
}
session.close();
}
④ 控制台打印:
- ==> Preparing: select * from user where user_name like '%花%' limit 0,2
- ==> Parameters:
- <== Total: 2
------------------User [userId=29, userName=班花, userAge=32, userSex=保密, userBrithday=Tue Sep 18 00:00:00 CST 2018]
------------------User [userId=38, userName=菜花, userAge=null, userSex=男, userBrithday=Tue Sep 18 00:00:00 CST 2018]
2.5、添加返回ID[类型int]
① 接口:
public void addUserBackUser_id(User u);
② 映射xml文件
<!-- 添加用户:返回插入这条信息的id -->
<insert id="addUserBackUser_id" parameterType="com.model.system.User">
<selectKey keyProperty="userId" order="AFTER" resultType="int">
SELECT LAST_INSERT_ID()
</selectKey>
INSERT INTO user
(user_id,user_name,user_sex,user_age,user_brithday)
VALUES
(#{userId},#{userName},#{userSex},#{userAge},now())
</insert>
③ 测试类:
④ 控制台打印:
- ==> Preparing: INSERT INTO user (user_id,user_name,user_sex,user_age,user_brithday) VALUES (?,?,?,?,now())
- ==> Parameters: null, 菜花(String), 男(String), null
- <== Updates: 1
- ==> Preparing: SELECT LAST_INSERT_ID()
- ==> Parameters:
- <== Total: 1
返回用户ID:44
打印对象:User [userId=44, userName=菜花, userAge=null, userSex=男, userBrithday=Tue Sep 18 15:13:56 CST 2018]
2.6、添加返回ID[类型UUID]
① 接口:
public void addUserBackUser_id_uuid(User2 u);
② 映射xml文件
<!-- 添加用户:返回插入这条信息的uuid -->
<insert id="addUserBackUser_id_uuid" parameterType="com.model.system.User2">
<selectKey keyProperty="userId" order="BEFORE" resultType="String">
SELECT uuid()
</selectKey>
INSERT INTO user2
(user_id,user_name,user_sex,user_age,user_brithday)
VALUES
(#{userId},#{userName},#{userSex},#{userAge},now())
</insert>
③ 测试类:
@Test
public void addUserBackUser_id_uuid10() {
SqlSession session = DBFactory.getSqlSession();
UserMapper mapper = session.getMapper(UserMapper.class);
// 业务
// a、实例化领域模型
User2 u = new User2();
u.setUserName("菜花");
u.setUserSex("男");
u.setUserBrithday(new Date());
mapper.addUserBackUser_id_uuid(u);
session.commit();
session.close();
System.out.println("返回用户ID:" + u.getUserId());
System.out.println("打印对象:" + u);
}
④ 控制台打印:
- ==> Preparing: SELECT uuid()
- ==> Parameters:
- <== Total: 1
- ==> Preparing: INSERT INTO user2 (user_id,user_name,user_sex,user_age,user_brithday) VALUES (?,?,?,?,now())
- ==> Parameters: e4b79324-0c62-1037-bfe2-1de217cbb424(String), 菜花(String), 男(String), null
- <== Updates: 1
返回用户ID:e4b79324-0c62-1037-bfe2-1de217cbb424
打印对象:User2 [userId=e4b79324-0c62-1037-bfe2-1de217cbb424, userName=菜花, userAge=null, userSex=男, userBrithday=Tue Sep 18 15:16:08 CST 2018]
3、多条件
3.1、where元素
① 接口:
public List<User> getAlluser3(User user);
② 映射xml文件
<!-- 查询所有用户信息 -->
<select id="getAlluser3" parameterType="com.model.system.User"
resultMap="user">
select * from user
<where>
<if test="userName!=null">
and user_name = #{userName}
</if>
<if test="userAge!=null">
and user_age = #{userAge}
</if>
</where>
</select>
③ 测试类:
@Test
public void getAlluser3() {
SqlSession session = DBFactory.getSqlSession();
UserMapper mapper = session.getMapper(UserMapper.class);
User user = new User();
user.setUserName("唐僧");
user.setUserAge(66);
List<User> userList = mapper.getAlluser3(user);
for (User user2 : userList) {
System.out.println(user2);
}
}
④ 控制打印SQL:
- ==> Preparing: select * from user WHERE user_name = ? and user_age = ?
- ==> Parameters: 唐僧(String), 66(Integer)
- <== Total: 1
User [userId=26, userName=唐僧, userAge=66, userSex=女, userBrithday=Mon Sep 17 00:00:00 CST 2018]
3.2、trim元素
① 接口:
public List getAlluser4(User user);
② 映射xml文件
<!-- 查询所有用户信息 -->
<select id="getAlluser4" parameterType="com.model.system.User"
resultMap="user">
select * from user
<trim prefix="WHERE" prefixOverrides="AND |OR ">
<if test="userName!=null">
and user_name = #{userName}
</if>
<if test="userAge!=null">
and user_age = #{userAge}
</if>
</trim>
</select>
③ 测试类
@Test
public void getAlluser4() {
SqlSession session = DBFactory.getSqlSession();
UserMapper mapper = session.getMapper(UserMapper.class);
User user = new User();
user.setUserName("唐僧");
user.setUserAge(66);
List<User> userList = mapper.getAlluser3(user);
for (User user2 : userList) {
System.out.println(user2);
}
}
④ 控制打印SQL:
- ==> Preparing: select * from user WHERE user_name = ? and user_age = ?
- ==> Parameters: 唐僧(String), 66(Integer)
- <== Total: 1
User [userId=26, userName=唐僧, userAge=66, userSex=女, userBrithday=Mon Sep 17 00:00:00 CST 2018]
3.3、set元素
① 接口:
public int updateUser5(User user);
② 映射xml文件
<update id="updateUser5" parameterType="com.model.system.User">
update user
<set>
<if test="userName!=null"> user_name=#{userName},</if>
<if test="userAge!=null"> user_age=#{userAge},</if>
</set>
where user_id = #{userId}
</update>
③ 测试类
@Test
public void updateUser5() {
SqlSession session = DBFactory.getSqlSession();
UserMapper mapper = session.getMapper(UserMapper.class);
User user = new User();
user.setUserId(23);
user.setUserName("翠华翠华");
user.setUserAge(18);
int i = mapper.updateUser5(user);
session.commit();
if (i > 0) {
System.out.println("修改成功!");
}
session.close();
}
④ 控制打印SQL:
- ==> Preparing: update user SET user_name=?, user_age=? where user_id = ?
- ==> Parameters: 翠华翠华(String), 18(Integer), 23(Integer)
- <== Updates: 1
修改成功!