Simple Java example to call a stored procedure in JPA / JDBC

Example 1: 
public void myMethod(Lists list, Account account) {

Query query = em.createNativeQuery("call example_procedure(?,?);");

query.setParameter(1, list.getListid()).setParameter(2,

account.getUserid());

query.executeUpdate();

}
or

public void myMethod(Lists list, Account account) {

em.createNativeQuery("call example_procedure(?p1,?p2);")

.setParameter("p1", list.getListid())

.setParameter("p2", account.getUserid()).executeUpdate();

}
Example 2:
// for getting the result back.
String q = "call `i88ca`.`example_procedure2`();";

Object o = em.createNativeQuery(q).getSingleResult();

// or

List l = em.createNativeQuery(q).getResultList();


PROCEDURE `example_procedure2`()

BEGIN

select 8888;

-- or any other select statements for output.

END
Example 3:
@Resource(name = "db2")
private DataSource dataSource;

public void changeAccount(Integer listid, Integer accountid) throws SQLException {

Connection conn = dataSource.getConnection();

CallableStatement cs =conn.prepareCall("call test(?,?)");

cs.setInt(2, 3);

cs.execute();

cs.close();

conn.close();
}
Example 4:
CallableStatement cs = conn.prepareCall("call test(?,?,?)");
cs.setInt(2, 3);
cs.registerOutParameter(3, Types.INTEGER);
cs.execute();
// Get the value of the output parameter.
int sum = cs.getInt(3);
cs.close();
conn.close();
You may want to check that:
Stored Procedures should be considered database assembly language: for use in only the most performance critical situations.

More info from here and here

See also:

Example of named native query in JPA

Comments

Popular posts from this blog

Check MySQL query history from command line

Installing chocolatey on Windows