This lecture gives an introductory overview of JDBC.

Agenda

• Introduce JDBC
• Show how JDBC deals with variations in SQL support
• Discuss JDBC drivers
• Look at DriverManager, JDBC URLs, Connection, Statement
• Discuss transaction support
• Walk you through a JDBC example

---

JDBC

• Standardized SQL lets you talk to databases from different vendors in a uniform way. 
• ODBC defined a standard C interface for talking to databases with SQL. 
• The JDBC (Java Database Connectivity) library (java.sql) allows you to use SQL to talk to databases from a Java program. 
• The java.sql calls go through a JDBC driver. 
• JDK comes with a JDBC to ODBC bridge. 
• DB vendors are creating native JDBC drivers; JDBC drivers that talk over a network also exist. 

---

JDBC Tier Models

---

SQL Variety

• Although SQL is a standard, only basic functionality is broadly and uniformly supported
• For example, not all DB vendors support stored procedures or outer joins
• JDBC passes any query string through to the DBMS
• JDBC "escape syntax" provides a standard way to access common non-standard functionality (for example, date literals)
• Can use DatabaseMetaData to adapt to particular underlying database

---

Registering JDBC Drivers

• DriverManager class:
  • keeps track of available drivers
  • connects a user with a driver
• Driver classes have a static initializer that creates an instance and calls DriverManager.registerDriver()
• DriverManager attempts to load any drivers specified in the java.lang.System property jdbc.drivers

  jdbc.drivers=sun.jdbc.odbc.JdbcOdbcDriver;

---

Making a Connection

• You call DriverManager.getConnection(), passing in:
  • A URL that specifies a driver
  • A user name
  • A password
• The DriverManager tests passes the URL to each registered driver (in order of registration)
• The first driver that can handle the URL gets to service the connection
• Only drivers loaded via the boot class loader or the class loader that loaded the requesting code are checked

---

JDBC URLs

• JDBC URLs have the form jdbc:<subprotocol>:<subname>
• <subprotocol> - name of a driver or a database connectivity mechanism
• <subname> - a way to identify the database
• URL decided upon by driver vendor
• Some examples:

  jdbc:odbc:mydb
  jdbc:dcenaming:accounts-receivable
  jdbc:netdb://vladimir:1066/conquer
  jdbc:odbc:mydb;UID=wtc;PWD=hastings
  

---

Using the Connection

• DriverManager.getConnection() returns a reference to a Connection object
• A Connection object represents one connection to a database
• An application can have:
  • One connection to one database
  • Multiple connections to the same database
  • Or multiple connections to different databases
• A "connection session" refers to the SQL statements executed and results returned over a connection

---

Statements

• Use the connection object to send SQL statements to the database
• If underlying database doesn't understand the statement, you'll get an exception
• Three kinds of statements:
  
  

  Class	Connection method	About
  Statement	createStatement()	Use for simple SQL statements with no input parameters
  PreparedStatement	prepareStatement()	Pre-compiled; Can take input parameters Use for simple SQL statements executed often, statements with input parameters
  CallableStatement	prepareCall()	Use to execute stored procedures

• Statement is the superclass of PreparedStatement, which is the superclass of CallableStatement

---

A Basic Query

• Register a driver with the DriverManager:
  
  

              Class.forName(
                  "sun.jdbc.odbc.JdbcOdbcDriver");
  

• Get a Connection from the DriverManager:
  
  

              Connection conn = DriverManager.getConnection(
                  "jdbc:odbc:mydb", "", "");
  

• Get a Statement object from the Connection:
  
  

              Statement stmt = conn.createStatement();
  

• Execute a query on the Statement object, get back a ResultSet object:
  
  

              ResultSet rs = stmt.executeQuery(
                  "SELECT LAST, FIRST, TOTAL " +
                  "FROM students.csv mydb " +
                  "ORDER BY TOTAL DESC");
  

• Process the results by extracting fields from the ResultSet:
  
  

              while (rs.next()) {
                  String s = rs.getString("FIRST") + " "
                      + rs.getString("LAST") + ": "
                      + rs.getString("TOTAL");
                  System.out.println(s);
              }
  

---

Transactions

• A transaction is a series of one or more statements followed by either a commit() or rollback() on the Connection
• When commit() or rollback() is invoked, the current transaction ends and a new one begins on that Connection
• Default mode is auto-commit: commit() automatically called after each statement
• If you disable auto-commit, you must explicitly call commit() or rollback() to complete the transaction
• All statements of a transaction will be committed or rolled back as a group

---

Transaction Isolation Levels

• Connections operate at a "transaction isolation level" that defines how conflicts between concurrent transactions will be resolved
• The Connection object starts out at a level that is most likely the default for the underlying database
• Can change it by calling setIsolationLevel() on the Connection
• JDBC has five isolation levels:
  • lowest - no transaction support
  • highest - no changes allowed to any data read by a transaction before commit
• The higher the isolation level, the slower the application will likely execute

---

JDBC Steps

• On your system or network:
  • First you need a database. 
  • Set up the system so that you can access the database via a JDBC driver. 
• In your Java program: 
  • Load the JDBC driver class, which will register itself.
  • Connect to the datasource through a "database URL" of the form jdbc.subprotocol.name
  • Execute SQL statements.
  • Close the connection.

---

The Student Database

• In the upcoming example, I use:  
  • The ODBC text driver that comes with Microsoft Office
  • the JDBC to ODBC bridge that comes with the JDK
• Here's the database, stored in file students.csv: 

  LAST,FIRST,HW1,HW2,HW3,HW4,TOTAL
  Busy,Lizzy,50,50,50,50,200
  Java,Jane,100,100,100,100,400
  Java,Joe,100,100,100,100,400
  Slow,Joe,0,0,0,0,0
  Smart,Bart,90,90,90,90,360
  

---

The Student Sorter Application

// In file jdbc/ex1/StudentSorter.java
import java.sql.*;
public class StudentSorter {
    public static void main(String[] args) {
        try {
            Class.forName(
                "sun.jdbc.odbc.JdbcOdbcDriver");
            Connection conn = DriverManager.getConnection(
                "jdbc:odbc:mydb", "", "");
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(
                "SELECT LAST, FIRST, TOTAL " +
                "FROM students.csv mydb " +
                "ORDER BY TOTAL DESC");
            while (rs.next()) {
                String s = rs.getString("FIRST") + " "
                    + rs.getString("LAST") + ": "
                    + rs.getString("TOTAL");
                System.out.println(s);
            }
            stmt.close();
        }
        catch(Exception e) {
            e.printStackTrace();
        }
    }
}

Here's the the output of StudentSorter:

Joe Java: 400
Jane Java: 400
Bart Smart: 360
Lizzy Busy: 200
Joe Slow: 0