Database Connectivity Now and Then

Spire and Duke adding stuff to a database.

I remember back in the old (Java) days, when we were sitting up late nights and experimented a lot with Java and databases. In the beginning of the Java era, there was not much support for database connectivity and so we had to basically write your own database classes and handle ResultSets, Connections and SQLExceptions by ourself.

Nowadays, we expect the simple things just to happen! Suppose that we have an existing database and we want to add or update information in it using a Java application. How can we do that in a simple way without having to write a lot of boilerplate code?

I have contributed a lot to the Java 8 Open Source project Speedment, that can be used to very easily extract Java code from existing database schemas and start coding applications directly.

Let's take it for a spin.

Example

Let's say we have a MySQL database table that is supposed to contain data on various countries. The table could look something like this:

mysql> explain country
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int(11)     | NO   | PRI | NULL    | auto_increment |
| name       | varchar(45) | YES  | UNI | NULL    |                |
| local_name | varchar(45) | YES  |     | NULL    |                |
| code       | int(11)     | YES  |     | NULL    |                |
| domain     | varchar(10) | YES  |     | NULL    |                
+------------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

Let us further pretend that we have the task of populating the table with a few countries and see how that can be solved.

Setting up the Speedment Project

In order to set up a project with Speedment, we need to include a few lines in our POM.xml file, connect to the database and generate code. Read more on how to do this here!.

Also, check out this film how easy it is:

Initializing the Database Connection

Now that we have our database domain model generated automatically, we can start with the actual coding for inserting data into the database. First, we need to setup our Java 8 database project like this:

// Setup
Speedment speedment = new JavapotApplication().withPassword("javapot").build();
Manager<Country> countries = speedment.managerOf(Country.class);

The JavapotApplication class was generated automatically from the database schema and contains all meta data (like columns and tables) of the database. Note that we manually need to provide the password since this is not stored in the meta data model (for security reasons). The countries variable is a "handle" for the table we are about to work with.

There is really no "magic" going on with the generation. We can see all the generated Java files in clear text and we can change them or introduce our own versions if we want.

Inserting Data in the Database

The newInstance() method creates a new empty Country object and then we just use the setters to initialize the country. After all parameters are set, we call the persist() method to store the object in the database. If there is an error during the database insert, a SpeedmentException will be thrown, allowing you to examine why (for example if you are trying to insert two countries with the same name).  I intentionally picked the wrong country call code for Sweden (förlåt Sverige) so that we can learn how to update data in our database too.

Updating Data in the Database

If you want to update an existing row in the database you can do it like this:

countries.stream()
                    .filter(Country.NAME.equal("Sweden"))
                    .findAny()
                    .ifPresent(c -> c.setCode(46).update());

This will create a Stream with all the counties that have the name "Sweden" (which, evidently, is only one country) and then it will try to find that country and if it is present, it will take that country and set the code to 46 (which is the correct calling code for Sweden) and then it will update the selected country in the database. It is important to understand that even though our country table might contain a large number of countries, it will only include those countries in the stream that are satisfying an equivalent query of "select * from country where name='Sweden' " in this case.

Now our database looks like this:

mysql> select * from country;
+----+----------------+----------------+------+--------+
| id | name           | local_name     | code | domain |
+----+----------------+----------------+------+--------+
|  1 | United States  | United States  |    1 | .us    |
|  2 | Germany        | Deutschland    |   49 | .de    |
|  3 | United Kingdom | United Kingdom |   44 | .uk    |
|  4 | Sweden         | Sverige        |   46 | .se    |
+----+----------------+----------------+------+--------+
4 rows in set (0.00 sec)

Success! Mission accomplished!

Contributing 

Read more about Speedment Open Source on www.speedment.org and that's the place to be if you want to learn more things like how the API looks like and how you use Speedment in your projects. Speedment is here on GitHub. You can contribute by submitting comments on gitter or download the source code and create pull requests with your own code contributions.

Conclusions

With Java 8, you can easily write database applications with almost no extra manual code work. There are tools that automatically can extract your domain model from a database schema.

These days, we Java programmers can can put more time on the actual problem (and perhaps get some more well deserved sleep) instead of fiddling around with basic database functionality.