mybatis动态sql
MyBatis 的强大特性之一便是它的动态 SQL。如果你有使用 JDBC 或其它类似框架的经验,你就能体会到根据不同条件拼接 SQL 语句的痛苦。例如拼接时要确保不能忘记添加必要的空格,还要注意去掉列表最后一个列名的逗号。利用动态 SQL 这一特性可以彻底摆脱这种痛苦。
if语句
动态 SQL 通常要做的事情是根据条件包含 where 子句的一部分。比如:
<?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="sz.lz.dao.IUserDao">
<select id="query" parameterType="sz.lz.vo.User" resultType="sz.lz.vo.User">
SELECT id,name,age FROM t_user WHERE 1=1
<if test="name !=null">
AND name =#{name}
</if>
</select>
</mapper>
这条语句是如果没有传入name
属性,那么将会查询出t_user表中的所有数据;反之若传入了name
,那么就会根据name
属性进行查询
dao层方法
package sz.lz.dao;
import java.util.List;
import sz.lz.vo.User;
public interface IUserDao {
public List<User> query(User user);
}
测试方法
不传name属性
public class Test {
@org.junit.Test
public void test() throws IOException {
InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(in);
SqlSession session = ssf.openSession(true);
IUserDao dao = session.getMapper(IUserDao.class);
User user = new User();
user.setAge(33);
List<User> list = dao.query(user);
for (User us : list) {
System.out.println(us);
}
}
}
不传name属性测试结果:可以看出查询出了所有的数据
传name属性
public class Test {
@org.junit.Test
public void test() throws IOException {
InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(in);
SqlSession session = ssf.openSession(true);
IUserDao dao = session.getMapper(IUserDao.class);
User user = new User();
user.setAge(33);
user.setName("小华");
List<User> list = dao.query(user);
for (User us : list) {
System.out.println(us);
}
}
}
传name属性测试结果:
choose,when,otherwise
有时我们不想应用到所有的条件语句,而只想从中择其一项。针对这种情况,MyBatis 提供了 choose 元素,它有点像 Java 中的 switch 语句。
<?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="sz.lz.dao.IUserDao">
<select id="query" parameterType="sz.lz.vo.User" resultType="sz.lz.vo.User">
SELECT id,name,age FROM t_user WHERE
<choose>
<when test="id!=null">
id=#{id}
</when>
<when test="name!=null">
name=#{name}
</when>
<otherwise>
id=1
</otherwise>
</choose>
</select>
</mapper>
这条语句的意思是,如果id不为空那么就按id查,如果id为空就看name,如果name不为空就按name查询,如果name为空就按id=1查询
测试方法
只设置了age属性没有设置name和id属性
public class Test {
@org.junit.Test
public void test() throws IOException {
InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(in);
SqlSession session = ssf.openSession(true);
IUserDao dao = session.getMapper(IUserDao.class);
User user = new User();
user.setAge(33);
List<User> list = dao.query(user);
for (User us : list) {
System.out.println(us);
}
}
}
测试结果:可以看到是按id=1查询的
只设置了name属性没有设置id属性
public class Test {
@org.junit.Test
public void test() throws IOException {
InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(in);
SqlSession session = ssf.openSession(true);
IUserDao dao = session.getMapper(IUserDao.class);
User user = new User();
user.setName("小华");
List<User> list = dao.query(user);
for (User us : list) {
System.out.println(us);
}
}
}
测试结果:可以看到是按name查询的
设置了name属性和id属性
public class Test {
@org.junit.Test
public void test() throws IOException {
InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(in);
SqlSession session = ssf.openSession(true);
IUserDao dao = session.getMapper(IUserDao.class);
User user = new User();
user.setId(43);
user.setName("小华");
List<User> list = dao.query(user);
for (User us : list) {
System.out.println(us);
}
}
}
测试结果:
where语句
在使用if语句做动态条件处理的时候如果所有条件都不满足,那么得到的SQL语句就会有问题如:SELECT * FROM t_user WHERE
在这种情况下,我们一般会加一个1=1来匹配语法规则,就像上面if语句中到的那样,然而mybatis提供了where标签来解决这一问题
<?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="sz.lz.dao.IUserDao">
<select id="query" parameterType="sz.lz.vo.User" resultType="sz.lz.vo.User">
SELECT id,name,age FROM t_user
<where>
<if test="id!=null">
AND id=#{id}
</if>
<if test="name!=null">
AND name=#{name}
</if>
</where>
</select>
</mapper>
测试方法
传递id和name
public class Test {
@org.junit.Test
public void test() throws IOException {
InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(in);
SqlSession session = ssf.openSession(true);
IUserDao dao = session.getMapper(IUserDao.class);
User user = new User();
user.setId(43);
user.setName("小芳");
List<User> list = dao.query(user);
for (User us : list) {
System.out.println(us);
}
}
}
测试结果:可以看到去掉了最前面的AND
id和name都不传
public class Test {
@org.junit.Test
public void test() throws IOException {
InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(in);
SqlSession session = ssf.openSession(true);
IUserDao dao = session.getMapper(IUserDao.class);
User user = new User();
List<User> list = dao.query(user);
for (User us : list) {
System.out.println(us);
}
}
}
测试结果
SET语句
set 元素会动态前置 SET 关键字,同时也会删掉无关的逗号,因为用了条件语句之后很可能就会在生成的 SQL 语句的后面留下这些逗号。
<?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="sz.lz.dao.IUserDao">
<update id="updateUser" parameterType="sz.lz.vo.User" >
UPDATE t_user
<set>
<if test="name!=null">
name = #{name},
</if>
<if test="age!=null">
age = #{age},
</if>
</set>
WHERE id = #{id}
</update>
</mapper>
测试方法
public class Test {
@org.junit.Test
public void test() throws IOException {
InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(in);
SqlSession session = ssf.openSession(true);
IUserDao dao = session.getMapper(IUserDao.class);
User user = new User();
user.setId(43);
user.setName("小小");
user.setAge(33);
int count = dao.updateUser(user);
System.out.println("更新了-->"+count+"条数据");
}
}
测试结果
trim语句
trim标记是一个格式化的标记.
替代where的用法
<?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="sz.lz.dao.IUserDao">
<select id="query" parameterType="sz.lz.vo.User" resultType="sz.lz.vo.User">
SELECT id,name,age FROM t_user
<trim prefix="WHERE" prefixOverrides="AND">
<if test="id!=null">
AND id=#{id}
</if>
<if test="name!=null">
AND name=#{name}
</if>
</trim>
</select>
</mapper>
测试方法
@org.junit.Test
public void test1() throws IOException {
InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(in);
SqlSession session = ssf.openSession(true);
IUserDao dao = session.getMapper(IUserDao.class);
User user = new User();
user.setId(43);
user.setName("小小");
user.setAge(33);
List<User> list = dao.query(user);
for (User user2 : list) {
System.out.println(user2);
}
}
测试结果
替代set的用法
<?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="sz.lz.dao.IUserDao">
<update id="updateUser" parameterType="sz.lz.vo.User" >
UPDATE t_user
<trim prefix="SET" suffixOverrides=",">
<if test="name!=null">
name = #{name},
</if>
<if test="age!=null">
age = #{age},
</if>
</trim>
WHERE id = #{id}
</update>
</mapper>
foreach语句
foreach
通常用来遍历数组或集合.
利用foreach添加数据
<?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="sz.lz.dao.IUserDao">
<insert id="addUsers">
INSERT INTO t_user(name,age) VALUE
<foreach collection="list" item="user" separator=",">
(#{user.name},#{user.age})
</foreach>
</insert>
</mapper>
dao层方法
package sz.lz.dao;
import java.util.List;
import org.apache.ibatis.annotations.Param;
import sz.lz.vo.User;
public interface IUserDao {
public int addUsers(@Param("list")List<User> userList);
}
测试方法
@org.junit.Test
public void test2() throws IOException {
InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(in);
SqlSession session = ssf.openSession(true);
IUserDao dao = session.getMapper(IUserDao.class);
List<User> list = new ArrayList<User>();
for(int i=0;i<=5;i++) {
User user = new User("测试"+i,33+i);
list.add(user);
}
int count = dao.addUsers(list);
System.out.println("更新了-->"+count+"条数据");
}
测试结果
bind
bind
元素可以从 OGNL
表达式中创建一个变量并将其绑定到上下文。
<?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="sz.lz.dao.IUserDao">
<select id="queryById" parameterType="int" resultType="sz.lz.vo.User">
<bind name="ddd" value="12"/>
SELECT * FROM t_user WHERE id = ${ddd}
</select>
</mapper>
dao层方法
public interface IUserDao {
public User queryById();
}
测试方法
@org.junit.Test
public void test3() throws IOException {
InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(in);
SqlSession session = ssf.openSession(true);
IUserDao dao = session.getMapper(IUserDao.class);
User user = dao.queryById();
System.out.println(user);
}
测试结果
sql
sql片段一般用来定义sql中的列
<?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="sz.lz.dao.IUserDao">
<!-- 一般用来定义需要重复使用的列名 -->
<sql id="baseSql">
id,name,age
</sql>
<select id="queryById" parameterType="int" resultType="sz.lz.vo.User">
<bind name="ddd" value="34"/>
SELECT <include refid="baseSql"/> FROM t_user WHERE id = ${ddd}
</select>
</mapper>
测试结果