基本原理
将sql语句写入到xml文件中，为这个xml文件提供统一的接口来操作数据库
引入整合
-
引入两个驱动包
Paste_Image.png - 在bean.xml中注册
sqlSessionFactory(相当于jdbc的connection)
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource"></property>
<property name="typeAliasesPackage" value="com.gavin.exam.model"></property>
<property name="mapperLocations" value="classpath*:com/gavin/exam/model/*Mapper.xml" />
</bean>
其中typeAliasesPackage 为类型别名包目录
mapperLocations为sql语句映射xml文件目录
- 注册
sqlSessionTemplate
<bean id="sqlSessionTemplate" class="org.mybatis.spring.SqlSessionTemplate">
<constructor-arg index="0" ref="sqlSessionFactory"></constructor-arg>
</bean>
- 注册
dao父类bean和实现bean
<bean id="baseDao" abstract="true">
<property name="sqlSessionTemplate" ref="sqlSessionTemplate"></property>
</bean>
<bean id="userDao" class="com.gavin.exam.dao.mybatis.UserDaoImpl" parent="baseDao"/>
- model包下结构如下
Paste_Image.png
- 编写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="com.gavin.exam.model.User">
<!--type 没有写全类名的原因是在bean.xml文件下定义了typeAliasesPackage-->
<resultMap type="User" id="userMap">
<!--column 为数据库中的类表 property 为model中字段-->
<id column="id" property="id"/>
<result column="user_name" property="userName"/>
</resultMap>
<select id="getUserByName" parameterType="String" resultMap="userMap">
<![CDATA[
SELECT * FROM user WHERE user_name = LOWER(#{userName})
]]>
</select>
</mapper>
其中select 中定义增删改查的语句
属性resultMap定义了返回map
实现
- DAO要extends
SqlSessionDaoSupport
public class UserDaoImpl extends SqlSessionDaoSupport implements UserDao{
private static final String CLASS_NAME = User.class.getName();
private static final String SQL_ID_USER_GET_USER_BY_NAME = ".getUserByName";
@Override
public User getUserByName(String userName) {
return getSqlSession().selectOne(CLASS_NAME + SQL_ID_USER_GET_USER_BY_NAME, userName);
}
}
其中CLASS_NAME对应mapper中的namespace
SQL_ID_USER_GET_USER_BY_NAME对应<select id="getUserByName"中的id
各类节点
insert
map方法定义
<insert id="createQuestion" useGeneratedKeys="true" keyProperty="id" parameterType="Question" >
<![CDATA[
Insert INTO question(display_id, description, created_time, updated_time)
VALUES(#{displayId}, #{description}, NOW(), NOW())
]]>
</insert>
自增长的id会自动放入传入的对象中
DAO中实现
@Override
public void createQuestion(Question question) {
getSqlSession().insert(QUESTION_CLASS_NAME+SQL_ID_CREATE_QUESTION, question);
}
此时book.getId()就能获得自增长id
delete
map方法定义
<delete id="delete" parameterType="int">
<![CDATA[
DELETE FROM question WHERE id = #{id}
]]>
</delete>
update
map方法定义
<update id="updateQuestion" parameterType="Question">
<![CDATA[
UPDATE question
SET
description = #{description},
updated_time = NOW()
WHERE
id = #{id}
]]>
</update>
动态sql
动态sql配置语句
- if
- where
- trim
- set
- choose(when, otherwise)
- foreach
- sql/include
foreach的使用
Paste_Image.png
如图就会循环插入
实例：根据某个状态来增加动态增加查询条件
<select id="getQuestionCount" resultType="int" parameterType="String">
<![CDATA[
SELECT count(*) AS count FROM question WHERE 1=1
]]>
<include refid=""statusSQL""/>
</select>
动态条件定义
<sql id=""statusSQL"">
<if test="status == 'deleted'">
<![CDATA[
AND is_deleted = 1
]]>
</if>
<if test="status == 'undeleted'">
<![CDATA[
AND is_deleted = 0
]]>
</if>
</sql>
其中sql节点用来定义公用的sql语句
同样这段代码可以直接写进getQuestionCount中
<select id="getQuestionCount" resultType="int" parameterType="String">
<![CDATA[
SELECT count(*) AS count FROM question WHERE 1=1
]]>
<if test="status == 'deleted'">
<![CDATA[
AND is_deleted = 1
]]>
</if>
<if test="status == 'undeleted'">
<![CDATA[
AND is_deleted = 0
]]>
</if>
</select>
使用总结
如果是在function中传两个以上的参数，那么可以将参数拼接成一个map传到sql节点中
xml中接收参数类型为parameterType="map"
其中#会根据你参数的类型做处理
而$不会，只会将你的值替换，所以当写入为字符串时记得加上单引号。所以尽量使用# 当使用like时可以使用$
