映射文件:
<!-- =================动态sql -->
<!--
where 用于动态条件组合查询,可以去掉where后的第一个and
-->
<select id="slelectPersonByCondition" parameterType="qc" resultType="zhou.model.Person">
select * from person_test t
<where>
<if test="name != null">
t.name like '%${name}%'
</if>
<if test="gender != null">
and t.gender = #{gender}
</if>
<if test="address != null">
and t.address like '%${address}%'
</if>
<if test="birthday != null">
and t.birthday < #{birthday}
</if>
</where>
</select>
<!--
动态修改
使用set标签处理,能处理掉最后一个逗号,
如果前端set的值都为空可加t.id = #{id} 变通处理
-->
<update id="dynamicUpdate" parameterType="person">
update person_test t
<set>
t.id = #{id},
<if test="name != null">
t.name = #{name},
</if>
<if test="gender != null">
t.gender = #{gender},
</if>
<if test="address != null">
t.address = #{address},
</if>
<if test="birthday != null">
t.birthday = #{birthday}
</if>
</set>
where t.id = #{id}
</update>
<!--
(1,2,3)
map.put("ids", list/array/set)
foreach:遍历集合来组装sql
collection: map集合的key
open: 以某种字符开始
close:以某种字符结束
item: 集合中的元素
separator:以某种字符分割
index: 当前所遍历到的索引号
-->
<select id="selectPersonByIn" parameterType="map" resultType="zhou.model.Person">
select * from person_test t where t.id in
<foreach collection="ids" open="(" close=")" item="id" separator="," index="index">
#{id}
</foreach>
</select>
<delete id="deleteBatch" parameterType="map">
delete from person_test where id in
<foreach collection="ids" open="(" close=")" item="id" separator="," index="index">
#{id}
</foreach>
</delete>
<!--
map.put("personList", List<Person> list)
mybatis有它的一种机制可插入多条: insert into person(id, name) values (1, "张三"), (2, "张三"), (3, "张三")
-->
<insert id="insertBatch" parameterType="person">
<selectKey keyProperty="id" order="AFTER" resultType="int">
select last_insert_id()
</selectKey>
insert into person_test (<include refid="colums"/>)
values
<foreach collection="personList" separator="," item="person">
(#{person.name},#{person.gender},#{person.address},#{person.birthday})
</foreach>
</insert>
Test类:
package zhou;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Before;
import org.junit.Test;
import zhou.model.Person;
import zhou.model1.QueryCondition;
import java.io.InputStream;
import java.util.*;
/**
* Unit test for simple App.
*/
public class AppTest02 {
SqlSessionFactory sessionFactory;
@Before
public void setUp() throws Exception {
InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
sessionFactory = new SqlSessionFactoryBuilder().build(in);
}
@Test
public void test() {
SqlSession session = sessionFactory.openSession();
try {
QueryCondition condition = new QueryCondition();
condition.setName("张三");
condition.setAddress("上");
// condition.setBirthday(new Date());
// condition.setGender(0);
List<Person> list = session.selectList("mappings.PersonTestMapper.slelectPersonByCondition", condition);
for (Person p : list) {
System.out.println(p);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
session.close();
}
}
@Test
public void dymicUpdate() {
SqlSession session = sessionFactory.openSession();
Person p = new Person();
// p.setName("张三");
// p.setAddress("上");
p.setId(1);
// p.setBirthday(new Date());
// p.setGender(0);
try {
int list = session.update("mappings.PersonTestMapper.dynamicUpdate", p);
session.commit();
} catch (Exception e) {
e.printStackTrace();
session.rollback();
} finally {
session.close();
}
}
@Test
public void dymicSelete() {
SqlSession session = sessionFactory.openSession();
try {
Integer[] ids = {1, 2, 3};
HashMap<String, Object> map = new HashMap<>();
map.put("ids", ids);
List<Person> list = session.selectList("mappings.PersonTestMapper.selectPersonByIn", map);
for (Person p : list) {
System.out.println(p);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
session.close();
}
}
@Test
public void dymicDelete() {
SqlSession session = sessionFactory.openSession();
try {
Integer[] ids = {1, 2, 3};
HashMap<String, Object> map = new HashMap<>();
map.put("ids", ids);
int list = session.delete("mappings.PersonTestMapper.deleteBatch", map);
session.commit();
} catch (Exception e) {
e.printStackTrace();
} finally {
session.close();
}
}
@Test
public void insertBatch() {
SqlSession session = sessionFactory.openSession();
List<Person> list = new ArrayList<>();
Map<String, Object> map = new HashMap<>();
try {
for (int i = 0; i < 1000000; i++) {
Person person = new Person();
person.setName("张三" + i);
person.setGender(0);
person.setAddress("上海" + i);
person.setBirthday(new Date());
list.add(person);
if (i % 100 == 0) {
map.put("personList", list);
session.insert("mappings.PersonTestMapper.insertBatch", map);
list.clear();
}
}
map.put("personList", list);
session.insert("mappings.PersonTestMapper.insertBatch", map);
// 数据库的变更都要提交事务
session.commit();
} catch (Exception e) {
e.printStackTrace();
session.rollback();
} finally {
session.close();
}
}
}