目前做的项目,要把Excel文件中的数据更新到DB2数据库中。就写了个存储过程,通过JAVA调用这个DB2存储过程。如下:
JAVA方法
/**
* 更新用户表
*
* @param userId
* @param userName
* @return
*/
public void updateDb2Data(List dataList) {
System.out.println("UpdateDb2DataDB-->updateDb2Data-->Start.");
CallableStatement stmt = null;
try {
stmt = conn.prepareCall("{call WTMS.UPDATE_UPDATEDB2DATA(?,?,?)}");
for (UpdateDb2DataStruct struct : dataList) {
int condIndex = 1;
stmt.setString(condIndex++, struct.getUserId());
stmt.setString(condIndex++, struct.getUserName());
stmt.registerOutParameter(condIndex++, Types.VARCHAR);
stmt.execute();
String errorMessage = stmt.getString(3); // 3表示,输出函数位于此存储过程参数列表第三个
if (errorMessage != null && !"".equals(errorMessage)) {
System.out.println("errorMessage="+errorMessage);
}
}
conn.commit();
} catch (SQLException e) {
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
System.out.println(e.getSQLState());
e.printStackTrace();
} finally {
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
System.out.println("UpdateDb2DataDB-->updateDb2Data-->End.");
}
[/code]
存储过程
[code lang="sql"]
CREATE OR REPLACE PROCEDURE UPDATE_UPDATEDB2DATA (
IN P_USERID VARCHAR (17),
IN P_USERNAME VARCHAR (20),
OUT P_ERROR VARCHAR (1000))
SPECIFIC UPDATE_UPDATEDB2DATA
DYNAMIC RESULT SETS 1
F1: BEGIN
--具体功能代码省略了
SET P_ERROR = P_USERID || ',' || P_USERNAME;
END