lOMoARcPSD|28883126

lOMoARcPSD|28883126

1. What is JDBC? Draw and explain JDBC

architecture? JDBC Definition

• JDBC stands for Java Database Connectivity, which is a standard Java

API for database-independent connectivity between the
Java programming language, and a wide range of databases.

• The JDBC library includes APIs for each of the tasks mentioned below
that are commonly associated with database usage.

 Making a connection to a database.

 Creating SQL or MySQL statements.

 Executing SQL or MySQL queries in the database.

 Viewing & Modifying the resulting records.

• JDBC provides the same capabilities as ODBC, allowing Java programs to

contain database-independent code.

JDBC Architecture

• The JDBC API supports both two-tier and three-tier processing models for
database access but in general, JDBC Architecture consists of two layers:

·JDBC API: This provides the application-to-JDBC Manager

connection.

· JDBC Driver API: This supports the JDBC Manager-to-Driver

Connection.

• The JDBC API uses a driver manager and database-specific drivers to

provide transparent connectivity to heterogeneous databases.

• The JDBC driver manager ensures that the correct driver is used to access
each data source. The driver manager is capable of supporting multiple
concurrent drivers connected to multiple heterogeneous databases.

• Following is the architectural diagram, which shows the location of the

driver manager with respect to the JDBC drivers and the Java application:
 lOMoARcPSD|28883126

2. List and explain types of drivers available JDBC?

Driver

• A JDBC driver is a software component enabling a Java application to

interact with a database.

Types of Drivers

There are 4 types of JDBC drivers:

1) Type-1 driver or JDBC-ODBC bridge driver.

2) Type-2 driver or Native-API driver.

3) Type-3 driver or Network Protocol driver.

4) Type-4 driver or Thin driver.

 lOMoARcPSD|28883126

Type-1 driver or JDBC-ODBC bridge driver.

• In a Type 1 driver, a JDBC bridge is used to access ODBC drivers installed on each client
machine.

• Using ODBC, requires configuring on your system a Data Source Name (DSN) that
represents the target database.

Type 2: JDBC-Native API

• In a Type 2 driver, JDBC API calls are converted into native C/C++ API calls, which
are unique to the database.

• These drivers are typically provided by the database vendors and used in the same manner
as the JDBC-ODBC Bridge.

• The vendor-specific driver must be installed on each client machine.

 lOMoARcPSD|28883126

Type 3: JDBC-Net pure Java

• In a Type 3 driver, a three-tier approach is used to access databases.

• The JDBC clients use standard network sockets to communicate with a middleware
application server.

• This kind of driver is extremely flexible, since it requires no code installed on the client
and a single driver can actually provide access to multiple databases.

Type 4: Pure Java Driver

• In a Type 4 driver, a pure Java-based driver communicates directly with the vendor's
database through socket connection. This is the highest performance driver available for
the database.
 lOMoARcPSD|28883126

• This kind of driver is extremely flexible, you don't need to install special software on
the client or server.

3. List and explain seven steps used to connect with JDBC?

1) Import JDBC packages.

2) Load and register the JDBC driver.

3) Open a connection to the database.

4) Create a statement object to perform a query.

5) Execute the statement object and return a query resultset.

6) Process the resultset.

7) Close the statement objects and Close the connection.

Import JDBC Package

• This is for making the JDBC API classes immediately available to the application
program.

• Syntax

import java.sql.*;

• It contains all the classes and interfaces that JDBC supports.

lOMoARcPSD|28883126

Load and Register the JDBC Driver

• To load and register then driver we have to used the following .

Class.forName(String);

• Since forName(String) method throws an ClassNotFound Exception, hence it has to be

written in try and catch block.

• To Register the driver class

• The forName() method of Class class is used to register the driver class.

• This method is used to dynamically load the driver class.Syntax of forName()

method

• public static void forName(String className)throws ClassNotFoundExcep tion

Create the Connection object

• The getConnection() method of DriverManager class is used to establish connection

with the database.

• Syntax of getConnection() method

• 1) public static Connection getConnection(String url)throws SQLException

• 2) public static Connection getConnection(String url,String name,String pas sword)

• throws SQLException

Create the Statement object

• The createStatement() method of Connection interface is used to create statement.

• The object of statement is responsible to execute queries with the database.

 • Syntax of createStatement() method

lOMoARcPSD|28883126

• public Statement createStatement()throws SQLException

• Example to create the statement object

• Statement stmt=con.createStatement();

Execute the query

• The executeQuery() method of Statement interface is used to execute queries to the

database.

• This method returns the object of ResultSet that can be used to get all the records of a
table.

• Syntax of executeQuery() method

• public ResultSet executeQuery(String sql)throws SQLException

• Example to execute query

• ResultSet rs=stmt.executeQuery("select * from emp");

Process the resultset

• To process the result return from ResultSet object we use while loop with ResultSet
method next() as

while(rs.next())

System.out.println(rs.getInt(1)+" "+rs.getString(2));

Close the connection and Statement object

• Following method is used to close the connection and statement object.

• public void close()throws SQLException

• Example to close connection

• con.close(); lOMoARcPSD|28883126
 • Example to close statement

• St.close();

4.Explain PreparedStatement with example?

PreparedStatement Interface

• The PreparedStatement interface is a subinterface of Statement. It is used

to execute parameterized query.
• String sql="insert into emp values(?,?,?)";
• As you can see, we are passing parameter (?) for the values. Its value will be
set by calling the setter methods of PreparedStatement

Why use PreparedStatement?

• Improves performance: The performance of the application will be faster if

you use PreparedStatement interface because query is compiled only once.
• The prepareStatement() method of Connection interface is used to return
the object of PreparedStatement. Syntax:

public PreparedStatement prepareStatement(String query)throws SQLException{}

Methods of PreparedStatement interface

The important methods of PreparedStatement interface are given below:

Method Description
public void setInt(int paramIndex, sets the integer value to the given
int value) parameter index.
public void setString(int sets the String value to the given
paramIndex, String value) parameter index.
public void setFloat(int sets the float value to the given
paramIndex, float value) parameter index.
public void setDouble(int sets the double value to the given
paramIndex, double value) parameter index.
public int executeUpdate() executes the query. It is used for create,
drop, insert, update, delete etc.
public ResultSet executeQuery() executes the select query. It returns an
instance of ResultSet.
 lOMoARcPSD|28883126

Example of PreparedStatement interface that inserts the record

First of all create table as given below:

create table emp(id number(10),name varchar2(50));

import java.sql.*;
class InsertPrepared{
public static void main(String args[]){
try{
Class.forName("com.mysql.jdbc.Driver");

Connection con=DriverManager.getConnection("jdbc:mysql://localhost/db1",
"root","root");

PreparedStatement stmt=con.prepareStatement("insert into Emp values(?,?)

");
stmt.setInt(1,101);//1 specifies the first parameter in the query
stmt.setString(2,"Ratan");

int i=stmt.executeUpdate();
System.out.println(i+" records inserted");

con.close();

}catch(Exception e){ System.out.println(e);}

}
}

lOMoARcPSD|28883126
5.Write a short note on
a. ResultSet Object
b. ResultSetMetaData Object.
• Java ResultSetMetaData Interface

• The metadata means data about data i.e. we can get further information
from the data.

• If you have to get metadata of a table like total number of column,

column name, column type etc. , ResultSetMetaData interface is useful
because it provides methods to get metadata from the ResultSet object.

• Commonly used methods of ResultSetMetaData interface

• Method • Description

• public int getColumnCount()throws • it returns the total number of

SQLException columns in the ResultSet
object.

• public String getColumnName(int • it returns the column name of

index)throws SQLException the specified column index.

• public String • it returns the column type

getColumnTypeName(int name for the specified index.
index)throws SQLException

• public String getTableName(int • it returns the table name for

index)throws SQLException the specified column index.

• How to get the object of ResultSetMetaData:

• The getMetaData() method of ResultSet interface returns the object

of ResultSetMetaData. Syntax:

• public ResultSetMetaData getMetaData()throws SQLException

 lOMoARcPSD|28883126

import java.sql.*;

class Rsmd{

public static void main(String args[]){

try{

Class.forName("com.mysql.jdbc.Driver ");

Connection con=DriverManager.getConnection( "

jdbc:mysql://localhost/db1","root","root");

PreparedStatement ps=con.prepareStatement("select * from emp");

ResultSet rs=ps.executeQuery();

ResultSetMetaData rsmd=rs.getMetaData();

System.out.println("Total columns: "+rsmd.getColumnCount());

System.out.println("Column Name of 1st column: "+rsmd.getColumnName(1));

System.out.println("Column Type Name of 1st column: "+rsmd.getColumnTypeN

ame(1));

con.close();

catch(Exception e){ System.out.println(e);}

lOMoARcPSD|28883126
ResultSet interface

• The object of ResultSet maintains a cursor pointing to a row of a

table. Initially, cursor points to before the first row.

• But we can make this object to move forward and backward direction by
passing either TYPE_SCROLL_INSENSITIVE or
TYPE_SCROLL_SENSITIVE in createStatement(int,int) method as well
as we can make this object as updatable by:

• Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSI

TIVE,

ResultSet.CONCUR_UPDATABLE);

• Commonly used methods of ResultSet interface

1) public boolean next(): • is used to move the cursor to the one row
next from the current position.

2) public boolean • is used to move the cursor to the one row

previous(): previous from the current position.

3) public boolean first(): • is used to move the cursor to the first row
in result set object.

4) public boolean last(): • is used to move the cursor to the last row
in result set object.

5) public boolean • is used to move the cursor to the specified

absolute(int row): row number in the ResultSet object.

6) public boolean • is used to move the cursor to the relative

relative(int row): row number in the ResultSet object, it
may be positive or negative.

7) public int getInt(int • is used to return the data of specified

columnIndex): column index of the current row as int.

8) public int getInt(String • is used to return the data of specified

lOMoARcPSD|28883126
columnName): column name of the current row as int.

9) public String • is used to return the data of specified

getString(int column index of the current row as String.
columnIndex):

10) public String • is used to return the data of specified

getString(String column name of the current row as String.
columnName):

import java.sql.*;

class FetchRecord{

public static void main(String args[])throws Exception{

Class.forName("com.mysql.jdbc.Driver ");

Connection con=DriverManager.getConnection(""jdbc:mysql://localhost/db
1","root","root");

Statement stmt=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIV
E,ResultSet.CONCUR_UPDATABLE);

ResultSet rs=stmt.executeQuery("select * from emp765");

//getting the record of 3rd row

rs.absolute(3);

System.out.println(rs.getString(1)+" "+rs.getString(2)+" "+rs.getString(3));

con.close();

}}