JDBC Statement – Извлечение записей (с Maven)

Для извлечения записей используется Statement.executeQuery(sql). Минимально необходимый код для извлечения записей:

Connection connection = getConnection();
Statement statement = connection.createStatement();

ResultSet resultSet = statement.executeQuery(query);

while (resultSet.next()) {
    System.out.println(resultSet.getString("ID"));
    System.out.println(resultSet.getString("USER_NAME"));
}

Полный пример, с созданием базы данных, таблицы в ней, вставкой записей в цикле и последующим извлечением приведен ниже:

import java.sql.*;

public class StatementsSelect {
    private static final String DRIVER_NAME = "org.apache.derby.jdbc.EmbeddedDriver";
    private static final String CONNECTION_STRING = "jdbc:derby:derbyDB;create=true";


    public static void main(String args[]) throws SQLException {
        StatementsSelect app = new StatementsSelect();
        app.run();
    }

    private void createTable() throws SQLException{
        String query = "CREATE TABLE USERS_TABLE (" +
                "ID INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), " +
                "USER_NAME VARCHAR(20) NOT NULL, " +
                "PRIMARY KEY (ID)" +
                ")";

        Connection connection = null;
        Statement statement = null;

        try {
            System.out.println("Creating table");

            connection = getConnection();
            statement = connection.createStatement();

            statement.execute(query);

            System.out.println("Table created");
        } catch (Exception e ){
            e.printStackTrace();
        } finally {
            if (connection != null) {
                connection.close();
            }
            if (statement != null) {
                statement.close();
            }
        }
    }

    private void insertRecords() throws SQLException{
        System.out.println("Inserting records");
        for (int index = 0; index < 10; index++) {
            System.out.println("Inserting record no. " + (index + 1));

            String query = "INSERT INTO USERS_TABLE " +
                    "(USER_NAME) VALUES " +
                    "('user_" + index + "')";

            Connection connection = null;
            Statement statement = null;
            try {
                connection = getConnection();
                statement = connection.createStatement();

                statement.executeUpdate(query);
            } catch (Exception e ){
                e.printStackTrace();
            } finally {
                if (connection != null) {
                    connection.close();
                }
                if (statement != null) {
                    statement.close();
                }
            }
        }
        System.out.println("Records inserted");
    }

    private void selectRecords() throws SQLException{
        System.out.println("Selecting records from table");

        String query = "SELECT ID, USER_NAME FROM USERS_TABLE";

        Connection connection = null;
        Statement statement = null;
        try {
            connection = getConnection();
            statement = connection.createStatement();

            ResultSet resultSet = statement.executeQuery(query);

            while (resultSet.next()) {
                System.out.println("Record:");
                System.out.println(" id: " + resultSet.getString("ID"));
                System.out.println(" name: " + resultSet.getString("USER_NAME"));
            }
        } catch (Exception e ){
            e.printStackTrace();
        } finally {
            if (connection != null) {
                connection.close();
            }
            if (statement != null) {
                statement.close();
            }
        }

        System.out.println("Records selected");
    }

    private void run() throws SQLException{
        createTable();
        insertRecords();
        selectRecords();
    }

    private Connection getConnection() throws ClassNotFoundException, SQLException {
        Class.forName(DRIVER_NAME);
        Connection connection = DriverManager.getConnection(CONNECTION_STRING);
        return connection;
    }
}

В результате в консоль будет выведено:

Creating table
Table created
Inserting records
Inserting record no. 1
Inserting record no. 2
Inserting record no. 3
Inserting record no. 4
Inserting record no. 5
Inserting record no. 6
Inserting record no. 7
Inserting record no. 8
Inserting record no. 9
Inserting record no. 10
Records inserted
Selecting records from table
Record:
 id: 1
 name: user_0
Record:
 id: 2
 name: user_1
Record:
 id: 3
 name: user_2
Record:
 id: 4
 name: user_3
Record:
 id: 5
 name: user_4
Record:
 id: 6
 name: user_5
Record:
 id: 7
 name: user_6
Record:
 id: 8
 name: user_7
Record:
 id: 9
 name: user_8
Record:
 id: 10
 name: user_9
Records selected

Необходимые зависимости могут быть получены с помощью Apache Maven, pom.xml приведен ниже:

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>ru.mydesignstudio</groupId>
  <artifactId>statements</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  
  <dependencies>
        <dependency>
            <groupId>org.apache.derby</groupId>
            <artifactId>derby</artifactId>
            <version>10.12.1.1</version>
        </dependency>
    </dependencies>
</project>