• 6 hours
  • Medium

Free online content available in this course.

course.header.alt.is_video

course.header.alt.is_certifying

Got it!

Last updated on 4/15/20

Get Started With Relational Databases by Configuring JDBC

You'll remember in the previous part of the course we wrote code to persist data - to store stuff - directly to disk as text files. That was fine for simple persistence, but when building applications for use by more than one person, it can cause problems.

Storing large datasets this way is clumsy: sharing can cause data loss. Data has to be converted to and from strings; there is little adequate security, and persisting data in large text files can seriously degrade the performance of your applications.

You could solve all this yourself, but that's a lot of work! Instead, let's use systems that already deal with these problems: relational databases.

Understanding Relational Databases

Relational databases are software services that store data items and the relationships between them. They have been developed over decades, and many have been developed for different uses, specializing in particular features, size, performance, scale, simplicity, etc.

The vast majority provide an application programming interface (API) so that you can write applications that use these services. These include a way to search the data, and usually, this is a querying language that conforms, at least somewhat, to the standard Structured Querying Language (SQL).

You can take advantage of these databases by using Java's Database Connection (JDBC), which tries to provide a single way of accessing these APIs. JDBC acts as a bridge between the database and the Java virtual machine that runs your application code:

JDBC: Connecting Java and Relational Databases
JDBC: Connecting Java and relational databases

In this chapter, you will install one of these, and then access it from your Java application. Once you have learned how to do this, you can connect any number of application instances to the same database and so share the same data effectively, safely, and securely.

Sharing a single database with several Java Apps
Sharing a single database with several Java apps

Choosing a Type of Database

There are many database providers, ranging from large enterprise systems to small, simple utilities, from table-based centralized relational databases such as Oracle or PostgreSQL, to distributed file archives such as Hadoop.

Each of them offers different features for different environments. For example, Postgres arranges data in a set of tables in a central location, while Hadoop stores data across many computers in whatever form it is presented. Hadoop can be much quicker at aggregate queries ("what is the average sales price of item X") because it can farm out the query to many computers to work on at once. On the other hand, Postgres can be much quicker at comparison queries ("what is the average difference in sales to purchase price of item X") because it doesn't have to move data between computers.

Understanding these trade-offs - and many others - can become quite a vast topic, and depend heavily on the requirements and environments of your application. We'll look at some of these areas to explore at the end of the course.

For now, the robot shop needs to store customer details which you can define reasonably well. That is, you can define what data you want to store about customers (name, address, etc.) in a structure; in our case, the person object that you have already seen. (By contrast, unstructured data could be free text, such as this course chapter). The shop's customer list can be centrally located so that you have a single master copy of it and a single place to update each customer's details. The same applies to the robot part catalog.

All this suggests that a relational database is a suitable choice. The size of the shop's customer list and catalog are reasonably small, and the rates of updates are reasonably low, so you can take advantage of simple relational databases that are easy to install and configure. One suitable and straightforward database for the shop is SQLite, which can store all the data in one file on the disk - so let's use this. If you want to follow along with the examples here, you can install it too. If you feel adventurous, I would recommend the PostgreSQL open-source database to learn more.

Installing a Database

Most of these databases include an application or data server, and a separate JDBC driver JAR file that you include in your application classpath to connect to the database. In most cases, you will need to download and install each one separately.

To keep things simple, we will use the self-contained SQLite library. This includes both the database system and the JDBC driver in one JAR file, and you can use it by downloading that file and including it in the classpath.

Configuring JDBC

Each JDBC driver knows how to connect to its database types. In turn, your application needs to be able to find the right driver for the database.

All JDBC connections use the same programming interface. When creating a connection, you provide an argument that specifies the database that you want to connect to. This argument, given as a Universal Resource Identifier (URI), includes the scheme (JDBC), the database type (i.e., Postgres, SQLite), where to find the database (the host and path), and any other connection parameters required (such as login details).

For example, for a locally-installed Postgres database, you would make a connection using code something like this:

    java.sql.Connection conn = java.sql.DriverManager.getConnection(
        "jdbc:postgresql://localhost/aDatabase?user=aUser&password=aPassword"
    ); 

Let's break this down:

URI Breakdown
URI Breakdown

The SQLite examples used here make a connection like this:

    java.sql.Connection conn = java.sql.DriverManager.getConnection(
        "jdbc:sqlite:./Customers.db"
    ); 

Which is broken down like this:

An example JDBC URI with callouts describing each element
SQLite JDBC URI breakdown

Once you've installed your chosen database, and added the associated JDBC driver JAR file to your classpath, check it works for you! The easiest way to do this is to create a class with a main method that makes a connection like the above:

    public static void main(String[] args) throws SQLException {
        java.sql.DriverManager.getConnection(
                "jdbc:sqlite:./Customers.db"
            ); 
    }

You can run this as an application to test it out. Typically, if you get an error, it will be something like this:

Exception in thread "main" java.sql.SQLException: No suitable driver found

If the JDBC driver JAR file is not on the classpath, check for spelling mistakes and that it has the right path.

Let's Recap!

In this chapter, you learned how to install and configure a Java Database Connector that gives your code access to powerful relational databases. You can use these to persist your own application data, and you can also use them to access other datasets provided by other people in their own databases.

Remember that:

  • Installing databases and connecting them to JDBC can vary between providers and versions, so follow the instructions in the documentation. 

  • If the installation fails, find and correct Java configuration problems, such as classpath settings.  

Now let's start using this connection!

Ever considered an OpenClassrooms diploma?
  • Up to 100% of your training program funded
  • Flexible start date
  • Career-focused projects
  • Individual mentoring
Find the training program and funding option that suits you best
Example of certificate of achievement
Example of certificate of achievement