Read Access

Figure 890. 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 891. Server / client object's life cycle Slide presentation
Server / client object's life cycle

Figure 892. JDBC record container Slide presentation

Figure 893. 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 894. 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 895. 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 896. 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 897. 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 898. 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 899. Error prone type accessors! Slide presentation
int getInt​(int columnIndex)
double getDouble​(int columnIndex)
Date getDate​(int columnIndex)
...

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

Best SQL to Java type match.


Figure 901. 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 902. 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.