user1935987 user1935987 - 3 months ago 12
Java Question

Spring getJdbcTemplate().update insert dynamic object

I never met such a prob before, google doesn't help.

I use Spring Framework,

JdbcDaoSupport
,
getJdbcTemplate().update()
method.
I have a dynamic query with 2 optional parameters.

Don't know the right way to input the dynamic object.
this is how i try:

StringBuilder sbUserRegQuery = new StringBuilder();
sbUserRegQuery.append("INSERT INTO users (username, password , email, enabled, datetime_condo_changed, datetime_last_login");
if(user.getCondo_id()!=null) sbUserRegQuery.append(", condo_id");
if(user.getProvider()!=null) sbUserRegQuery.append(", provider");
sbUserRegQuery.append(")");
sbUserRegQuery.append(" VALUES ( ?, ?, ? , ? , ?, ?");
if(user.getCondo_id()!=null) sbUserRegQuery.append(", ?");
if(user.getProvider()!=null) sbUserRegQuery.append(", ?"); //default - "local"
sbUserRegQuery.append(");");

Object regObject = new Object[]{
user.getUsername(), PasswordEncoderGenerator.main(user.getPassword()), user.getEmail(), enabled, user.getDatetime_condo_changed(), currentTimeStamp, user.getCondo_id()
};

int row = getJdbcTemplate().update(sbUserRegQuery.toString(), regObject, user.getCondo_id(), user.getProvider());


i.e. i'm trying to insert the dynamic object after the
regObject
.
But, in this case, the order of dynamic object can be changed, and query fails..

How to solve this case?

Answer

You almost have it by using the conditional checks on creating the SQL but you then don't do that for the variable arguments being passed into the update() method.

http://docs.spring.io/spring/docs/current/javadoc-api/org/springframework/jdbc/core/JdbcTemplate.html#update-java.lang.String-java.lang.Object...-

StringBuilder sbUserRegQuery = new StringBuilder();
sbUserRegQuery.append("INSERT INTO users (username, password , email, enabled, datetime_condo_changed, datetime_last_login");
if(user.getCondo_id()!=null) sbUserRegQuery.append(", condo_id");
if(user.getProvider()!=null) sbUserRegQuery.append(", provider");
sbUserRegQuery.append(")");
sbUserRegQuery.append(" VALUES ( ?, ?, ? , ? , ?, ?");
if(user.getCondo_id()!=null) sbUserRegQuery.append(", ?");
if(user.getProvider()!=null) sbUserRegQuery.append(", ?");
sbUserRegQuery.append(");");

ArrayList<Object> params = new ArrayList<Object>();
params.add(user.getUsername());
params.add(PasswordEncoderGenerator.main(user.getPassword()));
params.add(user.getEmail());
params.add(enabled);
params.add(user.getDatetime_condo_changed());
params.add(currentTimeStamp);
if(user.getCondo_id()!=null) params.add(user.getCondo_id());
if(user.getProvider()!=null) params.add(user.getProvider());

int row = getJdbcTemplate().update(sbUserRegQuery.toString(), params.toArray());