Parameter index out of range (1 > number of parameters, which is 0) in MySQL

Question

Im using Spring mvc, Hibernate and Mysql, in my DAOImplement i call sql file outsite.

This is my DAO:

public List<RenterDto> searchByNamePersonalId(String name, String personalId) {
    Session session = sessionFactory.openSession();
    String sqlString = GetSqlUtils.getSqlQueryString(RenterDaoImpl.class, SQL_DIR + SEARCH_BY_NAME_PERSON);
    List<RenterDto> list = new ArrayList<RenterDto>(0);
    try {
        Query query = session.createSQLQuery(sqlString);
        query.setParameter(0, name);
        query.setParameter(1, personalId);
        list = query.setResultTransformer(new AliasToBeanResultTransformer(RenterDto.class)).list();
    } catch (HibernateException e) {
        logger.error("error at RenterDaoImpl.searchByNameAddress: " + e.getMessage());
    } finally {
        session.close();
    }
    return list;
}

My SQL called (SEARCH_BY_NAME_PERSON), use to search name and personalId:

SELECT 
  R.ID, 
  R.NAME, 
  R.ACCOUNT_ID, 
  R.OTP_CODE, 
  R.OS_TYPE, 
  R.MANCHINID, 
  R.TYPE, 
  R.PERSONAL_ID, 
  R.PHONENUMBER, 
  R.EMAIL, 
  R.ADDRESS, 
  R.DISTRICT_ID, 
  D.NAME AS DISTRICT_NAME, 
  R.CITY_ID, 
  C.NAME AS CITY_NAME
FROM 
   RENTER R 
INNER JOIN 
   DISTRICT D 
ON 
   R.DISTRICT_ID = D.ID 
INNER JOIN 
   CITY C 
ON 
   R.CITY_ID = C.ID
WHERE 
   LOWER(R.NAME)
LIKE 
   ('%'||'?')
OR
   R.PERSONAL_ID
LIKE
   '%'||'%'

When I search with keyword: 'name' or 'personalId', I received an error result following as:

SqlExceptionHelper - SQL Error: 0, SQLState: S1009
SqlExceptionHelper - Parameter index out of range (1 > number of parameters, which is 0).
RenterDaoImpl - error at RenterDaoImpl.searchByNameAddress: could not execute query

How to fix this the problem ? Thank you so much !

Answer 1

You have this expression:

LIKE ('%'||'?')

I am guessing that you want '?' to be a parameter. But it is a lowly string, with just a single question mark.

Instead:

LIKE CONCAT('%', ?)

In general, in MySQL, you want to use CONCAT() for string concatenation.