Simple Java persistence, part 1

September 28, 2020

If owners have vehicles, do you want to write your database connectivity like this?

package cloud.stephen.springdatajdbc;
import java.util.List;
import org.springframework.data.jdbc.repository.query.Query;
import org.springframework.data.repository.PagingAndSortingRepository;
public interface OwnerRepository extends PagingAndSortingRepository<Owner, Long> {
List<Owner> findByFirstName(String firstName);
List<Owner> findByLastName(String lastName);
@Query("SELECT OWNER.ID, OWNER.FIRST_NAME, OWNER.LAST_NAME FROM OWNER JOIN VEHICLE ON OWNER.ID = VEHICLE.OWNER WHERE VEHICLE.MAKE = :make")
List<Owner> findByVehicleMake(String make);
}

That generates SQL for (only the trivial) CRUD operations like this

SELECT "OWNER"."ID" AS "ID", "OWNER"."LAST_NAME" AS "LAST_NAME", "OWNER"."FIRST_NAME" AS "FIRST_NAME" FROM "OWNER" WHERE "OWNER"."ID" = ?

With an endpoint you can access like this

curl "http://localhost:8080/owners/search/findByVehicleMake?make=VW"

To return

{
"_embedded" : {
"owners" : [ {
"firstName" : "Stephen",
"lastName" : "Harrison",
"vehicles" : [ {
"make" : "Honda",
"model" : "CR-V",
"mileage" : 25000,
"owner" : 1
}, {
"make" : "VW",
"model" : "GTI",
"mileage" : 30000,
"owner" : 1
} ],
"_links" : {
"self" : {
"href" : "http://localhost:8080/owners/1"
},
"owner" : {
"href" : "http://localhost:8080/owners/1"
}
}
} ]
},
"_links" : {
"self" : {
"href" : "http://localhost:8080/owners/search/findByVehicleMake?make=VW"
}
}
}

You do? Then this is for you.

We also say nice things about Spring Boot. And Spring Data JDBC. Also Spring Data REST.

A Cook’s tour of Java persistence

Let’s face it, we’ve been through a lot of tech around Java persistence frameworks. Those technologies have their place, for sure. And we use them a lot at Venmo.

The first version of JDBC came along in 1997 to abstract details of the Oracle driver directly. Up to that point there was no connection pooling and it could take 1-2 seconds just to open a client to the database from a Java server. FastCGI was developed at Open Market (where the author worked at the time) to cache connections. A huge win.

1997 was a banner year for Java things. Enterprise Java Beans came along and promised to reduce database load considerably, among other benefits. It persisted state to local disk saving memory. It could be made to worked at the expense of quite a bit of complexity under the covers. Early versions were super fragile.

Along came Hibernate in 2001 as an alternative to EJB. It maps objects to datasets with automation for dirty reads and lazy loading. Venmo uses a lot of Hibernate. Or actually Java Persistence API (JPA). Modern Hibernate is one of several JPA providers.

No free lunch

When JPA works, it works well. It can generate SQL for all the databases, including some no one’s ever heard of. Lazy-loading of dependent objects can make memory usage and database calls efficient and you get to define exactly which joins are lazy or eager.

But in the author’s experience it often doesn’t work well. Just one of countless similar questions on stackoverflow asks why this, for example.

org.hibernate.LazyInitializationException: failed to lazily initialize a collection of role: de.hoeso.gwt.platform.server.domain.common.Person.anschrift, no session or session was closed
at org.hibernate.collection.AbstractPersistentCollection.throwLazyInitializationException(AbstractPersistentCollection.java:383)
at org.hibernate.collection.AbstractPersistentCollection.throwLazyInitializationExceptionIfNotConnected(AbstractPersistentCollection.java:375)
at org.hibernate.collection.AbstractPersistentCollection.readSize(AbstractPersistentCollection.java:122)
at org.hibernate.collection.PersistentBag.size(PersistentBag.java:248)
at de.hoeso.sis.server.services.common.impl.UserServiceBeanImpl.login(UserServiceBeanImpl.java:397)
at de.hoeso.sis.server.rpc.LoginService.execute(LoginService.java:35)

Other people are tons better at JPA than others and don’t get the above so much.

JPA requires session state on thread-local storage for many operations, which makes people like us sad. We’re thinking reactive a lot these days and that just won’t work. Stateless is what we’re after.

So what’s an alternative?

There’s nothing wrong with JDBC. Ignoring the fact column indices start at 1.

So since SQL is the lingua franca, let’s leverage all JDBC’s got to offer, something we just said there’s nothing wrong with.

A look at Spring Data JDBC and Spring Data Rest

Along with many others, the author loves Spring Boot, so we’re going to use it with Spring Data JDBC.

Note

Let’s make sure there’s no misunderstanding: There is no JPA here at all! No Hibernate or anything like that. This is Spring Data JDBC speaking plain SQL, not Spring Data JPA.

Here’s a complete application

All Spring Boot applications start off the same.

Application.java
package cloud.stephen.springdatajdbc;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.data.jdbc.repository.config.EnableJdbcRepositories;
@SpringBootApplication
@EnableJdbcRepositories
public class Application {
public static void main(String[] args) {
SpringApplication.run(Application.class, args);
}
}
Owner.java

Simply an object with an id.

package cloud.stephen.springdatajdbc;
import org.springframework.data.annotation.Id;
public class Owner {
@Id
private final Long id;
private final String firstName;
private final String lastName;
public Owner(final Long id, final String firstName, final String lastName) {
this.id = id;
this.firstName = firstName;
this.lastName = lastName;
}
static Owner of(final Long id, final String firstName, final String lastName) {
return new Owner(id, firstName, lastName);
}
Owner withId(Long id) {
return of(id, firstName, lastName);
}
public Long getId() {
return id;
}
public String getFirstName() {
return firstName;
}
public String getLastName() {
return lastName;
}
public int hashCode() {
return id == null ? 31 : id.hashCode();
}
public boolean equals(final Object that) {
if (this == that) {
return true;
}
if (!(that instanceof Owner)) {
return false;
}
return id != null && id.equals(((Owner) that).id);
}
}

This is a nice pattern. Spring Data takes full advantage of classes written this way.

A note about how we implemented hashCode() and equals() here. Equality for database entities should express the semantics of “is the row the same?” and not “are the columns the same for different rows?” Each database row has a unique id so we can just compare ids. It’s safe to compare with that.id because the Java Object Model requires that == null always returns false. We delegate to the id’s hashCode() method if we can and a feel-good Mersenne prime if it’s null.

This class corresponds to the SQL

CREATE TABLE IF NOT EXISTS OWNER (
ID INT AUTO_INCREMENT PRIMARY KEY,
FIRST_NAME VARCHAR(50) NOT NULL,
LAST_NAME VARCHAR(50) NOT NULL
);
OwnerRepository.java

This just provides types for a generic interface.

package cloud.stephen.springdatajdbc;
import org.springframework.data.repository.CrudRepository;
public interface OwnerRepository extends CrudRepository<Owner, Long> {
}

Wait what? Where’s the implementation? This is just a generic interface.

Keen eye. The Spring Framework leverages Aspect Oriented Programming to create runtime proxies that either decorate concrete methods, or in this case implement interfaces. AOP is at the core of Spring Boot, which takes it and runs with it. Spring proxies interface methods in CrudRepository with clever code behind the scenes.

Add dependencies

Spring Boot works by inspecting the class path. It finds libraries it has opinions about and configures them with sensible defaults. Here, we’re adding spring-boot-starter-data-jdbc and spring-boot-starter-data-rest to the class path, which Spring Boot make available for dependency injection.

Here’s that for Maven

...
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-rest</artifactId>
</dependency>
...

And for mavens of Gradle

implementation 'org.springframework.boot:spring-boot-starter-data-jdbc'
implementation 'org.springframework.boot:spring-boot-starter-data-rest'

Let’s manipulate some data

Because we included the dependency spring-boot-starter-data-rest, Spring Boot created a REST interface for our data automatically. Spring Boot always works this way. Like auto insurance apparently: Only pay for what you need.

No code — let alone configuration — is required to get this functionality. But just to be clear, you can configure pretty much everything in Spring Boot. It provides sensible defaults, which are usually good ones. And the places you want to specialize some settings are astonishingly well documented.

The exported REST interface uses Spring HATEOAS, which some people pronounce “hate oas” when they either don’t know what it is, or do but don’t like it. We’re not advocating using this. But since all it took was adding a single dependency we might as well.

List owners

Basic HATEOAS REST calls just name a model.

$ curl -i "http://localhost:8080/owners"
HTTP/1.1 200
{
"_embedded" : {
"owners" : [ ]
},
"_links" : {
"self" : {
"href" : "http://localhost:8080/owners"
},
"profile" : {
"href" : "http://localhost:8080/profile/owners"
}
}
}

Empty.

Add an owner

$ curl -i -X POST -H "Content-Type:application/json" -d '{ "firstName" : "Alex", "lastName" : "Harrison" }' http://localhost:8080/owners
HTTP/1.1 201
{
"firstName" : "Alex",
"lastName" : "Harrison",
"_links" : {
"self" : {
"href" : "http://localhost:8080/owners/1"
},
"owner" : {
"href" : "http://localhost:8080/owners/1"
}
}
}

Cool. Looks like the id is 1.

Add another

$ curl -i -X POST -H "Content-Type:application/json" -d '{ "firstName" : "Stephen", "lastName" : "Harrison" }' http://localhost:8080/owners
HTTP/1.1 201
{
"firstName" : "Stephen",
"lastName" : "Harrison",
"_links" : {
"self" : {
"href" : "http://localhost:8080/owners/2"
},
"owner" : {
"href" : "http://localhost:8080/owners/2"
}
}
}

With an id of 2. Looks like Spring Data JDBC has created a sequence for ids. Spring Boot’s opinionated and helpful approach is catching on.

List them out

$ curl -i "http://localhost:8080/owners"
HTTP/1.1 200
{
"_embedded" : {
"owners" : [ {
"firstName" : "Alex",
"lastName" : "Harrison",
"_links" : {
"self" : {
"href" : "http://localhost:8080/owners/1"
},
"owner" : {
"href" : "http://localhost:8080/owners/1"
}
}
}, {
"firstName" : "Stephen",
"lastName" : "Harrison",
"_links" : {
"self" : {
"href" : "http://localhost:8080/owners/2"
},
"owner" : {
"href" : "http://localhost:8080/owners/2"
}
}
} ]
},
"_links" : {
"self" : {
"href" : "http://localhost:8080/owners"
},
"profile" : {
"href" : "http://localhost:8080/profile/owners"
}
}
}

Delete someone (me 😿)

$ curl -i -X DELETE -H "Content-Type:application/json" http://localhost:8080/owners/2
HTTP/1.1 204

List them out again

$ curl -i "http://localhost:8080/owners"
HTTP/1.1 200
{
"_embedded" : {
"owners" : [ {
"firstName" : "Alex",
"lastName" : "Harrison",
"_links" : {
"self" : {
"href" : "http://localhost:8080/owners/1"
},
"owner" : {
"href" : "http://localhost:8080/owners/1"
}
}
} ]
},
"_links" : {
"self" : {
"href" : "http://localhost:8080/owners"
},
"profile" : {
"href" : "http://localhost:8080/profile/owners"
}
}
}

Update someone

$ curl -i -X PATCH -H "Content-Type:application/json" -d '{ "firstName" : "Alexander" }' http://localhost:8080/owners/1
HTTP/1.1 200
{
"firstName" : "Alexander",
"lastName" : "Harrison",
"_links" : {
"self" : {
"href" : "http://localhost:8080/owners/1"
},
"owner" : {
"href" : "http://localhost:8080/owners/1"
}
}
}

Notice how we only needed to specify firstName in the body of the PATCH and things will work as expected.

Add some custom finders

package cloud.stephen.springdatajdbc;
import java.util.List;
import org.springframework.data.repository.CrudRepository;
public interface OwnerRepository extends CrudRepository<Owner, Long> {
List<Owner> findByFirstName(String firstName);
List<Owner> findByLastName(String lastName);
}

Spring Data JDBC creates proxies for the new methods just like it did for the other methods in the interface we saw earlier. That happens when we use these conventions for naming methods.

With that done, we can say

$ curl -i "http://localhost:8080/owners/search/findByFirstName?firstName=Alex"
HTTP/1.1 200
{
"_embedded" : {
"owners" : [ ]
},
"_links" : {
"self" : {
"href" : "http://localhost:8080/owners/search/findByFirstName?firstName=Alex"
}
}
}

Empty.

$ curl -i "http://localhost:8080/owners/search/findByFirstName?firstName=Alexander"
HTTP/1.1 200
{
"_embedded" : {
"owners" : [ {
"firstName" : "Alexander",
"lastName" : "Harrison",
"_links" : {
"self" : {
"href" : "http://localhost:8080/owners/1"
},
"owner" : {
"href" : "http://localhost:8080/owners/1"
}
}
} ]
},
"_links" : {
"self" : {
"href" : "http://localhost:8080/owners/search/findByFirstName?firstName=Alexander"
}
}
}

Bingo.

The upshot

That was simple CRUD that took only a little effort to build. It’s also of only a little use to us. Part 2 continues the story adding related classes, transactions, and testing.

Stephen Harrison, Venmo SRE