从java.sql.ResultSet中检索列名

对于java.sql.ResultSet，是否有一种方法通过使用列的索引来将列名作为String ?我看了API文档，但什么都没找到。

407139 次浏览
小开
最佳答案

你可以从ResultSet元数据中得到这个信息。看到ResultSetMetaData

如。

 ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM TABLE2");
ResultSetMetaData rsmd = rs.getMetaData();
String name = rsmd.getColumnName(1);

你可以从那里得到列名。如果有的话

select x as y from table

那么rsmd.getColumnLabel()也会为你获取标签名。

小开

你可以使用ResultSetMetaData (http://java.sun.com/javase/6/docs/api/java/sql/ResultSetMetaData.html)对象，就像这样:

ResultSet rs = stmt.executeQuery("SELECT * FROM table");
ResultSetMetaData rsmd = rs.getMetaData();
String firstColumnName = rsmd.getColumnName(1);
小开

除了上面的答案，如果您正在使用一个动态查询，并且您想要列名，但不知道有多少列，您可以使用ResultSetMetaData对象首先获得列的数量，然后循环遍历它们。

修改Brian的代码:

ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM TABLE2");
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();


// The column count starts from 1
for (int i = 1; i <= columnCount; i++ ) {
String name = rsmd.getColumnName(i);
// Do stuff with name
}
小开

从数据库查询中读取数据的SQL语句返回结果集中的数据。SELECT语句是从数据库中选择行并在结果集中查看它们的标准方法。**java.sql.ResultSet**接口表示数据库查询的结果集。

  • 获得方法:用于查看当前行各列中的数据 被光标指向。李< / >

使用MetaData of a result set to fetch the exact column count

ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM TABLE2");
ResultSetMetaData rsmd = rs.getMetaData();
int numberOfColumns = rsmd.getColumnCount();
boolean b = rsmd.isSearchable(1);

http://docs.oracle.com/javase/7/docs/api/java/sql/ResultSetMetaData.html

并进一步将其绑定到数据模型表

public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
try {
//STEP 2: Register JDBC driver
Class.forName("com.mysql.jdbc.Driver");


//STEP 3: Open a connection
System.out.println("Connecting to a selected database...");
conn = DriverManager.getConnection(DB_URL, USER, PASS);
System.out.println("Connected database successfully...");


//STEP 4: Execute a query
System.out.println("Creating statement...");
stmt = conn.createStatement();


String sql = "SELECT id, first, last, age FROM Registration";
ResultSet rs = stmt.executeQuery(sql);
//STEP 5: Extract data from result set
while(rs.next()){
//Retrieve by column name
int id  = rs.getInt("id");
int age = rs.getInt("age");
String first = rs.getString("first");
String last = rs.getString("last");


//Display values
System.out.print("ID: " + id);
System.out.print(", Age: " + age);
System.out.print(", First: " + first);
System.out.println(", Last: " + last);
}
rs.close();
} catch(SQLException se) {
//Handle errors for JDBC
se.printStackTrace();
} catch(Exception e) {
//Handle errors for Class.forName
e.printStackTrace();
} finally {
//finally block used to close resources
try {
if(stmt!=null)
conn.close();
} catch(SQLException se) {
} // do nothing
try {
if(conn!=null)
conn.close();
} catch(SQLException se) {
se.printStackTrace();
} //end finally try
}//end try
System.out.println("Goodbye!");
}//end main
//end JDBCExample

非常好的教程:http://www.tutorialspoint.com/jdbc/

ResultSetMetaData meta = resultset.getMetaData();  // for a valid resultset object after executing query


Integer columncount = meta.getColumnCount();


int count = 1 ; // start counting from 1 always


String[] columnNames = null;


while(columncount <=count) {
columnNames [i] = meta.getColumnName(i);
}


System.out.println (columnNames.size() ); //see the list and bind it to TableModel object. the to your jtbale.setModel(your_table_model);
小开

这个问题很老了，以前的正确答案也很老了。但当我找到这个话题时，我想要的是类似这样的解。希望它能帮助到某些人。

// Loading required libraries
import java.util.*;
import java.sql.*;


public class MySQLExample {
public void run(String sql) {
// JDBC driver name and database URL
String JDBC_DRIVER = "com.mysql.jdbc.Driver";
String DB_URL = "jdbc:mysql://localhost/demo";


// Database credentials
String USER = "someuser"; // Fake of course.
String PASS = "somepass"; // This too!


Statement stmt = null;
ResultSet rs = null;
Connection conn = null;
Vector<String> columnNames = new Vector<String>();


try {
// Register JDBC driver
Class.forName(JDBC_DRIVER);


// Open a connection
conn = DriverManager.getConnection(DB_URL, USER, PASS);


// Execute SQL query
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
if (rs != null) {
ResultSetMetaData columns = rs.getMetaData();
int i = 0;
while (i < columns.getColumnCount()) {
i++;
System.out.print(columns.getColumnName(i) + "\t");
columnNames.add(columns.getColumnName(i));
}
System.out.print("\n");


while (rs.next()) {
for (i = 0; i < columnNames.size(); i++) {
System.out.print(rs.getString(columnNames.get(i))
+ "\t");


}
System.out.print("\n");
}


}
} catch (Exception e) {
System.out.println("Exception: " + e.toString());
}


finally {
try {
if (rs != null) {
rs.close();
}
if (stmt != null) {
stmt.close();
}
if (conn != null) {
conn.close();
}
} catch (Exception mysqlEx) {
System.out.println(mysqlEx.toString());
}


}
}
}
小开

@Cyntech是对的。

如果你的表是空的，你仍然需要得到表的列名，你可以得到你的列类型为Vector，如下所示:

ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM TABLE2");
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();


Vector<Vector<String>>tableVector = new Vector<Vector<String>>();
boolean isTableEmpty = true;
int col = 0;


while(rs.next())
{
isTableEmpty = false;  //set to false since rs.next has data: this means the table is not empty
if(col != columnCount)
{
for(int x = 1;x <= columnCount;x++){
Vector<String> tFields = new Vector<String>();
tFields.add(rsmd.getColumnName(x).toString());
tableVector.add(tFields);
}
col = columnCount;
}
}




//if table is empty then get column names only
if(isTableEmpty){
for(int x=1;x<=colCount;x++){
Vector<String> tFields = new Vector<String>();
tFields.add(rsmd.getColumnName(x).toString());
tableVector.add(tFields);
}
}


rs.close();
stmt.close();


return tableVector;
小开 
ResultSet rsTst = hiSession.connection().prepareStatement(queryStr).executeQuery();
ResultSetMetaData meta = rsTst.getMetaData();
int columnCount = meta.getColumnCount();
// The column count starts from 1


String nameValuePair = "";
while (rsTst.next()) {
for (int i = 1; i < columnCount + 1; i++ ) {
String name = meta.getColumnName(i);
// Do stuff with name


String value = rsTst.getString(i); //.getObject(1);
nameValuePair = nameValuePair + name + "=" +value + ",";
//nameValuePair = nameValuePair + ", ";
}
nameValuePair = nameValuePair+"||" + "\t";
}
小开 
import java.sql.*;


public class JdbcGetColumnNames {


public static void main(String args[]) {
Connection con = null;
Statement st = null;
ResultSet rs = null;


try {
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/komal", "root", "root");


st = con.createStatement();


String sql = "select * from person";
rs = st.executeQuery(sql);
ResultSetMetaData metaData = rs.getMetaData();


int rowCount = metaData.getColumnCount();


System.out.println("Table Name : " + metaData.getTableName(2));
System.out.println("Field  \tDataType");


for (int i = 0; i < rowCount; i++) {
System.out.print(metaData.getColumnName(i + 1) + "  \t");
System.out.println(metaData.getColumnTypeName(i + 1));
}
} catch (Exception e) {
System.out.println(e);
}
}
}

表名:person 字段数据类型 id VARCHAR cname VARCHAR 捐赠日期< / p >

小开

当你需要列名，但不想获取条目时:

PreparedStatement stmt = connection.prepareStatement("SHOW COLUMNS FROM `yourTable`");


ResultSet set = stmt.executeQuery();


//store all of the columns names
List<String> names = new ArrayList<>();
while (set.next()) { names.add(set.getString("Field")); }

注意:只适用于MySQL

小开

SQLite 3

使用getMetaData ();

DatabaseMetaData md = conn.getMetaData();
ResultSet rset = md.getColumns(null, null, "your_table_name", null);


System.out.println("your_table_name");
while (rset.next())
{
System.out.println("\t" + rset.getString(4));
}

编辑:这也适用于PostgreSQL

小开 
while (rs.next()) {
for (int j = 1; j < columncount; j++) {
System.out.println( rsd.getColumnName(j) + "::" + rs.getString(j));
}
}
小开

如果你想使用spring jdbctemplate并且不想处理连接人员，你可以使用以下方法:

jdbcTemplate.query("select * from books", new RowCallbackHandler() {
public void processRow(ResultSet resultSet) throws SQLException {
ResultSetMetaData rsmd = resultSet.getMetaData();
for (int i = 1; i <= rsmd.getColumnCount(); i++ ) {
String name = rsmd.getColumnName(i);
// Do stuff with name
}
}
});
小开

U可以从resultSet.getMetaData()获得列名和值; 这段代码为我工作:

Connection conn = null;
PreparedStatement preparedStatement = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
conn = MySQLJDBCUtil.getConnection();
preparedStatement = conn.prepareStatement(sql);
if (params != null) {
for (int i = 0; i < params.size(); i++) {
preparedStatement.setObject(i + 1, params.get(i).getSqlValue());
}
ResultSet resultSet = preparedStatement.executeQuery();
ResultSetMetaData md = resultSet.getMetaData();
while (resultSet.next()) {
int counter = md.getColumnCount();
String colName[] = new String[counter];
Map<String, Object> field = new HashMap<>();
for (int loop = 1; loop <= counter; loop++) {
int index = loop - 1;
colName[index] = md.getColumnLabel(loop);
field.put(colName[index], resultSet.getObject(colName[index]));
}
rows.add(field);
}
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (preparedStatement != null) {
try {
preparedStatement.close();
}catch (Exception e1) {
e1.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return rows;
小开

我知道，这个问题已经回答了，但可能像我这样的人需要通过标签而不是索引访问DatabaseMetaData中的列名:

ResultSet resultSet = null;
DatabaseMetaData metaData = null;


try {
metaData  = connection.getMetaData();
resultSet = metaData.getColumns(null, null, tableName, null);


while (resultSet.next()){
String name = resultSet.getString("COLUMN_NAME");
}
}

提交答案