001: /*
002: * Copyright 2004-2008 H2 Group. Licensed under the H2 License, Version 1.0
003: * (license2)
004: * Initial Developer: H2 Group
005: */
006: package org.h2.test.jdbc;
007:
008: import java.io.ByteArrayOutputStream;
009: import java.io.PrintStream;
010: import java.sql.BatchUpdateException;
011: import java.sql.CallableStatement;
012: import java.sql.Connection;
013: import java.sql.DatabaseMetaData;
014: import java.sql.PreparedStatement;
015: import java.sql.ResultSet;
016: import java.sql.Statement;
017:
018: import org.h2.test.TestBase;
019:
020: /**
021: * Test for batch updates.
022: */
023: public class TestBatchUpdates extends TestBase {
024:
025: static final String COFFEE_UPDATE = "UPDATE TEST SET PRICE=PRICE*20 WHERE TYPE_ID=?";
026: static final String COFFEE_SELECT = "SELECT PRICE FROM TEST WHERE KEY_ID=?";
027: static final String COFFEE_QUERY = "SELECT C_NAME,PRICE FROM TEST WHERE TYPE_ID=?";
028: static final String COFFEE_DELETE = "DELETE FROM TEST WHERE KEY_ID=?";
029: static final String COFFEE_INSERT1 = "INSERT INTO TEST VALUES(9,'COFFEE-9',9.0,5)";
030: static final String COFFEE_DELETE1 = "DELETE FROM TEST WHERE KEY_ID=9";
031: static final String COFFEE_UPDATE1 = "UPDATE TEST SET PRICE=PRICE*20 WHERE TYPE_ID=1";
032: static final String COFFEE_SELECT1 = "SELECT PRICE FROM TEST WHERE KEY_ID>4";
033: static final String COFFEE_UPDATE_SET = "UPDATE TEST SET KEY_ID=?, C_NAME=? WHERE C_NAME=?";
034: static final String COFFEE_SELECT_CONTINUED = "SELECT COUNT(*) FROM TEST WHERE C_NAME='Continue-1'";
035:
036: int coffeeSize = 10;
037: int coffeeType = 11;
038: Connection conn;
039: Statement stat;
040: PreparedStatement prep;
041:
042: public void test() throws Exception {
043: testExecuteCall();
044: testException();
045: testCoffee();
046: }
047:
048: private void testExecuteCall() throws Exception {
049: deleteDb("batchUpdates");
050: Connection conn = getConnection("batchUpdates");
051: Statement stat = conn.createStatement();
052: stat.execute("CREATE ALIAS updatePrices FOR \""
053: + getClass().getName() + ".updatePrices\"");
054: CallableStatement call = conn
055: .prepareCall("{call updatePrices(?, ?)}");
056: call.setString(1, "Hello");
057: call.setFloat(2, 1.4f);
058: call.addBatch();
059: call.setString(1, "World");
060: call.setFloat(2, 3.2f);
061: call.addBatch();
062: int[] updateCounts = call.executeBatch();
063: int total = 0;
064: for (int i = 0; i < updateCounts.length; i++) {
065: total += updateCounts[i];
066: }
067: check(4, total);
068: conn.close();
069: }
070:
071: public static int updatePrices(String s, double f) {
072: return (int) f;
073: }
074:
075: private void testException() throws Exception {
076: deleteDb("batchUpdates");
077: Connection conn = getConnection("batchUpdates");
078: Statement stat = conn.createStatement();
079: stat.execute("create table test(id int primary key)");
080: PreparedStatement prep = conn
081: .prepareStatement("insert into test values(?)");
082: for (int i = 0; i < 700; i++) {
083: prep.setString(1, "x");
084: prep.addBatch();
085: }
086: try {
087: prep.executeBatch();
088: } catch (BatchUpdateException e) {
089: PrintStream temp = System.err;
090: try {
091: ByteArrayOutputStream buff = new ByteArrayOutputStream();
092: PrintStream p = new PrintStream(buff);
093: System.setErr(p);
094: e.printStackTrace();
095: } finally {
096: System.setErr(temp);
097: }
098: }
099: conn.close();
100: }
101:
102: private void testCoffee() throws Exception {
103: deleteDb("batchUpdates");
104: this .conn = getConnection("batchUpdates");
105: stat = conn.createStatement();
106: DatabaseMetaData meta = conn.getMetaData();
107: if (!meta.supportsBatchUpdates()) {
108: error("does not support BatchUpdates");
109: }
110: stat
111: .executeUpdate("CREATE TABLE TEST(KEY_ID INT PRIMARY KEY,"
112: + "C_NAME VARCHAR(255),PRICE DECIMAL(20,2),TYPE_ID INT)");
113: String newName = null;
114: float newPrice = 0;
115: int newType = 0;
116: prep = conn
117: .prepareStatement("INSERT INTO TEST VALUES(?,?,?,?)");
118: int newKey = 1;
119: for (int i = 1; i <= coffeeType && newKey <= coffeeSize; i++) {
120: for (int j = 1; j <= i && newKey <= coffeeSize; j++) {
121: newName = "COFFEE-" + newKey;
122: newPrice = newKey + (float) .00;
123: newType = i;
124: prep.setInt(1, newKey);
125: prep.setString(2, newName);
126: prep.setFloat(3, newPrice);
127: prep.setInt(4, newType);
128: prep.execute();
129: newKey = newKey + 1;
130: }
131: }
132: trace("Inserted the Rows ");
133: testAddBatch01();
134: testAddBatch02();
135: testClearBatch01();
136: testClearBatch02();
137: testExecuteBatch01();
138: testExecuteBatch02();
139: testExecuteBatch03();
140: testExecuteBatch04();
141: testExecuteBatch05();
142: testExecuteBatch06();
143: testExecuteBatch07();
144: testContinueBatch01();
145:
146: conn.close();
147: }
148:
149: public void testAddBatch01() throws Exception {
150: trace("testAddBatch01");
151: int i = 0;
152: int[] retValue = { 0, 0, 0 };
153: String s = COFFEE_UPDATE;
154: trace("Prepared Statement String:" + s);
155: prep = conn.prepareStatement(s);
156: prep.setInt(1, 2);
157: prep.addBatch();
158: prep.setInt(1, 3);
159: prep.addBatch();
160: prep.setInt(1, 4);
161: prep.addBatch();
162: int[] updateCount = prep.executeBatch();
163: int updateCountLen = updateCount.length;
164:
165: // PreparedStatement p;
166: // p = conn.prepareStatement(COFFEE_UPDATE);
167: // p.setInt(1,2);
168: // System.out.println("upc="+p.executeUpdate());
169: // p.setInt(1,3);
170: // System.out.println("upc="+p.executeUpdate());
171: // p.setInt(1,4);
172: // System.out.println("upc="+p.executeUpdate());
173:
174: trace("updateCount length:" + updateCountLen);
175: if (updateCountLen != 3) {
176: error("updateCount: " + updateCountLen);
177: } else {
178: trace("addBatch add the SQL statements to Batch ");
179: }
180: String query1 = "SELECT COUNT(*) FROM TEST WHERE TYPE_ID=2";
181: String query2 = "SELECT COUNT(*) FROM TEST WHERE TYPE_ID=3";
182: String query3 = "SELECT COUNT(*) FROM TEST WHERE TYPE_ID=4";
183: ResultSet rs = stat.executeQuery(query1);
184: rs.next();
185: retValue[i++] = rs.getInt(1);
186: rs = stat.executeQuery(query2);
187: rs.next();
188: retValue[i++] = rs.getInt(1);
189: rs = stat.executeQuery(query3);
190: rs.next();
191: retValue[i++] = rs.getInt(1);
192: for (int j = 0; j < updateCount.length; j++) {
193: trace("UpdateCount:" + updateCount[j]);
194: check(updateCount[j], retValue[j]);
195: }
196: }
197:
198: public void testAddBatch02() throws Exception {
199: trace("testAddBatch02");
200: int i = 0;
201: int[] retValue = { 0, 0, 0 };
202: int updCountLength = 0;
203: String sUpdCoffee = COFFEE_UPDATE1;
204: String sDelCoffee = COFFEE_DELETE1;
205: String sInsCoffee = COFFEE_INSERT1;
206: stat.addBatch(sUpdCoffee);
207: stat.addBatch(sDelCoffee);
208: stat.addBatch(sInsCoffee);
209: int[] updateCount = stat.executeBatch();
210: updCountLength = updateCount.length;
211: trace("updateCount Length:" + updCountLength);
212: if (updCountLength != 3) {
213: error("addBatch " + updCountLength);
214: } else {
215: trace("addBatch add the SQL statements to Batch ");
216: }
217: String query1 = "SELECT COUNT(*) FROM TEST WHERE TYPE_ID=1";
218: ResultSet rs = stat.executeQuery(query1);
219: rs.next();
220: retValue[i++] = rs.getInt(1);
221: // 1 as delete Statement will delete only one row
222: retValue[i++] = 1;
223: // 1 as insert Statement will insert only one row
224: retValue[i++] = 1;
225: trace("ReturnValue count : " + retValue.length);
226: for (int j = 0; j < updateCount.length; j++) {
227: trace("Update Count:" + updateCount[j]);
228: trace("Returned Value : " + retValue[j]);
229: if (updateCount[j] != retValue[j]) {
230: error("j=" + j + " right:" + retValue[j]);
231: }
232: }
233: }
234:
235: public void testClearBatch01() throws Exception {
236: trace("testClearBatch01");
237: String sPrepStmt = COFFEE_UPDATE;
238: trace("Prepared Statement String:" + sPrepStmt);
239: prep = conn.prepareStatement(sPrepStmt);
240: prep.setInt(1, 2);
241: prep.addBatch();
242: prep.setInt(1, 3);
243: prep.addBatch();
244: prep.setInt(1, 4);
245: prep.addBatch();
246: prep.clearBatch();
247: int[] updateCount = prep.executeBatch();
248: int updCountLength = updateCount.length;
249: if (updCountLength == 0) {
250: trace("clearBatch Method clears the current Batch ");
251: } else {
252: error("clearBatch " + updCountLength);
253: }
254: }
255:
256: public void testClearBatch02() throws Exception {
257: trace("testClearBatch02");
258: int updCountLength = 0;
259: String sUpdCoffee = COFFEE_UPDATE1;
260: String sInsCoffee = COFFEE_INSERT1;
261: String sDelCoffee = COFFEE_DELETE1;
262: stat.addBatch(sUpdCoffee);
263: stat.addBatch(sDelCoffee);
264: stat.addBatch(sInsCoffee);
265: stat.clearBatch();
266: int[] updateCount = stat.executeBatch();
267: updCountLength = updateCount.length;
268: trace("updateCount Length:" + updCountLength);
269: if (updCountLength == 0) {
270: trace("clearBatch Method clears the current Batch ");
271: } else {
272: error("clearBatch");
273: }
274: }
275:
276: public void testExecuteBatch01() throws Exception {
277: trace("testExecuteBatch01");
278: int i = 0;
279: int[] retValue = { 0, 0, 0 };
280: int updCountLength = 0;
281: String sPrepStmt = COFFEE_UPDATE;
282: trace("Prepared Statement String:" + sPrepStmt);
283: // get the PreparedStatement object
284: prep = conn.prepareStatement(sPrepStmt);
285: prep.setInt(1, 1);
286: prep.addBatch();
287: prep.setInt(1, 2);
288: prep.addBatch();
289: prep.setInt(1, 3);
290: prep.addBatch();
291: int[] updateCount = prep.executeBatch();
292: updCountLength = updateCount.length;
293: trace("Successfully Updated");
294: trace("updateCount Length:" + updCountLength);
295: if (updCountLength != 3) {
296: error("executeBatch");
297: } else {
298: trace("executeBatch executes the Batch of SQL statements");
299: }
300: // 1 is the number that is set First for Type Id in Prepared Statement
301: String query1 = "SELECT COUNT(*) FROM TEST WHERE TYPE_ID=1";
302: // 2 is the number that is set second for Type id in Prepared Statement
303: String query2 = "SELECT COUNT(*) FROM TEST WHERE TYPE_ID=2";
304: // 3 is the number that is set Third for Type id in Prepared Statement
305: String query3 = "SELECT COUNT(*) FROM TEST WHERE TYPE_ID=3";
306: ResultSet rs = stat.executeQuery(query1);
307: rs.next();
308: retValue[i++] = rs.getInt(1);
309: rs = stat.executeQuery(query2);
310: rs.next();
311: retValue[i++] = rs.getInt(1);
312: rs = stat.executeQuery(query3);
313: rs.next();
314: retValue[i++] = rs.getInt(1);
315: trace("retValue length : " + retValue.length);
316: for (int j = 0; j < updateCount.length; j++) {
317: trace("UpdateCount Value:" + updateCount[j]);
318: trace("RetValue : " + retValue[j]);
319: if (updateCount[j] != retValue[j]) {
320: error("j=" + j + " right:" + retValue[j]);
321: }
322: }
323: }
324:
325: public void testExecuteBatch02() throws Exception {
326: trace("testExecuteBatch02");
327: String sPrepStmt = COFFEE_UPDATE;
328: trace("Prepared Statement String:" + sPrepStmt);
329: prep = conn.prepareStatement(sPrepStmt);
330: prep.setInt(1, 1);
331: prep.setInt(1, 2);
332: prep.setInt(1, 3);
333: int[] updateCount = prep.executeBatch();
334: int updCountLength = updateCount.length;
335: trace("UpdateCount Length : " + updCountLength);
336: if (updCountLength == 0) {
337: trace("executeBatch does not execute Empty Batch");
338: } else {
339: error("executeBatch");
340: }
341: }
342:
343: public void testExecuteBatch03() throws Exception {
344: trace("testExecuteBatch03");
345: boolean batchExceptionFlag = false;
346: String sPrepStmt = COFFEE_SELECT;
347: trace("Prepared Statement String :" + sPrepStmt);
348: prep = conn.prepareStatement(sPrepStmt);
349: prep.setInt(1, 1);
350: prep.addBatch();
351: try {
352: int[] updateCount = prep.executeBatch();
353: trace("Update Count" + updateCount.length);
354: } catch (BatchUpdateException b) {
355: batchExceptionFlag = true;
356: }
357: if (batchExceptionFlag) {
358: trace("select not allowed; correct");
359: } else {
360: error("executeBatch select");
361: }
362: }
363:
364: public void testExecuteBatch04() throws Exception {
365: trace("testExecuteBatch04");
366: int i = 0;
367: int[] retValue = { 0, 0, 0 };
368: int updCountLength = 0;
369: String sUpdCoffee = COFFEE_UPDATE1;
370: String sInsCoffee = COFFEE_INSERT1;
371: String sDelCoffee = COFFEE_DELETE1;
372: stat.addBatch(sUpdCoffee);
373: stat.addBatch(sDelCoffee);
374: stat.addBatch(sInsCoffee);
375: int[] updateCount = stat.executeBatch();
376: updCountLength = updateCount.length;
377: trace("Successfully Updated");
378: trace("updateCount Length:" + updCountLength);
379: if (updCountLength != 3) {
380: error("executeBatch");
381: } else {
382: trace("executeBatch executes the Batch of SQL statements");
383: }
384: String query1 = "SELECT COUNT(*) FROM TEST WHERE TYPE_ID=1";
385: ResultSet rs = stat.executeQuery(query1);
386: rs.next();
387: retValue[i++] = rs.getInt(1);
388: // 1 as Delete Statement will delete only one row
389: retValue[i++] = 1;
390: // 1 as Insert Statement will insert only one row
391: retValue[i++] = 1;
392: for (int j = 0; j < updateCount.length; j++) {
393: trace("Update Count : " + updateCount[j]);
394: if (updateCount[j] != retValue[j]) {
395: error("j=" + j + " right:" + retValue[j]);
396: }
397: }
398: }
399:
400: public void testExecuteBatch05() throws Exception {
401: trace("testExecuteBatch05");
402: int updCountLength = 0;
403: int[] updateCount = stat.executeBatch();
404: updCountLength = updateCount.length;
405: trace("updateCount Length:" + updCountLength);
406: if (updCountLength == 0) {
407: trace("executeBatch Method does not execute the Empty Batch ");
408: } else {
409: error("executeBatch 0!=" + updCountLength);
410: }
411: }
412:
413: public void testExecuteBatch06() throws Exception {
414: trace("testExecuteBatch06");
415: boolean batchExceptionFlag = false;
416: // Insert a row which is already Present
417: String sInsCoffee = COFFEE_INSERT1;
418: String sDelCoffee = COFFEE_DELETE1;
419: stat.addBatch(sInsCoffee);
420: stat.addBatch(sInsCoffee);
421: stat.addBatch(sDelCoffee);
422: try {
423: stat.executeBatch();
424: } catch (BatchUpdateException b) {
425: batchExceptionFlag = true;
426: int[] updCounts = b.getUpdateCounts();
427: for (int i = 0; i < updCounts.length; i++) {
428: trace("Update counts:" + updCounts[i]);
429: }
430: }
431: if (batchExceptionFlag) {
432: trace("executeBatch insert duplicate; correct");
433: } else {
434: error("executeBatch");
435: }
436: }
437:
438: public void testExecuteBatch07() throws Exception {
439: trace("testExecuteBatch07");
440: boolean batchExceptionFlag = false;
441: String selectCoffee = COFFEE_SELECT1;
442: trace("selectCoffee = " + selectCoffee);
443: Statement stmt = conn.createStatement();
444: stmt.addBatch(selectCoffee);
445: try {
446: int[] updateCount = stmt.executeBatch();
447: trace("updateCount Length : " + updateCount.length);
448: } catch (BatchUpdateException be) {
449: batchExceptionFlag = true;
450: }
451: if (batchExceptionFlag) {
452: trace("executeBatch select");
453: } else {
454: error("executeBatch");
455: }
456: }
457:
458: public void testContinueBatch01() throws Exception {
459: trace("testContinueBatch01");
460: int[] batchUpdates = { 0, 0, 0 };
461: int buCountLen = 0;
462: try {
463: String sPrepStmt = COFFEE_UPDATE_SET;
464: trace("Prepared Statement String:" + sPrepStmt);
465: prep = conn.prepareStatement(sPrepStmt);
466: // Now add a legal update to the batch
467: prep.setInt(1, 1);
468: prep.setString(2, "Continue-1");
469: prep.setString(3, "COFFEE-1");
470: prep.addBatch();
471: // Now add an illegal update to the batch by
472: // forcing a unique constraint violation
473: // Try changing the key_id of row 3 to 1.
474: prep.setInt(1, 1);
475: prep.setString(2, "Invalid");
476: prep.setString(3, "COFFEE-3");
477: prep.addBatch();
478: // Now add a second legal update to the batch
479: // which will be processed ONLY if the driver supports
480: // continued batch processing according to 6.2.2.3
481: // of the J2EE platform spec.
482: prep.setInt(1, 2);
483: prep.setString(2, "Continue-2");
484: prep.setString(3, "COFFEE-2");
485: prep.addBatch();
486: // The executeBatch() method will result in a
487: // BatchUpdateException
488: prep.executeBatch();
489: } catch (BatchUpdateException b) {
490: trace("expected BatchUpdateException");
491: batchUpdates = b.getUpdateCounts();
492: buCountLen = batchUpdates.length;
493: }
494: if (buCountLen == 1) {
495: trace("no continued updates - OK");
496: return;
497: } else if (buCountLen == 3) {
498: trace("Driver supports continued updates.");
499: // Check to see if the third row from the batch was added
500: String query = COFFEE_SELECT_CONTINUED;
501: trace("Query is: " + query);
502: ResultSet rs = stat.executeQuery(query);
503: rs.next();
504: int count = rs.getInt(1);
505: rs.close();
506: stat.close();
507: trace("Count val is: " + count);
508: // Make sure that we have the correct error code for
509: // the failed update.
510: if (!(batchUpdates[1] == -3 && count == 1)) {
511: error("insert failed");
512: }
513: }
514: }
515: }
|