1:java 代码调用存储过程
public String getPriceByProcedureAndMaterialCodeAndCostType(String bridgeCode,
String materialCode, int costType, Date startDate, Date endDate) {
String procedure = "{call P_GET_HIGH_OR_LOW_PRICE(?,?,?,?,?,?)}";
Session session = this.hibernateTemplate.getSessionFactory().getCurrentSession();
Connection conn = session.connection();
CallableStatement cs = null;
try {
cs = conn.prepareCall(procedure);
cs.setString(1, bridgeCode);
cs.setString(2, materialCode);
cs.setInt(3, costType);
if(startDate != null){
cs.setDate(4, new java.sql.Date(startDate.getTime()));
}else{
cs.setDate(4, null);
}
if(endDate != null){
cs.setDate(5, new java.sql.Date(endDate.getTime()));
}else{
cs.setDate(5, null);
}
cs.registerOutParameter(6, Types.VARCHAR);
cs.executeUpdate();
String price_supplier = cs.getString(6);
return price_supplier;
} catch (SQLException e) {
e.printStackTrace();
return "";
} finally{
if(cs != null){
try {
cs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
红色的是带有返回参数的。
2:java 调用方法
public String getMaterialIdFunction(String materialCode,Long supplierId) {
String value="";
//调用存储过程获取材质ID和分类Code
String procedure = "{ ? = call F_GET_MATERIAL_ID(?,?)}";
Session session = this.hibernateTemplate.getSessionFactory()
.getCurrentSession();
@SuppressWarnings("deprecation")
Connection conn = session.connection();
Date startDate=null;
Date endDate1=null;
try {
conn.setAutoCommit(true);
} catch (SQLException e1) {
e1.printStackTrace();
}
try {
CallableStatement cs = conn.prepareCall(procedure);
cs.registerOutParameter(1, Types.VARCHAR);
cs.setString(2, materialCode);
cs.setLong(3, supplierId);
cs.executeUpdate();
value=cs.getString(1);
cs.close();
session.flush();
session.clear();
} catch (SQLException e) {
e.printStackTrace();
}
return value;
}
红色的是调用方法后返回的值。