Java Code Examples for com.alibaba.druid.util.JdbcUtils#close()

private boolean computeTotalRecords() {
    Statement statement = null;
    ResultSet resultSet = null;
    Connection connection = null;
    try {
        connection = druidDataSource.getConnection();
        statement = connection.createStatement();
        resultSet = statement.executeQuery(String.format("select count(offset) as total from t_transaction_%d", tableSuffix));
        if (!resultSet.next()) {
            log.warn("computeTotalRecords ResultSet is empty");
            return false;
        }

        this.totalRecordsValue.set(resultSet.getLong(1));
    } catch (Exception e) {
        log.warn("computeTotalRecords Exception", e);
        return false;
    } finally {
        JdbcUtils.close(resultSet);
        JdbcUtils.close(statement);
        JdbcUtils.close(connection);
    }

    return true;
}
 

public static long execulteCount(String sql, String toDn) throws SQLException, IOException {
    PhysicalDBNode dbNode = MycatServer.getInstance().getConfig().getDataNodes().get(toDn);
    PhysicalDBPool dbPool = dbNode.getDbPool();
    PhysicalDatasource datasource = dbPool.getSources()[dbPool.getActivedIndex()];
    DBHostConfig config = datasource.getConfig();
    Connection con = null;
    try {
        con = DriverManager.getConnection("jdbc:mysql://" + config.getUrl() + "/" + dbNode.getDatabase(), config.getUser(), config.getPassword());

        List<Map<String, Object>> result = JdbcUtils.executeQuery(con, sql, new ArrayList<>());
        if (result.size() == 1) {
            return (long) result.get(0).get("count");
        }
    } finally {
        JdbcUtils.close(con);
    }
    return 0;
}
 

private Map<Integer, Map<String, Object>> loadColumn(String database, String table) {
    Map<Integer, Map<String, Object>> rtn = new HashMap<>();
    List<Map<String, Object>> list = null;
    Connection con = null;
    try {
        con = DriverManager.getConnection("jdbc:mysql://" + hostname + ":" + port, username, password);
        list = executeQuery(con, "select  COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE, CHARACTER_SET_NAME from INFORMATION_SCHEMA.COLUMNS where table_name='" + table + "' and TABLE_SCHEMA='" + database + "'");

    } catch (SQLException e) {
        throw new RuntimeException(e);
    } finally {
        JdbcUtils.close(con);
    }
    for (Map<String, Object> stringObjectMap : list) {
        BigInteger pos = (BigInteger) stringObjectMap.get("ORDINAL_POSITION");
        rtn.put(pos.intValue(), stringObjectMap);
    }
    return rtn;
}
 

/**
 * 抛弃连接，不进行回收，而是抛弃
 *
 * @param realConnection
 */
public void discardConnection(Connection realConnection) {
    JdbcUtils.close(realConnection);

    lock.lock();
    try {
        activeCount--;
        discardCount++;

        if (activeCount <= minIdle) {
            emptySignal();
        }
    } finally {
        lock.unlock();
    }
}
 

private void loaddataToDn(File loaddataFile, String toDn, String table) throws SQLException, IOException {
    PhysicalDBNode dbNode = MycatServer.getInstance().getConfig().getDataNodes().get(toDn);
    PhysicalDBPool dbPool = dbNode.getDbPool();
    PhysicalDatasource datasource = dbPool.getSources()[dbPool.getActivedIndex()];
    DBHostConfig config = datasource.getConfig();
    Connection con = null;
    try {
        con = DriverManager.getConnection("jdbc:mysql://" + config.getUrl() + "/" + dbNode.getDatabase(), config.getUser(), config.getPassword());
        String sql = "load data local infile '" + loaddataFile.getPath().replace("\\", "//") + "' replace into table " + table + " character set 'utf8mb4'  fields terminated by ','  enclosed by '\"'  ESCAPED BY '\\\\'  lines terminated by '\\n'";
        JdbcUtils.execute(con, sql, new ArrayList<>());
    }
    catch (Exception e){
        try {
            pushMsgToZK(task.getZkpath(), task.getFrom() + "-" + task.getTo(), 0, e.getLocalizedMessage(), "", "");
        } catch (Exception e1) {
        }
    }
    finally {
        JdbcUtils.close(con);
    }
}
 

@Override
public long minPK() {
    Connection connection = null;
    PreparedStatement ps = null;
    try {
        connection = druidDataSource.getConnection();
        ps = connection.prepareStatement(String.format("select min(offset) as minOffset from t_transaction_%d", tableSuffix));
        ResultSet resultSet = ps.executeQuery();
        if (resultSet.next()) {
            Long minOffset = resultSet.getLong("minOffset");
            return minOffset != null ? minOffset : -1L;
        }
    } catch (SQLException e) {
        log.warn("maxPK Exception", e);
    } finally {
        JdbcUtils.close(ps);
        JdbcUtils.close(connection);
    }
    return -1L;
}
 

@Override
public long maxPK() {
    Connection connection = null;
    PreparedStatement ps = null;
    try {
        connection = druidDataSource.getConnection();
        ps = connection.prepareStatement(String.format("select max(offset) as maxOffset from t_transaction_%d", tableSuffix));
        ResultSet resultSet = ps.executeQuery();
        if (resultSet.next()) {
            Long maxOffset = resultSet.getLong("maxOffset");
            return maxOffset != null ? maxOffset : -1L;
        }
    } catch (SQLException e) {
        log.warn("maxPK Exception", e);
    } finally {
        JdbcUtils.close(ps);
        JdbcUtils.close(connection);
    }
    return -1L;
}
 

private boolean createDB() {
    Statement statement = null;
    Connection connection = null;
    try {
        connection = druidDataSource.getConnection();
        connection.setAutoCommit(false);
        statement = connection.createStatement();
        String sql = this.createTableSql();
        log.info("createDB SQL:\n {}", sql);
        statement.execute(sql);
        connection.commit();
        return true;
    } catch (Exception e) {
        log.warn("createDB Exception", e);
        return false;
    } finally {
        JdbcUtils.close(statement);
        JdbcUtils.close(connection);
    }
}
 

@Override
public List<TransactionRecord> traverse(long pk, int nums) {
    List<TransactionRecord> list = new ArrayList<TransactionRecord>(nums);
    Connection connection = null;
    PreparedStatement ps = null;
    try {
        connection = druidDataSource.getConnection();
        ps = connection.prepareStatement(String.format("select offset,producerGroup,timestamp,size from t_transaction_%d where offset>? order by offset limit ?", tableSuffix));
        ps.setLong(1, pk);
        ps.setInt(2, nums);
        ResultSet resultSet = ps.executeQuery();
        while (resultSet.next()) {
            TransactionRecord tr = new TransactionRecord(
                    resultSet.getLong("offset"),
                    resultSet.getLong("timestamp"),
                    resultSet.getInt("size"),
                    resultSet.getString("producerGroup")
            );
            list.add(tr);
        }
        return list;
    } catch (SQLException e) {
        log.warn("traverse Exception", e);
    } finally {
        JdbcUtils.close(ps);
        JdbcUtils.close(connection);
    }
    return list;
}
 

public boolean isValidConnection(Connection conn, String validateQuery, int validationQueryTimeout) throws Exception {
    if (validateQuery == null || validateQuery.isEmpty()) {
        validateQuery = this.defaultValidateQuery;
    }

    if (conn.isClosed()) {
        return false;
    }

    if (conn instanceof DruidPooledConnection) {
        conn = ((DruidPooledConnection) conn).getConnection();
    }

    if (conn instanceof ConnectionProxy) {
        conn = ((ConnectionProxy) conn).getRawObject();
    }

    int queryTimeout = validationQueryTimeout <= 0 ? defaultQueryTimeout : validationQueryTimeout;

    Statement stmt = null;
    ResultSet rs = null;
    try {
        stmt = conn.createStatement();
        stmt.setQueryTimeout(queryTimeout);
        rs = stmt.executeQuery(validateQuery);
        return true;
    } finally {
        JdbcUtils.close(rs);
        JdbcUtils.close(stmt);
    }
}
 

/**
 * 读取 properties配制文件的信息
* 方法描述
* @param name 名称信息
* @return
* @创建日期 2016年9月18日
*/
private String readProperties(String name) {

    String path = ZookeeperPath.ZK_LOCAL_CFG_PATH.getKey() + name;
    // 加载数据
    InputStream input = SequenceTozkLoader.class.getResourceAsStream(path);

    if (null != input) {

        StringBuilder mapFileStr = new StringBuilder();

        byte[] buffers = new byte[256];

        try {
            int readIndex = -1;

            while ((readIndex = input.read(buffers)) != -1) {
                mapFileStr.append(new String(buffers, 0, readIndex));
            }
        } catch (IOException e) {
            e.printStackTrace();
            LOGGER.error("SequenceTozkLoader readMapFile IOException", e);

        } finally {
            JdbcUtils.close(input);
        }

        return mapFileStr.toString();
    }
    return null;
}
 

public boolean isValidConnection(Connection conn, String validateQuery, int validationQueryTimeout) throws Exception {
    if (validateQuery == null || validateQuery.isEmpty()) {
        validateQuery = this.defaultValidateQuery;
    }

    if (conn.isClosed()) {
        return false;
    }

    if (conn instanceof DruidPooledConnection) {
        conn = ((DruidPooledConnection) conn).getConnection();
    }

    if (conn instanceof ConnectionProxy) {
        conn = ((ConnectionProxy) conn).getRawObject();
    }

    int queryTimeout = validationQueryTimeout <= 0 ? defaultQueryTimeout : validationQueryTimeout;

    Statement stmt = null;
    ResultSet rs = null;
    try {
        stmt = conn.createStatement();
        if(queryTimeout>=0){
            //pgsql Driver 9.0以及以下版本不支持setQueryTimeout，可通过设置queryTimeout<0兼容低版本
            stmt.setQueryTimeout(queryTimeout);
        }
        rs = stmt.executeQuery(validateQuery);
        return true;
    } finally {
        JdbcUtils.close(rs);
        JdbcUtils.close(stmt);
    }
}
 

/**
 * 读取 mapFile文件的信息
* 方法描述
* @param name 名称信息
* @return
* @创建日期 2016年9月18日
*/
private String readMapFile(String name) {

    StringBuilder mapFileStr = new StringBuilder();

    String path = ZookeeperPath.ZK_LOCAL_CFG_PATH.getKey() + name;
    // 加载数据
    InputStream input = RulesxmlTozkLoader.class.getResourceAsStream(path);

    checkNotNull(input, "read Map file curr Path :" + path + " is null! must is not null");

    byte[] buffers = new byte[256];

    try {
        int readIndex = -1;

        while ((readIndex = input.read(buffers)) != -1) {
            mapFileStr.append(new String(buffers, 0, readIndex));
        }
    } catch (IOException e) {
        e.printStackTrace();
        LOGGER.error("RulesxmlTozkLoader readMapFile IOException", e);

    } finally {
        JdbcUtils.close(input);
    }

    return mapFileStr.toString();
}
 

@Override
public void run() {
	String data = "";
	long offset = 0;
	Connection con = null;
	try {
		long start = System.currentTimeMillis();
		con = DataMigratorUtil.getMysqlConnection(srcDn);
		if(tableInfo.isExpantion()){
			deleteDataDependFile(data, offset, con);
		}else{
			//缩容，移除的节点直接truncate删除数据，非移除的节点按照临时文件的中值进行删除操作
			List<DataNode> list = tableInfo.getRemovedDataNodes();
			boolean isRemovedDn = false;
			for(DataNode dn:list){
				if(srcDn.equals(dn)){
					isRemovedDn = true;
				}
			}
			if(isRemovedDn){
				String sql = "truncate "+tableInfo.getTableName();
				JdbcUtils.execute(con, sql, new ArrayList<>());
			}else{
				deleteDataDependFile(data, offset, con);
			}
		}
		long end = System.currentTimeMillis();
		System.out.println(tableInfo.getSchemaAndTableName()+" clean dataNode "+srcDn.getName()+" completed in "+(end-start)+"ms");
		
	} catch (Exception e) {
		String errMessage = srcDn.toString()+":"+"clean data error!";
		LOGGER.error(errMessage, e);
		tableInfo.setError(true);
		tableInfo.getErrMessage().append(errMessage+"\n");
	} finally{
		JdbcUtils.close(con);
	}
}
 

@Override
public boolean confirm(List<Long> pks) {
    if (pks == null || pks.size() == 0) {
        return true;
    }
    PreparedStatement statement = null;
    Connection connection = null;
    try {
        connection = druidDataSource.getConnection();
        connection.setAutoCommit(false);
        statement = connection.prepareStatement(String.format("DELETE FROM t_transaction_%d WHERE offset = ?", tableSuffix));
        for (long pk : pks) {
            statement.setLong(1, pk);
            statement.addBatch();
        }
        int[] executeBatch = statement.executeBatch();
        connection.commit();
        this.totalRecordsValue.addAndGet(-updatedRows(executeBatch));
        return true;
    } catch (Exception e) {
        log.warn("createDB Exception", e);
    } finally {
        JdbcUtils.close(statement);
        JdbcUtils.close(connection);
    }
    return false;
}
 

@Override
public boolean parpare(List<TransactionRecord> trs) {
    if (trs == null || trs.size() == 0) {
        return true;
    }
    Connection connection = null;
    PreparedStatement statement = null;
    try {
        connection = druidDataSource.getConnection();
        connection.setAutoCommit(false);
        statement = connection.prepareStatement(String.format("insert into t_transaction_%d (offset,producerGroup,timestamp,size) values (?, ?, ?, ?)", tableSuffix));
        for (TransactionRecord tr : trs) {
            statement.setLong(1, tr.getOffset());
            statement.setString(2, tr.getProducerGroup());
            statement.setLong(3, tr.getTimestamp());
            statement.setInt(4, tr.getSize());
            statement.addBatch();
        }
        int[] executeBatch = statement.executeBatch();
        connection.commit();
        this.totalRecordsValue.addAndGet(updatedRows(executeBatch));
        return true;
    } catch (Exception e) {
        log.warn("createDB Exception", e);
        return false;
    } finally {
        JdbcUtils.close(statement);
        JdbcUtils.close(connection);
    }
}
 

public static void createTable(DataNode dn,String table) throws SQLException{
	Connection con = null;
	try {
		con =  getMysqlConnection(dn);
		JdbcUtils.execute(con, table, new ArrayList<>());
	} catch (SQLException e) {
		throw e;
	}finally{
		JdbcUtils.close(con);
	}
}
 

public static void execulteSql(String sql, String toDn) throws SQLException, IOException {
    PhysicalDBNode dbNode = MycatServer.getInstance().getConfig().getDataNodes().get(toDn);
    PhysicalDBPool dbPool = dbNode.getDbPool();
    PhysicalDatasource datasource = dbPool.getSources()[dbPool.getActivedIndex()];
    DBHostConfig config = datasource.getConfig();
    Connection con = null;
    try {
        con = DriverManager
                .getConnection("jdbc:mysql://" + config.getUrl() + "/" + dbNode.getDatabase(), config.getUser(), config.getPassword());

        JdbcUtils.execute(con, sql, new ArrayList<>());

    } finally {
        JdbcUtils.close(con);
    }

}
 

@Override
public void run() {
	if(tableInfo.isError()) {
		return;
	}
	
	long[] count = new long[newDnSize];
   	int page=0;
   	List<Map<String, Object>> list=null;
	
   	Connection con = null;
	try {
		con = DataMigratorUtil.getMysqlConnection(srcDn);
		//创建空的中间临时文件
		createTempFiles();
		
		//暂时只实现mysql的分页查询
		list = DataMigratorUtil.executeQuery(con, "select " 
		        + column+ " from " + tableName + " limit ?,?", page++ * pageSize,
		        pageSize);
		int total = 0; //该节点表总数据量
		
		while (!CollectionUtil.isEmpty(list)) {
			if(tableInfo.isError()) {
				return;
			}
			flushData(false);
   			for(int i=0,l=list.size();i<l;i++){
   				Map<String, Object> sf=list.get(i);
				Object objFieldVal = sf.get(column);
				String filedVal = objFieldVal.toString();
				if (objFieldVal instanceof  String){
					filedVal = "'"+filedVal+"'";
				}
   				Integer newIndex=alg.calculate(StringUtil.removeBackquote(objFieldVal.toString()));
   				total++;
   				DataNode newDn = newDnList.get(newIndex);
   				if(!srcDn.equals(newDn)){
   					count[newIndex]++;
   					map.get(newDn).append(filedVal+",");
   				}
   			}
   			list = DataMigratorUtil.executeQuery(con, "select "
                       + column + " from " + tableName + " limit ?,?", page++ * pageSize,
                       pageSize);
   		}
		flushData(true);
		statisticalData(total,count);
	} catch (Exception e) {
		//发生错误，终止此拆分表所有节点线程任务，记录错误信息，退出此拆分表迁移任务
		String message = "["+tableInfo.getSchemaName()+":"+tableName+"]  src dataNode: "+srcDn.getUrl()+
				" prepare temp files is failed! this table's migrator will exit! "+e.getMessage();
		tableInfo.setError(true);
		tableInfo.setErrMessage(message);
		System.out.println(message);
		LOGGER.error(message, e);
	}finally{
		JdbcUtils.close(con);
	}
}
 

@After
public void tearDown() throws Exception {
    JdbcUtils.close(dataSource);
}