Reputation: 109
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
Upvotes: 1
Views: 9600
Reputation: 1567
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
read more here
http://www.mkyong.com/spring/spring-jdbctemplate-querying-examples/
Upvotes: 0
Reputation: 2662
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.
Upvotes: 1