Spring JPA selecting specific columns

Question

I am using Spring JPA to perform all database operations. However I don't know how to select specific columns from a table in Spring JPA?

For example:
SELECT projectId, projectName FROM projects

Guv

see this #12618989 – Trunkfish

The idea behind JPA not looking for specific fields is that is cost (efficiency wise) the same to bring one column or all columns from one row of the table. – Faison

@Faison -- the cost is not always the same. It's probably not a big deal for simpler, primitive sort of datatypes but the reason I ended up on this page is because I noticed a simple "list documents" query was running slow. That entity has a BLOB column (needs it for file upload/storage) & I suspect it is slow because it is loading the BLOBs into memory even though they're not required for listing the docs. – Colophon

@Colophon As far as you remember, how many tables had BLOB columns? – Faison

@Faison it was just one table but I was doing a "list" function (multirow -- list all docs created by a given id). The only reason I noticed this issue was because this simple list query was taking several seconds, whereas more complex queries on other tables were happening almost instantly. Once I realized, I knew it would suffer more and more as rows are added because Spring JPA is loading every BLOB into memory even tho they are not used. I found a decent solution for Spring data (posted below) but I think I have an even better one that is pure JPA annotation, I will post tmrw if it works – Colophon

Spring Data Projection is the solution. Ref: docs.spring.io/spring-data/jpa/docs/current/reference/html/… – Marienbad

To people saying that selecting specific values has no sense: indeed selecting specific entries has much sense, for example whenever the user is doing something like SELECT MAX(something) or SELECT AVG(something). So, indeed it's a problem if it's not so intuitive to do so from JPA, and so indeed the question is useful. Thanks for reporting this question. – Exsert

Answer 1

You can set nativeQuery = true in the @Query annotation from a Repository class like this:

public static final String FIND_PROJECTS = "SELECT projectId, projectName FROM projects";

@Query(value = FIND_PROJECTS, nativeQuery = true)
public List<Object[]> findProjects();

Note that you will have to do the mapping yourself though. It's probably easier to just use the regular mapped lookup like this unless you really only need those two values:

public List<Project> findAll()

It's probably worth looking at the Spring data docs as well.

Bustup

no need for native queries. You should avoid using them, for they ruin the advantages of JPQL. see Atals answer. – Hornmad

For me I had to qualify the first attribute (above FIND_PROJECTS) with the value attribute name (hence if this was my code I would have had to write it as @Query(value = FIND_PROJECTS, nativeQuery = true), etc. – Bozarth

Answer 2

You can use projections from Spring Data JPA (doc). In your case, create interface:

interface ProjectIdAndName{
    String getId();
    String getName();
}

and add following method to your repository

List<ProjectIdAndName> findAll();

Biparous

This is a clean solution. it may have boiler template but the interface can be the inner class of the entity. Making it quite clean. – Flacon

awesome, just remember not to implement the interface on your Entity or it won't work – Pledge

where does the projected interface go? in its own file or can it be included in the public interface that returns the full entity properties? – Molton

This solution doesn't work when extending JpaRepository, any one knows a workaround? – Prim

For me the values are always null :( – Chryso

I had no issues getting this to work with JpaRepository, try this method signature List<ProjectIdAndName> findAllProjectedBy() inside your repository class. I would also suggest keeping your projection interfaces close to your DTOs in terms of packaging. IMO projection interfaces behave like DTOs. – Uralaltaic

You can not use findAll(); as it will clash with JPARepositorys method. You need to use something like List<ProjectIdAndName> findAllBy(); – Yea

It is good approach, but it does not work with native queries. -_- – Bullpup

You will also need to as any columns in your query that do not map to the name of the projection field e.g. if table col is event_type and the projection field is eventType then the query should be select event_type as eventType ... – Ashbey

Really like this solution, elegant and simple. Also, as it's an interface, it's easy to mock for testing. Also had to use an alias for column name where it has an underscore, as mentioned by @Ashbey above – Ypres

Maybe this will help : logicbig.com/tutorials/spring-framework/spring-data/… – Institutor

I need to use a @caxheable over this query, for such a case can I make the object serializable? I am getting an error when I try this. – Pillbox

This Answer is SimplySuperb. But what if I want to query like select col1, col2, col3 where col1 = id; Any Help? May be @Code_Mode – Polik

@JimKennedy Or can you give any Idea about my above comment? – Polik

This is good solution, too bad it does not work with specifications. – Jellybean

For JPARepositories I recommend List<YourType> findDistinctAllBy() – Moberg

I've tried this, even though it's return specific column, but the query that's generated still using all the column – Catlett

The underlying datasource is still returned by the repo, and you might want to reduce data processing by avoiding large objects – Saipan

@Germán Actually it's working for me when extending JpaRepository. Collection<SymbolOnly> findAllByIsActiveTrue(); – Pino

Answer 3

I don't like the syntax particularly (it looks a little bit hacky...) but this is the most elegant solution I was able to find (it uses a custom JPQL query in the JPA repository class):

@Query("select new com.foo.bar.entity.Document(d.docId, d.filename) from Document d where d.filterCol = ?1")
List<Document> findDocumentsForListing(String filterValue);

Then of course, you just have to provide a constructor for Document that accepts docId & filename as constructor args.

Colophon

(and btw I verified, you don't need to provide the fully qualified classname if "Document" is imported -- just had it that way because that's how it was done in the only sample I was able to find) – Colophon

this should be the accepted answer. It works perfectly and really selects only the necessary fields. – Budget

The unnecessary fields are also included, but with the value 'null', would those fields occupy memory? – Greenleaf

yes but so minimal that in vast majority of cases it would be really ridiculous to try to engineer around this -- #2431155 you'd have to make specialized lightweight objects without these fields & have them point to same table? which IMO is undesired when using ORMs and leveraging them for their relationships... hyper-optimization is maybe more in the realm of just using some lightweight query DSL and mapping directly to DTOs, & even then i think redundancy is discouraged – Colophon

jm0 it did not work for me without fully qualified classname, though it was imported. It did compile successfully though. – Bookbindery

interesting... maybe i didn't test it correctly (but I thought I did) or things have changed. This answer is quite old. – Colophon

You don't need a custom query. If Document contains only 2 fields, then Spring JPA knows to only fetch those 2 columns from the database, as described in this answer. – Nucleolated

This solution looks the most easy to apply but what if you have more than 8 fields that you want to fetch ? In that case sonar lint would fail if you have some max parameter length rule is set . – Hamza

It's the best solution ! (Simple, no new Interface or class) It's works with Json for send to a client waiting for full entity (without any client-side adaptation). I confirm that it's not necessary to provide fully qualified classname (Spring boot 2.3.3) – Erupt

This is the good solution. Also if the Repository and the Entity class in same package you don't need to provide the fully qualified class name. I generally organize code in module/features, for some one organize code by the type of code such as Model, DAO etc this won't help. – Plenum

@VishalNair can u elaborate on your point which says, " In that case sonar lint would fail if you have some max parameter length rule is set." – Blindfold

Perfect solution but not working without fully qualified class name in Spring:3.1.5 – Quixotic

Answer 4

In my situation, I only need the json result, and this works for me:

public interface SchoolRepository extends JpaRepository<School,Integer> {
    @Query("select s.id, s.name from School s")
    List<Object> getSchoolIdAndName();
}

in Controller:

@Autowired
private SchoolRepository schoolRepository;

@ResponseBody
@RequestMapping("getschoolidandname.do")
public List<Object> getSchool() {
    List<Object> schools = schoolRepository.getSchoolIdAndName();
    return schools;
}

Isaak

you should substitute Object with a custom interface as described by mpr. works flawlessly – Hornmad

mpr's solution selects all queries from the database and filters them server-side. See the difference by logging the native queries sent to the database. – Actual

Answer 5

With the newer Spring versions One can do as follows:

If not using native query this can done as below:

public interface ProjectMini {
    String getProjectId();
    String getProjectName();
}

public interface ProjectRepository extends JpaRepository<Project, String> { 
    @Query("SELECT p FROM Project p")
    List<ProjectMini> findAllProjectsMini();
}

Using native query the same can be done as below:

public interface ProjectRepository extends JpaRepository<Project, String> { 
    @Query(value = "SELECT projectId, projectName FROM project", nativeQuery = true)
    List<ProjectMini> findAllProjectsMini();
}

For detail check the docs

Apprehension

No converter found capable of converting from type – Auriferous

The first approach fetches all columns. The second works well. – Thuja

Answer 6

In my case i created a separate entity class without the fields that are not required (only with the fields that are required).

Map the entity to the same table. Now when all the columns are required i use the old entity, when only some columns are required, i use the lite entity.

e.g.

@Entity
@Table(name = "user")
Class User{
         @Column(name = "id", unique=true, nullable=false)
         int id;
         @Column(name = "name", nullable=false)
         String name;
         @Column(name = "address", nullable=false)
         Address address;
}

You can create something like :

@Entity
@Table(name = "user")
Class UserLite{
         @Column(name = "id", unique=true, nullable=false)
         int id;
         @Column(name = "name", nullable=false)
         String name;
}

This works when you know the columns to fetch (and this is not going to change).

won't work if you need to dynamically decide the columns.

Greyhen

Hi sachin, I have one doubt if i will create the entity like as you mention above. when JPA will run and it will try to create table with the name of user. which entity will use. – Doley

never create a table with JPA, create your tables manually in the db, use JPA to map relational world to object world. – Greyhen

Why can't you make use of inheritance here ? – Round

You'd inherit the column you were trying to exclude. @Round – Katherinakatherine

Answer 7

Using Spring Data JPA there is a provision to select specific columns from database

---- In DAOImpl ----

@Override
    @Transactional
    public List<Employee> getAllEmployee() throws Exception {
    LOGGER.info("Inside getAllEmployee");
    List<Employee> empList = empRepo.getNameAndCityOnly();
    return empList;
    }

---- In Repo ----

public interface EmployeeRepository extends CrudRepository<Employee,Integer> {
    @Query("select e.name, e.city from Employee e" )
    List<Employee> getNameAndCityOnly();
}

It worked 100% in my case. Thanks.

Ninurta

doesn't seem to work with native queries – Chinese

Answer 8

In my opinion this is great solution:

interface PersonRepository extends Repository<Person, UUID> {

    <T> Collection<T> findByLastname(String lastname, Class<T> type);
}

and using it like so

void someMethod(PersonRepository people) {

  Collection<Person> aggregates =
    people.findByLastname("Matthews", Person.class);

  Collection<NamesOnly> aggregates =
    people.findByLastname("Matthews", NamesOnly.class);
}

Greenaway

Why not return List<T> instead of collection?! – Spaceband

@AbdullahKhan because the result may not always have an order. – Parhelion

It will always be a list, and they'll always be in the order returned by the database. Abdullah is right - it makes sense to return these as List, not as Collection. – Alesiaalessandra

Answer 9

I guess the easy way may be is using QueryDSL, that comes with the Spring-Data.

Using to your question the answer can be

JPAQuery query = new JPAQuery(entityManager);
List<Tuple> result = query.from(projects).list(project.projectId, project.projectName);
for (Tuple row : result) {
 System.out.println("project ID " + row.get(project.projectId));
 System.out.println("project Name " + row.get(project.projectName)); 
}}

The entity manager can be Autowired and you always will work with object and clases without use *QL language.

As you can see in the link the last choice seems, almost for me, more elegant, that is, using DTO for store the result. Apply to your example that will be:

JPAQuery query = new JPAQuery(entityManager);
QProject project = QProject.project;
List<ProjectDTO> dtos = query.from(project).list(new QProjectDTO(project.projectId, project.projectName));

Defining ProjectDTO as:

class ProjectDTO {

 private long id;
 private String name;
 @QueryProjection
 public ProjectDTO(long projectId, String projectName){
   this.id = projectId;
   this.name = projectName;
 }
 public String getProjectId(){ ... }
 public String getProjectName(){....}
}

Allele

Answer 10

You can use JPQL:

TypedQuery <Object[]> query = em.createQuery(
  "SELECT p.projectId, p.projectName FROM projects AS p", Object[].class);

List<Object[]> results = query.getResultList();

or you can use native sql query.

Query query = em.createNativeQuery("sql statement");
List<Object[]> results = query.getResultList();

Quita

Answer 11

You can apply the below code in your repository interface class.

entityname means your database table name like projects. And List means Project is Entity class in your Projects.

@Query(value="select p from #{#entityName} p where p.id=:projectId and p.projectName=:projectName")

List<Project> findAll(@Param("projectId") int projectId, @Param("projectName") String projectName);

Labor

Answer 12

It is possible to specify null as field value in native sql.

@Query(value = "select p.id, p.uid, p.title, null as documentation, p.ptype " +
            " from projects p " +
            "where p.uid = (:uid)" +
            "  and p.ptype = 'P'", nativeQuery = true)
Project findInfoByUid(@Param("uid") String uid);

Unsphere

Answer 13

public static final String FIND_PROJECTS = "select ac_year_id,ac_year from tbl_au_academic_year where ac_year_id=?1";

    @Query(value = FIND_PROJECTS, nativeQuery = true)
    public  List<Object[]> findByAcYearId(Integer ac_year_id);

this works for me

Arela

Answer 14

You can use the answer suggested by @jombie, and:

• place the interface in a separate file, outside the entity class;
• use native query or not (the choice depended on your needs);
• don't override findAll() method for this purpose but use name of your choice;
• remember to return a List parametrized with your new interface (e.g. List<SmallProject>).

Argumentum

Answer 15

You can update your JPARepository as below.

@Query("select u.status from UserLogin u where u.userId = ?1 or u.email = ?1 or u.mobile = ?1")
public UserStatus findByUserIdOrEmailOrMobile(String loginId);

Where UserStatus is a Enum

public enum UserStatus
{
    New,
    Active,
    Deactived,
    Suspended,
    Locked
}

Fabianfabianism

Answer 16

You can use a DTO like that

 @Data
    public class UserDtoLight implements Serializable {
       private final Long id;
       private final String name;
    }

and in your repository

 List<UserDtoLight> findAll();

Unblown

Answer 17

With current version I'm using (JDK 11, Spring Boot 3) It's very simple:

1. You need to add a constructor in your entity class for limited fields which you want to select:

    class Projects {
        private String projectId;
        private String projectName;
        // other fields
    
        // No argument / all arguments  constructor as needed
    
        public Projects(String projectId, String projectName) {
            this.projectId = projectId;
            this.projectName = projectName;
        }
    }

2. In your Repository, you can add Query & method as below (add WHERE condition as needed):

    @Query("SELECT new Projects (p.projectId, p.projectName) FROM Projects p")
    List<Projects > findAllProjects();

For me, this works & I get list of all objects with only 2 fields specified.

Dreibund

Answer 18

Using Native Query:

Query query = entityManager.createNativeQuery("SELECT projectId, projectName FROM projects");
List result = query.getResultList();

Agamemnon

Answer 19

You can perform it in a lot of ways.

• By using nativeQuery, so by selecting directly from your database table (it must be defined inside your application.properties/yml).
  So simply declare your repository by extending JpaRepository:

    @Repository
    public interface ProjectRepository extends JpaRepository<Project, Long>{
    }
  

  Then, you should define the query (something like this) inside the Repository:

  @Repository
  public interface ProjectRepository extends JpaRepository<Project, Long>{
  
      @Query(value = "SELECT projectId, projectName FROM projects", nativeQuery = true)
      public List<Project> getProjectColumns();
  
  }
  

  Finally, you can call this method from your controller like that:

  @CrossOrigin(origins = "http://localhost:8080")
  @RestController
  @RequestMapping("/project")
  public class Controller {
      private ProjectRepository projectRepo;
  
      public Controller(ProjectRepository projectRepo) {
          this.projectRepo = projectRepo;
      }
  
      @GetMapping("/columns")
      public ResponseEntity<List<Project>> getProjectColumns(){
          List<Project> projects = new ArrayList<Project>();
          projects = projectRepo.getProjectColumns();
          return projects;   
      }
  }
  

• By using JPQL.
  The only thing that must be change from the nativeQuery example is the query itself. In fact with JPQL you are going to perform the query on the entity itself.
  So:

  @Repository
  public interface ProjectRepository extends JpaRepository<Project, Long>{
  
      @Query("SELECT p.projectId, p.projectName FROM Project p")
      public List<Project> getProjectColumns();
  
  }
  

  Note that Project is the entity and not the database table (in the previous example is projects).
  I left here a useful link I found on the Internet.

• Finally, you can use this other technique. I'll advise you that it is a little bit more complicated, but the result is the same.
  First thing is to define only the method signature in the repository:

  @Repository
  public interface ProjectRepository extends JpaRepository<Project, Long>{
  
      public List<Project> getProjectColumns();
  
  }
  

  Second thing is to modify your entity Project by writing the query inside the @NamedNativeQueries annotation, and then by writing the result mapping inside the @SqlResultSetMappings annotation. In this annotation you are going to define how the object that you are going to return will be, so you may also define a new constructor inside the Project entity.
  This is how it should look:

  @NamedNativeQueries(value = {
      @NamedNativeQuery(name = "Project.getProjectColumns", // The name of the repository method
          query = "SELECT p.projectId, p.projectName FROM projects p",
          resultSetMapping = "projectColumns")})
  @SqlResultSetMappings(value = {
      @SqlResultSetMapping(name = "projectColumns", // It must match with the resultSetMapping defined inside @NamedNativeQuery
         classes = @ConstructorResult(targetClass = Project.class,
                                      columns = {@ColumnResult(name = "projectId"),
                                                 @ColumnResult(name = "projectName")}))})
  @Entity
  @Table(name = "projects")
  public class Project {
  
      // Columns of the entity Project
      @Id
      private long projectId;
  
      private String projectName;
  
  
      // Some other attribute can be defined here
  
  
      // Default constructor
      public Project() {
      }
  
  
      // Constructor for getProjectColumns query. Note that the number of the
      // parameters and their type must be the same of the one returned by the query.
      // All the other attributes present in this entity will be set to their default value.
      public Project(long projectId, String projectName) {
          this.projectId = projectId;
          this.projectName = projectName;
      }
  
      // Other constructor can be available
  
      // Getter and setter here
  }  
  

  Finally your controller can be the same as the previous one. The only important thing is that all the names that are present (query, method, ...) must be the same or the query will note be performed.

I'll hope that this is clear and will help.

Marvelous

Answer 20

Use:

@Query("SELECT e FROM #{#entityName} e where e.userId=:uid")
List<ClienteEnderecoEntity> findInfoByUid(@Param("uid") UUID uid);

• hibernate - Native Query

Rhombic