Scribd
Upload
4 views29 pages

JDBC Student and Employee Management

The document outlines the creation of multiple JDBC applications that interact with a MySQL database to perform various operations on a Student and Employee table. It includes examples of using Statement and PreparedStatement objects for CRUD operations, as well as demonstrating stored procedures and scrollable/updatable ResultSets. Each section provides Java code snippets and SQL commands necessary for creating tables, inserting, updating, and deleting records, along with displaying results.

Uploaded by

vijaya_820432410
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
4 views29 pages

JDBC Student and Employee Management

The document outlines the creation of multiple JDBC applications that interact with a MySQL database to perform various operations on a Student and Employee table. It includes examples of using Statement and PreparedStatement objects for CRUD operations, as well as demonstrating stored procedures and scrollable/updatable ResultSets. Each section provides Java code snippets and SQL commands necessary for creating tables, inserting, updating, and deleting records, along with displaying results.

Uploaded by

vijaya_820432410
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

1.

Write a JDBC application which will


interact with Database and perform the
following task.
a.
b. Create Student Table with Roll No,
Name, and Address field and insert
few records.
[Link] Statement Object display the
content of Record.
d. Using Statement Object Insert
Two Record.
e. Using Statement Object Update
One Record.
f. Using Statement Object Delete One
Record.
g. Using Statement Object display
the content of Record.

[Link] [Link].*;

public class MySQLJDBCStudentApp {


public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
// Update these as per your MySQL
configuration
String url =
"jdbc:mysql://localhost:3306/college";
String user = "root";
String password = "yourpassword";

try {
// 1. Load the MySQL JDBC driver

[Link]("[Link]");

// 2. Connect to the database


conn =
[Link](url, user,
password);
stmt = [Link]();

// a. Create table if not exists and


insert initial records
String createTable = "CREATE TABLE
IF NOT EXISTS Student ("
+ "RollNo INT PRIMARY KEY, "
+ "Name VARCHAR(50), "
+ "Address VARCHAR(100))";
[Link](createTable);

// Clear old data


[Link]("DELETE FROM
Student");

String insertInitial = "INSERT INTO


Student (RollNo, Name, Address) VALUES "
+ "(1, 'Alice', 'New York'), "
+ "(2, 'Bob', 'Los Angeles'), "
+ "(3, 'Charlie', 'Chicago')";
[Link](insertInitial);

// b. Display records
[Link]("Initial Records:");
displayRecords(stmt);
// c. Insert two new records
String insertTwo = "INSERT INTO
Student (RollNo, Name, Address) VALUES "
+ "(4, 'David', 'Miami'), "
+ "(5, 'Eva', 'Dallas')";
[Link](insertTwo);

// d. Update one record


String updateOne = "UPDATE
Student SET Address='San Francisco'
WHERE RollNo=2";
[Link](updateOne

);

// e. Delete one record


String deleteOne = "DELETE FROM
Student WHERE RollNo=3";
[Link](deleteOne);

// f. Display final records


[Link]("\nFinal Records
after Insert, Update, and Delete:");
displayRecords(stmt);

} catch (Exception e) {
[Link]();
} finally {
try { if (stmt != null) [Link](); }
catch (Exception e) {}
try { if (conn != null) [Link](); }
catch (Exception e) {}
}
}

// Method to display records from Student


table
private static void
displayRecords(Statement stmt) throws
SQLException {
ResultSet rs =
[Link]("SELECT * FROM
Student");
while ([Link]()) {
int rollNo = [Link]("RollNo");
String name = [Link]("Name");
String address =
[Link]("Address");
[Link]("Roll No: %d,
Name: %s, Address: %s%n", rollNo, name,
address);
}
[Link]();
}
}

2. Write a JDBC application which will


interact with Database and perform the
following task.
a. Create Student Table with Roll No,
Name, and Address field and insert
few records.
b. Using Prepared Statement Object
display the content of Record.
[Link] Prepared Statement Object
Insert Two Record.
d. Using Prepared Statement Object
Update One Record.
e. Using Prepared Statement Object
Delete One Record.
f. Using Prepared Statement Object
display the content of Record

2. import [Link].*;

public class JDBCPreparedStatementApp {

public static void main(String[] args) {


Connection conn = null;

// Update as per your MySQL


credentials and DB name
String url =
"jdbc:mysql://localhost:3306/college";
String user = "root";
String password = "yourpassword"; //
← Replace with your password
try {
// Load MySQL JDBC Driver

[Link]("[Link]");

// Connect to MySQL Database


conn =
[Link](url, user,
password);

// a. Create Student Table and insert


few records
String createTable = "CREATE TABLE
IF NOT EXISTS Student ("
+ "RollNo INT PRIMARY
KEY, "
+ "Name VARCHAR(50),
"
+ "Address
VARCHAR(100))";
Statement stmt =
[Link]();
[Link](createTable);

// Clear old data


[Link]("DELETE FROM
Student");

// Insert few records using


PreparedStatement
String insertSQL = "INSERT INTO
Student (RollNo, Name, Address) VALUES
(?, ?, ?)";
PreparedStatement pstmtInsert =
[Link](insertSQL);
[Link](1, 1);
[Link](2, "Alice");
[Link](3, "New York");
[Link]();

[Link](1, 2);
[Link](2, "Bob");
[Link](3, "Los
Angeles");
[Link]();

[Link](1, 3);
[Link](2, "Charlie");
[Link](3, "Chicago");
[Link]();

// b. Display records using


PreparedStatement
[Link]("Initial Records:");
displayRecords(conn);

// c. Insert Two More Records


[Link](1, 4);
[Link](2, "David");
[Link](3, "Miami");
[Link]();
[Link](1, 5);
[Link](2, "Eva");
[Link](3, "Dallas");
[Link]();

// d. Update One Record


String updateSQL = "UPDATE
Student SET Address=? WHERE RollNo=?";
PreparedStatement pstmtUpdate =
[Link](updateSQL);
[Link](1, "San
Francisco");
[Link](2, 2);
[Link]();

// e. Delete One Record


String deleteSQL = "DELETE FROM
Student WHERE RollNo=?";
PreparedStatement pstmtDelete =
[Link](deleteSQL);
[Link](1, 3);
[Link]();

// f. Display final records


[Link]("\nFinal Records
after Insert, Update, and Delete:");
displayRecords(conn);

// Close prepared statements


[Link]();
[Link]();
[Link]();

} catch (Exception e) {
[Link]();
} finally {
try { if (conn != null) [Link](); }
catch (Exception e) {}
}
}
// Display records using
PreparedStatement
public static void
displayRecords(Connection conn) throws
SQLException {
String selectSQL = "SELECT * FROM
Student";
PreparedStatement pstmtSelect =
[Link](selectSQL);
ResultSet rs =
[Link]();

while ([Link]()) {
int rollNo = [Link]("RollNo");
String name = [Link]("Name");
String address =
[Link]("Address");
[Link]("Roll No: %d,
Name: %s, Address: %s%n", rollNo, name,
address);
}
[Link]();
[Link]();
}
}

3. Write a JDBC application which will


interact with Database and perform the
following task.
a. Create a store procedure which
will insert one record into employee
table.
b. Create a store procedure which
will retrieve salary for given
employee id.
[Link] a java application which will
call the above procedure and display
appropriate information on screen

CREATE TABLE employee (


emp_id INT PRIMARY KEY,
emp_name VARCHAR(100),
salary DOUBLE
);
DELIMITER //

CREATE PROCEDURE insert_employee(


IN p_emp_id INT,
IN p_emp_name VARCHAR(100),
IN p_salary DOUBLE
)
BEGIN
INSERT INTO employee(emp_id,
emp_name, salary)
VALUES (p_emp_id, p_emp_name,
p_salary);
END //

DELIMITER ;

DELIMITER //

[Link] PROCEDURE get_salary(


IN p_emp_id INT,
OUT p_salary DOUBLE
)
BEGIN
SELECT salary INTO p_salary
FROM employee
WHERE emp_id = p_emp_id;
END //

DELIMITER ;

c.
import [Link].*;

public class EmployeeProcedureDemo {


public static void main(String[] args) {
String jdbcUrl =
"jdbc:mysql://localhost:3306/your_db_na
me";
String username =
"your_username";
String password =
"your_password";

try (Connection conn =


[Link](jdbcUrl,
username, password)) {

// 1. Call procedure to insert


employee
CallableStatement insertStmt =
[Link]("{call
insert_employee(?, ?, ?)}");
[Link](1, 101);
[Link](2, "John
Doe");
[Link](3,
55000.00);
[Link]();
[Link]("Employee
inserted successfully.");

// 2. Call procedure to get salary


by emp_id
CallableStatement getSalaryStmt
= [Link]("{call
get_salary(?, ?)}");
[Link](1, 101);
[Link]
ter(2, [Link]);
[Link]();

double salary =
[Link](2);
[Link]("Salary of
Employee with ID 101: ₹" + salary);

} catch (SQLException e) {
[Link]();
}
}
}
import [Link].*;

public class EmployeeProcedureDemo {


public static void main(String[] args) {
String jdbcUrl =
"jdbc:mysql://localhost:3306/your_db_na
me";
String username =
"your_username";
String password =
"your_password";

try (Connection conn =


[Link](jdbcUrl,
username, password)) {

// 1. Call procedure to insert


employee
CallableStatement insertStmt =
[Link]("{call
insert_employee(?, ?, ?)}");
[Link](1, 101);
[Link](2, "John
Doe");
[Link](3,
55000.00);
[Link]();
[Link]("Employee
inserted successfully.");

// 2. Call procedure to get salary


by emp_id
CallableStatement getSalaryStmt
= [Link]("{call
get_salary(?, ?)}");
[Link](1, 101);
[Link]
ter(2, [Link]);
[Link]();

double salary =
[Link](2);
[Link]("Salary of
Employee with ID 101: ₹" + salary);

} catch (SQLException e) {
[Link]();
}
}
}

Employee inserted successfully.


Salary of Employee with ID 101:
₹55000.0

4. Design a JDBC application which will


demonstrate Scrollable Result Set
functionality.

2. Sample employee Table (SQL)

CREATE TABLE employee (

emp_id INT PRIMARY KEY,


emp_name VARCHAR(100),
salary DOUBLE
);

INSERT INTO employee VALUES (101,


'Alice', 60000);
INSERT INTO employee VALUES (102,
'Bob', 65000);
INSERT INTO employee VALUES (103,
'Charlie', 70000);
Java Code: Scrollable ResultSet Demo

import [Link].*;

public class ScrollableResultSetDemo {


public static void main(String[] args) {
String jdbcUrl =
"jdbc:mysql://localhost:3306/your_db_na
me";
String username =
"your_username";
String password =
"your_password";

try (
Connection conn =
[Link](jdbcUrl,
username, password);
Statement stmt =
[Link](
ResultSet.TYPE_SCROLL_INSEN
SITIVE, // Enable scrolling
ResultSet.CONCUR_READ_ONL
Y // Read-only result set
);
ResultSet rs =
[Link]("SELECT * FROM
employee")
){
// Move to last row
[Link]();
[Link]("Last
Employee: ID=" + [Link]("emp_id") +
", Name=" +
[Link]("emp_name") +
", Salary=" +
[Link]("salary"));

// Move to first row


[Link]();
[Link]("First
Employee: ID=" + [Link]("emp_id") +
", Name=" +
[Link]("emp_name") +
", Salary=" +
[Link]("salary"));

// Move to the 2nd row (absolute


positioning)
if ([Link](2)) {
[Link]("2nd
Employee: ID=" + [Link]("emp_id") +
", Name=" +
[Link]("emp_name") +
", Salary=" +
[Link]("salary"));
}

// Move backwards
[Link]();
[Link]("\nEmployees
in Reverse Order:");
while ([Link]()) {
[Link]("ID=" +
[Link]("emp_id") +
", Name=" +
[Link]("emp_name") +
", Salary=" +
[Link]("salary"));
}

} catch (SQLException e) {
[Link]();
}
}
}
Add JDBC Driver (Maven Example for
MySQL)

<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-j</artifa
ctId>
<version>8.0.33</version>
</dependency>

Output Example
Last Employee: ID=103, Name=Charlie,
Salary=70000.0
First Employee: ID=101, Name=Alice,
Salary=60000.0
2nd Employee: ID=102, Name=Bob,
Salary=65000.0

Employees in Reverse Order:


ID=103, Name=Charlie, Salary=70000.0
ID=102, Name=Bob, Salary=65000.0
ID=101, Name=Alice, Salary=60000.0
5. Design a JDBC application which will
demonstrate Updatable Result Set
functionality.
Here is a complete JDBC application that
demonstrates how to use an updatable ResultSet,
allowing you to:
 Retrieve data from a database table

 Update a record in-place using ResultSet

 Insert a new record

using [Link]()
 Delete a record

using [Link]()

 1. Sample Table
 We'll use the same employee table:

CREATE TABLE employee (


emp_id INT PRIMARY KEY,
emp_name VARCHAR(100),
salary DOUBLE
);

INSERT INTO employee VALUES (101, 'Alice',


60000);
INSERT INTO employee VALUES (102, 'Bob',
65000);
INSERT INTO employee VALUES (103, 'Charlie',
70000);

2. JDBC Application (Updatable ResultSet)


import [Link].*;

public class UpdatableResultSetDemo {


public static void main(String[] args) {
String jdbcUrl =
"jdbc:mysql://localhost:3306/your_db_name";
String username = "your_username";
String password = "your_password";

try (
Connection conn =
[Link](jdbcUrl, username,
password);
Statement stmt = [Link](
ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE
);
ResultSet rs = [Link]("SELECT *
FROM employee")
){
[Link]("Original Records:");
while ([Link]()) {
[Link]([Link]("emp_id") + " - "
+
[Link]("emp_name") + " - "
+
[Link]("salary"));
}

// --- Update a record (change salary for emp_id


101) ---
[Link](); // reset cursor
while ([Link]()) {
if ([Link]("emp_id") == 101) {
[Link]("salary", 75000);
[Link]();
[Link]("\nUpdated salary for
emp_id 101.");
break;
}
}

// --- Insert a new record ---


[Link]();
[Link]("emp_id", 104);
[Link]("emp_name", "David");
[Link]("salary", 80000);
[Link]();
[Link]("Inserted new employee with
emp_id 104.");
// --- Delete a record ---
[Link]();
while ([Link]()) {
if ([Link]("emp_id") == 102) {
[Link]();
[Link]("Deleted employee with
emp_id 102.");
break;
}
}

// --- Display updated table ---


[Link]();
[Link]("\nUpdated Employee
Table:");
while ([Link]()) {
[Link]([Link]("emp_id") + " - "
+
[Link]("emp_name") + " - "
+
[Link]("salary"));
}

} catch (SQLException e) {
[Link]();
}
}
}

Output example

Original Records:
101 - Alice - 60000.0
102 - Bob - 65000.0
103 - Charlie - 70000.0

Updated salary for emp_id 101.


Inserted new employee with emp_id 104.
Deleted employee with emp_id 102.

Updated Employee Table:


101 - Alice - 75000.0
103 - Charlie - 70000.0
104 - David - 80000.0

You might also like