Chapter 6 Demo

Spring JDBC

It's time to hook up our application to a real database instead of a fake, in-memory implementation. We'll need to add some dependencies to the project, create some beans that connect to the database, and a simple fetch query from the database will be provided.

Preparing the H2 Database

For Instructors and Self-Taught Students

The H2 database used throughout the rest of this training material can be hosted on your local machine, a server that has Java installed, or even a Raspberry Pi if one happens to be sitting around. If you are an instructor, be sure that you a machine whose IP address or domain name is easily accessible by all students.

• Copy the H2 Jar (download from here, or use the provided Jar in the codebase) to the shared machine.

• From the shared machine, create an empty database directory and start the H2 server with the TCP protocol

  $ mkdir -p ~/learn-spring/db
  $ java -jar /home/david/Desktop/h2-1.4.192.jar -tcp -tcpAllowOthers

  This will start an H2 server on port 9092 with an administrator account named sa and blank password.

• Add each of your student's user IDs (or names, or some unique string) to the learn_spring_ddl_values.json JSON file. Remove any examples that may be in the file to start with.

• Load all students' user accounts and schemas via the H2 database generator application (included in the codebase, also found on GitHub here)

  $ java -jar generate-h2-database-0.0.1-SNAPSHOT.jar \
      --host=<YOUR_SHARED_HOST> \
      --database=~/learn-spring/db/pokemon \
      --template=./learn_spring_ddl_template.sql \
      --values=./learn_spring_ddl_values.json

• To start the server and make it accessible both through TCP to external hosts (anyone other than localhost) and start the web application (recommended if you or your students do not have a standalone, multi-client database tool such as JetBrains DataGrip), run the following commands:

  $ echo 'webAllowOthers=True' > ~/.h2.server.properties
  $ java -jar software/h2-1.4.192.jar -tcp -tcpAllowOthers -web

• If the server is accessible to the general Internet (not behind a firewall), be sure to shut it down when not in use, or reset the passwords to something secure.

Learn H2's Web Interface

When H2 is running with its default settings, a web application will be running on port 8082 that provides a graphical interface for running queries against the database. Access this interface by pointing a web browser at http://<hostname>:8082/. Once there, you will be prompted for four pieces of information:

• Driver Class: org.h2.Driver
• JDBC URL: jdbc:h2:tcp://<hostname>:9092/~/learn-spring/db/pokemon;SCHEMA=<USERNAME>
• User Name: Provided by instructor
• Password: Provided by instructor

Once logged in, you'll be able to find a schema with your username on the left-hand side of the interface. Expanding your schema will provide you a list of two tables: POKEMON and POKEMON_LOCATION. Clicking on one of the tables once will place a SELECT query into the statement input box, which will let you click the "Run" button to view the table contents. Take some time to familiarize yourself with this interface. You should find one row in each table.

Demo 1 - Part 1

Replace the PokemonDao's .get(...) method with a query to the database, and implement a new .update(...) method. New Spring beans and Maven dependencies will be necessary, as well as lots of new code. This is a heavy demo, so get ready.

1. Write a failing unit test for the .get() method
   1. Three new dependencies will be needed to connect to and test the database. The JDBC driver, Spring's JDBC component which will help us interact with the database, and Spring's testing utilities:
   • com.h2database:h2 (scope: compile)
   • org.springframework:spring-jdbc (scope: compile)
   • org.springframework:spring-test (scope: test)
   • Our first integration test can now be created. The @ContextConfiguration method can be combined with @RunWith(SpringJUnit4ClassRunner.class) to load a Spring container from a test class. In proper test-driven-development style, we'll create a single test in this class which asserts that our DAO is capable of retrieving the one Pokemon from the database table whose ID is 1, HP is 50, and attack is 10.
   • A quick run of the test shows that this test fails. Now we're ready to start fixing things.

• Fix the failing unit test
  1. We'll start this off by adding the JdbcOperations interface as a dependency of PokemonDao. Use constructor injection to pull mark it as a required dependency.
  • The existing implementation of PokemonDao.get(final int id) can be wiped clean. It should be re-written with a call to the JdbcOperations field and its .queryForObject(...) method.
  • Attempting to re-run the test will show a compiler error in the DaoConfig class because PokemonDao's constructor is unfulfilled. Create a new bean for the JdbcOperations dependency (this is an interface, and the JdbcTemplate implementation is a solid choice for a concrete class).
  • You'll immediately find that JdbcTemplate also has a required constructor argument: DataSource. The instantiation of this bean will vary drastically based on your exact application and backing database, so it's one of the few places in this lab where you will not be able to copy and paste this code into a production project later. For now, use H2's org.h2.jdbcx.JdbcDataSource and provide it with three pieces of information: URL, user, and password. Each of these pieces of information should be provided as properties which can be retrieved from PropertyConfig (you'll need to add PropertyConfig as a dependency of DaoConfig).
  • Re-run the unit test to ensure it now passes.
• Write another failing unit test for the .update() method
  1. Create a new test method for the "update" operation. This should test get the Pokemon with ID = 1, set its HP and attack to something new, and invoke the update() method, and then query the database a second time with the .get() method to ensure the value was saved. Running the test is not necessary since any attempted invocation of the update() method should throw a compiler error.
• Fix another failing unit test
  1. Create and implement the update() method on the DAO
  • Ensure both tests still pass

Demo 1 - Part 2 - Transactions

Testing the .update() method was likely a bit tedious. You may well have found that you have to repeatedly go back to H2's web interface to revert the change that your unit test applied. Spring's @Transactional annotation is here to rescue you from this burden and make your tests fully repeatable.

1. Begin by adding two annotations to the integration test class: @Transactional and @Rollback.

• Neither of these annotations will do anything without the appropriate bean in the Spring container, so create one new bean of type PlatformTransactionManager. Note that PlatformTransactionManager is an interface with a few different implementations. DataSourceTransactionManager is a good choice for this use case (and most use cases), so go with that one. The bean should be declared with type PlatformTransactionManager though.
• Run your unit tests a couple more times to ensure they run and return the database to its previous state when the tests finish

Demo 2 - DbSetup, a Testing Utility

Transactions were a big step forward, but we still are dependent on the database containing specific information prior to invoking our tests. DbSetup is a database testing tool that is here to rescue us.

1. Utilizing DbSetup will require adding a new Maven dependency to our project: com.ninja-squad:DbSetup. It should be added to the project with its scope set to test. At the time of this write-up, version 2.1.0 is the latest, and is tested with these labs to work well.

• By default, DbSetup does not interact with Spring's transactions, and that's a real pity. Thankfully, the Internet has come to our rescue by providing a TransactionAwareDestination. I have copied it into this project with nothing more than formatting fixes (whitespace, curly braces, and prefixing various invocations with this.). You do not need to know anything about this class, other than it does some magic. Please download this version to your computer and save it under <project root>/src/test/java/com/uprr/app/tng/spring/dao/TransactionAwareDestination.java.
• The basis of DbSetup is simple: create an instance of DbSetup and provide a list of operations into its constructor that should be performed on the database at the start of the test, and then invoke the .launch() method prior to running the test. This should be done before every method in the test class. If the operations are identical for every test, then the instantiation and invocation of DbSetup can be performed in JUnit's @Before method, otherwise they will need to reside in each method individually.
• Use the static methods on com.ninja_squad.dbsetup.Operations to help write the operations necessary for each test.