public void closeConnections() throws SQLException { if (itemPreparedStatement != null) { itemPreparedStatement.close(); } if (itemConnection != null) { itemConnection.close(); } if (holdingsPreparedStatement != null) { holdingsPreparedStatement.close(); } if (holdingsConnection != null) { holdingsConnection.close(); } if (bibResultSet != null) { bibResultSet.close(); } if (bibStatement != null) { bibStatement.close(); } if (bibConnection != null) { bibConnection.close(); } if (connection != null) { connection.close(); } }
private void fetchAuthenticationType() throws SQLException { Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery("SELECT CODE,NAME from OLE_DS_AUTHENTICATION_TYPE_T"); while (resultSet.next()) { authenticationType.put(resultSet.getString("CODE"), resultSet.getString("NAME")); } resultSet.close(); }
public void init(int startIndex, int endIndex, String updateDate) throws SQLException { if (connection == null || connection.isClosed()) { connection = getConnection(); } if (startIndex != 0 && endIndex != 0) { bibQuery = "SELECT * FROM OLE_DS_BIB_T WHERE BIB_ID BETWEEN " + startIndex + " AND " + endIndex + " ORDER BY BIB_ID"; } else if (StringUtils.isNotEmpty(updateDate)) { updateDate = getDateStringForOracle(updateDate); bibQuery = "SELECT * FROM OLE_DS_BIB_T where DATE_UPDATED > '" + updateDate + "'"; } else { bibQuery = "SELECT * FROM OLE_DS_BIB_T ORDER BY BIB_ID"; } if (!isStaffOnly) { bibQuery = bibStaffOnly; holdingsQuery = holdingsQuery + staffOnlyHoldings; itemQuery = itemQuery + staffOnlyItem; } fetchCallNumberType(); fetchReceiptStatus(); fetchAuthenticationType(); fetchItemType(); fetchItemStatus(); fetchStatisticalSearchCode(); fetchExtentOfOwnershipType(); bibConnection = getConnection(); holdingsConnection = getConnection(); itemConnection = getConnection(); bibConnection.setAutoCommit(false); bibStatement = bibConnection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); if (dbVendor.equalsIgnoreCase("oracle")) { bibStatement.setFetchSize(1); } else if (dbVendor.equalsIgnoreCase("mysql")) { bibStatement.setFetchSize(Integer.MIN_VALUE); } bibResultSet = bibStatement.executeQuery(bibQuery); holdingsPreparedStatement = holdingsConnection.prepareStatement(holdingsQuery); itemPreparedStatement = itemConnection.prepareStatement(itemQuery); String insertQuery = "INSERT INTO OLE_DS_BIB_INFO_T(BIB_ID, BIB_ID_STR, TITLE, AUTHOR, PUBLISHER, ISXN) VALUES (?,?,?,?,?,?)"; bibInsertPreparedStatement = connection.prepareStatement(insertQuery); String updateQuery = "UPDATE OLE_DS_BIB_INFO_T SET TITLE=?, AUTHOR=?, PUBLISHER=?, ISXN=?, BIB_ID_STR=? WHERE BIB_ID=?"; bibUpdatePreparedStatement = connection.prepareStatement(updateQuery); }
public String getTotalNoOfRecords() throws SQLException { String totalRecords = "0"; Connection connection = getConnection(); PreparedStatement preparedStatement = null; if (isStaffOnly) { preparedStatement = connection.prepareStatement(bibCountQuery); } else { preparedStatement = connection.prepareStatement(bibCountQuery + staffOnly); } ResultSet resultSet = preparedStatement.executeQuery(); while (resultSet.next()) { totalRecords = resultSet.getString("totalRecords"); } preparedStatement.close(); resultSet.close(); connection.close(); return totalRecords; }
private void fetchReceiptStatus() throws SQLException { Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery("SELECT RCPT_STAT_CD,RCPT_STAT_NM from OLE_CAT_RCPT_STAT_T"); while (resultSet.next()) { receiptStatus.put(resultSet.getString("RCPT_STAT_CD"), resultSet.getString("RCPT_STAT_NM")); } resultSet.close(); }
private void fetchStatisticalSearchCode() throws SQLException { Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery( "SELECT STAT_SRCH_CD_ID, STAT_SRCH_CD, STAT_SRCH_NM from OLE_CAT_STAT_SRCH_CD_T"); while (resultSet.next()) { statisticalSearchCodeMap.put( resultSet.getString("STAT_SRCH_CD_ID"), resultSet.getString("STAT_SRCH_CD") + "|" + resultSet.getString("STAT_SRCH_NM")); } resultSet.close(); }
private void fetchItemType() throws SQLException { Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery("SELECT ITM_TYP_CD_ID,ITM_TYP_CD,ITM_TYP_NM from OLE_CAT_ITM_TYP_T"); while (resultSet.next()) { itemTypeMap.put( resultSet.getString("ITM_TYP_CD_ID"), resultSet.getString("ITM_TYP_CD") + "|" + resultSet.getString("ITM_TYP_NM")); } resultSet.close(); }
private void fetchCallNumberType() throws SQLException { Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery( "SELECT SHVLG_SCHM_ID,SHVLG_SCHM_CD,SHVLG_SCHM_NM from OLE_CAT_SHVLG_SCHM_T"); while (resultSet.next()) { callNumberType.put( resultSet.getString("SHVLG_SCHM_ID"), resultSet.getString("SHVLG_SCHM_CD") + "|" + resultSet.getString("SHVLG_SCHM_NM")); } resultSet.close(); }
private void fetchExtentOfOwnershipType() throws SQLException { Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery( "SELECT TYPE_OWNERSHIP_ID, TYPE_OWNERSHIP_CD,TYPE_OWNERSHIP_NM from OLE_CAT_TYPE_OWNERSHIP_T"); while (resultSet.next()) { extentOfOwnershipTypeMap.put( resultSet.getString("TYPE_OWNERSHIP_ID"), resultSet.getString("TYPE_OWNERSHIP_CD") + "|" + resultSet.getString("TYPE_OWNERSHIP_NM")); } resultSet.close(); }
private void fetchItemStatus() throws SQLException { Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery( "SELECT ITEM_AVAIL_STAT_ID,ITEM_AVAIL_STAT_CD,ITEM_AVAIL_STAT_NM from OLE_DLVR_ITEM_AVAIL_STAT_T"); while (resultSet.next()) { itemStatusMap.put( resultSet.getString("ITEM_AVAIL_STAT_ID"), resultSet.getString("ITEM_AVAIL_STAT_CD") + "|" + resultSet.getString("ITEM_AVAIL_STAT_NM")); } resultSet.close(); }
public void fetchBibHoldings() throws SQLException { Statement statement = connection.createStatement(); bibHoldingsResultSet = statement.executeQuery("SELECT * FROM OLE_DS_BIB_HOLDINGS_T"); }
public void fetchHoldingItems() throws SQLException { Statement statement = connection.createStatement(); holdingItemResultSet = statement.executeQuery("SELECT * FROM OLE_DS_ITEM_HOLDINGS_T"); }
public int storeBibInfo( int batchSize, String filePath, String fileName, BibInfoStatistics bibInfoStatistics, int batchNo) throws SQLException { StopWatch stopWatch = new StopWatch(); stopWatch.start(); int count = 0; BibMarcUtil bibMarcUtil = new BibMarcUtil(); while (bibResultSet.next()) { count++; int bibId = 0; int bibId2 = 0; String bibIdStr = ""; try { bibId = bibResultSet.getInt("BIB_ID"); bibId2 = bibResultSet.getInt("BIB_ID"); bibIdStr = bibResultSet.getString("UNIQUE_ID_PREFIX") + "-" + bibId; if (bibId != bibId2) { LOG.error("bibId is not equal to bibId2: bibId = " + bibId + "; bibId2 = " + bibId2); } BibMarcRecords bibMarcRecords = bibMarcRecordProcessor.fromXML(bibResultSet.getString("CONTENT")); if (bibMarcRecords != null && bibMarcRecords.getRecords() != null && bibMarcRecords.getRecords().size() > 0) { Map<String, String> dataFields = bibMarcUtil.buildDataValuesForBibInfo(bibMarcRecords.getRecords().get(0)); String title = dataFields.get(BibMarcUtil.TITLE_DISPLAY); String author = dataFields.get(BibMarcUtil.AUTHOR_DISPLAY); String publisher = dataFields.get(BibMarcUtil.PUBLISHER_DISPLAY); String isbn = dataFields.get(BibMarcUtil.ISBN_DISPLAY); String issn = dataFields.get(BibMarcUtil.ISSN_DISPLAY); String commonIdentifier = ""; if (StringUtils.isNotEmpty(isbn)) { commonIdentifier = isbn; } else { commonIdentifier = issn; } bibInsertPreparedStatement.setInt(1, bibId); bibInsertPreparedStatement.setString(2, bibIdStr); bibInsertPreparedStatement.setString(3, truncateData(title, 4000)); bibInsertPreparedStatement.setString(4, truncateData(author, 4000)); bibInsertPreparedStatement.setString(5, truncateData(publisher, 4000)); bibInsertPreparedStatement.setString(6, truncateData(commonIdentifier, 100)); try { bibInsertPreparedStatement.executeUpdate(); } catch (Exception e) { if (e.getMessage().startsWith("Duplicate entry")) { bibUpdatePreparedStatement.setString(1, truncateData(title, 4000)); bibUpdatePreparedStatement.setString(2, truncateData(author, 4000)); bibUpdatePreparedStatement.setString(3, truncateData(publisher, 4000)); bibUpdatePreparedStatement.setString(4, truncateData(commonIdentifier, 100)); bibUpdatePreparedStatement.setString(5, bibIdStr); bibUpdatePreparedStatement.setInt(6, bibId); try { bibUpdatePreparedStatement.executeUpdate(); } catch (Exception e1) { LOG.error( "Exception while updating into BIB_INFO_T, BibId = " + bibId + " BibIdStr = " + bibIdStr + " : ", e1); writeStatusToFile( filePath, fileName, "Exception while updating into BIB_INFO_T, BibId = " + bibId + " BibIdStr = " + bibIdStr + " : " + e1.getMessage()); } } else { LOG.error( "Exception while inserting into BIB_INFO_T, BibId = " + bibId + " BibIdStr = " + bibIdStr + " : ", e); writeStatusToFile( filePath, fileName, "Exception while inserting into BIB_INFO_T, BibId = " + bibId + " BibIdStr = " + bibIdStr + " : " + e.getMessage()); } } } } catch (Exception e) { LOG.error( "Exception inserting/updating bibId " + bibId + "; bibId2 = " + bibId2 + " BibIdStr = " + bibIdStr, e); writeStatusToFile( filePath, fileName, "Exception inserting/updating bibId " + bibId + "; bibId2 = " + bibId2 + " BibIdStr = " + bibIdStr + "\t" + e.getMessage()); } bibInfoStatistics.setBibCount((batchSize * batchNo) + count); if (count == batchSize) { break; } } stopWatch.stop(); connection.commit(); return count; }