mybatis

qinzhongliang
265 阅读5分钟

一对一:根据班级 id 查询班级信息(带老师的信息)

Teacher.java
public class Teacher { // teacher
    private int id; // t_id
    private String name; // t_name
}

Classes.java
public class Classes { // class
    private int id; // c_id
    private String name; // c_name
    private Teacher teacher; // teacher_id
}

ClassesMapper.java
public interface ClassesMapper {
    public Classes getCls(int id);
}

ClassesMapper.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.bjlemon.mapper.ClassesMapper">
    <!-- select * from class c, teacher t where c.teacher_id=t.t_id and c.c_id=1 -->
  	<resultMap type="com.bjlemon.entity.Classes" id="ClassResultMap">
  		<id property="id" column="c_id"></id>
  		<result property="name" column="c_name"/>
  		<association property="teacher" column="teacher_id" javaType="com.bjlemon.entity.Teacher">
  			<id property="id" column="t_id"></id>
  			<result property="name" column="t_name"/>
  		</association>
  	</resultMap>
  	
  	<select id="getCls" parameterType="int" resultMap="ClassResultMap">
  		select * from class c, teacher t where c.teacher_id=t.t_id and c.c_id=#{id}
  	</select>
  	
  </mapper>

动态mapper的写法

  • 书写mapper接口，跟dao写法一样
  • 与之对应的mapper.xml配置文件的名称空间必须和mapper接口全名称保持一致
  • mapper.xml接口中的方法名称必须与之对应的sql语句的id保持一致

一对一相关属性

  • association:用于一对一的关联查询
  • property:对象属性的名称
  • javaType:对象属性的类型
  • column:所对应的外键字段名称

一对多:根据 classId 查询对应的班级信息,包括学生,老师

Teacher.java
public class Teacher { // teacher
    private int id; // t_id
    private String name; // t_name
}

Classes.java
public class Classes { // class
    private int id; // c_id
    private String name; // c_name
    private Teacher teacher; // teacher_id
    private List<Student> students = new ArrayList<>(); //多的一方(student)有外键class_id
}

Student.java
public class Student { // student
    private int id; // s_id
    private String name; // s_name
}

ClassesMapper.java
public interface ClassesMapper {
    public Classes getCls(int id);
}

ClassesMapper.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.bjlemon.mapper.ClassesMapper">
  <!-- SELECT * FROM class c, teacher t,student s WHERE c.teacher_id=t.t_id AND c.C_id=s.class_id AND c.c_id=1 -->
  	<resultMap type="com.bjlemon.entity.Classes" id="ClassResultMap">
  		<id property="id" column="c_id"></id>
  		<result property="name" column="c_name"/>
  		<association property="teacher" column="teacher_id" javaType="com.bjlemon.entity.Teacher">
  			<id property="id" column="t_id"></id>
  			<result property="name" column="t_name"/>
  		</association>
  		<collection property="students" ofType="com.bjlemon.entity.Student">
  			<id property="id" column="s_id"></id>
  			<result property="name" column="s_name"/>
  		</collection>
  	</resultMap>
  	
  	<select id="getCls" parameterType="int" resultMap="ClassResultMap">
  		select * from class c, teacher t,student s where c.teacher_id=t.t_id and c.C_id=s.class_id and 
c.c_id=#{id}
  	</select>
  </mapper>

一对多相关属性

  • collection:用于一对多的关联查询
  • ofType:指定集合中元素对象的类型

多对多

User.java
public class User { //users
    private int id; // u_id
    private String name; // u_name
    private Set<Group> groups = new HashSet<>(); // 中间表users_groups中的外键user_id
}

Group.java
public class Group { // groups
    private int id; // g_id
    private String name; // g_name
    private Set<User> users = new HashSet<>();// 中间表users_groups中的外键group_id
}

GroupMapper.java
public interface GroupMapper {
    List<Group> getGroups(int id);
}

GroupMapper.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.bjlemon.mapper.GroupMapper">
	<!-- 给一个用户 id，查看该用户下的所有用户组信息 -->
    <select id="getGroups" resultMap="getGroupMap">
        select g.g_id,g.g_name from users_groups ug,groups g
            where ug.group_id=g.g_id and ug.user_id=#{id}
    </select>
    <resultMap type="com.bjlemon.entity.Group" id="getGroupMap">
        <id property="id" column="g_id"/>
        <result property="name" column="g_name"/>
        <collection property="users" ofType="com.bjlemon.entity.User">
            <id property="id" column="u_id"/>
        	<result property="name" column="u_name"/>
        </collection>
    </resultMap>
</mapper>

动态SQL

###MyBatis中可用的动态标签：

  • if
动态SQL通常要做的事情是有条件地包含where子句的一部分

<select id="findActiveBlogWithTitleLike" resultType="Blog">
    SELECT * FROM BLOG WHERE state = 'ACTIVE' 
    <if test="title != null">
        AND title like #{title}
    </if>
</select>
    
    
<select id="findActiveBlogLike" resultType="Blog">
    SELECT * FROM BLOG WHERE state = 'ACTIVE'
    <if test="title != null">
        AND title like #{title}
    </if>
    <if test="author != null and author.name != null">
        AND author_name like #{author.name}
    </if>
</select>
  • choose(when,otherwise)
    有些时候，我们不想用到所有的条件语句，而只想从中择其一二。
    针对这种情况，MyBatis 提供了 choose 元素，它有点像 Java 中的 switch 语句。

<select id="findActiveBlogLike" resultType="Blog">
  SELECT * FROM BLOG WHERE state = 'ACTIVE'
  <choose>
    <when test="title != null">
      AND title like #{title}
    </when>
    <when test="author != null and author.name != null">
      AND author_name like #{author.name}
    </when>
    <otherwise>
      AND featured = 1
    </otherwise>
  </choose>
</select>



    

  • trim(where,set)
    • 



where 元素知道只有在一个以上的if条件有值的情况下才去插入"WHERE"子句。
而且，若最后的内容是"AND"或"OR"开头的，where 元素也知道如何将他们去除。



<trim prefix="WHERE" prefixOverrides="AND |OR ">
  ... 等同于where标签
</trim>

<trim prefix="SET" suffixOverrides=",">
  ...等同于set标签
</trim>

<select id="findActiveBlogLike" resultType="Blog">
  SELECT * FROM BLOG 
  <where>
    <if test="state != null">
         state = #{state}
    </if> 
    <if test="title != null">
        AND title like #{title}
    </if>
    <if test="author != null and author.name != null">
        AND author_name like #{author.name}
    </if>
  </where>
</select>

<update id="updateAuthorIfNecessary">
  update Author
    <set>
      <if test="username != null">
        username=#{username},
      </if>
      <if test="password != null">
        password=#{password},
      </if>
      <if test="email != null">
        email=#{email},
        </if>
      <if test="bio != null">
        bio=#{bio}
      </if>
    </set>
  where id=#{id}
</update>

    

  • foreach
    • 



动态SQL的另外一个常用的必要操作是需要对一个集合进行遍历，通常是在构建in条件语句的时候
    item：表示集合中每一个元素进行迭代时的别名，
    index：指 定一个名字，用于表示在迭代过程中，每次迭代到的位置，
    open：表示该语句以什么开始，
    separator：表示在每次进行迭代之间以什么符号作为分隔 符，
    close：表示以什么结束。



如果传入的是单参数且参数类型是一个List的时候，collection属性值为list


<select id="dynamicForeachTest" parameterType="java.util.List" resultType="Blog">
    select * from t_blog 
    where id in
        <foreach collection="list" index="index" item="item" open="(" separator="," close=")">
            #{item}       
       </foreach>    
</select>

如果传入的是单参数且参数类型是一个array数组的时候，collection的属性值为array


<select id="dynamicForeach2Test" parameterType="java.util.ArrayList" resultType="Blog">
2     select * from t_blog where id in
3     <foreach collection="array" index="index" item="item" open="(" separator="," close=")">
4          #{item}
5     </foreach>
6 </select>    

如果传入的参数是多个的时候，我们就需要把它们封装成一个Map了，当然单参数也可


<select id="dynamicForeach3Test" parameterType="java.util.HashMap" resultType="Blog">
    select * from t_blog 
    where title like "%"#{title}"%" and id in
        <foreach collection="ids" index="index" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
</select>

    

  • 其他标签
    • 



<trim prefix="(" suffix=")" suffixOverrides=",">
    ...
</trim>

<sql id="Base_Column_List">
    user_id, user_name, user_phone, user_account, user_password, user_mark
</sql>

<include refid="Base_Column_List" />

 
 
 
 
avatar 
  
 
文章
  
阅读
  
粉丝