sqlxml
<?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();
}
}
} }