CODEWITHSUNDEEP
sqloracle-databasemybatisdao
A. Vasyukhin
A. Vasyukhin

Reputation: 99

SQL exception using MyBatis

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?

Upvotes: 3

Views: 1074

Answers (1)

Roman-Stop RU aggression in UA
Roman-Stop RU aggression in UA

Reputation: 15871

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>

Upvotes: 1

Related Questions