Token SQL92 not supported

Question

I have some troubles in my app using sqlRestriction method:

This is my movie class:

public class Movie {

    @Id
    @Column(name = "pk")
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "movie_seq")
    @SequenceGenerator(name = "movie_seq", sequenceName = "movie_seq", allocationSize = 1, initialValue = 1)
    public long id;

    public long getId() {
        return id;
    }

    public void setId(long id) {
        this.id = id;
    }

    @Column(name = "name")
    public String str_name = "";

...

}

When i do something like that (dummy test):

List<Movie> movies = session.createCriteria(Movie.class)
        .add(Restrictions.sqlRestriction("1 in (1)"))
        .list();

It Works perfectly.

My second test (changing the Restriction):

.add(Restrictions.sqlRestriction("Movie.id in (166,171)"))

And my third test

add(Restrictions.sqlRestriction("{Movie}.id in (166,171)"))

Both of them fails in the same way:

ERROR: Token SQL92 no soportado en la posición: 345
Exception in thread "main" org.hibernate.exception.GenericJDBCException: could not extract ResultSet
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:47)
    ...
Caused by: java.sql.SQLException: Token SQL92 no soportado en la posición: 345
    at oracle.jdbc.driver.OracleSql.handleODBC(OracleSql.java:1275)
    ...

What am I doing wrong?

Edited

I tried :

.add(Restrictions.sqlRestriction("{alias}.id in (?, ?)", new Long[]{166L,171L}, new Type[]{Hibernate.Long, Hibernate.Long}) )

And i got this : "Long cannot be resolved or is not a field"

It seems "Hibernate.{AnyType}" is deprecated, see this link: Why Hibernate STRING can not be resolved?

i tried this instead:

.add(Restrictions.sqlRestriction("{alias}.id in (?, ?)", new Long[]{166L,171L}, new Type[]{LongType.INSTANCE, LongType.INSTANCE}) )

But I got this error:

Caused by: java.sql.SQLSyntaxErrorException: ORA-00904: "THIS_"."ID": invalid identifier

just curious was the latest with `public long id` or `public Long id`? but it seems that alias might not be set correctly — pirho, Oct 15 '17 at 13:21
Finally the error was just detected. I was using the name of the hibernate table/field names (I mean "id") it was changed to "pk". and it worked!. .add(Restrictions.sqlRestriction("pk in (?, ?)", new Long[]{166L,171L}, new Type[]{LongType.INSTANCE, LongType.INSTANCE}) ) I thought I have to use the HQL table/field names. But the names/fields of the phisicall table is the correct!. Answering your question: Im using this: @Column(name = "pk") public long id; I appreciate a lot your help — Lev, Oct 15 '17 at 13:32
of course. i missed `@Column(name = "pk")` and `sqlRestriction` it IS native sql NOT JPQL :D. well, learned something new — pirho, Oct 15 '17 at 14:00

pirho · Answer 1 · 2017-10-15T14:21:02.877

0

Not sure if you use the alias correctly. Try following

Restrictions.sqlRestriction("{alias}.pk in (?, ?)", new Long[]{166L,171L}, new Type[]{Hibernate.Long, Hibernate.Long}) )

Count of ? needs to the same as the size of given array so the (?,?) should be constructed & appended dynamically based on the array passed. As well as the type array.

EDIT: the id field name is not id but pk, missed @Column(name = "pk") in origal answer. Newer hibernate versions have no more 'Hibernate.Long' but use LongType.INSTANCE instead.

Restrictions.sqlRestriction("{alias}.pk in (?, ?)", new Long[]{166L,171L}, new Type[]{LongType.INSTANCE, LongType.INSTANCE}) )

edited Oct 15 '17 at 14:21

answered Oct 15 '17 at 11:19

pirho

11,565
12
43
70

I tried your solution, but it seems "Hibernate.Long" is deprecated. I think I have to use "LongType.INSTANCE". However, i received this error: "java.lang.ClassCastException: [J cannot be cast to java.lang.Long", maybe because it is an array.. But anyway. I tried with only a value (no an array) : sqlRestriction("{alias}.id = ?", 166L, LongType.INSTANCE) And I got this error: ORA-00904: "THIS_"."ID": invalid identifier – Lev Oct 15 '17 at 12:04
did it work, even `Hibernate.Long` is deprecated ? How about `Long.class` then ? – pirho Oct 15 '17 at 12:06
Sorry, I edited my comment.. Answering your last comment. Yes it fails. "Hibernate.INTEGER cannot be resolved". What package is it imported from? ¿import org.hibernate.Hibernate? – Lev Oct 15 '17 at 12:09
just realized also: change primitive `long` to `Long`, the type of `id` – pirho Oct 15 '17 at 12:10
using Long.class : The method sqlRestriction(String, Object[], Type[]) in the type Restrictions is not applicable for the arguments (String, Long[], Class) – Lev Oct 15 '17 at 12:10
Well. I changed the type of id in my Movie class (public Long id;). then i tried : .add(Restrictions.sqlRestriction("{alias}.id in ?", new Long[]{166L,171L}, Long.class) ) But i got : The method sqlRestriction(String, Object[], Type[]) in the type Restrictions is not applicable for the arguments (String, Long[], Class) – Lev Oct 15 '17 at 12:16
`org.hibernate.Hibernate` for `Hibernate.Long` i guess is the package. – pirho Oct 15 '17 at 12:32

score 0 · Answer 2 · answered Oct 15 '17 at 16:04

Solved. I was using the name of the hibernate table/field names (I mean "id") it was changed to "pk". and it worked!.

.add(Restrictions.sqlRestriction("pk in (?, ?)", new Long[]{166L,171L}, new Type[]{LongType.INSTANCE, LongType.INSTANCE}) )

I thought I have to use the HQL table/field names. But the names/fields of the phisicall table is the correct!.

Token SQL92 not supported

2 Answers2