Overview of This Lesson

Refresh this page because I am probably still making changes to it.

Databases allow you to not only store data, but to organize it. You can create relationships between tables, which helps to keep data organized and assists with data integrity.

Resources for this Lesson

In-Memory Databases

To start the module on database access, we'll be working with in-memory databases. This means the entire database is kept in RAM or memory, which therefor means the database volatile: when the application shuts down or terminates, the in-memory database and all the data disappears. This means that the database doesn't consume resources running when no one is using the application.

In-memory databases are also much faster than using a database server, making them a good choice for POC (proof of concept) when you just want to quickly show how the database-driven part of the application will work. In-memory databases are also an excellent choice for performing unit tests, for data storage used in interactive gaming applications and online shopping carts, and other network applications that require temporary storage of relational data.

For example, if you are writing an application for an online store, a good use of an in-memory database would be the online shopping cart: the list of items in the user's cart is temporary: the list of items is removed as soon as the user checks out and they don't need the cart anymore.

If your solution requires more persistent data, such as the user's order history, then you would go with a database server such as MySQL to persistently store the data. We'll learn how to do this later in the course. For now, we'll learn how to work with databases in a Spring application by using H2.

JDBC

JDBC stands for Java Database Connectivity. This is the technology we use to connect Java applications (including Java web applications) to a database. This allows you to store persistent or temporary data for use in your applications.

H2

The H2 Database Engine is an open source RDBMS that is written in Java, so it was made to be embedded in Java applications.

H2 can run on client-server model and doesn't use a lot of resources, which is a huge advantage. It's already part of Spring Boot and ready to go, so no extra installation is required to use it, you just need to add the dependency to your project and configure it with a few properties in your application properties file.

H2 also comes with a browser-based console, which is handy when you need to check your tables and make sure that your database access code is actually working.

Creating an Application with H2

Creating an application that uses an in-memory database is simple, so let's try it. Go ahead and create a new application project and add dependencies for Spring Web, DevTools, Lombok, Thymeleaf, and add two new dependencies:

It's necessary to configure your application so that it knows where to find the database and how to access it. Open the application.properties file and add the following statement (recall that this file is in the /resources directory):

spring.h2.console.enabled=true
spring.datasource.url=jdbc:h2:mem:testdb
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=

Once you've saved your changes, run your application. Don't worry that you don't have any html pages or a controller, yet. Go to your browser and type the address 

localhost:8080/h2-console
testing connection and then connecting to database
Test your connection. When successful, click Connect button.

Double check that the driver class and JDBC URL are correct, and that the user name says "sa" and the password is blank.

Click the Test Connection button to test your connection. If you see the message in green at the bottom of the screen, everything is working fine. If you see anything else or the message doesn't appear, something isn't working: double-check that you typed all the values correctly in your application.properties file and in this dialog box.

Once you connect, you'll see an interface that looks a lot like PHPMyAdmin! In the top-left you can see the name of your database.

The node called "INFORMATION_SCHEMA" contains various tables that are part of H2. Don't touch them! H2 needs these in order to work and changing them might break many things.

The "Users" node is self-explanatory. If you know how to add users, feel free to do so!

Your database is currently empty. You could go in and add tables and data using the web console, but it's actually better to do this when your application starts up. If a developer had to change any of the schema of your database, you only need to edit the SQL code in your project instead of manually editing everything in the console.

Adding Tables and Data

When using an in-memory database, the best practice is to have any schemas and data added at program startup. To do this in a Spring application, you simply add 2 files to your src/main/resources directory:

When your application starts up, it will look for those 2 files and, if it finds either one of them, it will execute the code within them on whatever database you've configured in your application.properties file!

Adding a Table

Start by adding a schema file:

  1. Right click src/main/resources and select New > Other
  2. In the filter box type sql or browse to the category "SQL Development"
  3. Select "SQL File" and click Next.
  4. You must name the file schema.sql.

In the editor window for the schema.sql file type the following query to create the "teams" table (in a previous exercise we used an array and an enumeration to store the names and cities of hockey teams): 

CREATE TABLE teams (
	id VARCHAR(3) NOT NULL PRIMARY KEY,
	city VARCHAR(255),
	name VARCHAR(255)
);

Here we are creating a new table called "teams" with 3 columns:

Did it work? Go back to your browser and click on the REFRESH button in the top button bar (it's the 2nd button with 2 little arrows, right above the name of your database in the top-left)

showing refresh button and team table with columns
Click the Refresh button and then expand teams

You might be prompted to reconnect, that's fine: just click the Connect button again. You should now see the TEAMS table in the top-left, under your database name!

Expand the node for TEAMS and you'll see your three columns for id, city, and name.

Adding Rows to a Table

If you want to populate your teams table with teams, you can create the SQL statements in a data.sql file.

Add a new SQL file to src/main/resources and add the INSERT statements to add teams to your teams table. Note that your text/string values must use single-quotes, not double-quotes.

INSERT INTO teams (id, city, name) VALUES ('CGY', 'Calgary', 'Flames');
INSERT INTO teams (id, city, name) VALUES ('EDM', 'Edmonton', 'Oilers');
INSERT INTO teams (id, city, name) VALUES ('MTL', 'Montreal', 'Canadians');
INSERT INTO teams (id, city, name) VALUES ('OTT', 'Ottawa', 'Senators');
INSERT INTO teams (id, city, name) VALUES ('TOR', 'Toronto', 'Maple Leafs');
INSERT INTO teams (id, city, name) VALUES ('VAN', 'Vancouver', 'Canucks');
INSERT INTO teams (id, city, name) VALUES ('WPG', 'Winnipeg', 'Jets');

Note that if there are any errors in your SQL syntax, you'll see them in the console output window every time you save a file in your project.

Once you've saved your changes, go back to the console and take a look:

  1. Click on the TEAMS node, and you'll see the SQL statement "SELECT * FROM teams;" appear.
  2. Click the RUN button to execute the SELECT query.

If you don't see your data, click the REFRESH button and try again.

showing the run button and how the query executes
Click the Table name, then Run the query.

Exercises

1. Add the necessary SQL to your schema.sql file to create a Books table and Genres in your database. Your tables should have the following schemas:

Table Name: books
Column NameTypeNotes
isbnbigintPrimary Key, required
titlevarchar(255)required
authorvarchar(255) 
pricedecimal(6,2) 
genreintrequired
Table Name: genres
Column NameTypeNotes
idintPrimary Key, auto incremented
genrevarchar(255)required

Check your database using the H2 Console and make sure everything is correct.

2. a. Add the necessary code to your data.sql file to add the following genres to your genres table:

Feel free to edit the genres or add extra genres, but you'll need at least 7.

Check your database using the H2 Console and make sure all your genres have been added.

2. b. Add the necessary code to your data.sql file to add at least 3 books to the books table.

Check your database using the H2 Console and make sure all your books have been added.