Persist Your Java Application's Data With the Repository Pattern

In the last chapter, we set up and connected to our databases, and ran SQL statements on the databases from our application code. How do you use this to persist your objects? This chapter will show you how! 😁

Persisting Objects in Databases

Relational databases, like the SQLite one I am using, are typically structured around tables similar to those in a document or spreadsheet. Here is a table suitable for storing people details:

Each table has a name and a fixed set of columns. The column name (e.g., Name) tells you what is in that position in each row (i.e., a value in a cell, e.g., Sam). In a relational database, you usually describe what type of data is in the column - for example, a string, number, or date.

Objects have a somewhat similar structure: each object class names the fields that the object has, and assigns a type to each field. Each object instance has values in these fields.

So each object class corresponds to a database table. These define the structure - the names and the types of the fields. This diagram connects which definition elements correspond:

Each object instance corresponds to a row in the database table; it holds the values that describe a particular entry. 

This presents a problem: if you use a name to identify a customer, you may well find you have several John Smith entries in a large customer database. You can't, therefore, use the name to be sure which entry you mean when viewing or changing customer details.

Using Unique Identifiers

You need a way to refer to an instance or row uniquely. That is, if you want to get, update, or delete the details for an entity, it's crucial to be sure you are referring to one specific entry.

Every customer in the database needs to have a unique identifier that refers to them and only them. Usually, this is a number that increments every time you create a new customer; your first customer is "1," your second is "2," and so on. You can generate this in the application (you will see this in the mock customer repository later). Still, if you have several applications running at the same time, it can be hard to ensure they are unique, so it's best to ask the central database to do so.

When creating a database table for customers then, create a table with a generated identifier and the properties of your person class. For example, a SQL statement that does this in SQLite marks the column as a primary key:

create table Customers (ID int primary key, Name varchar(30) not null, Address varchar(200), Telephone varchar(15), email varchar(30));

Add this field to the class definition, so you can relate each of your object instances to that specific entry in the database:

public class Person { 
    int ID;
    String name;
    String address;
    ...etc...
}

So far, the objects have been conceptually related to the database. Next, let's see what the code looks like to act on the object/database persistence.

Using CRUD For Our Customers

CRUD stands for create, read, update, and delete. It refers to the common tasks you want to carry out on datasets. You want the shop staff to be able to add new customers (create), find out the details of existing ones (read), change their details when they move (update), and remove them when they are no longer involved (delete).

Here I show you how to provide these operations on a relational database, for customers as person objects:

Let's have a look at this in more detail.

First, create a skeleton persister that creates and maintains a database connection from when it is constructed, so you don't have to keep recreating a connection for every operation. It establishes a connection using the JDBC library when it is constructed and makes that connection available to the rest of the class:

public class CustomerDatabaseCrudPersister {
	
	private Connection conn = null;
	
	public CustomerDatabaseCrudPersister(String url) throws SQLException {
		conn = DriverManager.getConnection(url);
	}
}

This persister has a connection to the database that it initializes when the persister is constructed.

Creating a Person

As a reminder, this is the SQL for creating a new row in the table with a customer's details, using the SQL INSERT command:

insert into Customers (Name, Address, Telephone, email) values (Sam Jones, 12 Letsby Avenue, 07900 xxxxx, sam@letsby.co.nr)

When you create an entry, you not only want to store the details, but also get the new ID. Using JDBC, it would look something like this:

public void createCustomer(Person aCustomer) throws SQLException {
    PreparedStatement stmt = conn.prepareStatement(
    		"insert into Customers (Name, Address, Telephone, email) values (?,?,?,?)'",
    		Statement.RETURN_GENERATED_KEYS);

    stmt.setString(1,aCustomer.name);
    stmt.setString(2,aCustomer.address);
    stmt.setString(3,aCustomer.telephone);
    stmt.setString(4,aCustomer.email);

    stmt.executeUpdate();
    
    ResultSet rs = stmt.getGeneratedKeys();
    
    if (rs.next()) {
    	aCustomer.ID = rs.getInt(1);
	}
    
}
	

Let's break down the above code:

• Sets up the insert SQL statement.

• Sets the fields of the given person as positioned values in the SQL statement that correspond to the ? marks.

• Executes the SQL statement, and then reads the customer ID number that has been assigned to the new row by the database.

Reading a Person

There are two kinds of read: fetch me the details we have about a particular person and list all those people that match some search criteria.

To get the details of an individual, you need to know their identifier: the unique value assigned to one, and only one, entry. In SQL, you would use the SELECT ... WHERE statement, using the ID as the selection criteria, which would be something like:

SELECT * FROM Customers WHERE id = 1;

This selects all the fields from the customer database where the customer ID is 1. In simple Java, this would be:

public Person readCustomer(int ID) throws SQLException {
    PreparedStatement stmt = conn.prepareStatement("SELECT * FROM Customers where ID=?");
    stmt.setInt(1,ID);
 
    ResultSet rs = stmt.executeQuery();
    
    //you might want to check here that you have one and only one result

    rs.next();
    Person listed = new Person();
    listed.ID = rs.getInt("ID");
    listed.name = rs.getString("Name");
    listed.telephone = rs.getString("Telephone");
    listed.address = rs.getString("Address");
    listed.email = rs.getString("email");

    return listed;
}

This creates a SQL query statement and sets the ID value to 1, executes the query statement, then reads the response fields into a new person object instance to return.

Reading a List (Search Read)

Here you are searching for customers who match some criteria. For this, use the SELECT ... WHERE SQL statement, and look at the results. For example, you might look for all those customers with a certain name:

public List<Person> listCustomersWithName(String name) throws SQLException {
    PreparedStatement stmt = conn.prepareStatement("SELECT * FROM Customers where Name like '%?%'");
    stmt.setString(1,name);
 
    ResultSet rs = stmt.executeQuery();
    
    List<Person> matches = new ArrayList<Person>(); 
    while ( rs.next() ) {
        Person listed = new Person();
        listed.ID = rs.getInt("ID");
        listed.name = rs.getString("Name");
        listed.telephone = rs.getString("Telephone");
        listed.address = rs.getString("Address");
        listed.email = rs.getString("email");
        matches.add(listed);
    }
    
    return matches;
}

This creates a SQL query statement and sets the Name like value to the name given as the method argument, executes the query statement, then reads the response fields into a new person object instance for each row returned. Each new person is added to the matches list, and this is returned from the method.

Updating Person

If you change a person object instance in the application, you want to update the database entry. Do this using the SQL UPDATE statement:

public void updateCustomer(Person editedCustomer) throws SQLException {

    PreparedStatement stmt = conn.prepareStatement("UPDATE Customers set Name=?, Address=?, Telephone=?, email=? where ID=?");

    stmt.setString(1, editedCustomer.name);
    stmt.setString(2, editedCustomer.address);
    stmt.setString(3, editedCustomer.telephone);
    stmt.setString(4, editedCustomer.email);
    stmt.setInt(5, editedCustomer.ID);

    stmt.executeUpdate();

}

This overwrites the entry in the database (a row in the table) with the full details of the given person.

Yes! When multiple users make changes to one database, you have a synchronization problem.

Let's say we are both editing a customer's details: you are changing the email, and I am changing the telephone number. We both read the customer's details and bring them on our screen. You change the email and save it. However, I still have the old email on my screen - and when I change the telephone number and save it, the old email on my system overwrites your update on the database. 😧

Handling such multi-user problems is a topic in itself; in the last chapter, I'll introduce some terms that you can use to explore how to mitigate some of them.

Deleting Person

To delete a customer from the database, create and execute a DELETE SQL statement using the JDBC library, similar to the other examples:

public void deleteCustomer(Person deleteCustomer) throws SQLException {

	PreparedStatement stmt = conn.prepareStatement("DELETE Customers where ID=?");

	stmt.setInt(1, deleteCustomer.ID);

	stmt.executeUpdate();
}
	

This tells the database to delete the row in the Customers table with the ID that matches the given person's ID.

Using CRUD For Our Robot Parts

Now a challenge for you! Above, you can see how to persist customers for our shop to a database, using the person object to store customers. You also want to persist the robot parts catalog - and you can write a Java persister to store robot parts to a database.

The persister needs to create a RobotParts table that matches your robot part class, and have methods to carry out the CRUD actions, just like the customer persister above.

An example follows below.

Here I'll take you through a JDBC robot part CRUD persister:

For reference, here is my simple robot part from the above video - hopefully, yours is more interesting!

public class RobotPart {

	public int ID;
	public String name;
	public String description;
	public String supplier;
	
}

For this simple one, the CRUD persistence class for it looks like this:

public class RobotPartDatabaseCrudPersister {

    //each persister has one connection to the DB	
	Connection conn = null;
	
	public RobotPartDatabaseCrudPersister(String url) throws SQLException {
		conn = DriverManager.getConnection(url);
	}

    /** Assembles the SQL statement for inserting
     * the robot part and executes it */
	public void createRobotPart(RobotPart aRobotPart) throws SQLException {
	    PreparedStatement stmt = conn.prepareStatement(
	    		"insert into RobotParts (Name, Description, Supplier) values (?,?,?)'",
	    		Statement.RETURN_GENERATED_KEYS);

	    stmt.setString(1,aRobotPart.name);
	    stmt.setString(2,aRobotPart.description);
	    stmt.setString(3,aRobotPart.supplier);

	    stmt.executeUpdate();
	    
	    //the database generates the unique ID and returns it
	    // as a key
	    ResultSet rs = stmt.getGeneratedKeys();
	    
	    if (rs.next()) {
	    	aRobotPart.ID = rs.getInt(1);
    	}
	    
	}
	
    /** Assembles the SQL statement for updating
     * the robot part and executes it */
	public void updateRobotPart(RobotPart editedRobotPart) throws SQLException {

	    PreparedStatement stmt = conn.prepareStatement("UPDATE RobotParts set Name=?, Description=?, Supplier=? where ID=?");

	    stmt.setString(1, editedRobotPart.name);
	    stmt.setString(2, editedRobotPart.description);
	    stmt.setString(3, editedRobotPart.supplier);
	    stmt.setInt(5, editedRobotPart.ID);

	    stmt.executeUpdate();

	}
	
	/** Removes the RobotPart by using the standard SQL
	 * 'delete' command */
	public void deleteRobotPart(RobotPart deleteRobotPart) throws SQLException {

	    PreparedStatement stmt = conn.prepareStatement("DELETE RobotParts where ID=?");

	    stmt.setInt(1, deleteRobotPart.ID);

	    stmt.executeUpdate();
	}
	
	/** Queries the database using the SQL select
	 * command to get the single entry identified by the
	 * given unique ID key*/
	public RobotPart readRobotPart(int ID) throws SQLException {
	    PreparedStatement stmt = conn.prepareStatement("SELECT * FROM RobotParts where ID=?");
	    stmt.setInt(1,ID);
	 
	    ResultSet rs = stmt.executeQuery();
	    
	    //you might want to check here that you have one and only one result
    
	    rs.next();
	    RobotPart listed = new RobotPart();
	    listed.ID = rs.getInt("ID");
	    listed.name = rs.getString("Name");
	    listed.description = rs.getString("Description");
	    listed.supplier = rs.getString("Supplier");

	    return listed;
	}
	
	/** Queries the database for any entries with the given
	 * name string in the robot part name. */
	public List<RobotPart> listRobotPartsWithName(String name) throws SQLException {
	    PreparedStatement stmt = conn.prepareStatement("SELECT * FROM RobotParts where Name like '%?%'");
	    stmt.setString(1,name);
	 
	    ResultSet rs = stmt.executeQuery();
	    
	    List<RobotPart> matches = new ArrayList<RobotPart>(); 
	    while ( rs.next() ) {
	        RobotPart listed = new RobotPart();
	        listed.ID = rs.getInt("ID");
	        listed.name = rs.getString("Name");
	        listed.description = rs.getString("Description");
	        listed.supplier = rs.getString("Supplier");
	        matches.add(listed);
	    }
	    
	    return matches;
	}
	
	
}

It has the create, read, update, and delete methods. Where the data is sent to the database, it copies the given RobotPart properties into the SQL update statement. Where the data is read from the database, it copies the SQL ResultSet fields into a RobotPart object to return.

Let's Recap! 

In this chapter, you learned to:

• Use the Java Database Connection library to persist the shop's customer details and robot parts catalog in a relational database.

• Map objects into relational databases: classes are stored in tables, properties are stored in columns, and instances are stored as rows in the tables.

• Implement CRUD for customers and RobotParts.

You can use these same techniques to persist any data structures - taking advantage of the performance, security, and synchronizing features of the various relational databases out there.

However, in these examples, you have to write a lot of boilerplate code for each CRUD action, similar code that does very similar things but still has to be written out. When you add, remove, or change any class property, all the code had to be modified too.

Yes! Yes you can! Let's do that next!