JDBC – Java Database Connectivity

Java Application

JDBC API

Oracle MySQL MS-ACCESS SQL Server PostgreSQ

L

Introduction:
✓ Java JDBC (Java Database Connectivity) is an API (Application Programming
Interface) that enables Java applications to interact with databases.
✓ It allows developers to access, query, update, and manipulate data in
various relational databases using Java code.
✓ JDBC acts as a bridge between the Java programming language and the
database management systems (DBMS).
✓ Sun Microsystems Released JDBC API.
✓ JDBC API Contains Set of Interfaces and classes.
✓ Database Software Vendors will Provide Implementations for JDBC API, ie
database driver software.
✓ Every Database software has its own database driver software.

Java Application

JDBC API

Oracle Driver MySQL Driver SqlServer Postgre Driver

Driver

Oracle DB MySQL DB Sql Server Postgre SQL

Note:
✓ Driver is a Software Program which communicates with DB Software.
✓ Database Drivers are Released as .jar files
o Oracle Driver is available as ojdbc_xx.jar
o MySQL Driver is available as mysql-connector_xx.jar

Education is the most powerful weapon, which you can use to change the world.
-Nelson Mandela
[1]
Database:
Software Application which stores data permentaly.
Ex: Oracle, MySQL, MS-Access, Postgre SQL, SQL Server, Ingress, Unify, DB2 etc

Database Client ---------SQL-------> Database Server

Note:
✓ MySQL Workbench is the Client for MySQL Server
✓ SQL Developer is the Client for for Oracle DB

Need of JDBC:
1. By using JDBC we can perform Basic CURD operations from Java
application.
a. C - Create
b. R - Retrieve
c. U - Update
d. D – Delete
2. We can also perform Complex operations like joins, calling stored
procedures/functions.
3. Huge vendor Industry support for JDBC, they developed multiple products
based on JDBC API.
a. http://www.oracle.com/technetwork/java/index-136695.html

Java Application ---------JDBC API-------> Database Server

JDBC Versions:
The Current Stable JDBC Version is 4.3

JDBC Java SE
4.3 JDK 9 and Above
4.2 JDK 8
4.1 JDK 7
4.0 JDK 6
3.0 JDK 1.4
2.1 JDK 1.2
1.2 JDK 1.1

Education is the most powerful weapon, which you can use to change the world.
-Nelson Mandela
[2]
Details Required to Communicate with Database:

Database User Name Password Host Port

Oracle --- --- localhost 1521
MySQL --- --- localhost 3306

Note:
These Above details user name, password, host and port will change as per
project requirements and database locations

Components of JDBC API:

✓ Interfaces
o Driver
o Connection
o PreparedStatement
o CallableStatement
o ResultSet
o RowSet
✓ Classes
o DriverManager
o Types
✓ Exception Classes
o SQLException

Steps in JDBC Program:

1. Load the Driver class
2. Get the connection object from the Database
3. Create Statement/Prepared Statement/Callable Statement
4. Execute the Query
5. Process the Result
6. Close the Connection

Education is the most powerful weapon, which you can use to change the world.
-Nelson Mandela
[3]
Working with MySQL Database Server:
✓ Conntect to MySQL Server using MySQL Workbench

#Command to display existing databases:

$ Show databases

#Command to Create a new database:

$ Create database mydb

#Command to select the database:

$ Use mydb

#Command to display all the tables from the database

$ show tables

#Command to create student table

create table student(
rollno int,
name varchar(20),
avg int);

commit;

Java Program to Establish Connection to MySQL Database Server:

URL to download mysql-connector jar file:
https://dev.mysql.com/downloads/connector/j/
Select Operating System: platform independent

✓ Download mysql-connector-j-8.3.0.zip and extract.

✓ locate for mysql-connector-j-8.3.0 file.
✓ Driver Class name: com.mysql.cj.jdbc.Driver

Note: jar file version may change according to Database Server Version.

Open STS IDE:

File → New→Java Project →
Project Name:MySQL-DB-CURD-Operations
→Next → Add mysql-connector-j-8.3.0 to build path

Education is the most powerful weapon, which you can use to change the world.
-Nelson Mandela
[4]
Create MySQLInsert.java
import java.sql.*;
public class MySQLInsert {
private static final String
DB_URL="jdbc:mysql://localhost:3306/mydb";
private static final String DB_UNAME="root";
private static final String DB_PWD="root";
private static final String INSERT_QUERY="insert into student
values(101,'Raj',99)";

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

//Step1: Load the Driver
//Class.forName("com.mysql.jdbc.Driver");
Class.forName("com.mysql.cj.jdbc.Driver");

//Step2: Get the DB Connection

Connection
con=DriverManager.getConnection(DB_URL,DB_UNAME,DB_PWD);

if(con!=null)
System.out.println("Connection Created: "+con);
else
System.out.println("Connection Creation Problem");

//Step3: Create the Statement Object

Statement stmt = con.createStatement();
//Step 4: Execute the Query
int rowsEffected=stmt.executeUpdate(INSERT_QUERY);
//Step 5: Process the Result
System.out.println("Records Inserted:"+rowsEffected);
//Step 6: Close the Connection
con.close();
}
}

SQL Queries are of two types

✓ DML Queries/Non-Select
✓ DQL Queries/Select
To Execute Non-Select Queries use the below method
Syntax:
✓ int executeUpdate(String query);
To Execute Select Queries use the below method
Syntax:
✓ ResultSet executeQuery(String Query);

Education is the most powerful weapon, which you can use to change the world.
-Nelson Mandela
[5]
Assignment:
Write a Java JDBC Program to Perform Update and Delete Operations on Student
Database Table.

File Name: MySQL_UpdateQuery.java

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

public class MySQL_UpdateQuery {

private static final String
DB_URL="jdbc:mysql://localhost:3306/mydb";
private static final String DB_UNAME="root";
private static final String DB_PWD="root";
private static final String UPDATE_QUERY="update student set
name='Harish' where rollno=102";

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

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

Connection
con=DriverManager.getConnection(DB_URL,DB_UNAME,DB_PWD);

Statement stmt = con.createStatement();

int rowsEffected=stmt.executeUpdate(UPDATE_QUERY);
System.out.println("Records Updated:"+rowsEffected);

con.close();
}
}

Education is the most powerful weapon, which you can use to change the world.
-Nelson Mandela
[6]
File Name: MySQL_DeleteQuery.java

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

public class MySQL_DeleteQuery {

private static final String
DB_URL="jdbc:mysql://localhost:3306/mydb";
private static final String DB_UNAME="root";
private static final String DB_PWD="root";
private static final String DELETE_QUERY="delete from student
where rollno=103";

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

Class.forName("com.mysql.cj.jdbc.Driver");
Connection
con=DriverManager.getConnection(DB_URL,DB_UNAME,DB_PWD);
Statement stmt = con.createStatement();
int rowsEffected=stmt.executeUpdate(DELETE_QUERY);
System.out.println("Records Deleted:"+rowsEffected);

con.close();

}
}

Education is the most powerful weapon, which you can use to change the world.
-Nelson Mandela
[7]
 Executing Select Query:
We have to use executeQuery() metho to execute select query, and
executeQuery() method returns ResultSet Object.

ResultSet
executeQuery(sql) Student
Rollno Name Average
Cursor
101 Raj 99 101 Raj 99
102 Hari 98 102 Hari 98
103 Jack 95 Data
103 Jack 95

File Name: MySQL_SelectQuery.java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class MySQL_SelectQuery {
private static final String
DB_URL="jdbc:mysql://localhost:3306/mydb";
private static final String DB_UNAME="root";
private static final String DB_PWD="root";
private static final String SELECT_QUERY="select * from student
where rollno=101";
public static void main(String[] args) throws Exception{
Class.forName("com.mysql.cj.jdbc.Driver");
Connection
con=DriverManager.getConnection(DB_URL,DB_UNAME,DB_PWD);
Statement stmt = con.createStatement();
ResultSet rs=stmt.executeQuery(SELECT_QUERY);
if(rs.next())
{
int rollno=rs.getInt("rollno");
String name=rs.getString("name");
int average=rs.getInt("avg");
System.out.println(rollno+" "+name+" "+average);
}
else
System.out.println("Record Not Found");

con.close();
}
}

Education is the most powerful weapon, which you can use to change the world.
-Nelson Mandela
[8]
Program to Retrieve all Records from the Database table:

Filename: MySql_RetrieveAll.java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class MySQL_RetrieveAll {

private static final String
DB_URL="jdbc:mysql://localhost:3306/mydb";
private static final String DB_UNAME="root";
private static final String DB_PWD="root";
private static final String SELECT_QUERY="select * from
student";

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

Class.forName("com.mysql.cj.jdbc.Driver");
Connection
con=DriverManager.getConnection(DB_URL,DB_UNAME,DB_PWD);
Statement stmt = con.createStatement();

ResultSet rs=stmt.executeQuery(SELECT_QUERY);

while(rs.next())
{
System.out.println(rs.getInt(1)+" "+rs.getString(2)+"
"+rs.getInt(3));
}
con.close();
}
}

Note:
Bydefault resultset cursor moves only in forward direction, we make it to move
in bi-direction based on our project requirements.

Education is the most powerful weapon, which you can use to change the world.
-Nelson Mandela
[9]
Assignment:
✓ Develop a Java Application for User Registration and Login Functionality.
✓ For both the tasks(Registration & Login) Read Data at run time(keyboard
input)
o Email id must be unique
✓ When the application runs it should display two options
1. Register
▪ Registration application should have following fields
• Email id, First Name, Last Name,Password and Re-type
Password
2. Login

ResultSet:
✓ ResultSet will Hold the Data retrieved by Select Query.
✓ ResultSet contains a cursor which points to before the first row by default.
✓ next() method of ResultSet moves the cursor to next row.
✓ Bydefault ResultSet moves the cursor in forward Direction only.

Types of ResultSet:
✓ SENSITIVE_RESULTSET – Bidirectional and Update the ResultSet Changes
Immediately
✓ INSENSITIVE_RESULTSET – Bidirectional and Do not Update the ResultSet
Changes.
✓ Both ResultSet types allow dynamic access when fetching records,
enabling navigation to any specific row in the result set.
✓ CONCUR_READ_ONLY – Allows you to Perform only Read Operation on the
ResultSet.
✓ CONCUR_UPDATABLE – Allows you to Perform Update Operation also on
the ResultSet.
Create a Table with Primary key Column and insert few Records:
create table student(
rollno int primary key,
name varchar(20),
avg int);
insert into student values(101,'Raj',99);
insert into student values(102,'Hari',79);
insert into student values(103,'Sunil',89);
commit;

Education is the most powerful weapon, which you can use to change the world.
-Nelson Mandela
[10]
FileName: MySQL_ResultSetDemo.java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class MySQL_ResultDemo {
private static final String
DB_URL="jdbc:mysql://localhost:3306/mydb";
private static final String DB_UNAME="root";
private static final String DB_PWD="root";
private static final String SELECT_QUERY="select * from
student";
public static void main(String[] args) throws Exception{
Class.forName("com.mysql.cj.jdbc.Driver");
Connection
con=DriverManager.getConnection(DB_URL,DB_UNAME,DB_PWD);
Statement
stmt=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CON
CUR_UPDATABLE);

ResultSet rs=stmt.executeQuery(SELECT_QUERY);
rs.absolute(2);
System.out.println(rs.getInt(1)+" "+rs.getString(2)+"
"+rs.getInt(3));

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

rs.absolute(2);
rs.updateInt(3, 88);
rs.updateRow();
System.out.println(rs.getInt(1)+" "+rs.getString(2)+"
"+rs.getInt(3));
con.close();
}
}

Other Important Methods of ResultSet:

✓ rs.first();
✓ rs.last();
✓ rs.setFetchSize();
✓ rs.getFetchSize();
✓ rs.deleteRow();

Education is the most powerful weapon, which you can use to change the world.
-Nelson Mandela
[11]
TYPE_SENSITIVE vs TYPE_INSENSITIVE:

10:00 am rs.executeQuery();
Database
101 Raj 20
Rollno Name Age
102 Hari 19
101 Raj 20
103 Prem 18
102 Hari 19
103 Prem 18
10:03 am Printing RS Data

10:02 Record
Updated

SENSITIVE: Updated Result will be Retrieved from the Database

INSENSITIVE: ResultSet Data will be Retrieved irrespective of Database Updation.

Note:
✓ To Experience SENSITIVE/INSENSITIVE ResultSet we have to use Type1 or
Type2 Drivers.
✓ Type1 & Type2 Drivers are Removed from JDK 8 onwards

Code to Experience refreshRow() Method to fetch latest updated data:

rs.beforeFirst();
System.out.println("Printing Data from ResultSet, Press Any key
to Continue...");
while(rs.next())
{
System.in.read();
rs.refreshRow(); //compares ResultSet Data with Database
System.out.println(rs.getInt(1)+" "+rs.getString(2)+"
"+rs.getInt(3));

}
Note:
✓ The Advantage of the above code is we will get always latest Data.
✓ The Disadvantage of of the above code is it will degrade the performance
of the application.

Education is the most powerful weapon, which you can use to change the world.
-Nelson Mandela
[12]
Code to Insert new Row into Database Table through ResultSet:
//Inserting new Row using ResultSet
rs.moveToInsertRow();
rs.updateInt(1, 105);
rs.updateString(2, "Ganesh");
rs.updateInt(3, 99);
rs.insertRow();
System.out.println("Printing Data from ResultSet, After Inserting new
Row...");

rs.beforeFirst();
while(rs.next())
{
System.out.println(rs.getInt(1)+" "+rs.getString(2)+" "+rs.getInt(3));
}

Working with ResultSet Metadata:

Code to Display Column Count and Column Names:

ResultSetMetaData metadata=rs.getMetaData();
System.out.println(metadata.getColumnCount());

for(int i=1;i<=metadata.getColumnCount();i++)
System.out.println(metadata.getColumnName(i));

Note:
ResuletSet Column Index Starts from 1.

Working with PreparedStatement:

✓ PreparedStatement is used to execute both select and non-select queries.
✓ It supports positional parameters in the query.
✓ Postitional Parameters are used to pass dynamic values at run time
✓ When we want to execute same query multiple times with different values
then it is recommended to use PreparedStatement.
✓ Positional Parameters Starts from 1.
✓ By using PreparedStatement we can avoid SQL Injections.

Ex:
Query without Positional Parameters:
Insert into student values(101,'Kishore',90); //Query with Static Values

Query with Positional Parameters:

Insert into student values(?,?,?); //Query with Dynamic Values

Education is the most powerful weapon, which you can use to change the world.
-Nelson Mandela
[13]
Write a JDBC Program to insert a new recoding using Prepared Statement.
File Name:MYSQL_PreparedStatementDemo.java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.*;
public class MySQL_PreparedStatementDemo {

private static final String DB_URL="jdbc:mysql://localhost:3306/mydb";

private static final String DB_UNAME="root";
private static final String DB_PWD="root";

private static final String INSERT_QUERY="INSERT INTO STUDENT VALUES(?,?,?)";

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

Class.forName("com.mysql.cj.jdbc.Driver");
Connection con=DriverManager.getConnection(DB_URL,DB_UNAME,DB_PWD);

PreparedStatement pstmt=con.prepareStatement(INSERT_QUERY);

pstmt.setInt(1, 106);
pstmt.setString(2, "Nilesh");
pstmt.setInt(3, 77);
int count=pstmt.executeUpdate();
System.out.println("Rows Effected:"+count);
con.close();
}
}

Note:
When the Query is having Positional Parameters, then we have to set the values
for those parameters before executing the query.

Assignment:
1. Create the above program by reading Positional Parameter values t at run
time.
2. Develop JDBC Application to retrieve all the students whos average is
more than inputted Average. Read Average at run time from keyboard. If
the user does not input the price then display all the records.
a. Flipkart Website Example for filtering Data

Education is the most powerful weapon, which you can use to change the world.
-Nelson Mandela
[14]
Solution for the Above Assignments:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Scanner;

public class MySQL_PrepStmt_Assignment1 {

private static final String
DB_URL="jdbc:mysql://localhost:3306/mydb";
private static final String DB_UNAME="root";
private static final String DB_PWD="root";
private static final String INSERT_QUERY="INSERT INTO STUDENT
VALUES(?,?,?)";

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

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

Connection
con=DriverManager.getConnection(DB_URL,DB_UNAME,DB_PWD);

PreparedStatement pstmt=con.prepareStatement(INSERT_QUERY);

Scanner sc=new Scanner(System.in);

System.out.print("Enter Rollno:");
int rollno=sc.nextInt();

System.out.print("Enter Name:");
String name=sc.next();

System.out.print("Enter Average:");
int avg=sc.nextInt();

pstmt.setInt(1, rollno);
pstmt.setString(2, name);
pstmt.setInt(3, avg);

int count=pstmt.executeUpdate();
System.out.println("Rows Effected:"+count);

System.out.println("Filtering Records based on Average Marks:");

System.out.print("Enter Average Marks:");

int avg_marks=sc.nextInt();

Education is the most powerful weapon, which you can use to change the world.
-Nelson Mandela
[15]
 StringBuilder sql=new StringBuilder("SELECT * FROM STUDENT");

if (avg_marks>0)
sql.append(" WHERE AVG >= ?");
pstmt=con.prepareStatement(sql.toString());

if (avg_marks>0)
pstmt.setInt(1, avg_marks);

ResultSet rs=pstmt.executeQuery();

while(rs.next())
{
System.out.println(rs.getInt(1)+" "+rs.getString(2)+"
"+rs.getInt(3));
}

con.close();
}
}

Assignment: Dynamic Query Creation:

Create a table from the following given data

EMP_ID EMP_NAME EMP_SALARY EMP_DEPT EMP_GENDER EMP_LOCATION

101 Raj 100000 Admin M HYD
102 Prem 100000 Admin M PUNE
103 Sunil 80000 HR M HYD
104 Priya 85000 HR F Chennai
105 Riya 96000 Accounts F Mumbai
Note: Insert 10 Records

Write a Java JDBC Application to Retrieve Employee Records from Employee

Table based on EMP_LOCATION, EMP_DEPT, EMP_GENDER Criteria.

Note: if value the search criteria is missing then retrieve all the records, if the
search criteria is provided then retrieve records based on given search criteria.
User may any one or any two or all 3 or none of the values.

Education is the most powerful weapon, which you can use to change the world.
-Nelson Mandela
[16]
File Name: MYSQL_EMP_Dynamic_Query.java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Scanner;

public class MYSQL_EMP_Dynamic_Query {

private static final String
DB_URL="jdbc:mysql://localhost:3306/mydb";
private static final String DB_UNAME="root";
private static final String DB_PWD="root";

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

Class.forName("com.mysql.cj.jdbc.Driver");
Connection
con=DriverManager.getConnection(DB_URL,DB_UNAME,DB_PWD);

System.out.println("Filtering Employee Records");

Scanner sc=new Scanner(System.in);
System.out.print("Enter Employee Department:");
String emp_dept=sc.next();

System.out.print("Enter Employee Gender(MALE/FEMALE):");

String emp_gender=sc.next();

System.out.print("Enter Employee Location:");

String emp_location=sc.next();

StringBuilder sql=new StringBuilder("SELECT * FROM EMPLOYEE

where 1=1");
if (emp_dept!=null && !emp_dept.equals("null"))
{
sql.append(" AND EMP_DEPT = ?");
}
if (emp_gender!=null &&!emp_gender.equals("null"))
{
sql.append(" AND EMP_GENDER = ?");
}
if (emp_location!=null && !emp_location.equals("null"))
{
sql.append(" AND EMP_LOCATION = ?");
}

System.out.println(sql);

Education is the most powerful weapon, which you can use to change the world.
-Nelson Mandela
[17]
 PreparedStatement
pstmt=con.prepareStatement(sql.toString());

int index=1;

if (emp_dept!=null && !emp_dept.equals("null")) {

pstmt.setString(index, emp_dept);
index++;
}

if (emp_gender!=null && !emp_gender.equals("null")) {

pstmt.setString(index, emp_gender);
index++;
}
if (emp_location!=null && !emp_location.equals("null")) {
pstmt.setString(index, emp_location);
}

ResultSet rs=pstmt.executeQuery();

if(rs==null)
System.out.println("No Record Found");
while(rs.next())
{
System.out.println(rs.getInt(1)+" "+rs.getString(2)+"
"+rs.getInt(3)+" "+rs.getString(4)+" "+rs.getString(5)+"
"+rs.getString(6));
}

con.close();
}
}

Assignment:
Develop Java JDBC Application to provide increment for each employee based on
salary.
Note: Increment % read from the keyboard, if input is not provided then
bydefault increment by 10%

Education is the most powerful weapon, which you can use to change the world.
-Nelson Mandela
[18]
File Name: MYSQL_EMP_Salary_Hike.java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;

public class MYSQL_EMP_Salary_Hike {

private static final String
DB_URL="jdbc:mysql://localhost:3306/mydb";
private static final String DB_UNAME="root";
private static final String DB_PWD="root";
private static final String SELECT_QUERY="SELECT * FROM
EMPLOYEE";
private static final String UPDATE_QUERY="UPDATE EMPLOYEE SET
EMP_SALARY=? WHERE EMP_ID=?";
public static void main(String[] args)throws Exception {
Class.forName("com.mysql.cj.jdbc.Driver");
Connection
con=DriverManager.getConnection(DB_URL,DB_UNAME,DB_PWD);
Scanner sc=new Scanner(System.in);
System.out.print("Enter Salary Hike Percentage:");
int hike_per=sc.nextInt();

Statement stmt=con.createStatement();
ResultSet rs=stmt.executeQuery(SELECT_QUERY);
PreparedStatement pstmt=con.prepareStatement(UPDATE_QUERY);
while(rs.next())
{
int salary=rs.getInt("EMP_SALARY");
int newSalary=salary+(salary*hike_per/100);
int empid=rs.getInt("EMP_ID");
pstmt.setInt(1,newSalary);
pstmt.setInt(2,empid);
pstmt.executeUpdate();
}
System.out.println("Salary Hike Processed");
con.close();
}
}
Note:
✓ The Above Code will Decrease Performance of the application
because our java application interacts with the database for
each row of the database table

Education is the most powerful weapon, which you can use to change the world.
-Nelson Mandela
[19]
File Name: MYSQL_EMP_Salary_Hike2.java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;
public class MYSQL_EMP_Salary_Hike2 {

private static final String

DB_URL="jdbc:mysql://localhost:3306/mydb";
private static final String DB_UNAME="root";
private static final String DB_PWD="root";
private static final String UPDATE_QUERY="UPDATE EMPLOYEE SET
EMP_SALARY=EMP_SALARY + (EMP_SALARY * ? / 100)";

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

Class.forName("com.mysql.cj.jdbc.Driver");
Connection
con=DriverManager.getConnection(DB_URL,DB_UNAME,DB_PWD);
Scanner sc=new Scanner(System.in);

System.out.print("Enter Salary Hike Percentage:");

int hike_per=sc.nextInt();

PreparedStatement pstmt=con.prepareStatement(UPDATE_QUERY);
pstmt.setInt(1, hike_per);

int count=pstmt.executeUpdate();
System.out.println("No of Records Updated:"+count);
con.close();
}

Note: In the above provided code, all employee salaries are updated at
once due to the execution of business logic at the database level

Education is the most powerful weapon, which you can use to change the world.
-Nelson Mandela
[20]
Assignment:
Develop Java JDBC Application to Increase the Salary of the Employees based on
department, read hike percentage for each department from keyboard and
update the salary with the given percentage.

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

public class MYSQL_EMP_Salary_Hike3 {

private static final String
DB_URL="jdbc:mysql://localhost:3306/mydb";
private static final String DB_UNAME="root";
private static final String DB_PWD="root";
private static final String UPDATE_QUERY="UPDATE EMPLOYEE SET
EMP_SALARY=EMP_SALARY + (EMP_SALARY * ? / 100) WHERE EMP_DEPT = ?";

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

Class.forName("com.mysql.cj.jdbc.Driver");
Connection
con=DriverManager.getConnection(DB_URL,DB_UNAME,DB_PWD);
Scanner sc=new Scanner(System.in);

System.out.print("Enter Salary Hike Percentage for ADMIN

Department:");
int adminDeptHike=sc.nextInt();
System.out.print("Enter Salary Hike Percentage for SALES
Department:");
int salesDeptHike=sc.nextInt();
System.out.print("Enter Salary Hike Percentage for
DEVELOPER Department:");
int devDeptHike=sc.nextInt();
System.out.print("Enter Salary Hike Percentage for ACCOUNTS
Department:");
int accDeptHike=sc.nextInt();

PreparedStatement pstmt=con.prepareStatement(UPDATE_QUERY);

pstmt.setInt(1, adminDeptHike);
pstmt.setString(2, "ADMIN");
pstmt.executeUpdate();

Education is the most powerful weapon, which you can use to change the world.
-Nelson Mandela
[21]
 pstmt.setInt(1, salesDeptHike);
pstmt.setString(2, "SALES");
pstmt.executeUpdate();

pstmt.setInt(1, devDeptHike);
pstmt.setString(2, "DEVELOPER");
pstmt.executeUpdate();

pstmt.setInt(1, accDeptHike);
pstmt.setString(2, "ACCOUNTS");
pstmt.executeUpdate();

System.out.println("Salary Hike As Per Department

Processed");

con.close();
}
}

Working with CallableStatements:

✓ The program determines salary adjustments for each department,
requiring a separate update query for each.
✓ With 500 departments, executing 500 update queries in our Java
application is not recommended as it may slow down performance.
✓ Making multiple database calls from Java can increase network traffic and
harm the overall application performance.
✓ To tackle this, it's better to write the business logic at the database level
using procedures.
✓ Procedures act like database programs, containing sets of SQL statements
for different operations.
✓ Writing procedures in the database helps significantly reduce the number
of calls between Java and the database.
✓ To execute these procedures, we utilize CallableStatements in our Java
application, ensuring efficient operation execution at the database level.

Procedures:
✓ Procedures are similar to Java methods; they also have names just like
methods.
✓ Procedures also can take input and return values like methods in java, but
its optional.

Education is the most powerful weapon, which you can use to change the world.
-Nelson Mandela
[22]
Syntax for Creating Procedure:

CREATE PROCEDURE <Procedure_Name>(Parameters..)

BEGIN
//SQL Statements
END;

Procedure can have 3 types of parameters

1. IN Parameter – Represents Input
2. OUT Parameter – Represents Output
3. INOUT Parameter – Represents Input & Output

Procedure to display all employee Records:

DELIMITER $$
create procedure getEmployeeData()
begin
select * from employee;
end $$
commit;

Command to execute the Procedure from workebnch:

CALL getEmployeeData();

Command to display the all Proceudre in MySQL Database:

show procedure status;

File Name: MYSQL_CallableStmtDemo.java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.CallableStatement;

public class MYSQL_CallableStmtDemo {

private static final String
DB_URL="jdbc:mysql://localhost:3306/mydb";
private static final String DB_UNAME="root";
private static final String DB_PWD="root";
private static final String PROCEDURE="CALL getEmployeeData()";

Education is the most powerful weapon, which you can use to change the world.
-Nelson Mandela
[23]
 public static void main(String[] args)throws Exception {
Connection
con=DriverManager.getConnection(DB_URL,DB_UNAME,DB_PWD);

CallableStatement cstmt=con.prepareCall(PROCEDURE);

ResultSet rs=cstmt.executeQuery();

while(rs.next())
{
System.out.println(rs.getInt(1)+" "+rs.getString(2)+"
"+rs.getInt(3)+" "+rs.getString(4)+" "+rs.getString(5)+"
"+rs.getString(6));
}
}
}
Note: Loading of Driver class using Class.forName() is optional from JDBC 4.0
onrwards.

Create a Procedure which takes input parameter:

DELIMITER $$
CREATE PROCEDURE getEmployeeById(IN ID INT)
BEGIN
SELECT * FROM EMPLOYEE WHERE EMP_ID=id;
END $$

Calling the Procedure:

CALL getEmployeeById(101);

Education is the most powerful weapon, which you can use to change the world.
-Nelson Mandela
[24]
File Name: CallableStmtDemo2.java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.CallableStatement;
import java.util.Scanner;
public class MYSQL_CallableStmtDemo2 {
private static final String
DB_URL="jdbc:mysql://localhost:3306/mydb";
private static final String DB_UNAME="root";
private static final String DB_PWD="root";
private static final String PROCEDURE="CALL getEmployeebyId(?)";
public static void main(String[] args)throws Exception {

Connection
con=DriverManager.getConnection(DB_URL,DB_UNAME,DB_PWD);
Scanner sc=new Scanner(System.in);
CallableStatement cstmt=con.prepareCall(PROCEDURE);

System.out.print("Enter Employee ID:");

int eid=sc.nextInt();

cstmt.setInt(1,eid);
ResultSet rs=cstmt.executeQuery();

if(!rs.next())
System.out.println("Record not Found");

while(rs.next())
{
System.out.println(rs.getInt(1)+" "+rs.getString(2)+"
"+rs.getInt(3)+" "+rs.getString(4)+" "+rs.getString(5)+"
"+rs.getString(6));
}
con.close();
}
}

Education is the most powerful weapon, which you can use to change the world.
-Nelson Mandela
[25]
Create a Procedure which takes input & output parameter:
DELIMITER $$
CREATE PROCEDURE getEmployeeNameBySalary(IN SAL INT,OUT NAME
varchar(20))
BEGIN
SELECT EMP_NAME FROM EMPLOYEE WHERE EMP_SALARY>=SAL;
END $$

Calling the Procedure:

CALL getEmployeeNameBySalary(150000,@ename);
Note:
@indiates output parameter and we can provide any name.
File name: MYSQL_CallableStmtDemo3.java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Types;
import java.sql.CallableStatement;
import java.util.Scanner;
public class MYSQL_CallableStmtDemo3 {
private static final String DB_URL="jdbc:mysql://localhost:3306/mydb";
private static final String DB_UNAME="root";
private static final String DB_PWD="root";
private static final String PROCEDURE="CALL
getEmployeeNamebySalary(?,?)";
public static void main(String[] args)throws Exception {

Connection
con=DriverManager.getConnection(DB_URL,DB_UNAME,DB_PWD);
Scanner sc=new Scanner(System.in);

CallableStatement cstmt=con.prepareCall(PROCEDURE);
System.out.print("Enter Employee Salary:");
int esal=sc.nextInt();

cstmt.setInt(1,esal);
cstmt.registerOutParameter(2, Types.VARCHAR);
ResultSet rs=cstmt.executeQuery();

Education is the most powerful weapon, which you can use to change the world.
-Nelson Mandela
[26]
 if(!rs.next())
System.out.println("Record not Found");

while(rs.next())
{
System.out.println(rs.getString(1));
}
con.close();
}
}

Assignments:
1. Write a JDBC Program to Insert name and image into a database table.
2. Program to Execute SQL Query with IN Clause.
a. select * from employee where emp_location IN(‘HYD’,’PUNE’);
3. JDBC Program to Retrieve Employees Who joined between given dates.
a. select * from employee where join_Date between ? and ?

Working with blob datatype in MySQL:

create table person(
pid int,
pname varchar(20),
pimage blob);

blob: Binary Large Object.

FileName: MYSQL_InsertImage.java
import java.io.File;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class MYSQL_InsertImage {

private static final String
DB_URL="jdbc:mysql://localhost:3306/mydb";
private static final String DB_UNAME="root";
private static final String DB_PWD="root";

Education is the most powerful weapon, which you can use to change the world.
-Nelson Mandela
[27]
 private static final String INSERT_QUERY="INSERT INTO PERSON
VALUES(?,?,?)";

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

File f=new File("C:\\Users\\Dell\\Downloads\\atish.jpg");
FileInputStream fis=new FileInputStream(f);
Connection
con=DriverManager.getConnection(DB_URL,DB_UNAME,DB_PWD);

PreparedStatement pstmt=con.prepareStatement(INSERT_QUERY);

pstmt.setInt(1, 101);
pstmt.setString(2, "Atish Jain");
pstmt.setBlob(3,fis);

int count=pstmt.executeUpdate();
System.out.println("No of Records Inserted:"+count);

fis.close();
pstmt.close();
con.close();
}
}

Program to Retrieve Image from the Database Table:

FileName: MYSQL_RetrieveImage.java

import java.io.File;
import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class MYSQL_RetrieveImage {

private static final String
DB_URL="jdbc:mysql://localhost:3306/mydb";
private static final String DB_UNAME="root";
private static final String DB_PWD="root";
private static final String SELECT_QUERY="SELECT * FROM PERSON";

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

Connection
con=DriverManager.getConnection(DB_URL,DB_UNAME,DB_PWD);
Statement stmt=con.createStatement();
ResultSet rs=stmt.executeQuery(SELECT_QUERY);
Education is the most powerful weapon, which you can use to change the world.
-Nelson Mandela
[28]
 if(rs.next())
{
System.out.println(rs.getInt(1)+" "+rs.getString(2));
byte pimage[]=rs.getBytes(3);
File f=new File("e:\\user\\jain.jpg");
FileOutputStream fout=new FileOutputStream(f);
fout.write(pimage);
System.out.println("Image is Written to File..");
fout.close();
}
rs.close();
stmt.close();
con.close();
}
}

JDBC Batch Operations/Batch Updates:

When we want to perform bulk operations in database, then we cann use jdbc
batch operations.
Batch Means Bulk Operation.
Ex: inserting 1000 records into database

Approach 1: Approach 2:

Approach 1: 1
INSERT Query Query 1
executeBatch()

INSERT Query 2 Query 2

INSERT Query 3 DB Query 3

…..
DB
….. …..
…..

Q1 Q2 Q3

✓ In Approach 1 we are executing sql queries individually so for every query

executin java application will communicate with database, it will decrease
performance of the application.
✓ In Approach 2 we are adding all the sql queries to batch object and we will
execute the batch at a time.

Education is the most powerful weapon, which you can use to change the world.
-Nelson Mandela
[29]
File Name: MySQL_BatchUpdates.java

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

public class MYSQL_BatchUpdates {

private static final String
DB_URL="jdbc:mysql://localhost:3306/mydb";
private static final String DB_UNAME="root";
private static final String DB_PWD="root";

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

Connection
con=DriverManager.getConnection(DB_URL,DB_UNAME,DB_PWD);

Statement stmt=con.createStatement();
stmt.addBatch("INSERT INTO STUDENT
VALUES(112,'Kumar',56)");
stmt.addBatch("INSERT INTO STUDENT VALUES(113,'Rani',77)");
int count[]=stmt.executeBatch();
System.out.println("No of Records Inserted:"+count.length);
con.close();
}
}

Note:
IMPS – Immediate Payment Transfer, it executes the transaction
immediately(executing individual sql queries)

NEFT – National Electroinics Funds Transfer, if executes the transactions in

batches(Batch Updates)

✓ In Batch Operations if one query fails then all the remaining queries also
will fail.
✓ In Batch Operations we can use only non-select
operations(insert,update,delete)

Assignment:
✓ Insert 3 records into student table using batch operations using
preparedStatement.

Education is the most powerful weapon, which you can use to change the world.
-Nelson Mandela
[30]
Stroing Data in Multiple Tables:
✓ Program to Read Employee & Address data from keyboard and insert into
respective DB Tables.
o Read Complete Employee Details in one form and store data in
respective tables.
o Employee Table: emp_id,emp_name,emp_salary
o Address Table: city,state,country
Employee Registration

Employee Id

Employee Name

Employee Salary

City State Country

Submit

Code to Create the Tables:

create table emp(
emp_id int,
emp_name varchar(20),
emp_salary int);

create table emp_address(

emp_id int,
city varchar(20),
state varchar(20),
country varchar(20));

File Name: MYSQL_MultiTalble_Insert.java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class MYSQL_MultiTable_Insert {

private static final String
DB_URL="jdbc:mysql://localhost:3306/mydb";
private static final String DB_UNAME="root";
private static final String DB_PWD="root";

Education is the most powerful weapon, which you can use to change the world.
-Nelson Mandela
[31]
 private static final String EMP_INSERT="INSERT INTO EMP
VALUES(?,?,?)";
private static final String ADDR_INSERT="INSERT INTO EMP_ADDRESS
VALUES(?,?,?,?)";

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

Connection
con=DriverManager.getConnection(DB_URL,DB_UNAME,DB_PWD);

PreparedStatement pstmt=con.prepareStatement(EMP_INSERT);
pstmt.setInt(1, 1001);
pstmt.setString(2,"Jack");
pstmt.setInt(3, 5000);

pstmt.executeUpdate();

pstmt=con.prepareStatement(ADDR_INSERT);
pstmt.setInt(1, 1001);
pstmt.setString(2, "Hyderabad");
pstmt.setString(3, "TELANGANA");
pstmt.setString(4, "INDIA");

pstmt.executeUpdate();

con.close();

System.out.println("Records Inserted...");
}
}

Education is the most powerful weapon, which you can use to change the world.
-Nelson Mandela
[32]
ACID Properties:
ACID properties in JDBC ensure that database transactions are Atomic (either all
the changes are committed or none), Consistent (maintaining integrity), Isolated
(executed independently), and Durable (permanent despite failures).

Transaction Management:
✓ A transaction is a single unit of work, enabling the execution of multiple
queries.
✓ Every transaction must follow ACID properties: Atomicity, Consistency,
Isolation, and Durability.
✓ By default, Auto Commit is set to True in JDBC.
✓ Transaction Management is mandatory for non-select operations (insert,
update, delete).
✓ For select operations, transaction management is optional.
✓ In a transaction with multiple operations, either all operations succeed, or
none do.
✓ Transaction Commit is used to save the operations.
✓ Transaction Rollback is utilized to undo the operations

Note:
By default in JDBC, all transactions are automatically committed for every query
execution since the default behavior of the Connection object sets auto-commit
to true (e.g., con.setAutoCommit(true)).
✓ To manually manage transactions in JDBC, one needs to set auto-commit
to false explicitly (e.g., con.setAutoCommit(false)).
✓ When auto-commit is set to false in JDBC, transactions need to be
committed programmatically to persist the operations in the database. For
instance, by using con.commit().

To delete the tables data permentely from MYSQ:

truncate emp;
truncate emp_address;

Education is the most powerful weapon, which you can use to change the world.
-Nelson Mandela
[33]
code snippet:
Connection con=DriverManager.getConnection(DB_URL,DB_UNAME,DB_PWD);
con.setAutoCommit(false);

try{
//JDBC Code to Execute Queries
con.commit();
}
catch(Exception e)
{
con.rollback();
}

Example:
The above Employee and Employee Address Example using Transaction
Management.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class MYSQL_MultiTable_Insert {

private static final String
DB_URL="jdbc:mysql://localhost:3306/mydb";
private static final String DB_UNAME="root";
private static final String DB_PWD="root";
private static final String EMP_INSERT="INSERT INTO EMP
VALUES(?,?,?)";
private static final String ADDR_INSERT="INSERT INTO EMP_ADDRESS
VALUES(?,?,?,?)";

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

Connection
con=DriverManager.getConnection(DB_URL,DB_UNAME,DB_PWD);
con.setAutoCommit(false);
try {
PreparedStatement pstmt=con.prepareStatement(EMP_INSERT);
pstmt.setInt(1, 1001);
pstmt.setString(2,"Jack");
pstmt.setInt(3, 5000);
pstmt.executeUpdate();

pstmt=con.prepareStatement(ADDR_INSERT);

Education is the most powerful weapon, which you can use to change the world.
-Nelson Mandela
[34]
 pstmt.setInt(1, 1001);
pstmt.setString(2, "Hyderabad");
pstmt.setString(3, "TELANGANA");
pstmt.setString(4, "INDIA");
pstmt.executeUpdate();
con.commit();
System.out.println("Records Inserted...");
}
catch(Exception e)
{
con.rollback();
System.out.println("Transaction Rolled Back....");
}
con.close();
}
}

Assignment:
Develop JDBC Application to Read Employee ID from the Keyboard and retrieve
Employee Data along with Address.

Example: To Display one row based on Employee id

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class MYSQL_MultiTable_Select {

private static final String
DB_URL="jdbc:mysql://localhost:3306/mydb";
private static final String DB_UNAME="root";
private static final String DB_PWD="root";
private static final String SELECT_QUERY="SELECT * FROM EMP
E,EMP_ADDRESS A WHERE E.EMP_ID=A.EMP_ID AND E.EMP_ID=?;";

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

Connection
con=DriverManager.getConnection(DB_URL,DB_UNAME,DB_PWD);

PreparedStatement pstmt=con.prepareStatement(SELECT_QUERY);
pstmt.setInt(1, 1001);
ResultSet rs=pstmt.executeQuery();

Education is the most powerful weapon, which you can use to change the world.
-Nelson Mandela
[35]
 while(rs.next())
{
System.out.println(rs.getInt(1)+" "+rs.getString(2)+"
"+rs.getInt(3)+" "+rs.getString(5)+" "+rs.getString(6)+"
"+rs.getString(7));
}
con.close();
}
}

Example: Program to Retrieve all the records from both the tables
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.ResultSet;

public class MYSQL_MultiTable_SelectAll {

private static final String
DB_URL="jdbc:mysql://localhost:3306/mydb";
private static final String DB_UNAME="root";
private static final String DB_PWD="root";
private static final String SELECT_QUERY="SELECT * FROM EMP
E,EMP_ADDRESS A WHERE E.EMP_ID=A.EMP_ID;";

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

Connection
con=DriverManager.getConnection(DB_URL,DB_UNAME,DB_PWD);
Statement stmt=con.createStatement();
ResultSet rs=stmt.executeQuery(SELECT_QUERY);
while(rs.next())
{
System.out.println(rs.getInt(1)+" "+rs.getString(2)+"
"+rs.getInt(3)+" "+rs.getString(5)+" "+rs.getString(6)+"
"+rs.getString(7));
}
con.close();
}
}

Education is the most powerful weapon, which you can use to change the world.
-Nelson Mandela
[36]
Connection Pooling:
Connection Pooling a mechanism to resue the recourses.

Resource Pool
(Bench)
Project 1

Project 2

Project 3
Resourse Manager(HR)

✓ Connection pooling involves obtaining and storing a fixed number of

database connections for reusability, preventing connection exhaustion
issues in projects. Instead of using DriverManager.getConnection(), which
provides physical connections, it's recommended to set up connection
pooling for real-time projects.
✓ This approach utilizes logical connections, improving performance,
resource efficiency, and scalability in web and enterprise applications.
✓ Connection Pool means Data Source

Education is the most powerful weapon, which you can use to change the world.
-Nelson Mandela
[37]
How to Setup Connection Pool:
1. Client Side Connection Pool
a. DBCP, C3P0, Hikari etc
2. Server Managed Connection Pool
a. Tomcat, WebLogic, JBoss etc

URL to Download HikariCP and Dependent Jar files:

https://jar-download.com/artifacts/com.zaxxer/HikariCP/3.4.5/source-code

Example:
Project Name: HikariCP_JDBC_App
File Name: ConnectionFactory.java

Add 3 jar files to build path.

1. Hikarcp-3.4.5.jar
2. Mysql-connector-j-8.3.0.jar
3. sl4j-api-1.7.25.jar

Code:
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import java.sql.*;

public class ConnectionFactory {

private static final String

DB_URL="jdbc:mysql://localhost:3306/mydb";

private static final String DB_UNAME="root";

private static final String DB_PWD="root";

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

{
HikariConfig config=new HikariConfig();
config.setJdbcUrl(DB_URL);
config.setUsername(DB_UNAME);
config.setPassword(DB_PWD);

config.setMaximumPoolSize(10);
config.setMinimumIdle(5);

HikariDataSource datasource=new HikariDataSource(config);

Education is the most powerful weapon, which you can use to change the world.
-Nelson Mandela
[38]
 Connection con=datasource.getConnection();

if(con!=null)
{
System.out.println("Got Connection from pool");
System.out.println(con);
}
Statement st=con.createStatement();
String sql="insert into student values(114,'Muskan',88)";
int count=st.executeUpdate(sql);
System.out.println("No of Records inserted:"+count);
con.close();
}
}

Education is the most powerful weapon, which you can use to change the world.
-Nelson Mandela
[39]
Database Properties Configuration:
Hard Coding: Assigning value to a variable in the program.

Working with Properties file:

✓ As of now in JDBC Programs we have declared Database Properties in .java
which is not recommended, because if database properties are modified
then we have to modify java programs.
✓ In real time projects our project has to communite with multiple databases
as below.
o Dev DB: Developers Database
o SIT DB: System Integration Database for Testing
o UAT DB: User Access Testing
o PROD DB: Production DB
✓ Every database will have different credentials, so when we want to change
the Database then we have to do modifications in our java programs which
is not recommended.
✓ We need to sperate Java programs with Database properties using
Properties file.
✓ Properties file is used to confiure properties in the form of key-values
pairs.
✓ File name can be anything but the extension should be .propertiles only
o load(FileInputStream obj) – to load all properties into Properties
Object
o getProperty(String key) – to load property value based on key
o setProperty(String key,String value) – to set new property value

Developer
DB

System
Java Application Integration
DB

Production
db.properties DB
…………

Education is the most powerful weapon, which you can use to change the world.
-Nelson Mandela
[40]
Project Name: JDBC_Properties_Example
Properties File name: db.properties.txt
Java Class File Name: ReadProperties.java

db.properties.txt
db.url = jdbc:mysql://localhost:3306/mydb
db.username = root
db.password = root

RadProperties.java
package in.atishjain;

import java.io.File;
import java.io.FileInputStream;
import java.util.Properties;

public class ReadProperties {

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

File f=new File("db.properties");

FileInputStream fis=new FileInputStream(f);
Properties p=new Properties();
p.load(fis);

String url=p.getProperty("db.url");
String un=p.getProperty("db.username");
String pwd=p.getProperty("db.password");

System.out.println("URL:"+url);
System.out.println("User Name:"+un);
System.out.println("User Name:"+pwd);

p.clear();
fis.close();
}

Education is the most powerful weapon, which you can use to change the world.
-Nelson Mandela
[41]
Java JDBC Application as per Industry Standards:
Project Name: JDBC_Properties_Example
Add 3 jar files to build path.
1. Hikarcp-3.4.5.jar
2. Mysql-connector-j-8.3.0.jar
3. sl4j-api-1.7.25.jar

db.properties
db.url = jdbc:mysql://localhost:3306/mydb
db.username = root
db.password = root
db.poolSize = 20

ConnectionFactory.java
package in.atishjain;

import java.io.File;
import java.io.FileInputStream;
import java.sql.Connection;
import java.util.Properties;

import javax.sql.DataSource;

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

public class ConnectionFactory {

private static DataSource dataSource=null;

static
{
try {
File f=new File("db.properties");
FileInputStream fis=new FileInputStream(f);
Properties p=new Properties();
p.load(fis);

String url=p.getProperty("db.url");
String un=p.getProperty("db.username");
String pwd=p.getProperty("db.password");
String poolSize=p.getProperty("db.poolSize");

HikariConfig config=new HikariConfig();

config.setJdbcUrl(url);

Education is the most powerful weapon, which you can use to change the world.
-Nelson Mandela
[42]
 config.setUsername(un);
config.setPassword(pwd);
config.setMaximumPoolSize(Integer.parseInt(poolSize));

dataSource=new HikariDataSource(config);
}
catch(Exception e)
{
e.printStackTrace();
}
}
public static Connection getConnection()throws Exception
{
return dataSource.getConnection();
}
}

ConnectionDemo.java
package in.atishjain;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

public class ConnectionDemo {

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

Connection con=ConnectionFactory.getConnection();

if(con!=null)
System.out.println("Connection Created....");

Statement st=con.createStatement();

ResultSet rs=st.executeQuery("Select * from Student");

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

rs.close();
st.close();
con.close();
}
}

Education is the most powerful weapon, which you can use to change the world.
-Nelson Mandela
[43]
Maven & Gradle:
Maven and Gradle are both build automation tools commonly used in the
software development process to manage and build projects. They help organize
and handle dependencies, compile source code, run tests, and package the final
software.
Maven:
✓ Purpose: Maven is a build tool and project management tool used for Java
projects.
✓ Configuration: Maven uses XML files (pom.xml) to define project settings,
dependencies, and build instructions.
✓ Convention over Configuration: Maven follows a convention-based
approach, which means that it enforces a standard project structure.
Developers need to adhere to this structure to benefit from Maven's
automatic configurations.
✓ Dependency Management: Maven simplifies the process of managing
project dependencies. Dependencies are declared in the project's pom.xml
file, and Maven automatically downloads and includes them in the project.
Gradle:
✓ Purpose: Gradle is a build automation tool used for various types of
projects, including Java, Android, and more.
✓ Configuration: Gradle uses a Groovy or Kotlin-based DSL (Domain Specific
Language) for build scripts. This allows for more flexibility and
expressiveness in defining build logic.
✓ Flexibility: Gradle is known for its flexibility. It supports both imperative
and declarative build scripting, making it suitable for a wide range of
projects. Developers can write custom scripts to define build tasks.
✓ Incremental Builds: Gradle is optimized for incremental builds, meaning it
only builds the parts of the project that have changed, which can
significantly speed up the build process.
Simplified Comparison:
✓ Maven: Follows conventions, uses XML for configuration, and is focused
on simplicity and standardization.
✓ Gradle: Offers flexibility, supports Groovy or Kotlin DSL, and is known for
its powerful and expressive build scripts.
In summary, both Maven and Gradle are popular build tools, and the choice
between them often depends on factors such as project requirements, team
preferences, and the need for flexibility in build scripting.

Education is the most powerful weapon, which you can use to change the world.
-Nelson Mandela
[44]
JdbcRowSet:
✓ The JDBCRowSet is part of the Java Database Connectivity (JDBC) API and
is an extension of the ResultSet interface.
✓ It provides a disconnected and scrollable result set, allowing for more
flexibility and ease of use compared to the traditional ResultSet.
✓ The key feature of a JDBCRowSet is that it can operate without
maintaining a live connection to the database, making it suitable for use in
distributed applications or when a continuous database connection is not
desirable.

Some key characteristics of JDBCRowSet include:

✓ Disconnected Operation: JDBCRowSet allows fetching data from the
database and then disconnecting. You can close the connection and still
work with the data in your application.
✓ Serializable: It implements the Serializable interface, making it easy to
serialize and transfer across the network.
✓ Scrollable and Updatable: Similar to ResultSet, JDBCRowSet enables both
forward and backward scrolling through the data. Additionally, it supports
updating the data.
✓ Event Handling: JDBCRowSet supports event handling, allowing
applications to register listeners for events like data changes or errors.

Example:
import javax.sql.rowset.JdbcRowSet;
import javax.sql.rowset.RowSetProvider;

public class MYSQL_JdbcRowSetDemo {

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

JdbcRowSet
rowSet=RowSetProvider.newFactory().createJdbcRowSet();

rowSet.setUrl("jdbc:mysql://localhost:3306/mydb");
rowSet.setUsername("root");
rowSet.setPassword("root");
rowSet.setCommand("select * from student");
rowSet.execute();

while(rowSet.next())

Education is the most powerful weapon, which you can use to change the world.
-Nelson Mandela
[45]
 System.out.println(rowSet.getInt(1)+"
"+rowSet.getString(2)+" "+rowSet.getInt(3));

rowSet.close();

}
}

Insert Operation using JDBCRowSet:

import javax.sql.rowset.JdbcRowSet;
import javax.sql.rowset.RowSetProvider;
public class MYSQL_JdbcRowSet_Insert {
public static void main(String[] args)throws Exception {
JdbcRowSet
rowSet=RowSetProvider.newFactory().createJdbcRowSet();
rowSet.setUrl("jdbc:mysql://localhost:3306/mydb");
rowSet.setUsername("root");
rowSet.setPassword("root");
showStudent(rowSet);
addStudent(rowSet);
}
public static void showStudent(JdbcRowSet rowSet)throws
Exception
{
rowSet.setCommand("select * from student");
rowSet.execute();

while(rowSet.next())
System.out.println(rowSet.getInt(1)+"
"+rowSet.getString(2)+" "+rowSet.getInt(3));

rowSet.beforeFirst();
System.out.println("----------------------");
}
public static void addStudent(JdbcRowSet rowSet)throws Exception
{
rowSet.moveToInsertRow();
rowSet.updateInt(1, 104);
rowSet.updateString(2, "Priya");
rowSet.updateInt(3, 88);
rowSet.insertRow();
rowSet.beforeFirst(); //sets the cursor to before first row
showStudent(rowSet);
}

Education is the most powerful weapon, which you can use to change the world.
-Nelson Mandela
[46]
JDBC Driver Types:
There are thousands of drivers are there in the market, all the JDBC drivers
drived into four types based on the functionality and architecture.
✓ Type 1 Driver
o JDBC-ODBC Bridge Driver/Bridge Driver
✓ Type 2 Driver
o Native API-Partly Java Driver/Native Driver
✓ Type 3 Driver
o All Java Net Protocol Driver/Network Protocal Driver/Middleware
Driver
✓ Type 4 Driver
o Pure Java Driver/Native Protocol Driver/thin Driver

Note: Type 1, Type 2 and Type 3 are Outdated.

Type 1 Driver:
✓ JDBC-ODBC Bridge Driver.
✓ Provided by SUN as part of JDK.
✓ This Driver Converts JDBC calls into ODBC calls.
✓ ODBC Driver converts ODBC Calls into Database specific calls & vice versa.
✓ Type 1 acts as bridge between JDBC and ODBC.

JAVA Type 1 ODBC

Application Driver Driver
(JDBC) DATABASE

Advantages:
1. No need to install separately.
2. Very easy to use.
3. Database independent driver.
4. Migratation from one database to another database is very easy.
Limitations:
✓ Performance is slow.
✓ Slowest driver among all the Drivers.
✓ Platform dependent Driver – ODBC is available only on windows machine.
✓ Support is available upto Java 7 only.

Education is the most powerful weapon, which you can use to change the world.
-Nelson Mandela
[47]
Difference between ODBC & JDBC:
ODBC JDBC
Introduced by Microsoft 1992 Introduced by SUN Microsystem in
1997
Can be used with any language like Can be used with JAVA Langauge only
VB, PB, D2K
Can used on Windows Platform Can be used on Any platform
Drivers are developed in Native Developed in JAVA
languages like C/C++
Not Recommended for JAVA, Because Highly Recommended for JAVA
performance will be low and applications because there is no
application will become platform performance and platform dependent
dependent. problems.

Type 2 Driver:
✓ Partly Java Native API
✓ Type2 Driver exactly same as Type1 Except tat ODBC Driver is replaced with
database vendor specific native libraries.
✓ Native API means Non java (Developed using C/C++)
✓ Type2 Driver converts JDBC calls into Database Specific Native library calls.
✓ These DB Specific Native libraries provided by Database Vendor.

Vendor
JAVA Application Type 2 Provided
(JDBC) Driver DB Specific
DATABASE
Native Library

Advantages:
✓ Performance is good when compared with Type 1
✓ No ODBC Support required.
✓ Portability is more
✓ Can be used for different machines using Separate Native Library for each
platform.

Limitations:
✓ Platform Dependent.
Education is the most powerful weapon, which you can use to change the world.
-Nelson Mandela
[48]
 ✓ Database Dependent driver.
✓ we must install install native libraries on client machine.
✓ Every database vendor may not provide native libraries.
✓ Oracle provide Native libraries in the name of OCI (Oracle Call interface).
✓ No library support for MySql, so no Type2 Driver Support for MySql.

OCI Driver:
✓ OCI Driver Provided by Oracle Corporation to support Type2 Driver, OCI
Libraries are nothing but C language function.
✓ OCI Driver and OCI Libraries are available in the form of jar files, so we
need to place that jar in the classpath.
o ojdbc14.jar → Oracle 10g ( Java 1.4)
o ojdbc6.jar → Oracle 11g (Java 6)
o ojdbc7.jar → Oracle 12c (Java 7)

Type 3 Driver:
✓ All Java Net Protocol Driver/Middleware driver.
✓ Java application communicates with Type3 Driver, Type3 driver converts
JDBC calls into middleware server specific calls and middleware server
communicates with Database and converts DB specific calls.
✓ Middleware server uses type1/type2/type4 to coummunicate with the
database.
✓ It follows 3 tier architecture.
✓ http://www.idssoftware.com/download.html to download IDS Server
✓ IDS Server runs on port no 12

JAVA Application Type 3 Middleware

Driver DATABASE
(JDBC) server
(Middle (IDS Server)

Advantages:
✓ Database independent driver.
✓ Platform independent driver.
✓ For multiple database in a project it is highly recommended driver.

Education is the most powerful weapon, which you can use to change the world.
-Nelson Mandela
[49]
Servers:
Proxy – IDS Server
Web Server – Tomcat
Application server – Weblogic/Glassfish

Type 3 Driver Example:

Driver : ids.sql.IDSDriver
Jdbc Url : jdbc:jdbc:ids://localhost:12/con?dsn=accessdsn
Set classpath for : C:\IDSServer\classes\jdk13drv.jar

import java.sql.*;
class JdbcType3
{
public static void main(String[] args)throws Exception
{
Class.forName("ids.sql.IDSDriver");
Connection
con=DriverManager.getConnection("jdbc:ids://localhost:12/conn?dsn=accessdsn
");

if(con==null)
System.out.println("No connection..");
else
System.out.println("Connection Created....");

Statement st=con.createStatement();
ResultSet rs=st.executeQuery("select * from student");
while(rs.next())
{
System.out.println(rs.getInt(2)+"\t"+rs.getString(3)+"\t"+rs.getInt(4));
}
}
}
Type 4 Driver:
✓ Pure Java Driver/All Java Native Protocol Driver/thin driver.
✓ This driver uses Database specific Native protocol to communicate with
the database.

Education is the most powerful weapon, which you can use to change the world.
-Nelson Mandela
[50]
 ✓ This Driver converts JDBC calls into database specific calls directly.
✓ This driver is developed in java, hence it is known as pure java driver and it
is platform independent driver.
✓ No ODBC, No Native Libraries, No Middleware server is required at client
side.

JAVA Application Type 4

(JDBC) Driver
DATABASE

Advantages:
✓ platform independent driver.
✓ Performance is very high.
✓ Direct communication with the database so security is more.
✓ Available for MySQL too(Connector/J).
Limitations:
✓ Database Dependent driver.

Education is the most powerful weapon, which you can use to change the world.
-Nelson Mandela
[51]