Java resultset get column names

Java Guides

In this example, we will discuss how to get column names from a database table using java.sql.ResultSet.

We can get table columns from the ResultSet getMetaData() method returns ResultSetMetaData interface object. ResultSetMetaData interface object that can be used to get information about the types and properties of the columns in a ResultSet object.

Consider we have a users table in the database. The following DDL script shows there are five columns in a users table.

create table Users1( id int(3) primary key, name varchar(20), email varchar(20), country varchar(20), password varchar(20) );

Now, we will write a JDBC code to get all these columns from users table. The following fragment of code shows the same.

Retrieve column names from java.sql.ResultSet

package com.javaguides.jdbc.databasemetadata; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.sql.Statement; /** * Get column names from database table * @author Ramesh Fadatare * */ public class ResuleSetMetaDataExample < private static final String QUERY = "select id,name,email,country,password from Users"; public static void main(String[] args) < // using try-with-resources to avoid closing resources (boiler plate code) // Step 1: Establishing a Connection try (Connection connection = DriverManager .getConnection("jdbc:mysql://localhost:3306/mysql_database?useSSL=false", "root", "root"); // Step 2:Create a statement using connection object Statement stmt = connection.createStatement(); // Step 3: Execute the query or update query ResultSet rs = stmt.executeQuery(QUERY)) < ResultSetMetaData resultSetMetaData = rs.getMetaData(); System.out.println(resultSetMetaData.getColumnName(1)); System.out.println(resultSetMetaData.getColumnName(2)); System.out.println(resultSetMetaData.getColumnName(3)); System.out.println(resultSetMetaData.getColumnName(4)); System.out.println(resultSetMetaData.getColumnName(5)); > catch (SQLException e) < printSQLException(e); >// Step 4: try-with-resource statement will auto close the connection. > public static void printSQLException(SQLException ex) < for (Throwable e: ex) < if (e instanceof SQLException) < e.printStackTrace(System.err); System.err.println("SQLState: " + ((SQLException) e).getSQLState()); System.err.println("Error Code: " + ((SQLException) e).getErrorCode()); System.err.println("Message: " + e.getMessage()); Throwable t = ex.getCause(); while (t != null) < System.out.println("Cause: " + t); t = t.getCause(); > > > > >

id name email country password

Источник

How to get all the column names from a ResultSet using JDBC

You can get the name of a particular column using the getColumnName() method of the ResultSetMetadata interface.

This method accepts an integer value representing the index of a column and returns a String value representing the name of the specified column.

Let us create a table with name MyPlayers in MySQL database using CREATE statement as shown below −

CREATE TABLE MyPlayers( ID INT, First_Name VARCHAR(255), Last_Name VARCHAR(255), Date_Of_Birth date, Place_Of_Birth VARCHAR(255), Country VARCHAR(255), PRIMARY KEY (ID) );

Now, we will insert 7 records in MyPlayers table using INSERT statements −

insert into MyPlayers values(1, 'Shikhar', 'Dhawan', DATE('1981-12-05'), 'Delhi', 'India'); insert into MyPlayers values(2, 'Jonathan', 'Trott', DATE('1981-04-22'), 'CapeTown', 'SouthAfrica'); insert into MyPlayers values(3, 'Kumara', 'Sangakkara', DATE('1977-10-27'), 'Matale', 'Srilanka'); insert into MyPlayers values(4, 'Virat', 'Kohli', DATE('1988-11-05'), 'Delhi', 'India'); insert into MyPlayers values(5, 'Rohit', 'Sharma', DATE('1987-04-30'), 'Nagpur', 'India'); insert into MyPlayers values(6, 'Ravindra', 'Jadeja', DATE('1988-12-06'), 'Nagpur', 'India'); insert into MyPlayers values(7, 'James', 'Anderson', DATE('1982-06-30'), 'Burnley', 'England');

Following JDBC program establishes connection with the database, retrieves the contents of the table named MyPlayers in to a ResultSet object, gets the column names of the table using the getColumnName() method and displays them.

Example

import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.Statement; public class RS_AllColumnNames < public static void main(String args[]) throws Exception < //Registering the Driver DriverManager.registerDriver(new com.mysql.jdbc.Driver()); //Getting the connection String mysqlUrl = "jdbc:mysql://localhost/mydatabase"; Connection con = DriverManager.getConnection(mysqlUrl, "root", "password"); System.out.println("Connection established. "); //Creating a Statement object Statement stmt = con.createStatement(); //Retrieving the data ResultSet rs = stmt.executeQuery("select * from MyPlayers"); //Retrieving the ResultSetMetadata object ResultSetMetaData rsMetaData = rs.getMetaData(); System.out.println("List of column names in the current table: "); //Retrieving the list of column names int count = rsMetaData.getColumnCount(); for(int i = 1; i> >

Output

Connection established. List of column names in the current table: ID First_Name Last_Name Date_Of_Birth Place_Of_Birth Country

Источник

Interface ResultSet

A table of data representing a database result set, which is usually generated by executing a statement that queries the database.

A ResultSet object maintains a cursor pointing to its current row of data. Initially the cursor is positioned before the first row. The next method moves the cursor to the next row, and because it returns false when there are no more rows in the ResultSet object, it can be used in a while loop to iterate through the result set.

A default ResultSet object is not updatable and has a cursor that moves forward only. Thus, you can iterate through it only once and only from the first row to the last row. It is possible to produce ResultSet objects that are scrollable and/or updatable. The following code fragment, in which con is a valid Connection object, illustrates how to make a result set that is scrollable and insensitive to updates by others, and that is updatable. See ResultSet fields for other options.

Statement stmt = con.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); ResultSet rs = stmt.executeQuery("SELECT a, b FROM TABLE2"); // rs will be scrollable, will not show changes made by others, // and will be updatable

The ResultSet interface provides getter methods ( getBoolean , getLong , and so on) for retrieving column values from the current row. Values can be retrieved using either the index number of the column or the name of the column. In general, using the column index will be more efficient. Columns are numbered from 1. For maximum portability, result set columns within each row should be read in left-to-right order, and each column should be read only once.

For the getter methods, a JDBC driver attempts to convert the underlying data to the Java type specified in the getter method and returns a suitable Java value. The JDBC specification has a table showing the allowable mappings from SQL types to Java types that can be used by the ResultSet getter methods.

Column names used as input to getter methods are case insensitive. When a getter method is called with a column name and several columns have the same name, the value of the first matching column will be returned. The column name option is designed to be used when column names are used in the SQL query that generated the result set. For columns that are NOT explicitly named in the query, it is best to use column numbers. If column names are used, the programmer should take care to guarantee that they uniquely refer to the intended columns, which can be assured with the SQL AS clause.

A set of updater methods were added to this interface in the JDBC 2.0 API (Java 2 SDK, Standard Edition, version 1.2). The comments regarding parameters to the getter methods also apply to parameters to the updater methods.

to update a column value in the current row. In a scrollable ResultSet object, the cursor can be moved backwards and forwards, to an absolute position, or to a position relative to the current row. The following code fragment updates the NAME column in the fifth row of the ResultSet object rs and then uses the method updateRow to update the data source table from which rs was derived.

rs.absolute(5); // moves the cursor to the fifth row of rs rs.updateString("NAME", "AINSWORTH"); // updates the // NAME column of row 5 to be AINSWORTH rs.updateRow(); // updates the row in the data source

rs.moveToInsertRow(); // moves cursor to the insert row rs.updateString(1, "AINSWORTH"); // updates the // first column of the insert row to be AINSWORTH rs.updateInt(2,35); // updates the second column to be 35 rs.updateBoolean(3, true); // updates the third column to true rs.insertRow(); rs.moveToCurrentRow();

A ResultSet object is automatically closed when the Statement object that generated it is closed, re-executed, or used to retrieve the next result from a sequence of multiple results.

The number, types and properties of a ResultSet object’s columns are provided by the ResultSetMetaData object returned by the ResultSet.getMetaData method.

Источник

Column Names

Given a ResultSet object, it is possible to get the column name, column type, table name and position.

import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.Statement; /*from ja v a 2 s . c om*/ public class Main < public static void main(String[] args) throws Exception < Connection conn = getConnection(); Statement st = conn.createStatement(); st.executeUpdate("create table survey (id int,name varchar(30));"); st.executeUpdate("insert into survey (id,name ) values (1,'nameValue')"); st.executeUpdate("insert into survey (id,name ) values (2,null)"); st.executeUpdate("insert into survey (id,name ) values (3,'Tom')"); ResultSet rs = st.executeQuery("SELECT * FROM survey"); ResultSetMetaData rsMetaData = rs.getMetaData(); int numberOfColumns = rsMetaData.getColumnCount(); // get the column names; column indexes start from 1 for (int i = 1; i < numberOfColumns + 1; i++) < String columnName = rsMetaData.getColumnName(i); String tableName = rsMetaData.getTableName(i); System.out.println(columnName); System.out.println(tableName); >rs.close(); st.close(); conn.close(); > private static Connection getConnection() throws Exception < Class.forName("org.hsqldb.jdbcDriver"); String url = "jdbc:hsqldb:mem:data/tutorial"; return DriverManager.getConnection(url, "sa", ""); > > 

The code above generates the following result.

Next chapter.

What you will learn in the next chapter:

Источник