|
1
30
31
32 package org.hsqldb.test;
33
34 import java.sql.Connection  ;
35 import java.sql.Driver ;
36 import java.sql.DriverManager ;
37 import java.sql.PreparedStatement ;
38 import java.sql.ResultSet ;
39 import java.sql.SQLException ;
40 import java.sql.Statement ;
41
42 import org.hsqldb.lib.StopWatch;
43
44
51
52 public class TestBatchExecution extends TestBase {
54
55 static final String drop_table_sql = "drop table test if exists";
56 static final String create_cached = "create cached ";
57 static final String create_memory = "create memory ";
58 static final String create_temp = "create temp ";
59 static final String table_sql = "table test(id int primary key,"
60 + "fname varchar(20), lname "
61 + "varchar(20), zip int)";
62 static final String insert_sql = "insert into test values(?,?,?,?)";
63 static final String update_sql =
64 "update test set fname = 'Hans' where id = ?";
65 static final String select_sql = "select * from test where id = ?";
66 static final String delete_sql = "delete from test where id = ?";
67 static final String call_sql = "call identity()";
68 static final String shutdown_sql = "shutdown compact";
69 static final String def_db_path = "batchtest";
70 static final int def_runs = 5;
71 static final int rows = 10000;
72 static Connection conn;
73 static Statement stmnt;
74 static String url;
75
76 public TestBatchExecution(String name) {
77 super(name);
78 }
79
80 public void test() throws Exception {
81
82 conn = newConnection();
83 stmnt = conn.createStatement();
84 url = super.url;
85
86 nonPreparedTest();
87 preparedTestOne(5);
88 }
89
90 static void print(String s) {
91 System.out.print(s);
92 }
93
94 static void println(String s) {
95 System.out.println(s);
96 }
97
98 static void printCommandStats(StopWatch sw, String cmd) {
99
100 long et = sw.elapsedTime();
101
102 print(sw.elapsedTimeToMessage(rows + " " + cmd));
103 println(" " + ((1000 * rows) / et) + " ops/s.");
104 }
105
106 public static void main(String [] args) throws Exception {
107
108 int runs;
109 String db_path;
110 String url;
111 Driver driver;
112
113 runs = def_runs;
114 db_path = def_db_path;
115
116 try {
117 runs = Integer.parseInt(args[0]);
118 } catch (Exception e) {}
119
120 try {
121 db_path = args[1];
122 } catch (Exception e) {}
123
124 driver =
126 (Driver ) Class.forName("org.hsqldb.jdbcDriver").newInstance();
127
128 DriverManager.registerDriver(driver);
129
130 url = "jdbc:hsqldb:file:" + db_path;
131 conn = DriverManager.getConnection(url, "SA", "");
132 stmnt = conn.createStatement();
133
134 runTests(runs);
135 }
136
137 static void runTests(int runs) throws Exception {
138
139 println("");
140 println("***************************************");
141 println("featuring cached (persistent) table");
142 println("***************************************");
143
144 println(drop_table_sql);
146 stmnt.execute(drop_table_sql);
147 println(create_cached + table_sql);
148 stmnt.execute(create_cached + table_sql);
149 preparedTestOne(runs);
150
151 println(drop_table_sql);
153 stmnt.execute(drop_table_sql);
154 println("---------------------------------------");
155 println("shutting down database");
156 stmnt.execute(shutdown_sql);
157 println("---------------------------------------");
158
159 conn = DriverManager.getConnection(url, "SA", "");
161 stmnt = conn.createStatement();
162
163 println("");
164 println("***************************************");
165 println("featuring memory (persistent) table");
166 println("***************************************");
167
168 println(drop_table_sql);
170 stmnt.execute(drop_table_sql);
171 println(create_memory + table_sql);
172 stmnt.execute(create_memory + table_sql);
173 preparedTestOne(runs);
174
175 println(drop_table_sql);
177 stmnt.execute(drop_table_sql);
178 println("---------------------------------------");
179 println("shutting down database");
180 stmnt.execute(shutdown_sql);
181 println("---------------------------------------");
182
183 conn = DriverManager.getConnection(url, "SA", "");
185 stmnt = conn.createStatement();
186
187 println("");
188 println("***************************************");
189 println("featuring temp (transient) table");
190 println("***************************************");
191
192 println(drop_table_sql);
194 stmnt.execute(drop_table_sql);
195 println(create_temp + table_sql);
196 stmnt.execute(create_temp + table_sql);
197 preparedTestOne(runs);
198
199 println(drop_table_sql);
201 stmnt.execute(drop_table_sql);
202 println("---------------------------------------");
203 println("shutting down database");
204 stmnt.execute(shutdown_sql);
205 println("---------------------------------------");
206 preparedTestTwo();
207 }
208
209 public static void nonPreparedTest() throws Exception {
210
211 stmnt.addBatch(drop_table_sql);
212 stmnt.addBatch(create_memory + table_sql);
213 stmnt.executeBatch();
214 }
215
216 public static void preparedTestOne(int runs) throws Exception {
217
218 PreparedStatement insertStmnt;
219 PreparedStatement updateStmnt;
220 PreparedStatement selectStmnt;
221 PreparedStatement deleteStmnt;
222 PreparedStatement callStmnt;
223 StopWatch sw;
224
225 println("---------------------------------------");
226 println("Preparing Statements:");
227 println("---------------------------------------");
228 println(insert_sql);
229 println(update_sql);
230 println(select_sql);
231 println(delete_sql);
232 println(call_sql);
233
234 sw = new StopWatch();
235
236 insertStmnt = conn.prepareStatement(insert_sql);
238 updateStmnt = conn.prepareStatement(update_sql);
239 selectStmnt = conn.prepareStatement(select_sql);
240 deleteStmnt = conn.prepareStatement(delete_sql);
241 callStmnt = conn.prepareCall(call_sql);
242
243 println("---------------------------------------");
244 println(sw.elapsedTimeToMessage("statements prepared"));
245 println("---------------------------------------");
246 sw.zero();
247
248 for (int i = 0; i < rows; i++) {
250 insertStmnt.setInt(1, i);
251 insertStmnt.setString(2, "Julia");
252 insertStmnt.setString(3, "Peterson-Clancy");
253 insertStmnt.setInt(4, i);
254 updateStmnt.setInt(1, i);
255 selectStmnt.setInt(1, i);
256 deleteStmnt.setInt(1, i);
257 insertStmnt.addBatch();
258 updateStmnt.addBatch();
259 selectStmnt.addBatch();
260 deleteStmnt.addBatch();
261 callStmnt.addBatch();
262 }
263
264 println("---------------------------------------");
265 println(sw.elapsedTimeToMessage("" + 5 * rows
266 + " batch entries created"));
267 sw.zero();
268
269 for (int i = 0; i < 1; i++) {
271 println("---------------------------------------");
272
273 sw.zero();
275 insertStmnt.executeBatch();
276 printCommandStats(sw, "inserts");
277
278 sw.zero();
280 updateStmnt.executeBatch();
281 printCommandStats(sw, "updates");
282
283 sw.zero();
285 selectStmnt.executeBatch();
286 printCommandStats(sw, "selects");
287
288 sw.zero();
290 deleteStmnt.executeBatch();
291 printCommandStats(sw, "deletes");
292
293 sw.zero();
295 callStmnt.executeBatch();
296 printCommandStats(sw, "calls ");
297 }
298 }
299
300 public static void preparedTestTwo() {
301
302 try {
303 Class.forName("org.hsqldb.jdbcDriver");
304
305 Connection con = DriverManager.getConnection("jdbc:hsqldb:.",
306 "sa", "");
307
308 System.out.println("con=" + con);
309
310 Statement stmt = con.createStatement();
311
312 try {
313 stmt.executeUpdate("drop table ttt");
314 } catch (Exception e) {}
315
316 stmt.executeUpdate("create table ttt (id integer)");
317
318 PreparedStatement prep =
319 con.prepareStatement("INSERT INTO ttt (id) VALUES (?)");
320
321 con.setAutoCommit(false);
322
323 for (int i = 1; i <= 4; i++) { prep.setInt(1, i);
325 prep.addBatch();
326 System.out.println("executeBatch() for " + i);
327 prep.executeBatch();
328 con.commit();
329
330 }
333
334 prep.close();
335
336 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 }
351 |
Popular Tags |
Java API By Example, From Geeks To Geeks.