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?
CodePudding user response:
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 String rootId;
public EntityRootIdView(String 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<String>> rootIds = Panache.getSession()
.chain(session -> session
.createQuery("SELECT DISTINCT t.rootId from Entity t", String.class)
.getResultList() )
);
Or, if you are using Hibernate ORM, the entity manager:
List<String> rootIds = Panache.getEntityManager()
.createQuery( "SELECT DISTINCT t.rootId from Entity t", String.class )
.getResultList();
Funny enough, I've just created an issue to make this easier.