mybatis与spring如何集成，批量处理物理分页如何实现，参数为空时如何处理，如何支持jpa注解
mybatis与spring如何集成
批量处理物理分页如何实现
参数为空时如何处理
如何支持jpa注解
n2h24
10 years, 10 months ago
Answers
一、mybatis版本
个人建议使用：
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.1.1</version>
</dependency>
在mybatis3.1.1中优化了批量处理的效率
在底层statementHandle中提供了更加灵活的扩展性
主要是体现在插件，拦截器能做的，能够获取到的参数更多的方面
二、与spring集合的配置
<bean id="sqlSessionFactory"
class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="datasource"></property>
<property name="configLocation" value="classpath:context/mybatis-config.xml"></property>
<property name="mapperLocations" value="classpath*:/com/tx/demo/**/*SqlMap.xml" />
<property name="typeHandlersPackage" value="com.tx.core.mybatis.handler"></property>
<property name="failFast" value="true"></property>
<property name="plugins">
<array>
<bean class="com.tx.core.mybatis.interceptor.PagedDiclectStatementHandlerInterceptor">
<property name="dialect">
<bean class="org.hibernate.dialect.PostgreSQLDialect"></bean>
</property>
</bean>
</array>
</property>
</bean>
<bean id="myBatisExceptionTranslator" class="org.mybatis.spring.MyBatisExceptionTranslator">
<property name="dataSource">
<ref bean="datasource"></ref>
</property>
</bean>
<bean id="sqlSessionTemplate" class="org.mybatis.spring.SqlSessionTemplate">
<constructor-arg name="sqlSessionFactory" ref="sqlSessionFactory"></constructor-arg>
<constructor-arg name="executorType" ref="SIMPLE"></constructor-arg>
<constructor-arg name="exceptionTranslator" ref="myBatisExceptionTranslator"></constructor-arg>
</bean>
<bean id="myBatisDaoSupport" class="com.tx.core.mybatis.support.MyBatisDaoSupport">
<property name="sqlSessionTemplate">
<ref bean="sqlSessionTemplate"/>
</property>
</bean>
这里对配置进行一下说明
mapperLocations： 通过正则表达式，支持mybatis动态扫描添加mapper不用像ibatis，用一个还要蛋疼滴添加一个include
typeHandlersPackage: 由于mybatis默认入参如果为空，又没有指定jdbcType时会抛出异常，在这里通过配置一些默认的类型空值插入的handle，以便处理mybatis的默认类型为空的情况
例如NullAbleStringTypeHandle通过实现当String字符串中为null是调用ps.setString(i,null)其他常用类型雷同
failFast：开启后将在启动时检查设定的parameterMap,resultMap是否存在，是否合法。个人建议设置为true,这样可以尽快定位解决问题。不然在调用过程中发现错误，会影响问题定位。
myBatisExceptionTranslator：用以支持spring的异常转换，通过配置该translatro可以将mybatis异常转换为spring中定义的DataAccessException
三、mybatis的批量处理功能，
由于在3.1.1升级后，可直接通过batchExcutor实现具体的批量执行。在该excutor中会重用上一次相同的prepareStatement。
具体实现这里贴一个：
/**
* 批量插入数据 <br/>
* 1、数据批量插入，默认一次提交100条，当发生异常后继续提交异常行以后的数据，待集合全部进行提交后返回批量处理结果<br/>
* 2、数据批量插入，如果需要回滚，当发生异常后，数据库异常即向外抛出，不会进行至全部执行后再抛出异常 <br/>
* <功能详细描述>
*
* @param statement
* @param objectCollection
* @param isRollback
* @return [参数说明]
*
* @return BatchResult<T> [返回类型说明]
* @exception throws [异常类型] [异常说明]
* @see [类、类#方法、类#成员]
*/
public BatchResult batchInsert(String statement, List<?> objectList,
boolean isStopWhenFlushHappenedException) {
return batchInsert(statement,
objectList,
defaultDoFlushSize,
isStopWhenFlushHappenedException);
}
/**
* 批量插入数据
*
* @param statement
* @param objectList
* 对象列表
* @param doFlushSize
* @param isStopWhenFlushHappenedException
* 当在flush发生异常时是否停止，如果在调用insert时抛出的异常，不在此设置影响范围内
* @return void [返回类型说明]
* @exception throws [异常类型] [异常说明]
* @see [类、类#方法、类#成员]
*/
// 批量插入
public BatchResult batchInsert(String statement, List<?> objectList,
int doFlushSize, boolean isStopWhenFlushHappenedException) {
BatchResult result = new BatchResult();
if (CollectionUtils.isEmpty(objectList)) {
return result;
}
if (doFlushSize <= 0) {
doFlushSize = defaultDoFlushSize;
}
//设置总条数
result.setTotalNum(objectList.size());
//从当前环境中根据connection生成批量提交的sqlSession
SqlSession sqlSession = this.sqlSessionTemplate.getSqlSessionFactory()
.openSession(ExecutorType.BATCH);
try {
// 本次flush的列表开始行行索引
int startFlushRowIndex = 0;
for (int index = 0; index < objectList.size(); index++) {
// 插入对象
insertForBatch(sqlSession,
statement,
objectList.get(index),
null);
if ((index > 0 && index % doFlushSize == 0)
|| index == objectList.size() - 1) {
try {
List<org.apache.ibatis.executor.BatchResult> test = flushBatchStatements(sqlSession);
System.out.println(test);
startFlushRowIndex = index + 1;
}
catch (Exception ex) {
if (!(ex.getCause() instanceof BatchExecutorException)
|| isStopWhenFlushHappenedException) {
DataAccessException translated = this.sqlSessionTemplate.getPersistenceExceptionTranslator()
.translateExceptionIfPossible((PersistenceException) ex);
throw translated;
}
BatchExecutorException e = (BatchExecutorException) ex.getCause();
// 如果为忽略错误异常则记录警告日志即可，无需打印堆栈，如果需要堆栈，需将日志级别配置为debug
logger.warn("batchInsert hanppend Exception:{},the exception be igorned.",
ex.toString());
if (logger.isDebugEnabled()) {
logger.debug(ex.toString(), ex);
}
// 获取错误行数，由于错误行发生的地方
int errorRownumIndex = startFlushRowIndex
+ e.getSuccessfulBatchResults().size();
result.addErrorInfoWhenException(objectList.get(index),
errorRownumIndex,
ex);
//将行索引调整为错误行的行号，即从发生错误的行后面一行继续执行
index = errorRownumIndex;
startFlushRowIndex = errorRownumIndex + 1;
}
}
}
}
finally {
sqlSession.close();
}
return result;
}
这里的实现写得稍微复杂一些，
主要是，针对有些情况如果其中某条失败，还想后续数据能够继续成功提交的情况进行支持。
四、数据库物理分页，这个网上的文章也比较多，这里也提一下。在前人的基础上，我的物理分页类实现为：
/*
* 描 述: <描述>
* 修 改 人: PengQingyang
* 修改时间: 2012-11-5
* <修改描述:>
*/
package com.tx.core.mybatis.interceptor;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.util.Properties;
import org.apache.ibatis.executor.statement.PreparedStatementHandler;
import org.apache.ibatis.executor.statement.RoutingStatementHandler;
import org.apache.ibatis.executor.statement.SimpleStatementHandler;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Plugin;
import org.apache.ibatis.plugin.Signature;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.session.RowBounds;
import org.hibernate.dialect.Dialect;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
/**
* <数据库分页容器处理器>
* <功能详细描述>
*
* @author PengQingyang
* @version [版本号, 2012-11-5]
* @see [相关类/方法]
* @since [产品/模块版本]
*/
@Intercepts({
@Signature(type = StatementHandler.class, method = "prepare", args = { Connection.class }),
@Signature(type = StatementHandler.class, method = "parameterize", args = { Statement.class }) })
public class PagedDiclectStatementHandlerInterceptor implements Interceptor {
private Logger logger = LoggerFactory.getLogger(PagedDiclectStatementHandlerInterceptor.class);
private Dialect dialect;
/**
* 物理分页插件拦截
* @param invocation
* @return
* @throws Throwable
*/
public Object intercept(Invocation invocation) throws Throwable {
Method m = invocation.getMethod();
if ("prepare".equals(m.getName())) {
return prepare(invocation);
} else if ("parameterize".equals(m.getName())) {
return parameterize(invocation);
}
return invocation.proceed();
}
/**
* @param target
* @return
*/
public Object plugin(Object target) {
return Plugin.wrap(target, this);
}
/**
* @param properties
*/
public void setProperties(Properties properties) {
}
/**
* 拦截prepare修改分页
* <功能详细描述>
* @param invocation
* @return
* @throws Throwable [参数说明]
*
* @return Object [返回类型说明]
* @exception throws [异常类型] [异常说明]
* @see [类、类#方法、类#成员]
*/
private Object prepare(Invocation invocation) throws Throwable {
if (!(invocation.getTarget() instanceof RoutingStatementHandler)) {
return invocation.proceed();
}
//提取statement
RoutingStatementHandler statementHandler = (RoutingStatementHandler) invocation.getTarget();
MetaObject metaStatementHandler = MetaObject.forObject(statementHandler);
StatementHandler statement = (StatementHandler) metaStatementHandler.getValue("delegate");
//如果不为两种statement则不继续进行处理
if (!(statement instanceof SimpleStatementHandler)
&& !(statement instanceof PreparedStatementHandler)) {
return invocation.proceed();
}
RowBounds rowBounds = (RowBounds) metaStatementHandler.getValue("delegate.rowBounds");
//根据rowBounds判断是否需要进行物理分页
if (rowBounds == null
|| rowBounds.equals(RowBounds.DEFAULT)
|| (rowBounds.getOffset() <= RowBounds.NO_ROW_OFFSET && rowBounds.getLimit() == RowBounds.NO_ROW_LIMIT)) {
return invocation.proceed();
}
//进行处理
BoundSql boundSql = statementHandler.getBoundSql();
String sql = boundSql.getSql();
String limitSql = dialect.getLimitString(sql,
rowBounds.getOffset(),
rowBounds.getLimit());
if (statement instanceof SimpleStatementHandler) {
limitSql.replaceAll("rownum <= ?", "rownum <= " + rowBounds.getLimit());
limitSql.replaceAll("rownum_ > ?", "rownum_ > " + rowBounds.getOffset());
}
//如果为PreparedStatementHandler则无需替换即可
metaStatementHandler.setValue("delegate.boundSql.sql",limitSql);
if (logger.isDebugEnabled()) {
logger.debug("生成分页SQL : " + boundSql.getSql());
}
return invocation.proceed();
}
/**
* 设置分页参数
* <功能详细描述>
* @param invocation
* @return
* @throws Throwable [参数说明]
*
* @return Object [返回类型说明]
* @exception throws [异常类型] [异常说明]
* @see [类、类#方法、类#成员]
*/
private Object parameterize(Invocation invocation) throws Throwable {
//先执行系统默认的参数设置
Object returnObj = invocation.proceed();
//提取statement
RoutingStatementHandler routingStatementHandler = (RoutingStatementHandler) invocation.getTarget();
MetaObject metaStatementHandler = MetaObject.forObject(routingStatementHandler);
StatementHandler statementHandler = (StatementHandler) metaStatementHandler.getValue("delegate");
//如果不为两种statement则不继续进行处理
if (!(statementHandler instanceof PreparedStatementHandler)) {
return returnObj;
}
RowBounds rowBounds = (RowBounds) metaStatementHandler.getValue("delegate.rowBounds");
//根据rowBounds判断是否需要进行物理分页
if (rowBounds == null
|| rowBounds.equals(RowBounds.DEFAULT)
|| (rowBounds.getOffset() <= RowBounds.NO_ROW_OFFSET && rowBounds.getLimit() == RowBounds.NO_ROW_LIMIT)) {
return returnObj;
}
//提取参数设置statement
Statement statement = (Statement) invocation.getArgs()[0];
if (!(statement instanceof PreparedStatement)) {
//如果对应statement不为PreparedStatement则直接返回
return returnObj;
}
//设置分页的参数
PreparedStatement ps = (PreparedStatement) statement;
int parameterSize = statementHandler.getBoundSql().getParameterMappings().size();
if(rowBounds.getOffset() > RowBounds.NO_ROW_OFFSET
|| rowBounds.getLimit() < RowBounds.NO_ROW_LIMIT){
ps.setInt(parameterSize + 1, rowBounds.getLimit());
parameterSize++;
if(rowBounds.getOffset() > RowBounds.NO_ROW_OFFSET){
ps.setInt(parameterSize + 1, rowBounds.getOffset());
}
}
//替换rowBounds
metaStatementHandler.setValue("delegate.rowBounds",
RowBounds.DEFAULT);
return returnObj;
}
/**
* @return 返回 dialect
*/
public Dialect getDialect() {
return dialect;
}
/**
* @param 对dialect进行赋值
*/
public void setDialect(Dialect dialect) {
this.dialect = dialect;
}
}
五、jpa注解的支持，mybatis本来优势就在于其sql可控，
个人不太倾向用太多的注解，mybatis的resultMap已经在ibatis基础上改进了很多，已经非常好用了，个人倾向于，利用表生成对应的model
然后根据实际业务将model改写，如需要oneToOne manyToOne时，添加jpa注解，调整bean属性命名后，根据model直接生成，dao,service,daoImpl,sqlMap,以及单元测试类
这个我现在有一个初步实现：
已经可以用了，大家可以一起使用一下，如果有问题，可以在这里回帖，我再进行逐步优化：
具体代码请在github上下载，建议使用eclipse版本为spring的sts
sqlMap后来考虑重用statemen有关null处已经进行调整，将于明天更新于github
有兴趣的可以从github下载相关代码：
https://github.com/txteam/tx-core/tre...
这里贴下生成代码：
生成代码使用：
public static void main(String[] args) throws Exception{
JpaEntityFreeMarkerGenerator g = new JpaEntityFreeMarkerGenerator();
g.generate(Demo.class, "d:/mybatis");
}
生成sqlMap:
<?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="demo">
<!-- auto generate default resultMap -->
<resultMap id="demoMap"
type="com.tx.core.mybatis.generator.model.Demo">
<result column="subDemo_id" property="subDemo.id"/>
</resultMap>
<!-- auto generate default find -->
<select id="findDemo"
parameterType="com.tx.core.mybatis.generator.model.Demo"
resultMap="demoMap">
SELECT
TW.ID,
TW.INTTEST,
TW.BOOLEANTEST,
TW.PASSOWRD,
TW.ENDDATE,
TW.ISBOOLEANOBJTEST,
TW.LASTUPDATEDATE,
TW.TESTBIGDECEIMAL,
TW.INTEGERTEST,
TW.EMAIL,
TW.NAME,
TW.SUBDEMO AS subDemo_id,
TW.TESTINTEGER,
TW.CREATEDATE
FROM WD_DEMO TW
where
<trim prefixOverrides="AND | OR">
AND TW.ID = #{id}
</if>
</trim>
</select>
<!-- auto generate default query -->
<select id="queryDemo"
parameterType="com.tx.core.mybatis.generator.model.Demo"
resultMap="demoMap">
SELECT
TW.ID,
TW.INTTEST,
TW.BOOLEANTEST,
TW.PASSOWRD,
TW.ENDDATE,
TW.ISBOOLEANOBJTEST,
TW.LASTUPDATEDATE,
TW.TESTBIGDECEIMAL,
TW.INTEGERTEST,
TW.EMAIL,
TW.NAME,
TW.SUBDEMO AS subDemo_id,
TW.TESTINTEGER,
TW.CREATEDATE
FROM WD_DEMO TW
<trim prefix="WHERE" prefixOverrides="AND | OR">
AND TW.ID = #{id}
</if>
AND TW.INTTEST = #{intTest}
</if>
AND TW.BOOLEANTEST = #{booleanTest}
</if>
AND TW.PASSOWRD = #{passowrd}
</if>
AND TW.ENDDATE = #{endDate}
</if>
AND TW.ISBOOLEANOBJTEST = #{isBooleanObjTest}
</if>
AND TW.LASTUPDATEDATE = #{lastUpdateDate}
</if>
AND TW.TESTBIGDECEIMAL = #{testBigDeceimal}
</if>
AND TW.INTEGERTEST = #{integerTest}
</if>
AND TW.EMAIL = #{email}
</if>
AND TW.NAME = #{name}
</if>
<if test="subDemo != null">
AND TW.SUBDEMO = #{subDemo.id}
</if>
</if>
AND TW.TESTINTEGER = #{testInteger}
</if>
AND TW.CREATEDATE = #{createDate}
</if>
</trim>
</select>
<!-- auto generate default insert -->
<insert id="insertDemo"
parameterType="com.tx.core.mybatis.generator.model.Demo">
INSERT INTO WD_DEMO
(
ID,
INTTEST,
BOOLEANTEST,
PASSOWRD,
ENDDATE,
ISBOOLEANOBJTEST,
LASTUPDATEDATE,
TESTBIGDECEIMAL,
INTEGERTEST,
EMAIL,
NAME,
SUBDEMO,
TESTINTEGER,
CREATEDATE
)
VALUES
(
#{id},
#{intTest},
#{booleanTest},
#{passowrd},
#{endDate},
#{isBooleanObjTest},
#{lastUpdateDate},
#{testBigDeceimal},
#{integerTest},
#{email},
#{name},
<if test="subDemo != null">
#{subDemo.id},
</if>
<if test="subDemo == null">
null,
</if>
#{testInteger},
#{createDate}
)
</insert>
<!-- auto generate default delete -->
<delete id="insertDemo"
parameterType="com.tx.core.mybatis.generator.model.Demo">
DELETE FROM WD_DEMO TW WHERE
<trim prefixOverrides="and | or">
AND TW.ID = #{id}
</if>
</trim>
</delete>
</mapper>
<!--
sqlMap生成描述:
-->
由于字数限制：
生成的daoIml:略
生成的dao:略
生成的业务层：略
生成的单元测试类：略
熊吉666
answered 10 years, 10 months ago