unexpected token error while running PostgreSQL query in Java

Question

I have created the following query for PostgreSQL which is working fine in SqlWorkbench. But when I integrated this in Java code, I'm getting a syntax error exception:

Query running fine in SqlWorkbench:

ELECT poaiF.fnsku, 
       poaiF.acknowledgement_type_code AS last_ack_code 
FROM   po_acknowledgement_items poaiF, 
       (SELECT Max(poa.po_acknowledgement_id) AS last_po_ack_id, 
               poai.fnsku 
        FROM   po_acknowledgement_items poai, 
               po_acknowledgements poa 
        WHERE  poa.po_acknowledgement_id = poai.po_acknowledgement_id 
               AND poa.order_id = '5D7P2FLB' 
        GROUP  BY poai.fnsku) t 
WHERE  t.last_po_ack_id = poaiF.po_acknowledgement_id 
       AND t.fnsku = poaiF.fnsku

Query in java (giving syntax error in beginning of (SELECT Max(poa.po_acknowledgement_id) AS last_po_ack_id):

private static final String LAST_ACK_CODE_QUERY1 = "SELECT poaiF.fnsku, \n" +
        "       poaiF.acknowledgement_type_code AS last_ack_code \n" +
        "FROM   po_acknowledgement_items poaiF, \n" +
        "       (SELECT Max(poa.po_acknowledgement_id) AS last_po_ack_id, \n" +
        "               poai.fnsku \n" +
        "        FROM   po_acknowledgement_items poai, \n" +
        "               po_acknowledgements poa \n" +
        "        WHERE  poa.po_acknowledgement_id = poai.po_acknowledgement_id \n" +
        "               AND poa.order_id = \'5D7P2FLB\' \n" +
        "        GROUP  BY poai.fnsku) t \n" +
        "WHERE  t.last_po_ack_id = poaiF.po_acknowledgement_id \n" +
        "       AND t.fnsku = poaiF.fnsku";

While running the query through the integration test, getting the following error:

unexpected token: ( near line 4, column 8 which is the beginning of (SELECT Max(poa.po_acknowledgement_id)

I am running through org.springframework.orm.hibernate3.LocalSessionFactoryBean which is as follows.

SessionFactory postgresSessionFactory; //initialized with org.springframework.orm.hibernate3.LocalSessionFactoryBean class.


final Session session = postgresSessionFactory.getCurrentSession();

String query = LAST_ACK_CODE_QUERY;

final Query selectQuery = session.createQuery(query).setTimeout(queryTimeout);
final List<Object[]> terms = selectQuery.list();

Please help me to resolve this issue.

Tip of today: Always use modern, explicit JOIN syntax. Easier to write (without errors), easier to read and maintain, and easier to convert to outer join if needed! — jarlh
– jarlh, Commented Dec 3, 2019 at 20:17

Michele Dorigatti · Accepted Answer · 2019-12-03 21:56:08Z

2

From Hibernate documentation:

Query createQuery(String queryString)
Create a Query instance for the given HQL query string.

If you want a SQL query, you need to use createSQLQuery instead:

SQLQuery createSQLQuery(String queryString)
Create a SQLQuery instance for the given SQL query string.

answered Dec 3, 2019 at 21:56

Michele Dorigatti

8191 gold badge9 silver badges19 bronze badges

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

1 Comment

Shubha Debnath Over a year ago

SQLQuery not working. I could run a simple query with creatQuery. Initial issue was, entity mapping was not right. I corrected it and it ran successfully. But now I am not able to join virtual table. As you see in the original query, I am joining a virtual table, and hibernate is complaining there.

Collectives™ on Stack Overflow

unexpected token error while running PostgreSQL query in Java

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related