Example usage for javax.persistence StoredProcedureQuery execute

List of usage examples for javax.persistence StoredProcedureQuery execute

  1. HOME
  2. Java
  3. javax
  4. javax.persistence.*
  5. StoredProcedureQuery
  6. execute

Introduction

In this page you can find the example usage for javax.persistence StoredProcedureQuery execute.

Prototype

boolean execute();

Source Link

Document

Return true if the first result corresponds to a result set, and false if it is an update count or if there are no results other than through INOUT and OUT parameters, if any.

Usage

From source file:example.springdata.jpa.storedprocedures.UserRepositoryIntegrationTests.java

@Test
public void plainJpa21_entityAnnotatedCustomNamedProcedurePlus1IO() {

    StoredProcedureQuery proc = em.createNamedStoredProcedureQuery("User.plus1");

    proc.setParameter("arg", 1);
    proc.execute();

    assertThat(proc.getOutputParameterValue("res"), is((Object) 2));
}

From source file:example.springdata.jpa.storedprocedures.UserRepositoryIntegrationTests.java

@Test
public void plainJpa21() {

    StoredProcedureQuery proc = em.createStoredProcedureQuery("plus1inout");
    proc.registerStoredProcedureParameter(1, Integer.class, ParameterMode.IN);
    proc.registerStoredProcedureParameter(2, Integer.class, ParameterMode.OUT);

    proc.setParameter(1, 1);/*  w w w .ja v  a  2 s . c o  m*/
    proc.execute();

    assertThat(proc.getOutputParameterValue(2), is((Object) 2));
}

From source file:gov.opm.scrd.batchprocessing.jobs.BatchProcessingJob.java

/**
 * Collates new main frame payments./*from   w w  w  .ja  v a 2 s.c  o  m*/
 *
 * @param importStatus The import status.
 * @throws BatchProcessingException If major error occurred.
 */
private void collateNewMainframePayments(ImportStatus importStatus) throws BatchProcessingException {

    try {
        startTransaction();

        StoredProcedureQuery sp = entityManager.createNamedStoredProcedureQuery("BatchCollateNewPayments");

        sp.execute();

        Integer acceptedCount = (Integer) sp.getOutputParameterValue("pAcceptedCount");
        Integer unresolvedCount = (Integer) sp.getOutputParameterValue("pUnresolvedCount");
        Integer suspendedCount = (Integer) sp.getOutputParameterValue("pSuspendedCount");
        Integer acceptedACHCount = (Integer) sp.getOutputParameterValue("pAcceptedACHCount");
        Integer unresolvedACHCount = (Integer) sp.getOutputParameterValue("pUnresolvedACHCount");
        Integer suspendedACHCount = (Integer) sp.getOutputParameterValue("pSuspendedACHCount");

        commitTransaction();

        importStatus.setNumberAcceptedCheckPayments(acceptedCount);
        importStatus.setNumberUnresolvedCheckPayments(unresolvedCount);
        importStatus.setNumberSuspendedCheckPayments(suspendedCount);
        importStatus.setNumberAcceptedAchPayments(acceptedACHCount);
        importStatus.setNumberUnresolvedAchPayments(unresolvedACHCount);
        importStatus.setNumberSuspendedAchPayments(suspendedACHCount);
    } catch (PersistenceException pe) {
        throw new BatchProcessingException("Database Error: CollateNewMainframePayments", pe);
    }

    // Update audit batch log
    todayAuditBatch.setNumberAccepted(
            nullToZero(todayAuditBatch.getNumberAccepted()) + importStatus.getNumberAcceptedCheckPayments());
    todayAuditBatch.setNumberSuspended(
            nullToZero(todayAuditBatch.getNumberSuspended()) + importStatus.getNumberSuspendedCheckPayments());
    todayAuditBatch.setNumberUnresolved(nullToZero(todayAuditBatch.getNumberUnresolved())
            + importStatus.getNumberUnresolvedCheckPayments());
    todayAuditBatch.setNumberAchAccepted(
            nullToZero(todayAuditBatch.getNumberAchAccepted()) + importStatus.getNumberAcceptedAchPayments());
    todayAuditBatch.setNumberAchSuspended(
            nullToZero(todayAuditBatch.getNumberAchSuspended()) + importStatus.getNumberSuspendedAchPayments());
    todayAuditBatch.setNumberAchUnresolved(nullToZero(todayAuditBatch.getNumberAchUnresolved())
            + importStatus.getNumberUnresolvedAchPayments());

    try {
        todayAuditBatch = mergeEntity(todayAuditBatch);
    } catch (PersistenceException pe) {
        throw new BatchProcessingException(
                "Database error while updating audit batch log in collateNewMainframePayments", pe);
    }
}

From source file:gov.opm.scrd.batchprocessing.jobs.BatchProcessingJob.java

/**
 * Creates the General Ledger file given the database data.
 * <p/>/*from w  w  w . j ava2 s. com*/
 * This method does not throw any exception.
 *
 * @param glFileDirectory The directory to create GL file.
 * @param procMessage The process message. Used to build the mail message.
 * @param now The current date.
 * @return true if execution is successful; false otherwise.
 */
private boolean makeGLFile(File glFileDirectory, StringBuilder procMessage, Date now) {
    if (!glFileDirectory.exists() || !glFileDirectory.isDirectory() || !glFileDirectory.canRead()
            || !glFileDirectory.canWrite()) {
        logger.warn("Can not make GL file in directory:" + glFileDirectory);
        procMessage.append(CRLF).append(CRLF).append("Can not make GL file in directory:" + glFileDirectory)
                .append(CRLF);
        return false;
    }

    File outputGLFile = new File(glFileDirectory, "SCGL" + new SimpleDateFormat("yyMMdd").format(now) + ".txt");

    PrintWriter output = null;

    try {
        startTransaction();

        StoredProcedureQuery sp = entityManager.createNamedStoredProcedureQuery("BatchDailyGLFile");
        sp.setParameter("pDayToProcess", now, TemporalType.DATE);
        sp.execute();

        @SuppressWarnings("unchecked")
        List<GLFileRecord> records = sp.getResultList();

        commitTransaction();

        Calendar cal = Calendar.getInstance();
        cal.setTime(now);
        String dayOfYear = String.format("%03d", cal.get(Calendar.DAY_OF_YEAR));

        for (GLFileRecord record : records) {
            StringBuilder line = new StringBuilder("");
            line.append(record.getFeederSystemId());
            line.append(record.getJulianDate());
            line.append(dayOfYear);
            line.append(record.getGlFiller());
            line.append(record.getGlCode());

            int fiscalYear = record.getFiscalYear() == null ? 0 : record.getFiscalYear();
            if (fiscalYear < 1000) {
                line.append(StringUtils.rightPad(record.getGlAccountingCode(), 20));
            } else {
                line.append(fiscalYear % 100);
                line.append("  ");
                line.append(StringUtils.rightPad(record.getGlAccountingCode(), 16));
            }

            line.append(String.format("%015d",
                    record.getRecipientAmount().multiply(BatchProcessHelper.HUNDRED).longValue()));

            line.append(record.getRevenueSourceCode());

            // Pad 28 spaces
            for (int i = 0; i < 28; i++) {
                line.append(" ");
            }

            if (output == null) {
                // Lazily create output file only when there is line to write
                output = new PrintWriter(outputGLFile);
            }
            output.println(line.toString());
        }

        if (output != null) {
            output.flush();
            logger.info("General Ledger file created.");
            procMessage.append(CRLF).append(CRLF).append("General Ledger file created.").append(CRLF);
        } else {
            String info = "There are no GL entries for "
                    + DateFormat.getDateInstance(DateFormat.LONG, Locale.US).format(now)
                    + " so no GL file was created. ";
            logger.info(info);
            procMessage.append(CRLF).append(CRLF).append(info).append(CRLF);
        }

        return true;
    } catch (PersistenceException pe) {
        logger.error("Database error creating the GL file.", pe);
        procMessage.append(CRLF).append(CRLF).append("Database error creating the GL file.").append(CRLF);
        return false;
    } catch (IOException e) {
        logger.error("IO error creating the GL file.", e);
        procMessage.append(CRLF).append(CRLF).append("IO error creating the GL file.").append(CRLF);
        return false;
    } finally {
        if (output != null) {
            output.close();
        }
    }
}

From source file:gov.opm.scrd.batchprocessing.jobs.BatchProcessingJob.java

/**
 * Load the lock box file content, and insert the MainframeImport records into database.
 *
 * @param inputFile The lock box file to load
 * @param importStatus The import status
 * @return The MainframeImport records inserted into database
 * @throws BatchProcessingException If major error occurred.
 *//*  w ww.ja va  2s.  co  m*/
private List<MainframeImport> loadFileContent(File inputFile, ImportStatus importStatus)
        throws BatchProcessingException {

    importStatus.setInputName(inputFile.getName());

    Date fileArrivalDate = new Date(inputFile.lastModified());

    SimpleDateFormat df = new SimpleDateFormat("MMddyy");
    String achDate = df.format(fileArrivalDate);

    Calendar calendar = Calendar.getInstance();
    calendar.setTime(fileArrivalDate);
    calendar.add(Calendar.DAY_OF_MONTH, -1);

    String checkDate = df.format(calendar.getTime());

    String uniqueFileName = "SC" + checkDate + "_" + todayAuditBatch.getId();
    Date importDate = new Date();

    boolean goodDataInFile = false;
    BufferedReader reader = null;
    String line;
    List<MainframeImport> mainFrames = new ArrayList<MainframeImport>();

    // Read input file, insert text line into MainframeImport
    try {
        reader = new BufferedReader(new FileReader(inputFile));
        while ((line = reader.readLine()) != null) {
            line = line.replaceAll("\0", "\040"); // octal 040 = 32, the space character

            if (line.length() < 2) {
                logger.warn("Weird character is: [" + line + "]");
            } else {
                importStatus.setNumberLinesInFile(importStatus.getNumberLinesInFile() + 1);
                boolean achFlag = false;
                if (line.startsWith("R6")) {
                    achFlag = line.length() > 37 && line.charAt(37) == '1';
                    achFlag = achFlag || (line.length() >= 36 && achDate.equals(line.substring(30, 36)));
                }

                try {
                    startTransaction();

                    StoredProcedureQuery sp = entityManager
                            .createNamedStoredProcedureQuery("BatchMainframeImportInsert");
                    sp.setParameter("pRecordString", line);
                    sp.setParameter("pImportDate", importDate);
                    sp.setParameter("pACHFlag", achFlag);
                    sp.setParameter("pFileName", uniqueFileName);
                    sp.setParameter("pAuditBatchId", todayAuditBatch.getId());

                    sp.execute();

                    @SuppressWarnings("unchecked")
                    List<MainframeImport> mainframeImports = sp.getResultList();
                    if (mainframeImports != null && !mainframeImports.isEmpty()) {
                        mainFrames.add(mainframeImports.get(0));
                        goodDataInFile = true;
                    }

                    commitTransaction();
                } catch (PersistenceException pe) {
                    // Here need raise exception
                    throw new BatchProcessingException("Text Line import error: " + line, pe);
                }
            }
        }

    } catch (IOException e) {
        // Here need raise exception
        throw new BatchProcessingException("IO Error reading line from file: " + inputFile, e);
    } finally {
        if (reader != null) {
            try {
                reader.close();
            } catch (IOException e) {
                // Log a warn message and ignore
                logger.warn("Error closing input stream of " + inputFile, e);
            }
        }
    }

    // Import successful, backup the input file
    String newFileNamePrefix = goodDataInFile ? "Backup_" : "Dupe_";
    int fileCounter = 1;
    File backFile = new File(inputDirectoryPath, newFileNamePrefix + uniqueFileName + ".txt");
    while (backFile.exists()) {
        backFile = new File(inputDirectoryPath,
                newFileNamePrefix + uniqueFileName + "_" + fileCounter + ".txt");
    }
    inputFile.renameTo(backFile);
    importStatus.setOutputName(backFile.getName());

    logger.info(importStatus.getNumberLinesInFile() + " lines loaded from " + importStatus.getInputName()
            + " which is backup to " + importStatus.getOutputName());

    return mainFrames;
}

From source file:gov.opm.scrd.batchprocessing.jobs.BatchProcessingJob.java

/**
 * Process import record: R or C line./*from   w ww  .ja v  a 2s. co m*/
 *
 * @param mainFrame The MainframeImport record.
 * @param importStatus The import status.
 * @return The record type result.
 * @throws BatchProcessingException If major error occurred.
 */
private MainframeRecordType processImportRecord(MainframeImport mainFrame, ImportStatus importStatus)
        throws BatchProcessingException {

    String recordString = mainFrame.getRecordString();

    // Parse the record text
    rFile.parseFileLine(recordString);
    rFile.setAchPaymentFlag(
            rFile.getPaymentType() == LockboxPaymentType.ACH || mainFrame.getAchFlag() == Boolean.TRUE);

    String errorText = null;
    MainframeRecordType returnType = MainframeRecordType.NOT_A_RECORD;

    if (rFile.getValidFileRecord() == Boolean.TRUE) {

        if (rFile.getTransactionCode().equals("R")) {

            importStatus.setTransactionsTotal(importStatus.getTransactionsTotal().add(rFile.getAmount()));

            try {
                StoredProcedureQuery sp = entityManager
                        .createNamedStoredProcedureQuery("BatchInputBankPayments");
                sp.setParameter("pPayTransBatchNumber", rFile.getCdNumber());
                sp.setParameter("pPayTransBlockNumber", rFile.getBlockNumber());
                sp.setParameter("pPayTransSequenceNumber", rFile.getSequenceNumber());
                sp.setParameter("pSCMClaimNumber", rFile.getClaimNumber());
                sp.setParameter("pSCMDateOfBirth", rFile.getDateOfBirth(), TemporalType.DATE);
                sp.setParameter("pPayTransPaymentAmount", rFile.getAmount());
                sp.setParameter("pPayTransTransactionDate", rFile.getCdDate(), TemporalType.DATE);
                sp.setParameter("pACHPaymentFlag", rFile.getAchPaymentFlag());
                sp.setParameter("pNetworkId", batchProcessUser.getNetworkId());

                sp.execute();

                String returnCode = (String) sp.getOutputParameterValue("pReturn");
                Long paymentTransactionKey = (Long) sp.getOutputParameterValue("pPayTransactionKey");

                if ("0".equals(returnCode)) {
                    mainFrame.setPaymentTransactionId(paymentTransactionKey);
                    returnType = MainframeRecordType.VALID_R_TRANSACTION;
                } else if ("17".equals(returnCode)) {
                    returnType = MainframeRecordType.DUPLICATE_RECORD;
                    errorText = "Duplicate payment record based on year, batch, block, sequence, amount and date: "
                            + recordString;
                } else {
                    returnType = MainframeRecordType.BAD_R_TRANSACTION;
                    errorText = "Unknown R Transaction error (" + returnCode + "): " + recordString;
                }
            } catch (PersistenceException pe) {
                // Fill import status for logging
                fillImportStatus(importStatus, MainframeRecordType.BAD_R_TRANSACTION);
                errorText = "Database Error while inserting payment transaction record: " + recordString;
                throw new BatchProcessingException(errorText, pe);
            }
        } else {
            try {

                StoredProcedureQuery sp = entityManager
                        .createNamedStoredProcedureQuery("BatchPerformBankChanges");

                sp.setParameter("pSCMClaimnumber", rFile.getClaimNumber());
                sp.setParameter("pSCMDateOfBirth", rFile.getDateOfBirth(), TemporalType.DATE);
                sp.setParameter("pFieldNumber", rFile.getIndexOfAccountFieldToCorrect());
                sp.setParameter("pDataElement", rFile.getCorrectedData());

                String[] address = captureChanges(true, rFile.getCorrectedData());
                String[] name = captureChanges(false, rFile.getCorrectedData());

                sp.setParameter("pSCMCity", address[0]);
                sp.setParameter("pSCMState", address[1]);
                sp.setParameter("pSCMZipcode", address[2]);
                sp.setParameter("pSCMFName", name[0]);
                sp.setParameter("pSCMMInitial", name[1]);
                sp.setParameter("pSCMLastname", name[2]);
                sp.setParameter("pSCMSuffix", name[3]);

                sp.execute();

                Integer updatedCount = (Integer) sp.getOutputParameterValue("pUpdateCount");

                String returnCode = (String) sp.getOutputParameterValue("pErrorCode");

                if ("0".equals(returnCode)) {
                    if (updatedCount <= 0) {
                        returnType = MainframeRecordType.NOT_MATCH_C_TRANSACTION;
                        logger.warn("Change command did not update database for CSD #" + rFile.getClaimNumber()
                                + " Birthdate " + rFile.getDateOfBirth() + ": " + recordString);
                    } else {
                        returnType = MainframeRecordType.VALID_C_TRANSACTION;
                    }
                } else if ("-97".equals(returnCode)) {
                    returnType = MainframeRecordType.BAD_C_TRANSACTION;
                    errorText = "Invalid field number. The program does not accept changes to field # "
                            + rFile.getIndexOfAccountFieldToCorrect() + ": " + recordString;
                } else {
                    returnType = MainframeRecordType.BAD_C_TRANSACTION;
                    errorText = "Unknown C Transaction error (" + returnCode + "): " + recordString;
                }
            } catch (PersistenceException pe) {
                // Fill import status for logging
                fillImportStatus(importStatus, MainframeRecordType.BAD_C_TRANSACTION);
                errorText = "Database Error while changing the applicant record: " + recordString;
                throw new BatchProcessingException(errorText, pe);
            }
        }
    } else {
        if ("C".equals(rFile.getTransactionCode())) {
            returnType = MainframeRecordType.BAD_C_TRANSACTION;
        } else if ("R".equals(rFile.getTransactionCode())) {
            returnType = MainframeRecordType.BAD_R_TRANSACTION;
        } else {
            returnType = MainframeRecordType.NOT_A_RECORD;
        }
        errorText = "Text Line is not a valid Transaction Format [" + rFile.getRecordImportError() + "]: "
                + recordString;
    }

    if (errorText != null) {
        logger.error("Error importing the record from the MainframeImport table in"
                + " processImportRecord module: " + returnType + ". " + errorText);
    }

    return returnType;
}