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.
c. Using 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.
import java.sql.*;
public class JDBCAppWithPreparedStatement {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/college";
String user = "root";
String password = "root"; // Change to your MySQL password
try {
// Load JDBC driver
Class.forName("com.mysql.cj.jdbc.Driver");
// Establish connection
try (Connection con = DriverManager.getConnection(url, user, password)) {
// a. Create Table
String createTable = "CREATE TABLE IF NOT EXISTS student (" +
"rollno INT PRIMARY KEY, " +
"name VARCHAR(50), " +
"address VARCHAR(100))";
try (PreparedStatement pstCreate = con.prepareStatement(createTable)) {
pstCreate.executeUpdate();
}
// b. Display content before insertion
System.out.println("Before Insert:");
displayStudents(con);
// c. Insert Two Records (with basic duplicate handling)
String insertSQL = "INSERT INTO student (rollno, name, address) VALUES (?, ?, ?)";
try (PreparedStatement pstInsert = con.prepareStatement(insertSQL)) {
// Insert record 1
try {
pstInsert.setInt(1, 101);
pstInsert.setString(2, "John");
pstInsert.setString(3, "New York");
pstInsert.executeUpdate();
} catch (SQLIntegrityConstraintViolationException e) {
System.out.println("Record with rollno 101 already exists.");
}
// Insert record 2
try {
pstInsert.setInt(1, 102);
pstInsert.setString(2, "Alice");
pstInsert.setString(3, "California");
pstInsert.executeUpdate();
} catch (SQLIntegrityConstraintViolationException e) {
System.out.println("Record with rollno 102 already exists.");
}
}
// Display after insertion
System.out.println("After Insert:");
displayStudents(con);
// d. Update one record
String updateSQL = "UPDATE student SET address = ? WHERE rollno = ?";
try (PreparedStatement pstUpdate = con.prepareStatement(updateSQL)) {
pstUpdate.setString(1, "Texas");
pstUpdate.setInt(2, 101);
pstUpdate.executeUpdate();
}
// e. Delete one record
String deleteSQL = "DELETE FROM student WHERE rollno = ?";
try (PreparedStatement pstDelete = con.prepareStatement(deleteSQL)) {
pstDelete.setInt(1, 102);
pstDelete.executeUpdate();
}
// f. Final Display
System.out.println("After Update and Delete:");
displayStudents(con);
}
} catch (Exception e) {
e.printStackTrace();
}
}
// Method to display student records
private static void displayStudents(Connection con) throws SQLException {
String selectSQL = "SELECT * FROM student";
try (PreparedStatement pstSelect = con.prepareStatement(selectSQL);
ResultSet rs = pstSelect.executeQuery()) {
boolean recordsExist = false;
while (rs.next()) {
recordsExist = true;
System.out.println("Roll No: " + rs.getInt("rollno") +
", Name: " + rs.getString("name") +
", Address: " + rs.getString("address"));
}
if (!recordsExist) {
System.out.println("No records found.");
}
System.out.println("-----------------------------");
}
}
}