Java 类javax.persistence.StoredProcedureQuery 实例源码

@Test
public void calculate() {
    log.info("... calculate ...");
    EntityManager em = emf.createEntityManager();
       em.getTransaction().begin();

    // define the stored procedure
    StoredProcedureQuery query = em.createStoredProcedureQuery("calculate");
    query.registerStoredProcedureParameter("x", Double.class, ParameterMode.IN);
    query.registerStoredProcedureParameter("y", Double.class, ParameterMode.IN);
    query.registerStoredProcedureParameter("sum", Double.class, ParameterMode.OUT);

    // set input parameter
    query.setParameter("x", 1.23d);
    query.setParameter("y", 4d);

    // call the stored procedure and get the result
    query.execute();
    Double sum = (Double) query.getOutputParameterValue("sum");
    log.info("Calculation result: 1.23 + 4 = " + sum);

       em.getTransaction().commit();
       em.close();
}

@Test
public void calculate() {

    EntityManager em = emf.createEntityManager();
       em.getTransaction().begin();

    StoredProcedureQuery query = em.createNamedStoredProcedureQuery("calculate");
    query.setParameter("x", 1.23d);
    query.setParameter("y", 4d);
    query.execute();
    Double sum = (Double) query.getOutputParameterValue("sum");
    log.info("Calculation result: 1.23 + 4 = " + sum);

       em.getTransaction().commit();
       em.close();
}

/**
 * Creates a Backup of the Database in the directory specified in
 * config.properties.
 *
 * @param name the name of the Backup.
 *
 * @return Backup entinty.
 *
 * @throws QueryTimeoutException if the query should fail.
 * @throws PersistenceException if persisting should fail.
 * @Throws IOException if config.properties is not readable.
 */
public Backup runBackup(String name) throws QueryTimeoutException,
       PersistenceException, IOException {
    Properties props = ServerProperties.getProperties();
    Date date = new Date();
    String path = props.getProperty(dirPropertyKey)
            + name + "_" + getDateAsString(date);

    StoredProcedureQuery query = em.createStoredProcedureQuery(
            "SYSCS_UTIL.SYSCS_BACKUP_DATABASE");
    query.registerStoredProcedureParameter(1, String.class,
            ParameterMode.IN);
    query.setParameter(1, path);
    query.execute();
    log.debug("Backup query executed!");

    Backup backup =  generateBackup(name, path, date, getDirectorySize(new File(path)));

    return backup;
}

@Test
public void testHibernateProcedureCallMultipleOutParameter() {
    doInJPA(entityManager -> {
        StoredProcedureQuery query = entityManager
            .createStoredProcedureQuery("getStatistics")
            .registerStoredProcedureParameter(
                    "A", Long.class, ParameterMode.OUT)
            .registerStoredProcedureParameter(
                    "B", Long.class, ParameterMode.OUT)
            .registerStoredProcedureParameter(
                    "C", Long.class, ParameterMode.OUT);

        query.execute();

        Long a = (Long) query
                .getOutputParameterValue("A");
        Long b = (Long) query
                .getOutputParameterValue("B");
        Long c = (Long) query
                .getOutputParameterValue("C");
    });
}

@Test
public void testStoredProcedureRefCursor() {
    try {
        doInJPA(entityManager -> {
            StoredProcedureQuery query = entityManager.createStoredProcedureQuery("post_comments");
            query.registerStoredProcedureParameter(1, Long.class, ParameterMode.IN);
            query.registerStoredProcedureParameter(2, Class.class, ParameterMode.REF_CURSOR);
            query.setParameter(1, 1L);

            query.execute();
            List<Object[]> postComments = query.getResultList();
            assertNotNull(postComments);
        });
    } catch (Exception e) {
        assertTrue(Pattern.compile("Dialect .*? not known to support REF_CURSOR parameters").matcher(e.getCause().getMessage()).matches());
    }
}

@Test
public void testFunction() {
    try {
        doInJPA(entityManager -> {
            StoredProcedureQuery query = entityManager.createStoredProcedureQuery("fn_count_comments");
            query.registerStoredProcedureParameter("postId", Long.class, ParameterMode.IN);

            query.setParameter("postId", 1L);

            Long commentCount = (Long) query.getSingleResult();
            assertEquals(Long.valueOf(2), commentCount);
        });
    } catch (Exception e) {
        assertTrue(Pattern.compile("PROCEDURE high_performance_java_persistence.fn_count_comments does not exist").matcher(e.getCause().getCause().getMessage()).matches());
    }
}

@Test
public void testStoredProcedureOutParameter() {
    doInJPA(entityManager -> {
        StoredProcedureQuery query = entityManager
            .createStoredProcedureQuery("count_comments")
            .registerStoredProcedureParameter(
                "postId", Long.class, ParameterMode.IN)
            .registerStoredProcedureParameter(
                "commentCount", Long.class, ParameterMode.OUT)
            .setParameter("postId", 1L);

        query.execute();

        Long commentCount = (Long) query.getOutputParameterValue("commentCount");
        assertEquals(Long.valueOf(2), commentCount);
    });
}

@Test
public void testStoredProcedureRefCursor() {
    try {
        doInJPA(entityManager -> {
            StoredProcedureQuery query = entityManager
                .createStoredProcedureQuery("post_comments")
            .registerStoredProcedureParameter(1, Long.class, ParameterMode.IN)
            .registerStoredProcedureParameter(2, Class.class, ParameterMode.REF_CURSOR)
            .setParameter(1, 1L);

            query.execute();
            List<Object[]> postComments = query.getResultList();
            assertNotNull(postComments);
        });
    } catch (Exception e) {
        assertTrue(Pattern.compile("Dialect .*? not known to support REF_CURSOR parameters").matcher(e.getCause().getMessage()).matches());
    }
}

/**
 * Checks whether the current day is a holiday.
 *
 * @param now The current day.
 * @return True if the current date is a holiday, false otherwise.
 * @throws BatchProcessingException If major error occurred.
 */
protected boolean isNowHoliday(Date now) throws BatchProcessingException {
    Calendar calendar = Calendar.getInstance();
    calendar.setTime(now);
    int dayOfWeek = calendar.get(Calendar.DAY_OF_WEEK);

    if (dayOfWeek == Calendar.SUNDAY || dayOfWeek == Calendar.SATURDAY) {
        return true; // Sunday for 0 and Saturday for 6 are holidays
    }

    try {
        startTransaction();

        StoredProcedureQuery sp = entityManager.createNamedStoredProcedureQuery("IsThisHoliday");
        sp.setParameter("pDate2Test", now, TemporalType.DATE);

        Boolean result = (Boolean) sp.getSingleResult();

        commitTransaction();

        return result;
    } catch (PersistenceException pe) {
        throw new BatchProcessingException("Database error checking holiday.", pe);
    }
}

/**
 * Batch daily account update.
 * <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;
    }
}

@Override
public StoredProcedureQuery createStoredProcedureQuery(String procedureName, String... resultSetMappings)
{
    logger.error("Thread: {} using entity Manager {}", Thread.currentThread().getId(), emid);

    return em.createStoredProcedureQuery(procedureName, resultSetMappings);
}

@Test
public void shouldInvokeStoredProcedureWithOutbound() throws Exception {
    utx.begin();
    StoredProcedureQuery query =
        em.createStoredProcedureQuery("DOUBLE_SP")
        .registerStoredProcedureParameter(
            "P1", Float.class, ParameterMode.IN)
        .registerStoredProcedureParameter(
            "P2", Float.class, ParameterMode.OUT)
        .setParameter("P1", new Float(2.345));
    boolean status = query.execute();
    System.out.printf("****************** status=%s\n", status);
    assertFalse( query.hasMoreResults());
    Double p2 = ( Double)query.getOutputParameterValue("P2");
    System.out.printf("The output P2 was %12.7f\n", p2 );
    utx.commit();
}

@Test
public void shouldInvokeStoredProcedureWithOutbound() throws Exception {
    StoredProcedureQuery query =
        em.createStoredProcedureQuery("COMPOUND_INTEREST_SP")
        .registerStoredProcedureParameter(
            "P", Float.class, ParameterMode.IN)
        .registerStoredProcedureParameter(
            "r", Float.class, ParameterMode.IN)
        .registerStoredProcedureParameter(
            "n", Integer.class, ParameterMode.IN)
        .registerStoredProcedureParameter(
            "t", Integer.class, ParameterMode.IN)
        .registerStoredProcedureParameter(
            "A", Float.class, ParameterMode.OUT)
        .setParameter("P", new Float(1500))
        .setParameter("r", new Float(0.043))
        .setParameter("n", new Integer(4))
        .setParameter("t", new Integer(6));
    // P = 1500, r = 0.043 (4.3%), n = 4, and t = 6:
    boolean status = query.execute();
    System.out.printf("****************** status=%s\n", status);
    assertFalse( query.hasMoreResults());
    Double A = ( Double)query.getOutputParameterValue("A");
    System.out.printf("The output A was %12.7f\n", A );
    assertEquals( 1938.84, A, 0.005 );
}

@Test
public void shouldInvokeStoredProcedureWithResultSet() throws Exception {
    utx.begin();
    StoredProcedureQuery query =
        em.createStoredProcedureQuery("READ_TAX_SP")
        .registerStoredProcedureParameter(
            "TAX_CODE_ID", Integer.class, ParameterMode.IN)
        .setParameter("TAX_CODE_ID", 101);
    boolean status = query.execute();
    System.out.printf("****************** status=%s\n", status);
    List rs =  query.getResultList();
    assertNotNull(rs);
    System.out.printf("****************** rs=%s\n", rs );
    Object row[] = (Object[])rs.get(0);
    System.out.printf("****************** row=%s\n", row );
    for (int col=0; col<row.length; ++col ) {
        System.out.printf("**** row[%d]=%s\n", col, row[col]  );
    }
    utx.commit();
}

@Test
public void testCallStoreProcedure() {

    StoredProcedureQuery query = entityManager.createStoredProcedureQuery("my_sum");
    query.registerStoredProcedureParameter("x", Integer.class, IN);
    query.registerStoredProcedureParameter("y", Integer.class, IN);

    query.setParameter("x", 5);
    query.setParameter("y", 4);
    query.execute();
    Integer sum = (Integer) query.getSingleResult();
    assertEquals("the sum in the stored procedure", sum, new Integer(9));
}

@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();

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

/**
 * Saves a bid to the database
 * @param bid - bid
 */
public void save(Bid bid) {   
    entityManager.persist(bid);
    StoredProcedureQuery spq = entityManager.createStoredProcedureQuery("getQtyOrders");
    spq.registerStoredProcedureParameter("param1", Integer.class, ParameterMode.IN);
    spq.setParameter("param1",55);
    Object[] count = (Object[])spq.getSingleResult();
    logger.log(Level.INFO, "Single result: {0}", count[0]);
}

@Test
public void testStoredProcedureOutParameter() {
    doInJPA(entityManager -> {
        StoredProcedureQuery query = entityManager.createStoredProcedureQuery("count_comments");
        query.registerStoredProcedureParameter("postId", Long.class, ParameterMode.IN);
        query.registerStoredProcedureParameter("commentCount", Long.class, ParameterMode.OUT);

        query.setParameter("postId", 1L);

        query.execute();
        Long commentCount = (Long) query.getOutputParameterValue("commentCount");
        assertEquals(Long.valueOf(2), commentCount);
    });
}

@Test
public void testStoredProcedureReturnValue() {
    doInJPA(entityManager -> {
        StoredProcedureQuery query = entityManager.createStoredProcedureQuery("post_comments");
        query.registerStoredProcedureParameter(1, Long.class, ParameterMode.IN);

        query.setParameter(1, 1L);

        List<Object[]> postComments = query.getResultList();
        assertEquals(2, postComments.size());
    });
}

@Test
public void testStoredProcedureOutParameter() {
    doInJPA(entityManager -> {
        StoredProcedureQuery query = entityManager
            .createStoredProcedureQuery("count_comments")
            .registerStoredProcedureParameter("postId", Long.class, ParameterMode.IN)
            .registerStoredProcedureParameter("commentCount", Long.class, ParameterMode.OUT)
            .setParameter("postId", 1L);
        query.execute();
        Long commentCount = (Long) query.getOutputParameterValue("commentCount");
        assertEquals(Long.valueOf(2), commentCount);
    });
}

@Test
public void testStoredProcedureRefCursor() {
    doInJPA(entityManager -> {
        StoredProcedureQuery query = entityManager
            .createStoredProcedureQuery("post_comments")
            .registerStoredProcedureParameter(1, void.class, ParameterMode.REF_CURSOR)
            .registerStoredProcedureParameter(2, Long.class, ParameterMode.IN)
            .setParameter(2, 1L);

        List<Object[]> postComments = query.getResultList();
        assertEquals(2, postComments.size());
    });
}

/**
 * @see EntityManager#createNamedStoredProcedureQuery(java.lang.String)
 */
@Override
public StoredProcedureQuery createNamedStoredProcedureQuery(final String name) {

    EntityManager entityManager = null;
    final StoredProcedureQuery query;

    try {
        entityManager = createEntityManagerThatShouldBeClosed();
        query = entityManager.createNamedStoredProcedureQuery(name);
    } finally {
        closeEntityManager(entityManager);
    }
    return query;
}

/**
 * @see EntityManager#createStoredProcedureQuery(java.lang.String)
 */
@Override
public StoredProcedureQuery createStoredProcedureQuery(final String procedureName) {

    EntityManager entityManager = null;
    final StoredProcedureQuery query;

    try {
        entityManager = createEntityManagerThatShouldBeClosed();
        query = entityManager.createStoredProcedureQuery(procedureName);
    } finally {
        closeEntityManager(entityManager);
    }
    return query;
}

/**
 * @see javax.persistence.EntityManager#createStoredProcedureQuery(java.lang.String,
 *      java.lang.String[])
 */
@Override
public StoredProcedureQuery createStoredProcedureQuery(final String procedureName,
        final String... resultSetMappings) {

    return null;
}

@Override
public StoredProcedureQuery createNamedStoredProcedureQuery(final String name) {
    final Timer timer = Op.createNamedStoredProcedureQuery.start(this.timer, this);
    try {
        return getEntityManager().createNamedStoredProcedureQuery(name);
    } finally {
        timer.stop();
    }
}

@Override
public StoredProcedureQuery createStoredProcedureQuery(final String procedureName) {
    final Timer timer = Op.createNamedStoredProcedureQuery.start(this.timer, this);
    try {
        return getEntityManager().createStoredProcedureQuery(procedureName);
    } finally {
        timer.stop();
    }
}

@Override
public StoredProcedureQuery createStoredProcedureQuery(final String procedureName, final Class... resultClasses) {
    final Timer timer = Op.createStoredProcedureQuery.start(this.timer, this);
    try {
        return getEntityManager().createStoredProcedureQuery(procedureName, resultClasses);
    } finally {
        timer.stop();
    }
}

@Override
public StoredProcedureQuery createStoredProcedureQuery(final String procedureName, final String... resultSetMappings) {
    final Timer timer = Op.createStoredProcedureQuery.start(this.timer, this);
    try {
        return getEntityManager().createStoredProcedureQuery(procedureName, resultSetMappings);
    } finally {
        timer.stop();
    }
}

@Test
public void shouldCreateProcedureQuery() {
    String name = "My procedure name";
    StoredProcedureQuery query = mock(StoredProcedureQuery.class);
    when(recordManager.createProcedureQuery(name)).thenReturn(query);
    assertSame(query, dao.createProcedureQuery(name));
}

@Test
public void shouldAggregateResults() {
    StoredProcedureQuery procedure = mock(StoredProcedureQuery.class);
    doReturn(procedure).when(dao).createProcedureQuery("aggregate_results");
    dao.aggregateResults(ID);
    InOrder order = Mockito.inOrder(procedure);
    order.verify(procedure).setParameter("process_id", ID);
    order.verify(procedure).execute();
}

@Override
public StoredProcedureQuery createNamedStoredProcedureQuery(String name)
{
    logger.error("Thread: {} using entity Manager {}", Thread.currentThread().getId(), emid);

    return em.createNamedStoredProcedureQuery(name);
}

@Override
public StoredProcedureQuery createStoredProcedureQuery(String procedureName)
{
    logger.error("Thread: {} using entity Manager {}", Thread.currentThread().getId(), emid);

    return em.createStoredProcedureQuery(procedureName);
}

@Override
public StoredProcedureQuery createStoredProcedureQuery(String procedureName, @SuppressWarnings("rawtypes") Class... resultClasses)
{
    logger.error("Thread: {} using entity Manager {}", Thread.currentThread().getId(), emid);

    return em.createStoredProcedureQuery(procedureName, resultClasses);
}

@Test
public void shouldComputeCompoundInterestRate() throws Exception {
    StoredProcedureQuery query =
            em.createNamedStoredProcedureQuery("Employee.findByRegion")
            .setParameter("REGION_ID", 83001);
    query.execute();
    List list = query.getResultList();
    System.out.printf("**** list=%s\n", list );
    assertNotNull(list);
    List<Employee> employees = (List<Employee>)list;
    assertTrue( employees.size() > 0 );
}

@Override
public StoredProcedureQuery createNamedStoredProcedureQuery(String arg0) {
    return this.original.createNamedStoredProcedureQuery(arg0);
}

@Override
public StoredProcedureQuery createStoredProcedureQuery(String arg0) {
    return this.original.createStoredProcedureQuery(arg0);

}

@Override
public StoredProcedureQuery createStoredProcedureQuery(String arg0, Class... arg1) {
    return this.original.createStoredProcedureQuery(arg0, arg1);

}

@Override
public StoredProcedureQuery createStoredProcedureQuery(String arg0, String... arg1) {
    return this.original.createStoredProcedureQuery(arg0, arg1);

}

@Override
public StoredProcedureQuery createNamedStoredProcedureQuery(String name) {
  // TODO Auto-generated method stub
  return null;
}

@Override
public StoredProcedureQuery createStoredProcedureQuery(String procedureName) {
  // TODO Auto-generated method stub
  return null;
}