SQL exception using MyBatis

Question

I got a problem. When running this MyBatis select:

<select id="findIdByCode" resultType="long">
        select USER_ID from PWD_REST_CODE
        where RESTORATION_CODE = #{code}
        and current_date between date_from and DATE_FROM + interval #{interval} second
</select>

I got EJB Exception:

EJB exception occurred during invocation from home or business: 
com.project.auth.ejb.data.UserManagerBean_rdl8zk_Intf generated exception: 
org.apache.ibatis.exceptions.PersistenceException: ### Error querying 
database. Cause: java.sql.SQLSyntaxErrorException: ORA-00933: неверное 
завершение SQL-предложения ### The error may exist in 
com/project/auth/dao/UserDAO.xml ### The error may involve 
defaultParameterMap ### The error occurred while setting parameters ### SQL: 
select USER_ID from PWD_REST_CODE where RESTORATION_CODE = ? and current_date 
between date_from and DATE_FROM + interval ? second ### Cause: 
java.sql.SQLSyntaxErrorException: ORA-00933: неверное завершение SQL-предложения

(неверное завершение SQL-предложения stands for SQL command not properly ended)

But when I run exactly the same query in database manager:

    select USER_ID from PWD_REST_CODE
    where RESTORATION_CODE = '217799dfHj'
    and current_date between date_from and DATE_FROM + interval '86400' second

I got the deserved id (user_id, 5). Why it happens?

interval '86400' second is a bit different from interval 86400 second what you use in MyBasic. I suppose that because the type is NUMBER MyBatis doesn't add the quotes around it. — Marmite Bomber
– Marmite Bomber, Commented Oct 25, 2018 at 15:05

Roman-Stop RU aggression in UA · Accepted Answer · 2018-10-25 16:19:15Z

1

As Marmite Bomber correctly wrote in comments you can't use number in INTERVAL literal. Use numToDSInterval function to convert number to interval:

<select id="findIdByCode" resultType="long">
     select USER_ID from PWD_REST_CODE
     where RESTORATION_CODE = #{code}
         and current_date between date_from
         and DATE_FROM + numToDSInterval( #{interval}, 'second' )
</select>

answered Oct 25, 2018 at 16:19

Roman-Stop RU aggression in UA

16k3 gold badges52 silver badges59 bronze badges

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

Collectives™ on Stack Overflow

SQL exception using MyBatis

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related