- 动态sql:sql的内容是变化的,可以根据条件获取到不同的sql语句
主要是where部分发生变化。 - 动态sql的实现,使用的是mybatis提供的标签,,,
1.if标签
- 是判断条件的。
- 语法:
<if test="判断java对象的属性值">
部分sql语句
</if>
- dao层
//动态sql,使用java对象作为参数
List<Student> selectStudentIf(Student student);
- dao层xml
<!-- if
<if:test="使用参数java对象的属性值作为判断条件,语法 属性=xxx值">
-->
<select id="selectStudentIf" resultType="com.putao.domian.Student">
select * from student
where id>0
<if test="name !=null and name !=''">
and name=#{name}
</if>
<if test="age>0">
or age>#{age}
</if>
</select>
- Test
@Test
public void testSelectIf(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
StudentDao dao = sqlSession.getMapper(StudentDao.class);
Student student = new Student();
student.setName("李四");
// student.setAge(18);
//student.setName("李四");
student.setAge(20);
List<Student> students = dao.selectStudentIf(student);
students.forEach(t-> System.out.println("if==="+t));
}
2.where标签
- where用来包含多个if的,当多个if中有一个成立,where会自动增加一个where关键字 ,并去掉if中多余的and,or等。
- dao层
//where使用
List<Student> selectStudentWhere(Student student);
- dao层xml
<!-- where:<where><if><if>...</where>
-->
<select id="selectStudentWhere" resultType="com.putao.domian.Student">
select id,name,email,age from student
<where>
<if test="name !=null and name !=''">
name=#{name}
</if>
<if test="age>0">
or age>#{age}
</if>
</where>
</select>
- Test
@Test
public void testSelectWhere(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
StudentDao dao = sqlSession.getMapper(StudentDao.class);
Student student = new Student();
// student.setName("李四");
// student.setAge(18);
List<Student> students = dao.selectStudentWhere(student);
students.forEach(t-> System.out.println("where==="+t));
}
3.foreach标签
- foreach循环java中的数组,list集合的。主要用在sql的in语句中。
- 语法
<foreach collection="" item="" open="" close="" separator="">
</foreach>
collection:表示接口中的方法参数的类型,如果是数组使用array,如果是list集合使用list。
item:自定义的,表示数组和集合成员的变量。
open:循环开始时的字符。
close:循环结束时的字符。
separator:集合成员之间的分隔符。
- 学生id是1001,1002,1003的三个学生。
select * from student where id in (1001,1002,1003);
@Test
public void testFor(){
List<Integer> list = new ArrayList<>();
list.add(1001);
list.add(1002);
list.add(1003);
// String sql="select * from where id in (1001,1002,1003)";
String sql="select * from where id in";
// StringBuilder builder = new StringBuilder("");
StringBuilder builder1 = new StringBuilder("");
//
// int init=0;
// int len = list.size();
//
// //添加开始的(
// builder.append("(");
// for (Integer i:list){
// builder.append(i).append(",");
// }
// builder.deleteCharAt(builder.length()-1);
// //循环结尾
// builder.append(")");
// sql = sql + builder.toString();
// System.out.println("sql="+sql);
builder1.append("(");
list.forEach(l->builder1.append(l).append(","));
builder1.deleteCharAt(builder1.length()-1);
builder1.append(")");
String sql1 = sql + builder1.toString();
System.out.println("sql="+sql1);
}
3.1方式一
- dao层
//foreach 用法 1
List<Student> selectForeachOne(List<Integer> idlist);
- dao层xml
<!-- foreach使用1 ,List<Integer-->
<select id="selectForeachOne" resultType="com.putao.domian.Student">
select * from student where id in
<foreach collection="list" item="myid" open="(" close=")" separator=",">
#{myid}
</foreach>
</select>
- Test
@Test
public void testSelectForEachOne(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
StudentDao dao = sqlSession.getMapper(StudentDao.class);
List<Integer> list = new ArrayList<>();
list.add(1001);
list.add(1002);
list.add(1003);
List<Student> students = dao.selectForeachOne(list);
students.forEach(t-> System.out.println("foreach--one==="+t));
}
3.2方式二
- dao层
//foreach 用法 2
List<Student> selectForeachTwo(List<Student> stulist);
- dao层xml
<select id="selectForeachTwo" resultType="com.putao.domian.Student">
select * from student where id in (
<foreach collection="list" item="stu" separator=",">
#{stu.id}
</foreach>
)
</select>
- Test
@Test
public void testSelectForEachTwo(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
StudentDao dao = sqlSession.getMapper(StudentDao.class);
List<Student> stulist =new ArrayList<>();
Student s1 = new Student();
s1.setId(1002);
stulist.add(s1);
s1 = new Student();
s1.setId(1005);
stulist.add(s1);
List<Student> students = dao.selectForeachTwo(stulist);
students.forEach(t-> System.out.println("foreach--two==="+t));
}
4.代码片段
- sql代码片段,就是复用一些语法
- 步骤
-
先定义
<sql id="自定义名称唯一"> sql语句,表名,字段等 </sql>
-
再使用
<include refid="" />
例子:
<!-- 定义sql片段-->
<sql id="studentSql">
select id,name,email,age from student
</sql>
<sql id="studentSqlOne">
id,name,age,email
</sql>
<!-- 使用代码片段
-->
<select id="selectStudentIf" resultType="com.putao.domian.Student">
select <include refid="studentSqlOne" /> from student
where id>0
<if test="name !=null and name !=''">
and name=#{name}
</if>
<if test="age>0">
or age>#{age}
</if>
</select>