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