spring boot modeling MySQL native query result with a class without creating a table entity

user_16408923 0
Viewed 261

as the title indicate i have a native SQL query in my repository like that

@Repository
public interface BesoinRepository extends CrudRepository<Statistic, Long>{

    @Query(value="SELECT etat_besoin AS 'state',COUNT(DISTINCT id) AS 'number' FROM besoin WHERE YEAR(date_creation)=:year GROUP BY etat_besoin ",nativeQuery=true)
    List<Object> getStatistic(@Param("year") int year); 
}

class statistic

package fr.solinum.management.model;
public class Statistic {
    private String state;
    private int number;
    
    public String getState() {
        return state;
    }
    
    public int getNumber() {
        return number;
    }
}

and this is working ok but i want the return type of getStatistic to be List<Statistic> and without creating the table Statistic in my database since i dont need it. in other word i want only to read from the database and return the result as a class. so what are the changes in the model and in the repository or in the controller or what is the optimal approach for my problem? note that i modified my model class as following to solve that but i dont think that this is the optimal or the good approach.

package fr.solinum.management.model;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
@Entity
@Table(name = "besoin")
public class Statistic {
    @Id
    private int id;
    private String state;
    private int number;
    
    public String getState() {
        return state;
    }
    
    public int getNumber() {
        return number;
    }
}
spring-boot hibernate java
1 Answers

I can think of three approaches:

  1. Use a Database View.

With this, you can have your entity as you want mapped as any other table to the View. You can use a Repository with no problems and you don't create a different table.

  1. Map your List in a class and access this data from a service/DAO.

This is more labor intensive and might not "fit" with the rest of your code. Instead of using your repository directly, you would use another class. This class would do the querying of Objects and map them before delivering them to the controller or service where they are being requested. It's like doing JPA's work, really.

Take the following code as example:

@Component
public class StatisticDAO {
    
    @Autowired
    private BesoinRepository besoinRepository;

    public List<Statistic> getStatistic(int year) {
        List<Statistic> result = new ArrayList<>();
        List<Object[]> temp = besoinRepository.getStatistic(year);
        temp.stream().forEach(data -> result.add(build(data)));
        return result;
    }
    
    private Statistic build(Object[] data) {
        return new Statistic(String.valueOf(data[0]), (Integer)data[1]);
    }

}

You'll need to change the type of the list to Object[] and check the order in which order JPA is returning the attributes.

  1. Use interface-based projections.

I'd recommend approaches 1 and 3, but is up to you and whatever suits you better.

user_106212960
Related