0

After adding AND condition to the below mentioned query

  String querys = "SELECT dr.id,dr.creation_time,dr.drawing_spec_format,dr.end_time,dr.error_type,dr.last_access_time,dr.server_name,dr.start_time,dr.supply_unit,dr.client_id,ds.request_id,ds.bb,ds.car_offset_g,ds.car_sling_type,ds.car_type,ds.ch,ds.country"
            + " FROM FlcDrawingRequests dr, FlcDrawingRequestStats ds "
            + " where dr.id=ds.request_id"
            + " AND "
            + "(dr.start_time > "
            + monthStartDate
            + ")"
            + " AND "
            + "(dr.start_time <= "
            + monthEndDate + ")";

i am getting the below exception

 java.lang.IllegalArgumentException: 
         org.hibernate.hql.ast.QuerySyntaxException: unexpected token: 00 near line 1, column 851 [SELECT dr.id,dr.creation_time,dr.drawing_spec_format,dr.end_time,dr.error_type,dr.last_access_time,dr.server_name,dr.start_time,dr.supply_unit,dr.client_id,ds.request_id,ds.bb,ds.car_offset_g,ds.car_sling_type,ds.car_type,ds.ch,ds.country FROM com.kone.kss.cad.flcws.FlcDrawingRequests dr, com.kone.kss.cad.flcws.FlcDrawingRequestStats ds  where dr.id=ds.request_id AND (dr.start_time > 2019-04-01 00:00:00) AND (dr.start_time <= 2019-4-2 23:59:59)]
at org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:624)
at org.hibernate.ejb.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:96)

Could you please help me on this issue!

arjun
  • 107
  • 1
  • 1
  • 12
  • Your SQL statement is not safe against SQL injection! Btw you only need to quote the dates to get this statement running without errors. But take a look at @Tim Biegeleisen's anwser which is much better. – Marcel Goldammer Apr 02 '19 at 07:06

1 Answers1

1

This answer assumes that you are trying to execute a native query. In any case, you should be using a prepared statement, and this would actually resolve the source of the error, which has to do with unquoted date literals.

String sql = "SELECT dr.id, dr.creation_time, dr.drawing_spec_format, dr.end_time, dr.error_type, dr.last_access_time, dr.server_name, dr.start_time, dr.supply_unit, dr.client_id, ds.request_id, ds.bb, ds.car_offset_g, ds.car_sling_type, ds.car_type, ds.ch, ds.country ";
sql += "FROM FlcDrawingRequests dr ";
sql += "INNER JOIN FlcDrawingRequestStats ds ";
sql += "ON dr.id = ds.request_id AND ";
sql += "dr.start_time > ?1 AND ";
sql += "dr.start_time <= ?2";

Query q = em.createNativeQuery(sql);

q.setParameter(1, monthStartDate);
q.setParameter(2, monthEndDate);

Note that I have also replaced your old school implicit joins with explicit inner joins. This is the preferred way of writing a join in modern SQL.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • Query query = getEntityManager().createQuery(sql) .setParameter(1, monthStartDate).setParameter(2, monthEndDate); – arjun Apr 02 '19 at 10:44
  • when i use above query getting the below Exception: org.hibernate.hql.ast.QuerySyntaxException: unexpected token: ON near line 1, column 343 [SELECT dr.id,..... could you please help me on this! – arjun Apr 02 '19 at 10:45
  • You didn't run my code at all. First, you need to tell us whether you are trying to run a raw SQL query or an HQL query. – Tim Biegeleisen Apr 02 '19 at 10:51
  • i am running a raw SQL query. – arjun Apr 02 '19 at 10:52
  • The method createSQLQuery(String) is undefined for the type EntityManager – arjun Apr 02 '19 at 11:05
  • if we use Query query = getEntityManager().createNativeQuery(sql) .setParameter(1, monthStartDate).setParameter(2, monthEndDate); getting below exception: Caused by: java.sql.SQLException: No database selected – arjun Apr 02 '19 at 11:20
  • Then maybe you have some other configuration problem not necessarily related to your original question. – Tim Biegeleisen Apr 02 '19 at 11:24
  • it works when no and conditions in query with createQuery(String); – arjun Apr 02 '19 at 11:29