Java Database Connectivity
What is JDBC?
JDBC allows Java to connect with a Database.
Using JDBC we can:
Insert data
Update data
Delete data
Fetch data
Step 1: Install Requirements
1. Java (JDK installed)
2. MySQL Server installed
3. MySQL Workbench installed
4. MySQL Connector J (JDBC Drive)
After downloading:
Extract it
You will get . jar file (like mysql-connector-j -8. x. x. jar )
a
This is VERY IMPORTANT.
Step 2: Create Database in MySQL
Open MySQL Workbench and run:
CREATE DATABASE studentdb;
USE studentdb;
CREATE TABLE students ( id INT PRIMARY KEY, name VARCHAR(50), marks INT );`
Step 3: Add JDBC Driver to Java Project
Put the . jar file in your project folder.
Go to Java Projects in VS code Folders
In that navigate to Our folder name
Then in that select refrenced libraries and click + to add mysql connector jar
filre
Select the Jar file
Step 4: JDBC Basic Program Structure
There are 5 Important Steps in JDBC :
1. Import packages
2. Create connection
3. Create statement
4. Execute query
5. Close connection
1. Import The Packages :
import java. sql. *;
We Can Use the Following Under this :
Connection
Statement
ResultSet
DriverManager
2. Create The Connection :
Connection con = DriverManager.
getConnection( " jdbc: mysql: //localhost:
3306/yourDatabaseName", " root",
" password"
); // We Should give values for root and password
For root and password we should remember the password while Installation.
3. Create Statement :
Statement stmt = con. createStatement();
These are used for sending values to the Databases.
With this we can execute the sql query from Java vsc.
4. Execute Query :
ResultSet rs = stmt. executeQuery(" SELECT * FROM
students");
while(rs. next()) {
System. out. println(rs. getInt(1) + " " + rs.
getString(2));
}
The ResultSet is used for storing the result after the sql Query execution.
While we are not storing any values we just simply execute the Query.
stmt. executeUpdate(" INSERT INTO students VALUES(1, ' John' )");
6. Close The Connection :
It is always recommended to close the Connection To free the memory space.
con. close();
Some Of the Basic SQL Statements :
We can perform CRUD operations on to the Databases
Database and Table Creation :
CREATE DATABASE
studentdb; USE studentdb;
CREATE TABLE
students ( id INT
PRIMARY KEY,
name
VARCHAR(50),
marks INT
);
INSERT INTO students VALUES (1, ' John' , 85);
INSERT INTO students VALUES (2, ' Alice' ,
90); INSERT INTO students VALUES (3, '
David' , 78); SELECT * FROM students;
UPDATE students SET name=' Micheal' WHERE
1. Insert Into table :
INSERT INTO _table_name_ (_column1_, _column2_, _column3_, . . . )
VALUES (_value1_, _value2_, _value3_, . . . );
2. Reading From The Database :
SELECT _column1_, _column2, . . . _
FROM _table_name_;
3. Updating the Values in the Databases :
UPDATE _table_name_ SET _column1_ = _value1_, _column2_ = _value2_, .
. . WHERE _condition_; `
4. Deleting from The DataBases :
DELETE FROM _table_name_ WHERE _condition_;
Complete Example For Inserting Data
import java. sql.
*;
public class Main {
public static void main(String[]
args) {
String url = " jdbc: mysql: //localhost:
3306/studentdb";
String user = " root";
String password = " your_password";
try {
// 1. Create Connection
Connection con = DriverManager. getConnection(url,
user,
password);
// 2. Create Statement
Statement stmt = con.
createStatement();
// 3. Execute Query
String query = " INSERT INTO students VALUES (1, ' John' ,
85)"; stmt. executeUpdate(query);
System. out. println(" Data Inserted
Successfully");
// 4. Close
Connection
con. close();
} catch (Exception e) {
System. out. println(e);
}
}
}
Example For Fetching Data (SELECT)
Should no about the [Link]().
import java. sql.
*;
public class Main {
public static void main(String[]
args) {
String url = " jdbc: mysql: //localhost:
3306/studentdb";
String user = " root";
String password = " your_password";
try {
Connection con = DriverManager. getConnection(url,
user,
password);
Statement stmt = con.
createStatement();
ResultSet rs = stmt. executeQuery(" SELECT * FROM
students");
while (rs. next()) {
System. out. println(
rs. getInt(" id") + " " +
rs. getString(" name") + "
" + rs. getInt(" marks")
);
}
con. close();
} catch (Exception e) {
System. out. println(e);
}
}
}
Important JDBC Interfaces
Interface Purpose
DriverManager Incharge for Linking the java and
Mysql
Connection Connects to DB
Statement Executes SQL
PreparedStateme Executes parameterized SQL
nt
ResultSet Stores result
Flow Diagram (Very Important for Exams)
Java Program
↓
JDBC Driver
↓
Database
Complete CRUD Operation :
Here we are using PreparedStatements where it helps us to reuse it on a loop
instead of writing it every time manually. For example while inserting the
user might insert more than one rows, so we use PreparedStatements.
import java. sql. *;
import java. util. Scanner;
public class StudentCRUD {
static final String URL = " jdbc: mysql: //localhost:
3306/studentdb"; static final String USER = " root";
static final String PASSWORD = " password"; //
change this public static void main(String[] args) {
Scanner sc = new Scanner(System. in);
try {
Class. forName(" com. mysql. cj. jdbc. Driver");
Connection con = DriverManager. getConnection(URL, USER,
PASSWORD);
while (true) {
System. out. println("\n1.
Insert"); System. out. println(" 2.
Display"); System. out. println("
3. Update"); System. out.
println(" 4. Delete"); System.
out. println(" 5. Exit"); System.
out. print(" Enter choice: "); int ch
= sc. nextInt();
switch (ch) {
// 🔹
CREATE case
1:
System. out. print(" Enter ID: ");
int id = sc. nextInt();
sc. nextLine();
System. out. print(" Enter Name:
"); String name = sc. nextLine();
System. out. print(" Enter Marks:
"); int marks = sc. nextInt();
PreparedStatement ps1 = con.
prepareStatement( " INSERT INTO
students VALUES (?, ?, ?)");
ps1. setInt(1, id);
ps1. setString(2, name);
ps1. setInt(3, marks);
ps1. executeUpdate();
System. out. println(" Record Inserted Successfully!
"); break;
// 🔹
READ case
2:
students"); Statement stmt = con. createStatement();
ResultSet rs = stmt. executeQuery(" SELECT *
FROM
System. out. println("\nID Name
Marks");
while (rs. next()) {
System. out. println(
rs. getInt(" id") + " " +
rs. getString(" name") + "
"
+ rs. getInt(" marks"));
}
break;
// 🔹
UPDATE case
3:
System. out. print(" Enter ID to update:
"); int uid = sc. nextInt();
System. out. print(" Enter New Marks:
"); int newMarks = sc. nextInt();
PreparedStatement ps2 = con.
prepareStatement( " UPDATE students SET
marks=? WHERE id=?");
ps2. setInt(1, newMarks);
ps2. setInt(2, uid);
ps2. executeUpdate();
System. out. println(" Record Updated Successfully! ");
break;
// 🔹
DELETE case
4:
System. out. print(" Enter ID to delete: ");
int did = sc.
nextInt();
PreparedStatement ps3 = con.
prepareStatement(
" DELETE FROM students WHERE
id=?"); ps3. setInt(1, did);
ps3. executeUpdate();
System. out. println(" Record Deleted
Successfully! "); break;
case 5:
con. close();
System. out. println(" Connection Closed.
"); System. exit(0);
default:
System. out. println(" Invalid
} Choice! ");
}
} catch (Exception e) {
System. out. println(e);
}
}
}