© Wang Bin 2004

JDBC ----Java Database Connectivity

© Wang Bin 2004

In this lesson, you will learn to:

Identify the features of JDBC

Use JDBC to interact with a database

Objectives

© Wang Bin 2004

JDBC API: Is a part of JDK Enables Java applications to communicate with a

database

Getting Started

© Wang Bin 2004

Contains a set of classes and interfaces that are used to connect to a database

Is a low-level interface

Can be used with both two-tier and three-tier database architectures

Getting Started (Contd.)

© Wang Bin 2004

JDBC Drivers:

Are provided by a database vendor as a part of the database

Enable applications to communicate with the database

Are used by JDBC API to translate Java statements to SQL statements

Make a Java application DBMS independent

Getting Started (Contd.)

© Wang Bin 2004

Categories of JDBC Drivers:

JDBC-ODBC bridge driver

Used with DBMS/RDBMSes that contain the ODBC driver embedded into them

Native API partly Java driver

Used with DBMS/RDBMSes that contain a JDBC driver supplied by the database vendor

Native protocol pure Java driver/ JDBC-Net pure Java driver

Used to connect a client application or applet to a database over a TCP/IP connection

Getting Started (Contd.)

© Wang Bin 2004

JDBC Driver Manager:

Is used to maintain a list of drivers created for different databases

Connects a Java application to the appropriate driver specified in a Java program

JDBC-ODBC Bridge:

Is a driver provided by Sun Microsystems to access ODBC complaint databases from JDBC

Is implemented as the JdbcOdbc.class

Getting Started (Contd.)

© Wang Bin 2004

JDBC Application Architecture using the JDBC-ODBC bridge driver:

MS- ACCESS

DATABASE

MS-SQL

DATABASE

APPLICATION

JDBC

DRIVER

MANAGER

ACCESSDRIVER

SQLDRIVER

JDBC-

ODBC

BRIDGE

DRIVER

      

 

Getting Started (Contd.)

© Wang Bin 2004

A person who wants to open an account with Bank has

to submit his personal details by using the registration

form available on the bank’s Website. The following

program is used to accept the personal details of the

customers. You need to modify the code to store the

details in the Registration table of the Bank database.

Use JDBC to Query a Database

© Wang Bin 2004

Identify the generic steps involved in querying a database

Identify the mechanism to be used to load the driver

Identify the mechanism to be used to connect to a database

Identify the classes and the methods to be used to query a database

Create the DSN

Modify the program to store the customer registration details in the Bank database

Save, compile, and execute the program

Verify the data in the database

Task List

© Wang Bin 2004

The generic steps involved in querying a database are:

Loading the driver

Connecting to the database

Querying the database

Task 1: Identify the generic steps involved in

querying a database

© Wang Bin 2004

Loading a Driver

Is done by using the forName() method of the Class class

Result:

The following statement will be used to load the JDBC-ODBC bridge driver and JDBC driver:

Class.forName("sun.jdbc.odbc. JdbcOdbcDriver");

and

Class.forName( "com.microsoft.jdbc.sqlserver.SQLServerDriver" );

Task 2: Identify the mechanism to be used to load

the driver

© Wang Bin 2004

The Connection Object

Represents a connection with a database

The DriverManager. getConnection() method

Is used to establish a connection with a database

Returns a Connection object

Takes a JDBC URL as an input

<protocol>:<subprotocol>:<subname>

Task 3: Identify the mechanism to be used to connect

to a database

© Wang Bin 2004

A dsn named “MyDataSource” has to be created

The following statement will be used to connect to the database:     

String url = "jdbc:odbc:MyDatasource";

Connection con = DriverManager.getConnection( url,”user1”,””);

For JDBC you write as follow:

Connection con = DriverManager.getConnection( "jdbc:microsoft:sqlserver://MyDbComputerNameOrIP:1433;databaseName=master", sUsr, sPwd );

Task 3: Identify the mechanism to be used to connect

to a database (Contd.)

© Wang Bin 2004

The Statement Object:

Is used to send simple queries to the database

Contains the following methods:

executeQuery()

executeUpdate()

Task 4: Identify the classes and the methods to be

used to query a database

© Wang Bin 2004

Example

Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver");

Connection con = DriverManager.getConnection( "jdbc:odbc:MyDataSource",“user1","");

Statement stat=con.createStatement();

stat.executeQuery("Select * from Publishers");

Task 4: Identify the classes and the methods to be

used to query a database (Contd.)

© Wang Bin 2004

The ResultSet Object:

Is generated on executing a statement

Maintains a cursor pointing to the current row of data in the resultset

Contains the following methods:

next()

getYYY() where YYY is the datatype

Task 4: Identify the classes and the methods to be

used to query a database (Contd.)

© Wang Bin 2004

Example

Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver");

Connection con = DriverManager.getConnection( "jdbc:odbc:MyDataSource", "sa","");

Statement stat=con.createStatement();

ResultSet result=stat.executeQuery("Select * from Publishers");

while(result.next())

{ System.out.println(result.getString(2));

}

Task 4: Identify the classes and the methods to be

used to query a database (Contd.)

© Wang Bin 2004

The administrative supervisor of a primary school needs to access the database to display the list of names of those teachers who joined the school after June 2000. Write the JDBC related code for:

Loading the JDBC-ODBC bridge and establishing the connection

The appropriate query statement

(Hint: The table name is Teachers and the field name is TeacherName.)

Just a Minute…

© Wang Bin 2004

The PreparedStatement Object:

Is used for executing parameterized queries

Is created by using the prepareStatement()method

Example

PreparedStatement stat=con.prepareStatement(“Select * from publishers where pub_id = ?”);

Task 4: Identify the classes and the methods to be

used to query a database (Contd.)

© Wang Bin 2004

Parameters must be set before the statement is executed

stat.setString(1,pid.getText());

ResultSet result=stat.executeQuery();

Result:

Class - forName()

DriverManager - getConnection()

Connection - prepareStatement()

PreparedStatement - executeUpdate()

Task 4: Identify the classes and the methods to be

used to query a database (Contd.)

© Wang Bin 2004

Task 5: Create the DSN

Task 6: Modify the program to store the customer

registration details in the Bank database

Task 7: Save, compile, and execute the program

© Wang Bin 2004

Check whether the data has been inserted in the Registration table of the Bank database

Task 8: Verify the data in the database

© Wang Bin 2004

The ResultSetMetaData interface: Is used to obtain information about the columns stored in a

ResultSet object Contains the following methods:

• int getColumnCount()• String getColumnName(int column_number)

• String getColumnTypeName(int column_number)

Resultset Metadata

© Wang Bin 2004

In this lesson, you learned that:

JDBC API provides a database-programming interface for Java programs. A Java program can send queries to a database by using the JDBC driver

The java.sql package contains classes that help in connecting to a database, sending SQL statements to the database, and processing the query results

The Connection object represents a connection with a database. It can be initialized using the getConnection() method of the DriverManager class

Summary

© Wang Bin 2004

The PreparedStatement object allows you to execute parameterized queries. It can be initialized using the prepareStatement() method of the Connection object

The setString() method sets the query parameters of the PreparedStatement object

The executeUpdate() method executes the query statement present in the PreparedSatement object and returns the number of rows affected by the query

The ResultSetMetaData interface is used to obtain information about the columns stored in a ResultSet object

Summary (Contd.)