/*- * << * DBus * == * Copyright (C) 2016 - 2019 Bridata * == * Licensed under the Apache License, Version 2.0 (the "License"); * you may not use this file except in compliance with the License. * You may obtain a copy of the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations under the License. * >> */ package com.creditease.dbus.heartbeat.dao.impl; import com.creditease.dbus.enums.DbusDatasourceType; import com.creditease.dbus.heartbeat.container.DataSourceContainer; import com.creditease.dbus.heartbeat.container.HeartBeatConfigContainer; import com.creditease.dbus.heartbeat.container.MongoClientContainer; import com.creditease.dbus.heartbeat.dao.IHeartBeatDao; import com.creditease.dbus.heartbeat.exception.SQLTimeOutException; import com.creditease.dbus.heartbeat.log.LoggerFactory; import com.creditease.dbus.heartbeat.mongo.DBusMongoClient; import com.creditease.dbus.heartbeat.util.Constants; import com.creditease.dbus.heartbeat.util.DBUtil; import com.creditease.dbus.heartbeat.util.DateUtil; import com.creditease.dbus.heartbeat.vo.HeartBeatMonitorVo; import com.creditease.dbus.heartbeat.vo.MysqlMasterStatusVo; import com.mongodb.MongoClient; import com.mongodb.client.MongoCollection; import com.mongodb.client.MongoDatabase; import com.mysql.jdbc.exceptions.MySQLTimeoutException; import org.apache.commons.lang.StringUtils; import org.bson.Document; import java.sql.*; import java.util.Map; public class HeartBeatDaoImpl implements IHeartBeatDao { private String getSendPacketSql2Oracle() { StringBuilder sql = new StringBuilder(); sql.append(" insert into "); sql.append(" db_heartbeat_monitor (ID, DS_NAME, SCHEMA_NAME, TABLE_NAME, PACKET, CREATE_TIME)"); sql.append(" values (SEQ_HEARTBEAT_MONITOR.NEXTVAL, ?, ?, ?, ?, to_char(systimestamp, 'yyyymmdd hh24:mi:ss.ff6'))"); return sql.toString(); } private String getSendPacketSql2Mysql() { StringBuilder sql = new StringBuilder(); sql.append(" insert into "); sql.append(" db_heartbeat_monitor (DS_NAME, SCHEMA_NAME, TABLE_NAME, PACKET, CREATE_TIME, UPDATE_TIME)"); sql.append(" values (?, ?, ?, ?, ?, ?)"); return sql.toString(); } private String getSendPacketSql2DB2() { StringBuilder sql = new StringBuilder(); sql.append(" insert into "); sql.append(" dbus.db_heartbeat_monitor (DS_NAME, SCHEMA_NAME, TABLE_NAME, PACKET, CREATE_TIME, UPDATE_TIME)"); sql.append(" values (?, ?, ?, ?, CURRENT TIMESTAMP, CURRENT TIMESTAMP)"); return sql.toString(); } private String getMaxID() { return "select max(id) as maxID from db_heartbeat_monitor"; } private String getMaxID2DB2() { return "select max(id) as maxID from dbus.db_heartbeat_monitor"; } //保留最后的 10000个心跳信息 private String getDeleteOldHeartBeat() { return "delete from db_heartbeat_monitor where id < ?"; } private String getDeleteOldHeartBeat2DB2() { return "delete from dbus.db_heartbeat_monitor where id < ?"; } @Override public int sendPacket(String key, String dsName, String schemaName, String tableName, String packet, String dsType) { int cnt = 0; if (DbusDatasourceType.stringEqual(dsType, DbusDatasourceType.MONGO)) { cnt = sendNoSqlPacket(key, dsName, schemaName, tableName, packet, dsType); } else if (DbusDatasourceType.stringEqual(dsType, DbusDatasourceType.MYSQL) || DbusDatasourceType.stringEqual(dsType, DbusDatasourceType.ORACLE) || DbusDatasourceType.stringEqual(dsType, DbusDatasourceType.DB2) ) { cnt = sendRdbmsPacket(key, dsName, schemaName, tableName, packet, dsType); } return cnt; } private int sendNoSqlPacket(String key, String dsName, String schemaName, String tableName, String packet, String dsType) { int cnt = 0; DBusMongoClient dbusMongoClient = null; try { dbusMongoClient = MongoClientContainer.getInstance().getMongoClient(key); Document record = new Document(); record.put("DS_NAME", dsName); record.put("SCHEMA_NAME", schemaName); record.put("TABLE_NAME", tableName); record.put("PACKET", packet); record.put("CREATE_TIME", DateUtil.convertLongToStr4Date(System.currentTimeMillis())); record.put("UPDATE_TIME", DateUtil.convertLongToStr4Date(System.currentTimeMillis())); if (dbusMongoClient.getShardMongoClients() != null && dbusMongoClient.getShardMongoClients().size() > 0) { if (dbusMongoClient.isShardCollection(schemaName, tableName)) { for (Map.Entry<String, MongoClient> client : dbusMongoClient.getShardMongoClients().entrySet()) { MongoDatabase db = client.getValue().getDatabase("dbus"); MongoCollection collection = db.getCollection("db_heartbeat_monitor"); record.put("_id", dbusMongoClient.nextSequence("db_heartbeat_monitor")); collection.insertOne(record); cnt++; } } else { String majorShard = dbusMongoClient.getDbMajorShard(schemaName); if (StringUtils.isNotBlank(majorShard)) { MongoClient client = dbusMongoClient.getShardMongoClients().get(majorShard); MongoDatabase db = client.getDatabase("dbus"); MongoCollection collection = db.getCollection("db_heartbeat_monitor"); record.put("_id", dbusMongoClient.nextSequence("db_heartbeat_monitor")); collection.insertOne(record); cnt++; } } } else { MongoClient client = dbusMongoClient.getMongoClient(); MongoDatabase db = client.getDatabase("dbus"); MongoCollection collection = db.getCollection("db_heartbeat_monitor"); record.put("_id", dbusMongoClient.nextSequence("db_heartbeat_monitor")); collection.insertOne(record); cnt++; } } catch (Exception e) { LoggerFactory.getLogger().error("[db-HeartBeatDao]", e); } if (cnt >= 1) { LoggerFactory.getLogger().info("[db-HeartBeatDao] 数据源: " + key + ", 插入心跳包成功. " + packet); } else { LoggerFactory.getLogger().error("[db-HeartBeatDao]: 数据源: " + key + ", 插入心跳包失败!" + packet); } return cnt; } private int sendRdbmsPacket(String key, String dsName, String schemaName, String tableName, String packet, String dsType) { Connection conn = null; PreparedStatement ps = null; int cnt = 0; long beginConn = 0, endConn = 0, beginStmt = 0, endStmt = 0, closeConn = 0, closeEndConn = 0; try { beginConn = System.currentTimeMillis(); conn = DataSourceContainer.getInstance().getConn(key); endConn = System.currentTimeMillis(); if (StringUtils.equals(Constants.CONFIG_DB_TYPE_MYSQL, dsType)) { ps = conn.prepareStatement(getSendPacketSql2Mysql()); ps.setString(1, dsName); ps.setString(2, schemaName); ps.setString(3, tableName); ps.setString(4, packet); ps.setString(5, DateUtil.convertLongToStr4Date(System.currentTimeMillis())); ps.setString(6, DateUtil.convertLongToStr4Date(System.currentTimeMillis())); } else if (StringUtils.equals(Constants.CONFIG_DB_TYPE_ORA, dsType)) { ps = conn.prepareStatement(getSendPacketSql2Oracle()); ps.setString(1, dsName); ps.setString(2, schemaName); ps.setString(3, tableName); ps.setString(4, packet); } else if (StringUtils.equals(Constants.CONFIG_DB_TYPE_DB2, dsType)) { ps = conn.prepareStatement(getSendPacketSql2DB2()); ps.setString(1, dsName); ps.setString(2, schemaName); ps.setString(3, tableName); ps.setString(4, packet); } Integer queryTimeout = HeartBeatConfigContainer.getInstance().getHbConf().getQueryTimeout(); if (queryTimeout == null) queryTimeout = 5; ps.setQueryTimeout(queryTimeout); beginStmt = System.currentTimeMillis(); cnt = ps.executeUpdate(); endStmt = System.currentTimeMillis(); } catch (Exception e) { if (e instanceof MySQLTimeoutException) { throw new SQLTimeOutException(e.getMessage(), e); } else if (e instanceof SQLException) { SQLException sqle = (SQLException) e; if (sqle.getNextException() instanceof SQLRecoverableException) { SQLRecoverableException sqlre = (SQLRecoverableException) sqle.getNextException(); if (sqle.getErrorCode() == 17060 && sqlre.getErrorCode() == 17002) { throw new SQLTimeOutException(sqlre.getMessage(), sqlre); } } } LoggerFactory.getLogger().error("[db-HeartBeatDao]", e); } finally { closeConn = System.currentTimeMillis(); DBUtil.close(ps); DBUtil.close(conn); closeEndConn = System.currentTimeMillis(); } String statTime = String.format(", beginConn: %d, ConUsed: %d, beginStmt: %d, stmtUsed: %d, closeConn: %d, closeUsed: %d", beginConn, endConn - beginConn, beginStmt, endStmt - beginStmt, closeConn, closeEndConn - closeConn); if (cnt == 1) { LoggerFactory.getLogger().info("[db-HeartBeatDao] 数据源: " + key + ", 插入心跳包成功. " + packet + statTime); } else { LoggerFactory.getLogger().error("[db-HeartBeatDao]: 数据源: " + key + ", 插入心跳包失败!" + packet + statTime); } return cnt; } public MysqlMasterStatusVo queryMasterStatus(String key) { MysqlMasterStatusVo statusVo = null; Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; // 生成备库连接key String keyWk = StringUtils.join(new String[]{key, "slave"}, "_"); try { // show master status; // | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | conn = DataSourceContainer.getInstance().getConn(keyWk); if (conn != null) { ps = conn.prepareStatement("show master status"); rs = ps.executeQuery(); if (rs.next()) { statusVo = new MysqlMasterStatusVo(); statusVo.setFile(rs.getString("File")); statusVo.setPosition(rs.getLong("Position")); statusVo.setBinlogDoDB(rs.getString("Binlog_Do_DB")); statusVo.setBinlogIgnoreDB(rs.getString("Binlog_Ignore_DB")); // statusVo.setExecutedGtidSet(rs.getString("Executed_Gtid_Set")); } } else { LoggerFactory.getLogger().error("[db-HeartBeatDao]: 没有发现数据源: " + keyWk + "的连接!"); } } catch (Exception e) { LoggerFactory.getLogger().error("[db-HeartBeatDao]: 查询数据源: " + keyWk + "的master status信息发生错误!", e); } finally { DBUtil.close(rs); DBUtil.close(ps); DBUtil.close(conn); } return statusVo; } @Override public int deleteOldHeartBeat(String key, String dsType) { Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; PreparedStatement ps2 = null; int cnt = 0; long maxID = 0; try { conn = DataSourceContainer.getInstance().getConn(key); //select maxID if (StringUtils.equals(Constants.CONFIG_DB_TYPE_DB2, dsType)) { ps = conn.prepareStatement(getMaxID2DB2()); } else { ps = conn.prepareStatement(getMaxID()); } Integer queryTimeout = HeartBeatConfigContainer.getInstance().getHbConf().getQueryTimeout(); if (queryTimeout == null) queryTimeout = 5; ps.setQueryTimeout(queryTimeout); rs = ps.executeQuery(); while (rs.next()) { //read max id maxID = rs.getLong("maxID"); } DBUtil.close(rs); DBUtil.close(ps); //delete old heartbeat record if (StringUtils.equals(Constants.CONFIG_DB_TYPE_DB2, dsType)) { ps2 = conn.prepareStatement(getDeleteOldHeartBeat2DB2()); } else { ps2 = conn.prepareStatement(getDeleteOldHeartBeat()); } ps2.setQueryTimeout(queryTimeout); ps2.setLong(1, maxID - 10000); cnt = ps2.executeUpdate(); LoggerFactory.getLogger().info("[db-HeartBeatDao] 数据源: " + key + ", 删除旧心跳包成功. 条数=" + cnt); } catch (Exception e) { LoggerFactory.getLogger().error("[db-HeartBeatDao] 删除旧心跳包失败." + key + ", ", e); } finally { DBUtil.close(rs); DBUtil.close(ps); DBUtil.close(ps2); DBUtil.close(conn); } return cnt; } public static void main(String[] args) { //脱敏 String zkServers = "dbus-z1:2181"; Connection connection = null; PreparedStatement stmt = null; ResultSet rs = null; try { Class.forName("oracle.jdbc.driver.OracleDriver"); connection = DriverManager.getConnection("jdbc:oracle:thin:@ (DESCRIPTION= (FAILOVER = yes)(ADDRESS = (PROTOCOL = TCP)(HOST =dbus-n1)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST =dbus-n2)(PORT = 1521)) (CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = cedb)))", "cm", "cm9r"); String sqlQuery = "select f_Str2List('aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddaaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,d,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddaaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,d,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddaaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,d,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddaaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,d,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddaaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,d,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddaaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,d,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddaaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,d,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd,fff,ddddaaa,bbb,ccc,ddd,eee,ddd,fff,ddddd,aaa,bbb,ccc,ddd,eee,ddd') as a from dual"; stmt = connection.prepareStatement(sqlQuery); stmt.setQueryTimeout(2); System.out.println("begin ..."); // int i = stmt.executeUpdate(); rs = stmt.executeQuery(); /*while (rs.next()) { System.out.println(rs.getString("DOC_ID")); }*/ System.out.println("end!"); } catch (Exception e) { e.printStackTrace(); try { if (e instanceof SQLException && ((SQLException) (e)).getNextException() instanceof SQLRecoverableException) { SQLException sqle = (SQLException) e; if (sqle.getNextException() instanceof SQLRecoverableException) { SQLRecoverableException sqlre = (SQLRecoverableException) sqle.getNextException(); if (sqle.getErrorCode() == 17060 && sqlre.getErrorCode() == 17002) { throw new RuntimeException(sqlre.getMessage(), sqlre); } } } } catch (Exception innere) { innere.getCause(); } } finally { if (rs != null) { try { rs.close(); } catch (Exception e) { } } if (stmt != null) { try { stmt.close(); } catch (Exception e) { } } if (connection != null) { try { connection.close(); } catch (Exception e) { } } } } private String getQueryHeartbeatSql2Mysql() { StringBuilder sql = new StringBuilder(); sql.append(" select "); sql.append(" DS_NAME,"); sql.append(" SCHEMA_NAME,"); sql.append(" CREATE_TIME"); sql.append(" from"); sql.append(" db_heartbeat_monitor"); sql.append(" where"); sql.append(" DS_NAME = ? and"); sql.append(" SCHEMA_NAME = ?"); sql.append(" order by"); sql.append(" CREATE_TIME desc"); sql.append(" limit 1"); return sql.toString(); } private String getQueryHeartbeatSql2DB2() { StringBuilder sql = new StringBuilder(); sql.append(" select "); sql.append(" DS_NAME,"); sql.append(" SCHEMA_NAME,"); sql.append(" CREATE_TIME"); sql.append(" from"); sql.append(" dbus.db_heartbeat_monitor"); sql.append(" where"); sql.append(" DS_NAME = ? and"); sql.append(" SCHEMA_NAME = ?"); sql.append(" order by"); sql.append(" CREATE_TIME desc"); sql.append(" fetch first 1 rows only"); return sql.toString(); } private String getQueryHeartbeatSql2Oracle() { StringBuilder sql = new StringBuilder(); sql.append(" select"); sql.append(" wk.DS_NAME, wk.SCHEMA_NAME, wk.CREATE_TIME"); sql.append(" from "); sql.append(" (select"); sql.append(" DS_NAME,"); sql.append(" SCHEMA_NAME,"); sql.append(" CREATE_TIME"); sql.append(" from"); sql.append(" db_heartbeat_monitor"); sql.append(" where"); sql.append(" DS_NAME = ? and"); sql.append(" SCHEMA_NAME = ?"); sql.append(" order by"); sql.append(" CREATE_TIME desc"); sql.append(" ) wk"); sql.append(" where"); sql.append(" rownum = 1"); return sql.toString(); } @Override public HeartBeatMonitorVo queryLatestHeartbeat(String key, String dsName, String schemaName, String dsType) { Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; HeartBeatMonitorVo hbmVo = null; try { conn = DataSourceContainer.getInstance().getConn(key); if (StringUtils.equals(Constants.CONFIG_DB_TYPE_MYSQL, dsType)) { ps = conn.prepareStatement(getQueryHeartbeatSql2Mysql()); } else if (StringUtils.equals(Constants.CONFIG_DB_TYPE_ORA, dsType)) { ps = conn.prepareStatement(getQueryHeartbeatSql2Oracle()); } else if (StringUtils.equals(Constants.CONFIG_DB_TYPE_DB2, dsType)) { ps = conn.prepareStatement(getQueryHeartbeatSql2DB2()); } ps.setString(1, dsName); ps.setString(2, schemaName); Integer queryTimeout = HeartBeatConfigContainer.getInstance().getHbConf().getQueryTimeout(); if (queryTimeout == null) queryTimeout = 5; ps.setQueryTimeout(queryTimeout); rs = ps.executeQuery(); if (rs.next()) { hbmVo = new HeartBeatMonitorVo(); hbmVo.setDsName(rs.getString("DS_NAME")); hbmVo.setSchemaName(rs.getString("SCHEMA_NAME")); hbmVo.setCreateTime(rs.getString("CREATE_TIME")); } } catch (Exception e) { LoggerFactory.getLogger().error("[db-HeartBeatDao]", e); } finally { DBUtil.close(rs); DBUtil.close(ps); DBUtil.close(conn); } return hbmVo; } }