JDBC

(Java Database Connectivity )

Amit Sharma

JDBC

Rapid Application Development-CS3011

Introduction
A RDBMS is usually the primary data resource in an enterprise application. The JDBC API provides developers with a way to connect to relational data from within Java code. Using the JDBC API, developer can create a client that can connect to a database, execute SQL statements, and processes the result of those statements.
Amit Sharma JDBC Rapid Application Development-CS3011

Introduction
JDBC helps you to write java applications that manage these three programming activities:
1. Connect to a data source, like a database 2. Send queries and update statements to the database 3. Retrieve and process the results received from the database in answer to your query In a package called java.sql, JDBC consists of a set of class files.
Amit Sharma JDBC Rapid Application Development-CS3011

Classes of jav.sql package

java.sql.DriverManager: loads driver, and creates connection to the database java.sql.Connection: represents a connection to the database java.sql.Statement: executes statements java.sql.Resultset: holds results of executing statements
Amit Sharma JDBC Rapid Application Development-CS3011

JDBCODBC
The ODBC class of databases are a subset of ANSI SQL-2 databases. It is a standard proposed by Microsoft.

In Computing, Open Database Connectivity (ODBC) provides a standard software API method for using DBMSs.
The designers of ODBC aimed to make it independent of programming languages, database systems, and Operating systems.

Amit Sharma

JDBC

Rapid Application Development-CS3011

JDBCODBC Bridge
Java soft in a join effort with Intersolv that created an implementation of the java.sql interface which is designed to allow access to ODBC databases in order to get JDBC. There is an ODBC driver for each type of database. In addition, the application talks to a particular database through the ODBC driver manager. The driver manager translates between the application and the ODBC driver and the ODBC driver translates between the driver manager and the particular database.

Amit Sharma

JDBC

Rapid Application Development-CS3011

JDBC Architecture
ResultSet
Statement

ResultSet
PreparedStatement

ResultSet
CallableStatement

Connection
Driver Manager JDBC ODBC Bridge ODBC Driver

Oracle Database

JDBC Drivers
Driver must be registered with the JDBC DriverManager, before using Driver. This is done by using Class.forName() method, as

try {
Class.forName(sun.jdbc.odbc.JdbcOdbcDriver); //Class.forName(com.oracle.jdbc.OracleDriver); } catch (ClassNotFoundException ce) { // Handle exception }

JDBC Driver Types

There are following types of JDBC Drivers JDBC-ODBC Bridge plus ODBC driver Native API, Partly Java Driver Pure Java driver for database Middleware Pure Java driver for direct to - Database

JDBCODBC Bridge plus an ODBC Driver

All database interaction happens via the ODBC API in this type of driver. The JDBC driver makes ODBC API function calls which inturn interacts with the backend database. This type of database is mainly dependent on the ODBC API and requires ODBC API to be installed on the client machine.

Native API Partly Java Driver

Java Application
JDBC API JDBC Driver Manager or DataSource Object

JDBC-ODBC Bridge Driver

Partial Java JDBC Driver

ODBC
DB Client Lib

DB Client Lib

Database server

Database server

This driver converts JDBC calls into database-specific SQL statements. Though all DBMS use standard SQL for database interactions, all DBMS do not support all the features.

Pure Java Driver for Database Middleware

Net pure Java Driver converts JDBC function calls to an intermediate function calls such as RMI, CORBA or HTTP calls. The net protocol in turn translates these calls into standard function calls.

Amit Sharma

JDBC

Rapid Application Development-CS3011

Pure Java Driver for Direct-toDatabase

Java Applet/Application
JDBC API JDBC Driver Manager or DataSource Object Pure Java JDBC Driver

Partial Java JDBC Driver

DB Middleware
Database server Type 4

Database server Type 3

A Type 4 driver takes JDBC calls and translates them into the network protocol which is used directly by the DBMS. Thus direct calls can make to the DBMS server by the client machines or application server.

The java.sql Package

Based on the different functionality, the classes in the java.sql package can be divided into following categories Connection Management Database Access Data Types Database Metadata Exceptions and Warnings

Amit Sharma

JDBC

Rapid Application Development-CS3011

Connection Management
Following classes / interfaces allow to establish a connection to the database Java.sql.DriverManager This class provides functionality to manage one or more database drivers. Each driver in turn lets you connect to a specific database. Java.sql.Driver This is an interface that abstracts the vendor specific connection protocol. Java.sql.connection This interface abstracts most of the interaction with the database. Using a connection, you can send SQL statements to the database, and read the results of execution.

Database Access
After obtaining a connection following classes / interfaces allow to send statements to the database Java.sql.Statement This inteface lets you to execute SQL statements over the underlying connection and access the result. Java.sql.PreparedStatement This is a variant of java.sql.Statement interface, which allows parameterized SQL statements. Parameterized SQL statements include parameter markers (as ?), which can be replaced with actual value later on.

Database Access
Java.sql.CallableStatement This interface lets you to execute stored procedure. java.sql.ResultSet This interface abstracts the result of SELECT statements. It also provides methods to access the results row by row.

Amit Sharma

JDBC

Rapid Application Development-CS3011

Basic steps to use a database in Java

1.Establish a connection 2.Create JDBC Statements 3.Execute SQL Statements 4.GET ResultSet 5.Close connections

Amit Sharma

JDBC

Rapid Application Development-CS3011

1.Establish a connection
Remember, The loading of the database driver does not connect to the database; but it merely creates an environment in the program where this can be done. Prior to any database specific SQL statements can be executed, the requirement is, a connection must be established to the database. This can accomplished through a call to the DriverManager getConnection method to find a specific driver that can create a connection to the URL requested.

Amit Sharma

JDBC

Rapid Application Development-CS3011

1.Establish a connection
import java.sql.*; Load the vendor specific driver

Class.forName(sun.jdbc.odbc.JdbcOdbcDriver);
Dynamically loads a driver class, for Oracle database

The first step in using JDBC is to load the JDBC-ODBC bridge driver. This is usually accomplished by the forName static method of the Class object which is the part of base Java system.

1.Establish a connection
Make the connection

Connection con =

DriverManager.getConnection("Jdbc:Odbc:emp");

The loading of JDBC driver does not connect to the database, but it creates the environment where it can be done. Here, the DriverManager searches through registered drivers until the required one is found whose URL is provided.
Amit Sharma JDBC Rapid Application Development-CS3011

2. Create JDBC statement(s)

In order to interact with the database, the SQL statement must be executed. This requires that a Statement object needs to be created to manage the SQL statements. This is accomplished with a call to the Connection class createStatement method.

2. Create JDBC statement(s)

Statement stmt = con.createStatement() ;
In the above call, it creates a Statement object using the established database connection. The Statement class provides methods for executing SQL statements and retrieving the results from the statement execution.

Amit Sharma

JDBC

Rapid Application Development-CS3011

3. Executing SQL Statements

String createStudent = "Create table Student " +

"(SSN Integer not null, Name VARCHAR(32), " + "Marks Integer)";

stmt.executeUpdate(createStudent);

String insertStudent = "Insert into Student values + "(123456789,abc,100)"; stmt.executeUpdate(insertStudent);

Amit Sharma JDBC Rapid Application Development-CS3011

4. Get ResultSet
The SQL calls sends the queries to the database and returns the results of the query as a ResultSet. If there is an error to be generated during the execution of the query, an exception is generated and caught using the try catch block. Successful execution of the executeQuery moves control to the next line of code.

4. Get ResultSet
String queryStudent = "select * from Student"; ResultSet rs = Stmt.executeQuery(queryStudent);

Amit Sharma

JDBC

Rapid Application Development-CS3011

Iterate ResultSet
while (rs.next()) { int ssn = rs.getInt("SSN"); String name = rs.getString("NAME"); int marks = rs.getInt("MARKS"); }

Amit Sharma

JDBC

Rapid Application Development-CS3011

Close connection
stmt.close(); con.close();

Amit Sharma

JDBC

Rapid Application Development-CS3011

import java.sql.*; public class Display { public static void main(String[] args) { Connection conn; try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); conn=DriverManager.getConnection("jdbc:odbc:sap","",""); Statement stmt = conn.createStatement(); ResultSet r = stmt.executeQuery ("SELECT * FROM emp"); while (r.next()){ System.out.println("Name : " + r.getString(1) + " " + r.getString(2)); System.out.println("Designation : " + r.getString(3)); System.out.println("Salary : " + r.getString(4) + "\n"); }
Amit Sharma JDBC Rapid Application Development-CS3011

r.close(); stmt.close(); conn.close(); } catch (Exception e) { System.out.println("ERROR : " + e); e.printStackTrace(System.out); } } }

Amit Sharma

JDBC

Rapid Application Development-CS3011

Creating Table
Following table is to be created
Employee_ID 6323 5768 Name Hemanth Bob

1234
5678

Shawn
Michaels

CREATE TABLE Employees (Employee_ID INTEGER, Name VARCHAR(30));

import javax.sql.*; public class Display { public static void main(String[] args) { Connection conn; try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); conn=DriverManager.getConnection("jdbc:odbc:sap","",""); String createString stmt.executeUpdate("CREATE TABLE employee(Emp_code varchar(50),Emp_name varchar(50),Address varchar(50),Phone int,Designation varchar(50),Department varchar(50),Basic_salary int);"); Statement stmt = con.createStatement(); stmt.executeUpdate(createString); } stmt.close(); conn.close(); } catch (Exception e) { System.out.println("ERROR : " + e); e.printStackTrace(System.out); } }

Using Prepared Statements

Sometimes it is more convenient to use a PreparedStatement object for sending SQL statements to the database. This special type of statement is derived from the more general class, Statement, that you already know. If you want to execute a Statement object many times, it normally reduces execution.

Using Prepared Statements

The main feature of a PreparedStatement object is that it is given an SQL statement when it is created. The advantage to this is that in most cases, this SQL statement is sent to the DBMS right away, where it is compiled. As a result, the PreparedStatement object contains not just an SQL statement, but an SQL statement that has been precompiled. This means that when the PreparedStatement is executed, the DBMS can just run the PreparedStatement SQL statement without having to compile it first.

Using Prepared Statements

Although PreparedStatement objects can be used for SQL statements with no parameters, you probably use them most often for SQL statements that take parameters. The advantage of using SQL statements that take parameters is that you can use the same statement and supply it with different values each time you execute it.
Amit Sharma JDBC Rapid Application Development-CS3011

Creating a PreparedStatement Object

PreparedStatement updateSales = con.prepareStatement( "UPDATE COFFEES SET SALES = ? WHERE COF_NAME LIKE ?");
The variable updateSales now contains the SQL statement, "UPDATE COFFEES SET SALES = ? WHERE COF_NAME LIKE ?", which has also, in most cases, been sent to the DBMS and been precompiled.

Supplying Values for PreparedStatement Parameters

You need to supply values to be used in place of the question mark placeholders (if there are any) before you can execute a PreparedStatement object. You do this by calling one of the setXXX methods defined in the PreparedStatement class. If the value you want to substitute for a question mark is a Java int, you call the method setInt. If the value you want to substitute for a question mark is a Java String, you call the method setString, and so on. In general, there is a setXXX method for each primitive type declared in the Java programming language.

Amit Sharma

JDBC

Rapid Application Development-CS3011

Supplying Values for PreparedStatement Parameters

The following line of code sets the first question mark placeholder to a Java int with a value of 75: updateSales.setInt(1, 75); setXXX The first argument given to a setXXX method indicates which question mark placeholder is to be set, and the second argument indicates the value to which it is to be set. The next example sets the second placeholder parameter to the string " Colombian": updateSales.setString(2, "Colombian");

Supplying Values for PreparedStatement Parameters

Therefore, the following code fragments accomplish the updation: PreparedStatement updateSales = con.prepareStatement( "UPDATE COFFEES SET SALES = ? WHERE COF_NAME LIKE ? "); updateSales.setInt(1, 75); updateSales.setString(2, "Colombian"); updateSales.executeUpdate():

Using a Loop to Set Values

Looking at these examples, you might wonder why you would choose to use a PreparedStatement object with parameters instead of just a simple statement, since the simple statement involves fewer steps. If you were going to update the SALES column only once or twice, then there would be no need to use an SQL statement with input parameters. If you will be updating often, on the other hand, it might be much easier to use a PreparedStatement object, especially in situations where you can use a for loop or while loop to set a parameter to a succession of values.

PreparedStatement updateSales = con.prepareStatement(update COFFEES set SALES = ? where COF_NAME like ?"); int [] salesForWeek = {175, 150, 60, 155, 90}; String [] coffees = {"Colombian", "French_Roast", "Espresso", "Colombian_Decaf", "French_Roast_Decaf"}; int len = coffees.length; for(int i = 0; i < len; i++) { updateSales.setInt(1, salesForWeek[i]); updateSales.setString(2, coffees[i]); updateSales.executeUpdate(); }
Amit Sharma JDBC Rapid Application Development-CS3011

try {

// Prepare a statement to insert a record

String sql = "INSERT INTO my_table (col_string) VALUES(?)"; PreparedStatement pstmt = connection.prepareStatement(sql); // Insert 10 rows for (int i=0; i<10; i++) { // Set the value

pstmt.setString(1, "row "+i);

// Insert the row pstmt.executeUpdate(); } } catch (SQLException e) { }

Amit Sharma

JDBC

Rapid Application Development-CS3011

Stored Procedures
A stored procedure is a group of SQL statements that form a logical unit and perform a particular task. Stored procedures are used to encapsulate a set of operations or queries to execute on a database server. For example, operations on an employee database (hire, fire, promote, lookup) could be coded as stored procedures executed by application code. Stored procedures can be compiled and executed with different parameters and results, and they may have any combination of input, output, and input/output parameters.

Stored Procedures
In some DBMSs, the following SQL statement creates a stored procedure: create procedure SHOW_SUPPLIERS as select SUPPLIERS.SUP_NAME, COFFEES.COF_NAME from SUPPLIERS, COFFEES where SUPPLIERS.SUP_ID = COFFEES.SUP_ID order by SUP_NAME
Amit Sharma JDBC Rapid Application Development-CS3011

Stored Procedures
The following code puts the SQL statement into a string and assigns it to the variable createProcedure, which we will use later: String createProcedure = "create procedure SHOW_SUPPLIERS " + "as " + "select SUPPLIERS.SUP_NAME, COFFEES.COF_NAME " + "from SUPPLIERS, COFFEES " + "where SUPPLIERS.SUP_ID = COFFEES.SUP_ID " + "order by SUP_NAME";

Calling a Stored Procedure from JDBC

JDBC allows you to call a database stored procedure from an application written in the Java programming language. The first step is to create a CallableStatement object. As with Statement and PreparedStatement objects, this is done with an open Connection object. A callableStatement object contains a call to a stored procedure; it does not contain the stored procedure itself.

Amit Sharma

JDBC

Rapid Application Development-CS3011

Calling a Stored Procedure from JDBC

The first line of code below creates a call to the stored procedure SHOW_SUPPLIERS using the connection con. The part that is enclosed in curly braces is the escape syntax for stored procedures. When the driver encounters "{call SHOW_SUPPLIERS}", it will translate this escape syntax into the native SQL used by the database to call the stored procedure named SHOW_SUPPLIERS.

CallableStatement cs SHOW_SUPPLIERS}");

con.prepareCall("{call

ResultSet rs = cs.executeQuery();
Amit Sharma JDBC Rapid Application Development-CS3011