[FIXED] Mount a subselect in HQL on Spring

Issue

I am studying Spring MVC with Hibernate and came across the following situation.

I have a service-related rate card in which the last registered price is the current price of the service and developed the sql code to get the result like this:

My database looks like this:

SQL that I need in HQL Java method:

select s.*,
(select ps.price 
 from priceServices ps 
 where ps.idService = s.id 
 order by ps.dateRegister DESC limit 1) as currentPrice 
from service s

Java Classes:

@Entity
@Table(name = "service")
public class Service  {
    /****/
    @OneToMany(mappedBy="service",
            cascade= CascadeType.ALL,
            fetch = FetchType.LAZY)
    private List<PriceServices> prices;
}
// ......
@Entity
@Table(name = "priceServices")
public class PriceServices  {
    /****/
    @Id
    @ManyToOne(cascade = { CascadeType.PERSIST, CascadeType.MERGE,
             CascadeType.DETACH, CascadeType.REFRESH})
    @JoinColumn(name = "idService")
    private Service service;
}
// ......
@Repository
public class ServiceDaoImpl implements ServiceDao {

    @Autowired
    private SessionFactory sessionFactory;

    protected Session getCurrentSession() {
        return sessionFactory.getCurrentSession();
    }

    @Override
    public List<Service> findAll() {
        return this.getCurrentSession().createQuery("from Service", Service.class).getResultList();
    }
}

I am currently displaying a table with services and an "info" button at the end, when the user clicks this button, I do an ajax searching for the price of the service.

However I would like to already display the current value of the service in the table and in the course the examples are basic. Do I have to use Criteria or NamedQuery to be able to subselect inside findAll()???

Solution

I found an answer in another post here in the forum. I will leave the answer in case someone also needs help like I need. HQL doesn't actually implement the MySQL Limit function as I needed it, so one solution is to work with pure SQL itself.

This is the link: How to set a limit to inner query in Hibernate?

In my ServiceDaoImpl class I created the method:

public List<Service> findAllWithPrice() {
        NativeQuery<Service> query = this.getCurrentSession().createSQLQuery("select s.*, (select ps.price from priceServices ps where ps.idService = s.id order by ps.dateRegister DESC limit 1) as currentPrice from service s");
        query.addEntity( Service.class );

        return query.getResultList();
    }

And in the class Service that represents the entity create a field for the price:

@Entity
@Table(name = "service")
public class Service  {
    /****/
    private float currentPrice;

    // Getter and Setter of currentPrice
}

Answered By - Donadon