Implementing Database CRUD Operations Through Java Console Application
Student Entity Class
A JavaBean class serves as the data model with private fields and getter/setter methods.
public class Student {
private String studentId;
private String studentName;
private String gender;
private String age;
public String getStudentId() {
return studentId;
}
public void setStudentId(String id) {
this.studentId = id;
}
public String getStudentName() {
return studentName;
}
public void setStudentName(String name) {
this.studentName = name;
}
public String getGender() {
return gender;
}
public void setGender(String sex) {
this.gender = sex;
}
public String getAge() {
return age;
}
public void setAge(String studentAge) {
this.age = studentAge;
}
public Student() {}
public Student(String name, String sex, String studentAge) {
this.studentName = name;
this.gender = sex;
this.age = studentAge;
}
}
Database Access Object
The DAO class handles database connectivity and executes SQL statements.
Key JDBC Components:
Class.forName()loads the MySQL JDBC driverDriverManager.getConnection()establishes database connectionPreparedStatementexecutes parameterized SQL queriesResultSetstores query results
Connection Configuration:
String url = "jdbc:mysql://localhost:3306/school?serverTimezone=UTC&characterEncoding=UTF-8&useSSL=false";
String username = "root";
String password = "123456";
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class StudentDAO {
private static Connection establishConnection() {
Connection connection = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
String url = "jdbc:mysql://localhost:3306/school?serverTimezone=UTC&characterEncoding=UTF-8&useSSL=false";
String user = "root";
String pwd = "123456";
try {
connection = DriverManager.getConnection(url, user, pwd);
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
public static int addStudent(Student student) {
Connection conn = establishConnection();
int result = 0;
String sql = "INSERT INTO student_records(Name, Gender, Age) VALUES(?, ?, ?)";
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setString(1, student.getStudentName());
pstmt.setString(2, student.getGender());
pstmt.setString(3, student.getAge());
result = pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
closeResources(conn);
}
return result;
}
public static int modifyStudent(Student student) {
Connection conn = establishConnection();
int result = 0;
String sql = "UPDATE student_records SET Age = ? WHERE Name = ?";
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setString(1, student.getAge());
pstmt.setString(2, student.getStudentName());
result = pstmt.executeUpdate();
if (result > 0) {
System.out.println("Update completed successfully!");
} else {
System.out.println("No matching records found.");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
closeResources(conn);
}
return result;
}
public static void displayAllRecords() {
Connection conn = establishConnection();
String sql = "SELECT * FROM student_records";
try (PreparedStatement pstmt = conn.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery()) {
int columnCount = rs.getMetaData().getColumnCount();
System.out.println("========== Student Records ==========");
while (rs.next()) {
StringBuilder row = new StringBuilder();
for (int i = 1; i <= columnCount; i++) {
row.append(rs.getString(i)).append("\t");
}
System.out.println(row.toString());
}
System.out.println("====================================");
} catch (SQLException e) {
e.printStackTrace();
} finally {
closeResources(conn);
}
}
public static int removeStudent(String targetName) {
Connection conn = establishConnection();
int result = 0;
String sql = "DELETE FROM student_records WHERE Name = ?";
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setString(1, targetName);
result = pstmt.executeUpdate();
if (result > 0) {
System.out.println("Record deleted successfully!");
} else {
System.out.println("No matching records to delete.");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
closeResources(conn);
}
return result;
}
private static void closeResources(Connection conn) {
try {
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
Console Application Interface
package com.studentapp;
import java.util.Scanner;
import com.bean.Student;
import com.dao.StudentDAO;
public class StudentApp {
public static void main(String[] args) {
Scanner inputScanner = new Scanner(System.in);
boolean continueRunning = true;
while (continueRunning) {
displayMenu();
int choice = inputScanner.nextInt();
switch (choice) {
case 1:
handleInsert(inputScanner);
break;
case 2:
handleDelete(inputScanner);
break;
case 3:
handleUpdate(inputScanner);
break;
case 4:
StudentDAO.displayAllRecords();
break;
case 5:
continueRunning = false;
System.out.println("Application closed.");
break;
default:
System.out.println("Invalid option. Please try again.");
}
}
inputScanner.close();
}
private static void displayMenu() {
System.out.println("\n========================================");
System.out.println(" Student Management System ");
System.out.println("========================================");
System.out.println("1. Insert new student");
System.out.println("2. Delete student record");
System.out.println("3. Update student age");
System.out.println("4. Display all records");
System.out.println("5. Exit");
System.out.println("========================================");
System.out.print("Enter your choice: ");
}
private static void handleInsert(Scanner scanner) {
System.out.println("\n--- Insert New Student ---");
System.out.print("Enter name: ");
String name = scanner.next();
System.out.print("Enter gender: ");
String gender = scanner.next();
System.out.print("Enter age: ");
String age = scanner.next();
int status = StudentDAO.addStudent(new Student(name, gender, age));
if (status > 0) {
System.out.println("Student added successfully!");
}
StudentDAO.displayAllRecords();
}
private static void handleDelete(Scanner scanner) {
System.out.println("\n--- Delete Student Record ---");
System.out.print("Enter student name to delete: ");
String name = scanner.next();
StudentDAO.removeStudent(name);
StudentDAO.displayAllRecords();
}
private static void handleUpdate(Scanner scanner) {
System.out.println("\n--- Update Student Age ---");
System.out.print("Enter student name: ");
String name = scanner.next();
System.out.print("Enter new age: ");
String age = scanner.next();
StudentDAO.displayAllRecords();
StudentDAO.modifyStudent(new Student(name, "", age));
StudentDAO.displayAllRecords();
}
}
Database Table Schema:
CREATE TABLE student_records (
Id INT AUTO_INCREMENT PRIMARY KEY,
Name VARCHAR(50),
Gender VARCHAR(10),
Age VARCHAR(10)
);
Important Notes:
- When inputting Chinese characters through console, ensure the terminal encoding matches UTF-8 to avoid character corruption
- Always use parameterized queries (
PreparedStatement) to prevent SQL injection vulnerabilities - Close database connections in finally blocks to prevent resource leaks
