Fetch count(distinct value) along with other columns in spring boot application-CodePudding

I am stuck while fetching the count(distinct) values from the databse using JPQL queries. Please refer below query

I have got below table

**ID        USER_created    Laptop_Make Laptop_Model    Laptop_OS**
111111      User_A          Dell        Inspiron15      in10
111112      User_A          Dell        Inspiron17      Win10
111113     User_A           Dell        Inspiron16      Win10
222221     User_B           Dell        Inspiron17      Win10
222222     User_B           Dell        Inspiron15      Win10
222223     User_B           Lenovo      Ideapad4        Win10
333331     User_C           Lenovo      Ideapad5        Win10
333332     User_C           Lenovo      Ideapad7        Win10
333333     User_C           Dell        Inspiron16      Win10
444441     User_D           Dell        Inspiron17      Win10
444442     User_D           Lenovo      Ideapad4        Win10
444443     User_D           Lenovo      Ideapad5        Win10

using below query I got below output

SELECT Laptop_Make, Laptop_Model, Laptop_OS, count(distict ID) as TOTAL group by Laptop_Make, Laptop_Model, Laptop_OS;

**Laptop_Make   Laptop_Model    Laptop_OS   TOTAL**
Dell            Inspiron15      Win10       2
Dell            Inspiron16      Win10       2
Dell            Inspiron17      Win10       3
Lenovo          Ideapad4        Win10       2
Lenovo          Ideapad5        Win10       2
Lenovo          Ideapad7        Win10       1

How can we map the TOTAL column to Spring boot Entity as this column is not there on physical table with name Laptop and create output table as list of json values

CodePudding user response：

Another option apart from Andronicus' suggestion to use an extra class would be to use javax.persistence.Tuple as follows:

List<Tuple> resultList = entitityManager
  .createQuery("SELECT Laptop_Make, Laptop_Model, Laptop_OS, count(distict ID)) group by Laptop_Make, Laptop_Model, Laptop_OS", Tuple.class)
  .getResultList(); 
List<JSONObject> allObjects = new ArrayList<>();
for(Tuple result : resultList) {
  JSONObject object = new JSONObject();
  object.put("make", (String) tuple.get(0));
  object.put("model", (String) tuple.get(1));
  object.put("os", (String) tuple.get(2));
  object.put("total", (int) tuple.get(3));
  allObjects.add(object);
}

This gives you a list with JSONObjects for each of the grouped database entries.

CodePudding user response：

Considering that OP wants a solution with JPQL, the following should do the work

public class LaptopAggregation {

  private String laptopMake;
  private String laptopModel;
  private String laptopOS;
  private Long   total;

  public LaptopAggregation(String laptopMake, String laptopModel,
        String laptopOS, Long total) {
    this.laptopMake = laptopMake;
    this.laptopModel = laptopModel;
    this.laptopOS = laptopOS;
    this.total  = total;
  }
}

You should already have an entity like the following

@Entity
public class Laptop {

    @Id
    private Long id;

    private String laptopMake;
      
    private String laptopModel;
      
    private String laptopOS;
   
}

And you should also by now have a Spring JPA repository like the following

public interface LaptopRepository extends JpaRepository<Laptop, Long> {

}

Then in the last mentioned repository class you can add the following method and should work as expected

   @Query("SELECT "  
           "new com.package.path.to.LaptopAggregation(lp.laptopMake, lp.laptopModel, lp.laptopOs, COUNT(lp.id)) "  
           "FROM Laptop lp "  
           "GROUP BY lp.laptopMake, lp.laptopModel, lp.laptopOS")
    List<LaptopAggregation> findAggregateTotalLaptops();