Q.1)Explain the components of the JDBC API.

i. The JDBC API.

ii. The JDBC Driver Manager.
iii. The JDBC Test Suite.
iv. The JDBC-ODBC Bridge.

JDBC API :-

JDBC API (application programming interface) is a part of the Java platform that have included in
Java Standard Edition (Java SE) and the Java Enterprise Edition (Java EE) in itself.

The latest version of JDBC 4.0 application programming interface is divided into two packages

JDBC API provides various interfaces and methods to establish easy connection with different
databases.
javax.sql.*;
java.sql.*;
These packages contain all the APIs which provide programmatic access to a relational database (like
oracle,SQL server,MySQL etc.)

JDBC Test suite: JDBC Test suite facilitates the programmer to test the various operations such as
deletion, updation, insertion that are being executed by the JDBC Drivers.
JDBC Driver manager: JDBC Driver manager loads the database-specific driver into an application in
order to establish the connection with the database. The JDBC Driver manager is also used to make
the database-specific call to the database in order to do the processing of a user request.
List out types of JDBC Drivers. Explain Type1 and Type 4 Driver.

JDBC-ODBC Bridge Drivers: JDBC-ODBC Bridge Drivers are used to connect the database drivers to
the database. The bridge does the translation of the JDBC method calls into the ODBC method call. It
makes the usage of the sun.jdbc.odbc package that encompasses the native library in order to access
the ODBC (Open Database Connectivity) characteristics.
Q.2)Explain ORM working model

ORM stands for Object Relation Mapping. It is a middleware application or tool that sits between
the web application and database. It wraps the implementation specific details of storage drivers in
an API.

What is ORM?

ORM is a technique for converting data between Java objects and relational databases (table). In
simple words, we can say that the ORM implements responsibility of mapping the object to
relational model and vice-versa. the ORM tool does mapping in such a way that model class
becomes a table in the database and each instance becomes a row of the table.

Advantages of ORM Tool

o It saves time and efforts.

o It gives pace to development process.

o It reduces the development cost.

o It provides connectivity to the database.

o It makes development more object-oriented.

 o Easy transaction management.

o No need to implement database manually.

o Modification in any model (object or relational model) does not affect each other.

ORM Tools

There are many ORM tools available but the following ORM tools are the most commonly used.

o Hibernate

o TopLink

o EclipseLink

o OpenJPA

o MyBatis (formally known as iBatis)

Q.3)Explain callable statement with example.

 This interface extends Prepared statement interface and provides support for both input as
well as output parameter.
 Callable statement represents the stored procedure.
 Stored procedure: it is a subroutine used by applications to access data from a database.

Example

In database

create or replace procedure IDemo(id IN NUMBER,name IN VARCHAR2,p IN NUMBER) is

begin

insert into DEMO values(id,name,p);

end;

Source Code:

import java.sql.*;

import java.util.*;

public class DemoCall {

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

Class.forName("oracle.jdbc.driver.OracleDriver");

Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","hr","hr");
CallableStatement st=con.prepareCall("{call hr.IDEMO(?,?,?)}");

Scanner s=new Scanner(System.in);

System.out.println("enter student enrollment id");

int eid=s.nextInt();

System.out.println("enter student name");

String sname=s.next();

System.out.println("enter Student percentage");

float p=s.nextFloat();

st.setInt(1,eid);

st.setString(2,sname);

st.setFloat(3,p);

st.execute();

System.out.println("success");}}

Output

enter student enrollment id

enter student name

Asad

enter Student percentage

7.8

success

Q.4)Explain Prepared statement with example.

 The PreparedStatement interface is a subinterface of Statement. It is used to

execute parameterized query.
 PreparedStatement is used when the user wants to use the same SQL statements
many times in the program.
 It is an interface which accepts the input parameters at runtime.
Improves performance: The performance of the application will be faster if you use
PreparedStatement interface because query is compiled only once.
Methods of PreparedStatement interface

The important methods of PreparedStatement interface are given below:

Method Description

public void setInt(int paramIndex, int value) sets the integer value to the given parameter index.

public void setString(int paramIndex, String

sets the String value to the given parameter index.
value)

public void setFloat(int paramIndex, float

sets the float value to the given parameter index.
value)

public void setDouble(int paramIndex,

sets the double value to the given parameter index.
double value)

executes the query. It is used for create, drop, insert,

public int executeUpdate()
update, delete etc.

executes the select query. It returns an instance of

public ResultSet executeQuery()
ResultSet.

//Example of prepared Statement insert

import java.sql.*;

import java.util.Scanner;

class PreDemoInsert

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

Class.forName("oracle.jdbc.driver.OracleDriver");

Connection
con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","sonal","s123");

PreparedStatement pr=con.prepareStatement("Insert into student values(?,?,?)");

Scanner u=new Scanner(System.in);

System.out.println("enter id and name and percentage");

int ID = u.nextInt();

String name=u.next();

float p= u.nextFloat();
pr.setInt(1,ID);

pr.setString(2,name);

pr.setFloat(3,p);

pr.executeUpdate();

System.out.println(" records inserted");

con.close();

Q.5)Give Steps to connect Database

Steps to connect a Java Application to Database

The following 5 steps are the basic steps involve in connecting a Java application with Database using
JDBC.

1. Register the Driver

2. Create a Connection

3. Create SQL Statement

4. Execute SQL Statement

5. Closing the connection

1) Register the Driver

Class.forName() is used to load the driver class explicitly.

Example to register with JDBC-ODBC Driver

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

2) Create a Connection

getConnection() method of DriverManager class is used to create a connection.

Syntax

getConnection(String url)

getConnection(String url, String username, String password)

getConnection(String url, Properties info)

Connection con =

DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","username","password");

3) Create SQL Statement

createStatement() method is invoked on current Connection object to create a SQL Statement.

Syntax

public Statement createStatement() throws SQLException

Example to create a SQL statement

Statement s=con.createStatement();

4 )Execute SQL Statement

executeQuery() method of Statement interface is used to execute SQL statements.

Syntax

public ResultSet executeQuery(String query) throws SQLException

Example to execute a SQL statement

ResultSet rs=s.executeQuery("select * from user");

while(rs.next())

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

5)Closing the connection

After executing SQL statement you need to close the connection and release the session. The
close()

method of Connection interface is used to close the connection.

Syntax

public void close() throws SQLException

Example of closing a connection

con.close();

Q.6)List out types of JDBC Drivers. Explain Type1 and Type 4 Driver.

JDBC stands for Java Database Connectivity. JDBC is a Java API to connect and execute the
query with the database. It is a part of JavaSE (Java Standard Edition). JDBC API uses JDBC
drivers to connect with the database. There are four types of JDBC drivers:
JDBC Driver is a software component that enables java application to interact with the database.
There are 4 types of JDBC drivers:

1. JDBC-ODBC bridge driver

2. Native-API driver (partially java driver)
3. Network Protocol driver (fully java driver)
4. Thin driver (fully java driver)

1) JDBC-ODBC bridge driver

The JDBC-ODBC bridge driver uses ODBC driver to connect to the database.

The JDBC-ODBC bridge driver converts JDBC method calls into the ODBC function calls.

This is now discouraged (not use) because of thin driver.

Oracle does not support the JDBC-ODBC Bridge from Java 8.

Oracle recommends that you use JDBC drivers provided by the vendor of your database
instead of the JDBC-ODBC Bridge.

Advantages:
o easy to use.
o can be easily connected to any database.

Disadvantages:

2) Native-API driver

o The Native API driver uses the client-side libraries of the database.
o The driver converts JDBC method calls into native calls of the database API.
3) Network Protocol driver
o The Network Protocol driver uses middleware (application server) .
o It converts JDBC calls directly or indirectly into the vendor-specific database protocol.
o It is fully written in java.

Advantage:

Disadvantages:

 Network support is required on client machine.

 Requires database-specific coding to be done in the middle tier.
 Maintenance of Network Protocol driver becomes costly because it requires database-
specific coding to be done in the middle tier.
4) Thin driver
The thin driver converts JDBC calls directly into the vendor-specific database protocol. That is why it
is known as thin driver. It is fully written in Java language.

Advantage:

Disadvantage:

 Drivers depend on the Database.

Example establish connection with Oracle Driver