• JDBC: Accessing Relational Data

  Concept of persistence

  Data exchange RDBMS ⇌ Java application

  Dealing with transactions

• JDBC: Accessing Relational Data
  • ➟ Persistence in Object Oriented languages

Persistence allows an object to outlive the process that created it.

The state of the object may be stored to disk and an object with the same state re-created at some point in the future.

public class User {
  String commonName; // Common name e.g. 'Joe Bix'
  String uid;        // Unique login name e.g. 'bix'
  ...                // getters, setters and other stuff

}
//------------------------------------
// Thread lifespan (transient instance)
User u = new User("Joe Bix", "bix");

CREATE TABLE User(
  commonName CHAR(80)
 ,uid CHAR(10) PRIMARY KEY
);
-- Persistent record (see Durability in ACID)
INSERT INTO User VALUES('Joe Bix', 'bix');

• JDBC: Accessing Relational Data
  • ➟ Introduction to JDBC™
    • ➟ Write access, principles

      JDBC architecture

      Important interfaces in java.sql

      Write data using java.sql.Statement

https://www.ietf.org/rfc/rfc2396.txt:

absoluteURI   = scheme ":" ( hier_part | opaque_part )

hier_part     = ( net_path | abs_path ) [ "?" query ]

net_path      = "//" authority [ abs_path ]

abs_path      = "/"  path_segments
...

>grep postgresql /etc/services 
postgresql      5432/tcp       postgres      # PostgreSQL Database
postgresql      5432/udp       postgres

From JDBC and Multithreading:

Consequence:

try (final Connection conn =
       C3P0DataSource.getInstance().getConnection()) {

  final PreparedStatement pstmt = conn.create...;
    ...
  pstmt.executeUpdate();
  // Auto close connection, back to pool.
} catch (SQLException e) {
  e.printStackTrace();
}

• JDBC: Accessing Relational Data
  • ➟ Introduction to JDBC™
    • ➟ Write access, coding!

      Providing a driver based on maven.

      Specifying unit tests.

...
<dependency>
  <groupId>postgresql</groupId>
  <artifactId>postgresql</artifactId>
  <version>9.1-901-1.jdbc4</version>
  <scope>runtime</scope>
</dependency> ...

246. Why <scope>runtime</scope>?

CREATE TABLE Person (
   name CHAR(20)
  ,email CHAR(20) UNIQUE
)

// Step 1: Open connection to database server
final Connection conn = DriverManager.getConnection (
  "jdbc:postgresql://localhost/hdm",         // Connection parameter URL
  "hdmuser",                                 // Username
  "XYZ");                                    // Password

// Step 2: Create a Statement instance
final Statement stmt = conn.createStatement();

// Step 3: Execute the desired INSERT
final int updateCount = stmt.executeUpdate(
  "INSERT INTO Person VALUES('Jim', 'jim@foo.org')");

// Step 4: Give feedback to the end user
System.out.println("Successfully inserted " + updateCount + " dataset(s)");

247. Exception on inserting objects

public class InsertTest {
  static private Connection conn;
  static private Statement stmt;

  @BeforeClass ❶ static public void initDatabase() throws SQLException {
    conn = DriverManager.getConnection(
      SimpleInsert.jdbcProperties.getString("jdbcurl"),
      SimpleInsert.jdbcProperties.getString("username"),...);
    ScriptUtils.executeSqlScript(conn, new ClassPathResource("schema.sql"));
    stmt = conn.createStatement();}

  @Test ❷
  public void test_010_insertJill() throws SQLException {
    Assert.assertEquals(1, SimpleInsert.insertPerson(
         stmt, "Jill", "jill@programmer.org"));
 }
@AfterClass ❸ static public void releaseDatabase()
   throws SQLException {conn.close();}

Getting ScriptUtils.executeSqlScript(...) to work:

<dependency>
  <groupId>org.springframework</groupId>
  <artifactId>spring-jdbc</artifactId>
  <version>5.3.1</version>
  <scope>test</scope>
</dependency>

final Connection conn = DriverManager.getConnection(...);
... // CRUD operations
conn.close(); // Important! Wanna use a connection pool instead?

Using try-with-resources statement.

try (final Connection conn = DriverManager.getConnection(...) {
  ... // CRUD operations
} catch (SQLException e) {...}

248. Interactive inserts, connection properties, error handling and unit tests
249. Interfaces and classes in JDBC™
250. Closing JDBC™ connections
251. Driver dispatch mechanism

• JDBC: Accessing Relational Data
  • ➟ Introduction to JDBC™
    • ➟ JDBC™ and security
      • ➟ Network sniffing

[...
5.5.24-0ubuntu0.12.04.1.%...X*e?I1ZQ...................e,F[yoA5$T[N.mysql_native_password.
 A...........!.......................hdmuser ❶......U.>S.%..~h...!.xhdm............j..../*

 ... INSERT INTO Person VALUES('Jim', 'jim@foo.org') ❷6...
  .&.#23000Duplicate entry 'jim@foo.org' for key 'email' ❸

Password?

What about the missing password?

Making MySQL Secure Against Attackers:

• JDBC: Accessing Relational Data
  • ➟ Introduction to JDBC™
    • ➟ JDBC™ and security
      • ➟ SQL injection

252. Attack from the dark side

• JDBC: Accessing Relational Data
  • ➟ Introduction to JDBC™
    • ➟ JDBC™ and security
      • ➟ Sanitizing user input

Regular expression matching user names.

[a-zA-Z]+

253. Using regular expressions in Java™
254. Input validation by regular expressions

• JDBC: Accessing Relational Data
  • ➟ Introduction to JDBC™
    • ➟ JDBC™ and security
      • ➟ java.sql.PreparedStatement

INSERT INTO Person VALUES ('Jim', 'jim@q.org')
INSERT INTO Person VALUES ('Eve', 'eve@y.org')
INSERT INTO Person VALUES ('Pete', 'p@rr.com')
...

Wasting time parsing SQL over and over again!

INSERT INTO Person VALUES
  ('Jim', 'jim@q.org'),
  ('Eve', 'eve@y.org'),
  ('Pete', 'p@rr.com') ... ;

Dealing with large record counts even this option may become questionable.

The database server's interpreter may interpret an attacker's malicious code among with intended SQL.

Steps 2. and 3. may be repeated without re-parsing the underlying SQL statement thereby saving database server resources.

final Connection conn = DriverManager.getConnection (...

final PreparedStatement pStmt = conn.prepareStatement(
  "INSERT INTO Person VALUES(?, ?)");❶

pStmt.setString(1, "Jim");❷
pStmt.setString(2, "jim@foo.org");❸

final int updateCount = pStmt.executeUpdate();❹

System.out.println("Successfully inserted " + updateCount + " dataset(s)");

Jim', 'jim@c.com');DROP TABLE Person;INSERT INTO Person VALUES('Joe

Attacker's injection text simply becomes part of the database server's content.

Problem solved!

In a nutshell: Only attribute value literals may be parameterized.

255. Prepared Statements to keep the barbarians at the gate

• JDBC: Accessing Relational Data
  • ➟ Introduction to JDBC™
    • ➟ Read Access

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")); ❺
}

Shamelessly copied from JDBC Types Mapped to Java Types.

Shamelessly copied from Java Types Mapped to JDBC Types.

int getInt​(int columnIndex)
double getDouble​(int columnIndex)
Date getDate​(int columnIndex)
...

Object getObject​(int columnIndex)

Best SQL to Java type match.

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.

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.

256. Getter methods and type conversion

• JDBC: Accessing Relational Data
  • ➟ Introduction to JDBC™
    • ➟ Read Access
      • ➟ Getter methods and type conversion

final int count = resultSet.getInt("numProducts");

Problem: Two possibilities in case of count == 0:

final int count = resultSet.getInt("numProducts");

if (resultSet.wasNull()) {
...
} else {
...
}

See wasNull().

257. Handling NULL values.

• JDBC: Accessing Relational Data
  • ➟ Introduction to JDBC™
    • ➟ Dynamically load a JDBC™ driver

import java.sql.Driver;
  ...
public class DriverShim implements Driver {
    private Driver driver;
    DriverShim(Driver driver) {
        this.driver = driver;
    }
    @Override
    public Connection connect(String s, Properties properties) throws SQLException {
        return driver.connect(s, properties);
    }
    @Override
    public boolean acceptsURL(String u) throws SQLException {
        return driver.acceptsURL(u);
    }
...
}

• JDBC: Accessing Relational Data
  • ➟ Introduction to JDBC™
    • ➟ Handling surrogate keys

• JDBC: Accessing Relational Data
  • ➟ Introduction to JDBC™
    • ➟ Transactions

258. Isolation level 1 vs. 2
259. JDBC™ and transactions
260. Aborted transactions