MyBatis详解——多表关联及动态SQL
今天继续学习Mybatis相关的知识，这次来说说多表关联及动态SQL。
多表关联
何为多表关联，顾名思义，也就是两张以上表相互关联操作。
今天主要讲下一对一和一对多的多表关联。
一对一
何为一对一？就像一本图书对应一种图书类型。
下面我就用图书表book和图书类型表booktype演示下一对一的实现。
实例步骤
1.先创建实体类Book类和BookType类
package com.lanou3g.mybatis.bean;
import lombok.Getter;
import lombok.Setter;
@Setter
@Getter
public class Book {
private Integer id;
private String bname;
private BookType bookType;
private String author;
private String authorGender;
private Float price;
private String description;
@Override
public String toString() {
return "Book{" +
"id=" + id +
", bname='" + bname + '\'' +
", bookType=" + bookType +
", author='" + author + '\'' +
", authorGender='" + authorGender + '\'' +
", price=" + price +
", description='" + description + '\'' +
'}';
}
}
package com.lanou3g.mybatis.bean;
import lombok.Getter;
import lombok.Setter;
@Getter
@Setter
public class BookType {
private Integer id;
private String tname;
@Override
public String toString() {
return "BookType{" +
"id=" + id +
", tname='" + tname + '\'' +
'}';
}
}
2.配置 mybatis_conf.xml文件（它主要引入外部的properties文件(用于配置数据源)、定义类型别名（全局）、配置多套环境的数据库连接参数及引入哪些Mapper映射文件等）
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!-- 引入外部的properties文件，只能引入一个 -->
<properties resource="jdbc.properties" />
<settings>
<setting name="mapUnderscoreToCamelCase" value="false"/>
</settings>
<!-- 定义类型别名（全局），在所有的Mapper.xml中都可以用 -->
<typeAliases>
<typeAlias type="com.lanou3g.mybatis.bean.Teacher" alias="Teacher" />
<typeAlias type="com.lanou3g.mybatis.bean.Student" alias="Student" />
</typeAliases>
<!-- 配置多套环境的数据库连接参数（如：开发环境、生产环境） -->
<environments default="lanou">
<environment id="lanou">
<!-- 事务管理器：
MANAGED: 这个配置就是告诉mybatis不要干预事务，具体行为依赖于容器本身的事务处理逻辑。
JDBC: 这个配置就是直接使用了 JDBC 的提交和回滚设置，它依赖于从数据源得到的连接来管理事务作用域。
-->
<transactionManager type="MANAGED"/>
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.user}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper class="com.lanou3g.mybatis.dao.StudentDao/>
<mapper resource="mapper/TeacherMapper.xml" />
<mapper resource="mapper/StudentMapper.xml" />
<mapper resource="mapper/BookMapper.xml"/>
</mappers>
</configuration>
3.配置映射文件 BookMapper.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="com.lanou3g.mybatis.dao.BookDao">
<resultMap id="book" type="com.lanou3g.mybatis.bean.Book">
<id column="bid" property="id" />
<result column="bname" property="bname" />
<result column="author" property="author" />
<result column="author_gender" property="authorGender" />
<result column="price" property="price" />
<result column="description" property="description" />
<association property="bookType" javaType="com.lanou3g.mybatis.bean.BookType">
<id column="t_id" property="id"/>
<result column="tname" property="tname"/>
</association>
</resultMap>
<select id="queryBooks" resultMap="book">
select b.*,t.*, b.id bid, t.id t_id from book b, booktype t where b.btype = t.id;
</select>
</mapper>
4.定义接口类 BookDao.java，在其中定义与映射文件中select标签相对应得方法
package com.lanou3g.mybatis.dao;
import com.lanou3g.mybatis.bean.Book;
import java.util.List;
public interface BookDao {
List<Book> queryBooks();
}
5.最后在测试类测试方法
/**
* 一对一关联查询
*/
@Test
public void testQueryCascade() {
List<Book> bookList = bookDao.queryBooks();
log.info("关联查询图书表：" + bookList);
}
一对多
何为一对多？一个老师拥有多个学生，一个学生属于某个老师。
下面我就用教师表teacher和学生表student演示下一对多的实现。
实例步骤
1.先创建实体类Teacher和Student
package com.lanou3g.mybatis.bean;
import lombok.Getter;
import lombok.Setter;
import java.util.List;
@Getter
@Setter
public class Teacher {
private Integer id;
private String tname;
private Integer age;
private Integer salary;
private String remark;
private List<Student> students;
public Teacher (){}
public Teacher(String tname) {
this.tname = tname;
}
public Teacher(Integer id, String tname, Integer age, Integer salary, String remark) {
this.id = id;
this.tname = tname;
this.age = age;
this.salary = salary;
this.remark = remark;
}
@Override
public String toString() {
return "Teacher{" +
"id=" + id +
", tname='" + tname + '\'' +
", age=" + age +
", salary=" + salary +
", remark='" + remark + '\'' +
", students=" + students +
'}';
}
}
package com.lanou3g.mybatis.bean;
import lombok.Getter;
import lombok.Setter;
@Getter
@Setter
public class Student {
private Integer id;
private String name;
private Integer gender;
private Integer age;
public Student() {}
public Student(Integer id, String name, Integer gender, Integer age) {
this.id = id;
this.name = name;
this.gender = gender;
this.age = age;
}
@Override
public String toString() {
return "Student{" +
"id=" + id +
", name='" + name + '\'' +
", gender=" + gender +
", age=" + age +
'}';
}
public Student(Integer id, String name) {
this.id = id;
this.name = name;
}
}
2.配置 mybatis_conf.xml文件,上面一对一已有说明。
3.配置映射文件 TeacherMapper.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">
<!-- namespace对应空Dao接口的全名 -->
<mapper namespace="com.lanou3g.mybatis.dao.TeacherDao">
<resultMap id="teacher" type="Teacher" >
<id column="t_id" property="id" />
<result column="tname" property="tname" />
<result column="age" property="age" />
<result column="salary" property="salary" />
<result column="remark" property="remark" />
<collection property="students" ofType="Student">
<id column="s_id" property="id" />
<result property="id" column="sid" />
<result property="name" column="name" />
<result property="age" column="age" />
<result property="gender" column="gender"/>
</collection>
</resultMap>
<select id="queryTeacherCascade" resultMap="teacher">
SELECT t.*,s.*,t.id t_id,s.id s_id from teacher t, student s WHERE t.id = s.tid;
</select>
</mapper>
4.定义接口类TeacherDao.java，在其中定义与映射文件中select标签相对应得方法
package com.lanou3g.mybatis.dao;
import com.lanou3g.mybatis.bean.Teacher;
import org.apache.ibatis.annotations.Param;
import java.util.List;
public interface TeacherDao {
List<Teacher> queryAll();
List<Teacher> queryTeacherCascade();
}
5.测试
/**
* 一对多关联查询
*/
@Test
public void testQueryTeacherCascade() {
List<Teacher> teacherList = teacherDao.queryTeacherCascade();
log.info("关联查询教师表：" + teacherList);
}
动态SQL
1.简介
首先什么是动态SQL? 动态SQL有什么作用？
传统的使用JDBC的方法，相信大家在组合复杂的的SQL语句的时候，需要去拼接，稍不注意哪怕少了个空格，都会导致错误。Mybatis的动态SQL功能正是为了解决这种问题， 其通过 if, choose, when, otherwise, trim, where, set, foreach标签，可组合成非常灵活的SQL语句，从而提高开发人员的效率。下面就去感受Mybatis动态SQL的魅力吧。
用官方的话说：
MyBatis 的强大特性之一便是它的动态 SQL。如果你有使用 JDBC 或其它类似框架的经验，你就能体会到根据不同条件拼接 SQL 语句的痛苦。例如拼接时要确保不能忘记添加必要的空格，还要注意去掉列表最后一个列名的逗号。利用动态 SQL 这一特性可以彻底摆脱这种痛苦。
MyBatis 采用功能强大的基于 OGNL 的表达式来淘汰其它大部分元素。
要学习的mybatis动态 SQL 元素如下：
- if
- choose (when, otherwise)
- trim (where, set)
- foreach
2.where和if
mybatis中使用 where和if 组合
代码示例：
先在映射文件中配置
select id="queryStudentByCondition" resultType="Student">
select * from student
<where>
<if test="id!=null">
id=#{id}
</if>
<if test="name!=null">
and name=#{name}
</if>
</where>
</select>
再定义接口类方法
List<Student> queryStudentByCondition(Student condition);
测试
@Test
public void testQueryDynamicSQL(){
Student stu=new Student();
stu.setId(6);
stu.setName("后土");
List<Student> studentList=studentDao.queryStudentByCondition(stu);
log.info("where..if...学生列表：" + studentList);
}
3.choose和if
mybatis中使用 choose和if 组合
代码示例：
先在映射文件中配置
<select id="queryStudentByChooseWhen" resultType="Student">
select * from student
<where>
<choose>
<when test="name != null">
and name = #{name}
</when>
<when test="id != null">
and id = #{id}
</when>
<otherwise>
and id = 5
</otherwise>
</choose>
</where>
</select>
再定义接口类方法
List<Student> queryStudentByChooseWhen(Student condittion);
测试
@Test
public void testQueryDynamicSQL(){
Student stu=new Student();
stu.setId(4);
stu.setName("共工");
studentList = studentDao.queryStudentByChooseWhen(stu);
log.info("choose..when...学生列表：" + studentList);
}
4.set和trim
<update id="updateStu" parameterType="Student">
update student
<!--<set>
<if test="id!=null">
id=#{id},
</if>
<if test="name!=null">
name=#{name}
</if>
</set>-->
<trim prefix="set" suffixOverrides=",">
<if test="id!=null">
id=#{id},
</if>
<if test="name!=null">
name=#{name},
</if>
</trim>
where id = #{id}
</update>
定义接口类方法
int updateStu(Student condition);
测试
@Test
public void testUpdateDynamicSQL(){
Student student=new Student();
student.setId(5);
student.setName("玄冥");
int rows = studentDao.updateStu(student);
log.info("更新行数： " + rows);
}
527
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
