Published on

How to Select Some Columns with Spring JPA Repositories and Kotlin

Authors

Generally working with Spring JPA and Kotlin is pretty seamless. One area though where I ran into a wall was trying to get Spring JPA Repositories to return only some columns instead of the whole DB entity. For example, if I have the following DAO:

interface PersonDAO : JpaRepository<Person, Long>

But what if I only want only 2 columns, for example, the person's first and last name? After a bit of Googling, I came across this question and answer. Based on this it is doable with Spring JPA. JPA refers to this as a projection.

A projection is an interface (the name does is not important) that has getters in corresponding to the fields you want. So for example for name and last name I would create a projection as follows:

interface NameSurnameProjection {
  fun getName(): String
  fun getLastName(): String
}

Unfortunately the above has to be an interface - a data class with just these 2 fields will not work :'(. Fortunately, the above syntax can be simplified a bit:

interface NameSurnameProjection {
  val name: String
  val lastName: String
}

If you want to allow either of these fields to be nullable simply declare the type as nullable. For example for a nullable lastName: val lastName: String?.

Now that we have our projection we can write our query - it has to be a native query (HQL and JpQL will not work).

For example, I would have the following (the name of the function calling the native query is not relevant as long as it does not clash with any JpaRepository method names):

interface PersonDAO: JpaRepository<Person, Long> {
  @Query("select p.name, p.last_name as lastName from Person p", nativeQuery= true)
  fun findAllNameAndLastNames(): List<NameSurnameProjection>
}

Some important points on the above:

  • You need to as any columns where the SQL table name is different from the projection name for example in my table the last name field is stored as last_name so to get it to map using the projection I write the query as last_name as lastName.
    • If you do not do this the value will always map to null
    • This is described in this question and answer
  • You have to write a standard vanilla SQL query, not HQL or JpQL
  • You can make the return type any of the standard collections or a single type based on what your query is doing