最近在做一个需要调用存储过程的功能,大多数的写法是:
Connection conn=null;
CallableStatement csmt=null;
try {
conn=JDBCUtils.getConnection();
conn.setAutoCommit(false);
csmt=conn.prepareCall("call prc_1(?,?,?)");
csmt.setInt(1,80);
csmt.setString(2,"ioc");
csmt.setString(3,"fhp");
csmt.execute();
conn.commit();
System.out.println("success insert data");
} catch (SQLException e) {
e.printStackTrace();
}
但是在运用的过程是没有效果的,后来找了另外的方法,使用了jpa注解,代码如下:
1. 存储过程需要依赖在实体上,在实体上加上@NamedStoredProcedureQueries注解,@NamedStoredProcedureQuery中name为java的方法名,procedureName为存储过程的名字,@StoredProcedureParameter中mode代表入参,name是参数名,type是参数类型
@Entity
Table(name = "d_cp_delivery")
@NamedStoredProcedureQueries({ @NamedStoredProcedureQuery(name = "closePurchaseorder", procedureName = "SRMANLI.closePurchaseorder", parameters = { @StoredProcedureParameter(mode = ParameterMode.IN, name = "i_deliveryid", type = Long.class) }) })
2.调用方法如下
//java代码
EntityManager em;
@Override
@PersistenceContext(unitName = "srment")
public void setEntityManager(EntityManager em) {
super.setEntityManager(em);
this.em = em;
}
@Override
public void closePurchaseorder(Long deliveryId) {
Query qry = em.createNativeQuery("{call srmanli.closePurchaseorder(?1)}");
qry.setParameter(1, deliveryId);
qry.executeUpdate();
em.clear();
}
//存储过程
procedure closePurchaseorder(i_deliveryid in number) is
v_PurchaseorderDtlNum number; --
v_reqtime date := sysdate; --请求时间
v_err_msg clob; --异常
begin
end;