Read Access

Figure 878. JDBC read and write Slide presentation Create comment in forum
  • 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 879. Server / client object's life cycle Slide presentation Create comment in forum
Server / client object's life cycle

Figure 880. JDBC record container Slide presentation Create comment in forum

Figure 881. Reading data from a database server. Slide presentation Create comment in forum
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 882. Names and birth dates of friends Slide presentation Create comment in forum
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 883. Accessing friend's database records Slide presentation Create comment in forum
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 884. Important ResultSet states Slide presentation Create comment in forum
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 885. JDBC to Java type conversions Slide presentation Create comment in forum
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 886. Java to JDBC type conversions Slide presentation Create comment in forum
Java Type JDBC type
String CHAR, VARCHAR, LONGVARCHAR
java.math.BigDecimal NUMERIC
boolean BIT
... ...

Shamelessly copied from Java Types Mapped to JDBC Types.


Figure 887. Fixed type accessors Slide presentation Create comment in forum
int getInt​(int columnIndex)
double getDouble​(int columnIndex)
Date getDate​(int columnIndex)
...


Figure 889. Access by column name Slide presentation Create comment in forum
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
);

Figure 890. Access by column index Slide presentation Create comment in forum
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
);

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