多对一
例子
有两个表，学生表的tid是老师表的外键
老师表
CREATE TABLE teacher ( id int(10) NOT NULL, name varchar(30) DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
学生表
CREATE TABLE student1 ( id int(10) NOT NULL, name varchar(30) DEFAULT NULL, tid int(10) DEFAULT NULL, PRIMARY KEY (id), KEY fktid (tid), CONSTRAINT tid FOREIGN KEY (tid) REFERENCES teacher (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
maven和mybatis配置完毕之后
-
创建实体类
老师表就不必说了，照常写
学生表写为，也就是把tid去掉写一个私有老师
private int id;
private String name;
private Teacher teacher; -
按照惯例开始写Mapper
-
配置Mapper.xml文件
-
我们开始写把学生和学生对应的老师写出来的方法
public List<Student> getStudent();
public List<Student> getStudent2(); -
第一种方法，联表查询
<select id="getStudent" resultMap="StudentTeacher" >
select * from student1
</select>
<resultMap id="StudentTeacher" type="com.lt.pojo.Student">
<result property="id" column="id"/>
<result property="name" column="name"/>
<association property="teacher" column="tid" javaType="com.lt.pojo.Teacher" select="getTeacher"/>
</resultMap>
<select id="getTeacher" resultType="com.lt.pojo.Teacher">
select * from teacher where id=#{id}
</select> -
第二种方法，结果查询
<select id="getStudent2" resultMap="StudentTeacher2">
select s.id sid,s.name sname,t.name tname
from student1 s,teacher t
where s.tid=t.id;
</select>
<resultMap id="StudentTeacher2" type="com.lt.pojo.Student">
<result property="id" column="sid"/>
<result property="name" column="sname"/>
<association property="teacher" javaType="com.lt.pojo.Teacher">
<result property="name" column="tname"/>
</association>
</resultMap>
</mapper> - 测试即可
一对多
我们开始写把老师和对应的学生写出来的方法
Teacher getTeacher(@Param("tid") int id);
-
第一种方法，联表查询
略，本人太懒，只喜欢理解起来简单的结果查询
-
第二种方法，结果查询
<select id="getTeacher" resultMap="TeacherStudent">
select s.name sname,s.id sid,s.tid stid,t.name tname,t.id tid
from student1 s,teacher t
where s.tid=t.id and t.id=#{tid}
</select>
<resultMap id="TeacherStudent" type="com.lt.pojo.Teacher">
<result property="id" column="tid"/>
<result property="name" column="tname"/>
<collection property="student" ofType="com.lt.pojo.Student">
<result property="id" column="sid"/>
<result property="name" column="sname"/>
<result property="tid" column="stid"/>
</collection>
</resultMap>
动态 SQL
IF
<select id="findActiveBlogWithTitleLike"
resultType="Blog">
SELECT * FROM BLOG
WHERE state = ‘ACTIVE’
<if test="title != null">
AND title like #{title}
</if>
</select>
choose, when, otherwise
<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
<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>
SQL片段
一些重复部分我们抽取出来方便复用。
-
使用sql标签抽取公共部分
<sql id=" ">
</sql> -
在需要使用的地方用include
<where>
<include refid=" "></include>
</where>
ForEach
<select id="selectPostIn" resultType="domain.blog.Post">
SELECT *
FROM POST P
WHERE ID in
<foreach item="item" collection="list"
open="(" separator="," close=")">
#{item}
</foreach>
</select>
缓存
一级缓存sqlsession默认开启
在连接到关闭之间有效
失效的情况
-
查询不同的东西
-
增删改操作，改变原来的东西，必定会刷新
-
手动清楚缓存clearCache
-
查询不同的Mapper文件
二级缓存
步骤
-
开启全局缓存
<setting name="cacheEnabled" value="true"/>
-
要开启二级缓存,你需要在你的 SQL 映射文件中添加一行:
<cache/> //在当前Mapper中使用默认的
<cache
eviction="FIFO"
flushInterval="60000"
size="512"
readOnly="true"/> //也可以自定义
自定义缓存
ehcache
导包、properties文件