Java tutorial
/* * Copyright(C) 2014 * NEC Corporation All rights reserved. * * No permission to use, copy, modify and distribute this software * and its documentation for any purpose is granted. * This software is provided under applicable license agreement only. */ package com.nec.harvest.service.impl; import java.io.IOException; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.Date; import java.util.HashSet; import java.util.Iterator; import java.util.List; import java.util.Map; import java.util.Set; import org.apache.commons.lang.ArrayUtils; import org.apache.commons.lang.StringUtils; import org.hibernate.Criteria; import org.hibernate.HibernateException; import org.hibernate.Query; import org.hibernate.Session; import org.hibernate.Transaction; import org.hibernate.criterion.Criterion; import org.hibernate.criterion.Restrictions; import org.hibernate.exception.GenericJDBCException; import org.hibernate.exception.SQLGrammarException; import org.hibernate.transform.Transformers; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import com.hazelcast.core.Hazelcast; import com.hazelcast.core.HazelcastInstance; import com.hazelcast.core.HazelcastInstanceNotActiveException; import com.nec.core.exception.ObjectNotFoundException; import com.nec.crud.hibernate.HibernateSessionManager; import com.nec.harvest.bean.mapping.ActualViewBean; import com.nec.harvest.bean.mapping.PurchaseBean; import com.nec.harvest.bean.mapping.json.JSONActualViewBean; import com.nec.harvest.bean.mapping.json.JSONPurchase; import com.nec.harvest.constant.Constants; import com.nec.harvest.constant.SqlConstants; import com.nec.harvest.error.DefaultOutOfMemoryHandler; import com.nec.harvest.exception.ServiceException; import com.nec.harvest.helper.ProductHelper; import com.nec.harvest.model.Purchase; import com.nec.harvest.model.User; import com.nec.harvest.model.internal.Version; import com.nec.harvest.repository.PurchaseRepository; import com.nec.harvest.service.PurchaseService; import com.nec.harvest.userdetails.AuthenticatedUserDetails; import com.nec.harvest.util.DateFormatUtil; import com.nec.harvest.util.DateFormatUtil.DateFormat; /** * {@link PurchaseService} * * @author hungpd * */ public class PurchaseServiceImlp implements PurchaseService { private static final Logger logger = LoggerFactory.getLogger(PurchaseServiceImlp.class); private final PurchaseRepository repository; private final HazelcastInstance hzInstance; public PurchaseServiceImlp(PurchaseRepository repository, HazelcastInstance hzInstance) { this.repository = repository; this.hzInstance = hzInstance; // Handle out of memory Hazelcast.setOutOfMemoryHandler(new DefaultOutOfMemoryHandler()); } /** {@inheritDoc} */ @Override public PurchaseRepository getPurchaseRepository() { return repository; } /** {@inheritDoc} */ @Override public List<PurchaseBean> findByOrgCodeAndMonthAndSQL(String strCode, String getSudo, String sql) throws ServiceException { if (StringUtils.isEmpty(strCode)) { throw new IllegalArgumentException("Orginazation code must not be null or empty"); } if (StringUtils.isEmpty(getSudo)) { throw new IllegalArgumentException("Month must not be null or empty"); } if (StringUtils.isEmpty(sql)) { throw new IllegalArgumentException("Sql must not be null or empty"); } Connection connection = null; PreparedStatement preSmt = null; ResultSet rs = null; // Kind of SQL boolean isFindByOrgCodeAndMonthSQL = sql .equals(SqlConstants.SQL_FIND_PURCHASE_DATA_HEADER_BY_ORGCODE_AND_MONTH); List<PurchaseBean> purchases = new ArrayList<PurchaseBean>(); try { connection = HibernateSessionManager.getConnection(); preSmt = connection.prepareStatement(sql); preSmt.setString(1, getSudo); preSmt.setString(2, strCode); rs = preSmt.executeQuery(); while (rs.next()) { PurchaseBean purchase; String srsCode = rs.getString("srsCode"); String ctgCode = rs.getString("ctgCode"); String wakuNum = rs.getString("wakuNum"); String srsName = null; String srsNameR = null; String ctgName = null; String ctgNameR = null; int updNo = 0; int kingaku = 0; Date srDate = null; String gnrKbn1 = null; if (isFindByOrgCodeAndMonthSQL) { srsName = rs.getString("srsName"); srsNameR = rs.getString("srsNameR"); ctgName = rs.getString("ctgName"); ctgNameR = rs.getString("ctgNameR"); // New an instance purchase = new PurchaseBean(srsCode, srsName, srsNameR, ctgCode, ctgName, ctgNameR, wakuNum, updNo, srDate, kingaku, gnrKbn1); } else { updNo = rs.getInt("updNo"); srDate = rs.getDate("srDate"); kingaku = rs.getBigDecimal("kingaku").intValue(); gnrKbn1 = rs.getString("gnrKbn1"); // New an instance purchase = new PurchaseBean(srsCode, srsName, srsNameR, ctgCode, ctgName, ctgNameR, wakuNum, updNo, srDate, kingaku, gnrKbn1); } purchases.add(purchase); } } catch (Exception ex) { logger.error(ex.getMessage(), ex); } finally { try { if (rs != null) { rs.close(); } if (preSmt != null) { preSmt.close(); } if (connection != null) { connection.close(); } } catch (SQLException ex) { logger.error(ex.getMessage(), ex); } } if (purchases.size() <= 0) { throw new ObjectNotFoundException( "Could not find any purchase in the database for the organization's code " + strCode + " in month " + getSudo); } return purchases; } /** {@inheritDoc} */ @Override public Map<String, PurchaseBean> findByOrgCodeAndMonth(String orgCode, String getSudo, String sql) throws ServiceException { if (StringUtils.isEmpty(orgCode)) { throw new IllegalArgumentException("Orginazation code must not be null or empty"); } if (StringUtils.isEmpty(getSudo)) { throw new IllegalArgumentException("Month must not be null or empty"); } if (StringUtils.isEmpty(sql)) { throw new IllegalArgumentException("Sql must not be null or empty"); } Connection connection = null; PreparedStatement preSmt = null; ResultSet rs = null; final String HAZELCAST_PURCHASES = "PURCHASES"; final String UNDERSCORE_CHAR = "_"; // Get a reference to the shared system metadata map as a cluster member // NOTE: this loads the map from the backing store and can take a long time for large collections Map<String, PurchaseBean> purchases = hzInstance.getMap(HAZELCAST_PURCHASES); if (hzInstance.getLifecycleService().isRunning()) { try { purchases.clear(); } catch (HazelcastInstanceNotActiveException ex) { logger.debug(ex.getMessage(), ex); } } try { connection = HibernateSessionManager.getConnection(); preSmt = connection.prepareStatement(sql); preSmt.setString(1, getSudo); preSmt.setString(2, orgCode); rs = preSmt.executeQuery(); while (rs.next()) { String srsCode = rs.getString("srsCode"); String ctgCode = rs.getString("ctgCode"); String wakuNum = rs.getString("wakuNum"); int updNo = rs.getInt("updNo"); int kingaku = rs.getBigDecimal("kingaku").intValue(); Date srDate = rs.getDate("srDate"); String gnrKbn1 = rs.getString("gnrKbn1"); StringBuilder keyBuilder = new StringBuilder(); keyBuilder.append(DateFormatUtil.format(srDate, DateFormat.DATE_SHORT_DAY)); keyBuilder.append(UNDERSCORE_CHAR); keyBuilder.append(srsCode); keyBuilder.append(UNDERSCORE_CHAR); keyBuilder.append(ctgCode); keyBuilder.append(UNDERSCORE_CHAR); keyBuilder.append(wakuNum); // New an instance PurchaseBean purchase = new PurchaseBean(srsCode, null, null, ctgCode, null, null, wakuNum, updNo, srDate, kingaku, gnrKbn1); purchases.put(keyBuilder.toString(), purchase); } } catch (Exception ex) { logger.error(ex.getMessage(), ex); } finally { try { if (rs != null) { rs.close(); } if (preSmt != null) { preSmt.close(); } if (connection != null) { connection.close(); } } catch (SQLException ex) { logger.error(ex.getMessage(), ex); } } if (purchases.size() <= 0) { throw new ObjectNotFoundException( "Could not find any purchase in the database for the organization's code " + orgCode + " in month " + getSudo); } logger.info("Size of Hazelcast Map: {} items", purchases.size()); return purchases; } /** {@inheritDoc} */ @Override public boolean checkEmptyPurchase(String orgCode, String getSudo) throws ServiceException { if (StringUtils.isEmpty(getSudo)) { throw new IllegalArgumentException("orginazation code must not be null or empty"); } if (StringUtils.isEmpty(getSudo)) { throw new IllegalArgumentException("Month must not be null or empty"); } final Session session = HibernateSessionManager.getSession(); Transaction tx = null; long count = 0; try { StringBuilder sql = new StringBuilder(); sql.append(" SELECT count(ctgCode) "); sql.append(" FROM AT007 "); sql.append(" WHERE strCode=:strCode AND getSudo=:getSudo AND delKbn=2"); tx = session.beginTransaction(); Query query = repository.getSQLQuery(session, sql.toString()); query.setString("strCode", orgCode); query.setString("getSudo", getSudo); Object obj = query.uniqueResult(); count = Long.parseLong(obj.toString()); tx.commit(); } catch (SQLGrammarException | GenericJDBCException ex) { if (tx != null) { tx.rollback(); } throw new ServiceException("Hibernate runtime exception when check data available", ex); } finally { HibernateSessionManager.closeSession(session); } return (count > 0); } @Override public int updatePurchasesByOrgCodeAndMonth(JSONPurchase[] listPurchase, String usrCode, String monthly, String orgCode) throws ServiceException { if (ArrayUtils.isEmpty(listPurchase)) { throw new IllegalArgumentException("ListPurchase must not be null or empty"); } final Session session = HibernateSessionManager.getSession(); Transaction tx = null; int isUpdated = 0; try { String version = null; try { Version productVersion = ProductHelper.getProductInfor(); if (productVersion != null) { version = productVersion.getProjectVersion(); } } catch (IOException ex) { logger.warn(ex.getMessage()); } tx = session.beginTransaction(); Query query = repository.getNamedQuery(session, SqlConstants.SQL_UPDATE_PURCHASE_DATA); Set<String> setFlagConfirm = new HashSet<String>(); for (JSONPurchase jsonPurchase : listPurchase) { setFlagConfirm.add(jsonPurchase.getSrsCode() + ":" + jsonPurchase.getCtgCode() + ":" + jsonPurchase.getWakuNum() + ":" + jsonPurchase.getGnrKbn1()); // if (jsonPurchase.getSrDate() != null) { String kingaku = StringUtils.isEmpty(jsonPurchase.getKingaku()) ? "0" : jsonPurchase.getKingaku(); query.setString("kingaku", kingaku); query.setString("gnrKbn1", jsonPurchase.getGnrKbn1()); query.setString("tanCode", usrCode); query.setString("APInf2", usrCode); query.setString("stfCodeU", usrCode); query.setString("prdNoU", version); query.setString("strCode", orgCode); query.setString("getSudo", monthly); query.setDate("srDate", jsonPurchase.getSrDate()); query.setInteger("updNo", jsonPurchase.getUpdNo()); query.setString("srsCode", jsonPurchase.getSrsCode()); query.setString("ctgCode", jsonPurchase.getCtgCode()); query.setString("wakuNum", jsonPurchase.getWakuNum()); // Updating the record by using SQL for given changed information int numberOfRecordUpdated = query.executeUpdate(); if (numberOfRecordUpdated == 0) { // Save Error CM.UPD.M03 Criterion criterion = Restrictions.and( Restrictions.eq("pk.vendor.srsCode", jsonPurchase.getSrsCode()), Restrictions.eq("delKbn", Constants.STATUS_ACTIVE), Restrictions.eq("pk.organization.strCode", orgCode), Restrictions.eq("pk.category.ctgCode", jsonPurchase.getCtgCode()), Restrictions.eq("pk.wakuNum", jsonPurchase.getWakuNum()), Restrictions.eq("pk.getSudo", monthly), Restrictions.eq("pk.srDate", jsonPurchase.getSrDate())); Criteria criteria = repository.getCriteria(session, Purchase.class); criteria.add(criterion); String strErrorMsg = null; List<Purchase> purchase = repository.findByCriteria(criteria); if (purchase.size() == 0) { strErrorMsg = "Update fail !!!!!"; isUpdated = 2; } else { strErrorMsg = "Concurrent database update error record has been changed or deleted by another user. Please try again from menu screen."; isUpdated = 1; } logger.warn(strErrorMsg); break; } } } // Update flag Confirm if (isUpdated == 0) { query = repository.getNamedQuery(session, SqlConstants.SQL_UPDATE_FLAGCONFIRM_DATA); Iterator<String> iterator = setFlagConfirm.iterator(); while (iterator.hasNext()) { String string = (String) iterator.next(); String[] purchase = string.split(":"); query.setParameter("gnrKbn1", purchase[3]); query.setParameter("srsCode", purchase[0]); query.setParameter("ctgCode", purchase[1]); query.setParameter("wakuNum", purchase[2]); query.setString("strCode", orgCode); query.setString("getSudo", monthly); int numberOfRecordUpdated = query.executeUpdate(); if (numberOfRecordUpdated == 0) { isUpdated = 1; break; } } } if (isUpdated == 0) { tx.commit(); } else { tx.rollback(); } } catch (HibernateException ex) { if (tx != null) { tx.rollback(); } throw new ServiceException("Hibernate runtime exception when check data available", ex); } finally { HibernateSessionManager.closeSession(session); } return isUpdated; } /** {@inheritDoc} */ @Override public int findUpdNoByOrgCodeAndMonthAndConditions(String orgCode, String monthly, Date srDate, String ctgCode, String srsCode, String wakuNum) throws ServiceException { if (StringUtils.isEmpty(orgCode)) { throw new IllegalArgumentException("Organization's code must not be null or empty"); } if (StringUtils.isEmpty(monthly)) { throw new IllegalArgumentException("Monthly must not be null or empty"); } if (srDate == null) { throw new IllegalArgumentException("SrDate must not be null"); } if (StringUtils.isEmpty(ctgCode)) { throw new IllegalArgumentException("CtgCode must not be null"); } if (StringUtils.isEmpty(srsCode)) { throw new IllegalArgumentException("SrsCode must not be null"); } if (StringUtils.isEmpty(wakuNum)) { throw new IllegalArgumentException("Wakunum must not be null"); } final Session session = HibernateSessionManager.getSession(); Transaction tx = null; int currentUpdNo = 0; try { tx = session.beginTransaction(); final String SQL_FIND_UPDNO_BY_ORGCODE_AND_MONTH_CTGCODE_SRSCODE_AND_WAKUNUM = "SELECT MAX(c.updNo) FROM purchase AS c WHERE c.pk.organization.strCode = :strCode AND c.pk.getSudo = :getSudo AND c.pk.srDate = :srDate" + " AND c.pk.category = :ctgCode AND c.pk.vendor = :srsCode AND c.wakuNum = :wakuNum"; Query query = repository.getQuery(session, SQL_FIND_UPDNO_BY_ORGCODE_AND_MONTH_CTGCODE_SRSCODE_AND_WAKUNUM); query.setParameter("strCode", orgCode); query.setParameter("getSudo", monthly); query.setParameter("srDate", srDate); query.setParameter("ctgCode", ctgCode); query.setParameter("srsCode", srsCode); query.setParameter("wakuNum", wakuNum); Object updNo = query.uniqueResult(); if (updNo != null) { currentUpdNo = Integer.parseInt(updNo.toString()); } tx.commit(); } catch (HibernateException ex) { if (tx != null) { tx.rollback(); } throw new ServiceException("An exception occured while get max updNo for the given organization " + orgCode + " strCode and date" + srDate, ex); } finally { HibernateSessionManager.closeSession(session); } return currentUpdNo; } /** {@inheritDoc} */ @Override @SuppressWarnings("unchecked") public List<ActualViewBean> findDataBeansByOrgCodeAndMonthly(String orgCode, String monthly, String bunruiKbn) throws ServiceException { if (StringUtils.isEmpty(orgCode)) { throw new IllegalArgumentException("Orginazation's code must not be null or empty"); } if (StringUtils.isEmpty(monthly)) { throw new IllegalArgumentException("Month must not be null or empty"); } if (StringUtils.isEmpty(bunruiKbn)) { throw new IllegalArgumentException("bunruiKbn must not be null or empty"); } final Session session = HibernateSessionManager.getSession(); Transaction tx = null; List<ActualViewBean> jisekiBeans = new ArrayList<ActualViewBean>(); try { tx = session.beginTransaction(); Query query = repository.getQuery(session, " SELECT " + " a.pk.category.ctgCode as ctgCode, a.pk.category.ctgNameR as ctgNameR, a.kingaku as kingaku, a.updNo as updNo, a.pk.vendor.srsCode as srsCode, a.pk.vendor.srsNameR as srsNameR " + " FROM MonthlyPurchase a " + " WHERE a.delKbn = :delKbn AND a.pk.organization.strCode = :strCode AND a.pk.getSudo = :getSudo " + " AND a.pk.category.bunruiKbn = :bunruiKbn "); query.setParameter("strCode", orgCode); query.setParameter("getSudo", monthly); query.setParameter("delKbn", Constants.STATUS_ACTIVE); query.setParameter("bunruiKbn", bunruiKbn); query.setResultTransformer(Transformers.aliasToBean(ActualViewBean.class)); // jisekiBeans = query.list(); tx.commit(); } catch (HibernateException ex) { if (tx != null) { tx.rollback(); } throw new ServiceException("An error occurred while finding the Purchase Data by organization " + orgCode + " and monthly " + monthly); } finally { HibernateSessionManager.closeSession(session); } return jisekiBeans; } /** {@inheritDoc} */ @Override public boolean updatePurchaseData(Session session, String orgCode, String monthly, JSONActualViewBean[] beans) throws ServiceException { if (beans == null) { throw new IllegalArgumentException("Sales beans to update must not be null"); } User user = AuthenticatedUserDetails.getUserPrincipal(); if (user == null) { throw new IllegalArgumentException( "Sorry, you don't have a permission to use this. Please login with right permission"); } try { String sqlUpdate = " UPDATE at033 a SET a.`Kingaku` = :kingaku, a.`TanCode` = :tanCode , a.`UpdNo` = :updNo + 1, " + " a.`APInf2` = :aPInf2, a.`StfCodeU` = :stfCodeU, a.`PrdNoU` = :prdNoU, a.`TimeU` = NOW() " + " WHERE a.`Getsudo` = :getSudo AND a.`StrCode` = :strCode AND a.`CtgCode` = :ctgCode " + " AND a.`SrsCode` = :srsCode AND a.`UpdNo` = :updNo "; String usrCode = user.getUsrCode(); String version = StringUtils.EMPTY; try { version = ProductHelper.getProductInfor().getVersion(); } catch (IOException ex) { logger.warn(ex.getMessage()); } for (JSONActualViewBean bean : beans) { Query query = repository.getSQLQuery(session, sqlUpdate).setParameter("kingaku", bean.getKingaku()) .setParameter("tanCode", usrCode).setParameter("updNo", bean.getUpdNo()) .setParameter("aPInf2", usrCode).setParameter("stfCodeU", usrCode) .setParameter("prdNoU", version).setParameter("getSudo", monthly) .setParameter("strCode", orgCode).setParameter("ctgCode", bean.getCtgCode()) .setParameter("srsCode", bean.getSrsCode()); if (query.executeUpdate() <= 0) { logger.warn("can't update because updNo out of date at purchase data record with: strCode = " + orgCode + " and getSudo = " + monthly); return false; } } return true; } catch (SQLGrammarException | GenericJDBCException ex) { throw new ServiceException("An error occurred while update sales data", ex); } } }