Write access, principles -

Write access, principles

Connecting an application to a database by establishing a connection between client and database server:

Figure 840. Networking between clients and database server

Figure 841. JDBC™ features

Protocol connecting database client and server.
Vendor dependent implementations.

So JDBC™ is just one among a whole bunch of protocol implementations connecting database servers and applications. Consequently JDBC™ is expected to appear in the lower layer of multi-tier applications. We take a three-tier application as a starting point:

Figure 842. JDBC™ in a three-tier application

We may add an additional layer. Web applications are typically being build on top of an application server (WebSphere, Glassfish, Jboss,...) providing additional services:

Figure 843. JDBC™ connecting application server and database.

Opening a connection to a database server requires:

Figure 844. JDBC™ connection parameter

Database server type i.e. Oracle, DB2, Informix, Postgresql™, Mysql etc. due to vendor specific JDBC™ protocol implementations.
Server DNS name or IP number.
Server service's port number.
The database name within the given server.
Optional: A database user's account name and password.

Items 1 - 4 will be encapsulated into a so called JDBC™ URL. We consider a typical example corresponding to the previous parameter list:

Figure 845. Components of a JDBC™ URL

Figure 846. IETF Uniform Resource Identifier

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
...

Figure 847. URL examples

http://www.hdm-stuttgart.de/aaa
http://someserver.com:8080/someResource

Non-standard port 8080
ftp://mirror.mi.hdm-stuttgart.de/Firmen

Figure 848. Sub protocol examples

Database	JDBC™ URI
PostgreSQL	jdbc:postgresql://<HOST>:<PORT>/[database]
MySQL	jdbc:mysql://[host][:port]/[database][?p1=v1]...
Oracle	jdbc:oracle:thin:[user/password]@[host][:port]:SID
DB2	jdbc:db2://<HOST>:<PORT>/[database]
Derby	jdbc:derby://[host][:port]/[database]
MS. SQL S.	jdbc:sqlserver://host[:port];user=xxx;password=xyz
Sybase	jdbc:sybase:Tds:<HOST>:<PORT>/[database]

Figure 849. No standard port assignments ...

Postgresql:: 5432
IBM DB2:: 50000
Oracle:: 1521

No official IETF standard port assignments
Vendor specific defaults
Explicit port specification required

Figure 850. ... but Postgresql made it into Linux

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

Writing JDBC™ based applications follows a simple scheme:

Figure 851. JDBC™ architecture

From a programmer's point of view the java.sql.DriverManager is a bootstrapping object: Other objects like Connection instances are being created from this central and unique object.

Figure 852. DriverManager: Bootstrapping connections

Bootstrapping object.
java.sql.DriverManager shipped with JRE™.
Interfacing JRE™ and JDBC™ driver.
Provides instances of java.sql.Connection.
See Interfaces and classes in JDBC ™ .

Figure 853. Example: Mysql connection implementation

Interface MySQLConnection extends java.sql.Connection
Class ConnectionImpl implements MySQLConnection

Figure 854. Driver libraries

postgresql-42.1.4.jar
mysql-connector-java-x.y.z.jar
ojdbc6.jar

Figure 855. Driver libraries by Maven

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

<groupId>com.oracle</groupId>  <!-- requires access credentials -->
<artifactId>ojdbc7</artifactId>
<version>12.1.0</version>

Figure 856. Driver unavailable

conn = DriverManager.getConnection(
 "jdbc:postgresqll://localhost/hdm", "hdmuser", "XYZ");

java.sql.SQLException: No suitable driver found for
 jdbc:postgresqll://localhost/hdm

  at java.sql.DriverManager.getConnection(DriverManager.java:689)
  at java.sql.DriverManager.getConnection(DriverManager.java:247)
  at de.hdm_stuttgart.mi.sda1.DatabaseTest.initDatabase(DatabaseTest.java:34)
 ...

Figure 851, “JDBC™ architecture ” does not show details about the relations between java.sql.Connection, java.sql.Statement and java.sql.ResultSet objects. We start by giving a rough description of these three interfaces' tasks and responsibilities:

Figure 857. Connection interface

java.sql.Connection

Holding a permanent database server connection .
Stateful protocol.
Per connection properties: Isolation level, auto commit,...
rollback() / commit().

Figure 858. Statement interface

java.sql.Statement

Two distinct operation classes:

executeUpdate(): INSERT, UPDATE, DELETE: Integer return code
executeQuery(): SELECT: Returning java.sql.ResultSet, see the section called “Read Access”.

Figure 859. JDBC™ instances and relationships.

Figure 860. Important Connection methods

Figure 861. Important Statement methods

Figure 862. JDBC™ and threading.

From JDBC and Multithreading:

“Because all Oracle JDBC API methods are synchronized, if two threads try to use the connection object simultaneously, then one will be forced to wait until the other one finishes its use.”

Consequence:

Use one java.sql.Connection per thread.
Use connection pooling e.g. c3po.

Figure 863. JDBC™ connection pooling

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();
}

Prev	Up	Next
Introduction to JDBC™	Home	Write access, coding!

Introduction to JDBC™

Write access, principles