Mysql Stored procedure with java String Array

Question

I need a stored procedure to compare mobile number list in my database table. Example my table contain 100 rows,I pass java String array to stored procedure to get matching row values return.

The array contain multiple mobile numbers.

If any mobile numbers matched in my table mobile number column value it return row values.

Any possibilities available ?

If available please help me..

I have other solution it's working in spring Jdbc with mysql.

private static final String GET_MATCHING_MOBILE_NUMBERS = "SELECT USER_ID, USER_NAME, REGISTRATION_ID, IMEI_CODE, MOBILE_NUMBER FROM USER WHERE MOBILE_NUMBER IN (";

@Override
@Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.READ_COMMITTED)
public List<UserDO> getMatchingExistingUsers(final String[] mobileNumbers) throws UserDataException {
    JdbcTemplate jd = this.getJdbctemplate();
    Object[] params = mobileNumbers;
    List<UserDO> userDOs = jd.query(getSqlQuery(GET_MATCHING_MOBILE_NUMBERS, mobileNumbers), params, new BeanPropertyRowMapper<UserDO>(UserDO.class));
    return userDOs;
}

private String getSqlQuery(String query_string, String[] array_values) {
    StringBuilder query_builder = new StringBuilder();
    query_builder.append(query_string);
    for (@SuppressWarnings("unused")
    String values : array_values) {
        query_builder.append("?,");
    }
    query_builder.append("*");
    String formated_query = query_builder.toString().replace(",*", ")");
    return formated_query;
}

I want Mysql stored procedure if possible for above code.. Advance thanks..

score 0 · Answer 1 · answered Feb 10 '15 at 09:49

Select query is

private static final String GET_MATCHING_MOBILE_NUMBERS = "SELECT USER_ID, USER_NAME, REGISTRATION_ID, IMEI_CODE, MOBILE_NUMBER FROM USER WHERE FIND_IN_SET(MOBILE_NUMBER,?);";

Jdbc code is

List<UserDO> userDOs = jd.query(GET_MATCHING_MOBILE_NUMBERS, new Object[] { formatedSqlText }, new BeanPropertyRowMapper<UserDO>(UserDO.class));

It's working fine without using String Builder and Stored procedure.

Mysql Stored procedure with java String Array

1 Answers1

Linked