In this lab you will implement the database version of the Time Entry Repository using Spring JDBC Template.
After completing the lab, you will be able to:
You must have completed (or fast-forwarded to) the
Backing Services and Database Migrations lab.
You must have your pal-tracker application associated with the
migrations-solution codebase deployed and running on
Tanzu Application Service,
and your local and Tanzu Application Service databases must have
been migrated.
In a terminal window,
make sure you start in the ~/workspace/pal-tracker directory.
Pull in unit and integration tests:
git cherry-pick jdbc-start
The goal is to get your tests passing by the end of the lab.
If you get stuck within this lab,
you can either
view the solution,
or you can
fast-forward to the jdbc-solution tag.
You can also look at the Hints for some more assistance.
Add the following dependencies to the build.gradle file:
dependencies {
implementation 'org.springframework.boot:spring-boot-starter-web'
+ implementation 'org.springframework.boot:spring-boot-starter-jdbc'
+ implementation 'mysql:mysql-connector-java'
testImplementation 'org.springframework.boot:spring-boot-starter-test'
}
Review the .github/workflows/pipeline.yml pipeline declaration
additions from the jdbc-start cherry-pick.
You will see a Create test database section has been added to the
build-and-publish job to set up the CI test database.
It uses a new Flyway task you will add next to run it through Gradle instead of the command line.
Configure Flyway in your build.gradle file as follows:
Add import for Flyway as the first line in the build.gradle file:
+ import org.flywaydb.gradle.task.FlywayMigrateTask
Add the flyway plugin to the plugins closure:
plugins {
id 'org.springframework.boot' version '2.5.3'
id 'io.spring.dependency-management' version '1.0.11.RELEASE'
+ id "org.flywaydb.flyway" version "7.13.0"
id 'java'
}
Define connection details you can use within the build system to connect to the local databases you created earlier.
test {
useJUnitPlatform()
}
+ def developmentDbUrl = "jdbc:mysql://localhost:3306/tracker_dev?user=tracker&useSSL=false&useTimezone=true&serverTimezone=UTC&useLegacyDatetimeCode=false"
bootRun.environment([
"WELCOME_MESSAGE": "howdy",
+ "SPRING_DATASOURCE_URL": developmentDbUrl,
])
+ def testDbUrl = "jdbc:mysql://localhost:3306/tracker_test?user=tracker&useSSL=false&useTimezone=true&serverTimezone=UTC&useLegacyDatetimeCode=false"
test.environment([
"WELCOME_MESSAGE": "Hello from test",
+ "SPRING_DATASOURCE_URL": testDbUrl,
])
Configure the flyway task:
+ flyway {
+ url = developmentDbUrl
+ user = "tracker"
+ password = ""
+ locations = ["filesystem:databases/tracker/migrations"]
+ }
Configure the testMigrate task to derive from the flyway task,
but override the url with the testDbUrl you configured in
previous step:
+ task testMigrate(type: FlywayMigrateTask) {
+ url = testDbUrl
+ }
This is the Gradle task executed by the pipeline to migrate your test database.
Review the build.gradle file below to verify your datasource
and migration build configuration.
git show jdbc-solution:build.gradle
In the previous lab you manually bound the new tracker-database
service to the application.
Add the following lines to the manifest.yml to capture
the information about that binding so that it is not lost if
you re-create the application:
services:
- tracker-database
The services label should be at the same level of indentation
as the routes and env labels.
Create a new class called JdbcTimeEntryRepository that:
TimeEntryRepository interface.DataSource as a constructor argument.
Make sure to use the DataSource interface type,
not the concrete MysqlDataSource class type.Use the JdbcTimeEntryRepositoryTest to guide your
implementation.
Keep the following tips in mind:
Use the DataSource to construct a JdbcTemplate object and
store the JdbcTemplate object to a private field.
In the create method use the JDBCTemplate#update() method
to persist the TimeEntry object to the database.
Retrieve the newly created record from the database using the
generated ID (the ID can be retrieved using a
GeneratedKeyHolder)
Take a look at our solution if you need help:
git show jdbc-solution:src/main/java/io/pivotal/pal/tracker/JdbcTimeEntryRepository.java
In PalTrackerApplication modify the method that returns a
TimeEntryRepository bean to return an instance of
JdbcTimeEntryRepository.
Add a set-up method to TimeEntryApiTest.
@BeforeEach
public void setUp() throws Exception {
MysqlDataSource dataSource = new MysqlDataSource();
dataSource.setUrl(System.getenv("SPRING_DATASOURCE_URL"));
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
jdbcTemplate.execute("TRUNCATE time_entries");
TimeZone.setDefault(TimeZone.getTimeZone("UTC"));
}
git show jdbc-solution:src/test/java/test/pivotal/pal/trackerapi/TimeEntryApiTest.java
Run the build to make sure everything still passes.
./gradlew clean build
Commit and push your code to GitHub, then let your pipeline deploy the new application.
Now that you have completed the lab, you should be able to:
Verify whether or not the VCAP_SERVICES environment variable is
set in the current running pal-tracker container:
SSH to the running pal-tracker:
cf ssh pal-tracker -i 0
Determine if VCAP_SERVICES is set:
env | grep VCAP_SERVICES
Do you see the environment variable set in the running container? Why, or why not?
In this lab you saw the Java buildpack auto-reconfiguration feature
in work.
It replaces the Spring Boot auto-configured DataSource with one it
creates from the service binding found in VCAP_SERVICES.
One issue you may have noticed if you reviewed the app logs after deploying your JDBC code changes is this warning:
2020-12-18T13:03:59.98-0600 [APP/PROC/WEB/0] OUT 2020-12-18 19:03:59.988 INFO 14 --- [ main] o.c.reconfiguration.CloudServiceUtils : 'dataSource' bean of type with 'javax.sql.DataSource' reconfigured with 'tracker-database' bean
2020-12-18T13:04:00.02-0600 [APP/PROC/WEB/2] ERR Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
But you did not see this when you ran this locally as a Spring Boot app!
A trade-off of using the default Tanzu Application Service Java
buildpack auto-reconfiguration behavior is that it hijacks backing
service configuration,
and replaces with the associated Spring beans configured separately by
the auto-reconfiguration plugin.
This includes any DataSource configuration you
(the application developer or application operator) may have provided.
The reconfiguration behavior is scanning and picking up the deprecated
driver instead of the newer driver that is supplied in the
mysql-connector-java dependency.
See here for more information.
There are a couple of scenarios under which you may choose to suppress the auto-reconfiguration:
You can turn off the auto-reconfiguration behavior as follows:
cf set-env pal-tracker JBP_CONFIG_SPRING_AUTO_RECONFIGURATION '{ enabled: false }'
This will suppress auto-configuration of your datasource via
VCAP_SERVICES during the next push and staging of your application.
You will also need to configure your datasource or datasources by
parsing VCAP_SERVICES for the associated service binding credentials,
or you can also use the
VCAP processor provided by Spring Boot.
Try out reimplementing time entry persistence with Spring Data JDBC.
Read through the Spring Data JDBC reference for help getting started.
Try out reimplementing time entry persistence with JPA.
Read through the Spring JPA guide for help getting started.
If you completed both of the Spring Data JDBC and JPA extras, how did both of the solution compare to the JDBC Template solution?
DataSource from?It is obvious that, ultimately,
the application will need a DataSource instance,
connected to a real database.
It is much less obvious how to create that.
The good news is that you, the developer,
do not need to create the datasource at all —
Spring Boot will do that for you!
In order to do that it needs several things to be true:
Spring Boot auto-configuration is enabled, which it is by default.
There is a valid value for the configuration property
spring.datasource.url (in this case provided via the environment),
or the app is running in a cloud environment
(such as Tanzu Application Service) where information on bound
database instances is available.
There is an appropriate JDBC driver available on the classpath.
If these conditions are true, Spring Boot will create a datasource bean and place it in the application context.
DataSource into the JdbcTimeEntryRepository?As well as Spring auto-wiring parameters into bean class constructors,
it will also auto-wire the parameters of methods annotated with @Bean.