Building a Customer Feedback System in Java with JDBC

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!

Customer feedback system using mysql and jdbc - Review question for core java