ResultSetMetaData in Java
Metadata means "data about data". ResultSetMetaData describes the columns of a ResultSet: how many there are, what they are called, and what types they hold. You get it by calling rs.getMetaData(). It lets you write general-purpose code that works with any query without knowing its columns in advance, such as a tool that prints query results, exports them to CSV, or converts rows to JSON. In this lesson, you will learn its methods and use them to print any query as a table.
Methods
| Method | Description |
|---|---|
getColumnCount() | It is used to get the number of columns in the result. |
getColumnName(i) | It returns the column's real name in the table. |
getColumnLabel(i) | It returns the name used in the result: the AS alias if there is one. |
getColumnTypeName(i) | It returns the database's type name, such as INTEGER. |
getColumnType(i) | It returns the type as a constant from java.sql.Types. |
getColumnClassName(i) | It returns the Java class that getObject gives for this column. |
getPrecision(i), getScale(i) | They return the total digits and the digits after the decimal point. |
isNullable(i) | It tells whether the column can hold NULL. |
getTableName(i) | It returns the table the column comes from. |
isAutoIncrement(i) | It tells whether the column is generated automatically. |
Column numbers start at 1.
Example: Describing and Printing a Result
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
public class RsMeta {
// Prints any query as a table, without knowing its columns in advance.
static void print(Connection con, String sql) throws Exception {
try (Statement st = con.createStatement();
ResultSet rs = st.executeQuery(sql)) {
ResultSetMetaData md = rs.getMetaData();
int columns = md.getColumnCount();
StringBuilder header = new StringBuilder();
for (int i = 1; i <= columns; i++) {
header.append(String.format("%-12s", md.getColumnLabel(i)));
}
System.out.println(header.toString().stripTrailing());
while (rs.next()) {
StringBuilder row = new StringBuilder();
for (int i = 1; i <= columns; i++) {
row.append(String.format("%-12s", rs.getObject(i)));
}
System.out.println(row.toString().stripTrailing());
}
}
}
public static void main(String[] args) throws Exception {
try (Connection con =
DriverManager.getConnection("jdbc:h2:mem:school");
Statement st = con.createStatement()) {
st.execute("CREATE TABLE student (id INT PRIMARY KEY, "
+ "name VARCHAR(50) NOT NULL, marks DECIMAL(5,2))");
st.executeUpdate("INSERT INTO student VALUES "
+ "(1, 'Navika', 91.5), (2, 'Srihitha', 84.25)");
try (ResultSet rs = st.executeQuery(
"SELECT id, name, marks AS score FROM student")) {
ResultSetMetaData md = rs.getMetaData();
for (int i = 1; i <= md.getColumnCount(); i++) {
System.out.println(i + ". " + md.getColumnName(i)
+ " (label " + md.getColumnLabel(i) + ") "
+ md.getColumnTypeName(i)
+ ", Java: " + md.getColumnClassName(i)
+ ", nullable: " + (md.isNullable(i)
== ResultSetMetaData.columnNullable));
}
}
System.out.println();
print(con, "SELECT name, score FROM "
+ "(SELECT name, marks AS score FROM student) ORDER BY score");
}
}
}1. ID (label ID) INTEGER, Java: java.lang.Integer, nullable: false
2. NAME (label NAME) CHARACTER VARYING, Java: java.lang.String, nullable: false
3. MARKS (label SCORE) DECIMAL, Java: java.math.BigDecimal, nullable: true
NAME SCORE
Srihitha 84.25
Navika 91.50Explanation:
- The first part loops from 1 to
getColumnCount()and describes each column of the query. - Name vs label: the third column is selected as
marks AS score.getColumnNamereturns the real name,MARKS;getColumnLabelreturns the alias,SCORE. The label is the one to use withrs.getString(label)and for display. getColumnClassNameshows whatgetObjectwill return:Integer,StringandBigDecimal.isNullablecompares against the constantcolumnNullable.idandnameareNOT NULL;marksisn't.- The
printmethod knows nothing about the query it is given. It reads the column count and labels from the metadata, then prints every row withgetObject. It would work unchanged for anySELECT.
H2 reports names in upper case because that is how it stores them; other databases keep the case you typed.
Where It Is Used
| Use | How |
|---|---|
| Printing or exporting any query | Loop over the columns for headers, then over the rows |
| Converting rows to maps or JSON | Use each label as a key |
| Mapping rows to objects automatically | Frameworks match labels to field names |
| Database tools | SQL clients show query results this way |
Common Mistakes
Using getColumnName when the query has aliases. You get the underlying column name, and rs.getString with that name may fail.
Looping from 0. Columns are numbered from 1 to getColumnCount(), inclusive.
Confusing it with DatabaseMetaData. This describes one result; that describes the whole database (see "DatabaseMetaData in Java").
Interview Corner
What is ResultSetMetaData?
ResultSetMetaData provides information about the columns of a ResultSet, obtained with resultSet.getMetaData(). Its methods return the number of columns (getColumnCount), their names and labels (getColumnName, getColumnLabel), SQL and Java types (getColumnTypeName, getColumnType, getColumnClassName), precision and scale, nullability and the source table. It is used to write generic code that processes any query result, such as report generators, exporters and object mappers.
Practice
- Easy: Write a method that returns the column labels of a query as a
List<String>. - Harder: Write
List<Map<String, Object>> rows(Connection con, String sql)that turns any query into a list of maps keyed by column label.
Key Takeaways
Was this tutorial helpful?