001: /* Copyright (c) 2001-2005, The HSQL Development Group
002: * All rights reserved.
003: *
004: * Redistribution and use in source and binary forms, with or without
005: * modification, are permitted provided that the following conditions are met:
006: *
007: * Redistributions of source code must retain the above copyright notice, this
008: * list of conditions and the following disclaimer.
009: *
010: * Redistributions in binary form must reproduce the above copyright notice,
011: * this list of conditions and the following disclaimer in the documentation
012: * and/or other materials provided with the distribution.
013: *
014: * Neither the name of the HSQL Development Group nor the names of its
015: * contributors may be used to endorse or promote products derived from this
016: * software without specific prior written permission.
017: *
018: * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
019: * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
020: * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
021: * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG,
022: * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
023: * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
024: * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
025: * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
026: * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
027: * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
028: * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
029: */
030:
031: package org.hsqldb.test;
032:
033: import java.sql.Connection;
034: import java.sql.Driver;
035: import java.sql.DriverManager;
036: import java.sql.PreparedStatement;
037: import java.sql.ResultSet;
038: import java.sql.SQLException;
039: import java.sql.Statement;
040:
041: import org.hsqldb.lib.StopWatch;
042:
043: /**
044: * A quick test of the new CompiledStatement and batch execution facilities.
045: *
046: * @author boucherb@users
047: * @since 1.7.2
048: * @version 1.7.2
049: */
050:
051: // fredt@users - modified to do some network connection tests
052: public class TestBatchExecution extends TestBase {
053:
054: static final String drop_table_sql = "drop table test if exists";
055: static final String create_cached = "create cached ";
056: static final String create_memory = "create memory ";
057: static final String create_temp = "create temp ";
058: static final String table_sql = "table test(id int primary key,"
059: + "fname varchar(20), lname " + "varchar(20), zip int)";
060: static final String insert_sql = "insert into test values(?,?,?,?)";
061: static final String update_sql = "update test set fname = 'Hans' where id = ?";
062: static final String select_sql = "select * from test where id = ?";
063: static final String delete_sql = "delete from test where id = ?";
064: static final String call_sql = "call identity()";
065: static final String shutdown_sql = "shutdown compact";
066: static final String def_db_path = "batchtest";
067: static final int def_runs = 5;
068: static final int rows = 10000;
069: static Connection conn;
070: static Statement stmnt;
071: static String url;
072:
073: public TestBatchExecution(String name) {
074: super (name);
075: }
076:
077: public void test() throws Exception {
078:
079: conn = newConnection();
080: stmnt = conn.createStatement();
081: url = super .url;
082:
083: nonPreparedTest();
084: preparedTestOne(5);
085: }
086:
087: static void print(String s) {
088: System.out.print(s);
089: }
090:
091: static void println(String s) {
092: System.out.println(s);
093: }
094:
095: static void printCommandStats(StopWatch sw, String cmd) {
096:
097: long et = sw.elapsedTime();
098:
099: print(sw.elapsedTimeToMessage(rows + " " + cmd));
100: println(" " + ((1000 * rows) / et) + " ops/s.");
101: }
102:
103: public static void main(String[] args) throws Exception {
104:
105: int runs;
106: String db_path;
107: String url;
108: Driver driver;
109:
110: runs = def_runs;
111: db_path = def_db_path;
112:
113: try {
114: runs = Integer.parseInt(args[0]);
115: } catch (Exception e) {
116: }
117:
118: try {
119: db_path = args[1];
120: } catch (Exception e) {
121: }
122:
123: // get the connection and statement
124: driver = (Driver) Class.forName("org.hsqldb.jdbcDriver")
125: .newInstance();
126:
127: DriverManager.registerDriver(driver);
128:
129: url = "jdbc:hsqldb:file:" + db_path;
130: conn = DriverManager.getConnection(url, "SA", "");
131: stmnt = conn.createStatement();
132:
133: runTests(runs);
134: }
135:
136: static void runTests(int runs) throws Exception {
137:
138: println("");
139: println("***************************************");
140: println("featuring cached (persistent) table");
141: println("***************************************");
142:
143: // drop and recreate the test table
144: println(drop_table_sql);
145: stmnt.execute(drop_table_sql);
146: println(create_cached + table_sql);
147: stmnt.execute(create_cached + table_sql);
148: preparedTestOne(runs);
149:
150: // drop the test table and shut down database
151: println(drop_table_sql);
152: stmnt.execute(drop_table_sql);
153: println("---------------------------------------");
154: println("shutting down database");
155: stmnt.execute(shutdown_sql);
156: println("---------------------------------------");
157:
158: // get the connection and statement
159: conn = DriverManager.getConnection(url, "SA", "");
160: stmnt = conn.createStatement();
161:
162: println("");
163: println("***************************************");
164: println("featuring memory (persistent) table");
165: println("***************************************");
166:
167: // drop and recreate the test table
168: println(drop_table_sql);
169: stmnt.execute(drop_table_sql);
170: println(create_memory + table_sql);
171: stmnt.execute(create_memory + table_sql);
172: preparedTestOne(runs);
173:
174: // drop the test table and shut down database
175: println(drop_table_sql);
176: stmnt.execute(drop_table_sql);
177: println("---------------------------------------");
178: println("shutting down database");
179: stmnt.execute(shutdown_sql);
180: println("---------------------------------------");
181:
182: // get the connection and statement
183: conn = DriverManager.getConnection(url, "SA", "");
184: stmnt = conn.createStatement();
185:
186: println("");
187: println("***************************************");
188: println("featuring temp (transient) table");
189: println("***************************************");
190:
191: // drop and recreate the test table
192: println(drop_table_sql);
193: stmnt.execute(drop_table_sql);
194: println(create_temp + table_sql);
195: stmnt.execute(create_temp + table_sql);
196: preparedTestOne(runs);
197:
198: // drop the test table
199: println(drop_table_sql);
200: stmnt.execute(drop_table_sql);
201: println("---------------------------------------");
202: println("shutting down database");
203: stmnt.execute(shutdown_sql);
204: println("---------------------------------------");
205: preparedTestTwo();
206: }
207:
208: public static void nonPreparedTest() throws Exception {
209:
210: stmnt.addBatch(drop_table_sql);
211: stmnt.addBatch(create_memory + table_sql);
212: stmnt.executeBatch();
213: }
214:
215: public static void preparedTestOne(int runs) throws Exception {
216:
217: PreparedStatement insertStmnt;
218: PreparedStatement updateStmnt;
219: PreparedStatement selectStmnt;
220: PreparedStatement deleteStmnt;
221: PreparedStatement callStmnt;
222: StopWatch sw;
223:
224: println("---------------------------------------");
225: println("Preparing Statements:");
226: println("---------------------------------------");
227: println(insert_sql);
228: println(update_sql);
229: println(select_sql);
230: println(delete_sql);
231: println(call_sql);
232:
233: sw = new StopWatch();
234:
235: // prepare the statements
236: insertStmnt = conn.prepareStatement(insert_sql);
237: updateStmnt = conn.prepareStatement(update_sql);
238: selectStmnt = conn.prepareStatement(select_sql);
239: deleteStmnt = conn.prepareStatement(delete_sql);
240: callStmnt = conn.prepareCall(call_sql);
241:
242: println("---------------------------------------");
243: println(sw.elapsedTimeToMessage("statements prepared"));
244: println("---------------------------------------");
245: sw.zero();
246:
247: // set up the batch data
248: for (int i = 0; i < rows; i++) {
249: insertStmnt.setInt(1, i);
250: insertStmnt.setString(2, "Julia");
251: insertStmnt.setString(3, "Peterson-Clancy");
252: insertStmnt.setInt(4, i);
253: updateStmnt.setInt(1, i);
254: selectStmnt.setInt(1, i);
255: deleteStmnt.setInt(1, i);
256: insertStmnt.addBatch();
257: updateStmnt.addBatch();
258: selectStmnt.addBatch();
259: deleteStmnt.addBatch();
260: callStmnt.addBatch();
261: }
262:
263: println("---------------------------------------");
264: println(sw.elapsedTimeToMessage("" + 5 * rows
265: + " batch entries created"));
266: sw.zero();
267:
268: // do the test loop forever
269: for (int i = 0; i < 1; i++) {
270: println("---------------------------------------");
271:
272: // inserts
273: sw.zero();
274: insertStmnt.executeBatch();
275: printCommandStats(sw, "inserts");
276:
277: // updates
278: sw.zero();
279: updateStmnt.executeBatch();
280: printCommandStats(sw, "updates");
281:
282: // selects
283: sw.zero();
284: selectStmnt.executeBatch();
285: printCommandStats(sw, "selects");
286:
287: // deletes
288: sw.zero();
289: deleteStmnt.executeBatch();
290: printCommandStats(sw, "deletes");
291:
292: // calls
293: sw.zero();
294: callStmnt.executeBatch();
295: printCommandStats(sw, "calls ");
296: }
297: }
298:
299: public static void preparedTestTwo() {
300:
301: try {
302: Class.forName("org.hsqldb.jdbcDriver");
303:
304: Connection con = DriverManager.getConnection(
305: "jdbc:hsqldb:.", "sa", "");
306:
307: System.out.println("con=" + con);
308:
309: Statement stmt = con.createStatement();
310:
311: try {
312: stmt.executeUpdate("drop table ttt");
313: } catch (Exception e) {
314: }
315:
316: stmt.executeUpdate("create table ttt (id integer)");
317:
318: PreparedStatement prep = con
319: .prepareStatement("INSERT INTO ttt (id) VALUES (?)");
320:
321: con.setAutoCommit(false);
322:
323: for (int i = 1; i <= 4; i++) { // [2, 3, 4]
324: prep.setInt(1, i);
325: prep.addBatch();
326: System.out.println("executeBatch() for " + i);
327: prep.executeBatch();
328: con.commit();
329:
330: // prep.clearBatch(); // -> java.lang.NullPointerException
331: // at org.hsqldb.Result.getUpdateCounts(Unknown Source)
332: }
333:
334: prep.close();
335:
336: // see what we got
337: ResultSet rs = stmt.executeQuery("select * from ttt");
338:
339: while (rs.next()) {
340: System.out.println("id = " + rs.getInt(1));
341: }
342:
343: System.out.println("bye.");
344: } catch (SQLException e) {
345: e.printStackTrace();
346: } catch (ClassNotFoundException e) {
347: e.printStackTrace();
348: }
349: }
350: }
|