SQL fuzzy query report: ORA-00909: invalid number of parameters solution

When using Oracle database for fuzzy query,

The console error is shown in the following figure:

The reason is that I typed too fast and the grammar was wrong.

The correct way to write it is

pd.code like concat(concat('%',#{keyword}),'%')

java.sql.SQLSyntaxErrorException: ORA-00909: invalid number of parameters

I encountered this exception when using MyBatis for multi-parameter fuzzy query. I looked at the print log and found that the exception occurred after pre-compilation and when inserting actual parameters.

==> Preparing: select role_id, role_name, note from t_role where role_name like concat('%', ?, '%') and note like concat('%', ?, '%')
2018-12-13 20:24:28,567 DEBUG [com.ss.learn.chapter3.mapper.RoleMapper.getRolesByIdAndNote] - ==> Parameters: 1(String), 1(String)

Exception message: The number of parameters is invalid. Checked the SQL statement

select role_id, role_name, note from t_role
where role_name like concat('%', ?, '%') and note like concat('%', ?, '%')

I found that the problem occurred in concat. concat is a function that connects two strings. Here, three are connected. Change the SQL to two nested concats.

<select id="getRolesByIdAndNote" parameterType="map" resultType="role">
        select role_id, role_name, note from t_role 
        where role_name like concat(concat('%', #{roleName}), '%')
        and note like concat(concat('%', #{note}), '%')
    </select>

Summarize

The operation was successful! The above is my personal experience. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM!