Batch updating a array of actors

[Humble24]

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

[Marten Deinum]

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.

[Humble24]

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

[Marten Deinum]

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.

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.

[Humble24]

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