Announcement Announcement Module
No announcement yet.
JdbcTemplate to use in query<<Help Required>> Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

  • JdbcTemplate to use in query<<Help Required>>

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

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


    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

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


    • #3
      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 ++)
         if (i < arr.length) sqlPrams.append(",");
         arr[i] = new Integer(productIds[i]);
      query = query + sqlParams.toString() + ")";
      execute(query, arr);


      • #4
        Conceptually the jdbc should work.

        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.




        • #5
          Hi Loren Rosen

          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];
          String query="select * from product where id in(?)";
          SqlRowSet rows=this.queryForRowSet(query,arr);

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


          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 tion(Unknown Source)
          at n(Unknown Source)
          at sErrorToken(Unknown Source)
          at sReplyToken(Unknown Source)
          at cessReplyToken(Unknown Source)
          at sReply(Unknown Source)
          at t.getNextResultType(Unknown Source)
          at tionToState(Unknown Source)
          at ute(Unknown Source)
          at e(Unknown Source)
          at nal(Unknown Source)
          at own Source)
          at org.apache.commons.dbcp.DelegatingStatement.execut e(
          at org.springframework.jdbc.core.JdbcTemplate$1Execut eStatementCallback.doInStatement( :276)
          at org.springframework.jdbc.core.JdbcTemplate.execute (
          at org.springframework.jdbc.core.JdbcTemplate.execute (
          at java:43)
          at ulateProductCost(
          at ctCosting.testCosting(
          at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
          at sun.reflect.NativeMethodAccessorImpl.invoke(Native
          at sun.reflect.DelegatingMethodAccessorImpl.invoke(De
          at java.lang.reflect.Method.invoke(
          at junit.framework.TestCase.runTest( )
          at junit.framework.TestCase.runBare( )
          at junit.framework.TestResult$1.protect(TestResult.ja va:106)
          at junit.framework.TestResult.runProtected(TestResult .java:124)
          at )
          at junit.framework.TestSuite.runTest( 08)
          at org.eclipse.jdt.internal.junit.runner.RemoteTestRu nner.runTests(
          at org.eclipse.jdt.internal.junit.runner.RemoteTestRu
          at org.eclipse.jdt.internal.junit.runner.RemoteTestRu nner.main(


          • #6
            Originally posted by Mukesh Antil
            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.


            • #7
              Hi dsklyut,
              Just go through the Code its in Toplink

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

              private buildgetProductByIds(){
              ExpressionBuilder expBld=new ExpressionBuilder();
              Expression exp;
              exp=expBld.getField("id").in(expBld.getParameter(" Ids");


              and all works well

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


              • #8
                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.