Persist Your Java Application's Data With the Repository Pattern

Now that you have a JDBC bridge between your Java applications and powerful relational databases, what can you do with it? This chapter introduces how to make that bridge work.

Creating Content Using JDBC

Watch how you can interact with relational databases using Java and the Structured Query Language:

Let's break this down. Once you have the connection, all you have to do to execute a SQL statement is send it as a string, using the JDBC library. For example, to add a table to persist the shop's list of customer's, you would use the SQL CREATE command, and the SQL statement could be:

CREATE TABLE IF NOT EXISTS Customers (Name varchar(20), Address varchar(100), Telephone varchar(15), Email varchar(15));

This creates a table called Customers (if it doesn't exist already) with the columns Name, Address, Telephone, and Email.

To execute this from Java, create an SQL statement and execute it using the JDBC library like this:

Connection conn = DriverManager.getConnection("jdbc:sqlite:./RobotShop.db");

Statement stmt = conn.createStatement();
    
stmt.execute("CREATE TABLE IF NOT EXISTS Customers (Name varchar(20), Address varchar(100), Telephone varchar(15), Email varchar(15));");

Let's break this down:

• The first line creates a connection between the application and the database, and you can use this over the lifetime of the application.

• The second two lines create and run the SQL statement. In this case, it's the SQL string to create a customer table, as given above.

To add some data, such as when the shop wants to register a new customer, insert it using something like the following SQL:

insert into Customers (Name, Address, Telephone, email) values ("Sam Jones", "12 Letsby Avenue, SN7 2QN, UK", "07700-900411", "samjones49@mymail.not.fr");

This inserts a row into the Customer table, with the given values in the second set of brackets matched to the column names in the first set of brackets. The Java code to do this looks like this:

Connection conn = DriverManager.getConnection("jdbc:sqlite:./RobotShop.db"); 

Statement stmt = conn.createStatement();

stmt.executeQuery("insert into Customers (Name, Address, Telephone, email) values ('Sam Jones', '12 Letsby Avenue, SN7 2QN, UK', '07700-900411', 'samjones49@mymail.not.fr');");

As a little hack to see how this works, write a main method in a temporary class that creates a connection, and then runs statements to create a Customer table and put an entry in it:

public class Temp {
	 public static void main(String[] args) throws SQLException {
		 Connection conn = DriverManager.getConnection("jdbc:sqlite:./RobotShop.db");

		 Statement cstmt = conn.createStatement();
		     
		 cstmt.execute("CREATE TABLE IF NOT EXISTS Customers (Name varchar(20), Address varchar(100), Telephone varchar(15), Email varchar(15));");

		 Statement istmt = conn.createStatement();

		 istmt.executeQuery("insert into Customers (Name, Address, Telephone, email) values ('Sam Jones', '12 Letsby Avenue, SN7 2QN, UK', '07700-900411', 'samjones49@mymail.not.fr');");
	}
}

If you now look at the SQLite database using the SQLiteStudio I showed you in the video above - or whichever SQL administration tool you prefer - you should see a new Customer table with one row containing Sam Jones's details.

I've shown you here how to do it for customers - now try it yourself for the robot parts catalog! You need to create a table, and then add some entries to it.

Searching for Information in Your Database

Sifting through large datasets to find the nuggets of knowledge that inform business decisions is where database applications earn their keep - and only software engineers like you can make this happen! For instance, the shop might want to find all the robot motors with certain power output, or all the flashing lights that take a particular voltage, or all those tangle grommets with a specific under-related over-ratio.

I'll show you how to search the data (i.e., query it) in a relational database from Java.

Let's look at this in more detail.

Even simple queries can be useful. If the robot shop wants to run a promotion in a particular area, they will need to know which existing customers are in that area, with a query like this:

select * from Customers where Address like '%London%';

Or they might lookup to the details of a particular person, like this:

select * from Customers where Name like '%Martin Hill%';

To run a SQL query that returns a set of results, create a SELECT... WHERE statement and execute the query like above, but this time read the response from the  executeQuery  call:

	public void printCustomersWithName1(String name) throws SQLException {
		Connection connection = DriverManager.getConnection("jdbc:sqlite:./Customers.db");

		Statement stmt = connection.createStatement();

	    ResultSet rs = stmt.executeQuery("SELECT * FROM Customers WHERE name like '%"+name+"%';");
    	System.out.println("Found these people with name like '"+name+"': ");
	    while ( rs.next() ) {
	    	System.out.println("Name: "+rs.getString("Name"));
	    	System.out.println("Telephone: "+rs.getString("Telephone"));
	    	System.out.println("Address: "+rs.getString("Address"));
	    	System.out.println("Email: "+rs.getString("Email"));
	    	System.out.println("");
	    }

	    connection.close();
	}

Each next call at Line 8 loads the next matching row into the ResultSet. You can then read out the contents of the column that you are interested in (e.g. Name, Telephone) for that row. When you have all the information you want for that row, move onto the next by calling  next(), which returns true if there is another entry, and false if not.

There is a range of options available in the SELECT ... WHERE query. To show how they work, I have created an example dataset of 1,000 customers with some fictional, but realistic details in a table that looks like this:

You can download this SQLite database file from here, and place this in your working directory.

Let's explore some of the features of the SELECT ... WHERE statement.

You can use boolean logic to specify detailed search criteria such as a state like: 

select * from customers where name like %emily% and town like 'york'

It finds all those customers with a name that includes Emily and a town that is York.  You can add more AND or OR operators to build more specific or more general queries.  

There are also options to specify how the results are returned. You can specify the order in which results are returned, and group them by specific characteristics, like this:

select * from Customers where town like 'YORK' order by surname

This selects all those who live in York, and list them alphabetically by surname.

You can specify what you return; in the above examples, the asterisk tells the database to return all the details they have that match - i.e., the contents of all the columns for Customers. You can restrict this to just some columns by listing them:

select surname,postcode from Customers where town like 'YORK' order by surname

This returns just the surname and postcode for each matching customer. It means you need to change the columns that you read from the result set to match the surname and postcode given in the select statement instead of the Customer columns, as below::

Connection connection = DriverManager.getConnection("jdbc:sqlite:./RobotShop.db"); 

Statement stmt = connection.createStatement(); 

ResultSet rs = stmt.executeQuery("select surname,postcode from Customers where town like 'YORK' order by surname")

while ( rs.next() ) {

   System.out.print("Name: "+rs.getString(1)); //reads the first column

   System.out.println(", postcode: "+rs.getString(2)); //reads the second column

}

There are also a number of functions that you can apply to the results. For instance, you might just want to count how many people there are that match the criteria:

select count(*) from Customers where town like 'YORK'

This just returns the number of customers from York. You would read the results by getting the first column of the first result set entry, like this:

Connection connection = DriverManager.getConnection("jdbc:sqlite:./RobotShop.db");  

Statement stmt = connection.createStatement();  

ResultSet rs = stmt.executeQuery("select count(*) from Customers where town like 'YORK'")

rs.next();

System.out.println("count: "+rs.getString(1));

 You can also group results if you want to summarize different aspects of your dataset, such as to find out how many customers are in the various towns in the YO postcode:

select town, count(*) from Customers where postcode like 'YO%' group by town

Then reading each row (which corresponds to the town group) and the associated count, like this:

Connection connection = DriverManager.getConnection("jdbc:sqlite:./RobotShop.db");  

Statement stmt = connection.createStatement();  

ResultSet rs = stmt.executeQuery("select town, count(*) from Customers where postcode like 'YO%' group by town")

while ( rs.next() ) {

  System.out.println(rs.getString(1)+": "+rs.getString(2));

}

There are many more features available, but this should give you the starting skills for most day-to-day queries.

These examples use simple SQL, so you can see directly how the statements are submitted in Java. However, you should never take a string from an external source (such as the name argument in the method header), and insert it into a SQL statement.

This is because it creates a security vulnerability known as SQL injection. This is where people can inject - by accident or deliberately -  other SQL statements into the existing code. If the argument name contains the string 'banana; DROP TABLE Customers;', the resulting SQL statement to be executed is:

SELECT * FROM Customers WHERE name like banana; drop table Customers;

This is actually two SQL statements separated by semicolons. When it is executed, it will query the Customer table - and then the drop table Customers instruction will delete the Customer table. 😮

To protect against this, use the  PreparedStatement  object instead of  Statement. For these, lay out the SQL statement with question marks where you intend to insert arguments:

SELECT * FROM Customers WHERE name like ?

Then set the arguments to strings or values by position. The code for creating and executing a PreparedStatement looks like this:

PreparedStatement stmt = connection.prepareStatement("SELECT * FROM Customers WHERE name like ?;");
stmt.setString(1, "%"+name+"%");
ResultSet rs = stmt.executeQuery();

The PreparedStatement object carries out some safety checks that the arguments are valid and not dangerous. This makes the code longer, and in some ways more fragile as it is easy to mix up the positions, but the execution is more secure.

Configuring Code to Use Different Databases

You can write one application that runs on different machines. You might have an application you've written that you have delivered to run on several different customer sites. Maybe you imagined that once we finished the application for the Smiths' Robot Bits Shop, that you could sell it to other similar shops. These are likely to have different databases or database configurations, either because they already had them installed or because they have different specific performance or cost requirements.

The examples in the code above are based on a SQLite connection; the URL is given by  jdcb:sqlite:./{name of file}. If you want to connect to a database such as Postgres, you would need to change the code to use a URL to connect to a Postgres server:jdbc:postgres://mydbserver.com:667/mydatabase

However, you don't want to have different code for each customer and have to change it every time you build the system. Instead, separate the code from the configuration. That is, using the persistence techniques you saw earlier, store the differences in a configuration file at each customer site, and read in the differences to use in standard code.

For instance, you can use the Properties persistence library to read these settings. You could also create a text file application.properties that has this line in it:

jdbc.url=jdbc:postgresql://mydbserver.com/aDatabase?user=aUser&password=aPassword" 

Read your Property file application.properties, and use the setting as follows:

Properties p = new Properties();
p.load(new FileReader("application.properties"));
String url = p.getProperty("jdbc.url");

Connection conn = DriverManager.getConnection(url);

PreparedStatement stmt = conn.prepareStatement();
//do database stuff

You can see here that we read in the properties, then set the JDBC URI to the string read from the configuration file.

That means you can update the code, adding new features, say, and then install it the same way on all the customer sites. Each application on each site reads the applicatoin.properties at that site and so uses the JDBC URI for that site.  

Working with Errors and Logging

To keep the examples clean, I have deliberately left out the safety code that deals with problems. The configuration is likely to be different on different deployments, so configuration spelling mistakes, installation glitches, and unexpected setups can mean your application fails in different ways on different sites.

For instance, if you haven't started your database server, then the JDBC connection to it will fail. Similarly, if you haven't set up your login details, then your connection will fail. The messages that are produced may not be helpful. It can be useful, therefore, to report extra details if you have problems connecting. When you trap exceptions to log them, also add details about the connection you're trying to make - such as the URL that you are passing to JDBC:

Connection conn = null;
try {
    conn = DriverMaanger.getConnect(url);
}
catch (SQLException sqle) {
    logger.severe(sqle.getMessage()+" attempting to connect to "+url);
    return;
}

String sqlquery = "SELECT all FROM Customers";
try {
    PreparedStatement stmt = conn.prepareStatement("SELECT all FROM Customers");
    
    //do database stuff
}
catch (SQLException sqle) {
    logger.severe(sqle.getMessage()+" attempting to execute query '"+sqlquery+"' on connection "+url);
    return;
}
finally {
    try {
		conn.close();
	} catch (SQLException e) {}
    
}

Again, be aware of security issues when reporting errors. If the login fails, it can be useful to report what login account was being used (as this helps with mismatches). However, leaving such login details in log files is likely a security breach.

Try It Out for Yourself!

In this chapter, you learned how to use JDBC and simple SQL statements to interact with a relational database for the shop's customer list. Now you can do the same for the robot part list.

Use the above code to guide you in creating a RobotPart table in the same database using the CREATE command, with suitable columns for your robot parts.

You can create a few entries by using the INSERT command, and search them by using the SELECT command. Don't forget to use the PreparedStatement to keep the querying secure.

Finally, add some try/catch traps for SQLExceptions, and add whatever information you think will be useful to help debug any problems.

Let's Recap!

You learned how to use your JDBC connection to interact with a relational database from Java applications. This included:

• Executing SQL statements that set up tables and insert data into them.

• Querying the database using SQL SELECT ... WHERE statements and reading the results. 

You learned some of the SQL features - such as COUNT and GROUP BY - that you might use for common applications. You saw how you might configure your applications so that you can use the same code in different environments. And you learned error reporting to help investigate the common problems you might find when setting up JDBC connections.

In our scenario, however, we want to store customers and robot parts in the database. How do we relate our objects to the database tables? Let's do that next!