CODEWITHSUNDEEP
javasqlhibernate
Manish Patel
Manish Patel

Reputation: 157

Getting unexpected Token error in sql query

org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: TOP near line 1, column 8 [SELECT TOP 10 IServe.ID FROM TopUp.dbo.IServe WHERE ExpireDate >= '2019-10-03' AND TelcoID = '2' AND ProductID = '2' AND RechargeAmt = '100.0' AND Available = 1 ORDER BY ExpireDate, SN]

String query3 = "SELECT TOP " + importStockList.getOrderQuantity() +" IServe.ID FROM IServe WHERE "
            + " ExpireDate >= '" + sqlDate + "' " + " AND TelcoID = '" + importStockList.getTelcoId()
            + "' AND ProductID = '" + importStockList.getProductId() + "' AND " + "RechargeAmt = '"
            + importStockList.getRechargeAmt() + "' AND Available = 1 ORDER BY ExpireDate, SN" ;

    Session hbsessionSQL = HibernateUtilSQL.getSessionFactory().openSession();
    List<Iserve> iserve = hbsessionSQL.createQuery(query3).list();

Can you please help me this error. I am stuck here

Upvotes: 1

Views: 4077

Answers (2)

Rena
Rena

Reputation: 81

Since its not understood what type your variables are, try to see the data by yourself. If there is an option string values contain special characters, remove them first.

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522551

While your query is hard to read, and you should be using a prepared statement, I don't see anything wrong per se about the syntax. So the error is probably happening because TOP is not valid HQL syntax. TOP is really only supported on Microsoft databases, such as SQL Server or Access. Try using LIMIT instead:

try {
    Session session = HibernateUtilSQL.getSessionFactory().openSession();
    Connection conn = session.connection();
    String sql = "SELECT ID FROM IServe WHERE ExpireDate >= ? AND TelcoID = ? AND ProductID = ? AND RechargeAmt = ? AND Available = 1 ORDER BY ExpireDate, SN LIMIT ?";
    PreparedStatement ps = conn.prepareStatement(sql);
    ps.setDate(1, sqlDate);
    ps.setInt(2, importStockList.getTelcoId());
    ps.setInt(3, importStockList.getProductId());
    ps.setInt(4, importStockList.getRechargeAmt());
    ps.setInt(5, importStockList.getOrderQuantity());
    ResultSet rs = ps.executeQuery();

    while(rs.next()) {
        // process result set here
    }
}
catch(HibernateException e) {
    e.printStackTrace();
}

Upvotes: 1

Related Questions