今天学习了使用MyBatis框架对数据库内容进行条件查询、更新、复杂查询，对于基础环境的搭建，大家可以看我的另一篇文章。MyBatis环境搭建和基本的增删改查
https://blog.csdn.net/qq_49873907/article/details/129845464?spm=1001.2014.3001.5502
1.1 动态SQL的元素
sql的内容是变化的, 可以根据条件获取到不同的操作。
动态SQL语句标签包括以下标签：
| 元素 | 说明 |
| <if> | 判断语句，用于但条件判断 |
| <choose>(<when>、<otherwise>) | 相当于Java中的switch...case...default 语句，用于多条件判断 |
| <where> | 简化SQL语句中where的条件判断 |
| <trim> | 可以灵活地取出多余的关键字 |
| <set> | 用于SQL语句的动态更新 |
| <foreach> | 循环语句，常用语in语句等列举条件中 |
1.2 条件查询操作
1.1 <if>、<where>元素
1. 写接口
当查询的时候很有可能会返回多个结果，我们直接定义一个List类型，用来接受多个返回值，后面出现的接口也会这样定义。
List<User> findBynameorage2(User user);2.写sql语句
<if> 标签有一个test属性，用来定义查询条件
我们这里直接用了<where>语句，对于where语句，它会自动判断由组合条件拼装的sql语句，只有<where>元素内的某一个或多个条件成立时，才会在拼接SQL中加入关键字where，否则不会添加；即使where之后的内容有多余的“AND” 或 “OR”，<where>元素也会自动将他们去除。
<!--使用<where>标签-->
<select id="findBynameorage2" parameterType="com.houjinqiao.pojo.User" resultType="com.houjinqiao.pojo.User">
select * from users
<where>
<if test="uname != null and uname != ''">
and uname like concat('%',#{uname},'%')
</if>
<if test="uage != null and uage != ''">
and uage = #{uage}
</if>
</where>
</select>3.写测试类
//使用<where>标签
@Test
public void test07() throws IOException {
SqlSession sqlSession = MyBatisUtils.getSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = new User();
user.setUname("张三");
List<User> user1 = mapper.findBynameorage2(user);
System.out.print(user1);
sqlSession.commit();
sqlSession.close();
}
[DEBUG] [main] c.h.m.U.findBynameorage2 - ==> Preparing:
select * from users WHERE uname like concat('%',?,'%')
[DEBUG] [main] c.h.m.U.findBynameorage2 - ==> Parameters: 张三(String)
[DEBUG] [main] c.h.m.U.findBynameorage2 - <== Total: 2
[User{uid=1, uname='张三', uphone='13111111111', uage=20},User{uid=9, uname='张三', uphone='545645645', uage=22}]
这里我们的sql语句为select * from users WHERE uname like concat('%',?,'%') 查到两条结果
1.2<choose>(<when>、<otherwise>)元素
1.写接口
List<User> findBynameorage(User user);2.写SQL语句
<!-- choose when otherwise -->
<select id="findBynameorage" resultType="com.houjinqiao.pojo.User">
select * from users
<where>
<choose>
<when test="uname != null and uname != ''">
and uname like concat('%',#{uname},'%')
</when>
<when test="uage != null and uage != ''">
and uage = #{uage}
</when>
<otherwise>
and uphone is not null
</otherwise>
</choose>
</where>
</select>3.写测试类
这里分别测试传参数和不传参数的运行结果
1.给name or age 参数
//choose when otherwise
@Test
public void test06() throws IOException {
SqlSession sqlSession = MyBatisUtils.getSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = new User();
user.setUname("李四");
List<User> user1 = mapper.findBynameorage(user);
System.out.print(user1);
sqlSession.commit();
sqlSession.close();
}Preparing: select * from users WHERE uname like concat('%',?,'%')
[DEBUG] [main] c.h.m.U.findBynameorage - ==> Parameters: 李四(String)
[DEBUG] [main] c.h.m.U.findBynameorage - <== Total: 1
[User{uid=2, uname='李四', uphone='13211111111', uage=18}]
2.不给参数，执行 otherwise 中的语句
//choose when otherwise
@Test
public void test06() throws IOException {
SqlSession sqlSession = MyBatisUtils.getSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = new User();
//user.setUname("李四");
List<User> user1 = mapper.findBynameorage(user);
System.out.print(user1);
sqlSession.commit();
sqlSession.close();
}Preparing: select * from users WHERE uphone is not null
[DEBUG] [main] c.h.m.U.findBynameorage - ==> Parameters:
[DEBUG] [main] c.h.m.U.findBynameorage - <== Total: 5
[User{uid=1, uname='张三', uphone='13111111111', uage=20},User{uid=2, uname='李四', uphone='13211111111', uage=18},
User{uid=3, uname='梅西', uphone='13311111111', uage=20},
User{uid=9, uname='张三', uphone='545645645', uage=22},
User{uid=10, uname='侯晋悄', uphone='545645645', uage=20}]
可见，我们执行了uphone 不为空的sql语句select * from users WHERE uphone is not null
1.3 <trim>元素
| 属性 | 说明 |
| prefix | 指定给SQL语句增加的前缀 |
| prefixOverrides | 指定SQL语句中要去掉的前缀字符串 |
| suffix | 指定给SQL语句增加的后缀 |
| suffixOverrides | 指定SQL语句中要去掉的后缀字符串 |
1.写接口
List<User> findBynameorage3(User user);2.写SQL语句
<!--使用<trim>标签-->
<select id="findBynameorage3" parameterType="com.houjinqiao.pojo.User" resultType="com.houjinqiao.pojo.User">
select * from users
<trim prefix="where" prefixOverrides="and">
<if test="uname != null and uname != ''">
and uname like concat('%',#{uname},'%')
</if>
<if test="uage != null and uage != ''">
and uage = #{uage}
</if>
</trim>
</select>3.写测试类
//使用<trim>标签
@Test
public void test08() throws IOException {
SqlSession sqlSession = MyBatisUtils.getSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = new User();
user.setUname("张三");
List<User> user1 = mapper.findBynameorage3(user);
System.out.print(user1);
sqlSession.commit();
sqlSession.close();
}Preparing: select * from users where uname like concat('%',?,'%')
[DEBUG] [main] c.h.m.U.findBynameorage3 - ==> Parameters: 梅西(String)
[DEBUG] [main] c.h.m.U.findBynameorage3 - <== Total: 1
[User{uid=3, uname='梅西', uphone='13311111111', uage=20}][DEBUG]
1.3更新操作
1.1 <set>元素
<set>元素主要用于更新操们去除，它可以在动态SQL语句前输出一个关键字SET，并将SQL语句中最后一个多余的逗号去除使用<set>元素与<if>元素相结合的方式可以只更新需要更新的字段。
1.写接口
int updateUser2(User user);2.写SQL语句
<update id="updateUser2" parameterType="com.houjinqiao.pojo.User">
update users
<set>
<if test="uname != null and uname != ''">
uname = #{uname},
</if>
<if test="uage != null and uage != ''">
uage = #{uage},
</if>
</set>
where uid = #{uid}
</update>3.写测试类
//使用<set> 标签
@Test
public void test09() throws IOException {
SqlSession sqlSession = MyBatisUtils.getSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = new User();
user.setUname("内马尔");
user.setUage(31);
user.setUid(3);
mapper.updateUser2(user);
sqlSession.commit();
sqlSession.close();
}Preparing: update users SET uname = ?, uage = ? where uid = ?
[DEBUG] [main] c.h.m.U.updateUser2 - ==> Parameters: 内马尔(String), 31(Integer), 3(Integer)
[DEBUG] [main] c.h.m.U.updateUser2 - <== Updates: 1
1.4复杂查询操作
1.1 <foreach>元素
1.写接口
List<User> fore(int[] ids);2.写SQL语句
<select id="fore" parameterType="com.houjinqiao.pojo.User" resultType="com.houjinqiao.pojo.User">
select * from users where uid in
<foreach collection="array" item="uid" open="(" close=")" separator=",">
#{uid}
</foreach>
</select>3.写测试类
@Test
public void test10() throws IOException {
SqlSession sqlSession = MyBatisUtils.getSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
int[] ids = {1,2,3};
List<User> user1 = mapper.fore(ids);
System.out.print(user1);
sqlSession.close();
}Preparing: select * from users where uid in ( ? , ? , ? )
[DEBUG] [main] c.h.m.U.fore - ==> Parameters: 1(Integer), 2(Integer), 3(Integer)
[DEBUG] [main] c.h.m.U.fore - <== Total: 3
[User{uid=1, uname='张三', uphone='13111111111', uage=20}, User{uid=2, uname='李四', uphone='13211111111', uage=18}, User{uid=3, uname='内马尔', uphone='13311111111', uage=31}]
| 属性 | 说明 |
| item | 表示集合中每一个元素进行迭代时的别名，该属性为必选属性 |
| index | 在List和数组中，index是元素的序号；在Map中，index是元素的key。该属性为可选属性 |
| open | 表示foreach语句代码的开始符号，一般和close=“)”合用。常用在In条件语句中。该属性为可选属性 |
| separator | 表示元素之间的分隔符，例如，在条件语句中，separator=”,”会自动在元素中间用“,”隔开，避免手动输入逗号导致 SQL 错误，错误示例如 in(1,2,)。该属性为可选属性 |
| close | 表示foreach语句代码的关闭符号，一般和open=“(“合用。常用在in条件语句中。该属性为可选属性 |
| collection | 用于指定遍历参数的类型。注意，该属性必须指定。不同情况下该属性的值是不一样的，主要有以下3种情况。 若传入参数为单参数且参数类型是一个List，collection属性值为list。 若传入参数为单参数且参数类型是一个数组，collection属性值为array。 若传入参数为多参数，就需要把参数封装为一个Map进行处理，collection属性值为Map |
1.5 全部代码
1.接口
package com.houjinqiao.mapper;
import com.houjinqiao.pojo.User;
import javax.jws.soap.SOAPBinding;
import java.awt.*;
import java.util.List;
public interface UserMapper {
User findById(int a);
int addUser(User user);
int updateUser(User user);
int deleteUser(int a);
List<User> findBynameorage(User user);
List<User> findBynameorage2(User user);
List<User> findBynameorage3(User user);
int updateUser2(User user);
List<User> fore(int[] ids);
}
2.SQL语句
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.houjinqiao.mapper.UserMapper">
<select id="findById" parameterType="int" resultType="com.houjinqiao.pojo.User">
select * from users where uid = #{uid}
</select>
<!--对象中的属性，可以直接取出来-->
<insert id="addUser" parameterType="com.houjinqiao.pojo.User" keyProperty="uid" useGeneratedKeys="true">
insert into users(uid,uname, uage, uphone) values (#{uid},#{uname},#{uage},#{uphone});
</insert>
<update id="updateUser" parameterType="com.houjinqiao.pojo.User">
update users set uname = #{uname}, uphone = #{uphone},uage = #{uage} where uid = #{uid};
</update>
<delete id="deleteUser" parameterType="int">
delete from users where uid = #{uid};
</delete>
<!-- choose when otherwise -->
<select id="findBynameorage" resultType="com.houjinqiao.pojo.User">
select * from users
<where>
<choose>
<when test="uname != null and uname != ''">
and uname like concat('%',#{uname},'%')
</when>
<when test="uage != null and uage != ''">
and uage = #{uage}
</when>
<otherwise>
and uphone is not null
</otherwise>
</choose>
</where>
</select>
<!--使用<where>标签-->
<select id="findBynameorage2" parameterType="com.houjinqiao.pojo.User" resultType="com.houjinqiao.pojo.User">
select * from users
<where>
<if test="uname != null and uname != ''">
and uname like concat('%',#{uname},'%')
</if>
<if test="uage != null and uage != ''">
and uage = #{uage}
</if>
</where>
</select>
<!--使用<trim>标签-->
<select id="findBynameorage3" parameterType="com.houjinqiao.pojo.User" resultType="com.houjinqiao.pojo.User">
select * from users
<trim prefix="where" prefixOverrides="and">
<if test="uname != null and uname != ''">
and uname like concat('%',#{uname},'%')
</if>
<if test="uage != null and uage != ''">
and uage = #{uage}
</if>
</trim>
</select>
<update id="updateUser2" parameterType="com.houjinqiao.pojo.User">
update users
<set>
<if test="uname != null and uname != ''">
uname = #{uname},
</if>
<if test="uage != null and uage != ''">
uage = #{uage},
</if>
</set>
where uid = #{uid}
</update>
<select id="fore" parameterType="com.houjinqiao.pojo.User" resultType="com.houjinqiao.pojo.User">
select * from users where uid in
<foreach collection="array" item="uid" open="(" close=")" separator=",">
#{uid}
</foreach>
</select>
</mapper>3.测试类
import com.houjinqiao.mapper.UserMapper;
import com.houjinqiao.pojo.User;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import com.houjinqiao.utils.MyBatisUtils;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import java.util.*;
import java.io.IOException;
import java.io.Reader;
public class UserTest {
/*
SqlSession sqlSession;
UserMapper mapper;
@Before
public void before() throws IOException {
SqlSession sqlSession = MyBatisUtils.getSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
}
@After
public void after(){
sqlSession.commit();
sqlSession.close();
}
*/
@Test
public void test01() throws IOException {
SqlSession sqlSession = MyBatisUtils.getSession();
User user = sqlSession.selectOne("findById", 1);
System.out.printf(user.getUname());
sqlSession.close();
}
@Test
public void test02() throws IOException {
SqlSession sqlSession = MyBatisUtils.getSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = mapper.findById(7);
System.out.print(user);
sqlSession.close();
}
@Test
public void test03() throws IOException {
SqlSession sqlSession = MyBatisUtils.getSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
mapper.addUser(new User(7,"得等到","545645645",22));
sqlSession.commit();
sqlSession.close();
}
@Test
public void test04() throws IOException {
SqlSession sqlSession = MyBatisUtils.getSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
mapper.updateUser(new User(7,"梅西","8888888",34));
sqlSession.commit();
sqlSession.close();
}
@Test
public void test05() throws IOException {
SqlSession sqlSession = MyBatisUtils.getSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
mapper.deleteUser(7);
sqlSession.commit();
sqlSession.close();
}
//choose when otherwise
@Test
public void test06() throws IOException {
SqlSession sqlSession = MyBatisUtils.getSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = new User();
//user.setUname("李四");
List<User> user1 = mapper.findBynameorage(user);
System.out.print(user1);
sqlSession.commit();
sqlSession.close();
}
//使用<where>标签
@Test
public void test07() throws IOException {
SqlSession sqlSession = MyBatisUtils.getSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = new User();
user.setUname("张三");
List<User> user1 = mapper.findBynameorage2(user);
System.out.print(user1);
sqlSession.commit();
sqlSession.close();
}
//使用<trim>标签
@Test
public void test08() throws IOException {
SqlSession sqlSession = MyBatisUtils.getSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = new User();
user.setUname("梅西");
List<User> user1 = mapper.findBynameorage3(user);
System.out.print(user1);
sqlSession.commit();
sqlSession.close();
}
//使用<set> 标签
@Test
public void test09() throws IOException {
SqlSession sqlSession = MyBatisUtils.getSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = new User();
user.setUname("内马尔");
user.setUage(31);
user.setUid(3);
mapper.updateUser2(user);
sqlSession.commit();
sqlSession.close();
}
@Test
public void test10() throws IOException {
SqlSession sqlSession = MyBatisUtils.getSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
int[] ids = {1,2,3};
List<User> user1 = mapper.fore(ids);
System.out.print(user1);
sqlSession.close();
}
}
3万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
