JDBC

Content

Intro

JDBC Drivers

Multi-tier Architecture

JDBC Basics

Intro

• JDBC: Java Database Connectivity
• Goal: bindings for most SQL servers
• JDBC as a call-level-interface for SQL servers
• SQL calls
  • Either as string
  • Embedded SQL (ESQL)
• Return values / sets ready to use in Java
• ESQL
  • Keywords for SQL commands in Java
  • Preprocessor translates ESQL into valid Java code

JDBC Architecture

• Driver types
  • Define connection, instruction and result classes
  • 4 different types
• Type-1
  • ODBC driver is available
  • JDBC-ODBC-Bridge
• Type-2
  • ODBC driver and additional custom drivers available
• Type-3
  • JDBC driver is written entirely in Java but dependent on a middleware
• Type-4
  • JDBC driver written in Java, calls directly translated

Multi-tier App & SQL-2 Entry Level

• Multi-tier apps and drivers
  • 2 tier architecture: Client - Server
  • 3 tier architecture: Client - App Server - DB Server
  • Type-1, Type-2 driver require locally installed and configured software (ODBC or manufacturer driver)
  • Type-3, Type-4 driver do no require such configuration
• SQL-2 Entry Level
  • Every DB has own SQL dialect
  • JDBC driver implementation must at least implement SQL-2 Entry-Level-Standard 

Load Driver & Connection

// Load / specify driver
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

// Get connection object
// Requires connection string
//     consists of different parts
//       jdbc url, sub protocol, db name, etc...
DriverManager.getConnection(...);

• Connection
• First a connction to the DB is required
• Load & initialize DB drivers
• Create connection type via driver manager
• Connection is kept open

Connection Object & Instructions

• Connection
  • Allows creation of instruction objects
  • Change global DB settings
  • Close method closes the connection
  • Or automatically closed by garbage collector
• Instructions
  • All queries or changes are issued via instructions
  • Create some sort of Statement object
  • Statement object may be used to execute a query or update
  • Return type is either numerical or a set of DB elements
  • Statement objects are usually expensive (!)

Queries

• With the Statement object queries may be issues via executeQuery method
• Pass valid SQL instruction string
• Returns a ResultSet, which is similar to an Iterable
  • provides a next method
  • provides get methods to access certain data

Updates

• Either via INSERT INTO, UPDATE or DELETE FROM or
• SQL-DDL-Instructions (Data Definition Language)
• Return value is numerical value (number of elements that are updated)
• For DDL instructions return value is always 0

SQL Exception & SQLWarning

• SQLException
  • Thrown if SQL instruction fails
    • E.g. connection error, syntax error in instruction
  • getErrorCode returns manufacturer specific error code
  • getSQLState returns intern SQL state
  • getNextException allows iterating over chained exceptions
• SQLWarning
  • Never thrown, provided by getWarnings
  • Supported by Connection, Statement and ResultSet classes

References

Handbuch der Java-Programmierung

(5. Auflage)

Thank you for your attention!