Use SELECT DISTINCT in createQuery returning a list value

Question

I can't add SELECT DISTINCT taxtCode from this code:

public List<TblTaxType> findAlltaxtCode(String bfnsCode) {
        List<TblTaxType> result = null;

        String hql = "select distinct(taxtCode) from TblTaxType tbl_tax_type WHERE bfnsCode = ?";
        try {

                setSession(HibernateUtil.getSession());

                @SuppressWarnings("unchecked")
                List <TblTaxType>  resultList = getSession().createQuery(hql)
                                                            .setString(0, bfnsCode)
                                                            .list();


            if(!resultList.isEmpty()){  
                result = resultList; 
                Debugger.print("TAX CODES FOUND ");
            }else{
                Debugger.print("TAX CODES NOT FOUND ");
            }
        } catch (Exception e) {
            e.printStackTrace();
            Debugger.print(" TAX CODES NOT FOUND ");

        }

        Debugger.print(hql);
        closeSession();
        return result;
    }

Updated into whole code. The query is right but it seems its not returning a list value. Still java.lang.String cannot be cast to com.test.test.TblTaxType error appearing. How this query returns a list of value? The error occurs whenever a word DISTINCT is added. Is it impossible in HQL to use a distinct and return a list of value like in SQL Query?

I want to distinct the column name taxtCode whenever it finds the bfnsCode from table TblTaxType. The only problem here is I can't add a select tag, just started from `FROM` then it works perfectly. — Arem, Aug 25 '13 at 11:15

score 1 · Answer 1 · edited Nov 19 '13 at 11:56

1

You can also use Criteria and Projection together :

Criteria criteria = session.createCriteria(MyEntity.class);
criteria.setProjection(Projections.distinct(Projections.property( "id" )));

Hope it help someone.

edited Nov 19 '13 at 11:56

Vinoth Krishnan

2,925
6
29
34

answered Aug 26 '13 at 06:20

Ashish Chaurasia

1,747
17
23

score 1 · Accepted Answer · answered Aug 26 '13 at 09:53

1

Solved it using GROUP BY instead by using DISTINCT.

String hql = "FROM TblTaxType tbl_tax_type WHERE bfnsCode = ? GROUP BY taxtCode";

answered Aug 26 '13 at 09:53

Arem

35
2
10

score 0 · Answer 3 · edited May 23 '17 at 12:02

0

When you start your query with FROM TblTaxType OR Select * it returns the table rows with all your table Columns and assigns it to the list with List <TblTaxType> DataType hence it doesn't give any error.

But when you write Select colName it returns only the string present in that table cell. Which it can't convert to java.lang.String cannot be cast to com.test.test.classes.TblTaxType

There is no Problem in Using Distinct in Hibernate. See here :- How do you create a Distinct query in HQL

edited May 23 '17 at 12:02

Community

1
1

answered Aug 25 '13 at 11:38

user96546

57
2
11

So what should I do to return a string list value with distinct? – Arem Aug 26 '13 at 06:12

engma · Answer 4 · 2013-08-26T12:52:33.720

0

the right wat to do this with hibernate is

select tbl_tax_type  FROM TblTaxType tbl_tax_type WHERE BFNS_CODE = ?

EDIT 2

to get specific list of distinct column use:

select distinct(taxtCode) from TblTaxType WHERE BFNS_CODE = ?

and in hibernate you don't have to use the first select statement to get a list, just use the from statement like this:

from TblTaxType where BFNS_CODE = ?

edited Aug 26 '13 at 12:52

answered Aug 25 '13 at 12:20

engma

1,849
2
26
55

Still `java.lang.String cannot be cast`. I think its not returning a string list. – Arem Aug 26 '13 at 07:45
`ERROR org.hibernate.hql.PARSER - line 1:40: unexpected token: tbl_tax_type` – Arem Aug 26 '13 at 08:48
Edit it to `select distinct(tbl_tax_type.taxtCode) FROM TblTaxType tbl_tax_type WHERE bfnsCode = ?` Still **java.lang.String cannot be cast** – Arem Aug 26 '13 at 08:51
please try my new edit, and I tried a similar query in my application and it works perfectly, you made me suspect myself. check your coding, but this is the right way to it hibernate – engma Aug 26 '13 at 12:53
Sorry but its not really working. I post my answer. Solving using that code. Kindly check it. – Arem Aug 26 '13 at 13:19
what database you're using ? – engma Aug 26 '13 at 14:19

Use SELECT DISTINCT in createQuery returning a list value

4 Answers4