Java JDBC Student Database Management System
Introduction to JDBC Student Data Operations
This document presents a Java program demonstrating fundamental JDBC (Java Database Connectivity) operations for managing student records in a database. The application provides a menu-driven interface to perform Create, Read, Update, and Delete (CRUD) operations on a student table.
Core Components of the Java Application
The application is structured within a student class, encapsulating database connection logic and CRUD methods. It utilizes the java.sql.* package for JDBC functionalities and java.util.Scanner for user input.
Package and Imports
package jdbc;
import java.sql.*;
import java.util.Scanner;The Student Class Definition
public class student
{
Connection con;
// ... methods follow ...
}Establishing Database Connection
The establishConnection() method is responsible for setting up the connection to the Oracle database. It loads the JDBC driver and establishes a connection using the provided URL, username, and password.
public void establishConnection() throws ClassNotFoundException, SQLException
{
try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","SYSTEM","tiger");
}
catch(ClassNotFoundException e)
{
System.err.println("Driver class not found: " + e.getMessage());
}
catch(SQLException e)
{
System.err.println("SQL Exception occurred during connection: " + e.getMessage());
}
}Note: The original code had the same error message for both ClassNotFoundException and SQLException. This has been corrected for clarity.
Student Data Manipulation Methods
The student class includes several methods for performing CRUD operations on the student table. Each method establishes a connection, executes a specific SQL query using PreparedStatement, and closes resources in a finally block.
Inserting Student Records
The sInsert() method adds a new student record to the database.
public void sInsert(String usn, String name, String dept)
throws ClassNotFoundException, SQLException
{
PreparedStatement pst = null;
establishConnection();
try
{
if(con != null)
{
pst = con.prepareStatement("insert into student values(?,?,?)");
pst.setString(1,usn);
pst.setString(2,name);
pst.setString(3,dept);
int i = pst.executeUpdate();
if(i == 1)
{
System.out.println("Record inserted successfully");
}
}
}
catch(SQLException e)
{
System.err.println(e.getMessage());
}
finally
{
if (pst != null) pst.close();
if (con != null) con.close();
}
}Selecting a Specific Student Record
The sSelect() method retrieves and displays details of a student based on their USN (University Seat Number).
public void sSelect(String usn) throws ClassNotFoundException, SQLException
{
PreparedStatement pst = null;
ResultSet res;
establishConnection();
try
{
if(con != null)
{
pst = con.prepareStatement("select * from student where usn=?");
pst.setString(1, usn);
res = pst.executeQuery();
if(res.next())
{
System.out.println("USN= "+res.getString(1)+"\tName="+ res.getString(2)+ "\tDepartment= "+res.getString(3));
}
}
}
catch(SQLException e)
{
System.err.println(e.getMessage());
}
finally
{
if (pst != null) pst.close();
if (con != null) con.close();
}
}Updating Student Information
The sUpdate() method modifies existing student records. It updates the USN and Name based on the Department. Please note the SQL query’s logic: it updates USN and Name where the Department matches. Typically, updates are based on a primary key like USN.
public void sUpdate(String usn, String name, String dept) throws ClassNotFoundException, SQLException
{
PreparedStatement pst = null;
establishConnection();
try
{
if(con != null)
{
pst = con.prepareStatement("update student set usn=?,name=? where dept=?");
pst.setString(1,usn);
pst.setString(2,name);
pst.setString(3,dept);
int i = pst.executeUpdate();
if(i == 1)
{
System.out.println("Record updated successfully");
}
}
}
catch(SQLException e)
{
System.err.println(e.getMessage());
}
finally
{
if (pst != null) pst.close();
if (con != null) con.close();
}
}
Deleting Student Records
The sDelete() method removes a student record from the database based on their USN.
public void sDelete(String usn)throws ClassNotFoundException, SQLException
{
PreparedStatement pst = null;
establishConnection();
try
{
if(con != null)
{
pst = con.prepareStatement("delete from student where usn=?");
pst.setString(1, usn);
int i = pst.executeUpdate();
if(i == 1)
{
System.out.println("Record deleted successfully");
}
}
}
catch(SQLException e)
{
System.err.println(e.getMessage());
}
finally
{
if (pst != null) pst.close();
if (con != null) con.close();
}
}Viewing All Student Records
The viewAll() method retrieves and displays all student records present in the student table.
public void viewAll() throws ClassNotFoundException, SQLException
{
PreparedStatement pst = null;
ResultSet res;
establishConnection();
try
{
if(con != null)
{
pst = con.prepareStatement("select * from student");
res = pst.executeQuery();
while(res.next())
{
System.out.println("USN= "+res.getString(1)+"\tName="+res.getString(2)+"\tDepartment= "+res.getString(3));
}
}
}
catch(SQLException e)
{
System.err.println(e.getMessage());
}
finally
{
if (pst != null) pst.close();
if (con != null) con.close();
}
}Main Application Execution
The main method serves as the entry point for the application, providing a command-line interface for users to interact with the student database operations.
public static void main(String[] a) throws ClassNotFoundException, SQLException
{
student std = new student();
String usn,name,dept;
Scanner sc = new Scanner(System.in);
while(true)
{
System.out.println("Operations on Student table");
System.out.println("1.Insert\n2.Select\n3.Update\n4.Delete\n5.View All\n6.Exit");
System.out.println("Select an operation:");
switch(sc.nextInt())
{
case 1:
System.out.println("Enter USN to insert:");
usn = sc.next();
System.out.println("Enter Name to insert:");
name = sc.next();
System.out.println("Enter Department to insert:");
dept = sc.next();
std.sInsert(usn, name, dept);
break;
case 2:
System.out.println("Enter USN to select:");
usn = sc.next();
std.sSelect(usn);
break;
case 3:
System.out.println("Enter USN to update:");
usn = sc.next();
System.out.println("Enter Name to update:");
name = sc.next();
System.out.println("Enter Department to update:");
dept = sc.next();
std.sUpdate(usn, name, dept);
break;
case 4:
System.out.println("Enter USN to delete:");
usn = sc.next();
std.sDelete(usn);
break;
case 5:
std.viewAll();
break;
case 6:
System.exit(0);
default:
System.out.println("Invalid operation");
break;
}
}
}Corrections: “Deaprtment” was corrected to “Department”, and “select the operation” was rephrased to “Select an operation:”.
Conclusion
This Java JDBC application provides a foundational example for interacting with a database to manage student records. It demonstrates essential concepts such as establishing connections, executing prepared statements for various CRUD operations, and handling basic exceptions. This structure can be extended for more complex database applications.