Selecting distinct records using Quarkus PanacheEntity

Question

I have a table(MySql) like

(ID(primary-key), Name, rootId)
(2, asdf, 1)
(3, sdf, 1)
(12, tew, 4)
(13, erwq, 4)

Now I want to select distinct root tsg_ids present in database. In this case It should return 1,4.

I tried

List<Entity> entityList = Entity.find(SELECT DISTINCT t.rootId from table t).list().

In debug mode, I see entity list contains ("1", "4"). Entity.find() can only be taken into "Entity" object, but what I am getting from select query is String. So, I was not able to convert the Entity object to String object in this case.

Is there a way to get distinct values of a non-primary column using PanacheEntity?

Answer 1

I don't know if you are using Panache with Hibernate Reactive or with Hibernate ORM, but, at the moment, if you want to use Panache, you have to use a projection:

@RegisterForReflection
public class EntityRootIdView {
   public final Long rootId;

   public EntityRootIdView(Long rootId){ 
        this.rootId = rootId;
   }
}

// Panache with Hibernate ORM
List<EntityRootIdView> rootIds = Entity
    .find("SELECT DISTINCT t.rootId from Entity t")
    .project(EntityRootIdView.class)
    .list()

// Panache with Hibernate Reactive
Uni<List<EntityRootIdView>> rootIds = Entity
    .find("SELECT DISTINCT t.rootId from Entity t")
    .project(EntityRootIdView.class)
    .list()

Note that this requires at least Quarkus 2.12.0.Final

Alternatively, you can use the Hibernate Reactive session:

Uni<List<Long>> rootIds = Panache.getSession()
    .chain(session -> session
        .createQuery("SELECT DISTINCT t.rootId from Entity t", Long.class)
        .getResultList() )
    );

Or, if you are using Hibernate ORM, the entity manager:

List<Long> rootIds = Panache.getEntityManager()
    .createQuery( "SELECT DISTINCT t.rootId from Entity t", Long.class )
    .getResultList();

Funny enough, I've just created an issue to make this easier.