简介
MyBatis是一个支持普通SQL查询,存储过程和高级映射的优秀持久层框架。MyBatis消除了几乎所有的JDBC代码和参数的手工设置以及对结果集的检索封装。MyBatis可以使用简单的XML或注解用于配置和原始映射,将接口和Java的POJO(Plain Old Java Objects,普通的Java对象)映射成数据库中的记录。
配置
mybatis-config.xml
<?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>
<settings>
<setting name="lazyLoadingEnabled" value="false" />
<!-- 是否将查询结果中的null值注入map -->
<setting name="callSettersOnNulls" value="true" />
</settings>
<typeAliases>
<typeAlias alias="int" type="java.lang.Integer"/>
<typeAlias alias="string" type="java.lang.String" />
<typeAlias alias="map" type="java.util.Map" />
<typeAlias alias="list" type="java.util.List" />
</typeAliases>
<mappers>
</mappers>
</configuration>
applicationContext.xml
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:aop="http://www.springframework.org/schema/aop"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:mybatis-spring="http://mybatis.org/schema/mybatis-spring"
xmlns:tx="http://www.springframework.org/schema/tx"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.1.xsd
http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.1.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.1.xsd
http://mybatis.org/schema/mybatis-spring http://mybatis.org/schema/mybatis-spring-1.2.xsd
http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.1.xsd">
<!-- 导入资源文件 -->
<context:property-placeholder location="classpath:jdbc.properties" />
<!-- 配置C3P0数据源 -->
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="user" value="${jdbc.user}"></property>
<property name="password" value="${jdbc.password}"></property>
<property name="driverClass" value="${jdbc.driverClass}"></property>
<property name="jdbcUrl" value="${jdbc.jdbcUrl}"></property>
<property name="initialPoolSize" value="${jdbc.initPoolSize}"></property>
<property name="maxPoolSize" value="${jdbc.maxPoolSize}"></property>
</bean>
<!-- 配置SessionFactory,加载mybatis配置文件 -->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource"></property>
<property name="configLocation" value="classpath:mybatis-config.xml"></property>
</bean>
<!-- 事务配置 -->
<bean id="transactionManager"
class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource" />
</bean>
<!-- 使用annotation注解方式配置事务 -->
<tx:annotation-driven transaction-manager="transactionManager" />
<!-- 使用annotation装配bean需要写@Service("dbObjectService") @Resource(name="dbObjectService") -->
<context:component-scan base-package="com.aiutil">
<context:include-filter type="regex"
expression=".*.services.*" />
</context:component-scan>
<!-- 使用MapperScannerConfigurer Mybatis装载DAO层 @Autowired -->
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<!-- sqlSessionFactoryBeanName 这个名字不能更换 -->
<property name="sqlSessionFactoryBeanName" value="sqlSessionFactory" />
<property name="basePackage" value="com.aiutil" />
</bean>
</beans>
工作流
框架架构讲解:
(1)加载配置:配置来源于两个地方,一处是配置文件,一处是Java代码的注解,将SQL的配置信息加载成为一个
mybatis结构
mybatis结构
个MappedStatement对象(包括了传入参数映射配置、执行的SQL语句、结果映射配置),存储在内存中。
(2)SQL解析:当API接口层接收到调用请求时,会接收到传入SQL的ID和传入对象(可以是Map、JavaBean或者基本数据类型),Mybatis会根据SQL的ID找到对应的MappedStatement,然后根据传入参数对象对MappedStatement进行解析,解析后可以得到最终要执行的SQL语句和参数。
(3)SQL执行:将最终得到的SQL和参数拿到数据库进行执行,得到操作数据库的结果。
(4)结果映射:将操作数据库的结果按照映射的配置进行转换,可以转换成HashMap、JavaBean或者基本数据类型,并将最终结果返回。
示例
- resultMap定义
resultMap用于在数据库表中存在clob,blog等类型字段时,使用相应的typeHandler来进行处理。
<resultMap type="com.aiutil.report.entities.ReportModel" id="reportModel">
<result property="rptId" column="RPT_ID"/>
<result property="modelId" column="MODEL_ID"/>
<result property="modelName" column="MODEL_NAME"/>
<result property="modelPath" column="MODEL_PATH"/>
<result property="createrName" column="CREATER_NAME"/>
<result property="ifPage" column="IF_PAGE"/>
<result property="modelCss" column="MODEL_CSS" jdbcType="CLOB" javaType="java.lang.String"/>
<result property="modelHtml" column="MODEL_HTML" jdbcType="CLOB" javaType="java.lang.String"/>
</resultMap>
<select id="qryReportModelDao" parameterType="map" resultMap="reportModel">
SELECT T.RPT_ID,
T.MODEL_ID,
S.MODEL_NAME,
S.MODEL_PATH,
S.CREATER_NAME,
S.IF_PAGE,
S.MODEL_CSS,
S.MODEL_HTML
FROM RPT_REPORT_MODEL T, RPT_MODEL_INFO S
WHERE T.RPT_ID = #{rptId}
AND T.MODEL_ID = S.MODEL_ID
AND T.STATUS = '1'
</select>
- java.util.LinkedHashMap
在需要返回的结果集中保留null值字段时,可以在mybatis-config.xml增加
<setting name="callSettersOnNulls" value="true" />
并且使执行结果返回类型定义为java.util.LinkedHashMap,即可
<select id="qryDataBySqlDao" parameterType="map" resultType="java.util.LinkedHashMap">
SELECT T.* FROM (
SELECT T.*, ROWNUM RN FROM (
${sql}
) T) T
<if test="ifPage == '1'.toString()">
WHERE T.RN <![CDATA[>=]]> #{startIndex}
AND T.RN <![CDATA[<=]]> #{endIndex}
</if>
</select>
- 运算符
>= 使用 <![CDATA[>=]]>表示,> < <=等类推
- 条件语句
条件逻辑运算为and or与sql一致,对于字符串与数值的比较需要加上.toString() 例 id != ‘0’.toString()
<select id="qryDownloadInfoListDao" parameterType="map" resultType="java.util.HashMap">
SELECT T.* FROM (
SELECT T.*, ROWNUM RN FROM (
SELECT ID,
RPT_ID,
RPT_NAME,
TO_CHAR(CREATE_DATE, 'YYYY-MM-DD HH24:MI:SS') CREATE_DATE,
FILE_PATH,
decode(FILE_STATUS, '1', '下载完成', '2', '已查看') FILE_STATUS,
CREATER_NAME,
CDT_DESC
FROM RPT_DOWNLOAD_INFO T
WHERE 1=1
<if test="rptName!=null and rptName!=''">
AND T.RPT_NAME LIKE '%${rptName}%'
</if>
<if test="createrName!=null and createrName!=''">
AND T.CREATER_NAME LIKE '%{createrName}%'
</if>
ORDER BY T.CREATE_DATE DESC
) T) T
WHERE T.RN <![CDATA[>=]]> #{startIndex}
AND T.RN <![CDATA[<=]]> #{endIndex}
</select>
- IN条件中的值
foreach 中的值循环取各个值,collection中为数组或者list均可以
<select id="qryRptFieldListDao" parameterType="map" resultType="java.util.HashMap">
SELECT T.RPT_ID,
T.FIELD_ID,
T.FIELD_CODE,
T.FIELD_NAME,
S.FIELD_TYPE,
S.FIELD_LENGTH,
T.DISPLAY_ORDER,
T.IS_FIXED,
T.IS_DIM,
T.IS_ORDER_COL,
T.IS_SHOW,
T.GROUP_VALUE,
A.TB_OWNER,
A.TB_CODE
FROM RPT_REPORT_FIELD T, RPT_TABLE_FIELD S, RPT_TABLE_INFO A
WHERE T.RPT_ID = #{rptId}
AND T.FIELD_ID = S.FIELD_ID
AND S.TB_ID = A.TB_ID
AND A.TB_STATUS = '1'
AND S.FIELD_STATUS = '1'
<if test="rptFieldIdArray != null and rptFieldIdArray.length != 0">
AND T.FIELD_ID IN
<foreach collection="rptFieldIdArray" item="rptFieldId" open="(" separator="," close=")">
#{rptFieldId}
</foreach>
</if>
ORDER BY T.RPT_ID, T.DISPLAY_ORDER
</select>
- clob字段的值写入
需要指定jdbcType=CLOB
<insert id="insertModelInfoDao" parameterType="map">
INSERT INTO RPT_MODEL_INFO
(
MODEL_ID,
MODEL_NAME,
MODEL_PATH,
CREATE_DATE,
CREATER_NAME,
MODEL_CSS,
MODEL_HTML
)VALUES
(
#{modelId},
#{modelName},
#{modelPath},
SYSDATE,
#{createrName},
#{modelCss, jdbcType=CLOB},
#{modelHtml, jdbcType=CLOB}
)
</insert>
- #与
$
的区别
如果当前位置需要由mybatis自动去匹配类型,一般建议所有的字段输入均采用#的方式
如果需要拼入字符串sql语句或者需要写入特定的值时才使用$
示例
<select id="qryReportDataListDao" parameterType="map" resultType="java.util.HashMap">
SELECT T.* FROM (
SELECT T.*, ROWNUM RN FROM (
SELECT ${selectFieldList}
FROM ${tableName} T
WHERE 1=1
<foreach collection="whereSqlList" index="index" item="whereSql">
AND ${whereSql}
</foreach>
<if test="groupByFieldList != null and groupByFieldList != ''">
GROUP BY ${groupByFieldList}
</if>
<if test="orderByFieldList != null and orderByFieldList != ''">
ORDER BY ${orderByFieldList}
</if>
) T) T
WHERE T.RN <![CDATA[>=]]> #{startIndex}
AND T.RN <![CDATA[<=]]> #{endIndex}
</select>