Read Access

Figure 906. JDBC™ read and write Slide presentation

  • CREATE / UPDATE / DELETE

    client modifies database server data:

    int result = statement.executeUpdate("UPDATE Person ...");

  • SELECT

    client receives copies of database server data:

    ResultSet result = statement.executeQuery("SELECT ... FROM Person ...");

Figure 907. Server / client object's life cycle Slide presentation
Server / client object's life cycle

Figure 908. JDBC™ record container Slide presentation

Figure 909. Reading data from a database server. Slide presentation
Reading data from a database server.

We take an example. Suppose our database contains a table of our friends' nicknames and their respective birth dates:

Figure 910. Names and birth dates of friends Slide presentation 
CREATE TABLE Friends (
   id INTEGER NOT NULL PRIMARY KEY
  ,nickname char(10)
  ,birthdate DATE
);
 
INSERT INTO Friends VALUES
   (1, 'Jim', '1991-10-10')
  ,(2, 'Eve', '2003-05-24')
  ,(3, 'Mick','2001-12-30')
  ;

Figure 911. Accessing friend's database records Slide presentation 
final Connection conn = DriverManager.getConnection (...);
final Statement stmt = conn.createStatement();
// Step 3: Creating the client side JDBC container holding our data records
final ResultSet data = stmt.executeQuery("SELECT * FROM Friends"); ❶

// Step 4: Dataset iteration
while (data.next()) { ❷
   System.out.println(data.getInt("id") ❸
       + ", " + data.getString("nickname") ❹
       + ", " + data.getString("birthdate")); ❺
}

❶

As being mentioned in the introduction to this section the JDBC™ standard provides a container interface rather than using java.util.List or similar.

❷

Calling next() prior to actually accessing data on the client side is mandatory! The next() method positions an internal iterator to the first element of our dataset unless the latter being empty. Follow the link address and **read** the documentation.

❸ ❹ ❺

The access methods have to be chosen according to matching types. An overview of database/Java™ type mappings is being given in https://docs.oracle.com/javase/1.5.0/docs/guide/jdbc/getstart/mapping.html.

Figure 912. Important ResultSet states Slide presentation
New: resultSet = statement.executeQuery(...)

Caution: Data not yet accessible!

Cursor positioned: resultSet.next() returning true

Data accessible until resultSet.next()returns false.

Closed: resultSet.next() returning false

Caution: Data not longer accessible!


Figure 913. JDBC™ to Java™ type conversions Slide presentation
JDBC™ Type Java™ type
CHAR, VARCHAR, LONGVARCHAR String
NUMERIC, DECIMAL java.math.BigDecimal
BIT boolean
TINYINT byte
... ...

Shamelessly copied from JDBC Types Mapped to Java Types.


Figure 914. Java™ to JDBC™ type conversions Slide presentation
Java™ Type JDBC™ type
String CHAR, VARCHAR, LONGVARCHAR
java.math.BigDecimal NUMERIC
boolean BIT
... ...

Shamelessly copied from Java Types Mapped to JDBC Types.


Figure 915. Error prone type accessors! Slide presentation 
int getInt​(int columnIndex)
double getDouble​(int columnIndex)
Date getDate​(int columnIndex)
...

Figure 916. Polymorphic accessor Slide presentation 
Object getObject​(int columnIndex)

Best SQL to Java type match.


Figure 917. Access by column name Slide presentation 
final int id =
  resultSet.getInt("id");
final String nickName =
  resultSet.getString("nickname");
final Date birthDate =
  resultSet.getDate​("birthdate");
 
CREATE TABLE Friends (
   id INTEGER NOT NULL PRIMARY KEY
  ,nickname char(10)
  ,birthdate DATE
);

Caveat: May impact performance.


Figure 918. Access by column index Slide presentation 
final int id =
  resultSet.getInt(1);
final String nickName =
  resultSet.getString(2);
final Date birthDate =
  resultSet.getDate(3);
 
CREATE TABLE Friends (
   id INTEGER NOT NULL PRIMARY KEY
  ,nickname char(10)
  ,birthdate DATE
);

Caveat: Error prone on schema evolution.


We now present a series of exercises thereby exploring important aspects of JDBC™ read access.

Prev  Up  Next
java.sql.PreparedStatement  Home  Getter methods and type conversion