Select specific columns in JPA Spring Boot

Question

I want to get some specific columns from my table but I get this error: could not execute query; SQL [SELECT name, image FROM characters order by name asc]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query.

o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 0, SQLState: 42703 o.h.engine.jdbc.spi.SqlExceptionHelper : The column name id was not found in this ResultSet.

I want to run this query:

public interface CharacterRepository extends JpaRepository<CharacterEntity, Long> {
    @Query(value = "SELECT name, image FROM characters", countQuery = "SELECT COUNT(name) FROM characters", nativeQuery = true)
    Page<CharacterEntity> getCharacters(Pageable pageable);
}

My entitiy class:

@Entity(name = "characters")
@Getter @Setter
public class CharacterEntity implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue
    private long id;

    @Column(nullable = false)
    private String image;

    @Column(nullable = false, length = 100)
    private String name;

    @Column(nullable = false)
    private Integer age;

    @Digits(integer = 8, fraction = 2)
    private BigDecimal weight;

    private String story;

    @OneToMany(cascade = CascadeType.ALL, mappedBy = "character")
    private List<MovieDetailEntity> movies;

}

My Dto class:

@Getter @Setter
public class CharacterDto implements Serializable {

    private static final long serialVersionUID = 1L;

    private long id;
    private String image;
    private String name;
    private Integer age;
    private BigDecimal weight;
    private String story;
    private List<MovieDetailDto> movies;

    public CharacterDto() {

    }
    public CharacterDto(long id, String image, String name, Integer age, BigDecimal weight, String story) {
        this.id = id;
        this.image = image;
        this.name = name;
        this.age = age;
        this.weight = weight;
        this.story = story;
    }
}

Answer 1

I think this is a perfect use case for Blaze-Persistence Entity Views.

I created the library to allow easy mapping between JPA models and custom interface or abstract class defined models, something like Spring Data Projections on steroids. The idea is that you define your target structure(domain model) the way you like and map attributes(getters) via JPQL expressions to the entity model.

A DTO model for your use case could look like the following with Blaze-Persistence Entity-Views:

@EntityView(CharacterEntity.class)
public interface CharacterImageDto {
    @IdMapping
    Long getId();
    String getImage();
}

Querying is a matter of applying the entity view to a query, the simplest being just a query by id.

CharacterImageDto a = entityViewManager.find(entityManager, CharacterImageDto.class, id);

The Spring Data integration allows you to use it almost like Spring Data Projections: https://persistence.blazebit.com/documentation/entity-view/manual/en_US/index.html#spring-data-features

Page<CharacterImageDto> findAll(Pageable pageable);

The best part is, it will only fetch the state that is actually necessary! And Blaze-Persistence Entity-Views supports mapping collections as well e.g.

@EntityView(CharacterEntity.class)
public interface CharacterDto {
    @IdMapping
    Long getId();
    String getImage();
    Set<MovieDto> getMovies();

    @EntityView(MovieDetailEntity.class)
    interface MovieDto {
        @IdMapping
        Long getId();
        String getName();
    }
}

Answer 2

Instead of returning Page you can change the return type to Page<Object[]> :

@Query(value = "SELECT name, image FROM characters", countQuery = "SELECT COUNT(name) FROM characters", nativeQuery = true)
Page<Object[]> getCharacters(Pageable pageable);

Answer 3

If you wanted to select custom colums, you would create a custom dto and load data into it. You cant load custom data into managed entities, but you can use jpql to select into your custom dto.

package my.class.package;

class Chars{
    private String name;
    private IDONTKNOW image;  
    [other fields, constructors, getters,setters ...]
}

Then you can query into it:

@Query("SELECT new my.class.package.Chars(c.name,c.image) FROM characters c")
Page<Chars> getCharacters(Pageable pageable);

You can also use this kind of queries to load fields from different tables (entities) by joining those.

For example (one of my old, but maybe it will help you):

   public class ProgrammerNameAndCity {
       private String name;
       private String city;
   }
    
    @Entity
    @Table(name = "programmer")
    public class Programmer extends BaseEntity {
    
        @Basic
        private String name;
    
        @Column(name = "id_number", nullable = false, unique = true)
        private String idNumber;
    
        //hibernate specific
        @Type(type = "yes_no")
        private Boolean senior;
    
        @Basic
        private Integer salary;
    
        @OneToOne(fetch = FetchType.LAZY, cascade = CascadeType.ALL)
        @JoinColumn(name = "address_id")
        private Address address;
    
    }
    
    @Entity
    @Table
    public class Address extends BaseEntity {
    
        @Basic
        private Integer zip;
    
        @Basic
        private String city;
    
        @Basic
        private String street;
    
        @Column(name = "house_number")
        private Integer houseNumber;
    
        @OneToOne(mappedBy = "address")
        private Programmer programmer;
    
    }
    
   @Query("select new mypackage.ProgrammerNameAndCity(p.name,p.address.city) from Programmer p where  p.idNumber=?1")
   ProgrammerNameAndCity findNameAndCityByIdNumber(String idNumber);