0

I have two tables TUser and TComment, relation 1 to many. I want to write a Hibernate query which extract unique TUsers which did comments.

I tried:

EntityManager em = HibernateUtil.getEntityManager();

        Query queryCommentsAuthors = em.createQuery("select  u.author  from TComment u group by u.author");

Error: 

org.hibernate.exception.SQLGrammarException: could not extract ResultSet
Caused by: java.sql.SQLException: ORA-00979: not a GROUP BY expression

and this:

Query queryCommentsAuthors = em.createQuery("select distinct u.author from TComment");

Error:

org.hibernate.exception.SQLGrammarException: could not extract ResultSet
Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
Caused by: java.sql.SQLException: ORA-00932: inconsistent datatypes: expected - got BLOB

But without success. I appreciate any advices to cope with this problem.

Here is the part of my bean class where i have TUser object:

@Entity
@Table(name = "T_COMMENT")
public class TComment implements java.io.Serializable {

    private TUser author;
    ....
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "AUTHOR_ID", nullable = false)
    public TUser getAuthor() {
        return this.author;
    }

    public void setAuthor(TUser author) {
        this.author = author;
    }
    .....
}

This is the DDL of the table:

create table T_COMMENT
(
  id           NUMBER(19) not null,
  comment_body VARCHAR2(4000 CHAR) not null,
  comment_date DATE not null,
  author_id    NUMBER(19) not null,
)
Tot
  • 207
  • 8
  • 25
  • 2
    "*But without success*" is neither a valid Oracle error message nor a valid Java exception. –  May 17 '14 at 12:51
  • Sorry, i will update my question. – Tot May 17 '14 at 13:20
  • You can't do unique sorts on a BLOB... why on earth is the author name a BLOB? Isn't a 4,000 byte string long enough? – Ben May 17 '14 at 13:26
  • No, it is not a BLOB. author is of type TUser. – Tot May 17 '14 at 13:27
  • Your error message says that it's a BLOB @Tot... and you've posted nothing to disprove that... What does the DDL for the table give you? Is the `TUser` declared with a sub-type of a BLOB? – Ben May 17 '14 at 13:28
  • I added more information. – Tot May 17 '14 at 13:34
  • You have, however, the table name doesn't match the one that you're selecting from and it has no column author, which makes it unlikely to be the actual one you're using... – Ben May 17 '14 at 13:36
  • In TUser I have BLOB (user's photo). Do you have any idea how to solve this problem? – Tot May 17 '14 at 13:44
  • Drop the table and re-create it from your DDL; the mismatch between the DDL and the error message is your warning sign. – Josh May 17 '14 at 14:07
  • Have you tried: Query queryCommentsAuthors = em.createQuery("select distinct u.author from TComment c join c.author u"); ? – Vlad Mihalcea May 17 '14 at 20:10
  • I changed the structure of the table and i did what i wanted. Thanks to all for advices. – Tot May 18 '14 at 20:28

0 Answers0