Source code: nserverdemo/NsSampleClientThread.java
1 /*
2
3 Derby - Class nserverdemo.NsSampleClientThread
4
5 Copyright 2003, 2004 The Apache Software Foundation or its licensors, as applicable.
6
7 Licensed under the Apache License, Version 2.0 (the "License");
8 you may not use this file except in compliance with the License.
9 You may obtain a copy of the License at
10
11 http://www.apache.org/licenses/LICENSE-2.0
12
13 Unless required by applicable law or agreed to in writing, software
14 distributed under the License is distributed on an "AS IS" BASIS,
15 WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
16 See the License for the specific language governing permissions and
17 limitations under the License.
18
19 */
20
21 package nserverdemo;
22
23 import java.util.Properties;
24 import java.sql.SQLException;
25 import java.sql.DriverManager;
26 import java.io.IOException;
27 import java.sql.Statement;
28 import java.sql.ResultSet;
29 import java.sql.SQLException;
30 import java.sql.SQLWarning;
31 import java.io.PrintWriter;
32 import java.util.Properties;
33 import java.sql.PreparedStatement;
34 import java.sql.Connection;
35 import java.util.Random;
36 import java.lang.Math;
37 /**
38 * NsSampleClientThread thread to perform the NsSampleWork
39 */
40 public class NsSampleClientThread extends Thread {
41
42 protected int thread_id;
43 protected Properties properties;
44 protected PrintWriter pw;
45 protected String dbUrl;
46
47 NsSampleClientThread(int id,String dbUrl, Properties properties,PrintWriter pw) {
48 this.thread_id=id;
49 this.dbUrl = dbUrl;
50 this.properties = properties;
51 this.pw = pw;
52 }
53
54 public void run() {
55 System.out.println("[NsSampleClientThread] Thread id - "+this.thread_id + "; started.");
56 NsSampleWork w = new NsSampleWork(this.thread_id,dbUrl,properties,pw);
57 w.doWork(); // do all the NsSampleWork
58 pw.println("[NsSampleClientThread] Thread id - "+this.thread_id+"; finished all tasks.");
59 }
60 }
61
62
63
64 /**
65 * NsSampleWork class represents all the work done in the sample demo program.
66 * It includes
67 * getting a connection to the database, creating and loading of schema,
68 * preparing and execution of SQL statements (insert, select, update, delete )
69 */
70 class NsSampleWork {
71
72 protected int thread_id;
73 protected String dbUrl;
74 protected Properties properties;
75 PrintWriter pw;
76 PreparedStatement select = null;
77 PreparedStatement insert = null;
78 PreparedStatement delete = null;
79 PreparedStatement update = null;
80 PreparedStatement getMaxKey = null;
81
82
83
84 public static int counter=0;
85 static Integer lock = new Integer(0);
86 /**
87 * dbUrl is the database url to connect to
88 */
89 NsSampleWork(int id, String dbURL,Properties properties,PrintWriter pw) {
90 this.thread_id = id;
91 this.dbUrl = dbURL;
92 this.pw = pw;
93 this.properties = properties;
94 }
95
96
97 /**
98 * gets a database connection
99 * If the dbUrl is trying to connect to the Derby NetNsSampleWork server using JCC
100 * then the jcc driver must be already loaded before calling this method,
101 * else there will be an error
102 * return jcc connection if no error, else null
103 */
104 public Connection getConnection(String dbUrl, Properties properties) {
105 Connection conn = null;
106 try {
107 pw.println("[NsSampleWork] Thread id - "+thread_id + "; requests database connection, dbUrl ="+dbUrl);
108 conn = DriverManager.getConnection(dbUrl, properties);
109 } catch (Exception e) {
110 System.out.println("[NsSampleWork] Thread id - "+ thread_id + "; failed to get database connection. Exception thrown:");
111 e.printStackTrace();
112 }
113 return conn;
114 }
115
116
117 /**
118 * set the connection to this isolation level
119 */
120 public void setIsolationLevel(Connection conn, int level) {
121 try {
122 conn.setTransactionIsolation(level);
123 } catch (Exception e) {
124 pw.println("[NsSampleWork] Thread id - "+ thread_id +"; setIsolationLevel failed. Exception thrown: ");
125 e.printStackTrace();
126 }
127 }
128
129
130 /**
131 * close connection
132 */
133 public void closeConnection(Connection conn) {
134 try {
135 if(conn != null)
136 conn.close();
137 pw.println("[NsSampleWork] Thread id - "+thread_id + "; closed connection to the database.");
138 } catch (Exception e) {
139 pw.println("[NsSampleWork] Thread id - "+thread_id + "; error when closing connection;"+ e);
140 e.printStackTrace();
141 }
142 }
143
144
145 /**
146 * prepare required sql statements
147 */
148 public void prepareStmts(Connection conn) {
149 try {
150 select = conn.prepareStatement("select t_int, t_char, t_float,t_key from SAMPLETBL where t_key = ?");
151 insert = conn.prepareStatement("insert into SAMPLETBL (t_int, t_char,t_float,t_key) values (?,?,?,?)");
152 update = conn.prepareStatement(" update SAMPLETBL set t_int = ? where t_key = ?");
153 delete = conn.prepareStatement("delete from SAMPLETBL where t_key = ?");
154 getMaxKey = conn.prepareStatement("select max(t_key) from SAMPLETBL");
155 } catch (Exception e) {
156 e.printStackTrace();
157 }
158 }
159
160
161 /**
162 * executing a select and retrieving the results
163 * select the row with t_key value as 'selectWhat'
164 */
165 public int doSelectOperation(long selectWhat) {
166 int numRowsSelected = 0;
167 ResultSet rs = null;
168
169 try {
170 select.setLong(1,selectWhat);
171 rs = select.executeQuery();
172
173 while (rs.next()) {
174 numRowsSelected++;
175
176 int intVal = rs.getInt(1);
177 String strVal = rs.getString(2);
178 float floatVal = rs.getFloat(3);
179 long longVal = rs.getLong(4); //t_key column
180
181 pw.println("[NsSampleWork] Thread id - "+ thread_id +" selected "+numRowsSelected +" row ["+ intVal + ","
182 + strVal +","+ floatVal +","+ longVal +"]");
183 }
184 } catch (Exception e) {
185 e.printStackTrace();
186 } finally {
187 try {
188 if(rs != null)
189 rs.close();
190 } catch (Exception e) {
191 e.printStackTrace();
192 }
193 }
194 return numRowsSelected;
195 }
196
197
198 /**
199 * Opens a connection and executes DML (insert, select, update, delete) operations
200 */
201 public void doWork() {
202
203 Connection conn = null;
204 ResultSet rs = null;
205 try {
206 conn = getConnection(dbUrl,properties);
207
208 if(conn == null)
209 throw new Exception("Failed to obtain connection!");
210
211 conn.setAutoCommit(true);
212
213 // Setting isolation level to read uncommitted, since this is a sample application.
214 // Please set the isolation level depending on the requirements of your application
215 setIsolationLevel(conn,Connection.TRANSACTION_READ_UNCOMMITTED);
216
217 prepareStmts(conn);
218
219 // Perform the DML operations
220 for (int i=0; i<NsSample.ITERATIONS; i++) {
221 // Choose between either a select or any one of (insert or update or delete ) operation
222 int choice = (int) (Math.random() * 100) % 2;
223 switch (choice) {
224 case 0: { //select a row
225 rs = getMaxKey.executeQuery(); //gets max t_key value
226 long selectWhere = 0;
227 if(rs.next()) {
228 selectWhere = rs.getLong(1);
229 }
230 int numSelected = doSelectOperation(selectWhere);
231 break;
232 }
233
234 case 1: { //do an insert, update or delete
235 doIUDOperation();
236 break;
237 }
238 } //end of switch()
239 }//enf of for()
240
241 } catch(Exception e) {
242 pw.println("[NsSampleWork] Thread id - "+ thread_id + "; error when performing dml operations; ");
243 e.printStackTrace();
244 } finally {
245 try {
246 if(rs != null)
247 rs.close();
248
249 closeConnection(conn);
250 cleanup();
251 } catch(Exception ee) {
252 pw.println("[NsSampleWork] Thread id - " + thread_id+"; error when cleaning up connection, resultset; exception is ");
253 ee.printStackTrace();
254 }
255 }
256 }//end of method doNsSampleWork()
257
258
259 /**
260 * close resources
261 */
262 public void cleanup() {
263 try{
264 if(select != null)
265 select.close();
266 if(insert != null)
267 insert.close();
268 if(delete != null)
269 delete.close();
270 if(update != null)
271 update.close();
272 if(getMaxKey != null)
273 getMaxKey.close();
274 } catch (Exception e) {
275 e.printStackTrace();
276 }
277 }
278
279
280 /**
281 * Perform an insert or an update or delete operation
282 */
283 public void doIUDOperation() {
284 int decide = (int) (Math.random() * 100) % 3;
285 ResultSet rs = null;
286
287 try {
288 switch (decide) {
289 case 0: { //insert
290 int numInsert = insertRow(insert);
291 pw.println("[NsSampleWork] Thread id - "+thread_id+"; inserted "+numInsert+" row.");
292 break;
293 }
294
295 case 1: { //update
296 rs = getMaxKey.executeQuery();
297 long updateRow=0;
298 if(rs.next())
299 updateRow = rs.getLong(1);
300 int numUpdate = updateRow(update,updateRow);
301 System.out.println("[NsSampleWork] Thread id - "+thread_id+"; updated "+numUpdate+" row with t_key = " + updateRow);
302 break;
303 }
304
305 case 2: { //delete
306 rs = getMaxKey.executeQuery();
307 long deleteRow =0;
308 if(rs.next())
309 deleteRow = rs.getLong(1);
310 int numDelete = deleteRow(delete,deleteRow);
311 System.out.println("[NsSampleWork] Thread id - "+thread_id+"; deleted "+numDelete+" row with t_key = " + deleteRow);
312 break;
313 }
314 }//end of switch()
315 } catch (Exception e) {
316 e.printStackTrace();
317 } finally {
318 try {
319 if(rs != null)
320 rs.close();
321 } catch (Exception e) {
322 e.printStackTrace();
323 }
324 }
325 }//end of method doIUDOperation()
326
327
328 /**
329 * Create necessary schema if schema not already created
330 */
331 public static void checkAndCreateSchema(Connection conn,PrintWriter pw) {
332 Statement stmt = null;
333 ResultSet rs = null;
334
335 try {
336 conn.setAutoCommit(true);
337 } catch (SQLException se) {
338 pw.println("[NsSampleWork] Error when setting autocommit on connection; exception thrown: ");
339 se.printStackTrace();
340 }
341
342 // Check for existence of schema by quering the catalog systables
343 try {
344 stmt = conn.createStatement();
345 rs = stmt.executeQuery("select tablename from sys.systables " +
346 " where tablename = 'SAMPLETBL'");
347 if (rs.next()) {
348 pw.println("[NsSampleWork] Table 'SAMPLETBL' already exists; no need to create schema again.");
349 return;
350 }
351 } catch (SQLException se) {
352 pw.println("[NsSampleWork] Unable to query the metadata for existence of table SAMPLETBL; exception is "+se);
353 pw.println("[NsSampleWork] Exiting the application.");
354 se.printStackTrace();
355 System.exit(1);
356 }
357
358 // Create the necessary table and indexes
359 try {
360 pw.println("[NsSampleWork] Begin creating table - SAMPLETBL and necessary indexes. ");
361 stmt.execute("create table SAMPLETBL (" +
362 "t_int int," +
363 "t_char char(15),"+
364 "t_float float," +
365 "t_key bigint )");
366 stmt.execute("create index t_char_idx on SAMPLETBL ( t_char)");
367 stmt.execute("create index t_float_idx on SAMPLETBL ( t_float)");
368 stmt.execute("create index t_key_idx on SAMPLETBL ( t_key )" );
369 } catch (Exception e) {
370 pw.println("[NsSampleWork] Error when creating schema; exception is " + e.toString());
371 pw.println("[NsSampleWork] Exiting the application.");
372 e.printStackTrace();
373 System.exit(1);
374 } finally {
375 try {
376 if(rs != null)
377 rs.close();
378 if(stmt != null)
379 stmt.close();
380 } catch (Exception e) {
381 e.printStackTrace();
382 }
383 }
384 }//end of method checkAndCreateSchema()
385
386
387 /**
388 * Loads schema , inserts 'rowsToInsert' number of rows into the table
389 */
390 public static void loadSchema(Connection conn,int rowsToInsert,PrintWriter pw) {
391 int insertsRemaining = rowsToInsert;
392 PreparedStatement ps=null;
393
394 try {
395 ps = conn.prepareStatement("insert into SAMPLETBL (t_int, t_char,t_float,t_key) values (?,?,?,?)");
396 // Insert one row at a time
397 while (insertsRemaining-- >= 0) {
398 int numInserts = insertRow(ps);
399 if (numInserts != 1)
400 pw.println("[NsSampleWork] Failed to insert row.");
401 }
402 } catch (Exception e) {
403 pw.println("[NsSampleWork] Error when loading schema; exception is "+ e);
404 e.printStackTrace();
405 } finally {
406 try {
407 if(ps != null)
408 ps.close();
409 } catch (Exception e) {
410 e.printStackTrace();
411 }
412 }
413 }//end of method loadSchema()
414
415
416
417 /**
418 * Generates random values and performs the inserts into the database
419 */
420 public static int insertRow(PreparedStatement ps) {
421
422 int rowsAdded = 0;
423 try {
424 // Generate random values for the datatypes in the sample table
425 Random rand = new Random();
426 int intVal = Math.abs(rand.nextInt()%1000);
427
428 String charVal = "Derby";
429
430 synchronized(lock) {
431 charVal += counter;
432 counter++;
433 }
434
435 // Set parameter values
436 ps.setInt(1, intVal);
437 ps.setString(2,charVal);
438 ps.setFloat(3, rand.nextFloat()*(float)Math.pow(10,Math.abs(rand.nextInt()%30)));
439 ps.setLong(4,rand.nextLong()%10000);
440 rowsAdded = ps.executeUpdate();
441 return rowsAdded;
442 } catch (Exception e) {
443 e.printStackTrace();
444 return 0;
445 }
446 }
447
448
449 /**
450 * update a row in the table
451 * updateWhere is the value of the t_key row which needs to be updated
452 * return number of rows updated
453 */
454 public static int updateRow (PreparedStatement ps,long updateWhere) {
455 try {
456 int val=0;
457 synchronized(lock) {
458 val = counter++;
459 }
460 ps.setInt(1,val);
461 ps.setLong(2,updateWhere);
462 return(ps.executeUpdate());
463 } catch (SQLException se) {
464 se.printStackTrace();
465 return 0;
466 }
467 }
468
469
470 /**
471 * Delete row from table
472 * deleteRow is the value of the t_key of the row to be deleted
473 * return number of rows deleted
474 */
475 public static int deleteRow(PreparedStatement ps,long deleteRow) {
476 int rowsDeleted = 0;
477 try {
478 ps.setLong(1, deleteRow);
479 rowsDeleted = ps.executeUpdate();
480 return rowsDeleted;
481 } catch(Exception e) {
482 e.printStackTrace();
483 return 0;
484 }
485 }
486
487 }//end of class NsSampleWork