orcale 与mysql 适配

拦截器实现

@Intercepts(@Signature(type = StatementHandler.class, method = "prepare",args = {Connection.class,Integer.class}))
public class MyInterceptor implements Interceptor{private String dialect = "mysql";@SuppressWarnings("unchecked")public Object intercept(Invocation invocation) throws Throwable {StatementHandler statementHandler = (StatementHandler) invocation.getTarget();ParameterHandler parameterHandler = statementHandler.getParameterHandler();Map<String,Object> parameterObject = (Map<String,Object>)parameterHandler.getParameterObject();System.out.println(parameterObject);如果请求参数包含"pageBound"参数，则进行分页查询if (parameterObject.containsKey("pageBound")) {Object pageBoundParameter = parameterObject.get("pageBound");if(pageBoundParameter != null && pageBoundParameter instanceof PageBound) {doPaging(invocation, (PageBound)pageBoundParameter);}} 如果请求参数包含"fetchSize"参数，则进行限制记录数处理else if (parameterObject.containsKey("fetchSize")) {Object fetchSizeParameter = parameterObject.get("fetchSize");if (fetchSizeParameter != null && fetchSizeParameter instanceof Integer) {doLimit(invocation, (Integer)fetchSizeParameter);}} return invocation.proceed();}/*** 执行限制记录数处理* @param invocation* @param fetchSize*/private void doLimit(Invocation invocation, int fetchSize) {StatementHandler statementHandler = (StatementHandler) invocation.getTarget();String sql = statementHandler.getBoundSql().getSql();if ("mysql".equals(dialect)) {String limitSql = getMysqlLimitSql(sql, fetchSize);replaceSql(statementHandler, limitSql);} else if ("oracle".equals(dialect)){String limitSql = getOracleLimitSql(sql, fetchSize);replaceSql(statementHandler, limitSql);} else {throw new RuntimeException("不支持的数据库类型:" + dialect);}}/*** 执行分页特殊处理* @param invocation* @param pageBound* @throws SQLException*/private void doPaging(Invocation invocation, PageBound pageBound) throws SQLException {StatementHandler statementHandler = (StatementHandler) invocation.getTarget();ParameterHandler parameterHandler = statementHandler.getParameterHandler();获取当前数据库连接Connection connection = (Connection) invocation.getArgs()[0];获取执行SQLString sql = statementHandler.getBoundSql().getSql();查询总记录数long totalCount = getTotalCount(connection, parameterHandler, sql);将总记录数保持到totalCount字段pageBound.setTotalCount(totalCount);if ("mysql".equals(dialect)) {String pagingSql = getMysqlPagingSql(sql, pageBound);replaceSql(statementHandler, pagingSql);} else if ("oracle".equals(dialect)){String pagingSql = getOraclePagingSql(sql, pageBound);replaceSql(statementHandler, pagingSql);} else {throw new RuntimeException("不支持的数据库类型:" + dialect);}}/*** 用新生成的SQL替换原始的SQL* @param statementHandler* @param newSql*/private void replaceSql(StatementHandler statementHandler, String newSql) {MetaObject metaObject = MetaObject.forObject(statementHandler, SystemMetaObject.DEFAULT_OBJECT_FACTORY,SystemMetaObject.DEFAULT_OBJECT_WRAPPER_FACTORY, new DefaultReflectorFactory());metaObject.setValue("delegate.boundSql.sql", newSql);}/*** select row_.*, rownum _rownum* from ( select * from  user ) _tt  * where _rownum <= startIndex * @param sql* @param fetchSizeParameter* @return*/private String getOracleLimitSql(String sql, Integer fetchSize) {return new StringBuilder("select * from ( select _tt.*, rownum _rownum from (").append(sql).append(") _tt  where rownum <= ").append(fetchSize).toString();}/*** select * from  user limit 0, fetchSize* @param sql* @param fetchSize* @return*/private String getMysqlLimitSql(String sql, int fetchSize) {return new StringBuilder(sql).append(" limit ").append(0).append(",").append(fetchSize).toString();}/*** select * from ( *		select row_.*, rownum _rownum*		from ( select * from  user ) _tt  *		where _rownum <= startIndex * ) _ttt* where _rownum > endIndex* @param sql* @param pageBound* @return*/private String getOraclePagingSql(String sql, PageBound pageBound) {return new StringBuilder("select * from ( select _tt.*, rownum _rownum from (").append(sql).append(") _tt  where rownum <= ").append(pageBound.getEndRowIndex()).append(") _ttt where _rownum > ").append(pageBound.getStartRowIndex()).toString();}/*** select * from  user limit startIndex, endIndex* @param sql* @param pageBound* @return*/private String getMysqlPagingSql(String sql, PageBound pageBound) {return new StringBuilder(sql).append(" limit ").append(pageBound.getStartRowIndex()).append(",").append(pageBound.getPageSize()).toString();}/*** select count(0) from (select * from user) * @param connection* @param parameterHandler* @param sql* @return* @throws SQLException*/private long getTotalCount(Connection connection, ParameterHandler parameterHandler, String sql)throws SQLException {ResultSet resultSet = null;PreparedStatement countStatement = null;try {String countSql = "select count(0) from (" + sql + ") as _ct";countStatement = connection.prepareStatement(countSql);渲染参数parameterHandler.setParameters(countStatement);执行SQL获取总记录数resultSet = countStatement.executeQuery();return (resultSet.next()) ? resultSet.getLong(1) : 0;} finally {if (resultSet != null) {resultSet.close();}if (countStatement != null) {countStatement.close();}}}public Object plugin(Object arg0) {return Plugin.wrap(arg0, this);}public void setProperties(Properties properties) {String dialect = properties.getProperty("dialect");if (dialect != null) {this.dialect = dialect;}}}