虚谷数据库整合mybatisplus问题总结

虚谷数据库整合mybatisplus问题总结

一、 表名前是否增加模式名（表或视图不存在 ）：

1. 场景：

例如： url: jdbc:xugu://127.0.0.1:5138/CMA?return_rowid=true username: SYSDBA password: SYSDBA 用户名为SYSDBA，在不指定具体访问哪个模式的情况下，默认会访问CMA库下的模式为SYSDBA下的表， 如果我们的用户为SYSDBA，我们想访问的表也在SYSDBA模式下，不用加模式名，否则就得加模式名。 注意：虚谷数据库中不同于mysql，数据库下面就是表、视图等，数据库下还有一层是模式， 模式下面才是表、视图等。

2. 解决方案：

2.1 @TableName(value = “模式名.表名”)

这中解决方案有局限性，如果我们依赖公司架构开发的基础包（基础框架），我们不可能改变别人的代码， 所以在这种情况下并不适用。

2.2 实现Interceptor接口

通过拦截所有sql语句，在表名前面加上模式名

import java.sql.SQLException; import java.util.Properties; import org.apache.commons.lang3.StringUtils; import org.apache.ibatis.cache.CacheKey; import org.apache.ibatis.executor.Executor; import org.apache.ibatis.mapping.BoundSql; import org.apache.ibatis.mapping.MappedStatement; import org.apache.ibatis.mapping.SqlCommandType; import org.apache.ibatis.mapping.SqlSource; 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.DefaultReflectorFactory; import org.apache.ibatis.reflection.MetaObject; import org.apache.ibatis.reflection.factory.DefaultObjectFactory; import org.apache.ibatis.reflection.wrapper.DefaultObjectWrapperFactory; import org.apache.ibatis.session.ResultHandler; import org.apache.ibatis.session.RowBounds; /** * @author shuo_ */ @Intercepts(value = { @Signature(type = Executor.class, method = "update", args = {MappedStatement.class, Object.class}), @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class, CacheKey.class, BoundSql.class}), @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class})}) public class MybatisSqlInterceptor implements Interceptor { @Override public Object intercept(Invocation invocation) throws Throwable { // 获取sql String sql = getSqlByInvocation(invocation); sql = sql.replaceAll("(?i)CMA.", "").replaceAll("[\\t\\n\\r]", " ").replaceAll(" +", " "); sql = sql.replaceAll("(?i) FROM ", " FROM CMA."); sql = sql.replaceAll("(?i)INSERT INTO", " INSERT INTO CMA."); sql = sql.replaceAll("(?i)UPDATE ", " UPDATE CMA."); if (StringUtils.isBlank(sql)) { return invocation.proceed(); } // sql交由处理类处理 对sql语句进行处理 此处是范例 不做任何处理 String sql2Reset = sql; // 包装sql后，重置到invocation中 resetSql2Invocation(invocation, sql2Reset); // 返回，继续执行 return invocation.proceed(); } @Override public Object plugin(Object obj) { return Plugin.wrap(obj, this); } @Override public void setProperties(Properties arg0) { // doSomething } /** * 获取sql语句 * * @param invocation * @return */ private String getSqlByInvocation(Invocation invocation) { final Object[] args = invocation.getArgs(); MappedStatement ms = (MappedStatement) args[0]; Object parameterObject = args[1]; BoundSql boundSql = ms.getBoundSql(parameterObject); return boundSql.getSql(); } /** * 包装sql后，重置到invocation中 * * @param invocation * @param sql * @throws SQLException */ private void resetSql2Invocation(Invocation invocation, String sql) throws SQLException { final Object[] args = invocation.getArgs(); MappedStatement statement = (MappedStatement) args[0]; Object parameterObject = args[1]; BoundSql boundSql = statement.getBoundSql(parameterObject); MappedStatement newStatement = newMappedStatement(statement, new BoundSqlSqlSource(boundSql)); MetaObject msObject = MetaObject.forObject(newStatement, new DefaultObjectFactory(), new DefaultObjectWrapperFactory(), new DefaultReflectorFactory()); msObject.setValue("sqlSource.boundSql.sql", sql); args[0] = newStatement; } private MappedStatement newMappedStatement(MappedStatement ms, SqlSource newSqlSource) { MappedStatement.Builder builder = new MappedStatement.Builder(ms.getConfiguration(), ms.getId(), newSqlSource, ms.getSqlCommandType()); builder.resource(ms.getResource()); builder.fetchSize(ms.getFetchSize()); builder.statementType(ms.getStatementType()); builder.keyGenerator(ms.getKeyGenerator()); if (ms.getKeyProperties() != null && ms.getKeyProperties().length != 0) { StringBuilder keyProperties = new StringBuilder(); for (String keyProperty : ms.getKeyProperties()) { keyProperties.append(keyProperty).append(","); } keyProperties.delete(keyProperties.length() - 1, keyProperties.length()); builder.keyProperty(keyProperties.toString()); } builder.timeout(ms.getTimeout()); builder.parameterMap(ms.getParameterMap()); builder.resultMaps(ms.getResultMaps()); builder.resultSetType(ms.getResultSetType()); builder.cache(ms.getCache()); builder.flushCacheRequired(ms.isFlushCacheRequired()); builder.useCache(ms.isUseCache()); return builder.build(); } private String getOperateType(Invocation invocation) { final Object[] args = invocation.getArgs(); MappedStatement ms = (MappedStatement) args[0]; SqlCommandType commondType = ms.getSqlCommandType(); if (commondType.compareTo(SqlCommandType.SELECT) == 0) { return "select"; } if (commondType.compareTo(SqlCommandType.INSERT) == 0) { return "insert"; } if (commondType.compareTo(SqlCommandType.UPDATE) == 0) { return "update"; } if (commondType.compareTo(SqlCommandType.DELETE) == 0) { return "delete"; } return null; } // 定义一个内部辅助类，作用是包装sq class BoundSqlSqlSource implements SqlSource { private BoundSql boundSql; public BoundSqlSqlSource(BoundSql boundSql) { this.boundSql = boundSql; } @Override public BoundSql getBoundSql(Object parameterObject) { return boundSql; } } }

将sql拦截器注入到spring容器中

@Bean public MybatisSqlInterceptor mybatisSqlInterceptor() { return new MybatisSqlInterceptor(); }

2.3 将用户名和模式名统一

创建用户的时候，默认会创建一个和用户同名的模式，该模式初始情况下不可见的， 用这个用户任意创建一个对象以后，变为可见。将我们的表全部放在同名的模式下，这样也可以解决这个问题 对象就是表、视图等。

二、 Java和数据库数据类型不对应：

1. 场景：

Java中数据类型是LocalDateTime，数据库中数据类型是TimeStamp， 出现错误后的截图，很奇怪的错误：at least one parameter is not set by setXXX() method， 当时出现这个错误没有往数据类型上面想

2. 解决方案：（此解决方案为此场景下，遇到其他问题具体分析）

2.1 更改java数据类型

将java数据类型更改为String或Date，可以对应数据库中的TimeStamp 此解决方案和（一、2.1）下的具有同样的局限性

2.2 自定义TypeHandeler

我们需要先获取到SqlSessionFactory，然后再拿到Configuration，然后注册自定义TypeHandler。

自定义的TypeHandler：

import org.apache.ibatis.type.*; import java.sql.*; import java.time.LocalDateTime; import java.time.ZoneId; import java.time.ZoneOffset; import java.time.ZonedDateTime; import java.time.format.DateTimeFormatter; import java.util.Date; public class MyLocalDateTimeTypeHandler extends BaseTypeHandler<LocalDateTime> { private static final String FORMAT = "yyyy-MM-dd HH:mm:ss"; private static final DateTimeFormatter DF = DateTimeFormatter.ofPattern(FORMAT); @Override public void setNonNullParameter(PreparedStatement preparedStatement, int i, LocalDateTime localDateTime, JdbcType jdbcType) throws SQLException { preparedStatement.setString(i, localDateTime.format(DF)); } @Override public LocalDateTime getNullableResult(ResultSet resultSet, String s) throws SQLException { LocalDateTime time2 = LocalDateTime.ofEpochSecond((resultSet.getTimestamp(s).getTime() / 1000), 0, ZoneOffset.ofHours(8)); return time2; } @Override public LocalDateTime getNullableResult(ResultSet resultSet, int i) throws SQLException { LocalDateTime time2 = LocalDateTime.ofEpochSecond((resultSet.getTimestamp(i).getTime() / 1000), 0, ZoneOffset.ofHours(8)); return time2; } @Override public LocalDateTime getNullableResult(CallableStatement callableStatement, int i) throws SQLException { LocalDateTime time2 = LocalDateTime.ofEpochSecond((callableStatement.getTimestamp(i).getTime() / 1000), 0, ZoneOffset.ofHours(8)); return time2; } /** * LocalDateTime转换为Date * * @param localDateTime */ public Date localDateTime2Date(LocalDateTime localDateTime) { ZoneId zoneId = ZoneId.systemDefault(); ZonedDateTime zdt = localDateTime.atZone(zoneId);//Combines this date-time with a time-zone to create a ZonedDateTime. Date date = Date.from(zdt.toInstant()); System.out.println(date.toString());//Tue Mar 27 14:17:17 CST 2018 return date; } }

注册TypeHandler：

import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.type.JdbcType; import org.apache.ibatis.type.TypeHandlerRegistry; import org.springframework.beans.factory.InitializingBean; import org.springframework.context.annotation.Configuration; import javax.annotation.Resource; import java.time.LocalDateTime; /** * @Author: shuo_ * @CreateTime: 2020-10-21 10:58 * @Description: */ @Configuration public class SqlSessionFactoryConfiguration implements InitializingBean { @Resource private SqlSessionFactory sqlSessionFactory; @Override public void afterPropertiesSet() throws Exception { TypeHandlerRegistry typeHandlerRegistry = sqlSessionFactory.getConfiguration().getTypeHandlerRegistry(); typeHandlerRegistry.register(JdbcType.VARCHAR, new MyLocalDateTimeTypeHandler()); typeHandlerRegistry.register(LocalDateTime.class, new MyLocalDateTimeTypeHandler()); } }