java code for jdbc
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;
public class Main {
private static final String URL = "jdbc:derby:D:\\Users\\2725945\\MyDB;create=true";
private static final String DRIVERNAME = "org.apache.derby.jdbc.EmbeddedDriver";
private static Connection connection;
public static void main(String[] args) {
try {
// Establishing a connection
connection = DriverManager.getConnection(URL);
System.out.println("Connection established successfully.");
Scanner scanner = new Scanner(System.in);
System.out.println("Choose an option: ");
System.out.println("1. Register a new Customer");
System.out.println("2. Register a new Employee");
System.out.println("3. View Customer Details");
System.out.println("4. To delete a customer");
System.out.println("5. Update Customer");
int choice = scanner.nextInt();
scanner.nextLine(); // Consume newline
switch (choice) {
case 1:
insertCustomerDataDynamically(scanner);
break;
case 2:
insertEmployeeDataDynamically(scanner);
break;
case 3:
viewCustomerDetails(scanner);
break;
case 4:
deleteCustomerBySSNID(scanner);
break;
case 5:
updateCustomerDetailsBySSNID(scanner);
break;
default:
System.out.println("Invalid choice. Please enter 1, 2 or 3.");
break;
}
} catch (SQLException e) {
System.out.println("Connection failed. Error: " + e.getMessage());
} finally {
try {
if (connection != null && !connection.isClosed()) {
connection.close();
System.out.println("Connection closed.");
}
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
public static void insertCustomerDataDynamically(Scanner scanner) {
System.out.println("Enter Customer SSN ID:");
String ssn_id = scanner.nextLine();
System.out.println("Enter Customer Name:");
String name = scanner.nextLine();
System.out.println("Enter Customer Email:");
String email = scanner.nextLine();
System.out.println("Enter Customer Address:");
String address = scanner.nextLine();
System.out.println("Enter Customer Contact Number:");
String contactNumber = scanner.nextLine();
System.out.println("Enter Customer Aadhar Number:");
String aadharNumber = scanner.nextLine();
System.out.println("Enter Customer PAN Number:");
String panNumber = scanner.nextLine();
System.out.println("Enter Customer Account Number:");
String accountNumber = scanner.nextLine();
System.out.println("Enter Customer Account Balance:");
int accountBalance = scanner.nextInt();
String query = "INSERT INTO Customer (CustomerSSNID, Name, Email, Address, ContactNumber, AdhaarNumber, Pannumber, AccountNo, AccountBalance) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
try (PreparedStatement preparedStatement = connection.prepareStatement(query)) {
// Set the parameters for the prepared statement
preparedStatement.setString(1, ssn_id);
preparedStatement.setString(2, name);
preparedStatement.setString(3, email);
preparedStatement.setString(4, address);
preparedStatement.setString(5, contactNumber);
preparedStatement.setString(6, aadharNumber);
preparedStatement.setString(7, panNumber);
preparedStatement.setString(8, accountNumber);
preparedStatement.setInt(9, accountBalance);
// Execute the insert command
int rowsInserted = preparedStatement.executeUpdate();
if (rowsInserted > 0) {
System.out.println("A new customer was inserted successfully!");
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void insertEmployeeDataDynamically(Scanner scanner) {
System.out.println("Enter Employee ID:");
int empID = scanner.nextInt();scanner.nextLine();
System.out.println("Enter Employee Name:");
String name = scanner.nextLine();
System.out.println("Enter Employee Email:");
String email = scanner.nextLine();
System.out.println("Enter Employee Password:");
String password = scanner.nextLine();
System.out.println("Enter Employee Contact Number:");
int contact = scanner.nextInt();
String query = "INSERT INTO Employee (empid, empname, email, password, contact) VALUES (?, ?, ?, ?,?)";
try (PreparedStatement preparedStatement = connection.prepareStatement(query)) {
// Set the parameters for the prepared statement
preparedStatement.setInt(1, empID);
preparedStatement.setString(2, name);
preparedStatement.setString(3, email);
preparedStatement.setString(4, password);
preparedStatement.setInt(5, contact);
// Execute the insert command
int rowsInserted = preparedStatement.executeUpdate();
if (rowsInserted > 0) {
System.out.println("A new employee was inserted successfully!");
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void viewCustomerDetails(Scanner scanner) {
System.out.println("Enter customer SSN-ID:");
String ssnID=scanner.nextLine();
String query = "select * from Customer where CustomerSSNID = ?";
try (PreparedStatement preparedStatement = connection.prepareStatement(query)){
preparedStatement.setString(1, ssnID);
ResultSet resultSet = preparedStatement.executeQuery();
if(resultSet.next()) {
System.out.println("Customers Detaials: ");
System.out.println("SSN ID:"+ resultSet.getString("CustomerSSNID"));
System.out.println("Name:"+resultSet.getString("Name"));
System.out.println("Email:"+resultSet.getString("Email"));
System.out.println("Address:"+resultSet.getString("Address"));
System.out.println("Contact Number:"+resultSet.getString("ContactNumber"));
System.out.println("Aadhar Number:"+resultSet.getString("AdhaarNumber"));
System.out.println("Pan Number:"+resultSet.getString("Pannumber"));
System.out.println("Account Number:"+resultSet.getString("AccountNo"));
System.out.println("Account Balance :"+resultSet.getInt("AccountBalance"));
}
else {
System.out.println("Customer with SSN ID "+ssnID+"not found.");
}
}
catch(SQLException e){
e.printStackTrace();
}
}
public static void deleteCustomerBySSNID(Scanner scanner) {
System.out.println("Enter Customer SSN ID to delete:");
String ssn_id = scanner.nextLine();
// Check if the customer exists
String checkQuery = "SELECT * FROM Customer WHERE CustomerSSNID = ?";
try (PreparedStatement checkStatement = connection.prepareStatement(checkQuery)) {
checkStatement.setString(1, ssn_id);
ResultSet resultSet = checkStatement.executeQuery();
if (!resultSet.next()) {
System.out.println("Customer with SSN ID " + ssn_id + " not found.");
return;
}
} catch (SQLException e) {
e.printStackTrace();
return;
}
// If the customer exists, proceed with deletion
String deleteQuery = "DELETE FROM Customer WHERE CustomerSSNID = ?";
try (PreparedStatement preparedStatement = connection.prepareStatement(deleteQuery)) {
preparedStatement.setString(1, ssn_id);
// Execute the delete command
int rowsDeleted = preparedStatement.executeUpdate();
if (rowsDeleted > 0) {
System.out.println("Customer with SSN ID " + ssn_id + " was deleted successfully.");
} else {
System.out.println("Delete failed. Customer with SSN ID " + ssn_id + " not found.");
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void updateCustomerDetailsBySSNID(Scanner scanner) {
System.out.println("Enter Customer SSN ID to update:");
String ssn_id = scanner.nextLine();
// Check if the customer exists
String checkQuery = "SELECT * FROM Customer WHERE CustomerSSNID = ?";
try (PreparedStatement checkStatement = connection.prepareStatement(checkQuery)) {
checkStatement.setString(1, ssn_id);
ResultSet resultSet = checkStatement.executeQuery();
if (!resultSet.next()) {
System.out.println("Customer with SSN ID " + ssn_id + " not found.");
return;
}
} catch (SQLException e) {
e.printStackTrace();
return;
}
System.out.println("Enter new Customer Name:");
String name = scanner.nextLine();
System.out.println("Enter new Customer Email:");
String email = scanner.nextLine();
System.out.println("Enter new Customer Address:");
String address = scanner.nextLine();
System.out.println("Enter new Customer Contact Number:");
String contactNumber = scanner.nextLine();
System.out.println("Enter new Customer Aadhar Number:");
String aadharNumber = scanner.nextLine();
System.out.println("Enter new Customer PAN Number:");
String panNumber = scanner.nextLine();
System.out.println("Enter new Customer Account Number:");
String accountNumber = scanner.nextLine();
System.out.println("Enter new Customer Account Balance:");
int accountBalance = scanner.nextInt();
String updateQuery = "UPDATE Customer SET Name = ?, Email = ?, Address = ?, ContactNumber = ?, AdhaarNumber = ?, Pannumber = ?, AccountNo = ?, AccountBalance = ? WHERE CustomerSSNID = ?";
try (PreparedStatement preparedStatement = connection.prepareStatement(updateQuery)) {
// Set the parameters for the prepared statement
preparedStatement.setString(1, name);
preparedStatement.setString(2, email);
preparedStatement.setString(3, address);
preparedStatement.setString(4, contactNumber);
preparedStatement.setString(5, aadharNumber);
preparedStatement.setString(6, panNumber);
preparedStatement.setString(7, accountNumber);
preparedStatement.setInt(8, accountBalance);
preparedStatement.setString(9, ssn_id);
// Execute the update command
int rowsUpdated = preparedStatement.executeUpdate();
if (rowsUpdated > 0) {
System.out.println("Customer details updated successfully!");
} else {
System.out.println("Update failed. Customer with SSN ID " + ssn_id + " not found.");
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/*
CREATE TABLE Customer (
CustomerSSNID varchar(50) PRIMARY KEY,
Name varchar(50),
Email varchar(100),
Address varchar(100),
ContactNumber varchar(50),
AdhaarNumber varchar(50) ,
Pannumber varchar(10),
AccountNo varchar(50),
AccountBalance integer
);
select * from Customer
drop table Customer;
create table Employee(
empid integer,
empName varchar(50),
email varchar(100),
password varchar(20),
contact integer
);
select*from Employee
drop table Employee
*/