Announcement Announcement Module
Collapse
No announcement yet.
Batch updating a array of actors Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Batch updating a array of actors

    I have created the following method, to update an array of actors using a prepared statement. i could not make it work, i sort of know the problem but don't know how to fix it, any help.

    Here is some of the code i have so far.

    public void JdbcBatchUpdate(List<Actor> actors) {
    String sql = "select * from Actor";
    Connection conn = null;

    try {
    conn = dataSource.getConnection();
    PreparedStatement ps = conn.prepareStatement(sql);
    ps.addBatch("update actor set first_name= ? where actor_id='?'");
    //ps.setInt(1, actors.);
    ps.executeBatch();
    int[] updCnt = ps.executeBatch();
    ps.close();
    conn.rollback();
    //conn.commit();
    } catch (BatchUpdateException be) {
    //handle batch update exception
    int[] counts = be.getUpdateCounts();
    for (int i = 0; i < counts.length; i++) {
    System.out.println("Statement[" + i + "] :" + counts[i]);

    }
    } catch (SQLException e) {
    }
    }

    Thank you

  • #2
    Please use [ code][/ code] tags when posting code, that way it remains readable.

    1) You are executing twice (executeBatch is called twice)
    2) Don't escape ? in your where clause.
    3) Why are you rollingback?
    4) Use JdbcTemplate which is easier to execute/manage batch updates.
    5) Select and update don't really mix I suggest a read on batch updates.

    Comment


    • #3
      I see what you mean about the executeBatch i did twice, i though that when i have multiple statements i execute each and finally do a comple execute Batch? I am escaping in the where clause beacuse i wanted to create a test method where i will input the particular actor i want by the actor_id.

      I think i am not doing a bacth update that is getting an array of actors, i am puzzled by what i need to do?

      Thank you for your time

      Comment


      • #4
        If you use ? to fill in the parameters NEVER escape that is handled by the JDBC driver...

        As I suggest use springs JdbcTemplate and it's batch support.

        Code:
        public void JdbcBatchUpdate(final List<Actor> actors) {
        	String sql = "update actor set first_name= ? where actor_id=?";
        	JdbcTemplate template = new JdbcTemplate(dataSource);
        	template.batchUpdate(sql, new BatchPreparedStatementSetter() {
        			public int getBatchSize() {
        					return actors.size();
        			}
        			
        			public void setValues(PreparedStatement ps, int i) throws SQLException {
        					Actor actor = actors.get(i);
        					ps.setString(1, actor.getFirstName());
        					ps.setInt(2, actor.getId());
        			}
        		}
        	}
        Of course it would be better to use JdbcDaoSupport and get the JdbcTemplate from there or inject the JdbcTemplate. Constructing a JdbcTemplate is a lengthy business and as it is threadsafe should only occur once.

        Comment


        • #5
          The aim was to compare a batch using Raw JDBC to one that is using a Template. its a comparision to Raw JDBC vs Spring JDBC. i have done normal crud operations but i wanted some bulky batch for good performance measures. I already have this batch but its a template, i wanted a raw jdbc batch update. Any help

          Comment

          Working...
          X