Persisting data to MySQL with Spring Data JPA

With the addition of JPA entities to the weather data collection project I’ve been writing about recently, all of the pieces are finally in place and it all works as I had intended. In this final article about the project, I’ll walk you through what I did to get Spring Data JPA set up and working so that the weather data retrieved from the public weather API could be easily persisted to a MySQL database.

Throughout this article, you’ll see that I refer to JPA and Hibernate somewhat interchangeably. A bit of background is probably in order here. JPA (Java Persistence API) is the name of a specification that defines how how a Java application can persist data to a database. Hibernate is a standard (arguably the most popular) implementation of that specification. The best practice is to leverage the JPA classes in your code and let Spring and Hibernate take care of the details under the covers. Hibernate does have some potentially interesting features you can leverage by specifically using the Hibernate implementation classes in your code. Just know that if you do, you’re bound to that specific JPA implementation. You can read more about this topic over at Baeldung.

I started with the addition of the appropriate Spring Boot dependencies to our build.gradle file. All it took was a single line.

 implementation 'org.springframework.boot:spring-boot-starter-data-jpa'

Next, I added the appropriate configuration settings to the Spring application.properties file.

spring.datasource.url=jdbc:mysql://<host>:3306/<database name>
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.username=<db username>
spring.datasource.password=<db password>

spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true

The first four properties are required for Spring to properly configure the datasource. Just to get it working, I included the database password in this file, but I’ll explain in a bit how I removed it from the file and configured the Spring datasource with a password retrieved from AWS Secrets Manager. Though it’s not required, I like to set spring.jpa.show-sql=true while I’m working on a project so that I can see the SQL that the application is generating behind the scenes.

The spring.jpa.hibernate.ddl-auto=update setting is actually the most interesting bit of this particular configuration. That setting will cause Spring Data JPA to create (or update) the actual MySQL tables in the database instance. This is great for quick development as I only needed to define the JPA entity class definitions and Spring took care of the rest. If this setting is not specified, the default is none which will mean that Spring will not attempt to create any database tables and you’ll need to create them another way. You can read a full explanation of the different options for this setting here. I will say that JPA/Hibernate is not all that smart about altering existing tables to match changes to the entity class attributes. If you’re going to use this feature, I would plan on dropping your tables and letting them be re-created fairly often during development. I would recommend using a database versioning tool like Liquibase for managing a relational database schema, but diving into that is beyond the scope of this article.

The next step was to define the Entity classes that represent how we’ll work with the database tables in the Java code. The JPA specification provides the @Entity annotation for this purpose. Adding the annotation to a POJO class will, by default, allow you to easily perform CRUD actions with a database table that has the same name as the Java class and has column names with the same names and types as the member variables of the Java class. Here are the two entity classes I defined for the weather collection project (NOTE: I have left out the getters/setters to save space here).

@Entity
public class Location
{
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String city;
    private String state;
    private String country;
    private String zipCode;
    @OneToMany(mappedBy = "location")
    private List<WeatherData> weatherDatas;

    ...
}

@Entity
public class WeatherData
{
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    @ManyToOne
    private Location location;
    private LocalDate date;
    private Double maxTempC; private Double maxTempF; private Double minTempC; private Double minTempF;
    private Double avgTempC; private Double avgTempF; private Double maxWindMPH; private Double maxWindKPH;
    private Double totalPrecipMM; private Double totalPrecipIN; private Double avgVisKM;
    private Double avgVisMiles; private int avgHumidity; private String conditionStr; private int uv;

    ...
}

Each entity class must have a defined ID field which is the primary key of the underlying database table. You use the @id annotation to designate which field is the ID. In addition to that annotation, the @GeneratedValue annotation is used to indicate how values for the ID field are generated. I chose to use GenerationType.IDENTITY since, when used with MySQL, it corresponds with an auto increment column. I also tried using GenerationType.AUTO, but that resulted in a separate database table used as a sequence generator. You can read a good explanation about JPA/Hibernate ID columns here.

You’ll notice that there is no indication anywhere of what column types or sizes should be used when creating the database tables. So while this is a valid example of a JPA entity configuration and will result in a working system, it’s not the best way to configure your entities. JPA/Hibernate will create the tables using default column types and values based on the Java types you have declared. It’s very likely that you will want to specify column sizes at the very least, if not specific column data types.

The last thing I want to mention about those Java classes is that they’re related to one another. If you haven’t guessed, the corresponding database tables are also related. A Location object contains a List of WeatherData objects and a WeatherData object holds a reference to the Location object to which it’s related. You can probably figure out that the WeatherData database table has a foreign key relating back to the Location table. You make JPA/Hibernate aware of this relationship by annotating the attributes with the @ManyToOne and @OneToMany annotations. There’s a LOT to know about JPA associations. Seriously, you can really create a mess of an application if you start implementing them with limited knowledge. Unfortunately, I speak from personal experience here. I suggest doing some further reading and experimenting on the topic. I found this tutorial fairly informative.

After I had the Entity classes defined, I needed a way to tell JPA/Hibernate to execute a query and retrieve a list of Location objects for which to retrieve data from the weather API. I also needed a way to tell JPA to save the WeatherData objects that I populated with the API data to my database. This is where the powerful Spring Data Repository concept comes in. All I needed to do was to create an interface that extends CrudRepository. When you extend this generic interface you provide the type of your entity object and the type of the ID field of the entity class.

public interface LocationRepository extends CrudRepository<Location, Long>
{
}

public interface WeatherDataRepository extends CrudRepository<WeatherData, Long>
{
}

I then created a controller class and used Spring’s dependency injection feature to inject instances of these interfaces into the controller. Here’s a snippet from that class showing the @Autowired constructor.

@Autowired
public WeatherApiController(LocationRepository locationRepository, WeatherDataRepository weatherDataRepository)
{
    this.locationRepository = locationRepository;
    this.weatherDataRepository = weatherDataRepository;
 }

Having instances of those basic repository interfaces allows you to use all of the standard methods from CrudRepository. You can get all the details here. I use the standard methods findAll() and save() in my controller class. Here’s an example of how I’m using them.

for (Location location : locationRepository.findAll())
{
    WeatherData weatherData = restClient.getWeatherData(location);
    weatherDataRepository.save(weatherData);
}

If you need a more custom query you can define a new finder method in your repository interface like the example below which declares a findByCountry() method. Spring Data JPA will create the proper query based on the fact that the method name starts with ‘findBy’ and ends with the name of one of the attributes of the entity. You don’t need to write any actual code or queries!

public interface LocationRepository extends CrudRepository<Location, Long>
{
    List<Location> findByCountry(String country);
}

That all worked great, but if you’ve read my article about storing database credentials in AWS Secrets Manager, you might realize that my solution to this point is not optimal since I’m hard coding the password in a properties file. Sure, it works, but it’s not a good practice. Here’s what I did to remedy this egregious shortcoming.

@Configuration
public class Config
{
    @Value("${spring.datasource.password}")
    private String password;
    @Value("${spring.datasource.username}")
    private String username;
    @Value("${spring.datasource.driver-class-name}")
    private String driverClassName;
    @Value("${spring.datasource.url}")
    private String url;

    @Bean
    public DataSource dataSource()
    {
        if (password == null || password.isEmpty())
        {
            SecretsManagerClient client = SecretsManagerClient.create();
            GetSecretValueRequest request = GetSecretValueRequest.builder().secretId("weather-tracker-rds-password").build();
            password = client.getSecretValue(request).secretString();
        }
        DataSourceBuilder dataSourceBuilder = DataSourceBuilder.create();
        dataSourceBuilder.driverClassName(driverClassName);
        dataSourceBuilder.url(url);
        dataSourceBuilder.username(username);
        dataSourceBuilder.password(password);
        return dataSourceBuilder.build();
    }
}

I created the Config class above so that I could read in some of the datasource properties from the application.properties file, and override others dynamically. In theory, Spring allows partial data source configuration in a @Configuration annotated Java class and from application.properties, but I could not make it work. I had to configure all the properties in the Java class. Most of them are populated from application.properties, except for the password. I ended up with the functionality I wanted which was to supply all of the MySQL connection parameters in the properties file, and retrieve the password from Secrets Manager. The Config class just ended up being more bloated than I had initially planned. Spring has a certain precedence to where it looks for properties to populate a @Configuration class. You can read about how it works here.

If you’re interested, you can find the code for this article in my GitHub repository.

Was this article helpful? Did you learn something? Was it worth your time, or a waste of time? I’d love to hear from you if you have questions or feedback on this article and/or if I can help you get past any stumbling blocks you may have encountered along the way!