Example usage for javax.persistence StoredProcedureQuery setParameter

List of usage examples for javax.persistence StoredProcedureQuery setParameter

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

Introduction

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

Prototype

StoredProcedureQuery setParameter(int position, Object value);

Source Link

Document

Bind an argument value to a positional parameter.

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();// w  w w.  j  a  v  a 2 s  .  co m

    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);
    proc.execute();//from  ww w . j a  v  a  2  s .c  o m

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

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

/**
 * Batch daily account update.//from  ww w . j  a  v a2  s . co  m
 * <p/>
 * This method does not throw any exception.
 *
 * @param procMessage The process message. Used to build the mail message.
 * @return true if execution is successful; false otherwise.
 */
private boolean batchDailyAccountUpdate(StringBuilder procMessage) {
    try {
        startTransaction();

        StoredProcedureQuery sp = entityManager.createNamedStoredProcedureQuery("BatchDailyAccountUpdate");
        sp.setParameter("pAuditBatchIDLog", todayAuditBatch.getId());

        Integer count = (Integer) sp.getSingleResult();

        commitTransaction();

        String msg = "Updated " + count + " master records to history or ACH Stop status.";
        logger.info(msg);
        procMessage.append(CRLF).append(CRLF).append(msg);

        return true;
    } catch (PersistenceException pe) {
        logger.error("ERROR: Updating master records to history or ACH Stop status failed.", pe);
        procMessage.append(CRLF).append(CRLF)
                .append("ERROR: Updating master records to history or ACH Stop status failed. ");
        return false;
    }
}

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  . j  ava2 s  .  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 .  j  a 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;
}