|
1
18
19 package sync4j.syncclient.sps.jdbc;
20
21 import java.sql.Connection  ;
22 import java.sql.DriverManager ;
23 import java.sql.PreparedStatement ;
24 import java.sql.ResultSet ;
25 import java.sql.ResultSetMetaData ;
26 import java.sql.SQLException ;
27 import java.sql.Statement ;
28
29 import java.util.Date ;
30 import java.util.Hashtable ;
31 import java.util.Vector ;
32
33 import sync4j.syncclient.common.logging.Logger;
34
35 import sync4j.syncclient.spdm.DMException;
36 import sync4j.syncclient.spdm.ManagementNode;
37 import sync4j.syncclient.spdm.SimpleDeviceManager;
38
39 import sync4j.syncclient.sps.common.DataStore;
40 import sync4j.syncclient.sps.common.Record;
41 import sync4j.syncclient.sps.common.RecordMetadata;
42 import sync4j.syncclient.sps.common.RecordFilter;
43
44
51 public class DataStoreImpl extends DataStore {
52
53
55 Logger logger = new Logger();
56
57
59 public DataStoreImpl(String dataStoreName, RecordMetadata recordMetadata) {
60 super(dataStoreName, recordMetadata);
61 }
62
63
65
71 public Record newRecord(String key) {
72
73 Record record = null;
74 record = new Record(this, key);
75 return record;
76
77 }
78
79
83 public Record readRecord(Record record) {
84
85 String query = null;
86 Object params[] = null;
87
88 Vector dataRows = null;
89 Object [] dataRow = null;
90
91
92 query = DataStoreSql.selectQuery(this, record);
93
94 params = new Object [1];
95
96 params[0] = (Object ) record.getKey();
97
98 dataRows = execPreparedSelect(query, params);
99
100 for (int i=0; i<dataRows.size(); i++) {
101
102 dataRow = (Object []) dataRows.elementAt(i);
103
104 for (int j=1; j <= dataRow.length; j++) {
105 record.setField(j, (String ) dataRow[j-1]);
106 }
107 }
108
109 return record;
110
111 }
112
113
117 public Record storeRecord(Record record) {
118
119 String query = null;
120 Object params[] = null;
121
122 Vector dataRows = null;
123 Object [] dataRow = null;
124
125 boolean findRecord = false;
126
127 int recordSize = 0;
128
129 query = DataStoreSql.selectQuery(this, record);
130
131 params = new Object [1];
132
133 params[0] = (Object ) record.getKey();
134
135 dataRows = execPreparedSelect(query, params);
136
137 if (dataRows.size() > 0) {
138 findRecord = true;
139 }
140
141 recordSize = this.getRecordMetadata().getFieldMetadata().length;
142
143 params = new Object [recordSize];
144
145 if (findRecord) {
146
147 query = DataStoreSql.updateQuery(this, record);
148
149 int i=0;
150
151 for(i = 1; i < recordSize; i++) {
152
153 if (record.getString(i+1) == null || record.getString(i+1).equals("null")) {
155 params[i-1] = null;
156 } else {
157 params[i-1] = (Object ) record.getString(i+1);
158 }
159 }
160
161 params[i-1] = (Object ) record.getString(1);
163
164
165 } else {
166
167 record.setKey(String.valueOf(getNextKey()));
168
169 query= DataStoreSql.insertQuery(this, record);
170
171 for(int i = 1; i <= recordSize; i++) {
172
173 if (record.getString(i) == null || record.getString(i).equals("null")) {
175 params[i-1] = null;
176 } else {
177 params[i-1] = (Object ) record.getString(i);
178 }
179 }
180
181 }
182
183 execPreparedUpdate(query, params);
184
185 return record;
186
187 }
188
189
193 public void deleteRecord(Record record) {
194
195 String query = null;
196 Object params[] = null;
197
198 Vector dataRows = null;
199 Object [] dataRow = null;
200
201 boolean findRecord = false;
202
203 query = DataStoreSql.selectQuery(this, record);
204
205 params = new Object [1];
206
207 params[0] = (Object ) record.getKey();
208
209 dataRows = execPreparedSelect(query, params);
210
211 if (dataRows.size() > 0) {
212 findRecord = true;
213 }
214
215 if (findRecord) {
216 query = DataStoreSql.deleteQuery(this, record);
217 execPreparedUpdate(query, params);
218 } else {
219 return;
221 }
222
223 return;
224
225 }
226
227
232 public Vector findAllRecords() {
233
234 Vector records = new Vector ();
235
236 Record record = null;
237
238 Connection con = null;
239 Statement stmt = null;
240 ResultSet rs = null;
241 ResultSetMetaData rsmd = null;
242
243 String query = null;
244
245 try {
246
247 con = getConnection();
248
249 stmt = con.createStatement();
250
251 query = DataStoreSql.allRecordsQuery(this);
252
253 rs = stmt.executeQuery(query);
254
255 rsmd = rs.getMetaData();
256
257 while (rs.next()) {
258
259 record = new Record(this, rs.getString(1));
260
261 for (int i=2; i <= rsmd.getColumnCount(); i++) {
262 record.setField (i, rs.getString(i));
263 }
264
265 records.addElement(record);
266
267 }
268
269 } catch(Exception e) {
270 if (logger.isLoggable(Logger.INFO)) {
271 logger.info("Error reading records: " + e.getMessage());
272 }
273 } finally {
274
275 if(con != null) {
276 try {con.close();} catch (Exception e){}
277 con = null;
278 }
279
280 if(stmt != null) {
281 try {stmt.close();} catch (Exception e){}
282 stmt = null;
283 }
284
285 if(rs != null) {
286 try{rs.close();} catch (Exception e){}
287 rs = null;
288 }
289
290 }
291
292 return records;
293
294 }
295
296
304 public Vector findRecords(char state, Date since) {
305
306 Vector records = new Vector ();
307
308 Record record = null;
309
310 Connection con = null;
311 PreparedStatement ps = null;
312 Statement stmt = null;
313 ResultSet rs = null;
314 ResultSetMetaData rsmd = null;
315
316 String query = null;
317
318 String nameFieldState = null;
319 String nameFieldTimestamp = null;
320
321 String function = null;
322
323 for (int i=0; i < this.getRecordMetadata().getFieldMetadata().length; i++) {
324
325 function = (String ) this.getRecordMetadata().getFieldMetadata()[i].getAttributes().get("function");
326
327 if (state!=' ' && function.equals("mtype")) {
328 nameFieldState = this.getRecordMetadata().getFieldMetadata()[i].getName();
329 }
330
331 if (since!=null && function.equals("timestamp")) {
332 nameFieldTimestamp = this.getRecordMetadata().getFieldMetadata()[i].getName();
333 }
334
335 }
336
337 try {
338
339 con = getConnection();
340
341 stmt = con.createStatement();
342
343 query = DataStoreSql.RecordsByStateByDateQuery(this, nameFieldState, nameFieldTimestamp);
344
345 ps = con.prepareStatement(query);
346
347 if (nameFieldState != null && nameFieldTimestamp != null) {
348 ps.setString(1, String.valueOf(state));
349 ps.setDate(2, new java.sql.Date (since.getTime()));
350 } else if (nameFieldState != null && nameFieldTimestamp == null) {
351 ps.setString(1, String.valueOf(state));
352 } else if (nameFieldState == null && nameFieldTimestamp != null) {
353 ps.setDate(1, new java.sql.Date (since.getTime()));
354 }
355
356 rs = ps.executeQuery();
357
358 rsmd = rs.getMetaData();
359
360 while (rs.next()) {
361
362 record = new Record(this, rs.getString(1));
363
364 for (int i=2; i <= rsmd.getColumnCount(); i++) {
365 record.setField (i, rs.getString(i));
366 }
367
368 records.addElement(record);
369
370 }
371
372 } catch(Exception e) {
373 if (logger.isLoggable(Logger.INFO) ||
374 logger.isLoggable(Logger.DEBUG)) {
375 logger.info("Error reading records: " + e.getMessage());
376 }
377 } finally {
378
379 if(con != null) {
380 try {con.close();} catch (Exception e){}
381 con = null;
382 }
383
384 if(stmt != null) {
385 try {stmt.close();} catch (Exception e){}
386 stmt = null;
387 }
388
389 if(rs != null) {
390 try{rs.close();} catch (Exception e){}
391 rs = null;
392 }
393
394 }
395
396 return records;
397
398 }
399
400
401
407 public Vector findRecords(RecordFilter recordFilter) {
408
409 Vector allRecords = null;
410 Vector findRecords = new Vector ();
411
412 Record record = null;
413
414 allRecords = this.findAllRecords();
415
416 for(int i=0; i < allRecords.size(); i++) {
417
418 record = this.readRecord((Record) allRecords.elementAt(i));
419
420 if(recordFilter.accept(record)) {
421 findRecords.addElement(allRecords.elementAt(i));
422 }
423 }
424
425 return findRecords;
426
427 }
428
429
432 public long getNextKey() {
433
434 Connection con = null;
435 Statement stmt = null;
436 ResultSet rs = null;
437 ResultSetMetaData rsmd = null;
438
439 String query = null;
440
441 String function = null;
442 String nameFieldKey = null;
443
444 int nRecords = 0;
445 long key = 0;
446
447
448 for (int i=0; i < this.getRecordMetadata().getFieldMetadata().length; i++) {
449
450 function = (String ) this.getRecordMetadata().getFieldMetadata()[i].getAttributes().get("function");
451
452 if (function.equals("key")) {
453 nameFieldKey = this.getRecordMetadata().getFieldMetadata()[i].getName();
454 }
455
456 }
457
458 try {
459
460 con = getConnection();
461
462 stmt = con.createStatement();
463
464 query = "select count(*) from " + this.getDataStoreName();
465
466 rs = stmt.executeQuery(query);
467
468 while (rs.next()) {
469
470 try {
471 nRecords = Integer.parseInt(rs.getString(1));
472 } catch (NumberFormatException e) {
473 if (logger.isLoggable(Logger.INFO)) {
474 logger.info("Error reading records: " + e.getMessage());
475 }
476 }
477 }
478
479 if (nRecords!=0) {
480
481 query = DataStoreSql.getNextKeyQuery(this, nameFieldKey);
482
483 rs = stmt.executeQuery(query);
484
485 while (rs.next()) {
486 try {
487 key = Long.parseLong(rs.getString(1));
488 } catch (NumberFormatException e) {
489 if (logger.isLoggable(Logger.INFO)) {
490 logger.info("Error reading records: " + e.getMessage());
491 }
492 }
493 }
494
495 } else {
496 key = 1000;
498
499 }
500
501
502 } catch(Exception e) {
503 if (logger.isLoggable(Logger.INFO)) {
504 logger.info("Error reading records: " + e.getMessage());
505 }
506 } finally {
507
508 if(con != null) {
509 try {con.close();} catch (Exception e){}
510 con = null;
511 }
512
513 if(stmt != null) {
514 try {stmt.close();} catch (Exception e){}
515 stmt = null;
516 }
517
518 if(rs != null) {
519 try{rs.close();} catch (Exception e){}
520 rs = null;
521 }
522
523 }
524
525 return key;
526 }
527
528
530
536 private void execPreparedUpdate(String query, Object [] params) {
537
538 Connection con = null;
539 ResultSet rs = null;
540
541 PreparedStatement preparedStatement;
542
543 try {
544
545 con = getConnection();
546
547 preparedStatement = con.prepareStatement(query);
548
549 int numParam = params.length;
550
551 for (int i=0; i<numParam; i++) {
552 preparedStatement.setObject(i+1, params[i]);
553 }
554
555 preparedStatement.executeUpdate();
556
557 } catch(Exception e) {
558 if (logger.isLoggable(Logger.INFO)) {
559 logger.info("Error reading records: " + e.getMessage());
560 }
561 } finally {
562
563 if(con != null) {
564 try{con.close();} catch (Exception e){}
565 con = null;
566 }
567
568 }
569
570 }
571
572
579 private Vector execPreparedSelect(String query, Object [] params) {
580
581 Connection con = null;
582 ResultSet rs = null;
583
584 Vector dataRows;
585 Object [] dataRow = null;
586 Object obj =null;
587
588 dataRows = new Vector ();
589
590
591 try {
592
593 con = getConnection();
594
595 PreparedStatement preparedStatement = con.prepareStatement(query);
596
597 int numParam = params.length;
598
599 for (int i=0; i<numParam; i++) {
600 preparedStatement.setObject(i+1, params[i]);
601 }
602
603 rs = preparedStatement.executeQuery();
604
605 int numCol = rs.getMetaData().getColumnCount();
606
607 while (rs.next()) {
608
609 dataRow = new Object [numCol];
610
611 for (int i=0; i<numCol; i++) {
612 obj = rs.getObject(i+1);
613 dataRow[i] = obj;
614 }
615
616 dataRows.addElement(dataRow);
617
618 }
619 } catch(Exception e) {
620 if (logger.isLoggable(Logger.INFO)) {
621 logger.info("Error reading records: " + e.getMessage());
622 }
623 } finally {
624
625 if(con != null) {
626 try{con.close();} catch (Exception e){}
627 con = null;
628 }
629
630 }
631
632 return dataRows;
633
634 }
635
636
639 private Connection getConnection ()
640 throws ClassNotFoundException , SQLException , DMException {
641
642 String jdbcDriver = null;
643 String urlConnection = null;
644 String userConnection = null;
645 String passwordConnection = null;
646
647 Connection con = null;
648
649 String contextSources = "sps/jdbc";
650
651 Hashtable jdbcParams = null;
652
653 String appURI = null;
654
655 ManagementNode rootNode = null;
656 ManagementNode sourcesNode = null;
657 ManagementNode[] sources = null;
658
659
662
663 try {
664
665 rootNode = SimpleDeviceManager.getDeviceManager().getManagementTree(appURI);
666
667 sourcesNode = rootNode.getChildNode(contextSources);
668
669 sources = sourcesNode.getChildren();
670
671 jdbcParams = sources[0].getValues();
672
673 } catch (DMException e) {
674 if (logger.isLoggable(Logger.INFO)) {
675 logger.info("Error reading jdbc config values: " + e.getMessage());
676 }
677 }
678
679 jdbcDriver = (String ) jdbcParams.get("jdbcDriver");
680 urlConnection = (String ) jdbcParams.get("url");
681 userConnection = (String ) jdbcParams.get("user");
682 passwordConnection = (String ) jdbcParams.get("password");
683
684 Class.forName(jdbcDriver);
685
686 con = DriverManager.getConnection(urlConnection, userConnection, passwordConnection);
687
688 return con;
689
690 }
691
692
695 public void startDBOperations() {
696
697 }
698
699
702 public void commitDBOperations() {
703
704 }
705
706 } |
Popular Tags |
Java API By Example, From Geeks To Geeks.