Chapter 6 Lab

Spring JDBC

The labs in this chapter will give you the experience you need to learn how to use databases. If you are self-taught, the instructions in src/main/resources/DatabasePreparation teach you how to prepare an H2 database for use with this course.

Instructions

Lab 1

Some database queries and test cases have been written for you. Add the remaining database CRUD operations and tests.

1. Implement a create method which accepts a Pokemon and returns the newly created Pokemon ID.
   • Write a test case for the create method described above.
   • Your new create method should acquire a the new Pokemon ID from the database's sequence generator. The following SQL query will return the next available Pokemon ID. Reference the documentation for the JdbcOperations interface to find an appropriate method for invoking this statement.

     SELECT POKEMON_ID_SEQ.nextval FROM DUAL

   • Use the Pokemon ID that you acquired from the previous query to write an insert statement. Again, choose the appropriate method to use from the JdbcOperations interface.
   • Ensure your new test is passing.
2. Implement a delete method which accepts a Pokemon ID and has a void return type.
   • Write a test case for the delete method described above. There is a foreign constraint from the (yet unused) POKEMON_LOCATION table to the POKEMON table that will prevent you from deleting the only Pokemon in the POKEMON table. For this reason, it is recommended that your new test method create a new Pokemon, then assert that the Pokemon was successfully created, then delete the Pokemon and assert that the deletion passed.
   • Choose and invoke an appropriate method on the JdbcOperations interface to use for deletion of data.
   • Ensure your new test is passing.

Lab 2

In this lab, you are going to update your DAO to use named parameters.

1. Replace usage of JdbcOperations with NamedParameterJdbcOperations
2. Peruse the API documentation for NamedParameterJdbcOperations and learn what new methods are at your disposal.
3. Update your DAO implementation to use a named parameters (hint: make use of Guava's ImmutableMap.of(...) method).
4. Ensure your existing unit tests still pass.

Lab 3

You just completed the PokemonDao in its entirety. Now you will do the same thing for PokemonLocationDao.

1. Update the create() method
   • Write a test case for the create method described above.
   • Update the create method to invoke the database, rather than generating a random location
   • Ensure your new test is passing.
2. Update the getId() method
   • Write the test case for the method described above
   • Update the getId() method to invoke the database, rather than referencing the internal HashMap.
   • Ensure your new test is passing.
3. Now that the data has been fully persisted, we should no longer be using the command line to generate data anymore.
   • In HumanInterface.run(), remove the automatic generation of random pokemon and the delete unused code.