Passing array object to jdbc query

Question

I am developing an application in which i want to compare the list of phone numbers from the mobile device with database(MYSQL). I am passing phone numbers as array list.

here is my code:

ArrayList<String> arrayList = new ArrayList<String>();
    arrayList.add("1212121212");
    arrayList.add("1234567890");
    arrayList.add("1515151515");
    arrayList.add("1111111111");
    arrayList.add("2222222222");
    arrayList.add("3333333333");

    List<UserDO> userDOs = userDAOImpl.getExistingMobileNumber(arrayList);

The above code is my test case and the following code is my database query for selecting mobile number

private static final String SELECT_MOBILE_NUMBER = "SELECT USER_ID, USER_NAME, REGISTRATION_ID, MOBILE_NUMBER FROM USER WHERE MOBILE_NUMBER IN (?)";

Here is my DAOImpl code:

public List<UserDO> getExistingMobileNumber(ArrayList<String> mobileNumbers) throws UserDataException {
    JdbcTemplate jd = this.getJdbctemplate();
    List<UserDO> userDOs = jd.query(SELECT_MOBILE_NUMBER, new Object[] { mobileNumbers }, new RowMapper<UserDO>(){

        @Override
        public UserDO mapRow(ResultSet rs, int rowNum) throws SQLException {
            UserDO userDO = new UserDO();
            userDO.setMobileNumber(rs.getString(4));
            return userDO;
        }

    });
    return userDOs;
}

Actually my problem is when i pass mobile as string as following code

     List<UserDO> userDOs = jd.query(SELECT_MOBILE_NUMBER, new Object[] { "2222222222" }, new RowMapper<UserDO>(){
......
......
}
}

it's working fine,its giving expected output.But when i pass as "mobileNumbers" instead of "2222222222" its not giving expected output.Any one explain me,can we pass array list object in jdbc Template Query().If so how can we pass?

Thanks in advance

Check this StackOverflow link: http://stackoverflow.com/questions/4504592/how-to-use-select-in-clause-in-jdbctemplates — Sushant Tambare, Feb 03 '15 at 05:43

score 1 · Answer 1 · answered Feb 03 '15 at 05:40

1

In your solution you use

query(String sql, Object[] args, RowMapper rowMapper)

method of JdbcTemplate

you can use

query(PreparedStatementCreator psc, RowMapper rowMapper)

and create prepareStatement with your query and arrayList by yourself.

answered Feb 03 '15 at 05:40

Vitaly

2,552
2
18
21

score 0 · Accepted Answer · edited Apr 27 '15 at 10:02

You will need multiple ? in your SQL query depending on the number of items in your ArrayList. Therefore use StringBuilder to construct your SQL query:

StringBuilder sql = new StringBuilder()
sql.append("SELECT USER_ID, USER_NAME, REGISTRATION_ID, MOBILE_NUMBER FROM USER WHERE MOBILE_NUMBER IN (");

for(String mobileNumber: mobileNumbers) {
      sql.append("?,");
    }
sql.append(")");

//use this to remove the extra comma at the end of your IN() clause
String query = sql.toString().replace(",)", ")");

//Convert your list to array for use with jdbcTemplate
Object[] params = mobileNumbers.toArray();

// if you have an entity class to map the resultset to, I suggest you use BeanPropertyRowMapper
List<YourEntityClass> list = jdbcTemplate.query(query, params, new BeanPropertyRowMapper<YourEntityClass>(YourEntityClass.class));

Hope this helps.

EDIT

Passing array object to jdbc query

2 Answers2