Announcement Announcement Module
Collapse
No announcement yet.
Problem with JdbcTemplate.batchUpdate() --> Always returning -2 Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Problem with JdbcTemplate.batchUpdate() --> Always returning -2

    Hi All,

    I am using Spring JDBCTemplate.batchUpdate() with PreparedStatement as shown below

    Code:
     String[][] myData = {{"param1", "value1"}, {"param2", "value2"}, 
             {"param3", "value3"}}; 
             
             String sql = "update myTable set param_value=? where id=? and param_name=?";
             
             JdbcTemplate jdbcTemplate = new JdbcTemplate(getDataSource());
             MyUpdateBatchSetter setter = new MyUpdateBatchSetter(myData);
             int[] rowCounts = jdbcTemplate.batchUpdate(sql, setter);
    Following is my Batch Setter class

    Code:
    public class MyUpdateBatchSetter implements BatchPreparedStatementSetter
    {
    	private String[][] data; 
    	public MyUpdateBatchSetter(String[][] data)
    	{
    		this.data = data; 
    	}
    
    //	 this is called for each row 
    	public void setValues( PreparedStatement ps, int i )
    	{ 
    		System.out.println("data = " + data[i][1] + "\n" + data[i][0]);
    		try {
    			ps.setString( 1, data[i][1] ); // set first value 
    			ps.setInt( 2, 1000 ); // set second value
    			ps.setString( 3, data[i][0] ); // set third value 
    		} catch (SQLException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		}
    	} 
    	
    	public int getBatchSize() { 
    		return data.length; 
    		} 
    }

    This code is updating the database but the problem is that the number of affcted rows returned (rowCounts) is -2 for all the statements.

    I need correct rowCounts for inserting the data if that was not updated as shown below
    Code:
    for (int i = 0; i < rowCounts.length; i++)
             {        	 
            	 if (rowCounts[i] <= 0)
                {            
                	// row wasn't updated we should insert it   
                 // Code for inserting goes here....           
    
                 }
             }

    It would be great if anybody can help.

  • #2
    You can't rely on getting an accurate count in this scenario. Some JDBC drivers return -2 which is a valid return value -- it just means that the driver doesn't know the exact count. I don't think there is much you could do other than switch to another database/jdbc driver that does provide accurate counts.

    Comment


    • #3
      Hi Thomas,
      Thank you so much for the information.
      If the problem is with JDBC driver, I wonder why it works fine and returns correct no. of rows when code does not use PreparedStatement and setter class but sql string as shown below

      Code:
      String[][] myData = {{"InterfaceName", "cs2kCfgMgrIf2"}, {"Version", "4"}, 
               {"Usn", "2"}}; 
      for (int i = 0; i < myData.length; i++)
               {            
                  sqlString[i] =
                     new String(
                        "Update DEVICE_TYPE_CONFIG " + " set param_value= '" +
                        myData[i][1] + "'" + " where " +
                        " DEVICE_TYPE_ID = " + 1000 + " AND PARAM_NAME = '" +
                        myData[i][0] + "'");
               }
      
      JdbcTemplate jdbcTemplate = new JdbcTemplate(getDataSource());
      int[] rowCounts = jdbcTemplate.batchUpdate(sqlString);
      for (int i = 0; i < rowCounts.length; i++)
               {
              	 System.out.println("rowCounts[" + i + "] = " + rowCounts[i]);
               }

      The output is

      Code:
      rowCounts[0] = 1
      rowCounts[1] = 1
      rowCounts[2] = 1
      NOTE: I need to use PreparedStatement (since sql string fails when the input data contains " or ' characters).
      I am using Oracle JDBC Driver version - 9.0.2.0.0

      Comment


      • #4
        Originally posted by bijithkumar
        NOTE: I need to use PreparedStatement (since sql string fails when the input data contains " or ' characters).
        I am using Oracle JDBC Driver version - 9.0.2.0.0
        Have a look here. Maybe this could be helpful in solving problems with escaping quotation characters. Although I agree that a solution using prepared statements would be preferrable. Maybe you could try out another jdbc driver version.

        Regards,
        Andreas

        Comment


        • #5
          Thank you Senft,
          But this does not solve my problem .
          I have tried changing oracle driver version to latest (10.1.0.2.0) but that too didn't work.
          and using {escape } syntax also didn't work.

          If anybody can think of any other logic, My requirement is as given below

          I need to update some raws in DB (or insert if raw does not exist).
          and I need to use
          1) Spring JDBCTemplate

          2) with PreparedStatement and BatchPreparedStatementSetter. I already have this setter class implemented in my project).

          3) and Oracle DB

          Comment


          • #6
            As of the PreparedStatement issue I'm at a loss. However, concerning escaping you might try to escape the apostrophe by duplication. That should work for Oracle.
            E.g.: UPDATE test set val='xxx''yyy';

            Regards,
            Andreas

            Comment


            • #7
              You should be able to avoid the -2 by not using a batch update. The preformance will suffer, but you should get your counts.

              Comment


              • #8
                Thank you Senft,

                For time being I have fixed this issue with duplication.

                This is what I did.

                Code:
                for (int i = 0; i < myData.length; i++)
                         {            
                            sqlString[i] =
                               new String(
                                  "Update DEVICE_TYPE_CONFIG " + " set param_value= '" +
                                  myData[i][1].replaceAll("'","''") + "'" + " where " +
                                  " DEVICE_TYPE_ID = " + 1000 + " AND PARAM_NAME = '" +
                                  myData[i][0] + "'");
                         }
                The response I got from this forum was amazing.
                Thanks to everyone for the support.
                Will be back soon with new issues

                Comment

                Working...
                X