javax.persistence.ParameterMode Java Examples

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

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

            try {
                query.execute();
                Long commentCount = (Long) query.getOutputParameterValue("commentCount");

                assertEquals(Long.valueOf(2), commentCount);
            } finally {
                query.unwrap(ProcedureOutputs.class).release();
            }

            CallableStatement callableStatement = ReflectionUtils.getFieldValue(query.unwrap(ProcedureOutputs.class), "callableStatement");
            assertTrue(callableStatement.isClosed());
        } catch (SQLException e) {
            fail(e.getMessage());
        }
    });
}
 

@Override
@SuppressWarnings("unchecked")
public ProcedureCallImplementor<R> registerStoredProcedureParameter(int position, Class type, ParameterMode mode) {
	getProducer().checkOpen( true );

	try {
		registerParameter( position, type, mode );
	}
	catch (HibernateException he) {
		throw getExceptionConverter().convert( he );
	}
	catch (RuntimeException e) {
		getProducer().markForRollbackOnly();
		throw e;
	}

	return this;
}
 

@Override
@SuppressWarnings("unchecked")
public ProcedureCallImplementor<R> registerStoredProcedureParameter(String parameterName, Class type, ParameterMode mode) {
	getProducer().checkOpen( true );
	try {
		registerParameter( parameterName, type, mode );
	}
	catch (HibernateException he) {
		throw getExceptionConverter().convert( he );
	}
	catch (RuntimeException e) {
		getProducer().markForRollbackOnly();
		throw e;
	}

	return this;
}
 

private void internalSetValue(T value) {
	if ( procedureParameter.getMode() != ParameterMode.IN && procedureParameter.getMode() != ParameterMode.INOUT ) {
		throw new IllegalStateException( "Can only bind values for IN/INOUT parameters : " + procedureParameter );
	}

	if ( procedureParameter.getParameterType() != null ) {
		if ( value == null ) {
			if ( !procedureParameter.isPassNullsEnabled() ) {
				throw new IllegalArgumentException( "The parameter " +
						( procedureParameter.getName() != null
								? "named [" + procedureParameter.getName() + "]"
								: "at position [" + procedureParameter.getPosition() + "]" )
						+ " was null. You need to call ParameterRegistration#enablePassingNulls(true) in order to pass null parameters." );
			}
		}
		else if ( !procedureParameter.getParameterType().isInstance( value ) &&
				!procedureParameter.getHibernateType().getReturnedClass().isInstance( value ) ) {
			throw new IllegalArgumentException( "Bind value [" + value + "] was not of specified type [" + procedureParameter
					.getParameterType() );
		}
	}

	this.value = value;
	this.isBound = true;
}
 

@Override
@SuppressWarnings("unchecked")
public <T> ParameterRegistration<T> registerParameter(String name, Class<T> type, ParameterMode mode) {
	final ProcedureParameterImpl parameter = new ProcedureParameterImpl(
			this,
			name,
			mode,
			type,
			getSession().getFactory().getTypeResolver().heuristicType( type.getName() ),
			globalParameterPassNullsSetting
	);

	registerParameter( parameter );

	return parameter;
}
 

@Test
public void testProcedureCallMultipleOutParameter() {
    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 testStoredProcedureOutParameter() {
    doInJPA(entityManager -> {
        try {
            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);

            ProcedureOutputs procedureOutputs = query.unwrap(ProcedureOutputs.class);
            CallableStatement callableStatement = ReflectionUtils.getFieldValue(procedureOutputs, "callableStatement");
            assertFalse(callableStatement.isClosed());

            procedureOutputs.release();

            assertTrue(callableStatement.isClosed());
        } catch (SQLException e) {
            fail(e.getMessage());
        }
    });
}
 

@Test
public void deferredStoredProcedureQueryWithIndexedParameters() {
	EntityManagerFactory emf = mock(EntityManagerFactory.class);
	EntityManager targetEm = mock(EntityManager.class);
	StoredProcedureQuery query = mock(StoredProcedureQuery.class);
	given(emf.createEntityManager()).willReturn(targetEm);
	given(targetEm.createStoredProcedureQuery("x")).willReturn(query);
	willReturn("y").given(query).getOutputParameterValue(0);
	willReturn("z").given(query).getOutputParameterValue(2);
	given(targetEm.isOpen()).willReturn(true);

	EntityManager em = SharedEntityManagerCreator.createSharedEntityManager(emf);
	StoredProcedureQuery spq = em.createStoredProcedureQuery("x");
	spq.registerStoredProcedureParameter(0, String.class, ParameterMode.OUT);
	spq.registerStoredProcedureParameter(1, Number.class, ParameterMode.IN);
	spq.registerStoredProcedureParameter(2, Object.class, ParameterMode.INOUT);
	spq.execute();
	assertEquals("y", spq.getOutputParameterValue(0));
	try {
		spq.getOutputParameterValue(1);
		fail("Should have thrown IllegalArgumentException");
	}
	catch (IllegalArgumentException ex) {
		// expected
	}
	assertEquals("z", spq.getOutputParameterValue(2));

	verify(query).registerStoredProcedureParameter(0, String.class, ParameterMode.OUT);
	verify(query).registerStoredProcedureParameter(1, Number.class, ParameterMode.IN);
	verify(query).registerStoredProcedureParameter(2, Object.class, ParameterMode.INOUT);
	verify(query).execute();
	verify(targetEm).close();
	verifyNoMoreInteractions(query);
	verifyNoMoreInteractions(targetEm);
}
 

@Test
public void testHibernateProcedureCallRefCursor() {
    doInJPA(entityManager -> {
        Session session = entityManager.unwrap(Session.class);
        ProcedureCall call = session
            .createStoredProcedureCall("post_comments");
        call.registerParameter(1, void.class, ParameterMode.REF_CURSOR);
        call.registerParameter(2, Long.class, ParameterMode.IN).bindValue(1L);

        Output output = call.getOutputs().getCurrent();
        if (output.isResultSet()) {
            List<Object[]> postComments = ((ResultSetOutput) output).getResultList();
            assertEquals(2, postComments.size());
        }
    });
}
 

@Override
public void setHibernateType(Type expectedType) {
	super.setHibernateType( expectedType );

	if ( mode == ParameterMode.REF_CURSOR ) {
		sqlTypes = new int[] { Types.REF_CURSOR };
	}
	else {
		if ( expectedType == null ) {
			throw new IllegalArgumentException( "Type cannot be null" );
		}
		else {
			sqlTypes = expectedType.sqlTypes( procedureCall.getSession().getFactory() );
		}
	}

}
 

@Test
public void deferredStoredProcedureQueryWithNamedParameters() {
	EntityManagerFactory emf = mock(EntityManagerFactory.class);
	EntityManager targetEm = mock(EntityManager.class);
	StoredProcedureQuery query = mock(StoredProcedureQuery.class);
	given(emf.createEntityManager()).willReturn(targetEm);
	given(targetEm.createStoredProcedureQuery("x")).willReturn(query);
	willReturn("y").given(query).getOutputParameterValue("a");
	willReturn("z").given(query).getOutputParameterValue("c");
	given(targetEm.isOpen()).willReturn(true);

	EntityManager em = SharedEntityManagerCreator.createSharedEntityManager(emf);
	StoredProcedureQuery spq = em.createStoredProcedureQuery("x");
	spq.registerStoredProcedureParameter("a", String.class, ParameterMode.OUT);
	spq.registerStoredProcedureParameter("b", Number.class, ParameterMode.IN);
	spq.registerStoredProcedureParameter("c", Object.class, ParameterMode.INOUT);
	spq.execute();
	assertEquals("y", spq.getOutputParameterValue("a"));
	try {
		spq.getOutputParameterValue("b");
		fail("Should have thrown IllegalArgumentException");
	}
	catch (IllegalArgumentException ex) {
		// expected
	}
	assertEquals("z", spq.getOutputParameterValue("c"));

	verify(query).registerStoredProcedureParameter("a", String.class, ParameterMode.OUT);
	verify(query).registerStoredProcedureParameter("b", Number.class, ParameterMode.IN);
	verify(query).registerStoredProcedureParameter("c", Object.class, ParameterMode.INOUT);
	verify(query).execute();
	verify(targetEm).close();
	verifyNoMoreInteractions(query);
	verifyNoMoreInteractions(targetEm);
}
 

public ProcedureParameterImpl(
		ProcedureCallImpl procedureCall,
		String name,
		ParameterMode mode,
		Class<T> javaType,
		Type hibernateType,
		boolean initialPassNullsSetting) {
	super( hibernateType );
	this.procedureCall = procedureCall;
	this.name = name;
	this.position = null;
	this.mode = mode;
	this.javaType = javaType;
	this.passNullsEnabled = initialPassNullsSetting;

	setHibernateType( hibernateType );
}
 

public ProcedureParameterImpl(
		ProcedureCallImpl procedureCall,
		Integer position,
		ParameterMode mode,
		Class<T> javaType,
		Type hibernateType,
		boolean initialPassNullsSetting) {
	super( hibernateType );
	this.procedureCall = procedureCall;
	this.name = null;
	this.position = position;
	this.mode = mode;
	this.javaType = javaType;
	this.passNullsEnabled = initialPassNullsSetting;

	setHibernateType( hibernateType );
}
 

@Test
public void deferredStoredProcedureQueryWithNamedParameters() {
	EntityManagerFactory emf = mock(EntityManagerFactory.class);
	EntityManager targetEm = mock(EntityManager.class);
	StoredProcedureQuery query = mock(StoredProcedureQuery.class);
	given(emf.createEntityManager()).willReturn(targetEm);
	given(targetEm.createStoredProcedureQuery("x")).willReturn(query);
	willReturn("y").given(query).getOutputParameterValue("a");
	willReturn("z").given(query).getOutputParameterValue("c");
	given(targetEm.isOpen()).willReturn(true);

	EntityManager em = SharedEntityManagerCreator.createSharedEntityManager(emf);
	StoredProcedureQuery spq = em.createStoredProcedureQuery("x");
	spq.registerStoredProcedureParameter("a", String.class, ParameterMode.OUT);
	spq.registerStoredProcedureParameter("b", Number.class, ParameterMode.IN);
	spq.registerStoredProcedureParameter("c", Object.class, ParameterMode.INOUT);
	spq.execute();
	assertEquals("y", spq.getOutputParameterValue("a"));
	try {
		spq.getOutputParameterValue("b");
		fail("Should have thrown IllegalArgumentException");
	}
	catch (IllegalArgumentException ex) {
		// expected
	}
	assertEquals("z", spq.getOutputParameterValue("c"));

	verify(query).registerStoredProcedureParameter("a", String.class, ParameterMode.OUT);
	verify(query).registerStoredProcedureParameter("b", Number.class, ParameterMode.IN);
	verify(query).registerStoredProcedureParameter("c", Object.class, ParameterMode.INOUT);
	verify(query).execute();
	verify(targetEm).close();
	verifyNoMoreInteractions(query);
	verifyNoMoreInteractions(targetEm);
}
 

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

@Test
public void findCarsByYearNoNamed() {
    final StoredProcedureQuery storedProcedure = entityManager.createStoredProcedureQuery("FIND_CAR_BY_YEAR", Car.class)
        .registerStoredProcedureParameter(1, Integer.class, ParameterMode.IN)
        .setParameter(1, 2015);
    storedProcedure.getResultList()
        .forEach(c -> Assert.assertEquals(new Integer(2015), ((Car) c).getYear()));
}
 

/**
 * Create the memento
 *
 * @param position The parameter position
 * @param name The parameter name
 * @param mode The parameter mode
 * @param type The Java type of the parameter
 * @param hibernateType The Hibernate Type.
 * @param passNulls Should NULL values to passed to the database?
 */
public ParameterMemento(
		int position,
		String name,
		ParameterMode mode,
		Class type,
		Type hibernateType,
		boolean passNulls) {
	this.position = position;
	this.name = name;
	this.mode = mode;
	this.type = type;
	this.hibernateType = hibernateType;
	this.passNulls = passNulls;
}
 

@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 testHibernateProcedureCallOutParameter() {
    doInJPA(entityManager -> {
        Session session = entityManager.unwrap(Session.class);
        ProcedureCall call = session.createStoredProcedureCall("count_comments");
        call.registerParameter("postId", Long.class, ParameterMode.IN).bindValue(1L);
        call.registerParameter("commentCount", Long.class, ParameterMode.OUT);

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

public Integer updateBoardWithMessage(Long messageId) {
	register.registerMarshaller();

	StoredProcedureQuery storedProcedureQuery = em.createStoredProcedureQuery( "UpdateUserBoardTask" );
	storedProcedureQuery.registerStoredProcedureParameter( "messageId", Long.class, ParameterMode.IN );
	storedProcedureQuery.setParameter( "messageId", messageId );

	return (Integer) storedProcedureQuery.getSingleResult();
}
 

@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 testHibernateProcedureCallReturnValueParameter() {
    doInJPA(entityManager -> {
        Session session = entityManager.unwrap(Session.class);
        ProcedureCall call = session.createStoredProcedureCall("post_comments");
        call.registerParameter(1, Long.class, ParameterMode.IN).bindValue(1L);

        Output output = call.getOutputs().getCurrent();
        if (output.isResultSet()) {
            List<Object[]> postComments = ((ResultSetOutput) output).getResultList();
            assertEquals(2, postComments.size());
        }
    });
}
 

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

@Test
public void testStoredProcedureOutParameterDefaultClose() {
    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 testProcedureCallParameterDefaultClose() {
    doInJPA(entityManager -> {
        Session session = entityManager.unwrap(Session.class);

        ProcedureCall call = session.createStoredProcedureCall("count_comments");
        call.registerParameter("postId", Long.class, ParameterMode.IN).bindValue(1L);
        call.registerParameter("commentCount", Long.class, ParameterMode.OUT);

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