是根据逻辑中的条件,动态产生sql语句
动态新增
指定类的添加
实体类Student
public class Student {
private Integer sid;
private String name;
private String sex;
private Integer age;
private Date time;
//get set 方法,构造方法,tostring方法
}
StudentDao接口中
//指定添加
public interface StudentDao {
public void addStudent(Student student);
}
在实体类的映射类中
Student.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.ywy.dao.StudentDao">
<!--动态生成要添加的列-->
<sql id="key">
<!--trim suffixOverrides=","去掉后缀的指定字符-->
<trim suffixOverrides=",">
<if test="name!=null">
name,
</if>
<if test="sex!=null">
sex,
</if>
<if test="age!=null">
age,
</if>
<if test="time!=null">
time,
</if>
</trim>
</sql>
<!--上面如果属性都有值,那结果是 name,sex,age,time-->
<!--动态获取要新增的值-->
<sql id="values">
<trim suffixOverrides=",">
<if test="name!=null">
#{name},
</if>
<if test="sex!=null">
#{sex},
</if>
<if test="age!=null">
#{age},
</if>
<if test="time!=null">
#{time},
</if>
</trim>
</sql>
<!--上面如果属性都有值,那结果是 值1,值2,值3,值4-->
<!--动态添加,添加指定列,如果为空就不添加-->
<insert id="addStudent" parameterType="Student">
<!--include 包含 refid:引入一个sql语句,id名-->
insert into Student(<include refid="key"></include>) values(<include refid="values"></include>);
</insert>
</mapper>
测试(单元测试)
public class TestAll {
private SqlSession session;
@Before
public void before(){
SqlSessionFactory factory=new SqlSessionFactoryBuilder().build(getClass().getClassLoader().getResourceAsStream("mybatis-config.xml"));
session = factory.openSession();
}
@Test
public void test(){
StudentDao sd=session.getMapper(StudentDao.class);
//动态新增
//这里生成的sql:insert into student(name,age) values("夏明",22);
//只有有值的属性会添加
Student stu=new Student("夏明",null,22, null);
sd.addStudent(stu);
}
@After
public void after(){
session.commit();
session.close();
}
}
动态删除(批量删除)
数组方式
StudentDao中方法
//批量删除---数组
public void deleteStudentByConmer(int[] arr);
映射类中
<!--动态删除——————数组-->
<delete id="deleteStudentByConmer" >
delete from student
<!--添加-->
<where>
sid in
<!--循环遍历数组-->
<!--collection:类型 open:开始时加:( close:结束时加:) item:传进来数组的名称 separator:值用什么隔开-->
<foreach collection="array" open="(" close=")" item="arr" separator=",">
#{arr}
</foreach>
</where>
</delete>
测试
StudentDao sd=session.getMapper(StudentDao.class);
//批量删除
int[] arr={41,42,43,44};
sd.deleteStudentByConmer(arr);//删除编号为41,42,43,44的学生
集合方式的删除
dao中
//批量删除---集合
public void deleteStudentByList(List<Integer> stus);
映射类中Student.xml
<!--动态删除_______集合-->
<delete id="deleteStudentByList">
delete from student
<where>
sid in
<foreach collection="list" separator="," open="(" close=")" item="stus">
#{stus}
</foreach>
</where>
</delete>
测试
//批量删除---集合
List<Integer> stus=new ArrayList<Integer>();
stus.add(40);
stus.add(45);
stus.add(46);
sd.deleteStudentByList(stus);
动态修改(指定列修改)
dao中
//动态修改
public void updateStudent(Student student);
映射类中Student.xml
<!--动态修改 student对象-->
<update id="updateStudent" parameterType="Student">
update student
<!--加set,会自动将后缀的,去掉-->
<!--如果有值就修改-->
<set>
<if test="name!=null">
name=#{name},
</if>
<if test="sex!=null">
sex=#{sex},
</if>
<if test="age!=null">
age=#{age},
</if>
</set>
<!--修改条件-->
<where>
sid=#{sid}
</where>
</update>
测试
Student stu=new Student("xiaoming","男",22,null);
stu.setSid(47);
sd.updateStudent(stu);
高级查询
dao中
//模糊查询
public List<Student> findAllStudentByMh(Student student);
映射类中
<select id="findAllStudentByMh" parameterType="Student" resultType="Student">
select * from student
<!--哪个不为空就以哪个为条件,如果两个都不为空,就两个条件-->
<where>
<if test="sid!=null">
and sid=#{sid}
</if>
<if test="name!=null">
<!--模糊查询-->
and name like concat('%',#{name},'%')
</if>
</where>
</select>
测试
//模糊查询
Student stu2=new Student("小","男",22,null);
//这里就是根据姓名查询
List<Student> list = sd.findAllStudentByMh(stu2);
for (Student student : list) {
System.out.println(student);
}