Database Connection Setup

Establishing a connection to the database is the initial requirement for acessing table metadata. The JDBC API provides the necessary tools for this operation.

import java.sql.*;

public class DatabaseMetadataReader {
    private static final String DB_URL = "jdbc:mysql://localhost:3306/test";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "password";
    
    public Connection establishConnection() throws SQLException, ClassNotFoundException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        return DriverManager.getConnection(DB_URL, USERNAME, PASSWORD);
    }
}

Retrieving Column Information

Once the data base connection is established, the next step involves querying the database schema to extract column details from a specific table.

public void fetchTableColumns(Connection connection, String tableName) 
        throws SQLException {
    Statement queryStatement = connection.createStatement();
    ResultSet columnData = queryStatement.executeQuery(
        "SHOW COLUMNS FROM " + tableName
    );
    
    processColumnResults(columnData);
    
    columnData.close();
    queryStatement.close();
}

Processing Metadata Results

The final phase involves iterating through the result set and extracting relevant column attributes such as name and data type.

private void processColumnResults(ResultSet results) throws SQLException {
    while (results.next()) {
        String columnName = results.getString("Field");
        String columnType = results.getString("Type");
        
        System.out.printf("Column: %s | Type: %s%n", columnName, columnType);
    }
}

// Usage example
public static void main(String[] args) {
    DatabaseMetadataReader reader = new DatabaseMetadataReader();
    try {
        Connection dbConnection = reader.establishConnection();
        reader.fetchTableColumns(dbConnection, "users");
        dbConnection.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

Alternative approach using DatabaseMetaData:

public void getColumnDetailsUsingMetadata(Connection conn, String tableName) 
        throws SQLException {
    DatabaseMetaData metaData = conn.getMetaData();
    ResultSet columns = metaData.getColumns(null, null, tableName, null);
    
    while (columns.next()) {
        String name = columns.getString("COLUMN_NAME");
        String type = columns.getString("TYPE_NAME");
        String size = columns.getString("COLUMN_SIZE");
        
        System.out.printf("Name: %s | Type: %s | Size: %s%n", name, type, size);
    }
    columns.close();
}