In this tutorial, we’ll walk through the process of building a simple Customer Management System in Java using SQLite. We’ll cover how to set up the SQLite database, create a Java application to perform CRUD (Create, Read, Update, Delete) operations on customer data, and demonstrate the functionality with example code.
1. Setting Up SQLite Database
SQLite is a lightweight database that doesn’t require a separate server process. To get started, we need to set up our SQLite database.
First, ensure you have SQLite installed on your system. You can download SQLite from its official website (https://www.sqlite.org/download.html) or use a package manager like Homebrew on macOS or apt on Ubuntu.
Once SQLite is installed, create a new SQLite database file. You can do this using the SQLite command-line interface or any SQLite database management tool. Let’s name our database file customer_management.db.
2. Creating the Java Application
Now, let’s create a Java application that interacts with our SQLite database to manage customer data. We’ll perform the following steps:
- Connect to the SQLite database.
- Implement methods to perform CRUD operations on customer data.
- Demonstrate the usage of these methods with example code.
3. Connecting to SQLite Database
To connect to our SQLite database from Java, we’ll use JDBC (Java Database Connectivity). JDBC is a Java API for connecting and executing SQL queries on a database.
First, ensure you have the SQLite JDBC driver in your project dependencies. You can download the SQLite JDBC driver (sqlite-jdbc.jar) from the SQLite JDBC website (https://github.com/xerial/sqlite-jdbc).
Add the SQLite JDBC driver to your project’s classpath. If you’re using an IDE like IntelliJ IDEA or Eclipse, you can add the JAR file to your project’s dependencies.
Next, let’s establish a connection to our SQLite database:
import java.sql.*;
public class CustomerManagement {
public static Connection createConnection(String dbFile) {
Connection conn = null;
try {
conn = DriverManager.getConnection("jdbc:sqlite:" + dbFile);
System.out.println("Connected to database: " + dbFile);
} catch (SQLException e) {
System.out.println(e.getMessage());
}
return conn;
}
public static void main(String[] args) {
String database = "customer_management.db";
try (Connection conn = createConnection(database)) {
// Connection established successfully
} catch (SQLException e) {
System.out.println(e.getMessage());
}
}
}
In the createConnection method, we use the DriverManager.getConnection method to establish a connection to the SQLite database file specified by the dbFile parameter. If the connection is successful, a confirmation message is printed.
4. Implementing CRUD Operations
Next, let’s implement methods to perform CRUD operations on customer data:
- Create a new customer.
- Retrieve all customers.
- Update customer details.
- Delete a customer.
Here’s the implementation:
// Function to create a new customer
public static int createCustomer(Connection conn, String name, String email, String phoneNumber) throws SQLException {
String sql = "INSERT INTO customers(name, email, phone_number) VALUES(?,?,?)";
try (PreparedStatement pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
pstmt.setString(1, name);
pstmt.setString(2, email);
pstmt.setString(3, phoneNumber);
pstmt.executeUpdate();
ResultSet rs = pstmt.getGeneratedKeys();
if (rs.next()) {
return rs.getInt(1);
}
}
return -1;
}
// Function to retrieve all customers
public static void getAllCustomers(Connection conn) throws SQLException {
String sql = "SELECT * FROM customers";
try (Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql)) {
while (rs.next()) {
System.out.println(rs.getInt("id") + "\t" +
rs.getString("name") + "\t" +
rs.getString("email") + "\t" +
rs.getString("phone_number"));
}
}
}
// Function to update customer details
public static void updateCustomer(Connection conn, int id, String name, String email, String phoneNumber) throws SQLException {
String sql = "UPDATE customers SET name = ?, email = ?, phone_number = ? WHERE id = ?";
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setString(1, name);
pstmt.setString(2, email);
pstmt.setString(3, phoneNumber);
pstmt.setInt(4, id);
pstmt.executeUpdate();
}
}
// Function to delete a customer
public static void deleteCustomer(Connection conn, int id) throws SQLException {
String sql = "DELETE FROM customers WHERE id = ?";
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setInt(1, id);
pstmt.executeUpdate();
}
}
In these methods, we execute SQL queries using PreparedStatement to ensure security and prevent SQL injection attacks. The createCustomer method inserts a new customer into the customers table, getAllCustomers retrieves all customers, updateCustomer updates the details of an existing customer, and deleteCustomer deletes a customer based on their ID.
5. Demonstrating Usage
Finally, let’s demonstrate the usage of these methods in the main method:
public static void main(String[] args) {
String database = "customer_management.db";
try (Connection conn = createConnection(database)) {
// Create a new customer
int customerId = createCustomer(conn, "John Doe", "john@example.com", "1234567890");
System.out.println("New customer ID: " + customerId);
// Retrieve all customers
System.out.println("All Customers:");
getAllCustomers(conn);
// Update customer details
updateCustomer(conn, customerId, "Jane Smith", "jane@example.com", "0987654321");
// Retrieve all customers after update
System.out.println("All Customers after update:");
getAllCustomers(conn);
// Delete a customer
deleteCustomer(conn, customerId);
System.out.println("Deleted customer with ID: " + customerId);
} catch (SQLException e) {
System.out.println(e.getMessage());
}
}
In the main method, we establish a connection to the SQLite database, create a new customer, retrieve all customers, update a customer’s details, retrieve all customers again to see the update, and finally delete the customer we created.
Conclusion
In this tutorial, we’ve learned how to build a Customer Management System in Java using SQLite. We covered setting up the SQLite database, connecting to it from Java, implementing CRUD operations on customer data, and demonstrating the usage of these operations with example code. This project serves as a foundation for building more complex database-driven applications in Java.
