Spring Data JPA native query does not follow naming convention with projections

Question

I'm using Spring Boot 2.1.3.RELEASE, Spring Data JPA against a PostgreSQL database.

The column names are using underscores (e.g. created_by) and the entity beans normal Java camelCase createdBy, getCreatedBy() etc.

I am trying to write a native query with a projection interface, but I get back null values. Example:

public class MyEntity {
    private String createdBy;
    // getters and setters etc
    // more fields here
}

public interface MyProjection {
    String getCreatedBy();
}

public interface MyRepository extends JpaRepository<MyEntity, Long> {
    @Query(value = "
       SELECT DISTINCT cool_table.* FROM cool_table INNER JOIN
       // more SQL things", nativeQuery = true
    )
    List<MyProjection> searchNative(String filter);
}

When I run this, I get back null for underscore separated columns (which work fine with non-native queries).

As an experiment, I added a method in my projection called getCreated_by() and that one works fine...

I don't want to rename all the methods in my projection to have underscores in their names because that looks ugly. Is there a way to get native queries to work together with projections?

Answer 1

You can explicitly rename the fields in your query to match the desired method names in your projection interface.

public interface MyProjection {
  String getCreatedBy();
}

public interface MyRepository extends JpaRepository<MyEntity, Long> {
  @Query(
    value = "SELECT cool_table.created_by AS createdBy ...",
    nativeQuery = true
  )
  List<MyProjection> searchNative(String filter);
}

Answer 2

Just use the annotation @Column and physically set the column names as in the database. Another option is to set your schema to auto create and see what schema is created automatically.