MyBatisダイナミックSQLとあいまいなクエリ
14052 ワード
sqlxml
condition java bean
java bean
test
<?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="PersonCondition">
<!--<where> and-->
<select id="selectPersonByCondition" parameterType="person" resultMap="BaseResultMap">
select * from person t
<where>
<if test="name != null">
name like '%${name}%'
</if>
<if test="gender != null">
and gender = #{gender}
</if>
<if test="birthday !=null">
<![CDATA[
and birthday > #{birthday}
]]>
</if>
</where>
</select>
<!--set -->
<update id="dynamicUpdate" parameterType="person">
update person t
<set>
t.person_id = #{personId},
<if test="name != null">
name =#{name},
</if>
<if test="gender != null">
gender = #{gender},
</if>
<if test="birthday !=null">
birthday = #{birthday}
</if>
</set>
</update>
<!--foreach
map.put("ids",Integer[])
foreach sql
collection: map key
open:
close:
item:
separator:
index:
-->
<select id="selectPersonByIn" parmameterType="map" resultMap="BaseResultMap">
select * from person where t.person_id in
<foreach collection="ids" open="(" close=")" item="pId" separator="," index="indx">
#{pId}
</foreach>
</select>
<!--mysql ,insert into person() values (1..),(2..)-->
<insert id="insertBatch" parameterType="map">
insert into person (name,gender,addr,birthday)
values
<foreach collection="pList" separator="," item="person">
(#{person.name},#{person.gender},#{person.addr},#{person.birthday})
</foreach>
</insert>
<!-- , , -->
<select id="getPerson" parameterType="com.stone.bean.ConditionPerson"
resultType="com.stone.bean.Person">
select * from person where
<if test='name !="%null%"'>
name like #{name} and
</if>
age
between
#{minAge} and #{maxAge}
</select>
</mapper>
condition java bean
package com.stone.bean;
public class ConditionPerson {
private String name;
private int minAge;
private int maxAge;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getMinAge() {
return minAge;
}
public void setMinAge(int minAge) {
this.minAge = minAge;
}
public int getMaxAge() {
return maxAge;
}
public void setMaxAge(int maxAge) {
this.maxAge = maxAge;
}
public ConditionPerson(String name, int minAge, int maxAge) {
super();
this.name = name;
this.minAge = minAge;
this.maxAge = maxAge;
}
public ConditionPerson() {
super();
}
@Override
public String toString() {
return "ConditionPerson [name=" + name + ", minAge=" + minAge
+ ", maxAge=" + maxAge + "]";
}
}
java bean
package com.stone.bean;
import java.text.SimpleDateFormat;
import java.util.Date;
public class Person {
private int id;
private String name;
private Date birthday;
private int age;
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
@Override
public String toString() {
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMdd HH:mm:SS");
return "Person [id=" + id + ", name=" + name + ", birthday="
+ dateFormat.format(birthday) + "]";
}
}
test
package com.stone.dao;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import com.stone.bean.ConditionPerson;
import com.stone.bean.Person;
import com.stone.db.DBAccess;
public class DBDaoPerson {
public static void main(String[] args) {
DBAccess dbAccess = new DBAccess();
SqlSession sqlSession = null;
try {
sqlSession = dbAccess.getSqlSession();
String statement = "PersonCondition.getPerson";
ConditionPerson parameter = new ConditionPerson("%a%", 11, 18);
// sqlSession SQL ;
List<Person> list = sqlSession.selectList(statement, parameter);
System.out.println(list);
System.out.println("=======================");
} catch (Exception e) {
e.printStackTrace();
} finally {
if (sqlSession != null) {
sqlSession.close();
}
}
}
}