Java Code Examples for java.sql.ResultSetMetaData#getColumnCount()

/**
 * Execute query.
 *
 * @param query the query
 * @return the list
 */
public static List<Map<String, String>> executeQuery(String query) {
	List<Map<String, String>> results = new ArrayList<>();
	try (Connection conn = getConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(query);) {
		ResultSetMetaData rsmd = rs.getMetaData();
		int columnCount = rsmd.getColumnCount();
		Map<String, String> data;
		while (rs.next()) {
			data = new LinkedHashMap<>();
			for (int i = 1; i <= columnCount; i++) {
				data.put(rsmd.getColumnName(i), rs.getString(i));
			}
			results.add(data);
		}
	} catch (Exception ex) {
		ex.printStackTrace();
		LOGGER.error("Error Executing Query", Util.getStackTrace(ex));
	}
	return results;
}
 

private void compareRowSets(CachedRowSet crs, CachedRowSet crs1) throws Exception {

        int rows = crs.size();
        assertTrue(rows == crs1.size());

        ResultSetMetaData rsmd = crs.getMetaData();

        compareMetaData(rsmd, crs1.getMetaData());
        int cols = rsmd.getColumnCount();

        for (int row = 1; row <= rows; row++) {
            crs.absolute((row));
            crs1.absolute(row);
            for (int col = 1; col <= cols; col++) {
                compareColumnValue(JDBCType.valueOf(rsmd.getColumnType(col)),
                        crs, crs1, col);
            }
        }

    }
 

private TypeHandler<?> resolveTypeHandler(ResultSet rs, String column) {
	try {
		Map<String, Integer> columnIndexLookup;
		columnIndexLookup = new HashMap<String, Integer>();
		ResultSetMetaData rsmd = rs.getMetaData();
		int count = rsmd.getColumnCount();
		for (int i = 1; i <= count; i++) {
			String name = rsmd.getColumnName(i);
			columnIndexLookup.put(name, i);
		}
		Integer columnIndex = columnIndexLookup.get(column);
		TypeHandler<?> handler = null;
		if (columnIndex != null) {
			handler = resolveTypeHandler(rsmd, columnIndex);
		}
		if (handler == null || handler instanceof UnknownTypeHandler) {
			handler = OBJECT_TYPE_HANDLER;
		}
		return handler;
	} catch (SQLException e) {
		throw new TypeException("Error determining JDBC type for column " + column + ".  Cause: " + e, e);
	}
}
 

public static String getResultSetStr(ResultSet rs, int colWidths)
throws SQLException {
  if (rs == null) {
    return "null";
  }
  ResultSetMetaData rsmd = rs.getMetaData();
  int colCount = rsmd.getColumnCount();
  StringBuffer sb = new StringBuffer();
  for (int i = 1; i <= colCount; i++) {
    String label = String.format("%1$#" + colWidths + "s", rsmd.getColumnLabel(i));
    sb.append(label + "|");
  }
  sb.append("\n");
  while (rs.next()) {
    StringBuffer row = new StringBuffer();
    for (int i = 1; i <= colCount; i++) {
      String value = rs.getString(i);
      if (value == null) {
        value = "null";
      }
      value = value.substring(0, Math.min(20, value.length()));
      value = String.format("%1$#" + colWidths + "s", value.trim());
      row.append(value + "|");
    }
    sb.append("\n");
  }
  return sb.toString();
}
 

private Vector<byte[]> convertToVector(ResultSet rs) throws Exception {
  Vector<byte[]> v = new Vector<byte[]>();
  ResultSetMetaData rsmd = rs.getMetaData();
  while (rs.next()) {
    for (int i = 1; i <= rsmd.getColumnCount(); i++) {
      v.add(rs.getBytes(i));
    }
  }

  return v;
}
 

public Record[] handle(ResultSet rSet) throws SQLException {
	ResultSetMetaData meta = rSet.getMetaData();
	List<Record> recordList = new ArrayList<Record>();
	Record record = null;
	List<Value> valueList = null;
	
	if (startRow != null && startRow > 0) {
		for (int i = 0; i < startRow - 1 && rSet.next(); i++);
	}
	
	long nbrRows = 0;
	String columnName;
	while (rSet.next()) {
		record = new Record();
		recordList.add(record);
					
		valueList = new ArrayList<Value>();
		for (int columnIndex = 1; columnIndex <= meta.getColumnCount(); columnIndex++) {
			// TODO Normalize values (e.g. long varchars, etc.
			
			// Alias field impl
			columnName=meta.getColumnName(columnIndex);
			if (this.getAliasMap().containsKey(columnName)) {
				columnName=this.getAliasMap().get(columnName);
			}
			
			valueList.add(new Value(columnName, rSet.getObject(columnIndex)));
		}
		record.setValues(valueList.toArray(new Value[0]));
		
		nbrRows++;
		if (maxRows != null && maxRows > 0 && nbrRows >= maxRows) {
			break;
		}
	}

	return recordList.toArray(new Record[0]);
}
 

/**
 * 
 * @param rs
 * @param builder
 * @throws SQLException
 */
private void populate(ResultSet rs, Message.Builder builder)
		throws SQLException {
	ResultSetMetaData metaData = rs.getMetaData();
	int columnCount = metaData.getColumnCount();// 列个数
	String columnLabel = null;// 列名
	Object columnValue = null;// 列值
	Descriptors.FieldDescriptor fieldDescriptor = null;
	for (int i = 1; i <= columnCount; i++) {
		columnLabel = metaData.getColumnLabel(i);
		columnValue = rs.getObject(i);
		if (columnValue == null)
			continue;// 如果为空，继续下一个
		fieldDescriptor = descriptor.findFieldByName(columnLabel);
		if (fieldDescriptor == null)
			continue;// 如果为空，继续下一个
		// 转换为相应的类型 ，会自动将 date 类型转换为long
		if (fieldDescriptor.getType().equals(FieldDescriptor.Type.ENUM)) {
			columnValue = fieldDescriptor.getEnumType().findValueByNumber(
					(int) columnValue);
		} else {
			columnValue = ConvertUtils.convert(columnValue, fieldDescriptor
					.getDefaultValue().getClass());
		}
		builder.setField(fieldDescriptor, columnValue);
	}
}
 

private void checkReturnedColumnsForBug44451(String stepDescription, String methodName, List<String> expectedFields, ResultSet resultSetToCheck)
        throws Exception {
    ResultSetMetaData rsMetaData = resultSetToCheck.getMetaData();
    int numberOfColumns = rsMetaData.getColumnCount();

    assertEquals(stepDescription + ", wrong column count in method '" + methodName + "'.", expectedFields.size(), numberOfColumns);
    for (int i = 0; i < numberOfColumns; i++) {
        int position = i + 1;
        assertEquals(stepDescription + ", wrong column at position '" + position + "' in method '" + methodName + "'.", expectedFields.get(i),
                rsMetaData.getColumnName(position));
    }
    rs.close();
}
 

public Map<String, Object> runSelectSingleRow(String query_key, Object... params)
{
    HashMap<String, Object> res = new HashMap<>();
    try (ResultSet rs = runSelect(query_key, params))
    {
        if (!rs.next())
        {
            throw new NoResultException("The query returned zero rows when one was expected.");
        }
        ResultSetMetaData meta = rs.getMetaData();
        for (int i = 1; i <= meta.getColumnCount(); i++)
        {
            // We take the type as returned, with an exception for small numerics (we do not want long or BigInt which cannot be cast)
            Object or;
            if (meta.getColumnType(i) == java.sql.Types.NUMERIC && meta.getPrecision(i) <= 10)
            {
                or = rs.getInt(i);
            }
            else
            {
                or = rs.getObject(i);
            }
            res.put(meta.getColumnName(i).toUpperCase(), or);
        }

        if (rs.next())
        {
            throw new NonUniqueResultException("The query returned more than one row when one was expected");
        }
    }
    catch (SQLException e)
    {
        throw new DatabaseException(e);
    }
    return res;
}
 

public static void dumpResultsetMetadata(ResultSet rst, PrintStream out) {
    try {
        ResultSetMetaData metaData = rst.getMetaData();
        for (int i = 1; i <= metaData.getColumnCount(); i++) {
            out.print(metaData.getColumnName(i) + "\t");
        }
        out.println();
    } catch (SQLException e) {
        throw new RuntimeSqlException(e);
    }
    out.println();
}
 

private Vector<byte[]> convertToVector(ResultSet rs) throws Exception {
  Vector<byte[]> v = new Vector<byte[]>();
  ResultSetMetaData rsmd = rs.getMetaData();
  while (rs.next()) {
    for (int i = 1; i <= rsmd.getColumnCount(); i++) {
      v.add(rs.getBytes(i));
    }
  }

  return v;
}
 

/**
 * Print out resultSet in two dimensional array format, for use by
 * JDBC.assertFullResultSet(rs,expectedRows) expectedRows argument.
 * Useful while converting tests to get output in correct format.
 * 
 * @param rs
 * @throws SQLException
 */
public static void showResultSet(ResultSet rs) throws SQLException {
    System.out.print("{");
    int row = 0;
    boolean next = rs.next();
    while (next) {
        row++;
        ResultSetMetaData rsmd = rs.getMetaData();
        int nocols = rsmd.getColumnCount();
        System.out.print("{");
        
        for (int i = 0; i < nocols; i++)
        {
        	String val = rs.getString(i+1);
        	if (val == null)
        		System.out.print("null");
        	else
        		System.out.print("\"" + rs.getString(i+1) + "\"");
            if (i == (nocols -1))
                System.out.print("}");
            else
                System.out.print(",");
                   
        }
        next = rs.next();
           
        if (next)
            System.out.println(",");
        else
            System.out.println("};\n");
    }
}
 

/**
 * The following method returns an array of strings containing the column names for a given ResultSetMetaData object.
 */
public static String[] getColumnNames( ResultSetMetaData resultSetMetaData ) throws SQLException {
  int columnCount = resultSetMetaData.getColumnCount();
  String[] columnNames = new String[columnCount];
  for ( int colIndex = 1; colIndex <= columnCount; colIndex++ ) {
    columnNames[colIndex - 1] = resultSetMetaData.getColumnName( colIndex );
  }
  return columnNames;
}
 

private boolean compareColumns(ResultSetMetaData meta1,
    ResultSetMetaData meta2) throws SQLException {
  final int numColumns = meta1.getColumnCount();
  if (meta2.getColumnCount() != numColumns) {
    return false;
  }
  for (int i = 1; i <= numColumns; i++) {
    if (meta1.getColumnType(i) != meta2.getColumnType(i)
        || !meta1.getColumnName(i).equals(meta2.getColumnName(i))) {
      return false;
    }
  }
  return true;
}
 

public void testExecuteQueryWithDataAwareProcedureCall()
throws SQLException {

  setup();
  CallableStatement cs = prepareCall("CALL RETRIEVE_DYNAMIC_RESULTS(?) "
      + "ON TABLE EMP.PARTITIONTESTTABLE 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][1];
  results[0][0]="1";
  results[1][0]="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;
    }
  } while (cs.getMoreResults());    
  
}
 

public List<ReportMetaDataColumn> parseMetaDataColumns(final String sqlText) {
    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;
    List<ReportMetaDataColumn> columns = null;

    try {
        this.logger.debug("Parse Report MetaDataColumns SQL:{},", sqlText);
        conn = this.getJdbcConnection();
        stmt = conn.createStatement();
        rs = stmt.executeQuery(this.preprocessSqlText(sqlText));
        final ResultSetMetaData rsMataData = rs.getMetaData();
        final int count = rsMataData.getColumnCount();
        columns = new ArrayList<>(count);
        for (int i = 1; i <= count; i++) {
            final ReportMetaDataColumn column = new ReportMetaDataColumn();
            column.setName(rsMataData.getColumnLabel(i));
            column.setDataType(rsMataData.getColumnTypeName(i));
            column.setWidth(rsMataData.getColumnDisplaySize(i));
            columns.add(column);
        }
    } catch (final SQLException ex) {
        throw new RuntimeException(ex);
    } finally {
        JdbcUtils.releaseJdbcResource(conn, stmt, rs);
    }
    return columns;
}
 

public void testDataAwareProcedureCallUsingGlobalIndex()
throws SQLException {

  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][1];
  results[0][0]="1";
  results[1][0]="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;
    }
  } while (cs.getMoreResults());    
  
}
 

private void execCommand(Connection conn, StringBuffer command,
                         LineNumberReader lineReader) throws SQLException {
    boolean hasResults;
    try (Statement statement = conn.createStatement(); ResultSet rs = statement.getResultSet()) {

        logger.debug(command.toString());

        hasResults = false;
        try {
            hasResults = statement.execute(command.toString());
        } catch (SQLException e) {
            final String errText = String.format("Error executing '%s' (line %d): %s",
                    command, lineReader.getLineNumber(), e.getMessage());
            logger.debug(errText);
            System.err.println(errText);
            if (stopOnError) {
                throw new SQLException(errText, e);
            }
        }

        if (autoCommit && !conn.getAutoCommit()) {
            conn.commit();
        }

        if (hasResults && rs != null) {
            ResultSetMetaData md = rs.getMetaData();
            int cols = md.getColumnCount();
            for (int i = 1; i <= cols; i++) {
                String name = md.getColumnLabel(i);
                logger.debug(name + "\t");
            }
            logger.debug("");
            while (rs.next()) {
                for (int i = 1; i <= cols; i++) {
                    String value = rs.getString(i);
                    logger.debug(value + "\t");
                }
                logger.debug("");
            }
        }
    }
}
 

/**************supporting methods *******************/
private void dumpRSOriginal(ResultSet rs, String expectedValue) throws SQLException
{
    if (rs == null) return;

    ResultSetMetaData rsmd = rs.getMetaData();
    int numCols = rsmd.getColumnCount();
    if (numCols <= 0) return;

    StringBuilder heading = new StringBuilder();
    for (int i=1; i<=numCols; i++)
    {
        if (i > 1) heading.append(",");
        heading.append("COL"+i);
        heading.append("(datatype : " + rsmd.getColumnTypeName(i));
        heading.append(", precision : " + rsmd.getPrecision(i));
        heading.append(", scale : " + rsmd.getScale(i) + ")");

    }

    StringBuilder row = new StringBuilder();
    while (rs.next())
    {
        for (int i=1; i<=numCols; i++)
        {
            if (i > 1) row.append(",");
            row.append(rs.getString(i));
            row.append(" ");
        }

    }

    String actualValue = heading.toString() + " " + row.toString();

    if ( usingDerbyNetClient() ) {
        if ( expectedValue.indexOf("()") != -1 ) {
            String actualValue2  = actualValue.replaceAll("CHAR", "CHAR ()");
            actualValue = actualValue2;
        }

    }
    assertEquals(expectedValue, actualValue);
    rs.close();

}
 

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();
}