In this blog post, we'll explore how to create a simple Java application using JDBC to manage feedback, customers, and products. Our application will allow users to submit feedback, view it by product, update comments, and delete feedback entries. We'll focus on establishing a strong foundation with validations to ensure data integrity.
Table Structure
We will have three tables in our MySQL database:
1. Feedback Table
feedback_id (int, primary key)
customer_id (int, foreign key)
product_id (int, foreign key)
feedback_date (date)
comments (text)
2. Customer Table
customer_id (int, primary key)
customer_name (varchar)
3. Product Table
product_id (int, primary key)
product_name (varchar)
Step 1: Creating Entity Classes
We'll create three classes: Customer, Product, and Feedback. Each class will have the corresponding fields, along with constructors, getters, and setters.
Example: Feedback Class
public class Feedback {
private int feedbackId;
private int customerId;
private int productId;
private LocalDate feedbackDate;
private String comments;
// Constructors, getters, and setters
}
Step 2: Establishing JDBC Connection
We need to create a class to handle our database connection.
JDBC Connection Class
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class JdbcConnection {
private static final String URL = "jdbc:mysql://localhost/CustomerFeedbackSystem";
private static final String USERNAME = "userName";
private static final String PASSWORD = "password";
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(URL, USERNAME, PASSWORD);
}
}
Step 3: Main Application Class
We’ll create a main class named CustomerFeedbackSystem, which will utilize a switch statement to call various CRUD operations. Users will have options to submit, view, update, or delete feedback.
Main Class Structure
import java.util.Scanner;
public class CustomerFeedbackSystem {
public static void main(String[] args) {
Scanner scanner = new Scanner(System.in);
boolean running = true;
while (running) {
System.out.println("1. Submit Feedback");
System.out.println("2. View Feedback for Product");
System.out.println("3. Update Feedback Comments");
System.out.println("4. Delete Feedback");
System.out.println("5. Exit");
int choice = scanner.nextInt();
switch (choice) {
case 1:
InsertFeedback.insert();
break;
case 2:
ViewFeedbackForProduct.view();
break;
case 3:
UpdateFeedback.update();
break;
case 4:
DeleteFeedback.delete();
break;
case 5:
running = false;
break;
default:
System.out.println("Invalid choice. Please try again.");
}
}
scanner.close();
}
}
Step 4: Validation Classes
We will create classes like CustomerIds and ProductIds to check if the entered IDs are present in the database. This will help ensure that the IDs used in feedback submissions are valid.
Step 5: CRUD Operations
We will implement four main classes for our CRUD operations:
5.1 Insert Feedback
This class will handle inserting new feedback into the database, along with the necessary validations.
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.Scanner;
import java.util.regex.Pattern;
public class InsertFeedback {
public static void insert() {
Scanner input = new Scanner(System.in);
Feedback feedback = new Feedback();
String sql = "INSERT INTO Feedback VALUES (?, ?, ?, ?, ?)";
String pattern = "^[0-9]{6}";
try (Connection connection = JdbcConnection.getConnection();
PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
// Feedback ID
System.out.print("Enter Feedback ID: ");
String feedbackId = input.nextLine();
validateId(feedbackId, pattern);
feedback.setFeedbackId(Integer.parseInt(feedbackId));
// Customer ID
System.out.print("Enter Customer ID: ");
String customerId = input.nextLine();
validateId(customerId, pattern);
feedback.setCustomerId(Integer.parseInt(customerId));
// Product ID
System.out.print("Enter Product ID: ");
String productId = input.nextLine();
validateId(productId, pattern);
feedback.setProductId(Integer.parseInt(productId));
// Feedback Date
System.out.print("Enter Feedback Date (yyyy-mm-dd): ");
String feedbackDate = input.nextLine();
feedback.setFeedbackDate(LocalDate.parse(feedbackDate));
// Comments
System.out.print("Enter Comments: ");
String comments = input.nextLine();
validateComments(comments);
feedback.setComments(comments);
preparedStatement.setInt(1, feedback.getFeedbackId());
preparedStatement.setInt(2, feedback.getCustomerId());
preparedStatement.setInt(3, feedback.getProductId());
preparedStatement.setDate(4, java.sql.Date.valueOf(feedback.getFeedbackDate()));
preparedStatement.setString(5, feedback.getComments());
int result = preparedStatement.executeUpdate();
if (result > 0) {
System.out.println("Feedback submitted successfully!");
}
} catch (Exception e) {
System.out.println("Error: " + e.getMessage());
}
}
private static void validateId(String id, String pattern) {
while (!Pattern.matches(pattern, id)) {
System.out.print("ID must be a 6-digit integer. Enter again: ");
id = new Scanner(System.in).nextLine();
}
}
private static void validateComments(String comments) {
while (comments.length() > 500) {
System.out.print("Comments must be less than 500 characters. Enter again: ");
comments = new Scanner(System.in).nextLine();
}
}
}
5.2 View Feedback for Product
This class retrieves and displays all feedback for a specific product.
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Scanner;
public class ViewFeedbackForProduct {
public static void view() {
Scanner input = new Scanner(System.in);
System.out.print("Enter Product ID: ");
String productId = input.nextLine();
String sql = "SELECT * FROM Feedback WHERE product_id = ?";
try (Connection connection = JdbcConnection.getConnection();
PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
preparedStatement.setInt(1, Integer.parseInt(productId));
ResultSet resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
// Display feedback details
System.out.println("Feedback ID: " + resultSet.getInt("feedback_id"));
System.out.println("Customer ID: " + resultSet.getInt("customer_id"));
System.out.println("Comments: " + resultSet.getString("comments"));
System.out.println("Date: " + resultSet.getDate("feedback_date"));
System.out.println("-----");
}
} catch (Exception e) {
System.out.println("Error: " + e.getMessage());
}
}
}
5.3 Update Feedback
This class updates feedback comments based on feedback_id.
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.Scanner;
public class UpdateFeedback {
public static void update() {
Scanner input = new Scanner(System.in);
System.out.print("Enter Feedback ID to update: ");
String feedbackId = input.nextLine();
System.out.print("Enter new comments: ");
String newComments = input.nextLine();
String sql = "UPDATE Feedback SET comments = ? WHERE feedback_id = ?";
try (Connection connection = JdbcConnection.getConnection();
PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
preparedStatement.setString(1, newComments);
preparedStatement.setInt(2, Integer.parseInt(feedbackId));
preparedStatement.executeUpdate();
System.out.println("Feedback updated successfully!");
} catch (Exception e) {
System.out.println("Error: " + e.getMessage());
}
}
}
5.4 Delete Feedback
This class removes feedback records based on feedback_id.
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.Scanner;
public class DeleteFeedback {
public static void delete() {
Scanner input = new Scanner(System.in);
System.out.print("Enter Feedback ID to delete: ");
String feedbackId = input.nextLine();
String sql = "DELETE FROM Feedback WHERE feedback_id = ?";
try (Connection connection = JdbcConnection.getConnection();
PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
preparedStatement.setInt(1, Integer.parseInt(feedbackId));
preparedStatement.executeUpdate();
System.out.println("Feedback deleted successfully!");
} catch (Exception e) {
System.out.println("Error: " + e.getMessage());
}
}
}
Conclusion
In this blog post, we covered the essential steps to create a Customer Feedback System using Java and JDBC. By setting up a well-structured application with validations, we ensure that user input is handled gracefully and the integrity of our database is maintained.
Feel free to extend the functionalities and customize it further based on your needs! Happy coding!