Java 类org.hibernate.exception.SQLGrammarException 实例源码

@Override
public SQLExceptionConverter buildSQLExceptionConverter() {
        return new SQLExceptionConverter() {
                @Override
                public JDBCException convert(SQLException sqlException,
                                String message, String sql) {
                        final String sqlState = JdbcExceptionHelper
                                        .extractSqlState(sqlException);
                        if (sqlState != null) {
                                if (SQL_GRAMMAR_CATEGORIES.contains(sqlState)) {
                                        return new SQLGrammarException(message, sqlException,
                                                        sql);
                                } else if (DATA_CATEGORIES.contains(sqlState)) {
                                        return new DataException(message, sqlException, sql);
                                } else if (LOCK_ACQUISITION_CATEGORIES.contains(sqlState)) {
                                        return new LockAcquisitionException(message,
                                                        sqlException, sql);
                                }
                        }
                        return null;
                }
        };
}

public SQLExceptionConversionDelegate buildSQLExceptionConversionDelegate() {
        return new SQLExceptionConversionDelegate() {
                @Override
                public JDBCException convert(SQLException sqlException,
                                String message, String sql) {
                        final String sqlState = JdbcExceptionHelper
                                        .extractSqlState(sqlException);
                        if (sqlState != null) {
                                if (SQL_GRAMMAR_CATEGORIES.contains(sqlState)) {
                                        return new SQLGrammarException(message, sqlException,
                                                        sql);
                                } else if (DATA_CATEGORIES.contains(sqlState)) {
                                        return new DataException(message, sqlException, sql);
                                } else if (LOCK_ACQUISITION_CATEGORIES.contains(sqlState)) {
                                        return new LockAcquisitionException(message,
                                                        sqlException, sql);
                                }
                        }
                        return null;
                }
        };
}

@Override
public SQLExceptionConverter buildSQLExceptionConverter() {
  return new SQLExceptionConverter() {
    @Override
    public JDBCException convert(SQLException sqlException, String message,
        String sql) {
      final String sqlState = JDBCExceptionHelper
          .extractSqlState(sqlException);
      if (sqlState != null) {
        if (SQL_GRAMMAR_CATEGORIES.contains(sqlState)) {
          return new SQLGrammarException(message, sqlException, sql);
        }
        else if (DATA_CATEGORIES.contains(sqlState)) {
          return new DataException(message, sqlException, sql);
        }
        else if (LOCK_ACQUISITION_CATEGORIES.contains(sqlState)) {
          return new LockAcquisitionException(message, sqlException, sql);
        }
      }
      return null;
    }
  };
}

@Override
public SQLExceptionConverter buildSQLExceptionConverter() {
  return new SQLExceptionConverter() {
    @Override
    public JDBCException convert(SQLException sqlException, String message,
        String sql) {
      final String sqlState = JDBCExceptionHelper
          .extractSqlState(sqlException);
      if (sqlState != null) {
        if (SQL_GRAMMAR_CATEGORIES.contains(sqlState)) {
          return new SQLGrammarException(message, sqlException, sql);
        }
        else if (DATA_CATEGORIES.contains(sqlState)) {
          return new DataException(message, sqlException, sql);
        }
        else if (LOCK_ACQUISITION_CATEGORIES.contains(sqlState)) {
          return new LockAcquisitionException(message, sqlException, sql);
        }
      }
      return null;
    }
  };
}

private void handleException(Exception e, HttpServletResponse response) throws IOException {
  log4j.error(e.getMessage(), e);
  if (e instanceof SQLGrammarException) {
    log.error(((SQLGrammarException) e).getSQL());
  }
  if (!response.isCommitted()) {
    final JSONObject jsonResult = new JSONObject();
    final JSONObject jsonResponse = new JSONObject();
    String result = "";
    try {
      jsonResponse.put(JsonConstants.RESPONSE_STATUS,
          JsonConstants.RPCREQUEST_STATUS_VALIDATION_ERROR);
      jsonResponse.put("error", KernelUtils.getInstance().createErrorJSON(e));
      jsonResult.put(JsonConstants.RESPONSE_RESPONSE, jsonResponse);
      result = jsonResult.toString();
    } catch (JSONException e1) {
      log.error("Error genearating JSON error", e1);
    }
    writeResult(response, result);
  }
}

@Override
public SQLExceptionConverter buildSQLExceptionConverter() {
        return new SQLExceptionConverter() {
                @Override
                public JDBCException convert(SQLException sqlException,
                                String message, String sql) {
                        final String sqlState = JdbcExceptionHelper
                                        .extractSqlState(sqlException);
                        if (sqlState != null) {
                                if (SQL_GRAMMAR_CATEGORIES.contains(sqlState)) {
                                        return new SQLGrammarException(message, sqlException,
                                                        sql);
                                } else if (DATA_CATEGORIES.contains(sqlState)) {
                                        return new DataException(message, sqlException, sql);
                                } else if (LOCK_ACQUISITION_CATEGORIES.contains(sqlState)) {
                                        return new LockAcquisitionException(message,
                                                        sqlException, sql);
                                }
                        }
                        return null;
                }
        };
}

public SQLExceptionConversionDelegate buildSQLExceptionConversionDelegate() {
        return new SQLExceptionConversionDelegate() {
                @Override
                public JDBCException convert(SQLException sqlException,
                                String message, String sql) {
                        final String sqlState = JdbcExceptionHelper
                                        .extractSqlState(sqlException);
                        if (sqlState != null) {
                                if (SQL_GRAMMAR_CATEGORIES.contains(sqlState)) {
                                        return new SQLGrammarException(message, sqlException,
                                                        sql);
                                } else if (DATA_CATEGORIES.contains(sqlState)) {
                                        return new DataException(message, sqlException, sql);
                                } else if (LOCK_ACQUISITION_CATEGORIES.contains(sqlState)) {
                                        return new LockAcquisitionException(message,
                                                        sqlException, sql);
                                }
                        }
                        return null;
                }
        };
}

@Override
public SQLExceptionConverter buildSQLExceptionConverter() {
  return new SQLExceptionConverter() {
    @Override
    public JDBCException convert(SQLException sqlException, String message,
        String sql) {
      final String sqlState = JDBCExceptionHelper
          .extractSqlState(sqlException);
      if (sqlState != null) {
        if (SQL_GRAMMAR_CATEGORIES.contains(sqlState)) {
          return new SQLGrammarException(message, sqlException, sql);
        }
        else if (DATA_CATEGORIES.contains(sqlState)) {
          return new DataException(message, sqlException, sql);
        }
        else if (LOCK_ACQUISITION_CATEGORIES.contains(sqlState)) {
          return new LockAcquisitionException(message, sqlException, sql);
        }
      }
      return null;
    }
  };
}

@Override
public SQLExceptionConverter buildSQLExceptionConverter() {
  return new SQLExceptionConverter() {
    @Override
    public JDBCException convert(SQLException sqlException, String message,
        String sql) {
      final String sqlState = JDBCExceptionHelper
          .extractSqlState(sqlException);
      if (sqlState != null) {
        if (SQL_GRAMMAR_CATEGORIES.contains(sqlState)) {
          return new SQLGrammarException(message, sqlException, sql);
        }
        else if (DATA_CATEGORIES.contains(sqlState)) {
          return new DataException(message, sqlException, sql);
        }
        else if (LOCK_ACQUISITION_CATEGORIES.contains(sqlState)) {
          return new LockAcquisitionException(message, sqlException, sql);
        }
      }
      return null;
    }
  };
}

/**
 * @param s
 * @param uri
 * @return
 */
private Long getPersistedIdByUri(Session s, ReplicationInfo ri, String uri) {

    try {

        BaseEntity cached = uriCache.getByUriOrNull(
                checkAndCast(ri.getEntityClazz()), uri);
        if (cached != null) {
            return cached.getId();
        }
        String query = "select id from " + getEntityName(ri)
                + " where uri = :uri";
        Query q = s.createQuery(query);
        q.setParameter("uri", uri);

        return (Long) q.uniqueResult();
    } catch (SQLGrammarException sge) {
        throw new KarakuRuntimeException(Checker.format(
                "Can't find column 'uri' of entity %s, "
                        + "please use the annotation @URI, "
                        + "or create a field with name"
                        + "URI of type String.", ri.getEntityClazz()
                        .getSimpleName()), sge);
    }

}

@Override
public JDBCException convert(SQLException sqlException, String message, String sql) {
    if ( SQLClientInfoException.class.isInstance( sqlException )
            || SQLInvalidAuthorizationSpecException.class.isInstance( sqlException )
            || SQLNonTransientConnectionException.class.isInstance( sqlException )
            || SQLTransientConnectionException.class.isInstance( sqlException ) ) {
        return new JDBCConnectionException( message, sqlException, sql );
    }
    else if ( DataTruncation.class.isInstance( sqlException ) ||
            SQLDataException.class.isInstance( sqlException ) ) {
        throw new DataException( message, sqlException, sql );
    }
    else if ( SQLIntegrityConstraintViolationException.class.isInstance( sqlException ) ) {
        return new ConstraintViolationException(
                message,
                sqlException,
                sql,
                getConversionContext().getViolatedConstraintNameExtracter().extractConstraintName( sqlException )
        );
    }
    else if ( SQLSyntaxErrorException.class.isInstance( sqlException ) ) {
        return new SQLGrammarException( message, sqlException, sql );
    }
    else if ( SQLTimeoutException.class.isInstance( sqlException ) ) {
        return new QueryTimeoutException( message, sqlException, sql );
    }
    else if ( SQLTransactionRollbackException.class.isInstance( sqlException ) ) {
        // Not 100% sure this is completely accurate.  The JavaDocs for SQLTransactionRollbackException state that
        // it indicates sql states starting with '40' and that those usually indicate that:
        //      <quote>
        //          the current statement was automatically rolled back by the database because of deadlock or
        //          other transaction serialization failures.
        //      </quote>
        return new LockAcquisitionException( message, sqlException, sql );
    }

    return null; // allow other delegates the chance to look
}

public void testBadGrammar() throws Exception {
    SQLExceptionConverter converter = getDialect().buildSQLExceptionConverter();

    Session session = openSession();
    Connection connection = session.connection();

       // prepare/execute a query against a non-existent table
    PreparedStatement ps = null;
    try {
        ps = connection.prepareStatement("SELECT user_id, user_name FROM tbl_no_there");
        ps.executeQuery();

        fail("SQL compilation should have failed");
    }
    catch( SQLException sqle ) {
        assertEquals( "Bad conversion [" + sqle.getMessage() + "]", SQLGrammarException.class, converter.convert(sqle, null, null).getClass() );
    }
    finally {
        if ( ps != null ) {
            try {
                ps.close();
            }
            catch( Throwable ignore ) {
                // ignore...
            }
        }
    }

    session.close();
}

public static void rethrowAsDatastoreLogAll( final HibernateException he, final Log log, final String message )
        throws DatastoreException
{
    final String internalMess = concMess( message, he );
    if (he instanceof JDBCConnectionException || he instanceof GenericJDBCException
            || he instanceof SQLGrammarException)
    {
        log.error( internalMess, he );
        throw new DatastoreException( internalMess, he );
    }
    else if (he instanceof ConstraintViolationException)
    {
        log.error( internalMess, he );
        throw new DatastoreException( internalMess, he );
    }
    else if (he instanceof LockAcquisitionException)
    {
        log.error( internalMess, he );
        throw new DatastoreException( internalMess, he );
    }
    else if (he instanceof QuerySyntaxException)
    {
        log.error( internalMess, he );
        throw new DatastoreException( internalMess, he );
    }
    else if (he instanceof QueryException)
    {
        log.error( internalMess, he );
        throw new DatastoreException( internalMess, he );
    }
    else
    {
        log.error( internalMess, he );
        throw new DatastoreException( internalMess, he );
    }
}

public static void rethrowJdbcAsDatastore( final HibernateException he, final Log log, final String message, final int logErrorMask ) // NOPMD by dan on 01/02/15 07:21
        throws DatastoreException
{
    final String internalMess = concMess( message, he );
    if (he instanceof JDBCConnectionException || he instanceof GenericJDBCException
            || he instanceof SQLGrammarException)
    {
        if ((logErrorMask & JDBC_IS_ERROR) != 0)
        {
            log.error( internalMess, he );
        }
        throw new DatastoreException( internalMess, he );
    }
    else if (he instanceof ConstraintViolationException)
    {
        log.error( internalMess, he );
        throw new DatastoreException( internalMess, he );
    }
    else if (he instanceof LockAcquisitionException)
    {
        log.error( internalMess, he );
        throw new DatastoreException( internalMess, he );
    }
    else if (he instanceof QuerySyntaxException)
    {
        log.error( internalMess, he );
        throw new DatastoreException( internalMess, he );
    }
    else
    {
        log.error( internalMess, he );
        throw new DatastoreException( internalMess, he );
    }
}

public void execute() {
    // we need to do because of this class is app scoped
    Session session = sf.openSession();
    session.beginTransaction();
    try {
        update(session);
    } catch (SQLGrammarException ex) {
        ignoreIfTableDidNotExist(ex);
    }
    session.getTransaction().commit();
    session.close();
}

private void ignoreIfTableDidNotExist(SQLGrammarException ex) {
    if(ex.getCause().getMessage().contains(".question' doesn't exist")) {
        // ignore if its the first time we are running and the table still does not exist
        LOGGER.warn("Unable to run the mysql query to update the recent tags", ex);
    } else {
        // nasty catch and retrow, sorry.
        throw ex;
    }
}

/** {@inheritDoc} */
@Override
public boolean deleteByRecordId(String recId) throws ServiceException {
    if (StringUtils.isEmpty(recId)) {
        throw new IllegalArgumentException("Array FixedSystemCost must not be null or empty");
    }

    final Session session = HibernateSessionManager.getSession();
    Transaction tx = null;

    boolean isDeleted = Boolean.FALSE;
    try {
        tx = session.beginTransaction();
        String sql = " DELETE FROM  FixedSystemCost a WHERE  a.recID = :recID";
        Query query = fixedSystemCostRepository.getQuery(session, sql).setParameter("recID", recId);

        // 
        isDeleted = query.executeUpdate() > 0;
        // Release transaction
            tx.commit();
    } catch (SQLGrammarException | GenericJDBCException ex) {
        if (tx != null) {
            tx.rollback();
        }
        throw new ServiceException("An error occurred while delete FixedSystemCost list");
    } finally {
        HibernateSessionManager.closeSession(session);
    }
    return isDeleted;
}

/** {@inheritDoc} */
@Override
public boolean checkAvailableByOrgCodeAndMonthly(String orgCode, String monthly) throws ServiceException {
    if (StringUtils.isEmpty(orgCode)) {
        throw new IllegalArgumentException("Can not check budget performance available with oraganization code empty");
    }
    if (StringUtils.isEmpty(monthly)) {
        throw new IllegalArgumentException("Can not check budget performance available with current month in action empty");
    }

    final Session session = HibernateSessionManager.getSession();
    Transaction tx = null;

    boolean isAvailable = Boolean.FALSE;
    try {
        tx = session.beginTransaction();
        StringBuilder sql = new StringBuilder(" SELECT COUNT(YosanKingaku) + COUNT(JisekiKingaku) ");
        sql.append(" FROM " + TblConstants.TBL_BUDGET_PERFORMANCE);
        sql.append(" WHERE StrCode=:orgCode AND GetSudo=:monthly ");

        Query query = repository.getSQLQuery(session, sql.toString());
        query.setString("orgCode", orgCode);
        query.setString("monthly", monthly);

        Object count = query.uniqueResult();
        isAvailable = Double.valueOf(count.toString()) > 0;
        tx.commit();
    } catch (SQLGrammarException | GenericJDBCException ex) {
        if (tx != null) {
            tx.rollback();
        }
        throw new ServiceException("An exception occured while get budget performance data by organization code " + orgCode, ex);
    } finally {
        HibernateSessionManager.closeSession(session);
    }
    return isAvailable;
}

/** {@inheritDoc} */
@Override
public double findStampedLackingByOrgCodeAndBusinessDay(String orgCode, Date businessDay) throws ServiceException {
    if (StringUtils.isEmpty(orgCode)) {
        throw new IllegalArgumentException("Organization's code must not be null or empty");
    }

    if (businessDay == null) {
        throw new IllegalArgumentException("Business day must not be null or empty");
    }

    final Session session = HibernateSessionManager.getSession();
    Transaction tx = null;

    double isStamped = 0;
    try {
        tx = session.beginTransaction();
        String hql = "SELECT at021.Ninzu FROM at021 WHERE at021.StrCode = :StrCode "
                + " AND at021.SrDate = (SELECT at051.EigDateY FROM at051 WHERE at051.EigDate = :EigDate)"
                + " AND at021.DelKbn = 2 ";
        Query query = repository.getSQLQuery(session, hql);
        query.setString("StrCode", orgCode);
           query.setDate("EigDate", businessDay);

           Object result = query.uniqueResult();
           if (result != null) {
            isStamped = Double.parseDouble(result.toString());
           }
           tx.commit();
    } catch (SQLGrammarException | GenericJDBCException ex) {
        if (tx != null) {
            tx.rollback();
        }
        throw new ServiceException("An error occurred when getting all the number of stamped lacking "
                + "for the organization's code " + orgCode + " of day " + businessDay, ex);
    } finally {
        HibernateSessionManager.closeSession(session);
    }
    return isStamped;
}

/** {@inheritDoc}*/
@Override
public List<VJiseki> findByOrgCodeAndMonthies(String orgCode, String... monthlies) throws ServiceException {
    if (StringUtils.isEmpty(orgCode)) {
        throw new IllegalArgumentException("Organization must not be null or empty");
    }
    if (ArrayUtils.isEmpty(monthlies)) {
        throw new IllegalArgumentException("The monthlies must not be null");
    }

    final Session session = HibernateSessionManager.getSession();
    Transaction tx = null;

    List<VJiseki> jisekis = new ArrayList<>();
    try {
        tx = session.beginTransaction();
        Query query = repository.getSQLQuery(session, "SELECT  UriSkKG as uriSkKG, KtSkKG as ktSkKG, IdoSkKGU as idoSkKGU, IdoSkKGH as idoSkKGH, UriKrKG as uriKrKG,"
                + " KtKrKG as ktKrKG, KnSrKG as knSrKG, KtSrKG as ktSrKG, KgcSrKG as kgcSrKG, IdoSrKGU as idoSrKGU, IdoSrKGH as idoSrKGH, KtJkKG as ktJkKG, JkJkKG as jkJkKG, KgcJkKG as kgcJkKG, IdoJkKGU as idoJkKGU, IdoJkKGH as idoJkKGH, HelpJkKGU as helpJkKGU, "
                + " HelpJkKGH as helpJkKGH, KtKhKG as ktKhKG, KnKhKG as knKhKG, KgcKhKG as kgcKhKG, IdoKhKGU as idoKhKGU, IdoKhKGH as idoKhKGH, UriKhKG as uriKhKG FROM V_JISEKI WHERE StrCode = :strCode AND Getsudo in (:monthlies)");
        query.setParameter("strCode", orgCode);
        query.setParameterList("monthlies", monthlies);
        query.setResultTransformer(Transformers.aliasToBean(VJiseki.class));

        jisekis = repository.findByQuery(query);
        // Release transaction
        tx.commit();
        if (CollectionUtils.isEmpty(jisekis)) {
            throw new ObjectNotFoundException("There is no actual view object");
        }
    } catch (SQLGrammarException | GenericJDBCException ex) {
        if (tx != null) {
            tx.rollback();
        }
        throw new ServiceException("An exception occured while getting VJiseki data for the organization " + orgCode, ex);
    } finally {
        HibernateSessionManager.closeSession(session);
    }
    return jisekis;
}

/** {@inheritDoc} */
@Override
public boolean checkAvailableByOrgCodeAndMonthly(String orgCode, String... monthlies) throws ServiceException {
    if (StringUtils.isEmpty(orgCode) || monthlies == null) {
        throw new IllegalArgumentException("Can not check month available with organization code or monthly empty");
    }

    final Session session = HibernateSessionManager.getSession();
    Transaction tx = null;

    boolean isAvailable = Boolean.FALSE;
    try {
        tx = session.beginTransaction();
        Query query = repository.getSQLQuery(session, "SELECT count(StrCode) FROM v_jiseki WHERE StrCode = :orgCode AND GetSudo in (:monthlies)");
        query.setParameter("orgCode", orgCode);
        query.setParameterList("monthlies", monthlies);

        Object count =  query.uniqueResult();           
        if (count != null) {
            isAvailable = Long.parseLong(count.toString()) > 0;
           }
        tx.commit();
    } catch (SQLGrammarException | GenericJDBCException ex ) {
        if (tx != null) {
            tx.rollback();
        }
        throw new ServiceException("An exception occured while checking data for the organization " + orgCode, ex);
    } finally {
        HibernateSessionManager.closeSession(session);
    }
    return isAvailable;
}

/** {@inheritDoc} */
@Override
public boolean checkAvailableByOrgCodesAndMonthlies(List<String> orgCode, String... monthlies) throws ServiceException {
    if (CollectionUtils.isEmpty(orgCode)) {
        throw new IllegalArgumentException("Can not check month available with organization code empty");
    }
    if (ArrayUtils.isEmpty(monthlies)) {
        throw new IllegalArgumentException("Can not check month available with monthlies empty");
    }

    final Session session = HibernateSessionManager.getSession();
    Transaction tx = null;

    boolean isAvailable = Boolean.FALSE;
    try {
        tx = session.beginTransaction();
        Query query = repository.getSQLQuery(session, "SELECT count(StrCode) FROM v_jiseki WHERE StrCode in (:orgCode) AND GetSudo in (:monthlies)");
        query.setParameterList("orgCode", orgCode);
        query.setParameterList("monthlies", monthlies);
        Object count =  query.uniqueResult();           
        if (count != null) {
            isAvailable = Long.parseLong(count.toString()) > 0;
           }
        tx.commit();
    } catch (SQLGrammarException | GenericJDBCException ex ) {
        if (tx != null) {
            tx.rollback();
        }
        throw new ServiceException("An exception occured while checking data for the organization " + orgCode, ex);
    } finally {
        HibernateSessionManager.closeSession(session);
    }
    return isAvailable;
}

/** {@inheritDoc} */
@Override
@SuppressWarnings("unchecked")
public PettyCashBookReport findDistrictByShop(String shopID) throws ServiceException {
    if (StringUtils.isEmpty(shopID)) {
           throw new IllegalArgumentException("ShopIDがNULLべきではない");
       }

    final Session session = HibernateSessionManager.getSession();
    Transaction tx = null;

    PettyCashBookReport organization = null;
       try {
        tx = session.beginTransaction();
        Query query = repository.getSQLQuery(session, SqlConstants.SQL_FIND_ORGANIZATION_BY_SHOP);
        query.setParameter("StrCode", shopID);
        query.setResultTransformer(Transformers.aliasToBean(PettyCashBookReport.class));
        List<PettyCashBookReport> organizations = query.list();
        // Release transaction
        tx.commit();
           if (CollectionUtils.isEmpty(organizations)) {
               throw new ObjectNotFoundException("部門コード " + shopID + "　のデータが見つけなかった ");
           }
           organization = organizations.get(0);
       } catch (SQLGrammarException | GenericJDBCException ex) {
        if (tx != null) {
            tx.rollback();
        }
           throw new ServiceException("An exception occured while finding Organization list with ShopID " + shopID, ex);
       } finally {
        HibernateSessionManager.closeSession(session);
       }
       return organization;
}

/** {@inheritDoc} */
@Override
@SuppressWarnings("unchecked")
public List<String> findOrgsByDepartmentCode(String strCode) throws ServiceException {
    if (StringUtils.isEmpty(strCode)) {
        throw new IllegalArgumentException("Can not find department with the strCode empty");
    }

    final Session session = HibernateSessionManager.getSession();
    Transaction tx = null;

    List<String> organizations = new ArrayList<String>();
    try {
        tx = session.beginTransaction();
        StringBuilder sql = new StringBuilder("SELECT ifnull(t5.StrCode,ifnull(t4.StrCode,ifnull(t3.StrCode,ifnull(t2.StrCode,t1.StrCode)))) as strcode FROM at102 AS t1");
        sql.append("  LEFT JOIN at102 AS t2 ON t2.StrCodeUp = t1.StrCode ");
        sql.append("  LEFT JOIN at102 AS t3 ON t3.StrCodeUp = t2.StrCode ");
        sql.append("  LEFT JOIN at102 AS t4 ON t4.StrCodeUp = t3.StrCode ");
        sql.append("  LEFT JOIN at102 AS t5 ON t5.StrCodeUp = t4.StrCode ");
        sql.append(" where t1.StrCode = :strcode ");

        // executed sql to get list data orgazation's codes with kaisobango = 5
        Query query = repository.getSQLQuery(session, sql.toString());
        query.setParameter("strcode", strCode);
        organizations = query.list();
        // Release transaction
        tx.commit();
        if (CollectionUtils.isEmpty(organizations)) {
            throw new ObjectNotFoundException("Can not find organizations with strCode " + strCode);
        }
    } catch (SQLGrammarException | GenericJDBCException ex) {
        if (tx != null) {
            tx.rollback();
        }
        throw new ServiceException("An exception occured while finding Organization list with strCode " + strCode, ex);
    } finally {
        HibernateSessionManager.closeSession(session);
    }
    return organizations;
}

/** {@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);
}

/** {@inheritDoc} */
@Override
public boolean checkAvailable(String orgCode, Date getSudo) throws ServiceException {
    if (StringUtils.isEmpty(orgCode) || getSudo == null) {
        throw new IllegalArgumentException("To check month's data available, The input parameter organization code or business day must not be empty.");
    }

    final Session session = HibernateSessionManager.getSession();
    Transaction tx = null;

    boolean isAvailable = false;
    try {
        tx = session.beginTransaction();
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT count(recID) ");
        sql.append(" FROM AT009 ");
        sql.append(" WHERE strCode=:strCode AND getSudo=:getSudo AND delKbn=2");

        // 
        String strBusinessDay = DateFormatUtil.format(getSudo, DateFormat.DATE_WITHOUT_DAY);
        Query query = pettyCashRepository.getSQLQuery(session, sql.toString())
                .setString("strCode", orgCode)
                .setString("getSudo", strBusinessDay);

        BigInteger count = (BigInteger)query.uniqueResult();
        isAvailable = (count.floatValue() > 0);
        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 isAvailable;
}

/** {@inheritDoc} */
@Override
public List<PettyCash> findUpdateNoById(String... recIDs) throws ServiceException {
    if (ArrayUtils.isEmpty(recIDs)) {
        throw new IllegalArgumentException("To get petty cash update number, its record id must not be empty");
    }
    final Session session = HibernateSessionManager.getSession();
    Transaction tx = null;

    List<PettyCash> pettyCashes = new ArrayList<PettyCash>();
    try {
        tx = session.beginTransaction();
        Query query = pettyCashRepository.getSQLQuery(session, " SELECT UpdNo FROM AT009 WHERE RecID in (:recIDs) AND DelKbn=2 ")
                .setParameterList("recIDs", recIDs);

        pettyCashes = pettyCashRepository.findByQuery(query);
        tx.commit();
    } catch (SQLGrammarException | GenericJDBCException ex) {
        if (tx != null) {
            tx.rollback();
        }
        throw new ServiceException("Hibernate runtime exception when get update no of petty cash list ", ex);
    } finally {
        HibernateSessionManager.closeSession(session);
    }
    return pettyCashes;
}

@Test
public void testGetReviewsInvalidOrder2() {
    String orderBy = "namea";
    SQLGrammarException ex = new SQLGrammarException("", new SQLException());
    testGetReviewsException(orderBy, ex, 400, ErrorType.BAD_REQUEST, 
            String.format(MESSAGE_INVALID_ORDER, orderBy), null);
}

@Test
public void testGetReviewsInvalidOrder2() {
    String orderBy = "namea";
    SQLGrammarException ex = new SQLGrammarException("", new SQLException());
    testGetReviewsException(orderBy, ex, 400, ErrorType.BAD_REQUEST, 
            String.format(MESSAGE_INVALID_ORDER, orderBy), null);
}

/**
 * Sometimes, the exception means retry and sometimes not.  The stack of exceptions is also checked
 * for any occurence of {@link DoNotRetryException} and, if found, nothing is returned.
 *
 * @param cause     the cause to examine
 * @return          Returns the original cause if it is a valid retry cause, otherwise <tt>null</tt>
 */
public static Throwable extractRetryCause(Throwable cause)
{
    Throwable retryCause = ExceptionStackUtil.getCause(cause, RETRY_EXCEPTIONS);

    if (retryCause == null)
    {
        return null;
    }
    else if (ExceptionStackUtil.getCause(cause, DoNotRetryException.class) != null)
    {
        // Someone decided that the txn should NOT retry
        return null;
    }
    else if (retryCause instanceof SQLGrammarException
            && ((SQLGrammarException) retryCause).getErrorCode() != 3960)
    {
       return null;
    }
    else if (retryCause instanceof UncategorizedSQLException)
    {
        // The exception will have been caused by something else, so check that instead
        if (retryCause.getCause() != null && retryCause.getCause() != retryCause)
        {
            // We dig further into this
            cause = retryCause.getCause();
            // Check for SQL-related "deadlock" messages
            if (retryCause.getMessage().toLowerCase().contains("deadlock"))
            {
                // The word "deadlock" is usually an indication that we need to resolve with a retry.
                return retryCause;
            }
            else if (retryCause.getMessage().toLowerCase().contains("constraint"))
            {
                // The word "constraint" is also usually an indication or a concurrent update
                return retryCause;
            }
            // Recurse
            return extractRetryCause(cause);
        }
        else
        {
            return null;
        }
    }
    // A simple match
    return retryCause;
}

@Override
public JDBCException convert(SQLException sqlException, String message, String sql) {
    final String sqlState = JdbcExceptionHelper.extractSqlState( sqlException );
    final int errorCode = JdbcExceptionHelper.extractErrorCode( sqlException );

    if ( sqlState != null ) {
        String sqlStateClassCode = JdbcExceptionHelper.determineSqlStateClassCode( sqlState );

        if ( sqlStateClassCode != null ) {
            if ( SQL_GRAMMAR_CATEGORIES.contains( sqlStateClassCode ) ) {
                return new SQLGrammarException( message, sqlException, sql );
            }
            else if ( INTEGRITY_VIOLATION_CATEGORIES.contains( sqlStateClassCode ) ) {
                final String constraintName = getConversionContext()
                        .getViolatedConstraintNameExtracter()
                        .extractConstraintName( sqlException );
                return new ConstraintViolationException( message, sqlException, sql, constraintName );
            }
            else if ( CONNECTION_CATEGORIES.contains( sqlStateClassCode ) ) {
                return new JDBCConnectionException( message, sqlException, sql );
            }
            else if ( DATA_CATEGORIES.contains( sqlStateClassCode ) ) {
                return new DataException( message, sqlException, sql );
            }
        }

        if ( "40001".equals( sqlState ) ) {
            return new LockAcquisitionException( message, sqlException, sql );
        }

        if ( "40XL1".equals( sqlState ) || "40XL2".equals( sqlState )) {
            // Derby "A lock could not be obtained within the time requested."
            return new PessimisticLockException( message, sqlException, sql );
        }

        // MySQL Query execution was interrupted
        if ( "70100".equals( sqlState ) ||
                // Oracle user requested cancel of current operation
                ( "72000".equals( sqlState ) && errorCode == 1013 ) ) {
            throw new QueryTimeoutException(  message, sqlException, sql );
        }
    }

    return null;
}

@Override
public SQLExceptionConversionDelegate buildSQLExceptionConversionDelegate() {
    return new SQLExceptionConversionDelegate() {
        @Override
        public JDBCException convert(final SQLException sqlException, final String message, final String sql) {

            final int errorCode = JdbcExceptionHelper.extractErrorCode( sqlException );

            if ( errorCode == 131 ) {
                // 131 - Transaction rolled back by lock wait timeout
                return new LockTimeoutException( message, sqlException, sql );
            }

            if ( errorCode == 146 ) {
                // 146 - Resource busy and acquire with NOWAIT specified
                return new LockTimeoutException( message, sqlException, sql );
            }

            if ( errorCode == 132 ) {
                // 132 - Transaction rolled back due to unavailable resource
                return new LockAcquisitionException( message, sqlException, sql );
            }

            if ( errorCode == 133 ) {
                // 133 - Transaction rolled back by detected deadlock
                return new LockAcquisitionException( message, sqlException, sql );
            }

            // 259 - Invalid table name
            // 260 - Invalid column name
            // 261 - Invalid index name
            // 262 - Invalid query name
            // 263 - Invalid alias name
            if ( errorCode == 257 || ( errorCode >= 259 && errorCode <= 263 ) ) {
                throw new SQLGrammarException( message, sqlException, sql );
            }

            // 257 - Cannot insert NULL or update to NULL
            // 301 - Unique constraint violated
            // 461 - foreign key constraint violation
            // 462 - failed on update or delete by foreign key constraint violation
            if ( errorCode == 287 || errorCode == 301 || errorCode == 461 || errorCode == 462 ) {
                final String constraintName = getViolatedConstraintNameExtracter().extractConstraintName(
                        sqlException );

                return new ConstraintViolationException( message, sqlException, sql, constraintName );
            }

            return null;
        }
    };
}

@Transactional
public ValueResponse<User> createUser( User user ) {

    if( user == null ) throw new IllegalArgumentException( "user is required" );

    ZonedDateTime utc = ZonedDateTime.now( ZoneOffset.UTC );
    user.setDateCreated( Date.from( utc.toInstant() ) );

    if( !validEmail( user.getEmail() ) ) {
        return new ValueResponse<User>( ValueResponse.FAILURE, "email is invalid", null );
    }
    if( StringUtils.isEmpty( user.getUsername() ) ) {
        user.setUsername( generateUsername() );
    }
    if( !StringUtils.isEmpty( user.getFirstName() ) && !validName( user.getFirstName() ) ) {
        return new ValueResponse<User>( ValueResponse.FAILURE, "first name is invalid", null );
    }
    if( !StringUtils.isEmpty( user.getLastName() ) && !validName( user.getLastName() ) ) {
        return new ValueResponse<User>( ValueResponse.FAILURE, "last name is invalid", null );
    }
    if( !StringUtils.isEmpty( user.getUsername() ) && !validUsername( user.getUsername() ) ) {
        return new ValueResponse<User>( ValueResponse.FAILURE, "username is invalid", null );
    }

    try {
        user.setPassword( aes.encrypt( user.getPassword() ) );
    } catch ( Exception e ) {
        return new ValueResponse<User>( ValueResponse.FAILURE, e.getMessage() );
    }

    try {
        userDAO.createUser( user );
    } catch( ConstraintViolationException cve ) {

        return new ValueResponse<User>( ValueResponse.FAILURE, cve.getMessage() );

    } catch( SQLGrammarException sge ) {

        return new ValueResponse<User>( ValueResponse.FAILURE, sge.getMessage() );

    } catch( Throwable t ) {

        return new ValueResponse<User>( ValueResponse.FAILURE, t.toString() );
    }

    return new ValueResponse<User>( ValueResponse.SUCCESS, "", user );

}

public void testStaleVersionedInstanceFoundOnLock() {
    if ( ! readCommittedIsolationMaintained( "repeatable read tests" ) ) {
        return;
    }
    if ( getDialect().doesReadCommittedCauseWritersToBlockReaders()) {
        reportSkip( "lock blocking", "stale versioned instance" );
        return;
    }
    String check = "EJB3 Specification";
    Session s1 = getSessions().openSession();
    Transaction t1 = s1.beginTransaction();
    Item item = new Item( check );
    s1.save(  item );
    t1.commit();
    s1.close();

    Long itemId = item.getId();
    long initialVersion = item.getVersion();

    // Now, open a new Session and re-load the item...
    s1 = getSessions().openSession();
    t1 = s1.beginTransaction();
    item = ( Item ) s1.get( Item.class, itemId );

    // now that the item is associated with the persistence-context of that session,
    // open a new session and modify it "behind the back" of the first session
    Session s2 = getSessions().openSession();
    Transaction t2 = s2.beginTransaction();
    Item item2 = ( Item ) s2.get( Item.class, itemId );
    item2.setName( "EJB3 Persistence Spec" );
    t2.commit();
    s2.close();

    // at this point, s1 now contains stale data, so acquire a READ lock
    // and make sure we get the already associated state (i.e., the old
    // name and the old version)
    s1.lock( item, LockMode.READ );
    item2 = ( Item ) s1.get( Item.class, itemId );
    assertTrue( item == item2 );
    assertEquals( "encountered non-repeatable read", check, item2.getName() );
    assertEquals( "encountered non-repeatable read", initialVersion, item2.getVersion() );

    // attempt to acquire an UPGRADE lock; this should fail
    try {
        s1.lock( item, LockMode.UPGRADE );
        fail( "expected UPGRADE lock failure" );
    }
    catch( StaleObjectStateException expected ) {
        // this is the expected behavior
    }
    catch( SQLGrammarException t ) {
        if ( getDialect() instanceof SQLServerDialect ) {
            // sql-server (using snapshot isolation) reports this as a grammar exception /:)
            //
            // not to mention that it seems to "lose track" of the transaction in this scenario...
            t1.rollback();
            t1 = s1.beginTransaction();
        }
        else {
            throw t;
        }
    }

    t1.commit();
    s1.close();

    // clean up
    s1 = getSessions().openSession();
    t1 = s1.beginTransaction();
    s1.createQuery( "delete Item" ).executeUpdate();
    t1.commit();
    s1.close();
}

public static void rethrowJdbcAsDatastoreAndConstraintAsItself( final HibernateException he, final Log log, final String message,
        final int logErrorMask ) throws DatastoreException, MAConstraintViolationException
{
    final String internalMess = concMess( message, he );
    if (he instanceof JDBCConnectionException || he instanceof GenericJDBCException
            || he instanceof SQLGrammarException)
    {
        if ((logErrorMask & JDBC_IS_ERROR) != 0)
        {
            log.error( internalMess, he );
        }
        throw new DatastoreException( internalMess, he );
    }
    else if (he instanceof ConstraintViolationException)
    {
        if ((logErrorMask & CONSTRAINT_IS_ERROR) != 0)
        {
            log.error( internalMess, he );
        }
        throw new MAConstraintViolationException( internalMess, he );
    }
    else if (he instanceof LockAcquisitionException)
    {
        log.error( internalMess, he );
        throw new DatastoreException( internalMess, he );
    }
    else if (he instanceof QuerySyntaxException)
    {
        log.error( internalMess, he );
        throw new DatastoreException( internalMess, he );
    }
    else if (he instanceof IdentifierGenerationException)
    {
        log.error( internalMess, he );
        throw new DatastoreException( internalMess, he );
    }
    else if (he instanceof PropertyValueException)
    {
        log.error( internalMess, he );
        throw new DatastoreException( internalMess, he );
    }
    else
    {
        log.error( internalMess, he );
        throw new DatastoreException( internalMess, he );
    }
}

/**
 * Sometimes, the exception means retry and sometimes not.  The stack of exceptions is also checked
 * for any occurence of {@link DoNotRetryException} and, if found, nothing is returned.
 *
 * @param cause     the cause to examine
 * @return          Returns the original cause if it is a valid retry cause, otherwise <tt>null</tt>
 */
public static Throwable extractRetryCause(Throwable cause)
{
    Throwable retryCause = ExceptionStackUtil.getCause(cause, RETRY_EXCEPTIONS);

    if (retryCause == null)
    {
        return null;
    }
    else if (ExceptionStackUtil.getCause(cause, DoNotRetryException.class) != null)
    {
        // Someone decided that the txn should NOT retry
        return null;
    }
    else if (retryCause instanceof SQLGrammarException
            && ((SQLGrammarException) retryCause).getErrorCode() != 3960)
    {
       return null;
    }
    else if (retryCause instanceof UncategorizedSQLException)
    {
        // The exception will have been caused by something else, so check that instead
        if (retryCause.getCause() != null && retryCause.getCause() != retryCause)
        {
            // We dig further into this
            cause = retryCause.getCause();
            // Check for SQL-related "deadlock" messages
            if (retryCause.getMessage().toLowerCase().contains("deadlock"))
            {
                // The word "deadlock" is usually an indication that we need to resolve with a retry.
                return retryCause;
            }
            else if (retryCause.getMessage().toLowerCase().contains("constraint"))
            {
                // The word "constraint" is also usually an indication or a concurrent update
                return retryCause;
            }
            // Recurse
            return extractRetryCause(cause);
        }
        else
        {
            return null;
        }
    }
    // A simple match
    return retryCause;
}

/**
 * Thit is function handle updating data from Jisekim screen
 * 
 * @param orgCode
 * @param monthly
 * @param jSONListActualView
 * @return boolean status update
 * @throws ServiceException
 */
public boolean handleUpdateJisekimData(String orgCode, String monthly, JSONListActualViewBean jSONListActualView) 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 (jSONListActualView == null) {
        throw new IllegalArgumentException("Jisekim Beans object must not be null");
    }

    final Session session = HibernateSessionManager.getSession();
    Transaction tx = null;

    try {
        // 
        tx = session.beginTransaction();
        JSONActualViewBean[] salesBeans = jSONListActualView.getSalesBeans();
        if (ArrayUtils.isNotEmpty(salesBeans)) {
            boolean isUpdatedMonthlySale = monthlySalesService.updateSalesData(session, orgCode, monthly, salesBeans);
            if (!isUpdatedMonthlySale) {
                if (tx != null) {
                    tx.rollback();
                }

                // An error occurred while trying to update sale on month {} for organization {}
                logger.error("An error occurred while trying to update sale on month " + monthly + " for organization " + orgCode);
                return false;
            }
        }

        JSONActualViewBean[] purchaseBeans = jSONListActualView.getPurchaseBeans();
        if (ArrayUtils.isNotEmpty(purchaseBeans)) {
            boolean isUpdatedPurchase = purchaseService.updatePurchaseData(session, orgCode, monthly, purchaseBeans);
            if (!isUpdatedPurchase) {
                if (tx != null) {
                    tx.rollback();
                }

                // An error occurred while trying to update sale on month {} for organization {}
                logger.error("An error occurred while trying to update purchas on month " + monthly + " for organization " + orgCode);
                return false;
            }
        }

        JSONActualViewBean[] inventoryBeans = jSONListActualView.getInventoryBeans();
        if (ArrayUtils.isNotEmpty(inventoryBeans)) {
            boolean isUpdatedInventory = inventoryService.updateInventoryData(session, orgCode, monthly, inventoryBeans);
            if (!isUpdatedInventory) {
                if (tx != null) {
                    tx.rollback();
                }

                // 
                logger.error("An error occurred while trying to update inventory on month " + monthly + " for organization " + orgCode);
                return false;
            }
        }
        tx.commit();
        return true;
    } catch (SQLGrammarException ex) {
        if (tx != null) {
            tx.rollback();
        }
        throw new ServiceException("An error occurred while update Jisekim Datas", ex);
    } finally {
        // セッションを閉じる
        HibernateSessionManager.closeSession(session);
    }
}

/** {@inheritDoc} */
@Override
@SuppressWarnings("unchecked")
public List<FixedSystemCostBean> findByOrgCodeAndMonth(String orgCode, String monthly, boolean isCopying) 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");
    }

    final Session session = HibernateSessionManager.getSession();
    Transaction tx = null;

    List<FixedSystemCostBean> fixedSystemCosts = new ArrayList<>();
    try {
        StringBuffer sql;
        if (!isCopying) {
            sql = new StringBuffer("SELECT a.RecID as recID, a.KmkCode as kmkCode, a.ShoriKbn as shoriKbn,");
            sql.append(" a.Tekiyo as tekiyo, a.HSKbn as hsKbn, a.kingaku as kingaku, a.UpdNo as updNo, ( SELECT d.kmkNameR FROM AT106 d WHERE d.DelKbn = :delKbn AND d.KmkCode = a.kmkCode) as kmkNameR");
            sql.append(" FROM AT017 a WHERE a.Getsudo =:getSudo and a.StrCode =:strCode AND a.DelKbn = 2");
            sql.append(" ORDER BY HSKbn ASC, KmkCode ASC");

        } else {
            sql = new StringBuffer("SELECT a.RecID as recID, a.KmkCode as kmkCode, a.ShoriKbn as shoriKbn,");
            sql.append(" a.Tekiyo as tekiyo, a.HSKbn as hsKbn, a.kingaku as kingaku, a.UpdNo as updNo, ( SELECT d.kmkNameR FROM AT106 d WHERE d.DelKbn = :delKbn AND d.KmkCode = a.kmkCode) as kmkNameR");
            sql.append(" FROM AT017 a WHERE a.Getsudo =:getSudo and a.StrCode =:strCode AND a.DelKbn = 2 AND HSKbn = 1");
            sql.append(" ORDER BY HSKbn ASC, KmkCode ASC");
        }

        tx = session.beginTransaction();
        // 
        Query query = fixedSystemCostRepository.getSQLQuery(session, sql.toString());
           query.setParameter("delKbn", Constants.DEFAULT_DEL_KBN);
           query.setParameter("strCode", orgCode);
           query.setParameter("getSudo", monthly);
           query.setResultTransformer(Transformers.aliasToBean(FixedSystemCostBean.class));

           //
           fixedSystemCosts = query.list();
           // Release transaction
            tx.commit();
           if (CollectionUtils.isEmpty(fixedSystemCosts)) {
               throw new ObjectNotFoundException("Could not find any object in monthly " + monthly + " for the organization " + orgCode);
           }
    } catch (SQLGrammarException | GenericJDBCException ex) {
        if (tx != null) {
            tx.commit();
        }
        throw new ServiceException("An exception occured while get data ", ex);
    } finally {
        HibernateSessionManager.closeSession(session);
    }
    return fixedSystemCosts;
}

/** {@inheritDoc} */
@Override
public boolean updateFixedSystemCost(JSONFixedSystemCost jsonFixedSystemCost) throws ServiceException {
    if (jsonFixedSystemCost == null) {
        throw new IllegalArgumentException("Inventory must not be null or empty");
    }

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

    final Session session = HibernateSessionManager.getSession();
    Transaction tx = null;

    boolean isUpdated = Boolean.FALSE;
    try{

        StringBuilder sqlUpdate = new StringBuilder();
        sqlUpdate.append(" UPDATE AT017 c" );
        sqlUpdate.append(" SET c.KmKCode=:kmkCode, ");
        sqlUpdate.append(" c.ShoriKbn = :shoriKbn, c.tanCode = :tanCode, c.APInf2 =:apInf2, c.StfCodeU = :stfCodeU, c.prdNoU = :prdNoU, c.TimeU = NOW(),");
        sqlUpdate.append(" c.Tekiyo = :tekiYo, c.HSKbn = :hsKbn, c.Kingaku = :kingaku, c.UpdNo = (:updNo + 1), c.RecCkbn = :recCkbn, c.DelKbn = :delKbn ");
        sqlUpdate.append(" WHERE c.RecID = :recID AND c.UpdNo = :updNo");

        tx = session.beginTransaction();
        //
        Query query = fixedSystemCostRepository.getSQLQuery(session, sqlUpdate.toString());
        query.setParameter("kmkCode", jsonFixedSystemCost.getKmkCode());
        query.setParameter("shoriKbn", jsonFixedSystemCost.getShoriKbn());
        query.setParameter("tanCode", user.getUsrCode());
        query.setParameter("apInf2", user.getUsrCode());
        query.setParameter("stfCodeU", user.getUsrCode());
        query.setParameter("tekiYo", jsonFixedSystemCost.getTekiyo());
        query.setParameter("hsKbn", jsonFixedSystemCost.getHsKbn());
        query.setParameter("kingaku", jsonFixedSystemCost.getKingaku());
        query.setParameter("updNo", jsonFixedSystemCost.getUpdNo());
        query.setParameter("recID", jsonFixedSystemCost.getRecID());
        query.setParameter("delKbn", Constants.STATUS_ACTIVE);
        query.setParameter("recCkbn", Constants.DEFAULT_KBN);

        try {
            query.setParameter("prdNoU", ProductHelper.getProductInfor().getVersion());
        } catch (IOException e) {
            logger.warn("Can not get product informations when update help record");
        }

        // 
        int result = query.executeUpdate();
        isUpdated = result > 0;
        tx.commit();
    } catch (SQLGrammarException | GenericJDBCException ex) {
        if (tx != null) {
            tx.rollback();
        }
        throw new ServiceException("An exception occured while updating  data ", ex);
    } finally {
        HibernateSessionManager.closeSession(session);
    }
    return isUpdated;
}

/** {@inheritDoc} */
@Override
public Map<String, BudgetPerformanceBean> findByOrgCodesAndMonthAndKmkCodeJs(String month, String[] orgCodes, String... kmkCodeJs) throws ServiceException {
    if (StringUtils.isEmpty(month)) {
        throw new IllegalArgumentException("Can not find budget performance with given month is empty");
    }
    if (ArrayUtils.isEmpty(orgCodes)) {
        throw new IllegalArgumentException("Can not find budget performance with given organization code array is null");
    }
    if (ArrayUtils.isEmpty(kmkCodeJs)) {
        throw new IllegalArgumentException("Can not find budget performance with given kmkCodeJs array is null");
    }

    final Session session = HibernateSessionManager.getSession();
    Transaction tx = null;

    Map<String, BudgetPerformanceBean> mapBudgetPerformances = new HashMap<String, BudgetPerformanceBean>();
    try {
        tx = session.beginTransaction();
        StringBuilder sql = new StringBuilder("SELECT KmkCodeJ as kmkCodeJ, SUM(YosanKingaku) as yosanKingaku, SUM(JisekiKingaku) as jisekiKingaku ");
        sql.append(" FROM " + TblConstants.TBL_BUDGET_PERFORMANCE);
        sql.append(" WHERE StrCode IN (:orgCodes) ");
        sql.append(" AND GetSudo = :month ");
        sql.append(" AND KmkCodeJ IN (:kmkCodeJs) ");
        sql.append(" AND DelKbn = 2 ");
        sql.append(" GROUP BY Getsudo, KmkCodeJ");

        Query query = repository.getSQLQuery(session, sql.toString());
        query.setParameterList("orgCodes", orgCodes);
        query.setString("month", month);
        query.setParameterList("kmkCodeJs", kmkCodeJs);
        query.setResultTransformer(Transformers.aliasToBean(BudgetPerformanceBean.class));

        List<BudgetPerformanceBean> budgetPerformances = query.list();
        // Release transaction
        tx.commit();
        if (CollectionUtils.isEmpty(budgetPerformances)) {
            throw new ObjectNotFoundException("No budget performance object found");
        }

        for (BudgetPerformanceBean budgetPerformance : budgetPerformances) {
            mapBudgetPerformances.put(budgetPerformance.getKmkCodeJ(), budgetPerformance);
        }
    } catch (SQLGrammarException | GenericJDBCException ex) {
        if (tx != null) {
            tx.rollback();
        }
        throw new ServiceException("Runtime exception occur when get a map of budget performance objects with given organization codes, kmkCodeJs and month", ex);
    } finally {
        HibernateSessionManager.closeSession(session);
    }
    return mapBudgetPerformances;
}