jdbc3ResultSetAndMetadata

1package pkg11jdbc;2 3import java.sql.Connection;4import java.sql.DriverManager;5import java.sql.ResultSet;6import java.sql.ResultSetMetaData;7import java.sql.SQLException;8import java.sql.Statement;9 10/*11 * jdbc3ResultSetAndMetadata.java12 * ------------------------------13 * Reading rows from a ResultSet and discovering columns via ResultSetMetaData.14 *15 * DEFINITION:16 *   A ResultSet is a forward cursor over query results. ResultSetMetaData17 *   describes the shape of those results (column names, types, counts) — useful18 *   for generic tools that don't know the schema ahead of time.19 *20 * KEY POINTS:21 *   - rs.next() advances the cursor; returns false past the last row.22 *   - Read columns by 1-based index OR by name; getObject() is type-generic.23 *   - getMetaData() gives column count, labels, and SQL type names.24 *   - Watch for NULLs: use wasNull() after a primitive getXxx() if it matters.25 *26 * Runs for real with an in-memory DB driver; otherwise prints guidance.27 */28public class jdbc3ResultSetAndMetadata {29 30    static Connection tryConnect() {31        for (String url : new String[]{"jdbc:h2:mem:demo;DB_CLOSE_DELAY=-1",32                                       "jdbc:sqlite::memory:", "jdbc:hsqldb:mem:demo"}) {33            try { return DriverManager.getConnection(url); } catch (SQLException ignored) {}34        }35        return null;36    }37 38    public static void main(String[] args) throws SQLException {39        Connection conn = tryConnect();40        if (conn == null) {41            System.out.println("No in-memory DB driver found.");42            System.out.println("This demo would SELECT rows, then print each column's");43            System.out.println("name and type using ResultSetMetaData. Add h2.jar to run it.");44            return;45        }46 47        try (conn; Statement st = conn.createStatement()) {48            st.execute("CREATE TABLE product (id INT, name VARCHAR(30), price DECIMAL(8,2))");49            st.execute("INSERT INTO product VALUES (1,'Keyboard',49.99),(2,'Mouse',19.50)");50 51            try (ResultSet rs = st.executeQuery("SELECT * FROM product ORDER BY id")) {52                ResultSetMetaData md = rs.getMetaData();53                int cols = md.getColumnCount();54 55                // Describe the columns (metadata)56                System.out.println("Columns (" + cols + "):");57                for (int i = 1; i <= cols; i++)58                    System.out.printf("  %d. %-8s %s%n", i, md.getColumnName(i), md.getColumnTypeName(i));59 60                // Iterate the rows generically61                System.out.println("\nRows:");62                while (rs.next()) {63                    StringBuilder row = new StringBuilder("  ");64                    for (int i = 1; i <= cols; i++)65                        row.append(md.getColumnName(i)).append('=').append(rs.getObject(i)).append("  ");66                    System.out.println(row.toString().stripTrailing());67                }68            }69        }70    }71}