Unit 1:JDBC

TITLE OF PAPER: ADVANCED JAVA

UNIT 1: JDBC
1. Introduction

 JDBC is a Set of java API'S [Application programming interface] which are used to
execute SQL Statement. JDBC stands for java database Connectivity. It Consist of
set of classes & interface return in java programming language.
 It is developed by Sun microsystem. JDBC is an application programming interface
which provides mechanism for dynamically loading the correct java packages &
registering them with the JDBC driver manager. That is used as a connection
factory for Creating JDBC Connections which Supports Creating an executing
statements such as SQL insert, Update, &delete.
 JDBC is an application programming interface that define how a java programmer
can access the database in tabular format from java code using a set of standard
interface & classes return in the java programming Language. JDBC provides
methods for querying & updating the data in relational database management
System such as SQL, Oracle, MySQL, etc.
 JDBC is similar to open database connectivity [ODBC] which is used for accessing
& managing databases, but difference is that JDBC is design specifically for Java
programs whereas ODBC is not depended upon any language.

2. JDBC API

 JDBC API provides programmatic access to relational data from the java
programming language using the JDBC APJ's, application can execute SQL
statement, retrieved result & propagate changes back to Underrating data source.
The Java JDBC API Can also interact with multiple data Source in a distributed
heterogeneous environment. The JDBC 2.0 API include two packages java.sql &
javax.sql known as JDBC standard extension. Together they contain the necessary
classes to develop database application using java.

1
 Unit 1:JDBC

 The JDBC API found in java.sql package. In simplest term a JDBC API make it
possible to three things.
A] Established a connection with a data source.
B] Send queries & update statements to the data source.
C] Process the result.

3. Methods use In JDBC Connection

Method Description
Void close () Free & object of type Connection from database & other JDBC
resources.
Void commit () Makes all the changes since the last commit of roll back permanent
Statement create Create an object of type statement For Sending SQL statements to
statement() the database.

callable statements. It creates an object of type callable statement for calling the stored
prepare call (strings) procedures from database
Prepared statements It creates an object of type prepared statement for sending dynamic
prepare statements SQL statements to the database.
(string)
Boolean is closed () It returns true if the Connection is closed else return false.
Void rollback () The process of calling a function from another function is referred
to as rollback.

4. Methods used in JDBC statement

 close- It freeze an object of type Connection.

 Boolean execute (strings) -It executes the SQL statements specifically by the
strings. The get result set method used to retrieve the result.
 get Result set- This method retrieves the result set that generated by execute
method.

2
 Unit 1:JDBC

 executeQuery - This method is used to execute SQL statement Specified by the

string & returns the object of return type result set.
 get maxrows - This method returns maximum no of rows generated
 executeUpdate -This method executes SQL statements. The statements may be
insert, update, delete.

5. JDBC Components

 JDBC Consist of set of interfaces & classes which enables java programs to
execute SQL statements. Interfaces & classes in JDBC API are return in Java. The
JDBC API consist of following four Components.

A] JDBC Drivers - The JDBC driver is a Collection of classes which implements

interfaces define in the JDBC API for opening database connections. interacting
with databases & closing database Connections

B] Connections - Before performing any database operations via JDBC we have to

open a database connection. To open a database Connection, we can Call
getConnection() method of driver manager class.

Connection con=DriverManager.getConnection("URL,USER,PASS");

C] Statements - The JDBC statements are used to execute the SQL Or PLSQL
queries against the database. We need a statement for every single query. JDBC
API defines the Statement Callable Statement, Prepared statement types of
statement.

D] Result Sets - A query returns the data in the form of ResultSets. To read the
query result data ResultSet provides cursor that points to the Current row in the
ResultSet.

F] SQL Exception – This class handles any errors that occurred in a database
application.
3
 Unit 1:JDBC

6. JDBC Driver
JDBC driver is a software Component that enables java applications to interact with
the database "There are 4 types of JDBC drivers.

A] JDBC-ODBC - BRIDGE DRIVER -

The JDBC - ODBC bridge driver usages ODBC driver to Connect to the database.
The JDBC - ODBC bridge driver converts JDBC method calls into the ODBC
function Calls.

 Advantages
I) Easy to use
II) It can be easily connected to any database

 Disadvantage
I) Performance degraded because JDBC method Call is Converted into ODBC
function call
II) The ODBC driver needs to be installed on the client machine.

4
 Unit 1:JDBC

 Note - In Java 8 JDBC - ODBC - Bridge has been removed. Oracle doesn't support
the JDBC - ODBC Bridge from Java 8. Oracle recommends that you used JDBC
drivers provided by the vender of your database instead of the JDBC-ODBC bridge.

B] Native API Driver[Partially Java Driver] –

The native API driver uses client-side libraries of the database. The driver Converts
JDBC method call into native calls of the database API. It is not return entirely in
Java.

 Advantage
I) Performance Upgraded than JOBC-ODAC-BRIDGE Driver

 Disadvantage
I) The native driver needs to be installed on each client machine.
II) The vender client library needs to be installed on client machine.

C] Network Protocol Driver [fully Java Driver] –

The network Protocol Driver uses middleware [Application server] that converts
JDBC Call directly or indirectly into the vendors Specific database protocol. It is fully
return in java.
5
 Unit 1:JDBC

 Advantage
I) No client-side library is required because of application served that can perform
many tasks like load balancing, logging, etc.

 Disadvantage
I) Network Support is required on client-machine.
II) Requires database Specific coding to be done in the middle time.
III) Maintenance of network Protocol driver becomes Costly because it requires
database Specific Coding to be done in the middle timer.

D] Thin Driver –
The thin Driver Converts JDBC calls directly into Vendors Specific database protocol.
That is why it is known as Thin Driver. It is fully return in Java language. It is most
frequently use JDBC driver type.

6
 Unit 1:JDBC

 Advantage
I) Better performance than all other drivers.
II) No Software is required at client side or Server side.

 Disadvantage
I) Drivers depends on the database.

7. Java Database Connectivity

There are 5 Steps to Connect any java application with the database using JDBC. The
Steps are as follows:

A] Register the driver class –

The for Name () of class [capital class is class Name] class is used to register the
driver class. This method is use to dynamically load the driver class.

Syntax:
7
 Unit 1:JDBC

public static void ForName (String ClassName) throwsException.

Throws class not found exception.
Class.forName("com.mysql.jdbc.Driver");

B] Create Connection –
The getConnection() of Driver Manager class is used to establish a Connection with
the database
Syntax:
public static Connection get connection (string URL)throws SQL Exception.
Connection con=DriverManager.getConnection("");
("jdbc:mysql://localhost:3306/b56","root","root");

C] Create statement –
This method createStatement() of Connection interface is used to create statement. The
object of statement is responsible to execute queries with the database.

Syntax:
public statement create statement () throws SQL Exception.
It is responsible to Use the query in database
Statement stmt=con.createStatement();

D] Execute queries –
executeQuery() of statement interface is used to execute queries with database. This
method returns the object of ResultSet that can be used to get all the records of the
table.

Syntax:
public ResultSet executeQuery (string SQL) throws SQLException
ResultSet rs = stmt.executeQuery(“select count(tno) from teacher”);
while(rs.next()) {
System.out.println(rs.getInt(1));
}

E] Close Connection –

8
 Unit 1:JDBC

By closing connection object statement & result set will be close automatically. The
close() of connection interface is used to close the connection.

Syntax:
Public void close()throws SQLException
con.close();

8. Architecture of JDBC

 JDBC API is divided into user level API for processing SQL statements & handling
all applications to JDBC driver manager Communication & a JDBC driver API that
database venders use to interface their database drivers to java gives us an overview
of the JDBC architecture.
 The JDBC driver managed is the core of the JDBC architecture & Serve to Connect
the java application with one of the four different driver types.
 The classes that you use for JDBC programming are Contain in the java.sql &
javax.sql Package. The JDBC architecture Can be classified into two categories
A] JDBC API
B] Types of JDBC drivers
[For more info see point no 5 & 6]

9. Java Driver models.

JDBC API Support both 2-tier & 3-tier processing models for database access.

 2-tier model java applet or application interface direct with database. A JDBC
driver is required to communicate with the particular DBMS which is being
access. The SQL statements are sent to the database & used gets the result.

9
 Unit 1:JDBC

 3-tier model in the 3-tier model commands are send to a middle tier of Services
which then sends the Commands to the data Source. The data Source process the
Commands & Send to the result Back to the middle tier which then sends them
to the user.

10
 Unit 1:JDBC

 MIS directors find the 3-tier model very attractive because the middle tier makes
it possible to maintain control over access & the kinds of updates that can be
made to the corporate data. Another advantage is that it simplifies the
deployment of the application. Finally, in many cases 3-tier architecture can
provide performance.

 The function of middle tier are as follows :

A] It collects SQL statement from client.
B] Handle over these statement to the database.
C] Receives Result from the database to the client.
D] Maintains the control over access & update of the data.

 JDBC is a java application programming interface that allows java programmer

to access database management system from java code. Java application cannot
directly Communicate with a database to submit data & retrieve the result of
query. This is because a database can interpret only SQL Commands /
statements & not the java language statement.
11
 Unit 1:JDBC

 For this reason, we need a mechanism to translate java statement to SQL

statements, The JDBC architecture provides the mechanism for this kind of
translation. From the start the S/W developer of the java technology at Sun
microsystem were aware of the potential that java show for working with
databases.
 In 1915 these S/W developers began working on extending the standard java.
library to deal with SQL access to databases. The S/W developer 1st hope to do
was extend java So that it could talk to random database Using only pure java &
it did not take is long time to realize that this an important task.
 There are Simply to many databases out there using to many protocols moreover
although database vendors were all in favor of Sun microsystem providing a
Standard network protocol for database access.
 They were only in favor of it if Sun microsystem decided to use their network
protocol. All the database Vendors & tool vendor did agree on it would be
useful if Sun microsystem provided pure java API for SQL access along with a
driver manager to allow third-party drivers to Connect to a Specific databases.
 Database vendors could provide their own drivers to plug into the driver
manager. There would be a simple mechanism for registering third party drivers
with the driver manager as a result two APIs were created. Application
programmer Used JDBC API & database vendors & tool providers use the
JDBC follows uses driver API's.
 This organization follows Successful model of Microsoft which provided C
programming language interface for database access. Both JDBC & ODBC are
based on same idea. Programs returns according to the API talk to the driver
manager which inters uses a driver to talk to the actual database. All this means
the JDBC API is all that most programmers will ever have to deal with. JDBC
provides methods for querying & updating the data in relational database
management System such as SQL, oracle & soon.

12
 Unit 1:JDBC

10. Prepared Statement –

 Its interface extends the statement interface which gives you added
functionality with Couple of advantages over the generic statement object.
This statement gives you flexibility of Supplying arguments dynamically.
 E.g., string sql = update employee SET_name =? where id = 25
Prepared statement pstmt =con.preparedstatement (sql);
pstmt.close();
 Syntax:
public PreparedStatement prepareStatement(String query)throws SQLException{}

All parameters in JDBC are presented by ? mark Symbol which known as parameter
marker you must supply values For every parameter before executing SQL statement. It
improves performance of application will be faster if you are use prepared statement
interface because is compiled only once.
Method Description
public void setInt(int paramIndex, int sets the integer value to the given parameter
value) index.
public void setString(int paramIndex, sets the String value to the given parameter
String value) index.
public void setFloat(int paramIndex, float sets the float value to the given parameter
value) index.
public void setDouble(int paramIndex, sets the double value to the given parameter
double value) 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.

13
 Unit 1:JDBC

11. Callable statement –

 Callable statement interface is used to call the stored procedures & functions.
We can have business logic on the database by the use of stored procedure &
functions that will make the performance better because these are precompiled.
The prepare call() method of Connection interface returns the instance of
callable statement.

 Syntax : public CallableStatement prepareCall("{ call procedurename(?,?...?)}");

14
 Unit 1:JDBC

12. Programs

1. Write a JDBC program to display the details of employees (eno, ename,

department, sal) whose department is BBA[CA].

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.ResultSet;
public class Employee {

public static void main(String[] args) {

// TODO Auto-generated method stub
try {
Class.forName("com.mysql.jdbc.Driver");
Connection
con=DriverManager.getConnection("jdbc:mysql://localhost:3306/b56","root","root");
Statement stmt=con.createStatement();
String sql= "select * from employees where department='BBACA'";

ResultSet rs=stmt.executeQuery(sql);
while(rs.next())
{
System.out.println("\n");
System.out.println("\t" +rs.getInt(1));
System.out.println("\t" +rs.getString(2));
System.out.println("\t" +rs.getString(3));
System.out.println("\t" +rs.getInt(4));
}
}
catch(Exception e) {
System.out.print(e);
}
}
}
15
 Unit 1:JDBC

2. Write a java program to create Teacher table(TNo.TName, Sal, Desg) and insert a
record in it.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class Teacher {

public static void main(String[] args) {

// TODO Auto-generated method stub
try {
Class.forName("com.mysql.jdbc.Driver");
Connection
con=DriverManager.getConnection("jdbc:mysql://localhost:3306/b56","root","root");
Statement stmt=con.createStatement();
String sql= "insert into teacher values(2,'Patil Mam',50000,'HOD')";
stmt.executeUpdate(sql);
System.out.print("Values Inserted Sucessfully");
}
catch(Exception e) {
System.out.print(e);
}
}
}

16
 Unit 1:JDBC

3. Write a JDBC program to delete the records of employees whose names are starts
with 'a' character

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class Delete {

public static void main(String[] args) {

// TODO Auto-generated method stub
try {
Class.forName("com.mysql.jdbc.Driver");
Connection
con=DriverManager.getConnection("jdbc:mysql://localhost:3306/b56","root","root");
Statement stmt=con.createStatement();
String sql =("delete from employees where ename like 'a%'");
stmt.executeUpdate(sql);
System.out.print("Value Deleted Sucessfully");
}
catch(Exception e) {
System.out.print(e);
}
}
}

17
 Unit 1:JDBC

4. Write a JDBC program to count the number of records in table.(Without using

standard method)

import java.sql.ResultSet;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class Count {

public static void main(String[] args) {

// TODO Auto-generated method stub
try {
Class.forName("com.mysql.jdbc.Driver");
Connection
con=DriverManager.getConnection("jdbc:mysql://localhost:3306/b56","root","root");
Statement stmt=con.createStatement();
String sql = "select count(tno) from teacher";
ResultSet rs = stmt.executeQuery(sql);
while(rs.next()) {
System.out.println(rs.getInt(1));
}
}
catch(Exception e) {
System.out.print(e);
}
}
}

18
 Unit 1:JDBC

5. Write a JDBC program to remove “percentage” column from student (rno,

sname, percentage) table. Student table is already created.

package javaprograms;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class percentage {

public static void main(String[] args) {

// TODO Auto-generated method stub
try {
Class.forName("com.mysql.jdbc.Driver");
Connection
con=DriverManager.getConnection("jdbc:mysql://localhost:3306/b56","root","root");
Statement stmt = con.createStatement();
String str = "ALTER TABLE student DROP COLUMN percentage;";
stmt.executeUpdate(str);
System.out.println("column deleted...");
}
catch(Exception e) {
System.out.println(e);
}
}

19
 Unit 1:JDBC

6. Write a JDBC program to accept the details of customer (CID, CName, Address,
Ph_No) and store it into the database (Use Prepared Statement interface)

import java.util.Scanner;
import java.sql.*;
public class Custtable {
public static void main(String[] args) {
// TODO Auto-generated method stub
PreparedStatement ps;
try
{
int cid,no;
String cname,caddr,pno;
Scanner sc = new Scanner(System.in);
Class.forName("com.mysql.jdbc.Driver");
Connection
con=DriverManager.getConnection("jdbc:mysql://localhost:3306/b56","root","root");
if(con==null)
{
/ System.out.print("Connection Failed");
}
String sql="insert into Customer1 values(?,?,?,?)";
ps=con.prepareStatement(sql);
System.out.println("Customer Details....");
System.out.println("Enter Cid");
cid=sc.nextInt();
ps.setInt(1, cid);
System.out.println("Enter Cname");
cname=sc.nextLine();
ps.setString(2, cname);
System.out.println("Enter Pno");
pno=sc.nextLine();
ps.setString(3, pno);
System.out.println("Enter Caddr");
caddr=sc.nextLine();
20
 Unit 1:JDBC

ps.setString(4, caddr);
no=ps.executeUpdate();
if(no!=0)
System.out.println("Record added successfully");
else
System.out.println("Record Not added");
con.close();
}catch(Exception e)
{
System.out.println(e);
}
}
}

21
 Unit 1:JDBC

7. Write a JDBC program in java to update an address of given customer(CID,

CName, Address) and display updated details.

package javaprograms;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class upaddcust {

public static void main(String[] args) {

try {
Class.forName("com.mysql.jdbc.Driver");
Connection
con=DriverManager.getConnection("jdbc:mysql://localhost:3306/b56","root","root");
Statement stmt = con.createStatement();
String str = " update customer set Caddress='phaltan' where Cid=101;";
stmt.executeUpdate(str);
System.out.println("updated address..");
}
catch(Exception e) {
System.out.println(e);
}

22
 Unit 1:JDBC

8. Write a JDBC program to create a Mobile (Model_No, Company_Name, Price,

Color) table and insert a record in it.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.util.Scanner;
public class mobile {

public static void main(String[] args) {

// TODO Auto-generated method stub
try {
int mno,price;
String company,color;
Scanner sc = new Scanner(System.in);
Class.forName("com.mysql.jdbc.Driver");
Connection
con=DriverManager.getConnection("jdbc:mysql://localhost:3306/b56","root","root");
Statement stmt = con.createStatement();
String sql = "create table Mobile(Model_No INT,Company
varchar(20),Price INT,Color varchar(20))";
stmt.executeUpdate(sql);
System.out.println("Table Created");
System.out.println("Enter Model no");
mno=sc.nextInt();
System.out.println("Enter Company");
company=sc.nextLine();
System.out.println("Enter Price");
price=sc.nextInt();
System.out.println("Enter Color");
color=sc.nextLine();
stmt.executeUpdate("insert into Mobile
values("+mno+",'"+company+"',"+price+",'"+color+"')");
System.out.println("Record added successfully");
con.close();
23
 Unit 1:JDBC

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

24
 Unit 1:JDBC

9. Write a JDBC Program in java to display the names of Employees starting with
'S' character.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class empstarts {

public static void main(String[] args) {

// TODO Auto-generated method stub
try {
Class.forName("com.mysql.jdbc.Driver");
Connection
con=DriverManager.getConnection("jdbc:mysql://localhost:3306/b56","root","root");
Statement stmt=con.createStatement();
ResultSet rs=stmt.executeQuery("select * from employee where ename
like 'S%'");
System.out.println("eno\t"+"ename\t"+"department\t"+"sal");
while(rs.next())
{

System.out.println("\n"+rs.getInt(1)+"\t"+rs.getString(2)+"\t"+rs.getString(3)+"\t"+
rs.getInt(4));
}
}
catch(Exception e)
{
System.out.println(e);
}
}

25
 Unit 1:JDBC

10. Write a JDBC program to delete the details of given employee (ENoEName
Salary).

package javaprograms;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.util.Scanner;

public class delemployee {

public static void main(String[] args) {

Scanner sc= new Scanner(System.in);
try {
Class.forName("com.mysql.jdbc.Driver");
Connection
con=DriverManager.getConnection("jdbc:mysql://localhost:3306/b56","root","root");
Statement stmt = con.createStatement();
System.out.println("enter id:- ");
int i = sc.nextInt();
String str = " delete from emp where id="+i+";";
stmt.executeUpdate(str);
System.out.println("deleted successfully..");
}
catch(Exception e) {
System.out.println(e);
}

26
 Unit 1:JDBC

11. Write a java program to create a student table with field’s rno, name and per.
Insert values in the table. Display all the details of the student on screen. (Use
PreparedStatement Interface).

import java.sql.*;
import java.util.Scanner;
public class student {

public static void main(String[] args) {

// TODO Auto-generated method stub
PreparedStatement ps;
try
{
int per,rno,no;
String name;
Scanner sc = new Scanner(System.in);
Class.forName("com.mysql.cj.jdbc.Driver");
Connection
con=DriverManager.getConnection("jdbc:mysql://localhost:3306/b56","root","root");
Statement stmt=con.createStatement();
String sql="create table student1(rno INT,"+"name
varchar(30),"+"per INT);";
stmt.executeUpdate(sql);
System.out.println("given table created in database");
String query="insert into student1 values(?,?,?)";
ps=con.prepareStatement(query);
System.out.println("Enter roll no : ");
rno=sc.nextInt();
System.out.println("Enter name : ");
name=sc.nextLine();
System.out.println("Enter per : ");
per=sc.nextInt();
ps.setInt(1,rno);
ps.setString(2,name);
ps.setInt(3,per);
27
 Unit 1:JDBC

no=ps.executeUpdate();
if(no!=0)
System.out.println("Data inserted succesfully.....");
else
System.out.println("Data not inserted");
ResultSet rs=stmt.executeQuery("select * from
Student1");
System.out.println("rno\t"+"sname\t"+"perc");
while(rs.next())
{

System.out.println("\n"+rs.getInt(1)+"\t"+rs.getString(2)+"\t"+rs.getInt(3));
}
con.close();
}
catch(Exception e)
{
System.out.println(e);
}
}
}

Copyright © 2023 Tejas.R.Shinde [SYB.B.A(C.A)]

All Rights Reserved.
28