How do I use DatabaseMetaData to get table column names?

In this example we are using the DatabaseMetaData to retrieve table’s column names. The metadata information can be obtained by calling the connection.getMetaData(). Next, we can then get a ResultSet object by calling metadata.getColumns(String catalog, String schemaPattern, String tableNamePattern, String columnNamePattern).

From this result set COLUMN_NAME, TYPE_NAME, COLUMN_SIZE and others information can be read.

package org.kodejava.example.sql;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

public class DbMetadataExample {
    private static final String DRIVER = "com.mysql.jdbc.Driver";
    private static final String URL = "jdbc:mysql://localhost/testdb";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "";
    
    public static void main(String[] args) throws Exception {
        Connection connection = null;
	try {
	    Class.forName(DRIVER);
	    connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
	    
	    DatabaseMetaData metadata = connection.getMetaData();
	    ResultSet resultSet = metadata.getColumns(null, null, "users", null);
	    while (resultSet.next()) {
		String name = resultSet.getString("COLUMN_NAME");
		String type = resultSet.getString("TYPE_NAME");
		int size = resultSet.getInt("COLUMN_SIZE");
		
		System.out.println("Column name: [" + name + "]; type: [" + type 
		    + "]; size: [" + size + "]");
	    }
	} catch (SQLException e) {
	    e.printStackTrace();
	} finally {
	    connection.close();
	}
    }
}

A bit information of stocks table described below:

Column name: [id]; type: [bigint]; size: [20]
Column name: [stock_id]; type: [varchar]; size: [8]
Column name: [name]; type: [varchar]; size: [32]
Column name: [price]; type: [double]; size: [10]
Column name: [created]; type: [datetime]; size: [19]
Column name: [create_by]; type: [varchar]; size: [16]
Column name: [modified]; type: [datetime]; size: [19]
Column name: [modified_by]; type: [varchar]; size: [16]

Wayan Saryada

A programmer, runner, recreational diver, currently living in the island of Bali, Indonesia. Mostly programming in Java, creating web based application with Spring Framework, JPA, etc. If you need help on Java programming you can hire me on Fiverr.

Leave a Reply