Introduction to Java Database Connectivity (JDBC) PATH 1

What is JDBC?
JDBC is an interface which allows Java code to execute SQL statements inside relational databases
- the databases must follow the ANSI SQL-2 standard

The JDBC-ODBC Bridge

- ODBC (Open Database Connectivity) is a Microsoft standard from the mid 1990’s.

- It is an API that allows C/C++ programs to execute SQL inside databases

- ODBC is supported by many products.

-The JDBC-ODBC bridge allows Java code to use the C/C++ interface of ODBC
         - it means that JDBC can access many different database products

- The layers of translation (Java --> C --> SQL) can slow down execution.

- The JDBC-ODBC bridge comes free with the JDK:

         - called sun.jdbc.odbc.JdbcOdbcDriver

- The ODBC driver for Microsoft Access comes with MS Office

         - so it is easy to connect Java and Access

Four Kinds of JDBC Driver

1.  JDBC-ODBC Bridge

         - translate Java to the ODBC API

2.  Native API

        - translate Java to the database’s own API

3.  Native Protocol

        - use Java to access the database more directly using its low level protocols

4.  Net Protocol

        - use Java to access the database via networking middleware (usually TCP/IP)

        - required for networked applications

JDBC Drivers

- A searchable list of drivers (freeware, shareware, and commercial) can be found at: http://developers.sun.com/product/jdbc/drivers

JDBC PseudoCode

All JDBC programs do the following:

1) load the JDBC driver

2) Specify the name and location of the database being used

3) Connect to the database with a Connection object

4)  Execute a SQL query using a Statement object

5) Get the results in a ResultSet object

6)  Finish by closing the ResultSet, Statement and Connection objects

DriveManager

It is responsible for establishing the connection to the database through the driver.

e.g.

Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver"); Connection conn =      DriveManager.getConnection(url);

Name the Database  

The name and location of the database is given as a URL

        - the details of the URL vary depending on the type of database that is being used

ODBC Database URL

Statement Object

The Statement object provides a ‘workspace’ where SQL queries can be created, executed, and results collected.

e.g.

Statement st = conn.createStatement():

        ResultSet rs = st.executeQuery( “ select * from Authors” );

:

       st.close();

ResultSet Object

- Stores the results of a SQL query.

- A ResultSet object is similar to a ‘table’ of answers, which can be examined by moving a ‘pointer’ (cursor).

- Cursor operations:

     -  first(), last(), next(), previous(), etc.

- Typical code:

while( rs.next() ) {

       // process the row;

        }

Output

Username & Password
The database’s link to the outside (e.g. its ODBC interface) must be configured to have a login and password
   -  details for ODBC are given later

Accessing a ResultSet
_ The ResultSet class contains many methods for accessing the value of a column of the current row
    -  can use the column name or position
       e.g. get the value in the lastName column:   rs.getString("lastName")

- The ‘tricky’ aspect is that the values are SQL data, and so must be converted to Java types/objects.
- There are many methods for accessing/converting the data,
        e.g.  getString(), getDate(), getInt(), getFloat(), getObject()

Reference : Introduction to Java Database Connectivity (JDBC) - Andrew Division