JdbcTemplate to use in query<<Help Required>>

[Mukesh Antil]

Hi
I am having a problem in using the query using JDBC Template

public void deleteProduct(int productIds[])
{
Object arr[]=new Object[1];
arr[0]=productIds;
String query="delete from product where id in(?)";
this.execute(query,arr);

}

this method is in a DAO which gets the DataSource.
if i execute the same query individually for all ids in the List its executes well

Not geeting what the proble is

Thanks in advance
Mukesh Antil

[Loren Rosen]

Specifically what problem are you having? Are you, for example, getting an exception? What's the strack trace?

[dsklyut]

That sql will not work with jdbc.
What you are doing is binding an array of ints to a single "?" sql parameter. What you will need to do is to calculate number of elements in the productIds array and for each element in array you must have corresponding "?" in your where clause.
For example:
if you array has 2 elements
productIds[0] = 1;
productIds[1] = 123;
your sql will look like this
delete from product where id in ( 1, 123)

and you must pass an array of Integers to the execute method

String query = "delete from product where id in (";
StringBuffer sqlParams = new StringBuffer();
Object arr[] = new Integer[productIds.length];
if (int i = 0; i < arr.length; i ++)
{ 
   sqlParams.append("?");
   if (i < arr.length) sqlPrams.append(",");
   arr[i] = new Integer(productIds[i]);
}
query = query + sqlParams.toString() + ")";
execute(query, arr);

Dmitry

[Mukesh Antil]

Thanks dsklyut,
It was great but what my expectation is the spring should take care of this buring the paremeter binding time as some of the framework does.
Spring does this for Objects and array is also an Object then why not for array.

thanks

Mukesh

[Mukesh Antil]

Hi
The Complete Class code is
public class ProductDAO extends JdbcTemplate implements IProductDAO {
private final int finalProduct=1;
private final int intermediateProduct=2;
private final int rawMaterial=4;

public void getProduct(int productIds[])
{
Object arr[]=new Object[1];
arr[0]=productIds;
String query="select * from product where id in(?)";
SqlRowSet rows=this.queryForRowSet(query,arr);

}
public void deleteProduct(int productIds[])
{
Object arr[]=new Object[1];
arr[0]=productIds;
String query="delete from product where id in(?)";
this.execute(query);

}

}
The stack trace is that is Recieved is
org.springframework.jdbc.UncategorizedSQLException : executing StatementCallback: encountered SQLException [[Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Must declare the variable '@P1'.]; nested exception is java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Must declare the variable '@P1'.
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Must declare the variable '@P1'.
at com.microsoft.jdbc.base.BaseExceptions.createExcep tion(Unknown Source)
at com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sErrorToken(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sReplyToken(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRPCRequest.pro cessReplyToken(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sReply(Unknown Source)
at com.microsoft.jdbc.sqlserver.SQLServerImplStatemen t.getNextResultType(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.commonTransi tionToState(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.postImplExec ute(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.commonExecut e(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.executeInter nal(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.execute(Unkn own Source)
at org.apache.commons.dbcp.DelegatingStatement.execut e(DelegatingStatement.java:261)
at org.springframework.jdbc.core.JdbcTemplate$1Execut eStatementCallback.doInStatement(JdbcTemplate.java :276)
at org.springframework.jdbc.core.JdbcTemplate.execute (JdbcTemplate.java:256)
at org.springframework.jdbc.core.JdbcTemplate.execute (JdbcTemplate.java:283)
at com.sca.dao.stub.ProductDAO.getProduct(ProductDAO. java:43)
at com.sca.planner.productcosting.ProductCosting.calc ulateProductCost(ProductCosting.java:67)
at com.sca.testcases.planner.ProductCosting.TestProdu ctCosting.testCosting(TestProductCosting.java:144)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Native MethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(De legatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at junit.framework.TestCase.runTest(TestCase.java:154 )
at junit.framework.TestCase.runBare(TestCase.java:127 )
at junit.framework.TestResult$1.protect(TestResult.ja va:106)
at junit.framework.TestResult.runProtected(TestResult .java:124)
at junit.framework.TestResult.run(TestResult.java:109 )
at junit.framework.TestCase.run(TestCase.java:118)
at junit.framework.TestSuite.runTest(TestSuite.java:2 08)
at junit.framework.TestSuite.run(TestSuite.java:203)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRu nner.runTests(RemoteTestRunner.java:478)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRu nner.run(RemoteTestRunner.java:344)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRu nner.main(RemoteTestRunner.java:196)

[dsklyut]

Spring does this for Objects and array is also an Object then why not for array.

Can you please clarify the "does this for Objects" part.
If you mean the signature of the queryForRowSet API (String, Object[]), I will say that you are confusing a few things.

Each element in an array of Objects passed into the query API corresponds to a single bind parameter in your String sql argument.
So if you have "select * from table a where a.col1 = ? and a.col2 = ? and a.col3 in (?,?,?)" you will need to pass an object array of length 5 to satisfy each of the bind parameters in your query.

Your sql on the other hand only has one bind parameter so Spring will try to bind an array of ints as an object. If you try to do the same thing with JDBC API without Spring you will fail also.

Spring does not take away responsibility from the developer for writting correct sql. Spring provides a very nice abstraction level where the developer does not need to worry or care about low level JDBC API.

[Mukesh Antil]

Hi dsklyut,
Just go through the Code its in Toplink

class Product extends ToplinkDAOSupport{
privae final ReadAllQuery getProduct;
public void ProductDAO(){
getProduct=new ReadAllQuery();
buildgetProductByIds();
}
public Collection getProducts(int ids[]){
Object arr[]=new Object[1];
arr[0]=ids;
return (Collection)getToplinkTemplate.executeQuery(getPro duct,arr);

}
private buildgetProductByIds(){
ExpressionBuilder expBld=new ExpressionBuilder();
Expression exp;
exp=expBld.getField("id").in(expBld.getParameter(" Ids");
getProduct.setReferenceClass(Product.java);
getProduct.addAttribute("Ids");
getProduct.setShouldBindAllParameter(true);
getProduct.setShouldCacheStatement(true);



}
}


and all works well

I am Converting the Same Class to use JDBCTemplate and is expecting the same way of parameterBinding.

[dsklyut]

Well, Toplink is an ORM tool. It is a level above JDBC. It does a lot of mapping behind the scenes, with JDBC you have to do mapping by hand.