最近参与一个项目,项目的有一需求模块:在前台可以动态指定数据源,并执行配置的sql
.
之前使用的功能是jdbc实现的,后来被我改造成了使用mybatis:
mybatis动态切换|增加数据源
,在经过一段时间后,发现还挺好用。
然后同事就参照修改了其他的功能,但是在改造的过程中,出现问题:
之前在使用jdbc,当查询结果为空时,可以获取到
column-name,但是切换到mybatis时无法获取,遂决定rollback.
.
暂且不讨论需求的奇葩性(有特殊使用场景,咱也不懂,也不敢问)。
于是乎,查看了之前jdbc的实现逻辑:
public List<String> querySql(String querySql, String dataSourceId) throws SQLException {
Connection con = OracleJDBCUtil.getConnection(dataSourceId);
PreparedStatement pStatement = con.prepareStatement(querySql);
ResultSet resultSet = pStatement.getResultSet();
ResultSetMetaData meta = resultSet.getMetaData();
int columnCount = meta.getColumnCount();
List<String> cols = new ArrayList<>();
for (int i = 1; i < columnCount; i++) {
cols.add(meta.getColumnName(i));
}
//......
return null;
}
暗中观察代码之后,发现最重要的逻辑resultSet.getMetaData()
.
于是乎想到了使用mybatis插件来实现功能:在ResultSetHandler#handleResultSets()
执行完成之后,判断执行的结果是否为空,如果为空则,主动去获取元数据.
ResultSetMetaDataInterceptor
@Slf4j
@Intercepts({
@Signature(type = ResultSetHandler.class, method = "handleResultSets", args = {Statement.class})})
public class ResultSetMetaDataInterceptor implements Interceptor {
@Override
public Object intercept(Invocation invocation) throws Throwable {
// implement pre processing if need
Object returnObject = invocation.proceed();
try {
if (returnObject instanceof List) {
List list = (List) returnObject;
if (list.isEmpty()) {
Statement stmt = (Statement) invocation.getArgs()[0];
ResultSetMetaData metaData = stmt.getResultSet().getMetaData();
int length = metaData.getColumnCount();
String[] columns = new String[length];
IntStream.rangeClosed(1, metaData.getColumnCount()).forEach(index -> {
try {
columns[index - 1] = metaData.getColumnName(index);
} catch (SQLException e) {
e.printStackTrace();
}
});
log.info("执行结果为空时,设置表头:" + Arrays.asList(columns));
EmptyResultColumnNamesHolder.getInstance().set(columns);
}
}
} catch (Exception e) {
log.error("执行结果为空时,打印表头异常:" + e.getMessage());
}
// implement post processing if need
return returnObject;
}
@Override
public Object plugin(Object target) {
return Plugin.wrap(target, this);
}
@Override
public void setProperties(Properties properties) {
}
}
注入插件
@Configuration
@ConditionalOnBean(SqlSessionFactory.class)
@AutoConfigureAfter(MybatisAutoConfiguration.class)
public class ResultSetMetaDataAutoConfiguration {
private List<SqlSessionFactory> sqlSessionFactoryList;
public ResultSetMetaDataAutoConfiguration(List<SqlSessionFactory> sqlSessionFactoryList) {
this.sqlSessionFactoryList = sqlSessionFactoryList;
}
@PostConstruct
public void addResultSetMetaDataInterceptor() {
ResultSetMetaDataInterceptor metaDataInterceptor = new ResultSetMetaDataInterceptor();
for (SqlSessionFactory sqlSessionFactory : sqlSessionFactoryList) {
sqlSessionFactory.getConfiguration().addInterceptor(metaDataInterceptor);
}
}
}
执行sql测试;
@Test
public void getNoDataTest() {
String databaseId = "databaseXXX";
String sql = "select * from xxx";
List<Map> list = mapper.list(databaseId, sql);
if (list.isEmpty()) {
log.info("查询结果为空:::::");
String[] cloumns = EmptyResultColumnNamesHolder.getInstance().get();
Arrays.stream(cloumns).forEach(columnName -> {
//打印 columnname
System.out.println(columnName);
});
} else {
System.out.println(list);
}
}