Transcript No Slide Title

MIS 3023
Business Programming II
Professor: Akhilesh Bajaj
Introduction to JDBC
© Akhilesh Bajaj, 2001-2008. All Rights Reserved.
Goals for Today
• To understand the programs we wrote in the JDBC lab
• To understand the basic JDBC API
Advantages of JDBC
• JDBC = Java Database Connectivity (popular belief though not true)
• The API (Application Programming Interface) is meant to
allow java programs to connect to a database, to retrieve,
insert, update or delete data in the database, and to process the data
in the java program.
Some advantages of this:
•The java program will be largely unchanged across different
platforms & databases.
• The only changes will be in the SQL part, in case we move across
databases, that have slightly different SQL syntax. The bulk of
the java program (the processing part) will be unchanged.
• This means potentially large increases in application programmer
productivity and also in maintenance costs of the application.
JDBC Drivers
• JDBC connections to a database are done using a JDBC driver.
• Almost all vendors now offer JDBC drivers.
•We are using a jdbc-odbc bridge driver to talk to a MS Access
Database or to a MYSL database.
• In case we want to run our programs on another database,
like Oracle, we need to install the JDBC driver for that DBMS on
our machine.
• http://www.javasoft.com/products/jdbc
has a searchable database of available drivers for different DBMSs.
In addition, we can also check the sites for the DBMS vendors,
like www.oracle .com.
Loading the Driver
• The JDBC API is in the java.sql package. We imported that
in all the code that we wrote in the labs.
• The driver is loaded into a program with the following code:
Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
•If we are using another driver, then it’s documentation will
tell us its class name. E.g., A driver whose class name is
jdbc.DriverXYZ will be loaded with:
Class.forName(“jdbc.DriverXYZ”);
Here, the java.lang.ClassLoader object locates and loads
the driver class from the CLASSPATH.
Connecting to the Database
• java.sql.DriverManager is a static class that provides methods to
obtain connections.
• The advantage is that this class provides a uniform method of
accessing all the drivers, so we write the same code for all drivers,
except that the driver class name is different for different drivers.
• The DriverManager.getConnection(url,user,password)
method provides an easy way to access all databases.
URL:
Basic syntax: protocol:<subprotocol>:<subname>
Protocol is always jdbc.
Subprotocol is supplied by the maker of that particular JDBC driver.
In our case, it’s odbc.
Subname is the way to identify the database.
In our case,it’s simply the name of the database: MyGarage.
If the database is on another machine, then the subname is where the
address will be, e.g., //hostname:port/subsubname
Connection Interface
• The Connection interface offers 3 methods for sending SQL
statements to the underlying database:
createStatement(): For a simple, parameterless SQL statement
prepareStatement(): For a prepared statement (with parameters)
prepareCall(): For a stored procedure. We won’t be doing this
method, because stored procedures are inherently DB specific
and hence against the principle of portability of an application.
•In CreateGarage.java & DropGarage.java, we used the
createStatement() method. This method gives a Statement Object.
• In InsertGarage.java, we used the prepareStatement() method.
This method gives a PreparedStatement object.
Statement Interface
• A Statement object is created using the Connection method
createStatement().
• Statement objects are used to send SQL statements without
parameters.
The method we used in this interface is:
•int executeUpdate(String sql): Performs an Update, Insert or
Delete on the database, and is also used for DDL statements
(like create table). It returns the number of rows affected by the
statement.
PreparedStatement Interface
• This is an extension of the Statement interface.
• Used for parametrized SQL queries. we wrote these in
InsertGarage.java.
The new methods here include
• setInt(int parameterIndex, int x): sets the given parameter
to x.
• Similarly, we have setDouble, setString, SetDate, etc.
SQLException Class
• Is derived from java.lang.Exception. It contains a description of
the error, and a chain to the next SQL Exception object, in case
there was more than one exception.
• In our labs, we printed out only the current SQL exception.
Below is useful code that prints out the chain of exceptions:
catch(SQLException ex) {
System.err.println(“\n---SQL EXCEPTION CAUGHT---\n”);
while(ex!=null){
System.err.println("SQLException: "+ex.getMessage());
ex.printStackTrace(System.err);
ex=ex.getNextException();
System.err.println(“ ”);
}//while
}//catch
Conclusion
• We made sense of the lab we have done , and also got an overview
of the portion of the JDBC API,that is used most often.
• For further reading, we can look at:
1. JDBC: Database Access with Java, by Hamilton, Cattell & Fisher
Addison Wesley.
2. http://java.sun.com has a lot of useful
information on JDBC, including features on new versions of the API.