Java JdbcUtils类代码示例

import org.springframework.jdbc.support.JdbcUtils; //导入依赖的package包/类
/**
 * Executes the SQL as specified by {@link #getSequenceQuery()}.
 */
@Override
protected long getNextKey() throws DataAccessException {
	Connection con = DataSourceUtils.getConnection(getDataSource());
	Statement stmt = null;
	ResultSet rs = null;
	try {
		stmt = con.createStatement();
		DataSourceUtils.applyTransactionTimeout(stmt, getDataSource());
		rs = stmt.executeQuery(getSequenceQuery());
		if (rs.next()) {
			return rs.getLong(1);
		}
		else {
			throw new DataAccessResourceFailureException("Sequence query did not return a result");
		}
	}
	catch (SQLException ex) {
		throw new DataAccessResourceFailureException("Could not obtain sequence value", ex);
	}
	finally {
		JdbcUtils.closeResultSet(rs);
		JdbcUtils.closeStatement(stmt);
		DataSourceUtils.releaseConnection(con, getDataSource());
	}
}
 

import org.springframework.jdbc.support.JdbcUtils; //导入依赖的package包/类
/**
 * Implementation of ResultSetCallbackHandler.
 * Work out column size if this is the first row, otherwise just count rows.
 * <p>Subclasses can perform custom extraction or processing
 * by overriding the {@code processRow(ResultSet, int)} method.
 * @see #processRow(java.sql.ResultSet, int)
 */
@Override
public final void processRow(ResultSet rs) throws SQLException {
	if (this.rowCount == 0) {
		ResultSetMetaData rsmd = rs.getMetaData();
		this.columnCount = rsmd.getColumnCount();
		this.columnTypes = new int[this.columnCount];
		this.columnNames = new String[this.columnCount];
		for (int i = 0; i < this.columnCount; i++) {
			this.columnTypes[i] = rsmd.getColumnType(i + 1);
			this.columnNames[i] = JdbcUtils.lookupColumnName(rsmd, i + 1);
		}
		// could also get column names
	}
	processRow(rs, this.rowCount++);
}
 

import org.springframework.jdbc.support.JdbcUtils; //导入依赖的package包/类
/**
 * Execute the given schema script on the given JDBC Connection.
 * <p>Note that the default implementation will log unsuccessful statements
 * and continue to execute. Override the {@code executeSchemaStatement}
 * method to treat failures differently.
 * @param con the JDBC Connection to execute the script on
 * @param sql the SQL statements to execute
 * @throws SQLException if thrown by JDBC methods
 * @see #executeSchemaStatement
 */
protected void executeSchemaScript(Connection con, String[] sql) throws SQLException {
	if (sql != null && sql.length > 0) {
		boolean oldAutoCommit = con.getAutoCommit();
		if (!oldAutoCommit) {
			con.setAutoCommit(true);
		}
		try {
			Statement stmt = con.createStatement();
			try {
				for (String sqlStmt : sql) {
					executeSchemaStatement(stmt, sqlStmt);
				}
			}
			finally {
				JdbcUtils.closeStatement(stmt);
			}
		}
		finally {
			if (!oldAutoCommit) {
				con.setAutoCommit(false);
			}
		}
	}
}
 

import org.springframework.jdbc.support.JdbcUtils; //导入依赖的package包/类
private static Collection<String> readPrimaryKeyNames(DatabaseMetaDataWrapper metaData, String tableName)
                                                                                                         throws SQLException {
    ResultSet pkData = null;

    try {
        List<String> pks = new ArrayList<String>();
        Map<String, Object> values;

        for (pkData = metaData.getPrimaryKeys(tableName); pkData.next(); pks.add(readPrimaryKeyName(metaData,
            values))) {
            values = readColumns(pkData, initColumnsForPK());
        }

        return pks;
    } finally {
        JdbcUtils.closeResultSet(pkData);
    }
}
 

import org.springframework.jdbc.support.JdbcUtils; //导入依赖的package包/类
@Override
public T mapRow(ResultSet rs, int rowNum) throws SQLException {
    T mappedObject = this.entityMapper.createInstance();
    ResultSetMetaData rsmd = rs.getMetaData();
    int columnCount = rsmd.getColumnCount();
    Map<String, Class<?>> types = this.entityMapper.types();

    this.entityMapper.startMapping(mappedObject);
    for (int index = 1; index <= columnCount; index++) {
        String column = JdbcUtils.lookupColumnName(rsmd, index);
        Object value = JdbcUtils.getResultSetValue(rs, index, types.get(column));

        this.entityMapper.setValue(mappedObject, column, value);
    }
    this.entityMapper.finishMapping(mappedObject);
    return mappedObject;
}
 

import org.springframework.jdbc.support.JdbcUtils; //导入依赖的package包/类
/**
 * <B>方法名称：</B>映射行数据<BR>
 * <B>概要说明：</B><BR>
 * 
 * @param rs 结果集
 * @param row 行号
 * @return JSONObject 数据
 * @throws SQLException SQL异常错误
 * @see RowMapper#mapRow(ResultSet,
 *      int)
 */
public JSONObject mapRow(ResultSet rs, int row) throws SQLException {
    String key = null;
    Object obj = null;
    JSONObject json = new JSONObject();
    ResultSetMetaData rsmd = rs.getMetaData();
    int count = rsmd.getColumnCount();
    for (int i = 1; i <= count; i++) {
        key = JdbcUtils.lookupColumnName(rsmd, i);
        obj = JdbcUtils.getResultSetValue(rs, i);
        try {
            json.put(key, obj);
        }
        catch (JSONException e) {
        }
    }
    return json;
}
 

import org.springframework.jdbc.support.JdbcUtils; //导入依赖的package包/类
public List<String> findDefaultColumnType(String dbInfoId) throws Exception {
	DataSource ds = this.getDataSourceByDbInfoId(dbInfoId);
	Connection conn = null;
	ResultSet resultSet = null;
	try {
		conn = DataSourceUtils.getConnection(ds);
		DatabaseMetaData metaData = conn.getMetaData();
		resultSet = metaData.getTypeInfo();
		List<String> list = new ArrayList<String>();
		while (resultSet.next()) {
			String typeName = resultSet.getString("TYPE_NAME").toUpperCase();
			list.add(typeName);
		}
		return list;
	} finally {
		JdbcUtils.closeResultSet(resultSet);
		JdbcUtils.closeConnection(conn);
	}
}
 

import org.springframework.jdbc.support.JdbcUtils; //导入依赖的package包/类
public List<String> findTablePrimaryKeys(String dbInfoId, String tableName) throws Exception {
	List<String> primaryKeys = new ArrayList<String>();
	Connection con = null;
	ResultSet rs = null;
	DataSource ds = this.getDataSourceByDbInfoId(dbInfoId);
	try {
		con = ds.getConnection();
		DatabaseMetaData metaData = con.getMetaData();
		rs = metaData.getPrimaryKeys(null, null, tableName.toUpperCase());
		while (rs.next()) {
			primaryKeys.add(rs.getString("COLUMN_NAME").toUpperCase());
		}
		return primaryKeys;
	} finally {
		JdbcUtils.closeResultSet(rs);
		JdbcUtils.closeConnection(con);
	}
}
 

import org.springframework.jdbc.support.JdbcUtils; //导入依赖的package包/类
/**
 * 初始化默认数据库配置信息
 * 
 * @return 返回DbInfo对象
 * @throws Exception
 */
public DbInfo initDefaultDbInfo() throws Exception {
	DbInfo dbInfo = new DbInfo();
	dbInfo.setId(DbConstants.DEFAULTDATASOURCE);
	Connection conn = null;
	try {
		conn = this.getJdbcTemplate().getDataSource().getConnection();
		DatabaseMetaData metaData = conn.getMetaData();
		dbInfo.setDbType(metaData.getDatabaseProductName());
		dbInfo.setName("默认连接" + dbInfo.getDbType());
		dbInfo.setUrl(metaData.getURL());
		dbInfo.setUsername(metaData.getUserName());
		dbInfo.setProductName(metaData.getDatabaseProductName());
		dbInfo.setProductVersion(metaData.getDatabaseProductVersion());
	} finally {
		JdbcUtils.closeConnection(conn);
	}
	return dbInfo;

}
 

import org.springframework.jdbc.support.JdbcUtils; //导入依赖的package包/类
private List<String> loadTablePrimaryKeys(String tableName)throws Exception{
	DataSource ds=this.getJdbcTemplate().getDataSource();
	Connection con = DataSourceUtils.getConnection(ds);
	List<String> primaryKeyList=new ArrayList<String>();
	Statement stmt = null;
	ResultSet rs=null;
	try{
		DatabaseMetaData metaData = con.getMetaData();
		rs = metaData.getPrimaryKeys(null, null, tableName.toUpperCase());
		while (rs.next()) {
			primaryKeyList.add(rs.getString("COLUMN_NAME"));
		}
	}finally{
		JdbcUtils.closeResultSet(rs);
		JdbcUtils.closeStatement(stmt);
		JdbcUtils.closeConnection(con);
	}
	return primaryKeyList;
}
 

import org.springframework.jdbc.support.JdbcUtils; //导入依赖的package包/类
private String loadTablePrimaryKeys(String tableName,DatabaseMetaData metaData) throws Exception{
	String keys="";
	ResultSet rs = null;
	try {
		rs = metaData.getPrimaryKeys(null, null, tableName.toUpperCase());
		int i=0;
		while (rs.next()) {
			if(i>0){
				keys+=",";
			}
			i++;
			keys+=rs.getString("COLUMN_NAME");
		}
	} finally {
		JdbcUtils.closeResultSet(rs);
	}
	return keys;
}
 

import org.springframework.jdbc.support.JdbcUtils; //导入依赖的package包/类
@Override
public Map<String, Object>  mapRow(ResultSet rs, int rowNum) throws SQLException {
	ResultSetMetaData rsmd = rs.getMetaData();
	int columnCount = rsmd.getColumnCount();
	Map mapOfColValues = createColumnMap(columnCount);
	for (int i = 1; i <= columnCount; i++) {
		String key = getColumnKey(JdbcUtils.lookupColumnName(rsmd, i));
		key = key.toLowerCase();
		Object obj = null;
		String typename= rsmd.getColumnTypeName(i).toUpperCase();
		if("DECIMAL".equals(typename)){
			obj = rs.getDouble(i);
		}else{
			obj = getColumnValue(rs, i);
		}
		 
		mapOfColValues.put(key, obj);
	}
	return mapOfColValues;
}
 

import org.springframework.jdbc.support.JdbcUtils; //导入依赖的package包/类
/**
 * 根据mlog物化日志表的SEQUENCE$$字段单独查询
 *
 * @param sequence
 * @param context
 * @param columns
 * @return
 */
private List<ColumnValue> getSingleMlogRecord(final String singleMlogExtractSql, final int sequence, final YuGongContext context, final List<ColumnMeta> mlogCols, final List<ColumnMeta> columns, final List<ColumnValue> rowIds) {
    JdbcTemplate jdbcTemplate = new JdbcTemplate(context.getSourceDs());
    final List<ColumnValue> columnValues = new ArrayList<ColumnValue>();
    return (List<ColumnValue>) jdbcTemplate.execute(singleMlogExtractSql, new PreparedStatementCallback() {
        @Override
        public List<ColumnValue> doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
            ps.setInt(1, sequence);
            ResultSet rs = ps.executeQuery();
            try {
                while (rs.next()) {
                    rowIds.add(new ColumnValue(rowidColumn, rs.getObject("rowid")));
                    columnValues.addAll(buildColumnValue(rs, context.getSourceEncoding(), mlogCols, columns));
                }
            } finally {
                JdbcUtils.closeResultSet(rs);
            }
            return columnValues;
        }
    });
}
 

import org.springframework.jdbc.support.JdbcUtils; //导入依赖的package包/类
/**
 * 获取DRDS下表的拆分字段, 返回格式为 id,name
 *
 * @param dataSource
 * @param schemaName
 * @param tableName
 * @return
 */
public static String getShardKeyByDRDS(final DataSource dataSource, final String schemaName, final String tableName) {
    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
    return (String) jdbcTemplate.execute(queryShardKey, new PreparedStatementCallback() {

        public Object doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
            DatabaseMetaData metaData = ps.getConnection().getMetaData();
            // String sName = getIdentifierName(schemaName, metaData);
            String tName = getIdentifierName(tableName, metaData);

            ps.setString(1, tName);
            ResultSet rs = null;
            try {
                rs = ps.executeQuery();
                String log = null;
                if (rs.next()) {
                    log = rs.getString("KEYS");
                }

                return log;
            } finally {
                JdbcUtils.closeResultSet(rs);
            }
        }
    });
}
 

import org.springframework.jdbc.support.JdbcUtils; //导入依赖的package包/类
/**
 * @return the current database url and user name (for a given Datasource)
 * @throws RuntimeException when the database metadata cannot be retrieved
 */
public static String getDatabaseURLAndUserName(DataSource dataSource) {
    DatabaseMetaDataCallback callback = new DatabaseMetaDataCallback() {
        public Object processMetaData(DatabaseMetaData dbmd) throws SQLException, MetaDataAccessException {
            String url = dbmd.getURL();
            String userName = dbmd.getUserName();

            StringBuilder s = new StringBuilder(url);
            s.append(" (userName='");
            s.append(userName);
            s.append("')");
            return s.toString();
        }
    };
    try {
        return (String) JdbcUtils.extractDatabaseMetaData(dataSource, callback);
    } catch (Exception e) {
        throw new RuntimeException("unable to read database metadata", e);
    }
}