最近项目使用分布式数据库中间件 ,后端数据库使用到Oracle ,使用客户端工具连接 ,发现中间件使用JDBC连接方式连接后端数据库为Oracle、SQL Server /DB2等 不显示表名 ,查询数据必须使用SQL命令窗口执行,但是SQL命令窗口执行SQL时,也不提示表名 字段名 、给开发人员和数据库运维人员带来极大的不便,偷窥一下源码 ,也许这个不是最好的解决,欢迎吐槽和转载 。。。
问题:
用 Navicat Client 、MySQL 客户端 连接数据库中间件(MyCat),中间件连接不显示表名称, 后端数据库是Oracle、DB2等 效果图如下:
解决方式
涉及类 : JDBCConnection 主要解决Oracle 表显示 【已解决】 需要完善表显示数据和分片节点 后续迁移到 io.mycat.backend.jdbc.oracle.ShowTables中
修个内容:
private void executeSQL(RouteResultsetNode rrn, ServerConnection sc,
boolean autocommit) throws IOException {
String orgin = rrn.getStatement();
// String sql = rrn.getStatement().toLowerCase();
LOGGER.debug("JDBC SQL:"+orgin+"|"+sc.toString());
if (!modifiedSQLExecuted && rrn.isModifySQL()) {
modifiedSQLExecuted = true;
}
try {
syncIsolation(sc.getTxIsolation()) ;
if (!this.schema.equals(this.oldSchema)) {
con.setCatalog(schema);
this.oldSchema = schema;
}
if (!this.isSpark) {
con.setAutoCommit(autocommit);
}
int sqlType = rrn.getSqlType();
if( rrn.isCallStatement() && "oracle".equalsIgnoreCase(getDbType()))
{
//存储过程暂时只支持oracle
ouputCallStatement(rrn,sc,orgin);
} else if (sqlType == ServerParse.SELECT || sqlType == ServerParse.SHOW) {
if( (sqlType == ServerParse.SHOW) && (!dbType.equals("MYSQL")) ) {
// showCMD(sc, orgin);
//ShowVariables.execute(sc, orgin);
boolean showExecute = true;
if ("SHOW TABLES".equalsIgnoreCase(orgin)){
orgin = "SELECT TABLE_NAME FROM USER_TABLES" ;
} else if ("SHOW FULL TABLES WHERE Table_type != 'VIEW'".equalsIgnoreCase(orgin)){
orgin = " SELECT OBJECT_NAME,'BASE TABLE' AS Table_type FROM USER_OBJECTS WHERE OBJECT_TYPE='TABLE' ";
} else if ("SHOW TABLE STATUS".equalsIgnoreCase(orgin)){
StringBuffer sb = new StringBuffer();
sb.append(" SELECT A.TABLE_NAME,A.NUM_ROWS AS \"Rows\",A.LAST_ANALYZED AS Create_time,A.LAST_ANALYZED AS Update_time,");
sb.append(" 'InnoDB' AS ENGINE, '' AS Auto_increment , B.COMMENTS AS \"COMMENT\" ");
sb.append(" FROM USER_TABLES A, USER_TAB_COMMENTS B WHERE A.TABLE_NAME = B.TABLE_NAME ORDER BY TABLE_NAME ");
orgin = sb.toString();
} else {
showExecute = false;
}
if( showExecute ){
ouputResultSet(sc, orgin);
} else {
ShowVariables.execute(sc, orgin,this);
}
} else if ("SELECT CONNECTION_ID()".equalsIgnoreCase(orgin)) {
//ShowVariables.justReturnValue(sc,String.valueOf(sc.getId()));
ShowVariables.justReturnValue(sc,String.valueOf(sc.getId()),this);
} else{
if("SELECT @@character_set_database, @@collation_database".equalsIgnoreCase(orgin)){
orgin = "SELECT * FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER= 'NLS_CHARACTERSET' ";
}
ouputResultSet(sc, orgin);
}
} else {
executeddl(sc, orgin);
}
} catch (SQLException e) {
String msg = e.getMessage();
ErrorPacket error = new ErrorPacket();
error.packetId = ++packetId;
error.errno = e.getErrorCode();
error.message = msg.getBytes();
this.respHandler.errorResponse(error.writeToBytes(sc), this);
} catch (Exception e) {
String msg = e.getMessage();
ErrorPacket error = new ErrorPacket();
error.packetId = ++packetId;
error.errno = ErrorCode.ER_UNKNOWN_ERROR;
error.message = ((msg == null) ? e.toString().getBytes() : msg.getBytes());
String err = null;
if(error.message!=null){
err = new String(error.message);
}
LOGGER.error("sql execute error, "+ err , e);
this.respHandler.errorResponse(error.writeToBytes(sc), this);
}
finally {
this.running = false;
}
}