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