Notes

Outline

JDBC What is JDBC? JDBC Drivers JDBC Architecture Security model Anatomy of a JDBC application Examples

What is JDBC? It is an API for database connectivity It provides “universal database access” Provides a cross-DBMS connectivity to a wide range SQL databases Not necessary to develop a separate program to access databases from different vendors The new APIs provide access to other tabular data (e.g. spreadsheets) The APIs is provided in the java.sql package

Phases A Java application built on top of the JDBC API goes through three different phases: Opening a connection to a database Creating a statement object through which it passes SQL statements to the underlying DBMS Retrieve the results The anatomy of a JDBC-based application is discussed later We’ll see examples later….

JDBC Drivers A JDBC driver is a platform specific software providing access to a particular database Usually provided by the database vendor It implements the Driver interface A driver is made available by loading it in the JVM It is possible to load multiple drivers

A new JDBC driver Example Driver: class MyDriver implements Driver {    static {       new MyDriver();     }    MyDriver() {        DriverManager.registerDriver(this); } Note: A properly written driver must have a static initializer that causes an instance of the driver to be registered when the driver is loaded

DriverManager The topmost class in the java.sql hierarchy Maintains a collection of loaded drivers The registerDriver() method adds a driver to the collection When a JDBC-based Java application open a connection to a database, the DriverManager selects the driver (depending on the JDBC URL) that suits the application

JDBC URLs The JDBC URL takes the following form: jdbc:<subprotocol>:<subname> This URL has three parts: jdbc, subprotocol, subname The first part is always jdbc The subprotocol is the driver The third part identifies the database Example: jdbc:msql://host:port/DB

JDBC Architecture The general architecture of JDBC

Security Model JDBC follows the standard applet security model: It assumes that unsigned applets are untrustworthy Untrusted applets are not allowed to access local databases Untrusted applets are only allowed to open a database connection back to the server they were downloaded from Applications and trusted applets have no connection restrictions

Anatomy of a JDBC application Architecture of a typical JDBC application

Anatomy of a JDBC application Select a driver Decide on a URL to use Establish a connection Create a statement object Access the result Close the connection

Select a driver Select and load a driver using Class.forName For example, to load the imaginary MsqlDriver: Class.forName(“com.imaginary.msql.MsqlDriver”);

URL Here is an example String url = “jdbc:msql://host:port/DB”; Here we are following the rules of URLs discussed earlier with the DriverManager

Establish a connection Once a driver is loaded, it is time to establish a connection with the database A request for a connection is made through a call to DriverManager.getConnection Example: Connection con = DriverManager.getConnection(url, “user”, “passwd”);

Create a statement object Once a connection is established, it can be used to send SQL statements to the database A statement object is created as follows: Statement stmt = con.createStatement(); Now, an SQL statement can be sent to the database ResultSet ts = stmt.executeQuery(“Select * from table”); Three different method: executeQuery(), executeUpdate(), and execute()

Access the result The ResultSet object contains all of the rows that satisfied the condition in the SQL stmt The next() method is used to move to the next row Access to the data in a row can be obtained through the getXXX() where XXX is Int, Float, etc. Or, getXXX(Z) where Z is the column number

Close the connection Once the SQL statements have been executed and the results have been retrieved, close the connection: stmt.close(); con.close();

Examples The source code: SelectEmployee.java UpdateEmployee.java