Updating a row in sql using jdbc

Rated 3.95/5 based on 980 customer reviews

The difference is much more drastic if each call to the SQL engine has to be done over the network from another process.

Again, the benchmark code is available from a gist, and I will paste it to the end of this blog post as well.

ID: 100, Age: 38, First: Zara, Last: Ali ID: 102, Age: 55, First: Zaid, Last: Khan ID: 103, Age: 50, First: Sumit, Last: Mittal ID: 104, Age: 40, First: John, Last: Paul Goodbye!

Typically in a database program like this you'll end up updating rows based on the primary key of the database table.

Something that has been said many times, but needs constant repeating until every developer is aware of its importance, is the performance difference between row-by-row updating and bulk updating. Let me ask this differently: The best way to find out is to benchmark.

If you cannot guess which one will be much faster, remember that row-by-row kinda rhymes with "slow-by-slow" (hint, hint). I'm doing two benchmarks for this: Run 1, Statement 1 : .01457 (avg : .0098) Run 1, Statement 2 : .0133 (avg : .01291) Run 1, Statement 3 : .02351 (avg : .02519) Run 2, Statement 1 : .00882 (avg : .0098) Run 2, Statement 2 : .01159 (avg : .01291) Run 2, Statement 3 : .02348 (avg : .02519) Run 3, Statement 1 : .01012 (avg : .0098) Run 3, Statement 2 : .01453 (avg : .01291) Run 3, Statement 3 : .02544 (avg : .02519) Run 4, Statement 1 : .00799 (avg : .0098) Run 4, Statement 2 : .01346 (avg : .01291) Run 4, Statement 3 : .02958 (avg : .02519) Run 5, Statement 1 : .00749 (avg : .0098) Run 5, Statement 2 : .01166 (avg : .01291) Run 5, Statement 3 : .02396 (avg : .02519) The difference between Statement 1 and 3 is a factor of 2.5x.

The first thing we need for our Java UPDATE example is a sample My SQL database table.

To keep it simple — but also show several different data types — I've created the following My SQL database table: create table users ( id int unsigned auto_increment not null, first_name varchar(32) not null, last_name varchar(32) not null, date_created timestamp default now(), is_admin boolean, num_points int, primary key (id) ); -- insert some sample records insert into users (first_name, last_name) values ('Fred', 'Flinstone'); insert into users (first_name, last_name) values ('Barney', 'Rubble'); A few of these fields are a little contrived, but I wanted to show several different data types in one table, so this is what I came up with.

Leave a Reply