目录
动态SQL
创建数据表
CREATE TABLE worker(
id INT NOT NULL AUTO_INCREMENT,
NAME VARCHAR(30),
gender VARCHAR(6),
age INT ,
salary INT,
PRIMARY KEY(id)
);
INSERT INTO worker(NAME,gender,age,salary) VALUES('张三1','男',22,5000);
INSERT INTO worker(NAME,gender,age,salary) VALUES('张三2','女',20,8000);
INSERT INTO worker(NAME,gender,age,salary) VALUES('张三3','男',28,4000);
INSERT INTO worker(NAME,gender,age,salary) VALUES('张三4','女',33,9000);
INSERT INTO worker(NAME,gender,age,salary) VALUES('张三5','女',18,12000);
INSERT INTO worker(NAME,gender,age,salary) VALUES('张三6','男',27,30000);
where标签
在sql句中附加where关键字,当where标签中有条件成立时,就会附加where关键字.
Where关键字,还会忽略掉离他最近的一个and 或or
<mapper namespace="cn.offcn.mapper.WorkerMapper">
<select id="getSearchByWorker" resultType="Worker">
select * from worker
<where>
<if test="name!=null">
name=#{name}
</if>
<if test="gender!=null">
and gender=#{gender}
</if>
<if test="age!=null">
and age>#{age}
</if>
</where>
</select>
测试:
@Test
public void testWhereIf(){
SqlSession session= MyBatisUtils.getSession();
WorkerMapper workerMapper = session.getMapper(WorkerMapper.class);
Worker worker=new Worker();
worker.setGender("女");
worker.setAge(20);
List<Worker> workerList=workerMapper.getSearchByWorker(worker);
for (Worker worker1 : workerList) {
System.out.println(worker1);
}
}
if标签
表示判断,当条件成立时就会附加<if>和</if>标签之间的sql语句。否则不附加.
<if test="name!=null">
name=#{name}
</if>
<if test="gender!=null">
and gender=#{gender}
</if>
<if test="age!=null">
and age>#{age}
</if>
choose,when,otherwise
当choose标签中,只要一个when成立,其它的when和oterwise将不会被执行.如果choose标签中的每个when都不成立,将执行otherwise.
<select id="getSearchByWorker" resultType="Worker">
select * from worker
<where>
<choose>
<when test="name!=null">
name=#{name}
</when>
<when test="gender!=null">
gender=#{gender}
</when>
<when test="age!=null">
age=#{age}
</when>
<otherwise>
1=1
</otherwise>
</choose>
</where>
</select>
Set标签
Set标签用于update 更新,当set标签中有条件成立时就会附加set关键字,去掉除去与sql无关的逗号。
<select id="updateWorker">
update worker
<set>
<if test="name!=null">
name=#{name},
</if>
<if test="gender!=null">
gender=#{gender},
</if>
<if test="age!=null">
age=#{age},
</if>
<if test="salary!=null">
salary=#{salary}
</if>
</set>
where id=#{id}
Trim标签
Trim应用于where关键字
当trim标签中有条件成立时就会附加prefix所指定的关键字。suffixOverrides 去掉无关的所指定的关键字。
<select id="getSearchByWorker" resultType="Worker">
select * from worker
<trim prefix="where" suffixOverrides ="and | or">
<if test="name!=null">
name=#{name} and
</if>
<if test="gender!=null">
gender=#{gender} and
</if>
<if test="age!=null">
age>#{age} and
</if>
</trim>
</select>
Trim应用于set
<trim prefix="set" prefixOverrides=",">
<if test="name!=null">
,name=#{name}
</if>
<if test="gender!=null">
,gender=#{gender}
</if>
<if test="age!=null">
,age=#{age}
</if>
<if test="salary!=null">
,salary=#{salary}
</if>
</trim>
where id=#{id}
Foreach标签
Foreach表示循环.有三个应用场景。
Collection : 表示集合的类型 list set
Item: 集合每个元素
Separator: 分隔符
Open: 在循环这前附加字符串
Close: 在循环完毕后附加字符串
Index: 集合的索引
批量添加
<insert id="addBatchWorker">
insert into worker (name,gender,age,salary) values
<foreach collection="list" item="worker" separator=",">
(#{worker.name},#{worker.gender},#{worker.age},#{worker.salary})
</foreach>
</insert>
public void testAddBatchWorker(){
SqlSession session= MyBatisUtils.getSession();
WorkerMapper workerMapper = session.getMapper(WorkerMapper.class);
List<Worker> workerList=new ArrayList<Worker>();
workerList.add(new Worker("李小表","男",20,3000));
workerList.add(new Worker("李老表","男",29,5500));
workerList.add(new Worker("李大彪","男",35,2500));
workerMapper.addBatchWorker(workerList);
session.commit();
MyBatisUtils.close(session);
}
批量更新
<update id="updateBatchWorker">
<foreach collection="list" item="worker" separator=";">
update worker
<set>
<if test="worker.name!=null">
name=#{worker.name},
</if>
<if test="worker.gender!=null">
gender=#{worker.gender},
</if>
<if test="worker.age!=null">
age=#{worker.age},
</if>
<if test="worker.salary!=null">
salary=#{worker.salary},
</if>
</set>
where id=#{worker.id}
</foreach>
</update>
Mysql不支持批量更新,如果想支持批量更新在url后面附加参数?allowMultiQueries=true
public void testUpdateBatchWorker(){
SqlSession session= MyBatisUtils.getSession();
WorkerMapper workerMapper = session.getMapper(WorkerMapper.class);
List<Worker> workerList=new ArrayList<Worker>();
Worker worker1=new Worker();
worker1.setId(1);
worker1.setName("name1");
Worker worker2=new Worker();
worker2.setId(6);
worker2.setName("name2");
worker2.setSalary(28000);
Worker worker3=new Worker();
worker3.setId(3);
worker3.setGender("女");
worker3.setName("name3");
worker3.setSalary(8000);
workerList.add(worker1);
workerList.add(worker2);
workerList.add(worker3);
workerMapper.updateBatchWorker(workerList);
session.commit();
MyBatisUtils.close(session);
}
批量删除
<delete id="deleteBatchWorker">
delete from worker where id in
<foreach collection="list" item="workerId" separator="," open="(" close=")">
#{workerId}
</foreach>
</delete>
@Test
public void testDelelteBatchWorker(){
SqlSession session= MyBatisUtils.getSession();
WorkerMapper workerMapper = session.getMapper(WorkerMapper.class);
List<Integer> ids= Arrays.asList(5,8,9);
workerMapper.deleteBatchWorker(ids);
session.commit();
MyBatisUtils.close(session);
}