VIDYASHILP UNIVERSITY
Lab Worksheet 11
Object Oriented Programming
2nd April 2025
Objectives:
Database Connectivity
Check the Java Version: java -version
Check the Workbench version: mysql –version
Download the mysql connector: Download the MySQL Connector/J from MySQL JDBC Driver.
Place the jar file in the folder which as java program: mysql-connector-j-9.2.0.jar
Use MySql Workbench
create database jBank;
use jBank;
create table Account
(
Account_num varchar(12) primary key,
Account_type varchar(10),
Balance int
);
create table Person (
Aid varchar(16) primary key,
name varchar(20),
age int,
ph_num int,
email varchar(50)
);
create table Holds (
Account_num VARCHAR(12),
Aid VARCHAR(16),
PRIMARY KEY (Account_num, Aid),
FOREIGN KEY (Account_num) REFERENCES Account(Account_num),
FOREIGN KEY (Aid) REFERENCES Person(Aid)
);
INSERT INTO Account (Account_num, Account_type, Balance) VALUES
('A1001', 'Savings', 5000),
('A1002', 'Current', 12000),
('A1003', 'Savings', 8000),
('A1004', 'Current', 15000),
('A1005', 'Savings', 10000);
INSERT INTO Person (Aid, name, age, ph_num, email) VALUES
('P001', 'Hari Prasad', 25, 98765432, '
[email protected]'),
('P002', 'Bijoy', 30, 91234567, '
[email protected]'),
('P003', 'Chandra Shekar', 28, 99887766, '
[email protected]'),
('P004', 'Diivakar Reddy', 35, 91122334, '
[email protected]'),
('P005', 'Emma Davis', 22, 95566778, '
[email protected]'),
('P006', 'Frank S', 40, 90011223, '
[email protected]'),
('P007', 'Geetha Rathod', 27, 96677889, '
[email protected]'),
('P008', 'Hannumantha K', 29, 94455667, '
[email protected]');
INSERT INTO Holds (Account_num, Aid) VALUES
('A1001', 'P001'),
('A1002', 'P002'),
('A1003', 'P003'),
('A1004', 'P004');
SELECT Person.*, Holds.Account_num
FROM Person
INNER JOIN Holds ON Person.Aid = Holds.Aid;
SELECT
P.Aid,
P.name,
P.ph_num,
A.Account_type,
A.Balance
FROM Person P, Holds H, Account A
WHERE P.Aid = H.Aid
AND H.Account_num = A.Account_num;
Java Program
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class jBankDb {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/jBank";
String user = "root";
String password = "Your Database Password";
try {
Class.forName("com.mysql.cj.jdbc.Driver");
Connection conn = DriverManager.getConnection(url, user, password);
System.out.println("Connected to the database!");
Statement stmt = conn.createStatement();
String query = "SELECT P.Aid, P.name, P.ph_num, A.Account_type, A.Balance
FROM Person P, Holds H, Account A WHERE P.Aid = H.Aid AND H.Account_num
= A.Account_num";
ResultSet rs = stmt.executeQuery(query);
while (rs.next()) {
System.out.println("ID: " + rs.getString("Aid") + ", Name: " +
rs.getString("name") +", Ph Number " + rs.getInt("ph_num")+ ", Acc Type: " +
rs.getString("Account_type")+ ", Balance: " + rs.getInt("Balance"));
}
rs.close();
stmt.close();
conn.close();
} catch (ClassNotFoundException e) {
System.out.println("MySQL JDBC Driver not found. Include it in your
library path.");
e.printStackTrace();
} catch (SQLException e) {
System.out.println("Connection failed! Check output console");
e.printStackTrace();
}
}
}
Compilation
javac -cp ".;mysql-connector-j-9.2.0.jar" jBankDb.java
java -cp ".;mysql-connector-j-9.2.0.jar" jBankDb
javac -cp ".;mysql-connector-j-9.2.0.jar" jFullStack.java
Bank Manager Program
Insert & Update Operations
import java.sql.*;
import java.util.Scanner;
public class jBankManager {
private static final String DB_URL = "jdbc:mysql://localhost:3306/jBank";
private static final String USER = "root";
private static final String PASSWORD = "Enter your Database Password";
public static void main(String[] args) {
Scanner scanner = new Scanner(System.in);
try (Connection conn = DriverManager.getConnection(DB_URL, USER,
PASSWORD)) {
System.out.println("Connected to the database.");
while (true) {
System.out.println("\nChoose an operation:");
System.out.println("1. Insert into Account");
System.out.println("2. Update Account Balance");
System.out.println("3. Insert into Person");
System.out.println("4. Update Person Details");
System.out.println("5. Insert into Holds");
System.out.println("6. Exit");
System.out.print("Enter your choice: ");
int choice = scanner.nextInt();
switch (choice) {
case 1:
insertAccount(conn, scanner);
break;
case 2:
updateAccountBalance(conn, scanner);
break;
case 3:
insertPerson(conn, scanner);
break;
case 4:
updatePersonDetails(conn, scanner);
break;
case 5:
insertHolds(conn, scanner);
break;
case 6:
System.out.println("Exiting program...");
return;
default:
System.out.println("Invalid choice. Please try again.");
}
}
} catch (SQLException e) {
e.printStackTrace();
}
}
private static void insertAccount(Connection conn, Scanner scanner) throws
SQLException {
System.out.print("Enter Account Number: ");
String accountNum = scanner.next();
System.out.print("Enter Account Type: ");
String accountType = scanner.next();
System.out.print("Enter Balance: ");
int balance = scanner.nextInt();
String sql = "INSERT INTO Account (Account_num, Account_type) VALUES
(?, ?)";
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setString(1, accountNum);
pstmt.setString(2, accountType);
pstmt.setInt(3, balance);
pstmt.execute();
System.out.println("Account inserted successfully.");
}
}
private static void updateAccountBalance(Connection conn, Scanner
scanner) throws SQLException {
System.out.print("Enter Account Number: ");
String accountNum = scanner.next();
System.out.print("Enter New Balance: ");
int newBalance = scanner.nextInt();
String sql = "UPDATE Account SET Balance = ? WHERE Account_num = ?";
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setInt(1, newBalance);
pstmt.setString(2, accountNum);
int rowsUpdated = pstmt.executeUpdate();
if (rowsUpdated > 0) {
System.out.println("Account balance updated successfully.");
} else {
System.out.println("Account not found.");
}
}
}
private static void insertPerson(Connection conn, Scanner scanner) throws
SQLException {
System.out.print("Enter Person ID (Aid): ");
String aid = scanner.next();
System.out.print("Enter Name: ");
String name = scanner.next();
System.out.print("Enter Age: ");
int age = scanner.nextInt();
System.out.print("Enter Phone Number: ");
long phone = scanner.nextLong();
System.out.print("Enter Email: ");
String email = scanner.next();
String sql = "INSERT INTO Person (Aid, name, age, ph_num, email)
VALUES (?, ?, ?, ?, ?)";
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setString(1, aid);
pstmt.setString(2, name);
pstmt.setInt(3, age);
pstmt.setLong(4, phone);
pstmt.setString(5, email);
pstmt.executeUpdate();
System.out.println("Person inserted successfully.");
}
}
private static void updatePersonDetails(Connection conn, Scanner scanner)
throws SQLException {
System.out.print("Enter Person ID (Aid): ");
String aid = scanner.next();
System.out.print("Enter New Phone Number: ");
long newPhone = scanner.nextLong();
System.out.print("Enter New Email: ");
String newEmail = scanner.next();
String sql = "UPDATE Person SET ph_num = ?, email = ? WHERE Aid = ?";
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setLong(1, newPhone);
pstmt.setString(2, newEmail);
pstmt.setString(3, aid);
int rowsUpdated = pstmt.executeUpdate();
if (rowsUpdated > 0) {
System.out.println("Person details updated successfully.");
} else {
System.out.println("Person not found.");
}
}
}
private static void insertHolds(Connection conn, Scanner scanner) throws
SQLException {
System.out.print("Enter Account Number: ");
String accountNum = scanner.next();
System.out.print("Enter Person ID (Aid): ");
String aid = scanner.next();
String sql = "INSERT INTO Holds (Account_num, Aid) VALUES (?, ?)";
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setString(1, accountNum);
pstmt.setString(2, aid);
pstmt.executeUpdate();
System.out.println("Holds record inserted successfully.");
}
}
}
Compilation
javac -cp ".;mysql-connector-j-9.2.0.jar" jBankManager.java
java -cp ".;mysql-connector-j-9.2.0.jar" jBankManager
