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

public static String[] getColumnNames(ResultSet rs, boolean closeResultSet)
        throws SQLException {
    String[] columns = new String[0];

    if (rs != null) {
        try {
            ResultSetMetaData metaData = rs.getMetaData();

            columns = new String[metaData.getColumnCount()];
            for (int i = 0; i < columns.length; i++) {
                columns[i] = metaData.getColumnName(i + 1);
            }
        } catch (SQLException e) {
            throw e;
        } finally {
            if (closeResultSet) {
                rs.close();
            }
        }
    }

    return columns;
}
 

private static String print(ResultSet rs) throws SQLException {
	ResultSetMetaData rsmd = rs.getMetaData();
	int columnsNumber = rsmd.getColumnCount();
	List<String> x = new LinkedList<>();
	while (rs.next()) {
		String ret = "";
		for (int i = 1; i <= columnsNumber; i++) { // wow, this starts at 1, apparently
			if (i > 1) {
				ret += ",  ";
			}
			String columnValue = rs.getString(i);
			ret += rsmd.getColumnName(i) + " " + columnValue;
		}
		x.add(ret);
	}
	return Util.sep(x, "\n");
}
 

public String resultSetToString(ResultSet resultSet) throws SQLException {
  ResultSetMetaData metaData = resultSet.getMetaData();
  int numColumns = metaData.getColumnCount();
  StringBuffer sb = new StringBuffer("");
  int rowNum = 0;
  while (resultSet.next() == true) {
    sb.append("Row " + rowNum++ + " : ");
    for (int i = 0; i < numColumns; i++) {
      String columnName = metaData.getColumnName(i);
      int type = metaData.getColumnType(i);
      sb.append(columnName);
      sb.append("=");
      sb.append(resultSet.getObject(i).toString());
      if (i < numColumns - 1) {
        sb.append(",");
      }
    }
  }
  return sb.toString();
}
 

private String getAutoIncrementColumnName(final Connection con, final String tableNameWithSchema) throws SQLException {
    String autoIncrementColumnName = null;

    Statement stmt = null;
    ResultSet rs = null;

    try {
        stmt = con.createStatement();

        rs = stmt.executeQuery("SELECT * FROM " + tableNameWithSchema);
        final ResultSetMetaData md = rs.getMetaData();

        for (int i = 0; i < md.getColumnCount(); i++) {
            if (md.isAutoIncrement(i + 1)) {
                autoIncrementColumnName = md.getColumnName(i + 1);
                break;
            }
        }

    } finally {
        this.close(rs);
        this.close(stmt);
    }

    return autoIncrementColumnName;
}
 

/**
 * Returns the column name for a MySQL field checking if the driver major version is "greater than" or "lower or equal" to 3.
 *
 * @param dbMetaData
 * @param rsMetaData
 * @param index
 * @return The column label if version is greater than 3 or the column name if version is lower or equal to 3.
 * @throws HopDatabaseException
 */
@Override
public String getLegacyColumnName( DatabaseMetaData dbMetaData, ResultSetMetaData rsMetaData, int index ) throws HopDatabaseException {
  if ( dbMetaData == null ) {
    throw new HopDatabaseException( BaseMessages.getString( PKG, "MySQLDatabaseMeta.Exception.LegacyColumnNameNoDBMetaDataException" ) );
  }

  if ( rsMetaData == null ) {
    throw new HopDatabaseException( BaseMessages.getString( PKG, "MySQLDatabaseMeta.Exception.LegacyColumnNameNoRSMetaDataException" ) );
  }

  try {
    return dbMetaData.getDriverMajorVersion() > 3 ? rsMetaData.getColumnLabel( index ) : rsMetaData.getColumnName( index );
  } catch ( Exception e ) {
    throw new HopDatabaseException( String.format( "%s: %s", BaseMessages.getString( PKG, "MySQLDatabaseMeta.Exception.LegacyColumnNameException" ), e.getMessage() ), e );
  }
}
 

public static void main(String[] args) throws Exception {
  // String sql = "select * from test-table.fam0 where fam0.col0 = 'abroad'";
  String sql = "select * from test-table.fam0";
  List<Connection> connections = BlurClientManager.getConnections("10.192.56.10:40010");
  // BlurResultSetRows resultSet = new BlurResultSetRows(sql,connections);

  Iface client = BlurClient.getClient(connections);
  Parser parser = new Parser();
  parser.parse(sql);

  BlurResultSetRecords resultSet = new BlurResultSetRecords(client, parser);
  int c = 0;
  while (resultSet.next()) {
    System.out.println(c + " ------------------------");
    ResultSetMetaData metaData = resultSet.getMetaData();
    int columnCount = metaData.getColumnCount();
    for (int i = 1; i <= columnCount; i++) {
      String value = resultSet.getString(i);
      String name = metaData.getColumnName(i);
      System.out.println("\t" + name + ":[" + value + "]");
    }
    c++;
  }
}
 

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

@RequestMapping("/rollback")
public List<Map<String, String>> rollback() {
    List<Map<String, String>> results = new ArrayList<>();
    try (Connection connection = dataSource.getConnection();
         Statement statement = connection.createStatement();
         ResultSet resultSet = statement.executeQuery("SELECT * FROM INFORMATION_SCHEMA.COLUMNS")) {
        ResultSetMetaData metaData = resultSet.getMetaData();
        while (resultSet.next()) {
            Map<String, String> result = new HashMap<>();
            for (int i = 0; i < metaData.getColumnCount(); i++) {
                String columnName = metaData.getColumnName(i + 1);
                result.put(columnName, resultSet.getString(columnName));
            }
            results.add(result);
        }
        connection.rollback();
    }
    catch (Exception e) {
        throw new IllegalStateException(e);
    }
    return results;
}
 

protected ArrayList<Object> getDataFrmResult(Object result) throws RuntimeException
{
  try {
    ResultSet resultSet = (ResultSet)result;
    if (resultSet.next()) {
      ResultSetMetaData rsdata = resultSet.getMetaData();
      // If the includefields is empty, populate it from ResultSetMetaData
      if (CollectionUtils.isEmpty(includeFieldInfo)) {
        if (includeFieldInfo == null) {
          includeFieldInfo = new ArrayList<>();
        }
        for (int i = 1; i <= rsdata.getColumnCount(); i++) {
          String columnName = rsdata.getColumnName(i);
          // TODO: Take care of type conversion.
          includeFieldInfo.add(new FieldInfo(columnName, columnName, FieldInfo.SupportType.OBJECT));
        }
      }

      ArrayList<Object> res = new ArrayList<Object>();
      for (FieldInfo f : includeFieldInfo) {
        res.add(getConvertedData(resultSet.getObject(f.getColumnName()), f));
      }
      return res;
    } else {
      return null;
    }
  } catch (SQLException e) {
    throw new RuntimeException(e);
  }
}
 

public String getColumnName(int columnIndex) throws IOException {
    if(closed) {
        throw new IOException("Cursor is closed");
    }
    try {
        ResultSetMetaData meta = resultSet.getMetaData();
        return meta.getColumnName(columnIndex + 1);
    } catch (SQLException ex) {
        ex.printStackTrace();
        throw new IOException(ex.getMessage());
    }
}
 

@Test
public void testGetSchemaStar() {
    ResultSetMetaData rsmd=statement.getSchema("select * from block ");
    String colName="";
    try {
       colName=rsmd.getColumnName(1);
    } catch (SQLException e) {
       throw new BlkchnException("Error while running test case - testGetSchema");
    }
    
    assertEquals("blocknumber",colName );
}
 

/**
 * Retrieves basic meta data from the result set.
 *
 * @throws SQLException
 */
private void populateMetaData() throws SQLException
{
   m_meta.clear();

   ResultSetMetaData meta = m_rs.getMetaData();
   int columnCount = meta.getColumnCount() + 1;
   for (int loop = 1; loop < columnCount; loop++)
   {
      String name = meta.getColumnName(loop);
      Integer type = Integer.valueOf(meta.getColumnType(loop));
      m_meta.put(name, type);
   }
}
 

public static Map<String, String> getJtMap(SqlFly sqlFly, String tableName) {
	Map<String, String> map = new HashMap<>();
	try {
		String sql = "select * from " + tableName + " where 1=0";
		ResultSetMetaData rsmd = sqlFly.getResultSet(sql).getMetaData();
		for (int i = 0; i < rsmd.getColumnCount(); i++) {
			String propName = rsmd.getColumnName(i + 1); // 列名
			String javaType = JDBC2JT(rsmd.getColumnClassName(i + 1)); // 类型
			map.put(propName, javaType);
		}
	} catch (SQLException e) {
		e.printStackTrace();
	}
	return map;
}
 

private DatabaseRow getNextRow() throws SQLException {
  _numRowsInResult++;
  try {
    ResultSetMetaData rsmd = _queryResultSet.getMetaData();
    int colCount = rsmd.getColumnCount();
    DatabaseRow payloadRecord = new DatabaseRow();
    for (int i = 1; i <= colCount; i++) {
      String columnName = rsmd.getColumnName(i);
      Object columnValue = _queryResultSet.getObject(i);
      payloadRecord.addField(columnName, columnValue, rsmd.getColumnType(i));
      // If column is one of the key values, save the result from query to perform chunking query in the future
      if (_chunkingKeys.containsKey(columnName)) {
        if (columnValue == null) {
          ErrorLogger.logAndThrowDatastreamRuntimeException(LOG, columnName + " field is not expected to be null");
        }
        _chunkingKeys.put(columnName, columnValue);
      }
    }
    return payloadRecord;
  } catch (SQLException e) {
    _metrics.updateErrorRate();

    if (_skipBadMessagesEnabled) {
      LOG.warn("Skipping row due to SQL exception", e);
      _metrics.updateSkipBadMessagesRate();
    } else {
      ErrorLogger.logAndThrowDatastreamRuntimeException(LOG, "Failed to interpret row and skipBadMessage not enabled", e);
    }
    return null;
  }
}
 

/**
 * Gets a first row of a result set of a query and returns it as a HashMap.
 *
 * @param sQuery the query to be executed - expected to return only one row
 * @return HashMap object with the column names and values of the row
 * @exception SQLException - if a database error occurs
 */
public Map<String, String> getFirstRow( String sQuery ) throws DbException {

    ResultSet rs = null;
    Connection connection = ConnectionPool.getConnection(dbConnection);

    HashMap<String, String> hash = new HashMap<String, String>();

    PreparedStatement stmnt = null;
    try {
        stmnt = connection.prepareStatement(sQuery);
        rs = stmnt.executeQuery();
        ResultSetMetaData rsmd = rs.getMetaData();

        // get the first row
        if (rs.next()) {
            // iterate the columns and fill the hash map
            for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                String columnName = rsmd.getColumnName(i);
                String columnValue = rs.getString(i);
                hash.put(columnName, columnValue);
            }
        }
    } catch (SQLException ex) {
        log.error(ExceptionUtils.getExceptionMsg(ex));
    } finally {
        DbUtils.closeResultSet(rs);
        DbUtils.close(connection, stmnt);
    }

    return hash;
}
 

public <E> List<E> selectList(Mapper mapper, Connection conn) {
    PreparedStatement pstm = null;
    ResultSet rs = null;
    try {
        //1.取出mapper中的数据
        String queryString = mapper.getQueryString();//select * from user
        String resultType = mapper.getResultType();//com.itheima.domain.User
        Class domainClass = Class.forName(resultType);
        //2.获取PreparedStatement对象
        pstm = conn.prepareStatement(queryString);
        //3.执行SQL语句，获取结果集
        rs = pstm.executeQuery();
        //4.封装结果集
        List<E> list = new ArrayList<E>();//定义返回值
        while (rs.next()) {
            //实例化要封装的实体类对象
            E obj = (E) domainClass.newInstance();

            //取出结果集的元信息：ResultSetMetaData
            ResultSetMetaData rsmd = rs.getMetaData();
            //取出总列数
            int columnCount = rsmd.getColumnCount();
            //遍历总列数
            for (int i = 1; i <= columnCount; i++) {
                //获取每列的名称，列名的序号是从1开始的
                String columnName = rsmd.getColumnName(i);
                //根据得到列名，获取每列的值
                Object columnValue = rs.getObject(columnName);
                //给obj赋值：使用Java内省机制（借助PropertyDescriptor实现属性的封装）
                PropertyDescriptor pd = new PropertyDescriptor(columnName, domainClass);//要求：实体类的属性和数据库表的列名保持一种
                //获取它的写入方法
                Method writeMethod = pd.getWriteMethod();
                //把获取的列的值，给对象赋值
                writeMethod.invoke(obj, columnValue);
            }
            //把赋好值的对象加入到集合中
            list.add(obj);
        }
        return list;
    } catch (Exception e) {
        throw new RuntimeException(e);
    } finally {
        release(pstm, rs);
    }
}
 

public MySQLColumnDefinition41Packet(final int sequenceId, final ResultSetMetaData resultSetMetaData, final int columnIndex) throws SQLException {
    this(sequenceId, resultSetMetaData.getSchemaName(columnIndex), resultSetMetaData.getTableName(columnIndex), resultSetMetaData.getTableName(columnIndex), 
            resultSetMetaData.getColumnLabel(columnIndex), resultSetMetaData.getColumnName(columnIndex), resultSetMetaData.getColumnDisplaySize(columnIndex), 
            MySQLColumnType.valueOfJDBCType(resultSetMetaData.getColumnType(columnIndex)), resultSetMetaData.getScale(columnIndex));
}
 

private void reportMemoryAnalytics() throws SQLException {
  if (this.queryAPI != QueryPrms.GFXD) {
    noop();
  } else {
    Log.getLogWriter().info("Printing memory analytics...");
    Connection tmpconn = QueryUtil.gfxdEmbeddedSetup(this);
    tmpconn.setTransactionIsolation(QueryPerfPrms.TRANSACTION_NONE);
    String stmt = "select * from sys.memoryanalytics";
    List<String> sizerHints = QueryPerfPrms.getSizerHints();
    if (sizerHints != null) {
      stmt += " -- GEMFIREXD-PROPERTIES";
      String hints = "";
      for (String sizerHint : sizerHints) {
        if (hints.length() > 0) {
          hints += ",";
        }
        hints += " sizerHints=" + sizerHint;
      }
      stmt += hints;
    }
    Log.getLogWriter().info("Executing " + stmt);
    PreparedStatement memstmt = tmpconn.prepareStatement(stmt);
    ResultSet rs = memstmt.executeQuery();
    Log.getLogWriter().info("Executed " + stmt + ", reading results");
    long total = 0L;
    long ndxTotal = 0L;
    long ndxOverheadTotal = 0L;
    long tableTotal = 0L;
    long tableRowTotal = 0L;
    long ndxRowTotal = 0L;
    long footprintTotal = 0L;
    while (rs.next()) {
      ResultSetMetaData rsmd = rs.getMetaData();
      StringBuilder s = new StringBuilder();
      String sqlentity = null;
      String memory = null;
      for (int i = 1; i <= rsmd.getColumnCount(); i++) {
        String colname = rsmd.getColumnName(i);
        Object obj = rs.getObject(colname);
        s.append(colname).append("=").append(obj).append(" ");
        if (colname.equals("SQLENTITY")) sqlentity = obj.toString();
        if (colname.equals("MEMORY")) memory = obj.toString();
      }
      Log.getLogWriter().info(s.toString());
      if (sqlentity != null && memory != null) {
        int index = memory.indexOf(" ");
        String mem = (index == -1) ? memory : memory.substring(0, index);
        long[] nums = getLongs(mem.split(","));
        if (nums.length == 3) {
          if (sqlentity.contains("(Entry Size, Value Size, Row Count)")) {
            tableTotal += nums[0] + nums[1];
            tableRowTotal += nums[2];
          } else if (sqlentity.contains("(Index Entry Size, Value Size, Row Count)")) {
            ndxTotal += nums[0] + nums[1];
            ndxRowTotal += nums[2];
          } else if (sqlentity.contains("(Index Entry Overhead, SkipList Size, Max Level)")) {
            ndxOverheadTotal += nums[0] + nums[1] + nums[2];
          } else if (sqlentity.contains("(gemfirexd,gemfire,others)")) {
            footprintTotal += nums[0] + nums[1] + nums[2];
          } else {
            Log.getLogWriter().warning("Skipping memory: " + mem);
          }
        } else {
          Log.getLogWriter().warning("Skipping memory: " + mem);
        }
      }
      total = tableTotal + ndxTotal + ndxOverheadTotal;
    }
    Log.getLogWriter().info("MEMORY: table=" + + tableTotal + " ndx=" + ndxTotal + " ndxOverhead=" + ndxOverheadTotal + " totalTableNdxOverheadMemory=" + total + " footprint=" + footprintTotal);
    Log.getLogWriter().info("ENTRIES: table=" + + tableRowTotal + " ndx=" + ndxRowTotal);
    rs.close();
    rs = null;
    tmpconn.close();
    Log.getLogWriter().info("Done printing memory analytics");
  }
}
 

@Override
public void printResult(PrintWriter sout, InputStream sin, TableDesc tableDesc,
                        float responseTime, ResultSet res) throws Exception {
  long resultRows = tableDesc.getStats() == null ? -1 : tableDesc.getStats().getNumRows();
  if (resultRows == -1) {
    resultRows = Integer.MAX_VALUE;
  }

  if (res == null) {
    sout.println(getQuerySuccessMessage(tableDesc, responseTime, 0, "inserted", true));
    return;
  }
  ResultSetMetaData rsmd = res.getMetaData();
  int numOfColumns = rsmd.getColumnCount();
  for (int i = 1; i <= numOfColumns; i++) {
    if (i > 1) sout.print(",  ");
    String columnName = rsmd.getColumnName(i);
    sout.print(columnName);
  }
  sout.println("\n-------------------------------");

  int numOfPrintedRows = 0;
  int totalPrintedRows = 0;
  boolean endOfTuple = true;
  while (res.next()) {
    for (int i = 1; i <= numOfColumns; i++) {
      if (i > 1) sout.print(",  ");
      String columnValue = res.getString(i);
      if(res.wasNull()){
        sout.print(nullChar);
      } else {
        sout.print(columnValue);
      }
    }
    sout.println();
    sout.flush();
    numOfPrintedRows++;
    totalPrintedRows++;
    if (printPause && printPauseRecords > 0 && totalPrintedRows < resultRows && numOfPrintedRows >= printPauseRecords) {
      if (resultRows < Integer.MAX_VALUE) {
        sout.print("(" + totalPrintedRows + "/" + resultRows + " rows, continue... 'q' is quit)");
      } else {
        sout.print("(" + totalPrintedRows + " rows, continue... 'q' is quit)");
      }
      sout.flush();
      if (sin != null) {
        if (sin.read() == QUIT_COMMAND) {
          endOfTuple = false;
          sout.println();
          break;
        }
      }
      numOfPrintedRows = 0;
      sout.println();
    }
  }
  sout.println(getQuerySuccessMessage(tableDesc, responseTime, totalPrintedRows, "selected", endOfTuple));
  sout.flush();
}
 

public boolean dumpTable(BufferedWriter w, String name) throws Exception{
	Stm stm = getStatement();
	ResultSet res = stm.query("SELECT * from " + name);
	ResultSetMetaData rm = res.getMetaData();
	int cols = rm.getColumnCount();
	String[] ColNames = new String[cols];
	int[] colTypes = new int[cols];
	w.write("CREATE TABLE " + name + "(");
	for (int i = 0; i < cols; i++) {
		ColNames[i] = rm.getColumnName(i + 1);
		colTypes[i] = rm.getColumnType(i + 1);
		w.write(ColNames[i] + " " + colTypes[i] + ",\n");
	}
	w.write(");");
	
	while ((res != null) && (res.next() == true)) {
		w.write("INSERT INTO " + name + " (");
		for (int i = 0; i < cols; i++) {
			w.write(ColNames[i]);
			if (i < cols - 1) {
				w.write(",");
			}
		}
		w.write(") VALUES (");
		for (int i = 0; i < cols; i++) {
			Object o = res.getObject(ColNames[i]);
			switch (JdbcLink.generalType(colTypes[i])) {
			case JdbcLink.INTEGRAL:
				if (o == null) {
					w.write("0");
				} else {
					w.write(Integer.toString(((Integer) o).intValue()));
				}
				break;
			case JdbcLink.TEXT:
				if (o == null) {
					w.write(JdbcLink.wrap("null"));
				} else {
					w.write(JdbcLink.wrap((String) o));
				}
				break;
			
			default:
				String t = o.getClass().getName();
				log.log("Unknown type " + t, Log.ERRORS);
				throw new Exception("Cant write " + t);
				
			}
			if (i < cols - 1) {
				w.write(",");
			}
		}
		w.write(");");
		w.newLine();
	}
	res.close();
	releaseStatement(stm);
	return true;
}