可以方便的在 sql 语句中实现某些逻辑. 总体说来mybatis 动态SQL 语句主要有以下几类:
1. if 语句 (简单的条件判断)
2. where (主要是用来简化sql语句中where条件判断的,能智能的处理 and or ,不必担心多余导致语法错误)
3. choose (when,otherwize) ,与 jstl 中的choose 很类似.满足一个条件后其他分支就不再执行
4. set (主要用于update语句),下面代码中没有示例但是用法差不多也很简单
5. trim (在自己包含的内容前加上某些前缀或者后缀) 与java中的trim要区分开 java中是去除字符两边的空格与制表符
6. foreach (在实现in 语句查询时特别有用)
新建博客表:
create table t_blog( id number, title varchar(100), content varchar(500), owner varchar(50) )
insert into t_blog values(1,'标题1','内容1','张三'); insert into t_blog values(2,'标题2','内容2','张三'); insert into t_blog values(3,'标题3','内容3','张三'); insert into t_blog values(4,'标题4','内容4','张三');
|
以下是项目中主要的四个源码文件
Blog.java
package com.lq.model;
public class Blog {
private Integer id;
private String title;
private String content;
private String owner;
public Blog() {
super();
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getContent() {
return content;
}
public void setContent(String content) {
this.content = content;
}
public String getOwner() {
return owner;
}
public void setOwner(String owner) {
this.owner = owner;
}
}
SqlMapConfig.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">
<configuration>
<!-- 设置别名 -->
<typeAliases>
<typeAlias type="com.lq.model.Blog" alias="blog"/>
</typeAliases>
<!-- 数据源配置 开发环境下设置为development 可以打印更多的日志信息 -->
<environments default="development">
<environment id="development">
<!-- 使用jdbc自带的事务管理器,进行简单的事务开启和提交 -->
<transactionManager type="JDBC" />
<!-- 使用jdbc自带的数据库连接池 -->
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://localhost:3306/test1" />
<property name="username" value="root" />
<property name="password" value="root" />
</dataSource>
</environment>
</environments>
<!-- 配置mapper映射文件 -->
<mappers>
<mapper resource="com/lq/model/BlogMapper.xml"/>
</mappers>
</configuration>
BlogMapper.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="com.lq.model.BlogMapper">
<!-- where if 语句使用 -->
<select id="queryBlog_if" parameterType="blog" resultType="blog">
select * from t_blog
<where>
<!-- 以下使用模糊查询的两种方式,#表示使用占位符传参;
$不使用占位符,直接将参数解析后拼接到SQL中,会引起SQL注入 -->
<if test="title!=null">
and title like '%'||#{title}||'%'
</if>
<if test="content!=null">
and content like '%${content}%'
</if>
<if test="owner!=null">
and owner like '%'||#{owner}||'%'
</if>
</where>
</select>
<!-- choose 语句使用 -->
<select id="queryBlog_choose" parameterType="blog" resultType="blog">
select * from t_blog
<where>
<choose>
<when test="title!=null">and title like '%'||#{title}||'%'</when>
<when test="content!=null">and content like '%${content}%'</when>
<otherwise>and owner = "张三" </otherwise>
</choose>
</where>
</select>
<!-- trim 语句使用 -->
<select id="queryBlog_trim" parameterType="blog" resultType="blog">
select * from t_blog
<trim prefix="where" prefixOverrides="and|or">
<if test="title!=null">
and title like '%'||#{title}||'%'
</if>
<if test="content!=null">
and content like '%${content}%'
</if>
<if test="owner!=null">
and owner like '%'||#{owner}||'%'
</if>
</trim>
</select>
<!-- foreach遍历list 语句使用 -->
<select id="query_foreach_list" parameterType="list" resultType="blog">
select * from t_blog where 1=1
<if test="list!=null">
and id in
<foreach collection="list" item="id" open="(" close=")" separator=",">#{id}</foreach>
</if>
</select>
<!-- foreach遍历数组 语句使用 -->
<select id="query_foreach_array" parameterType="int" resultType="blog">
select * from t_blog where 1=1
<if test="array!=null">
and id in
<foreach collection="array" item="id" open="(" close=")" separator=",">#{id}</foreach>
</if>
</select>
<!-- foreach遍历map 语句使用 -->
<!--
Map map = new HashMap();
map.put("owner", "张三");
map.put("array",array);
当传递map时 ,在sql中使用的都是map的key
-->
<select id="query_foreach_map" parameterType="map" resultType="blog">
select * from t_blog where 1=1
<if test="array!=null">
and id in
<foreach collection="array" item="id" open="(" close=")" separator=",">#{id}</foreach>
</if>
<if test="owner!=null">
and owner=#{owner}
</if>
</select>
</mapper>
BlogTest.java 每个test测试方法对应上面一个Select
package com.lq.test;
import java.io.IOException;
import java.io.Reader;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
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 org.junit.After;
import org.junit.Before;
import org.junit.Test;
import com.lq.model.Blog;
public class BlogTest {
SqlSession session =null;
@Before
public void before(){
//加载核心配置文件
Reader reader;
try {
reader = Resources.getResourceAsReader("SqlMapConfig.xml");
//创建session工厂
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
//打开一个和数据库的会话
session = sessionFactory.openSession();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
@After
public void after(){
//提交事务
session.commit();
//关闭连接
session.close();
}
@Test
public void testQuery_if() throws IOException{
Blog blog=new Blog();
blog.setTitle("标题");
blog.setContent("内容");
List<Blog> list=session.selectList("com.lq.model.BlogMapper.queryBlog_if", blog);
for (Blog blog2 : list) {
System.out.println(blog2.getId()+"-----"+blog2.getTitle()+"-----"+
blog2.getContent()+"-----"+blog2.getOwner());
}
}
@Test
public void testQuery_choose() throws IOException{
Blog blog=new Blog();
List<Blog> list=session.selectList("com.lq.model.BlogMapper.queryBlog_choose", blog);
for (Blog blog2 : list) {
System.out.println(blog2.getId()+"-----"+blog2.getTitle()+"-----"+
blog2.getContent()+"-----"+blog2.getOwner());
}
}
@Test
public void testQuery_trim() throws IOException{
Blog blog=new Blog();
blog.setTitle("标题");
blog.setContent("内容");
List<Blog> list=session.selectList("com.lq.model.BlogMapper.queryBlog_trim", blog);
for (Blog blog2 : list) {
System.out.println(blog2.getId()+"-----"+blog2.getTitle()+"-----"+
blog2.getContent()+"-----"+blog2.getOwner());
}
}
@Test
public void queryForeachList() throws IOException{
List list=new ArrayList();
list.add(1);
list.add(2);
List<Blog> blogs=session.selectList("com.lq.model.BlogMapper.query_foreach_list", list);
for (Blog blog2 : blogs) {
System.out.println(blog2.getId()+"-----"+blog2.getTitle()+"-----"+
blog2.getContent()+"-----"+blog2.getOwner());
}
}
@Test
public void queryForeachArray() throws IOException{
int[] array={1,2};
List<Blog> blogs=session.selectList("com.lq.model.BlogMapper.query_foreach_array", array);
for (Blog blog2 : blogs) {
System.out.println(blog2.getId()+"-----"+blog2.getTitle()+"-----"+
blog2.getContent()+"-----"+blog2.getOwner());
}
}
@Test
public void queryForeachMap() throws IOException{
Map map = new HashMap();
int[] array={1,2};
map.put("owner", "张三");
map.put("array",array);
List<Blog> blogs=session.selectList("com.lq.model.BlogMapper.query_foreach_map", map);
for (Blog blog2 : blogs) {
System.out.println(blog2.getId()+"-----"+blog2.getTitle()+"-----"+
blog2.getContent()+"-----"+blog2.getOwner());
}
}
}