0

The parameters to be passed are the int type and the quantity is uncertain. How can I pass all the parameters at once in the case of uncertain parameters?

String SQL = "select * from table where enterprise_id in (?,?,?)";
int a,b,c = 1,2,3;//uncertain quantity
this.server.findBySql(SQL,a,b,c);

Is there a good way to avoid traversing parameters and splicing the query statements?

Martin.J
  • 3
  • 1

3 Answers3

2

I think the easiest way is to pass a list is to use org.springframework.jdbc.core.namedparam.MapSqlParameterSource.MapSqlParameterSource() which can take any type of argument for a prepared statement.

So, in your case, you can modify your SQL like this to take list parameter:

String sql = "select * from table where enterprise_id in (:listOfInt)";.

Then add the list as parameter:

MapSqlParameterSource sqlParams = new MapSqlParameterSource();        
sqlParams.addValue("listOfInt", Arrays.asList(1,2,3));

Pass it to the org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate instance to execute the query like this,

this.namedParameterJdbcTemplate.queryForList(sql, sqlParams);

Which gives you a resultset, further this assumes that you have created an instance of NamedParameterJdbcTemplate at the class level.

VPK
  • 3,010
  • 1
  • 28
  • 35
0

I'am not a Java dev, but you shall pass an array parameter to an IN SQL statement:

String SQL = "select * from table where enterprise_id in (?)";
int[] array = {1, 2, 3};//uncertain quantity
this.server.findBySql(SQL, array);

please see How to use an arraylist as a prepared statement parameter for the right way to do this.

Be carefull, if your array may be very large (I mean, very very large), you better have to insert it into a temporary table using a bulk insert and then to use a JOIN statement (but only if you may reach the MySQL IN limits).

fso
  • 144
  • 2
  • 14
0

We can definitely pass array of type int to stored procs, but not sure about statements.

However we can get away with workaround like this.

Let's say int values are coming from int[] arrData.

StringBuffer arguments= new StringBuffer();

for(Integer value:arrData){
 if(arguments.toString().length!=0)
  arguments.append(",");
  arguments.append(""+value+"");
}

and then finally pass comma separated values as input like this.

String SQL = "select * from table where enterprise_id in (?)";
this.server.findBySql(SQL,arguments.toString());
Dark Knight
  • 8,218
  • 4
  • 39
  • 58