下面列出了java.sql.ResultSet#getMetaData ( ) 实例代码，或者点击链接到github查看源代码，也可以在右侧发表评论。
@Test
public void testColumnMetaWithZeroPrecision() throws SQLException
{
Connection connection = getConnection();
Statement statement = connection.createStatement();
statement.execute("create or replace table testColDecimal(cola number(38, 0), " +
"colb number(17, 5))");
ResultSet resultSet = statement.executeQuery("select * from testColDecimal");
ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
assertThat(resultSetMetaData.getColumnType(1), is(Types.BIGINT));
assertThat(resultSetMetaData.getColumnType(2), is(Types.DECIMAL));
assertThat(resultSetMetaData.isSigned(1), is(true));
assertThat(resultSetMetaData.isSigned(2), is(true));
statement.execute("drop table if exists testColDecimal");
connection.close();
}
@Override
public List<Row> handle (ResultSet rs) throws SQLException {
List<Row> results = new ArrayList<>();
int rowId = 0;
ResultSetMetaData metaData = rs.getMetaData();
Map<String, Object> map = null;
for (int i = 1; i <= metaData.getColumnCount(); i++) {
map = new HashMap<String, Object>(2);
map.put("tableName", metaData.getTableName(i));
map.put("columnName", metaData.getColumnName(i));
map.put("columnLabel", metaData.getColumnLabel(i));
map.put("columnType", metaData.getColumnTypeName(i));
map.put("columnSize", String.valueOf(metaData.getColumnDisplaySize(i)));
map.put("precision", String.valueOf(metaData.getPrecision(i)));
map.put("scale", String.valueOf(metaData.getScale(i)));
// map.put("catalog_name", metaData.getCatalogName(i));
// map.put("column_class_name", metaData.getColumnClassName(i));
// map.put("schema_name", metaData.getSchemaName(i));
// map.put("column_type", String.valueOf(metaData.getColumnType(i)));
results.add(new Row(rowId++, map));
}
return results;
}
public static String prettyFormat(ResultSet res) throws SQLException {
StringBuilder sb = new StringBuilder();
ResultSetMetaData rsmd = res.getMetaData();
int numOfColumns = rsmd.getColumnCount();
for (int i = 1; i <= numOfColumns; i++) {
if (i > 1) sb.append(", ");
String columnName = rsmd.getColumnName(i);
sb.append(columnName);
}
sb.append("\n-------------------------------\n");
while (res.next()) {
for (int i = 1; i <= numOfColumns; i++) {
if (i > 1) sb.append(", ");
String columnValue = res.getObject(i).toString();
sb.append(columnValue);
}
sb.append("\n");
}
return sb.toString();
}
/** (Worked before.) */
@Test
public void testInfoSchemaTablesZeroRowsByLimitWorks() throws Exception {
Statement stmt = getConnection().createStatement();
ResultSet results =
stmt.executeQuery(
"SELECT * FROM INFORMATION_SCHEMA.\"TABLES\" LIMIT 0" );
// Result set should still have columns even though there are no rows:
ResultSetMetaData metadata = results.getMetaData();
assertThat( "ResultSetMetaData.getColumnCount() should have been > 0",
metadata.getColumnCount(), not( equalTo( 0 ) ) );
assertThat( "Unexpected non-empty results. Test rot?",
false, equalTo( results.next() ) );
}
public void testJdbc3Support() throws Exception {
Connection conn = sqlSessionFactory.getConfiguration().getEnvironment().getDataSource().getConnection();
PreparedStatement stmt = conn.prepareStatement("insert into users2 values(null, 'Pocoyo')", Statement.RETURN_GENERATED_KEYS);
stmt.addBatch();
stmt.executeBatch();
ResultSet rs = stmt.getGeneratedKeys();
if (rs.next()) {
ResultSetMetaData rsmd = rs.getMetaData();
int colCount = rsmd.getColumnCount();
do {
for (int i = 1; i <= colCount; i++) {
String key = rs.getString(i);
System.out.println("key " + i + " is " + key);
}
} while (rs.next());
} else {
System.out.println("There are no generated keys.");
}
}
private Pair<List<List<String>>, List<SelectedColumnMeta>> createResponseFromResultSet(ResultSet resultSet)
throws Exception {
List<List<String>> results = Lists.newArrayList();
List<SelectedColumnMeta> columnMetas = Lists.newArrayList();
ResultSetMetaData metaData = resultSet.getMetaData();
int columnCount = metaData.getColumnCount();
// Fill in selected column meta
for (int i = 1; i <= columnCount; ++i) {
columnMetas.add(new SelectedColumnMeta(metaData.isAutoIncrement(i), metaData.isCaseSensitive(i),
metaData.isSearchable(i), metaData.isCurrency(i), metaData.isNullable(i), metaData.isSigned(i),
metaData.getColumnDisplaySize(i), metaData.getColumnLabel(i), metaData.getColumnName(i),
metaData.getSchemaName(i), metaData.getCatalogName(i), metaData.getTableName(i),
metaData.getPrecision(i), metaData.getScale(i), metaData.getColumnType(i),
metaData.getColumnTypeName(i), metaData.isReadOnly(i), metaData.isWritable(i),
metaData.isDefinitelyWritable(i)));
}
// fill in results
while (resultSet.next()) {
List<String> oneRow = Lists.newArrayListWithCapacity(columnCount);
for (int i = 0; i < columnCount; i++) {
oneRow.add((resultSet.getString(i + 1)));
}
results.add(oneRow);
}
return new Pair<>(results, columnMetas);
}
public Object getValidateSource(String id, ResultSet rs) throws SQLException
{
ResultSetMetaData metadata = rs.getMetaData();
byte[] rv = null;
switch(metadata.getColumnType(1))
{
case Types.BLOB:
Blob blob = rs.getBlob(1);
if(blob != null)
{
rv = blob.getBytes(1L, (int) blob.length());
}
else
{
log.info("getValidateSource(" + id + ") blob == null" );
}
break;
case Types.CLOB:
Clob clob = rs.getClob(1);
if(clob != null)
{
rv = clob.getSubString(1L, (int) clob.length()).getBytes();
}
break;
case Types.CHAR:
case Types.LONGVARCHAR:
case Types.VARCHAR:
rv = rs.getString(1).getBytes();
break;
case Types.BINARY:
case Types.VARBINARY:
case Types.LONGVARBINARY:
rv = rs.getBytes(1);
break;
}
return rv;
}
public <T> T convert(ResultSet rs, Class<T> cls) throws SQLException {
T obj = null;
boolean isNull = true;
try {
ResultSetMetaData md = rs.getMetaData();
List<String> list = new ArrayList<String>();
for (int j = 1; j <= md.getColumnCount(); j++) {
list.add(md.getColumnName(j));
}
obj = cls.newInstance();
Field[] fields = obj.getClass().getDeclaredFields();
for (Field field : fields) {
String fieldName = field.getName();
String columnName = convertColumnName(fieldName);
if (list.contains(columnName)) {
isNull = false;
@SuppressWarnings("rawtypes")
Class fieldType = field.getType();
field.setAccessible(true);
field.set(obj, convertColumnValue(fieldType.getName(), columnName, rs));
}
}
}
catch (Exception e) {
// logger.error("Convert DB Data FAIL", e);
}
return isNull ? null : obj;
}
/**
* Tests that <code>CallableStatement.executeQuery()</code> fails
* when multiple result sets are returned.
* @exception SQLException if a database error occurs
*/
public void testExecuteQueryWithOutgoingResultSetInDerbyCall()
throws SQLException {
setup();
CallableStatement cs = prepareCall("CALL RETRIEVE_OUTGOING_RESULTS(?)");
cs.setInt(1, 2);
cs.execute();
String[][] results=new String[2][10];
results[0][0]="1";
for(int i=0; i<10; i++) {
results[1][i]=i+"String"+i;
}
int[] numRows={0,9};
int rsIndex=-1;
do {
++rsIndex;
int rowIndex=0;
ResultSet rs = cs.getResultSet();
ResultSetMetaData metaData = rs.getMetaData();
int rowCount = metaData.getColumnCount();
while (rs.next()) {
String row="";
for (int i = 1; i <=rowCount; ++i) {
Object value = rs.getObject(i);
row+=value.toString();
}
if(rsIndex>1 || rowIndex>numRows[rsIndex]) {
fail("the result is not correct!");
}
assertEquals(results[rsIndex][rowIndex], row);
++rowIndex;
}
} while (cs.getMoreResults());
}
/**
* Iterates over a row vector, comparing each to the corrsponding
* column in the ResultSet. The i'th entry in the row vector is
* compared (using assertEquals) to the return value from
* getObject(i) on the ResultSet.
* @param message info from the caller
* @param expected the expected row vector
* @param returned the resultset to verify
*/
private static void assertRow(String message,
Object[] expected,
ResultSet returned) throws SQLException {
final ResultSetMetaData rmd = returned.getMetaData();
assertEquals(message+" columns:", expected.length,
rmd.getColumnCount());
for (int i = 0; i < expected.length; ++i) {
assertEquals(message+
rmd.getColumnLabel(i+1)+") ",
expected[i],
returned.getObject(i+1));
}
}
/**
* Tests that <code>CallableStatement.executeQuery()</code> fails
* when multiple result sets are returned.
* @exception SQLException if a database error occurs
*/
public void testExecuteQueryWithOutgoingResultSetInDerbyCall()
throws Exception {
setup();
CallableStatement cs = prepareCall("CALL RETRIEVE_OUTGOING_RESULTS(?)");
cs.setInt(1, 2);
cs.execute();
String[][] results=new String[2][10];
results[0][0]="1";
for(int i=0; i<10; i++) {
results[1][i]=i+"String"+i;
}
int[] numRows={0,9};
int rsIndex=-1;
do {
++rsIndex;
int rowIndex=0;
ResultSet rs = cs.getResultSet();
ResultSetMetaData metaData = rs.getMetaData();
int rowCount = metaData.getColumnCount();
while (rs.next()) {
String row="";
for (int i = 1; i <=rowCount; ++i) {
Object value = rs.getObject(i);
row+=value.toString();
}
if(rsIndex>1 || rowIndex>numRows[rsIndex]) {
fail("the result is not correct!");
}
if(!row.equals(results[rsIndex][rowIndex])) {
fail("the result is not correct!");
}
++rowIndex;
}
} while (cs.getMoreResults());
}
/**
* Create record and add it to {@link com.streamsets.pipeline.api.BatchMaker}
*/
@Override
public void createAndAddRecord(
ResultSet rs,
TableRuntimeContext tableRuntimeContext,
BatchContext batchContext
) throws SQLException, StageException {
ResultSetMetaData md = rs.getMetaData();
LinkedHashMap<String, Field> fields = jdbcUtil.resultSetToFields(
rs,
commonSourceConfigBean,
errorRecordHandler,
tableJdbcConfigBean.unknownTypeAction,
recordHeader,
DatabaseVendor.SQL_SERVER
);
Map<String, String> columnOffsets = new HashMap<>();
// Generate Offset includes primary keys, sys_change_version, and sys_change_operation
for (String key : tableRuntimeContext.getSourceTableContext().getOffsetColumns()) {
String value = rs.getString(key);
if (Strings.isNullOrEmpty(value)) {
value = fields.get(key) != null ? fields.get(key).getValueAsString() : "";
}
columnOffsets.put(key, value);
}
columnOffsets.put(SYS_CHANGE_OPERATION, rs.getString(SYS_CHANGE_OPERATION));
String offsetFormat = OffsetQueryUtil.getOffsetFormat(columnOffsets);
Record record = context.createRecord(tableRuntimeContext.getQualifiedName() + "::" + offsetFormat);
record.set(Field.createListMap(fields));
//Set Column Headers
jdbcUtil.setColumnSpecificHeaders(
record,
Collections.singleton(tableRuntimeContext.getSourceTableContext().getTableName()),
md,
JDBC_NAMESPACE_HEADER
);
//Set Operation Headers
int op = MSOperationCode.convertToJDBCCode(rs.getString(SYS_CHANGE_OPERATION));
record.getHeader().setAttribute(OperationType.SDC_OPERATION_TYPE, String.valueOf(op));
for (String fieldName : recordHeader) {
record.getHeader().setAttribute(JDBC_NAMESPACE_HEADER + fieldName, rs.getString(fieldName) != null ? rs.getString(fieldName) : "NULL" );
}
batchContext.getBatchMaker().addRecord(record);
offsets.put(tableRuntimeContext.getOffsetKey(), offsetFormat);
}
protected String toJsonString(ResultSet rs) throws SQLException, JSONException {
ResultSetMetaData rsmd = rs.getMetaData();
JSONArray array = new JSONArray();
int numColumns = rsmd.getColumnCount();
while (rs.next()) {
JSONObject obj = new JSONObject();
for (int i = 1; i < numColumns + 1; i++) {
String column_label = rsmd.getColumnLabel(i);
log.debug("Column Name=" + column_label + ",type=" + rsmd.getColumnType(i));
switch (rsmd.getColumnType(i)) {
case Types.ARRAY:
obj.put(column_label, rs.getArray(i));
break;
case Types.BIGINT:
obj.put(column_label, rs.getInt(i));
break;
case Types.BOOLEAN:
obj.put(column_label, rs.getBoolean(i));
break;
case Types.BLOB:
obj.put(column_label, rs.getBlob(i));
break;
case Types.DOUBLE:
obj.put(column_label, rs.getDouble(i));
break;
case Types.FLOAT:
obj.put(column_label, rs.getFloat(i));
break;
case Types.INTEGER:
obj.put(column_label, rs.getInt(i));
break;
case Types.NVARCHAR:
obj.put(column_label, rs.getNString(i));
break;
case Types.VARCHAR:
obj.put(column_label, rs.getString(i));
break;
case Types.TINYINT:
obj.put(column_label, rs.getInt(i));
break;
case Types.SMALLINT:
obj.put(column_label, rs.getInt(i));
break;
case Types.DATE:
obj.put(column_label, rs.getDate(i));
break;
case Types.TIMESTAMP:
obj.put(column_label, rs.getTimestamp(i));
break;
default:
obj.put(column_label, rs.getObject(i));
break;
}
}
array.put(obj);
}
return array.toString();
}
public MemoryQueryResult(final ResultSet resultSet) throws SQLException {
resultSetMetaData = resultSet.getMetaData();
rows = getRows(resultSet);
}
@SuppressWarnings( "unused" )
public static Object fireToDB( ScriptEngine actualContext, Bindings actualObject, Object[] ArgList,
Object FunctionContext ) {
Object oRC = new Object();
if ( ArgList.length == 2 ) {
try {
Object scmO = actualObject.get( "_transform_" );
Script scm = (Script) scmO;
String strDBName = (String) ArgList[ 0 ];
String strSQL = (String) ArgList[ 1 ];
DatabaseMeta ci = DatabaseMeta.findDatabase( scm.getPipelineMeta().getDatabases(), strDBName );
if ( ci == null ) {
throw new RuntimeException( "Database connection not found: " + strDBName );
}
ci.shareVariablesWith( scm );
Database db = new Database( scm, ci );
db.setQueryLimit( 0 );
try {
db.connect( scm.getPartitionID() );
ResultSet rs = db.openQuery( strSQL );
ResultSetMetaData resultSetMetaData = rs.getMetaData();
int columnCount = resultSetMetaData.getColumnCount();
if ( rs != null ) {
List<Object[]> list = new ArrayList<Object[]>();
while ( rs.next() ) {
Object[] objRow = new Object[ columnCount ];
for ( int i = 0; i < columnCount; i++ ) {
objRow[ i ] = rs.getObject( i + 1 );
}
list.add( objRow );
}
Object[][] resultArr = new Object[ list.size() ][];
list.toArray( resultArr );
db.disconnect();
return resultArr;
}
} catch ( Exception er ) {
throw new RuntimeException( er.toString() );
}
} catch ( Exception e ) {
throw new RuntimeException( e.toString() );
}
} else {
throw new RuntimeException( "The function call fireToDB requires 2 arguments." );
}
return oRC;
}
@Test
public void testIssue18() throws Exception
{
con.close();
con = DriverManager.getConnection(String.format("jdbc:cassandra://%s:%d/%s",HOST,PORT,KEYSPACE));
Statement statement = con.createStatement();
String truncate = "TRUNCATE regressiontest;";
statement.execute(truncate);
String insert1 = "INSERT INTO regressiontest (keyname,bValue,iValue) VALUES( 'key0',true, 2000);";
statement.executeUpdate(insert1);
String insert2 = "INSERT INTO regressiontest (keyname,bValue) VALUES( 'key1',false);";
statement.executeUpdate(insert2);
String select = "SELECT * from regressiontest;";
ResultSet result = statement.executeQuery(select);
ResultSetMetaData metadata = result.getMetaData();
int colCount = metadata.getColumnCount();
System.out.println("Before doing a next()");
System.out.printf("(%d) ",result.getRow());
for (int i = 1; i <= colCount; i++)
{
System.out.print(showColumn(i,result)+ " ");
}
System.out.println();
System.out.println("Fetching each row with a next()");
while (result.next())
{
metadata = result.getMetaData();
colCount = metadata.getColumnCount();
System.out.printf("(%d) ",result.getRow());
for (int i = 1; i <= colCount; i++)
{
System.out.print(showColumn(i,result)+ " ");
}
System.out.println();
}
}
public void executeQueries(boolean prepare,boolean verbose) throws SQLException{
rowsExpected=new int[queries.size()]; //initialize the array with correct size
String query="";
if(prepare){
if (verbose)
System.out.println("=====================> Using java.sql.PreparedStatement <====================");
}else{
if (verbose)
System.out.println("=====================> Using java.sql.Statement <====================");
}
try{
for(int k=0;k<queries.size();k++){
query=(String)queries.get(k);
String [] times=new String [StaticValues.ITER];
int rowsReturned=0;
for (int i=0;i<StaticValues.ITER;i++){
Statement stmt=null;
ResultSet rs=null;
PreparedStatement pstmt=null;
if(prepare){
pstmt=conn.prepareStatement(query);
}else{
stmt=conn.createStatement();
}
long start=System.currentTimeMillis();
if(prepare)
rs=pstmt.executeQuery();
else
rs=stmt.executeQuery(query);
ResultSetMetaData rsmd=rs.getMetaData();
int totalCols=rsmd.getColumnCount();
while(rs.next()){
String row="";
for(int j=1;j<=totalCols;j++){
row+=rs.getString(j)+" | ";
}
rowsReturned++;
}
long time_taken=(System.currentTimeMillis() - start);
if (verbose){
System.out.println("Time required to execute:");
System.out.println(query);
System.out.println("Total Rows returned = "+rowsReturned);
System.out.println("==> "+time_taken+" milliseconds "+" OR "+TestUtils.getTime(time_taken));
times[i]=TestUtils.getTime(time_taken);
}
rs.close();
if(prepare){
pstmt.close();
}else{
stmt.close();
}
rowsExpected[k]=rowsReturned;//add expected rows for respective queries
rowsReturned=0;
}//end for loop to run StaticValues.ITER times
if(prepare){
prepStmtRunResults.add(times);
}else{
stmtRunResults.add(times);
}
}
}catch(SQLException sqe){
throw new SQLException("Failed query:\n "+query+"\n SQLState= "+sqe.getSQLState()+"\n ErrorCode= "+sqe.getErrorCode()+"\n Message= "+sqe.getMessage());
}
}
public void testDataAwareProcedureCallUsingGlobalIndex()
throws Exception {
setup();
CallableStatement cs = prepareCall("CALL RETRIEVE_DYNAMIC_RESULTS(?) ON TABLE EMP.PARTITIONTESTTABLE1 WHERE SECONDID in (?,?,?) AND THIRDID='3'");
cs.setInt(1, 2);
cs.setInt(2, 3);
cs.setInt(3, 4);
cs.setInt(4, 5);
cs.execute();
String[][] results=new String[2][2];
results[0][0]="1";
results[0][1]="1";
results[1][0]="1";
results[1][1]="1";
int rsIndex=-1;
do {
++rsIndex;
int rowIndex=0;
ResultSet rs = cs.getResultSet();
ResultSetMetaData metaData = rs.getMetaData();
int rowCount = metaData.getColumnCount();
while (rs.next()) {
String row="";
for (int i = 1; i <=rowCount; ++i) {
Object value = rs.getObject(i);
row+=value.toString();
}
if(rsIndex>1 || rowIndex>1) {
fail("the result is not correct!");
}
if(!row.equals(results[rsIndex][rowIndex])) {
fail("the result is not correct!");
}
++rowIndex;
}
if(rsIndex<=1 && rowIndex!=2) {
fail("the number of row to be excpected is "+2+ " not "+rowIndex);
}
} while (cs.getMoreResults());
if(rsIndex!=3) {
fail("the number of result sets to be excpected is 4 not" + (rsIndex+1));
}
}
private Record processRow(ResultSet resultSet, long rowCount) throws SQLException {
Source.Context context = getContext();
ResultSetMetaData md = resultSet.getMetaData();
int numColumns = md.getColumnCount();
LinkedHashMap<String, Field> fields = jdbcUtil.resultSetToFields(
resultSet,
commonSourceConfigBean,
errorRecordHandler,
unknownTypeAction,
null,
hikariConfigBean.getVendor()
);
if (fields.size() != numColumns) {
errorRecordHandler.onError(JdbcErrors.JDBC_35, fields.size(), numColumns);
return null; // Don't output this record.
}
final String recordContext = StringUtils.substring(query.replaceAll("[\n\r]", ""), 0, 100) + "::rowCount:" + rowCount + (StringUtils.isEmpty(offsetColumn) ? "" : ":" + resultSet.getString(offsetColumn));
Record record = context.createRecord(recordContext);
if (jdbcRecordType == JdbcRecordType.LIST_MAP) {
record.set(Field.createListMap(fields));
} else if (jdbcRecordType == JdbcRecordType.MAP) {
record.set(Field.create(fields));
} else {
// type is LIST
List<Field> row = new ArrayList<>();
for (Map.Entry<String, Field> fieldInfo : fields.entrySet()) {
Map<String, Field> cell = new HashMap<>();
cell.put("header", Field.create(fieldInfo.getKey()));
cell.put("value", fieldInfo.getValue());
row.add(Field.create(cell));
}
record.set(Field.create(row));
}
if (createJDBCNsHeaders) {
jdbcUtil.setColumnSpecificHeaders(record, Collections.emptySet(), md, jdbcNsHeaderPrefix);
}
// We will add cdc operation type to record header even if createJDBCNsHeaders is false
// we currently support CDC on only MS SQL.
if (hikariConfigBean.getConnectionString().startsWith("jdbc:sqlserver")) {
MSOperationCode.addOperationCodeToRecordHeader(record);
}
return record;
}
private static void dumpRS(ResultSet s) throws SQLException
{
if (s == null)
{
System.out.println("<NULL>");
return;
}
ResultSetMetaData rsmd = s.getMetaData();
// Get the number of columns in the result set
int numCols = rsmd.getColumnCount();
if (numCols <= 0)
{
System.out.println("(no columns!)");
return;
}
StringBuilder heading = new StringBuilder("\t ");
StringBuilder underline = new StringBuilder("\t ");
int len;
// Display column headings
for (int i=1; i<=numCols; i++)
{
if (i > 1)
{
heading.append(",");
underline.append(" ");
}
len = heading.length();
heading.append(rsmd.getColumnLabel(i));
len = heading.length() - len;
for (int j = len; j > 0; j--)
{
underline.append("-");
}
}
System.out.println(heading.toString());
System.out.println(underline.toString());
StringBuilder row = new StringBuilder();
// Display data, fetching until end of the result set
while (s.next())
{
row.append("\t{");
// Loop through each column, getting the
// column data and displaying
for (int i=1; i<=numCols; i++)
{
if (i > 1) row.append(",");
row.append(s.getString(i));
}
row.append("}\n");
}
System.out.println(row.toString());
s.close();
}