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;
  }