1 package org.kuali.ole.select.gokb.service.impl;
2
3 import org.kuali.ole.docstore.common.util.DataSource;
4 import org.kuali.ole.select.gokb.*;
5 import org.kuali.ole.select.gokb.service.GokbRdbmsService;
6 import org.kuali.rice.core.api.config.property.ConfigContext;
7 import org.slf4j.Logger;
8 import org.slf4j.LoggerFactory;
9
10 import java.beans.PropertyVetoException;
11 import java.io.IOException;
12 import java.sql.*;
13 import java.text.DateFormat;
14 import java.text.SimpleDateFormat;
15 import java.util.Date;
16 import java.util.List;
17
18
19
20
21 public class GokbRdbmsServiceImpl implements GokbRdbmsService {
22
23 private static final Logger LOG = LoggerFactory.getLogger(GokbLocalServiceImpl.class);
24
25 private Connection connection = null;
26 private final static String dbVendor = ConfigContext.getCurrentContextConfig().getProperty("db.vendor");
27
28 private static Connection getConnection() throws SQLException {
29 DataSource dataSource = null;
30 try {
31 dataSource = DataSource.getInstance();
32 } catch (IOException e) {
33 LOG.error("Exception While getting the connection: " + e);
34 } catch (SQLException e) {
35 LOG.error("Exception While getting the connection: " + e);
36 } catch (PropertyVetoException e) {
37 LOG.error("Exception While getting the connection: " + e);
38 }
39 return dataSource.getConnection();
40 }
41
42
43
44
45
46
47
48 public void insertOrUpdatePackages(List<OleGokbPackage> oleGokbPackages) {
49 try {
50 if (connection == null || connection.isClosed()) {
51 connection = getConnection();
52 }
53 PreparedStatement pstmt = connection.prepareStatement(INSERT_PACKAGE_PREPARED_STMT);
54 for (OleGokbPackage oleGokbPack : oleGokbPackages) {
55 buildPackage(pstmt, oleGokbPack);
56 try {
57 pstmt.execute();
58 } catch (SQLException ex) {
59 if (!ex.getMessage().isEmpty() && ex.getMessage().contains("Duplicate entry")) {
60 updatePackage(oleGokbPack);
61 }
62 }
63 }
64 connection.commit();
65 pstmt.close();
66 } catch (SQLException ex) {
67 LOG.error("Exception while Inserting the Package: " + ex);
68 } finally {
69 try {
70 closeConnections();
71 } catch (Exception e) {
72 LOG.error("Exception while close the Connection: " + e);
73 }
74 }
75 }
76
77
78
79
80
81
82 public void insertOrUpdateTipps(List<OleGokbTipp> oleGokbTipps) {
83 try {
84 if (connection == null || connection.isClosed()) {
85 connection = getConnection();
86 }
87 PreparedStatement pstmt = connection.prepareStatement(INSERT_TIPP_PREPARED_STMT);
88 for (OleGokbTipp oleGokbTipp : oleGokbTipps) {
89 buildTipp(pstmt, oleGokbTipp);
90 try {
91 pstmt.execute();
92 } catch (SQLException ex) {
93 if (!ex.getMessage().isEmpty() && ex.getMessage().contains("Duplicate entry")) {
94 updateTipp(oleGokbTipp);
95 }
96 }
97 }
98 connection.commit();
99 pstmt.close();
100 } catch (SQLException ex) {
101 LOG.error("Exception while Inserting the Tipp: " + ex);
102 } finally {
103 try {
104 closeConnections();
105 } catch (Exception e) {
106 LOG.error("Exception while close the Connection: " + e);
107
108 }
109 }
110 }
111
112
113
114
115
116
117 public void insertOrUpdateTitles(List<OleGokbTitle> oleGokbTitles) {
118 try {
119 if (connection == null || connection.isClosed()) {
120 connection = getConnection();
121 }
122 PreparedStatement pstmt = connection.prepareStatement(INSERT_TITLE_PREPARED_STMT);
123 for (OleGokbTitle oleGokbTitle : oleGokbTitles) {
124 buildTitle(pstmt, oleGokbTitle);
125 try {
126 pstmt.execute();
127 } catch (SQLException ex) {
128 if (!ex.getMessage().isEmpty() && ex.getMessage().contains("Duplicate entry")) {
129 updateTitle(oleGokbTitle);
130 }
131 }
132 }
133 connection.commit();
134 pstmt.close();
135 } catch (SQLException ex) {
136 LOG.error("Exception while Inserting the Title: " + ex);
137 } finally {
138 try {
139 closeConnections();
140 } catch (Exception e) {
141 LOG.error("Exception while close the Connection: " + e);
142 }
143 }
144 }
145
146
147
148
149
150
151
152 public void insertOrUpdatePlatforms(List<OleGokbPlatform> oleGokbPlatforms) {
153 try {
154 if (connection == null || connection.isClosed()) {
155 connection = getConnection();
156 }
157 PreparedStatement pstmt = connection.prepareStatement(INSERT_PLATFORM_PREPARED_STMT);
158 for (OleGokbPlatform oleGokbPlatform : oleGokbPlatforms) {
159 buildPlatform(pstmt, oleGokbPlatform);
160 try {
161 pstmt.execute();
162 } catch (SQLException ex) {
163 if (!ex.getMessage().isEmpty() && ex.getMessage().contains("Duplicate entry")) {
164 updatePlatform(oleGokbPlatform);
165 }
166 }
167 }
168 connection.commit();
169 pstmt.close();
170 } catch (SQLException ex) {
171 LOG.error("Exception while Inserting the Platform: " + ex);
172 } finally {
173 try {
174 closeConnections();
175 } catch (Exception e) {
176 LOG.error("Exception while close the Connection: " + e);
177 }
178 }
179 }
180
181
182
183
184
185
186 public void insertOrUpdateOrganizations(List<OleGokbOrganization> oleGokbOrganizations) {
187 try {
188 if (connection == null || connection.isClosed()) {
189 connection = getConnection();
190 }
191 PreparedStatement pstmt = connection.prepareStatement(INSERT_ORG_PREPARED_STMT);
192 for (OleGokbOrganization oleGokbOrganization : oleGokbOrganizations) {
193 buildOrganization(pstmt, oleGokbOrganization);
194 try {
195 pstmt.execute();
196 } catch (SQLException ex) {
197 if (!ex.getMessage().isEmpty() && ex.getMessage().contains("Duplicate entry")) {
198 updateOrganization(oleGokbOrganization);
199 }
200 }
201 }
202 connection.commit();
203 pstmt.close();
204 } catch (SQLException ex) {
205 LOG.error("Exception while Inserting the Organization: " + ex);
206 } finally {
207 try {
208 closeConnections();
209 } catch (Exception e) {
210 LOG.error("Exception while close the Connection: " + e);
211 }
212 }
213 }
214
215
216
217
218
219
220
221 public void insertOrUpdateOrganizationRoles(List<OleGokbOrganizationRole> oleGokbOrganizationRoles) {
222 Integer lastInsertedId = 0;
223 boolean empty = true;
224 try {
225 if (connection == null || connection.isClosed()) {
226 connection = getConnection();
227 }
228 Statement stmt = connection.createStatement();
229 PreparedStatement pstmt = connection.prepareStatement(INSERT_ORG_ROLE_PREPARED_STMT);
230
231 ResultSet rs1 = stmt.executeQuery("SELECT GOKB_ORG_ROLE_ID FROM OLE_GOKB_ORG_ROLE_T ORDER BY GOKB_ORG_ROLE_ID DESC");
232 while (rs1.next()) {
233 lastInsertedId = rs1.getInt(1);
234 break;
235 }
236
237
238 for (OleGokbOrganizationRole oleGokbOrganizationRole : oleGokbOrganizationRoles) {
239 ResultSet rs = stmt.executeQuery("SELECT * FROM OLE_GOKB_ORG_ROLE_T WHERE GOKB_ORG_ID=" + oleGokbOrganizationRole.getGokbOrganizationId() + " AND ROLE='" + oleGokbOrganizationRole.getRole() + "'");
240 while (!rs.next()) {
241 lastInsertedId++;
242 oleGokbOrganizationRole.setGokbOrgRoleId(lastInsertedId);
243 buildOrganizationRole(pstmt, oleGokbOrganizationRole);
244 pstmt.execute();
245 break;
246 }
247 }
248 connection.commit();
249 pstmt.close();
250 stmt.close();
251 } catch (SQLException ex) {
252 LOG.error("Exception while Inserting the Organization Role: " + ex);
253 } finally {
254 try {
255 closeConnections();
256 } catch (Exception e) {
257 LOG.error("Exception while close the Connection: " + e);
258 }
259 }
260 }
261
262
263
264
265
266
267
268 public void updatePackage(OleGokbPackage oleGokbPack) {
269 try {
270 if (connection == null || connection.isClosed()) {
271 connection = getConnection();
272 }
273 PreparedStatement pstmt = connection.prepareStatement(UPDATE_PACKAGE_PREPARED_STMT);
274 pstmt.setString(1, oleGokbPack.getPackageName());
275 pstmt.setString(2, oleGokbPack.getVariantName());
276 pstmt.setString(3, oleGokbPack.getStatus());
277 pstmt.setString(4, oleGokbPack.getPackageScope());
278 pstmt.setString(5, oleGokbPack.getBreakable());
279 pstmt.setString(6, oleGokbPack.getFixed());
280 pstmt.setString(7, oleGokbPack.getAvailability());
281 pstmt.setTimestamp(8, oleGokbPack.getDateCreated());
282 pstmt.setTimestamp(9, oleGokbPack.getDateUpdated());
283 pstmt.setInt(10, oleGokbPack.getGokbPackageId());
284 pstmt.execute();
285 connection.commit();
286 pstmt.close();
287
288 } catch (SQLException ex) {
289 LOG.error("Exception while Update the Package: " + ex);
290 }
291
292 }
293
294
295
296
297
298
299 public void updateTipp(OleGokbTipp oleGokbTipp) {
300 try {
301 if (connection == null || connection.isClosed()) {
302 connection = getConnection();
303 }
304 PreparedStatement pstmt = connection.prepareStatement(UPDATE_TIPP_PREPARED_STMT);
305 pstmt.setInt(1, oleGokbTipp.getGokbPackageId());
306 pstmt.setInt(2, oleGokbTipp.getGokbTitleId());
307 pstmt.setInt(3, oleGokbTipp.getGokbPlatformId());
308 pstmt.setString(4, oleGokbTipp.getStatus());
309 pstmt.setString(5, oleGokbTipp.getStatusReason());
310 pstmt.setTimestamp(6, oleGokbTipp.getStartdate());
311 pstmt.setString(7, oleGokbTipp.getStartVolume());
312 pstmt.setString(8, oleGokbTipp.getStartIssue());
313 pstmt.setTimestamp(9, oleGokbTipp.getEndDate());
314 pstmt.setString(10, oleGokbTipp.getEndVolume());
315 pstmt.setString(11, oleGokbTipp.getEndIssue());
316 pstmt.setString(12, oleGokbTipp.getEmbarco());
317 pstmt.setString(13, oleGokbTipp.getPlatformHostUrl());
318 pstmt.setTimestamp(14, oleGokbTipp.getDateCreated());
319 pstmt.setTimestamp(15, oleGokbTipp.getDateUpdated());
320 pstmt.setInt(16, oleGokbTipp.getGokbTippId());
321 pstmt.execute();
322 connection.commit();
323 pstmt.close();
324 } catch (SQLException ex) {
325 LOG.error("Exception while Update the Tipp: " + ex);
326 }
327 }
328
329
330
331
332
333
334 public void updateTitle(OleGokbTitle oleGokbTitle) {
335 try {
336 if (connection == null || connection.isClosed()) {
337 connection = getConnection();
338 }
339 PreparedStatement pstmt = connection.prepareStatement(UPDATE_TITLE_PREPARED_STMT);
340 pstmt.setString(1, oleGokbTitle.getTitleName());
341 pstmt.setString(2, oleGokbTitle.getVariantName());
342 pstmt.setString(3, oleGokbTitle.getMedium());
343 pstmt.setString(4, oleGokbTitle.getPureQa());
344 pstmt.setString(5, oleGokbTitle.getIssnOnline());
345 pstmt.setString(6, oleGokbTitle.getIssnPrint());
346 pstmt.setString(7, oleGokbTitle.getIssnL());
347 pstmt.setInt(8, oleGokbTitle.getOclcNumber());
348 pstmt.setString(9, oleGokbTitle.getDoi());
349 pstmt.setInt(10, oleGokbTitle.getProprietaryId());
350 pstmt.setString(11, oleGokbTitle.getSuncat());
351 pstmt.setString(12, oleGokbTitle.getLccn());
352 pstmt.setInt(13, oleGokbTitle.getPublisherId());
353 pstmt.setInt(14, oleGokbTitle.getImprint());
354 pstmt.setTimestamp(15, oleGokbTitle.getDateCreated());
355 pstmt.setTimestamp(16, oleGokbTitle.getDateUpdated());
356 pstmt.setInt(17, oleGokbTitle.getGokbTitleId());
357 pstmt.execute();
358 connection.commit();
359 pstmt.close();
360 } catch (SQLException ex) {
361 LOG.error("Exception while Update the Title: " + ex);
362 }
363 }
364
365
366
367
368
369
370 public void updatePlatform(OleGokbPlatform oleGokbPlatform) {
371 try {
372 if (connection == null || connection.isClosed()) {
373 connection = getConnection();
374 }
375 PreparedStatement pstmt = connection.prepareStatement(UPDATE_PLATFORM_PREPARED_STMT);
376 pstmt.setString(1, oleGokbPlatform.getPlatformName());
377 pstmt.setString(2, oleGokbPlatform.getStatus());
378 pstmt.setInt(3, oleGokbPlatform.getPlatformProviderId());
379 pstmt.setString(4, oleGokbPlatform.getAuthentication());
380 pstmt.setString(5, oleGokbPlatform.getSoftwarePlatform());
381 pstmt.setTimestamp(6, oleGokbPlatform.getDateCreated());
382 pstmt.setTimestamp(7, oleGokbPlatform.getDateUpdated());
383 pstmt.setInt(8, oleGokbPlatform.getGokbPlatformId());
384 pstmt.execute();
385 connection.commit();
386 pstmt.close();
387 } catch (SQLException ex) {
388 LOG.error("Exception while Update the Platform: " + ex);
389 }
390 }
391
392
393
394
395
396
397 public void updateOrganization(OleGokbOrganization oleGokbOrganization) {
398 try {
399 if (connection == null || connection.isClosed()) {
400 connection = getConnection();
401 }
402 PreparedStatement pstmt = connection.prepareStatement(UPDATE_ORG_PREPARED_STMT);
403 pstmt.setString(1, oleGokbOrganization.getOrganizationName());
404 pstmt.setString(2, oleGokbOrganization.getVariantName());
405 pstmt.setTimestamp(3, oleGokbOrganization.getDateCreated());
406 pstmt.setTimestamp(4, oleGokbOrganization.getDateUpdated());
407 pstmt.setInt(5, oleGokbOrganization.getGokbOrganizationId());
408 pstmt.execute();
409 connection.commit();
410 pstmt.close();
411 } catch (SQLException ex) {
412 LOG.error("Exception while Update the Organization: " + ex);
413 }
414 }
415
416
417
418
419
420
421
422 public void updateOrganizationRole(OleGokbOrganizationRole oleGokbOrganizationRole) {
423 try {
424 if (connection == null || connection.isClosed()) {
425 connection = getConnection();
426 }
427 PreparedStatement pstmt = connection.prepareStatement(UPDATE_ORG_ROLE_PREPARED_STMT);
428 pstmt.setInt(1, oleGokbOrganizationRole.getGokbOrganizationId());
429 pstmt.setString(2, oleGokbOrganizationRole.getRole());
430 pstmt.setInt(3, oleGokbOrganizationRole.getGokbOrgRoleId());
431 pstmt.execute();
432 connection.commit();
433 pstmt.close();
434 } catch (SQLException ex) {
435 LOG.error("Exception while Update the Organization Role: " + ex);
436 }
437 }
438
439
440
441
442
443 public void truncateTables() {
444 try {
445 if (connection == null || connection.isClosed()) {
446 connection = getConnection();
447 }
448 Statement stmt = connection.createStatement();
449 stmt.executeUpdate("TRUNCATE TABLE OLE_GOKB_PKG_T");
450 stmt.executeUpdate("TRUNCATE TABLE OLE_GOKB_TIPP_T");
451 stmt.executeUpdate("TRUNCATE TABLE OLE_GOKB_TITLE_T");
452 stmt.executeUpdate("TRUNCATE TABLE OLE_GOKB_PLTFRM_T");
453 stmt.executeUpdate("TRUNCATE TABLE OLE_GOKB_ORG_T");
454 stmt.executeUpdate("TRUNCATE TABLE OLE_GOKB_ORG_ROLE_T");
455 connection.commit();
456 stmt.close();
457 } catch (SQLException ex) {
458 LOG.error("Exception while Truncate the Tables: " + ex);
459 } finally {
460 try {
461 closeConnections();
462 } catch (Exception e) {
463 LOG.error("Exception while close the Connection: " + e);
464 }
465 }
466 }
467
468
469
470
471
472
473 public Timestamp getUpdatedDate() {
474 Timestamp startTime = null;
475 try {
476 if (connection == null || connection.isClosed()) {
477 connection = getConnection();
478 }
479
480 Statement selectStmt = connection.createStatement();
481 ResultSet rs = null;
482 rs = selectStmt.executeQuery("SELECT START_TIME FROM OLE_GOKB_UPDATE_LOG_T ORDER BY ID DESC");
483 while (rs.next()) {
484 startTime = rs.getTimestamp(1);
485 break;
486 }
487 connection.commit();
488 selectStmt.close();
489 rs.close();
490 } catch (SQLException ex) {
491 LOG.error("Exception while getting Last Update date: " + ex);
492 } finally {
493 try {
494 closeConnections();
495 } catch (Exception e) {
496 LOG.error("Exception while close the Connection: " + e);
497 }
498 }
499 return startTime;
500 }
501
502
503
504
505
506
507 public int insertStatus() {
508 Integer lastInsertedId = 0;
509 OleGokbUpdateLog oleGokbUpdateLog = new OleGokbUpdateLog();
510
511 try {
512 if (connection == null || connection.isClosed()) {
513 connection = getConnection();
514 }
515 Date localTime = new Date();
516 DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
517 Statement stmt = connection.createStatement();
518 ResultSet rs = null;
519 rs = stmt.executeQuery("SELECT ID FROM OLE_GOKB_UPDATE_LOG_T ORDER BY ID DESC");
520 while (rs.next()) {
521 lastInsertedId = rs.getInt(1);
522 break;
523 }
524 lastInsertedId = lastInsertedId + 1;
525 String sqlStmt = "INSERT INTO OLE_GOKB_UPDATE_LOG_T (ID,START_TIME, STATUS) VALUES(" + lastInsertedId + "," + getDateStringForOracle(df.format(localTime).toString()) + ", 'Running')";
526 stmt.execute(sqlStmt);
527 connection.commit();
528 stmt.close();
529 rs.close();
530 oleGokbUpdateLog.setId(lastInsertedId);
531 } catch (SQLException ex) {
532 LOG.error("Exception while getting Last Inserted Id from Log table: " + ex);
533 } finally {
534 try {
535 closeConnections();
536 } catch (Exception e) {
537 LOG.error("Exception while close the Connection: " + e);
538 }
539 }
540 return Integer.parseInt(lastInsertedId.toString());
541 }
542
543 public void insertLogEndTime(int updatedId) {
544 try {
545 if (connection == null || connection.isClosed()) {
546 connection = getConnection();
547 }
548 Date localTime = new Date();
549 DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
550 Statement stmt = connection.createStatement();
551 String sqlStmt = "UPDATE OLE_GOKB_UPDATE_LOG_T SET STATUS='Completed', END_TIME=" + getDateStringForOracle(df.format(localTime).toString()) + " where ID=" + updatedId;
552 stmt.execute(sqlStmt);
553 connection.commit();
554 stmt.close();
555 } catch (SQLException ex) {
556 LOG.error("Exception while Insert End time in Log table: " + ex);
557 } finally {
558 try {
559 closeConnections();
560 } catch (Exception e) {
561 LOG.error("Exception while close the Connection: " + e);
562 }
563 }
564 }
565
566
567
568
569
570
571
572 public void updateStatus(int id, String columnValue) {
573 try {
574 if (connection == null || connection.isClosed()) {
575 connection = getConnection();
576 }
577 String sqlStmt = "UPDATE OLE_GOKB_UPDATE_LOG_T SET " + columnValue + " WHERE ID=" + id;
578 Statement stmt = connection.createStatement();
579 stmt.execute(sqlStmt);
580 connection.commit();
581 stmt.close();
582 } catch (SQLException ex) {
583 LOG.error("Exception while Update the the Status: " + ex);
584 } finally {
585 try {
586 closeConnections();
587 } catch (Exception e) {
588 LOG.error("Exception while close the Connection: " + e);
589 }
590 }
591 }
592
593
594
595
596
597
598
599 public void insertPackages(List<OleGokbPackage> oleGokbPackages) {
600 try {
601 if (connection == null || connection.isClosed()) {
602 connection = getConnection();
603 }
604 PreparedStatement pstmt = connection.prepareStatement(INSERT_PACKAGE_PREPARED_STMT);
605 for (OleGokbPackage oleGokbPack : oleGokbPackages) {
606 buildPackage(pstmt, oleGokbPack);
607 pstmt.addBatch();
608 }
609 pstmt.executeBatch();
610 connection.commit();
611 pstmt.close();
612 } catch (SQLException ex) {
613 LOG.error("Exception while Insert the Package: " + ex);
614 } finally {
615 try {
616 closeConnections();
617 } catch (Exception e) {
618 LOG.error("Exception while close the Connection: " + e);
619 }
620 }
621 }
622
623
624
625
626
627 public void insertTipps(List<OleGokbTipp> oleGokbTipps) {
628 try {
629 if (connection == null || connection.isClosed()) {
630 connection = getConnection();
631 }
632 PreparedStatement pstmt = connection.prepareStatement(INSERT_TIPP_PREPARED_STMT);
633 for (OleGokbTipp oleGokbTipp : oleGokbTipps) {
634 buildTipp(pstmt, oleGokbTipp);
635 pstmt.addBatch();
636 }
637 pstmt.executeBatch();
638 connection.commit();
639 pstmt.close();
640 } catch (SQLException ex) {
641 LOG.error("Exception while Insert the Tipp: " + ex);
642 } finally {
643 try {
644 closeConnections();
645 } catch (Exception e) {
646 LOG.error("Exception while close the Connection: " + e);
647 }
648 }
649 }
650
651
652
653
654
655
656
657 public void insertTitles(List<OleGokbTitle> oleGokbTitles) {
658 try {
659 if (connection == null || connection.isClosed()) {
660 connection = getConnection();
661 }
662 PreparedStatement pstmt = connection.prepareStatement(INSERT_TITLE_PREPARED_STMT);
663 for (OleGokbTitle oleGokbTitle : oleGokbTitles) {
664 buildTitle(pstmt, oleGokbTitle);
665 pstmt.addBatch();
666 }
667 pstmt.executeBatch();
668 connection.commit();
669 pstmt.close();
670 } catch (SQLException ex) {
671 LOG.error("Exception while Insert the Title: " + ex);
672 } finally {
673 try {
674 closeConnections();
675 } catch (Exception e) {
676 LOG.error("Exception while close the Connection: " + e);
677 }
678 }
679 }
680
681
682
683
684
685
686 public void insertPlatforms(List<OleGokbPlatform> oleGokbPlatforms) {
687 try {
688 if (connection == null || connection.isClosed()) {
689 connection = getConnection();
690 }
691 PreparedStatement pstmt = connection.prepareStatement(INSERT_PLATFORM_PREPARED_STMT);
692 for (OleGokbPlatform oleGokbPlatform : oleGokbPlatforms) {
693 buildPlatform(pstmt, oleGokbPlatform);
694 pstmt.addBatch();
695 }
696 pstmt.executeBatch();
697 connection.commit();
698 pstmt.close();
699 } catch (SQLException ex) {
700 LOG.error("Exception while Insert the Platform: " + ex);
701 } finally {
702 try {
703 closeConnections();
704 } catch (Exception e) {
705 LOG.error("Exception while close the Connection: " + e);
706 }
707 }
708 }
709
710
711
712
713
714
715
716 public void insertOrganizations(List<OleGokbOrganization> oleGokbOrganizations) {
717 try {
718
719 if (connection == null || connection.isClosed()) {
720 connection = getConnection();
721 }
722
723 PreparedStatement pstmt = connection.prepareStatement(INSERT_ORG_PREPARED_STMT);
724 for (OleGokbOrganization oleGokbOrganization : oleGokbOrganizations) {
725 buildOrganization(pstmt, oleGokbOrganization);
726 pstmt.addBatch();
727 }
728 pstmt.executeBatch();
729 connection.commit();
730 pstmt.close();
731 } catch (SQLException ex) {
732 LOG.error("Exception while Insert the Organization: " + ex);
733 } finally {
734 try {
735 closeConnections();
736 } catch (Exception e) {
737 LOG.error("Exception while close the Connection: " + e);
738 }
739 }
740 }
741
742
743
744
745
746
747 public void insertOrganizationRoles(List<OleGokbOrganizationRole> oleGokbOrganizationRoles) {
748 try {
749 Integer lastInsertedId = 0;
750 if (connection == null || connection.isClosed()) {
751 connection = getConnection();
752 }
753 Statement stmt = connection.createStatement();
754 ResultSet rs = null;
755 rs = stmt.executeQuery("SELECT ID FROM OLE_GOKB_ORG_ROLE_T ORDER BY ID DESC");
756 while (rs.next()) {
757 lastInsertedId = rs.getInt(1);
758 break;
759 }
760 PreparedStatement pstmt = connection.prepareStatement(INSERT_ORG_ROLE_PREPARED_STMT);
761 for (OleGokbOrganizationRole oleGokbOrganizationRole : oleGokbOrganizationRoles) {
762 oleGokbOrganizationRole.setGokbOrgRoleId(lastInsertedId + 1);
763 buildOrganizationRole(pstmt, oleGokbOrganizationRole);
764 pstmt.addBatch();
765 }
766 pstmt.executeBatch();
767 connection.commit();
768 pstmt.close();
769 stmt.close();
770 rs.close();
771
772 } catch (SQLException ex) {
773 LOG.error("Exception while Insert the Organization Role: " + ex);
774 } finally {
775 try {
776 closeConnections();
777 } catch (Exception e) {
778 LOG.error("Exception while close the Connection: " + e);
779 }
780 }
781 }
782
783
784
785
786
787
788 private void buildPackage(PreparedStatement pstmt, OleGokbPackage oleGokbPack) {
789 try {
790 pstmt.setInt(1, oleGokbPack.getGokbPackageId());
791 pstmt.setString(2, oleGokbPack.getPackageName());
792 pstmt.setString(3, oleGokbPack.getVariantName());
793 pstmt.setString(4, oleGokbPack.getStatus());
794 pstmt.setString(5, oleGokbPack.getPackageScope());
795 pstmt.setString(6, oleGokbPack.getBreakable());
796 pstmt.setString(7, oleGokbPack.getFixed());
797 pstmt.setString(8, oleGokbPack.getAvailability());
798 pstmt.setTimestamp(9, oleGokbPack.getDateCreated());
799 pstmt.setTimestamp(10, oleGokbPack.getDateUpdated());
800 } catch (SQLException ex) {
801 LOG.error("Exception while build the Package: " + ex);
802 }
803 }
804
805
806
807
808
809
810 private void buildTipp(PreparedStatement pstmt, OleGokbTipp oleGokbTipp) {
811 try {
812
813 Integer gokbTippId=0;
814 Integer gokbPackageId=0;
815 Integer gokbTitleId=0;
816 Integer gokbPlatformId=0;
817
818
819 if(oleGokbTipp.getGokbTippId() !=null){
820 gokbTippId =oleGokbTipp.getGokbTippId();
821 }
822 if(oleGokbTipp.getGokbPackageId() !=null){
823 gokbPackageId =oleGokbTipp.getGokbPackageId();
824 }
825 if(oleGokbTipp.getGokbTitleId() !=null){
826 gokbTitleId =oleGokbTipp.getGokbTitleId();
827 }
828 if(oleGokbTipp.getGokbPlatformId() !=null){
829 gokbPlatformId =oleGokbTipp.getGokbPlatformId();
830 }
831
832
833 pstmt.setInt(1, gokbTippId);
834 pstmt.setInt(2, gokbPackageId);
835 pstmt.setInt(3, gokbTitleId);
836 pstmt.setInt(4, gokbPlatformId);
837 pstmt.setString(5, oleGokbTipp.getStatus());
838 pstmt.setString(6, oleGokbTipp.getStatusReason());
839 pstmt.setTimestamp(7, oleGokbTipp.getStartdate());
840 pstmt.setString(8, oleGokbTipp.getStartVolume());
841 pstmt.setString(9, oleGokbTipp.getStartIssue());
842 pstmt.setTimestamp(10, oleGokbTipp.getEndDate());
843 pstmt.setString(11, oleGokbTipp.getEndVolume());
844 pstmt.setString(12, oleGokbTipp.getEndIssue());
845 pstmt.setString(13, oleGokbTipp.getEmbarco());
846 pstmt.setString(14, oleGokbTipp.getPlatformHostUrl());
847 pstmt.setTimestamp(15, oleGokbTipp.getDateCreated());
848 pstmt.setTimestamp(16, oleGokbTipp.getDateUpdated());
849 } catch (SQLException ex) {
850 LOG.error("Exception while build the Tipp: " + ex);
851 }
852 }
853
854
855
856
857
858
859 private void buildTitle(PreparedStatement pstmt, OleGokbTitle oleGokbTitle) {
860 try {
861 pstmt.setInt(1, oleGokbTitle.getGokbTitleId());
862 pstmt.setString(2, oleGokbTitle.getTitleName());
863 pstmt.setString(3, oleGokbTitle.getVariantName());
864 pstmt.setString(4, oleGokbTitle.getMedium());
865 pstmt.setString(5, oleGokbTitle.getPureQa());
866 pstmt.setString(6, oleGokbTitle.getIssnOnline());
867 pstmt.setString(7, oleGokbTitle.getIssnPrint());
868 pstmt.setString(8, oleGokbTitle.getIssnL());
869 pstmt.setInt(9, oleGokbTitle.getOclcNumber());
870 pstmt.setString(10, oleGokbTitle.getDoi());
871 pstmt.setInt(11, oleGokbTitle.getProprietaryId());
872 pstmt.setString(12, oleGokbTitle.getSuncat());
873 pstmt.setString(13, oleGokbTitle.getLccn());
874 pstmt.setInt(14, oleGokbTitle.getPublisherId());
875 pstmt.setInt(15, oleGokbTitle.getImprint());
876 pstmt.setTimestamp(16, oleGokbTitle.getDateUpdated());
877 pstmt.setTimestamp(17, oleGokbTitle.getDateUpdated());
878 } catch (SQLException ex) {
879 LOG.error("Exception while build the Title: " + ex);
880 }
881 }
882
883
884
885
886
887
888 private void buildPlatform(PreparedStatement pstmt, OleGokbPlatform oleGokbPlatform) {
889 try {
890 pstmt.setInt(1, oleGokbPlatform.getGokbPlatformId());
891 pstmt.setString(2, oleGokbPlatform.getPlatformName());
892 pstmt.setString(3, oleGokbPlatform.getStatus());
893 pstmt.setInt(4, oleGokbPlatform.getPlatformProviderId());
894 pstmt.setString(5, oleGokbPlatform.getAuthentication());
895 pstmt.setString(6, oleGokbPlatform.getSoftwarePlatform());
896 pstmt.setTimestamp(7, oleGokbPlatform.getDateCreated());
897 pstmt.setTimestamp(8, oleGokbPlatform.getDateUpdated());
898 } catch (SQLException ex) {
899 LOG.error("Exception while build the Platform: " + ex);
900 }
901 }
902
903
904
905
906
907
908 private void buildOrganization(PreparedStatement pstmt, OleGokbOrganization oleGokbOrganization) {
909 try {
910 pstmt.setInt(1, oleGokbOrganization.getGokbOrganizationId());
911 pstmt.setString(2, oleGokbOrganization.getOrganizationName());
912 pstmt.setString(3, oleGokbOrganization.getVariantName());
913 pstmt.setTimestamp(4, oleGokbOrganization.getDateCreated());
914 pstmt.setTimestamp(5, oleGokbOrganization.getDateUpdated());
915 } catch (SQLException ex) {
916 LOG.error("Exception while build the Organization: " + ex);
917 }
918 }
919
920
921
922
923
924
925 private void buildOrganizationRole(PreparedStatement pstmt, OleGokbOrganizationRole oleGokbOrganizationRole) {
926 try {
927 pstmt.setInt(1, oleGokbOrganizationRole.getGokbOrgRoleId());
928 pstmt.setInt(2, oleGokbOrganizationRole.getGokbOrganizationId());
929 pstmt.setString(3, oleGokbOrganizationRole.getRole());
930 } catch (SQLException ex) {
931 LOG.error("Exception while build the Organization Role: " + ex);
932 }
933 }
934
935
936
937
938
939
940
941 public void closeConnections() throws SQLException {
942 if (connection != null) {
943 connection.close();
944 }
945 }
946
947
948 private String getDateStringForOracle(String updateDate) {
949 if (dbVendor.equalsIgnoreCase("oracle")) {
950 updateDate = "TO_DATE('" + updateDate + "', 'yyyy-mm-dd hh24:mi:ss')";
951 }else{
952 updateDate="'"+updateDate+"'";
953 }
954 return updateDate;
955 }
956 }