Write access, principles

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

Figure 779. Networking between clients and database server Slide presentation Create comment in forum
Networking between clients and database server

Figure 780. JDBC features Slide presentation Create comment in forum
  • 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 781. JDBC™ in a three-tier application Slide presentation Create comment in forum
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 782. JDBC™ connecting application server and database. Slide presentation Create comment in forum
JDBC™ connecting application server and database.

Opening a connection to a database server requires:

Figure 783. JDBC connection parameter Slide presentation Create comment in forum
  1. Database server type i.e. Oracle, DB2, Informix, Postgresql, Mysql etc. due to vendor specific JDBC™ protocol implementations.

  2. Server DNS name or IP number.

  3. Server service's port number.

  4. The database name within the given server.

  5. Optional: A database user's account name and password.


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

Figure 784. Components of a JDBC™ URL Slide presentation Create comment in forum
Components of a JDBC™ URL

Figure 785. IETF Uniform Resource Identifier Slide presentation Create comment in forum

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 786. URL examples Slide presentation Create comment in forum
  • http://www.hdm-stuttgart.de/aaa

  • http://someserver.com:8080/someResource

    Non-standard port 8080

  • ftp://mirror.mi.hdm-stuttgart.de/Firmen


Figure 787. Sub protocol examples Slide presentation Create comment in forum
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 788. No standard port assignments ... Slide presentation Create comment in forum
Postgresql:

5432

IBM DB2:

50000

Oracle:

1521

  • No official IETF standard port assignments

  • Vendor specific defaults

  • Explicit port specification required


Figure 789. ... but Mysql made it into Linux Slide presentation Create comment in forum
>grep postgresql /etc/services 
postgresql      5432/tcp       postgres      # PostgreSQL Database
postgresql      5432/udp       postgres

Writing JDBC™ based applications follows a simple scheme:

Figure 790. JDBC architecture Slide presentation Create comment in forum
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 791. DriverManager: Bootstrapping connections Slide presentation Create comment in forum

Figure 792. Example: Mysql connection implementation Slide presentation Create comment in forum

Figure 793. Driver libraries Slide presentation Create comment in forum
  • postgresql-42.1.4.jar

  • mysql-connector-java-x.y.z.jar

  • ojdbc6.jar


Figure 794. Driver libraries, Maven Slide presentation Create comment in forum
<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 795. Driver unavailable Slide presentation Create comment in forum
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 790, “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 796. Connection interface Slide presentation Create comment in forum
java.sql.Connection

Figure 797. Statement interface Slide presentation Create comment in forum
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 798. JDBC™ instances and relationships. Slide presentation Create comment in forum
JDBC™ instances and relationships.


Figure 800. Important Statement methods Slide presentation Create comment in forum

Figure 801. JDBC and threading. Slide presentation Create comment in forum

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:


Figure 802. JDBC connection pooling Slide presentation Create comment in forum
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();
}