源码:Mybatis学习源码
Mybatis动态sql
先搭建好Mybatis环境
实体类Student
package com.zhang.pojo;
/**
* @title: Student
* @Author 张宜强
* @Date: 2021/2/26 15:44
* @Effect:
*/
public class Student {
private String sid;
private String name;
private String tid;
//构造方法,set,get,toString方法
...
}
if标签
StudentMapper.java
现在有两个查询,一个是根据id查询学生信息,一个是查询所有学生信息,方法重载。
package com.zhang.mapper;
import com.zhang.pojo.Student;
import org.apache.ibatis.annotations.Param;
import java.util.List;
/**
* @title: StudentMapper
* @Author 张宜强
* @Date: 2021/2/26 15:58
* @Effect:
*/
public interface StudentMapper {
public List<Student> findStudent();
public List<Student> findStudent(@Param("sid") String sid);
}
在if标签中的test你妹可以写语句,相当于一个判断,当sid != null
时sql语句为
select * from Student where 1=1 and sid = #{sid}
当sid == null
时,sql语句为
select * from Student where 1=1
<select id="findStudent" resultType="Student">
select * from Student where 1=1
<if test="sid != null">
and sid = #{sid}
</if>
</select>
测试一下,下面是传入参数的情况,即sid!=null的情况。
import com.zhang.mapper.BlogMapper;
import com.zhang.mapper.StudentMapper;
import com.zhang.pojo.Blog;
import com.zhang.pojo.Student;
import com.zhang.util.GetSqlSession;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.List;
/**
* @title: MybatisTest
* @Author 张宜强
* @Date: 2021/3/1 11:53
* @Effect:
*/
public class MybatisTest {
@Test
public void Test2() {
SqlSession sqlSession = GetSqlSession.getSqlSession();
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
List<Student> students = studentMapper.findStudent("1");
for (Student student : students) {
System.out.println(student);
}
sqlSession.close();
}
}
运行结果,查出了sid=1的学生的基本信息。
修改代码为不传入参数。
import com.zhang.mapper.BlogMapper;
import com.zhang.mapper.StudentMapper;
import com.zhang.pojo.Blog;
import com.zhang.pojo.Student;
import com.zhang.util.GetSqlSession;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.List;
/**
* @title: MybatisTest
* @Author 张宜强
* @Date: 2021/3/1 11:53
* @Effect:
*/
public class MybatisTest {
@Test
public void Test1() {
SqlSession sqlSession = GetSqlSession.getSqlSession();
BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);
List<Blog> blog = blogMapper.getBlog();
for (Blog blog1 : blog) {
System.out.println(blog1);
}
sqlSession.close();
}
@Test
public void Test2() {
SqlSession sqlSession = GetSqlSession.getSqlSession();
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
List<Student> students = studentMapper.findStudent();
for (Student student : students) {
System.out.println(student);
}
sqlSession.close();
}
}
查询出所有学生的信息。
choose、when、otherwise标签
choose相当于语言中的switch,when相当于case,otherwise相当于default
修改代码,方法传入两个参数,按照这两个参数来查询学生信息。
package com.zhang.mapper;
import com.zhang.pojo.Student;
import org.apache.ibatis.annotations.Param;
import java.util.List;
/**
* @title: StudentMapper
* @Author 张宜强
* @Date: 2021/2/26 15:58
* @Effect:
*/
public interface StudentMapper {
public List<Student> findStudent(@Param("sid") String sid, @Param("name") String name);
}
修改StudentMapper.xml
该语句的意思是当sid与name都不为空时,按照两者查询;
当sid不为空,name为空时,按照sid查询;
当name不为空,sid为空时,按照name查询;
当两者都为空时,查询sid为1的学生信息。
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.zhang.mapper.StudentMapper">
<select id="findStudent" resultType="Student">
select * from student where 1=1
<choose>
<when test="sid != null and name != null">
and sid = #{sid} and name = #{name}
</when>
<when test="sid != null">
and sid = #{sid}
</when>
<when test="name != null">
and name = #{name}
</when>
<otherwise>
and sid = '1'
</otherwise>
</choose>
</select>
</mapper>
测试代码:当传入sid与name都不为空时
@Test
public void Test3() {
SqlSession sqlSession = GetSqlSession.getSqlSession();
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
List<Student> students = studentMapper.findStudent("2", "李四");
for (Student student : students) {
System.out.println(student);
}
sqlSession.close();
}
运行结果:
测试代码:当传入sid不为空,name为空时
@Test
public void Test3() {
SqlSession sqlSession = GetSqlSession.getSqlSession();
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
List<Student> students = studentMapper.findStudent("4", null);
for (Student student : students) {
System.out.println(student);
}
sqlSession.close();
}
运行结果:
测试代码:当传入sid为空,name不为空时
@Test
public void Test3() {
SqlSession sqlSession = GetSqlSession.getSqlSession();
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
List<Student> students = studentMapper.findStudent(null, "王五");
for (Student student : students) {
System.out.println(student);
}
sqlSession.close();
}
运行结果:
测试代码:当传入sid与name都为空时
@Test
public void Test3() {
SqlSession sqlSession = GetSqlSession.getSqlSession();
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
List<Student> students = studentMapper.findStudent(null, null);
for (Student student : students) {
System.out.println(student);
}
sqlSession.close();
}
运行结果:
where、set
where
where标签只会在后面有内容的情况下才插入 “WHERE” 子句。而且,若子句的开头为 “AND” 或 “OR”,where 元素也会将它们去除。
可以看到在按前面的代码中我在where语句后面加了1=1,这是为了更好的拼接sql语句,但是在实际的开发中这样写是有风险的。
如果我的sql语句不加1=1,
当sid为空时,拼接的sql语句为:
select * from student where and name = #{name}
当sid和name都为空时,拼接的sql语句为
select * from student where
很明显都是不符合sql语法的。
<select id="findStudent" resultType="Student">
select * from student where
<if test="sid != null">
sid = #{sid}
</if>
<if test="name != null">
and name = #{name}
</if>
</select>
使用下面的sql配置,使用where标签
<select id="findStudent" resultType="Student">
select * from student
<where>
<if test="sid != null">
and sid = #{sid}
</if>
<if test="name != null">
and name = #{name}
</if>
</where>
</select>
当name为空,sid为空时
@Test
public void Test3() {
SqlSession sqlSession = GetSqlSession.getSqlSession();
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
List<Student> students = studentMapper.findStudent(null, null);
for (Student student : students) {
System.out.println(student);
}
sqlSession.close();
}
sql语句
查询结果:
当id为空,name不为空时
测试代码:
@Test
public void Test3() {
SqlSession sqlSession = GetSqlSession.getSqlSession();
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
List<Student> students = studentMapper.findStudent(null, "张三");
for (Student student : students) {
System.out.println(student);
}
sqlSession.close();
}
sql语句:
查询结果:
当name为空,id 不为空时
测试代码:
@Test
public void Test3() {
SqlSession sqlSession = GetSqlSession.getSqlSession();
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
List<Student> students = studentMapper.findStudent("2", null);
for (Student student : students) {
System.out.println(student);
}
sqlSession.close();
}
sql语句:
运行结果:
set
set标签的作用和where类似
set元素会动态地在行首插入 SET 关键字,并会删掉额外的逗号
<insert id="insertStudent" parameterType="Student">
insert into student
<set>
<if test="sid != null">
sid = #{sid},
</if>
<if test="name != null">
name = #{name},
</if>
<if test="tid != null">
tid = #{tid}
</if>
</set>
</insert>
当三者都不为空时:
@Test
public void Test4() {
SqlSession sqlSession = GetSqlSession.getSqlSession();
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
Student student = new Student();
student.setName("小张");
student.setSid("10");
student.setTid("1");
studentMapper.insertStudent(student);
sqlSession.commit();
sqlSession.close();
}
可以插入成功
当tid为空时,如果不使用set标签,sql语句为
insert into student set name = #{name}, sid = #{sid},
多了一个逗号。
@Test
public void Test4() {
SqlSession sqlSession = GetSqlSession.getSqlSession();
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
Student student = new Student();
student.setName("小李");
student.setSid("11");
//student.setTid("1");
studentMapper.insertStudent(student);
sqlSession.commit();
sqlSession.close();
}
使用set标签,会自动去掉末尾多余的空格,插入成功。
sql片段
可以使用sql标签把常用的sql语句作为一个sql片段,然后在其他地方使用include标签可以使用sql片段。
比如,我把select * from student 作为一个片段
<sql id="select">
select * from student
</sql>
在下面的sql语句中使用sql片段
<select id="findStudent" resultType="Student">
<include refid="select"></include>
<where>
<if test="sid != null">
sid = #{sid}
</if>
<if test="name != null">
and name = #{name}
</if>
</where>
</select>
java代码查询
@Test
public void Test2() {
SqlSession sqlSession = GetSqlSession.getSqlSession();
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
List<Student> students = studentMapper.findStudent("1", "张三");
for (Student student : students) {
System.out.println(student);
}
sqlSession.close();
}
查询结果:
foreach
有时候我们可能会写这样的sql语句
select * from student where sid in ('1','2','3')
这时候可以使用foreach
<select id="selectIdIn" parameterType="map" resultType="Student">
select * from student where sid in
<foreach collection="ids" item="id" open="(" close=")" separator=",">
#{id}
</foreach>
</select>
collection是从map中取出来的集合的key,item 是 从集合中取出来的值, open是以引号中的内容开始,close是以引号中的内容结束,separator是以引号中的内容分隔。
然后在StudentMapper.java中添加接口。
public List<Student> selectIdIn(Map map);
测试方法
我们先定义一个List作为要查询的id的集合,然后将集合作为map的value,调用方法。
@Test
public void Test5() {
SqlSession sqlSession = GetSqlSession.getSqlSession();
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
List<String> ids = new ArrayList<String>();
ids.add("1");
ids.add("2");
Map map = new HashMap<String,Object>();
map.put("ids",ids);
List<Student> students = studentMapper.selectIdIn(map);
for(Student student : students) {
System.out.println(student);
}
sqlSession.commit();
sqlSession.close();
}
查询结果:
还有像
select * from student where (sid = '1' or sid ='2'
这种sql都可以用foreach实现
<select id="selectIdIn" parameterType="map" resultType="Student">
select * from student
<where>
<foreach collection="ids" item="id" open="(" close=")" separator="or">
sid = #{id}
</foreach>
</where>
</select>