Reputation: 157
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
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
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