最近在做一个需要调用存储过程的功能,大多数的写法是:

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;