文章目录
动态 SQL 是 MyBatis 的强大特性之一,使用动态 SQL 并非一件易事，但借助可用于任何 SQL 映射语句中的强大的动态 SQL 语言，MyBatis 显著地提升了这一特性的易用性。Mbatis-Plus封装了一些固定的单表的查询，对于一些复杂的关联还得使用sql进行查询 。
常见的标签有以下几种：
- if
- choose (when, otherwise)
- trim (where, set)
- foreach
1. if 标签
if标签可以对进行传入的参数进行判断.
<!--定义结果集-->
<resultMap id="baseResultMap" type="com.elite.mybatis.entity.Person">
<result column="id" property="id"/>
<result column="name" property="name"/>
<result column="age" property="age"/>
<result column="email" property="email"/>
</resultMap>
<!--定义sql片段-->
<sql id="selectList">
select id,name,age,email from person
</sql>
<!--if标签-->
<select id="selectPersonByName" resultMap="baseResultMap">
<include refid="selectList"></include>
where 1=1
<if test="name!=null and name!= ''">
and name like CONCAT('%',CONCAT(#{name},'%'))
</if>
</select>
测试
public class TestDynamicSql {
SqlSession sqlSession =null;
@Before
public void init() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
sqlSession = sqlSessionFactory.openSession();
}
/**
* 查询人员信息
*/
@Test
public void testIf() {
PersonMapper personMapper = sqlSession.getMapper(PersonMapper.class);
List<Person> personList = personMapper.selectPersonByName(null);
personList.forEach(p->{
System.out.println(p.toString());
});
PersonMapper personMapper1 = sqlSession.getMapper(PersonMapper.class);
List<Person> personList1 = personMapper.selectPersonByName("elite");
personList1.forEach(p->{
System.out.println(p.toString());
});
}
}
2.choose、when、otherwise
choose可以根据条件进行判断加上条件进行查询。
/**
* selectPersonByNameAndAge
*/
/**
* 查询人员信息
*/
@Test
public void testChoose() {
PersonMapper personMapper = sqlSession.getMapper(PersonMapper.class);
System.out.println("================传入名字按名字查询===============================");
List<Person> personList = personMapper.selectPersonByNameAndAge("elite",null);
personList.forEach(p -> {
System.out.println(p.toString());
});
System.out.println("==================传入年龄按年龄查询=============================");
List<Person> personList1 = personMapper.selectPersonByNameAndAge(null,24);
personList1.forEach(p -> {
System.out.println(p.toString());
});
System.out.println("===============都不传入按默认的id=1查询返回================================");
List<Person> personList2 = personMapper.selectPersonByNameAndAge(null,null);
personList2.forEach(p -> {
System.out.println(p.toString());
});
}
3. trim、where、set
前边拼接sql的地方可能一个条件都不加的情况下，我们需要写一个where 1=1才可以，这几个标签就可以解决这个问题。
<select id="selectPersonById" resultMap="baseResultMap">
<include refid="selectList"></include>
<where>
<if test="id != null and id!= '' ">
id = #{id}
</if>
</where>
</select>
测试代码
/**
* 查询人员信息
*/
@Test
public void testWhere() {
PersonMapper personMapper = sqlSession.getMapper(PersonMapper.class);
System.out.println("================不传入id===============================");
List<Person> personList = personMapper.selectPersonById(null);
personList.forEach(p -> {
System.out.println(p.toString());
});
System.out.println("================传入id===============================");
List<Person> personList1 = personMapper.selectPersonById(1L);
personList1.forEach(p -> {
System.out.println(p.toString());
});
}
测试set
<!--set标签-->
<update id="updatePersonById">
update person
<set>
<if test="name != null">name=#{name},</if>
<if test="age != null">age=#{age},</if>
<if test="email != null">email=#{email}</if>
</set>
where id=#{id}
</update>
测试
/**
* 测试更新
*/
@Test
public void testSet() {
PersonMapper personMapper = sqlSession.getMapper(PersonMapper.class);
Person p = new Person();
p.setId(1L);
p.setName("testset");
p.setEmail("testset@qq.com");
personMapper.updatePersonById(p);
System.out.println(personMapper.selectPerson(1L).toString());
//Person{id=1, name='testset', age=22, email='testset@qq.com'}
}
4. foreach
动态 SQL 的另一个常见使用场景是对集合进行遍历。
mapperxml
<!--foreach-->
<select id="selectPersonByIds" resultMap="baseResultMap">
<include refid="selectList"></include>
<where>
<foreach item="item" index="index" collection="ids"
open="id in (" separator="," close=")">
#{item}
</foreach>
</where>
</select>
测试
/**
* 测试更新
*/
@Test
public void testForeach() {
PersonMapper personMapper = sqlSession.getMapper(PersonMapper.class);
Long[] ids = new Long[]{1L,2L};
personMapper.selectPersonByIds(ids).forEach(person -> {
System.out.println(person.toString());
});
}
测试结果
Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@12b08d6]
==> Preparing: select id,name,age,email from person WHERE id in ( ? , ? )
==> Parameters: 1(Long), 2(Long)
<== Columns: id, name, age, email
<== Row: 1, elite, 22, elite@qq.com
<== Row: 2, elite2, 24, elite2@qq.com
<== Total: 2
Person{id=1, name='elite', age=22, email='elite@qq.com'}
Person{id=2, name='elite2', age=24, email='elite2@qq.com'}
