New to JPA: How to get distinct 2 fields.

[Paul Z. Wu]

I tried this:

  TypedQuery<Object[]> q = em.createQuery("select  distinct t.listName, t.word  from WordList t", Object[].class);
     
      out.println(q.getResultList().size() +"<br/>") ;
      
      for (Object[] e: q.getResultList())
      {
     
    out.println(e[0] +".."+ e[1] + "<br/>");
   
      }
 
I got the result: 
5
test..word
test..word
test..word
test..word
test..word


What is the proper way to get only one row?
 
Paul Z. Wu
 
http://www.elookinto.com

[Konstantin Ignatyev]

I tend to shy away from JPA/Hibernate etc. but used to love them.
HQL is not SQL so distinct makes sense in relation to object in HQL context
so it should be something like

q = em.createQuery("select distinct t from WordList t")

if you need SQL level distinct, then I suggest just using SQL via myBatis
or with JDBCTemplate.

JPA sucks in my opinion for bulk operations on few fields (reports,
lookups, etc.) and databases excel at that, so I see no reason to shy away
from performing operations directly and skipping layers of abstraction.

http://stackoverflow.com/questions/263850/how-do-you-create-a-distinct-query-in-hql

TypedQuery<Object[]> q = em.createQuery("select distinct t.listName,
t.word from WordList t", Object[].class);
out.println(q.getResultList().size() +"<br/>") ;
for (Object[] e: q.getResultList())
{
out.println(e[0] +".."+ e[1] + "<br/>");
}
5
test..word
test..word
test..word
test..word
test..word
What is the proper way to get only one row?
Paul Z. Wu
http://www.elookinto.com

--
Konstantin Ignatyev

PS: If this is a typical day on planet Earth, humans will add fifteen
million tons of carbon to the atmosphere, destroy 115 square miles of
tropical rainforest, create seventy-two miles of desert, eliminate between
forty to one hundred species, erode seventy-one million tons of topsoil,
add 2,700 tons of CFCs to the stratosphere, and increase their population
by 263,000

Bowers, C.A. The Culture of Denial: Why the Environmental Movement Needs a
Strategy for Reforming Universities and Public Schools. New York: State
University of New York Press, 1997: (4) (5) (p.206)

[Laird Nelson]

TypedQuery<Object[]> q = em.createQuery("select distinct t.listName,
t.word from WordList t", Object[].class);

[snip]

What is the proper way to get only one row?

I haven't looked at this in detail, but I wonder if DISTINCT is making use
of Object.equals()? If so, using a constructor expression for an object
with an overridden equals() method might do what you want. Interesting
experiment for me to try tonight.

Best,
Laird