syntax error in database query with Hibernate

Question

I have the following query. When I execute it I get such error:

[http-nio-8090-exec-9] WARN  org.hibernate.engine.jdbc.spi.SqlExceptionHelper - SQL Error: 0, SQLState: 42601
[http-nio-8090-exec-9] ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper - ERROR: syntax error at or near "."
  Position: 5385

I think the problem is in select CG.codes from CodeGroup CG, but how do I write this query correctly? I need to get all the codes that belong to the CodeGroup. The codes is a list of Code.

    StringBuilder queryBuilder = new StringBuilder();
    queryBuilder.append("select distinct AI from AppInfo AI ")
            .append("left join fetch AI.plan as PSAP ")
            .append("where PSAP.edType in ( select C from Code C where C.column1= 'XXXX' ")
            .append("and C in (select CG.codes from CodeGroup CG where CG.name = 'YYYY'))");

Are you selecting AI column from AppInfo named AS AI? (select distinct AI from AppInfo AI). So I think SQL does not understand which AI do you want to use there join fetch AI.plan. — Dumbo
– Dumbo, Commented Feb 1, 2018 at 10:15
I select entire AppInfo object from the table. The AI has a column with plan's id based on which I get PSAP from PSAP table. The problem with the query started when I add the last append. Before that everything works fine. — krltos
– krltos, Commented Feb 1, 2018 at 10:25
That looks like HQL - but the database doesn't understand HQL, only SQL. — user330315
– user330315, Commented Feb 1, 2018 at 10:30

FrankPl · Accepted Answer · 2018-02-01 10:15:40Z

1

Your line left join fetch AI.plan as PSAP is wrong. Omit the fetch.

answered Feb 1, 2018 at 10:15

FrankPl

6016 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Indra K · Accepted Answer · 2018-02-01 10:37:31Z

0

What i have observed that, Your select query has small problem, It should be like this

StringBuilder queryBuilder = new StringBuilder();
    queryBuilder.append("select DISTINCT AI.COLUMN_NAME from AppInfo AI ")
            .append("left join fetch AI.plan as PSAP ")
            .append("where PSAP.edType in ( select C from Code C where C.column1= 'XXXX' ")
            .append("and C in (select CG.codes from CodeGroup CG where CG.name = 'YYYY'))");

Because, AI is the alias name of AppInfo table. Distinct will work on specific column.

edited Feb 1, 2018 at 10:37

answered Feb 1, 2018 at 10:26

Indra K

1341 silver badge2 bronze badges

1 Comment

Indra K Over a year ago

Perfect, It's not function it's a keyword. you can use only to get distinct values.

Collectives™ on Stack Overflow

syntax error in database query with Hibernate

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related