Hibernate query returns a list of equals value

Question

I have this query:

Query query1 = session.createSQLQuery(
            //"select transactions.userId from transactions where transactions.id in (select tickets.transactionId from tickets where tickets.validate=:validate and tickets.auctionId in (select id from auctions where sellShare=:sellShare)) group by transactions.userId")
            "select transactions.userId, auctions.id from auctions inner join (tickets inner join transactions on tickets.transactionId = transactions.id) on auctions.id = tickets.auctionId where auctions.sellShare = :sellShare and tickets.validate=:validate")
            .addEntity(Test.class).setParameter("sellShare", 1).setParameter("validate", 1);
    List<Test> tests = query1.list();

The query returns a list of 20 fields but they are all equal. How can i fix it? I know that the query works fine because in mysql returns the correct fields.

enter image description here

Here is the xml mapping of Test:

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN" "http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
    <class name="applica.luxury.frontend.viewmodel.Test">
        <id name="id" type="long">
            <generator class="native" />
        </id>
        <property name="userId" />
    </class>
</hibernate-mapping>

Did you try `select disctinct transactions.userId .......` ? — Suresh Atta, Jul 22 '15 at 07:07
Did you tried same query with `Distinct` ? http://stackoverflow.com/questions/263850/how-do-you-create-a-distinct-query-in-hql — Subodh Joshi, Jul 22 '15 at 07:10

score 1 · Answer 1 · answered Jul 22 '15 at 07:15

Try this stuff

List<Test> tests = null;
    String query = "select transactions.userId, auctions.id from auctions inner join (tickets inner join transactions on tickets.transactionId = transactions.id) on auctions.id = tickets.auctionId where auctions.sellShare = :sellShare and tickets.validate=:validate";
    tests = (List<Test>) ((Query) session.createQuery(query).addEntity(Test.class).setParameter("sellShare", 1).setParameter("validate", 1)).list();

Iterate tests list and fetch value.Hope this work.

score 0 · Answer 2 · answered Jul 22 '15 at 07:49

You can probably use a ResultTransformer with Criteria.DISTINCT_ROOT_ENTITY for your Query. For example:

Query query1 = session.createSQLQuery(
            //"select transactions.userId from transactions where transactions.id in (select tickets.transactionId from tickets where tickets.validate=:validate and tickets.auctionId in (select id from auctions where sellShare=:sellShare)) group by transactions.userId")
            "select transactions.userId, auctions.id from auctions inner join (tickets inner join transactions on tickets.transactionId = transactions.id) on auctions.id = tickets.auctionId where auctions.sellShare = :sellShare and tickets.validate=:validate")
            .addEntity(Test.class).setParameter("sellShare", 1).setParameter("validate", 1).setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
    List<Test> tests = query1.list();

Hibernate query returns a list of equals value

2 Answers2