|
通过MyBatis提供的各种标签方法实现动态拼接Sql
（1）select
????????select中where与if标签的引用
<!--if和where标签-->
<select id="selectByCondition" parameterType="Student" resultMap="studentMap">
select
<include refid="studentColumn"></include>
from
student
<where>
<if test="name!=null and name!='' ">
and name like concat('%', #{name}, '%')
</if>
<if test="age!=null">
and age=#{age}
</if>
<if test="gender!=null and gender!='' ">
and gender=#{gender}
</if>
</where>
</select>
用了where-if标签后，可以动态地添加where条件，不用思考如何处理and问题，该标签会自动将第一个条件的and去掉
? ? ? ? select中choose-when标签的引用：
<select id="selectByWhen" parameterType="Student" resultMap="student">
select <include refid="studentColumn"></include>
from student
where
<choose>
<when test="name!=null and name!='' ">
name like concat('%', #{name}, '%')
</when>
<when test="age!=null">
age=#{age}
</when>
<when test="gender!=null and gender!='' ">
gender=#{gender}
</when>
<otherwise></otherwise>
</choose>
</select>
choose条件与if条件的区别在于前者只会选择满足了条件的一个，后面的不再进行判断选择，后者则会将所有满足if条件的数据都删选出来，可能不止一个
注：concat函数用于连接多个字符串
（2）update中set与if标签的引用
<update id="updateCondition" parameterType="Student">
update
student
<set>
<if test="name!=null and name!='' ">
name=#{name},
</if>
<if test="age!=null">
age=#{age},
</if>
<if test="gender!=null and gender!='' ">
gender=#{gender},
</if>
</set>
where id=#{id}
</update>
用了set-if标签后，可以动态的添加set设置，该标签可以自动将最后一个的,去掉
（3）delete中foreach标签的引用
传递的参数是Array时，
<delete id="deleteAllByArray">
delete from student where id in
<foreach collection="array" open="(" close=")" separator="," item="id">
#{id}
</foreach>
</delete>
传递的参数是List时，
<delete id="deleteAllByList">
delete from student where id in
<foreach collection="list" open="(" close=")" separator="," item="id">
#{id}
</foreach>
</delete>
在delete中foreach标签可以动态的删除满足数组或者列表的多行数据
（4）在insert中trim与if标签的引用
<insert id="insertOne" parameterType="Student">
insert into student
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="id!=null">
id,
</if>
<if test="name!=null and name!='' ">
name,
</if>
<if test="age!=null">
age,
</if>
<if test="gender!=null and gender!='' ">
gender,
</if>
</trim>
<trim prefix=" values(" suffix=")" suffixOverrides=",">
<if test="id!=null">
#{id},
</if>
<if test="name!=null and name!='' ">
#{name},
</if>
<if test="age!=null">
#{age},
</if>
<if test="gender!=null and gender!='' ">
#{gender},
</if>
</trim>
</insert>
在insert中利用该标签，可以动态的添加数据
#############################################################################
多表
一对一
MyBatis中使用association标签解决一对一关联查询，association标签可以使用的属性如下：
（1）property：对象属性的名称
（2）javaType：对象以昂属性的类型
（3）column：数据库中字段的名称（也可能是起的别名）
比如，
创建Banji类
public class Banji {
private Integer id;
private String name;
public Banji() {
}
public Banji(Integer id, String name) {
this.id = id;
this.name = name;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
@Override
public String toString() {
return "Banji{" +
"id=" + id +
", name='" + name + '\'' +
'}';
}
}
<resultMap id="studentMap" type="Student">
<!--映射主键属性：如果有多个主键字段，则定义多个id-->
<!--property:自己定义的类的属性名-->
<!--column:数据库里面表的字段名-->
<id property="id" column="id"></id>
<!--result定义普通属性-->
<result property="name" column="name"></result>
<result property="age" column="age"></result>
<result property="gender" column="gender"></result>
<result property="banjiId" column="banji_id"></result>
<association property="banji" javaType="Banji">
<id property="id" column="banjiId"></id>
<result property="name" column="banjiName"></result>
</association>
</resultMap>
<select id="selectStudentBanjiInfo" resultMap="studentMap">
select s.id, s.name, s.age, s.gender, b.id as banjiId, b.name as banjiName
from student as s
inner join banji as b on s.banji_id = b.id;
</select>
// 一对一关系
@Test
public void testSelectStudentBanjiInfo() {
SqlSession sqlSession = MyBatisUtil.getSqlSession();
List<Student> list = sqlSession.selectList("student.selectStudentBanjiInfo");
for (Student student : list) {
System.out.println(student);
}
}
注：需要再创建实体类Banji，添加属性id，与name，并且在Student类中添加Banji属性
这种形式破坏了数据的完整性，我们一般不用
一对多
MyBatis中使用connection标签解决一对多关联查询
比如，
在Banji类中加入数据
public class Banji {
private Integer id;
private String name;
// 一个班级下面有多个学生，表达一对多关系
private List<Student> list;
public List<Student> getList() {
return list;
}
public void setList(List<Student> list) {
this.list = list;
}
public Banji() {
}
public Banji(Integer id, String name) {
this.id = id;
this.name = name;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
@Override
public String toString() {
return "Banji{" +
"id=" + id +
", name='" + name + '\'' +
'}';
}
}
在Banji同级目录下创建一个BanjiMapper.xml文件，在全局配置文件mybatis.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="banji">
<resultMap id="banjiMap" type="Banji">
<id property="id" column="id"></id>
<!--result定义普通属性-->
<result property="name" column="name"></result>
<collection property="list" ofType="Student">
<id property="id" column="studentId"></id>
<result property="name" column="studentName"></result>
<result property="age" column="studentAge"></result>
<result property="gender" column="studentGender"></result>
</collection>
</resultMap>
<select id="selectBanjiStudentInfo" resultMap="banjiMap">
SELECT b.id, b.name, s.id AS studentId, s.name AS studentName, s.age AS studentAge, s.gender AS studentGender
FROM banji AS b
INNER JOIN student AS s ON b.id = s.banji_id
</select>
</mapper>
测试select语句：
/*一对多关系*/
@Test
public void testSelectBanjiStudentInfo() {
SqlSession sqlSession = MyBatisUtil.getSqlSession();
List<Banji> list = sqlSession.selectList("banji.selectBanjiStudentInfo");
for (Banji banji : list) {
System.out.println(banji);
List<Student> studentList = banji.getList();
for (Student student : studentList) {
System.out.println(student);
}
}
}
多对多其实就是分解为两个一对多
|