核心配置
- JavaBeanMapper.xml(sql映射)
作用
JavaBeanMapper.xml实现:
用来干什么?
- 定义Sql语句映射。相对照JDBC的实现,是将原本的Sql代码提取出来,最终根据映射关系执行Sql操作。
好处?
- 解耦,mapper只关心定义Sql的映射关系,与java代码分离,更易维护。
如何使用?
- 先来展示一个基本的mapper xml,这里涉及到主要的几个标签元素:
- Select
- Insert
- Update
- Delete
- ResultMap
- Sql
- Cache
- 先来展示一个基本的mapper 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="org.example.daos.UserMapper">
<resultMap id="userMap" type="Customer">
<result column="pwd" property="password"/>
</resultMap>
<select id="getUserList" resultMap="userMap">
SELECT * FROM mybatis.user
</select>
<select id="getUserListByRowBounds" resultMap="userMap">
SELECT * FROM mybatis.user
</select>
<!-- 模糊查询1-->
<!--<select id="getUserListForFuzzyQuery" resultType="org.example.pojo.User">
SELECT * FROM mybatis.user where name like #{name}
</select>-->
<!-- 模糊查询2: "%"-->
<select id="getUserListForFuzzyQuery" resultType="org.example.pojo.User">
SELECT * FROM mybatis.user where name like "%"#{name}
</select>
<!-- 形参只有一个,且为基本类型时,parameterType可省略(parameterType="int" )-->
<select id="getUserById" resultType="org.example.pojo.User">
SELECT * FROM mybatis.user where id = #{id}
</select>
<insert id="addUser" parameterType="org.example.pojo.User">
INSERT INTO mybatis.user(id, name, pwd) values (#{id},#{name},#{pwd})
</insert>
<update id="updateUserByUser" parameterType="org.example.pojo.User">
UPDATE mybatis.user set name=#{name}, pwd=#{pwd} where id=#{id}
</update>
<update id="updateUserByMap" parameterType="map">
UPDATE mybatis.user set name=#{userName}, pwd=#{userPwd} where id=#{userId}
</update>
<delete id="deleteUser" parameterType="int">
DELETE FROM mybatis.user where id=#{id}
</delete>
</mapper>
- 具体的标签元素:
- Select:
- 这里的重点是,resultType,resultMap的使用,两者只能二选一
- ResultType:语句中返回结果的类全限定名或别名。一般是该sql映射方法的返回值类型。特殊的,如果是集合类型,则只需定义集合的泛型类型即可。
- ResultMap:对外部 resultMap 的命名引用。一般用于处理复杂的映射结果查询,比如:多表查询(一对多,多对一):
- 这里的重点是,resultType,resultMap的使用,两者只能二选一
- Select:
多对一查询:
<?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="org.example.daos.StudentMapper">
<!-- 多对一查询方式1:Teacher再查一次:子查询
相当于:
select id, name, tid from student where tid = (select id from teacher where id = tid)
-->
<!--<resultMap id="StudentTeacher1" type="Student">
<id property="id" column="id"/>
<result column="name" property="name"/>
<association property="teacher" column="tid" select="getTeacherById" javaType="Teacher"/>
</resultMap>
<select id="getStudentList" resultMap="StudentTeacher1">
select * from student
</select>
<select id="getTeacherById" resultType="Teacher">
select * from teacher where id=#{tid}
</select>-->
<!-- 多对一查询方式2:按照结果嵌套, 联表查询
相当于:
select s.id sid, s.name sname, t.id tid, t.name tname from student s, teacher t where s.tid = t.id
-->
<select id="getStudentList" resultMap="StudentTeacher2">
select s.id sid, s.name sname, t.id tid, t.name tname from student s, teacher t where s.tid = t.id
</select>
<resultMap id="StudentTeacher2" type="Student">
<result property="id" column="sid"/>
<result property="name" column="sname"/>
<association property="teacher" javaType="Teacher">
<id column="id" property="tid"/>
<result property="name" column="tname"/>
</association>
</resultMap>
</mapper>
一对多查询:
<?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="org.example.daos.TeacherMapper">
<!--一对多:方式1:联表查询 -->
<select id="getTeacherById" resultMap="TeacherStudent">
select t.id tid, t.name tname, s.id sid, s.name sname from teacher t, student s where t.id = s.tid and t.id=#{tid}
</select>
<resultMap id="TeacherStudent" type="Teacher">
<result property="id" column="tid"/>
<result property="name" column="tname"/>
<collection property="studentList" ofType="Student">
<result property="id" column="sid"/>
<result property="name" column="sname"/>
<result property="tid" column="tid"/>
</collection>
</resultMap>
<!--一对多:方式2:子查询 -->
<select id="getTeacherById2" resultMap="TeacherStudent2">
select * from teacher where id=#{tid}
</select>
<resultMap id="TeacherStudent2" type="Teacher">
<result column="id" property="id"/>
<collection property="studentList" column="id" ofType="Student" select="getStudentByTid"/>
</resultMap>
<select id="getStudentByTid" resultType="Student">
select * from student where tid=#{tid}
</select>
</mapper>
- Insert:涉及到自动生成主键id的设置(keyProperty, useGeneratedKeys),多行插入(foreach)
- Update:
- Delete:
<!-- 自动生成主键id -->
<insert id="insertAuthor" useGeneratedKeys="true"
keyProperty="id">
insert into Author (username,password,email,bio)
values (#{username},#{password},#{email},#{bio})
</insert>
<!-- 多行插入 -->
<insert id="insertAuthor" useGeneratedKeys="true"
keyProperty="id">
insert into Author (username, password, email, bio) values
<foreach item="item" collection="list" separator=",">
(#{item.username}, #{item.password}, #{item.email}, #{item.bio})
</foreach>
</insert>
<update id="updateAuthor">
update Author set
username = #{username},
password = #{password},
email = #{email},
bio = #{bio}
where id = #{id}
</update>
<delete id="deleteAuthor">
delete from Author where id = #{id}
</delete>
- Sql:sql语句重用片段。也可动态赋值
<sql id="if_title_author">
<if test="title!= null">
AND title = #{title}
</if>
<if test="author != null">
AND author = #{author}
</if>
</sql>
<select id="queryBlog1" parameterType="map">
select * from blog where 1=1
<include refid="if_title_author"/>
</select>
<!-- 动态赋值: ${include_target}, property -->
<sql id="someinclude">
from
<include refid="${include_target}"/>
</sql>
<select id="select" resultType="map">
select
field1, field2, field3
<include refid="someinclude">
<property name="prefix" value="Some"/>
</include>
</select>
- 参数的定义:
- 如果一个列允许使用 null 值,并且会使用值为 null 的参数,就必须要指定 JDBC 类型(jdbcType)
#{average,javaType=double,jdbcType=NUMERIC,typeHandler=MyTypeHandler,numericScale=2}
- 字符串替换: ${}方式不会被预编译转义,可以通过这种方式指定某个字符串column,而非对应的数值。但存在sql注入风险。
@Select("select * from user where ${column} = #{value}")
User findByColumn(@Param("column") String column, @Param("value") String value);
- association & collection :collection 用于一对多,association用于多对一。
association 联表查询
<association property="author" column="blog_author_id" javaType="Author">
<id property="id" column="author_id"/>
<result property="username" column="author_username"/>
</association>
association 子表查询
<resultMap id="blogResult" type="Blog">
<association property="author" column="author_id" javaType="Author" select="selectAuthor"/>
</resultMap>
collection子表查询
<collection property="posts" column="id" ofType="Post" select="selectPostsForBlog"/>
collection联表查询
<resultMap id="blogResult" type="Blog">
<id property="id" column="blog_id" />
<result property="title" column="blog_title"/>
<collection property="posts" ofType="Post">
<id property="id" column="post_id"/>
<result property="subject" column="post_subject"/>
<result property="body" column="post_body"/>
</collection>
</resultMap>
- OfType:
<collection property="posts" javaType="ArrayList" column="id" ofType="Post" select="selectPostsForBlog"/>
可以读作: “posts 是一个存储 Post 的 ArrayList 集合” 。且在一般情况下,MyBatis 可以推断 javaType 属性,因此并不需要填写。
缓存Cache:
- 一级缓存:默认开启。SqlSession级别的缓存,也叫本地缓存
- 二级缓存:基于namespace级别的缓存,针对mapper
<cache>
, LRU, FIFO,开启二级缓存,需要在对于mapper上,加入标签元素<Cache>
即可 - 当会话sqlSession提交commit或关闭close时,一级缓存的数据才会提交到二级缓存中!!!
- 缓存顺序:当查询业务来到DAO层时:
- 先查看二级缓存;
- 再查看一级缓存;
- 最后再查数据库
动态Sql:解决在定义Sql映射时,拼接sql语句:where子句条件,SET子句,多条语句foreach的编写。参考链接
- If, choose, foreach, trim
分页:limit
- Select * from user limit startIndex, pageSize
- RowBounds(
selectList (String statement, Object parameter, RowBounds rowBounds)
) - Mybatis PageHelper
注解开发: 参考链接