Back To JDBC Overview . . Internet Technology School . . . Instructor Jeff Zhuk

JDBC@ITS . . or 5 Steps with JDBC, a Java API for executing SQL statements

Different databases have different ways of communications, different API. Consistently following the principle Write once - run everywhere, Java offers a solution to this problem, Java Database Connectivity (JDBC).

JDBC concepts is implemented via JDBC drivers, a software created by each DB vendor to transform internal API of a specific database into a unified JDBC API. Developers only need to know unified JDBC API. They should not be concerned of specific ways connecting to different databases. Of course, as any rule, this one also has some exceptions.

JDBC API is implemented in the Java library package java.sql. A set of classes in this library allows developers to accomplish all tasks of handling data in RDBMS.

Working with a database our program usually executes the following tasks:

1. Making sure that a proper JDBC driver is loaded by JVM. Of course, this requires this driver to be present in a jar file in your project library. Usually it is the lib directory in your project.

2. Connect to a database with a proper connection URL, user name and password.

3. Use the connection object to prepare an SQL statement: java.sql.Statement or java.sql.PreparedStatement

4. Execute the statement. In the case of the SELECT statement our program will retrieve java.sql.ResultSet (a set of records) from the database. Otherwise (INSERT, UPDATE, DELETE) this execution will change data.

5. This step is needed in the case of retrieving data with the SELECT statement. Our program will need to process the Result Set to get specific data from the records.

The table below provides source fragments to illustrate each task.
1
Make sure a proper JDBC driver is loaded.
The driver must be present in the project library as a jar file, such as ojdbc6.jar for Oracle		The following lines will load and register the driver:
String driverName = "oracle.jdbc.driver.OracleDriver"; // for Oracle DB
java.sql.Driver driver =
(java.sql.Driver) Class.forName(driverName).newInstance(); // fails if ojdbc6.jar is not in the lib
DriverManager.registerDriver(driver);
2
Connect to a databaseUsing Oracle XE default connection URL		String connectionUrl = "jdbc:oracle:thin:@localhost:1521:xe"; // hostName=localhost; port=1521; DB instance=xe
String user = "ITS"; // In the Oracle world, usually user is the name of a database to connect
String password = "tiger";
Connection con = DriverManager.getConnection(connectionUrl, user, password);
3. Use the connection object con to prepare an SQL statement.
There are two types of statements: java.sql.Statement (a light version) and java.sql.PreparedStatement (a heavy version).
PreparedStatement is slower to create, but is reusable and performing better when program repeats the same SQL many times.
In the case of PreparedStatement we provide "?" instead of variable values and later replace "?" with run-time variables.
PreparedStatement is commonly used in JDBC programs.
3
Example of using PreparedStatement		PreparedStatement pstmt = con.prepareStatement(
"select NAME, SSN from PAYROLL where SSN = ?");
pstmt.setString(1, ssnVariable); // replacing the first (and only) "?" with ssnVariable
4
Execute the statement
pstmt.executeQuery(); // for SELECT
pstmt.execute(); // for INSERT, etc.
// While executing a query, a program will get back java.sql.ResultSet from the database
ResultSet rset = pstmt.executeQuery(); // ResultSet is a set of records retrieved by the query
5
Processing the Result Set
Getting specific data from the records
retrieved by our program with the execute method.		// Check if ResultSet still not empty and get first column from the next string result
while (rset.next() ) 
  {
      String aName = rset.getString( 1 );
      String ssn = rset.getString( 2 );
      System.out.println("Name is " + aName + " SSN is " + ssn);
  }

Assignments:
Read JDBC Intro from Oracle
Click for Source Samples