Для извлечения записей используется 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>