Java > Open Source Codes > org > h2 > test > db > TestBatchUpdates


1 /*
2  * Copyright 2004-2006 H2 Group. Licensed under the H2 License, Version 1.0 (http://h2database.com/html/license.html).
3  * Initial Developer: H2 Group
4  */
5 package org.h2.test.db;
6
7 import java.sql.*;
8
9 import org.h2.test.TestBase;
10
11 public class TestBatchUpdates extends TestBase {
12
13     static final String COFFEE_UPDATE = "UPDATE TEST SET PRICE=PRICE*20 WHERE TYPE_ID=?";
14     static final String COFFEE_SELECT = "SELECT PRICE FROM TEST WHERE KEY_ID=?";
15     static final String COFFEE_QUERY = "SELECT COF_NAME,PRICE FROM TEST WHERE TYPE_ID=?";
16     static final String COFFEE_DELETE = "DELETE FROM TEST WHERE KEY_ID=?";
17     static final String COFFEE_INSERT1 = "INSERT INTO TEST VALUES(9,'COFFEE-9',9.0,5)";
18     static final String COFFEE_DELETE1 = "DELETE FROM TEST WHERE KEY_ID=9";
19     static final String COFFEE_UPDATE1 = "UPDATE TEST SET PRICE=PRICE*20 WHERE TYPE_ID=1";
20     static final String COFFEE_SELECT1 = "SELECT PRICE FROM TEST WHERE KEY_ID>4";
21     static final String COFFEE_UPDATE_SET = "UPDATE TEST SET KEY_ID=?, COF_NAME=? WHERE COF_NAME=?";
22     static final String COFFEE_SELECT_CONTINUED = "SELECT COUNT(*) FROM TEST WHERE COF_NAME='Continue-1'";
23
24     int coffeeSize = 10;
25     int coffeeType = 11;
26     Connection conn;
27     Statement stat;
28     PreparedStatement prep;
29
30     public void test() throws Exception {
31         deleteDb("batchUpdates");
32         this.conn = getConnection("batchUpdates");
33         stat = conn.createStatement();
34         DatabaseMetaData meta = conn.getMetaData();
35         if (!meta.supportsBatchUpdates()) {
36             error("does not support BatchUpdates");
37         }
38         stat.executeUpdate("CREATE TABLE TEST(KEY_ID INT PRIMARY KEY,"
39                 + "COF_NAME VARCHAR(255),PRICE DECIMAL(20,2),TYPE_ID INT)");
40         String newName = null;
41         float newPrice = 0;
42         int newType = 0;
43         prep = conn.prepareStatement("INSERT INTO TEST VALUES(?,?,?,?)");
44         int newKey = 1;
45         for (int i = 1; i <= coffeeType && newKey <= coffeeSize; i++) {
46             for (int j = 1; j <= i && newKey <= coffeeSize; j++) {
47                 newName = "COFFEE-" + newKey;
48                 newPrice = newKey + (float) .00;
49                 newType = i;
50                 prep.setInt(1, newKey);
51                 prep.setString(2, newName);
52                 prep.setFloat(3, newPrice);
53                 prep.setInt(4, newType);
54                 prep.execute();
55                 newKey = newKey + 1;
56             }
57         }
58         trace("Inserted the Rows ");
59         testAddBatch01();
60         testAddBatch02();
61         testClearBatch01();
62         testClearBatch02();
63         testExecuteBatch01();
64         testExecuteBatch02();
65         testExecuteBatch03();
66         testExecuteBatch04();
67         testExecuteBatch05();
68         testExecuteBatch06();
69         testExecuteBatch07();
70         testContinueBatch01();
71         
72         conn.close();
73     }
74
75     public void testAddBatch01() throws Exception {
76         trace("testAddBatch01");
77         int i = 0;
78         int[] retValue = { 0, 0, 0};
79         String s = COFFEE_UPDATE;
80         trace("Prepared Statement String:" + s);
81         prep = conn.prepareStatement(s);
82         prep.setInt(1, 2);
83         prep.addBatch();
84         prep.setInt(1, 3);
85         prep.addBatch();
86         prep.setInt(1, 4);
87         prep.addBatch();
88         int[] updateCount = prep.executeBatch();
89         int updateCountlen = updateCount.length;
90         
91 // PreparedStatement p;
92 // p = conn.prepareStatement(COFFEE_UPDATE);
93 // p.setInt(1,2);
94 // System.out.println("upc="+p.executeUpdate());
95 // p.setInt(1,3);
96 // System.out.println("upc="+p.executeUpdate());
97 // p.setInt(1,4);
98 // System.out.println("upc="+p.executeUpdate());
99
100         
101         trace("updateCount length:" + updateCountlen);
102         if (updateCountlen != 3) {
103             error("addBatch");
104         } else {
105             trace("addBatch add the SQL statements to Batch ");
106         }
107         String query1 = "SELECT COUNT(*) FROM TEST WHERE TYPE_ID=2";
108         String query2 = "SELECT COUNT(*) FROM TEST WHERE TYPE_ID=3";
109         String query3 = "SELECT COUNT(*) FROM TEST WHERE TYPE_ID=4";
110         ResultSet rs = stat.executeQuery(query1);
111         rs.next();
112         retValue[i++] = rs.getInt(1);
113         rs = stat.executeQuery(query2);
114         rs.next();
115         retValue[i++] = rs.getInt(1);
116         rs = stat.executeQuery(query3);
117         rs.next();
118         retValue[i++] = rs.getInt(1);
119         for (int j = 0; j < updateCount.length; j++) {
120             trace("UpdateCount:" + updateCount[j]);
121             check(updateCount[j], retValue[j]);
122         }
123     }
124
125     public void testAddBatch02() throws Exception {
126         trace("testAddBatch02");
127         int i = 0;
128         int[] retValue = { 0, 0, 0};
129         int updCountLength = 0;
130         String sUpdCoffee = COFFEE_UPDATE1;
131         String sDelCoffee = COFFEE_DELETE1;
132         String sInsCoffee = COFFEE_INSERT1;
133         stat.addBatch(sUpdCoffee);
134         stat.addBatch(sDelCoffee);
135         stat.addBatch(sInsCoffee);
136         int[] updateCount = stat.executeBatch();
137         updCountLength = updateCount.length;
138         trace("updateCount Length:" + updCountLength);
139         if (updCountLength != 3) {
140             error("addBatch");
141         } else {
142             trace("addBatch add the SQL statements to Batch ");
143         }
144         String query1 = "SELECT COUNT(*) FROM TEST WHERE TYPE_ID=1";
145         ResultSet rs = stat.executeQuery(query1);
146         rs.next();
147         retValue[i++] = rs.getInt(1);
148         // 1 as delete Statement will delete only one row
149 retValue[i++] = 1;
150         // 1 as insert Statement will insert only one row
151 retValue[i++] = 1;
152         trace("ReturnValue count : " + retValue.length);
153         for (int j = 0; j < updateCount.length; j++) {
154             trace("Update Count:" + updateCount[j]);
155             trace("Returned Value : " + retValue[j]);
156             if (updateCount[j] != retValue[j]) {
157                 error("j=" + j + " right:" + retValue[j]);
158             }
159         }
160     }
161
162     public void testClearBatch01() throws Exception {
163         trace("testClearBatch01");
164         String sPrepStmt = COFFEE_UPDATE;
165         trace("Prepared Statement String:" + sPrepStmt);
166         prep = conn.prepareStatement(sPrepStmt);
167         prep.setInt(1, 2);
168         prep.addBatch();
169         prep.setInt(1, 3);
170         prep.addBatch();
171         prep.setInt(1, 4);
172         prep.addBatch();
173         prep.clearBatch();
174         int[] updateCount = prep.executeBatch();
175         int updCountLength = updateCount.length;
176         if (updCountLength == 0) {
177             trace("clearBatch Method clears the current Batch ");
178         } else {
179             error("clearBatch");
180         }
181     }
182
183     public void testClearBatch02() throws Exception {
184         trace("testClearBatch02");
185         int updCountLength = 0;
186         String sUpdCoffee = COFFEE_UPDATE1;
187         String sInsCoffee = COFFEE_INSERT1;
188         String sDelCoffee = COFFEE_DELETE1;
189         stat.addBatch(sUpdCoffee);
190         stat.addBatch(sDelCoffee);
191         stat.addBatch(sInsCoffee);
192         stat.clearBatch();
193         int[] updateCount = stat.executeBatch();
194         updCountLength = updateCount.length;
195         trace("updateCount Length:" + updCountLength);
196         if (updCountLength == 0) {
197             trace("clearBatch Method clears the current Batch ");
198         } else {
199             error("clearBatch");
200         }
201     }
202
203     public void testExecuteBatch01() throws Exception {
204         trace("testExecuteBatch01");
205         int i = 0;
206         int[] retValue = { 0, 0, 0};
207         int updCountLength = 0;
208         String sPrepStmt = COFFEE_UPDATE;
209         trace("Prepared Statement String:" + sPrepStmt);
210         // get the PreparedStatement object
211 prep = conn.prepareStatement(sPrepStmt);
212         prep.setInt(1, 1);
213         prep.addBatch();
214         prep.setInt(1, 2);
215         prep.addBatch();
216         prep.setInt(1, 3);
217         prep.addBatch();
218         int[] updateCount = prep.executeBatch();
219         updCountLength = updateCount.length;
220         trace("Successfully Updated");
221         trace("updateCount Length:" + updCountLength);
222         if (updCountLength != 3) {
223             error("executeBatch");
224         } else {
225             trace("executeBatch executes the Batch of SQL statements");
226         }
227         //1 is the number that is set First for Type Id in Prepared Statement
228 String query1 = "SELECT COUNT(*) FROM TEST WHERE TYPE_ID=1";
229         //2 is the number that is set second for Type id in Prepared Statement
230 String query2 = "SELECT COUNT(*) FROM TEST WHERE TYPE_ID=2";
231         // 3 is the number that is set Third for Type id in Prepared Statement
232 String query3 = "SELECT COUNT(*) FROM TEST WHERE TYPE_ID=3";
233         ResultSet rs = stat.executeQuery(query1);
234         rs.next();
235         retValue[i++] = rs.getInt(1);
236         rs = stat.executeQuery(query2);
237         rs.next();
238         retValue[i++] = rs.getInt(1);
239         rs = stat.executeQuery(query3);
240         rs.next();
241         retValue[i++] = rs.getInt(1);
242         trace("retvalue length : " + retValue.length);
243         for (int j = 0; j < updateCount.length; j++) {
244             trace("UpdateCount Value:" + updateCount[j]);
245             trace("Retvalue : " + retValue[j]);
246             if (updateCount[j] != retValue[j]) {
247                 error("j=" + j + " right:" + retValue[j]);
248             }
249         }
250     }
251
252     public void testExecuteBatch02() throws Exception {
253         trace("testExecuteBatch02");
254         String sPrepStmt = COFFEE_UPDATE;
255         trace("Prepared Statement String:" + sPrepStmt);
256         prep = conn.prepareStatement(sPrepStmt);
257         prep.setInt(1, 1);
258         prep.setInt(1, 2);
259         prep.setInt(1, 3);
260         int[] updateCount = prep.executeBatch();
261         int updCountLength = updateCount.length;
262         trace("UpdateCount Length : " + updCountLength);
263         if (updCountLength == 0) {
264             trace("executeBatch does not execute Empty Batch");
265         } else {
266             error("executeBatch");
267         }
268     }
269
270     public void testExecuteBatch03() throws Exception {
271         trace("testExecuteBatch03");
272         boolean bexpflag = false;
273         String sPrepStmt = COFFEE_SELECT;
274         trace("Prepared Statement String :" + sPrepStmt);
275         prep = conn.prepareStatement(sPrepStmt);
276         prep.setInt(1, 1);
277         prep.addBatch();
278         try {
279             int[] updateCount = prep.executeBatch();
280             trace("Update Count" + updateCount.length);
281         } catch (BatchUpdateException b) {
282             bexpflag = true;
283         }
284         if (bexpflag) {
285             trace("select not allowed; correct");
286         } else {
287             error("executeBatch select");
288         }
289     }
290
291     public void testExecuteBatch04() throws Exception {
292         trace("testExecuteBatch04");
293         int i = 0;
294         int[] retValue = { 0, 0, 0};
295         int updCountLength = 0;
296         String sUpdCoffee = COFFEE_UPDATE1;
297         String sInsCoffee = COFFEE_INSERT1;
298         String sDelCoffee = COFFEE_DELETE1;
299         stat.addBatch(sUpdCoffee);
300         stat.addBatch(sDelCoffee);
301         stat.addBatch(sInsCoffee);
302         int[] updateCount = stat.executeBatch();
303         updCountLength = updateCount.length;
304         trace("Successfully Updated");
305         trace("updateCount Length:" + updCountLength);
306         if (updCountLength != 3) {
307             error("executeBatch");
308         } else {
309             trace("executeBatch executes the Batch of SQL statements");
310         }
311         String query1 = "SELECT COUNT(*) FROM TEST WHERE TYPE_ID=1";
312         ResultSet rs = stat.executeQuery(query1);
313         rs.next();
314         retValue[i++] = rs.getInt(1);
315         // 1 as Delete Statement will delete only one row
316 retValue[i++] = 1;
317         // 1 as Insert Statement will insert only one row
318 retValue[i++] = 1;
319         for (int j = 0; j < updateCount.length; j++) {
320             trace("Update Count : " + updateCount[j]);
321             if (updateCount[j] != retValue[j]) {
322                 error("j=" + j + " right:" + retValue[j]);
323             }
324         }
325     }
326
327     public void testExecuteBatch05() throws Exception {
328         trace("testExecuteBatch05");
329         int updCountLength = 0;
330         int[] updateCount = stat.executeBatch();
331         updCountLength = updateCount.length;
332         trace("updateCount Length:" + updCountLength);
333         if (updCountLength == 0) {
334             trace("executeBatch Method does not execute the Empty Batch ");
335         } else {
336             error("executeBatch 0!=" + updCountLength);
337         }
338     }
339
340     public void testExecuteBatch06() throws Exception {
341         trace("testExecuteBatch06");
342         boolean bexpflag = false;
343         //Insert a row which is already Present
344 String sInsCoffee = COFFEE_INSERT1;
345         String sDelCoffee = COFFEE_DELETE1;
346         stat.addBatch(sInsCoffee);
347         stat.addBatch(sInsCoffee);
348         stat.addBatch(sDelCoffee);
349         try {
350             stat.executeBatch();
351         } catch (BatchUpdateException b) {
352             bexpflag = true;
353             int[] updCounts = b.getUpdateCounts();
354             for (int i = 0; i < updCounts.length; i++) {
355                 trace("Update counts:" + updCounts[i]);
356             }
357         }
358         if (bexpflag) {
359             trace("executeBatch insert duplicate; correct");
360         } else {
361             error("executeBatch");
362         }
363     }
364
365     public void testExecuteBatch07() throws Exception {
366         trace("testExecuteBatch07");
367         boolean bexpflag = false;
368         String sSelCoffee = COFFEE_SELECT1;
369         trace("sSelCoffee = " + sSelCoffee);
370         Statement stmt = conn.createStatement();
371         stmt.addBatch(sSelCoffee);
372         try {
373             int[] updateCount = stmt.executeBatch();
374             trace("updateCount Length : " + updateCount.length);
375         } catch (BatchUpdateException be) {
376             bexpflag = true;
377         }
378         if (bexpflag) {
379             trace("executeBatch select");
380         } else {
381             error("executeBatch");
382         }
383     }
384
385     public void testContinueBatch01() throws Exception {
386         trace("testContinueBatch01");
387         int[] batchUpdates = { 0, 0, 0};
388         int buCountlen = 0;
389         try {
390             String sPrepStmt = COFFEE_UPDATE_SET;
391             trace("Prepared Statement String:" + sPrepStmt);
392             prep = conn.prepareStatement(sPrepStmt);
393             // Now add a legal update to the batch
394 prep.setInt(1, 1);
395             prep.setString(2, "Continue-1");
396             prep.setString(3, "COFFEE-1");
397             prep.addBatch();
398             // Now add an illegal update to the batch by
399 // forcing a unique constraint violation
400 // Try changing the key_id of row 3 to 1.
401 prep.setInt(1, 1);
402             prep.setString(2, "Invalid");
403             prep.setString(3, "COFFEE-3");
404             prep.addBatch();
405             // Now add a second legal update to the batch
406 // which will be processed ONLY if the driver supports
407 // continued batch processing according to 6.2.2.3
408 // of the J2EE platform spec.
409 prep.setInt(1, 2);
410             prep.setString(2, "Continue-2");
411             prep.setString(3, "COFFEE-2");
412             prep.addBatch();
413             // The executeBatch() method will result in a
414 // BatchUpdateException
415 prep.executeBatch();
416         } catch (BatchUpdateException b) {
417             trace("expected BatchUpdateException");
418             batchUpdates = b.getUpdateCounts();
419             buCountlen = batchUpdates.length;
420         }
421         if (buCountlen == 1) {
422             trace("no continued updates - OK");
423             return;
424         } else if (buCountlen == 3) {
425             trace("Driver supports continued updates.");
426             // Check to see if the third row from the batch was added
427 String query = COFFEE_SELECT_CONTINUED;
428             trace("Query is: " + query);
429             ResultSet rs = stat.executeQuery(query);
430             rs.next();
431             int count = rs.getInt(1);
432             rs.close();
433             stat.close();
434             trace("Count val is: " + count);
435             // Make sure that we have the correct error code for
436 // the failed update.
437 if (!(batchUpdates[1] == -3 && count == 1)) {
438                 error("insert failed");
439             }
440         }
441     }
442 }
443

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags