Java > Open Source Codes > nserverdemo > NsSampleClientThread


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

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags