📄 gbs_rfpattach_db.java
字号:
ReturnValue returnValueD = new ReturnValue();
MessageList messageList = new MessageList();
returnValueD.setMessageList(messageList);
StringBuffer sqlLock = new StringBuffer();
StringBuffer sql = new StringBuffer();
sqlLock.append(" SELECT ");
sqlLock.append(" CUSTOMER_ID, RFP_NO, LINE_NO ");
sqlLock.append(" FROM ");
sqlLock.append(" T_RFP_ATTACH ");
sqlLock.append(" WHERE ");
sqlLock.append(" CUSTOMER_ID ='" + in_st.getCustomerID() + "'");
sqlLock.append(" AND RFP_NO ='" + in_st.getRfpNo() + "'");
sqlLock.append(" AND LINE_NO ='" + in_st.getLineNo() + "'");
sqlLock.append(" FOR UPDATE");
sql.append(" UPDATE ");
sql.append(" T_RFP_ATTACH ");
sql.append(" SET ");
sql.append(" TITLE = '" + in_st.getTitle() + "', ");
if (in_st.getFileDeleteFlg()) {
sql.append(" ATTACHMENT_FILE_NAME = '', ");
sql.append(" ATTACHMENT_FILE=null, ");
}
sql.append(" T_RFP_ATTACH.UPDATE_DATE = SYSDATE,");
sql.append(" T_RFP_ATTACH.UPDATE_USER = '" + userID + "' ");
sql.append(" WHERE ");
sql.append(" CUSTOMER_ID ='" + in_st.getCustomerID() + "'");
sql.append(" AND RFP_NO ='" + in_st.getRfpNo() + "'");
sql.append(" AND LINE_NO ='" + in_st.getLineNo() + "'");
ResultSet rset = null;
Statement st = null;
PreparedStatement pstmt = null;
BLOB blob = null;
int lineNo = 1;
int bresult = 0;
try {
st = conn.createStatement();
rset = st.executeQuery(sqlLock.toString());
if (rset.next()) {
} else {
//僨乕僞偑側偄
messageList.setMessage("", "", "10000004", Integer.MIN_VALUE);
returnValueD.setBussinessError();
bresult = -1;
}
if (!returnValueD.isError()) {
pstmt = conn.prepareStatement(sql.toString());
bresult = pstmt.executeUpdate();
if (bresult == 0) {
messageList.setMessage("", "", "10000014", Integer.MIN_VALUE);
returnValueD.setBussinessError();
}
if (bresult > 0 && !in_st.getSeqNo().equals("")) {
returnValueD = updateAttachmentFile(in_st, conn);
}
}
} catch (Exception exception) {
System.out.println("[Error Happen!]");
System.out.println("[Start Trace]");
exception.printStackTrace();
System.out.println("[End Trace]");
throw exception;
} finally {
//release db
try {
if (rset != null) {
rset.close();
}
if (st != null) {
st.close();
}
//pstmt
if (pstmt != null) {
pstmt.close();
}
} catch (SQLException se) {
throw se;
}
}
returnValueD.setDataValue(new Integer(bresult));
return returnValueD;
}
public ReturnValue saveRfpAttachment(List list, String userID) throws Exception {
//Add by Gxk 2004/09/10 Start
userID = BaseCommonCheck.convertSql(userID);
//Add by Gxk 2004/09/10 End
StringBuffer sql = new StringBuffer();
ReturnValue returnValueD = new ReturnValue();
ResultSet rset = null;
Connection conn = null;
Statement st = null;
PreparedStatement pstmt = null;
BLOB blob = null;
int ret = 0;
//excution result
int bresult = 0;
String lineNo = "";
GBS_RfpAttachList_stBean RfpAttachList_stBean = new GBS_RfpAttachList_stBean();
try {
conn = this.datasource.getConnection();
for (int i = 0; i < list.size(); i++) {
RfpAttachList_stBean = (GBS_RfpAttachList_stBean) list.get(i);
lineNo = RfpAttachList_stBean.getLineNo();
if (lineNo.charAt(0) == 'A') {
lineNo = "";
}
//no modify
if (!lineNo.equals("") && !RfpAttachList_stBean.getRowUpdateFlg()) {
continue;
}
//update
if (!lineNo.equals("")
&& RfpAttachList_stBean.getRowUpdateFlg()
&& !RfpAttachList_stBean.getRecordDeleteFlg()) {
returnValueD = updateRfpAttachment(RfpAttachList_stBean, conn, userID);
if (returnValueD.isError()) {
break;
}
}
//delete
if (!lineNo.equals("")
&& RfpAttachList_stBean.getRowUpdateFlg()
&& RfpAttachList_stBean.getRecordDeleteFlg()) {
returnValueD = deleteRfpAttachment(RfpAttachList_stBean, conn, userID);
if (returnValueD.isError()) {
break;
}
}
//insert
if (lineNo.equals("") && !RfpAttachList_stBean.getRecordDeleteFlg()) {
returnValueD = insertRfpAttachment(RfpAttachList_stBean, conn, userID);
if (returnValueD.isError()) {
break;
}
}
}
} catch (Exception exception) {
System.out.println("[Error Happen!]");
System.out.println("[Start Trace]");
exception.printStackTrace();
System.out.println("[End Trace]");
throw exception;
} finally {
//release db
try {
if (!returnValueD.isError()) {
conn.commit();
} else {
conn.rollback();
}
if (rset != null) {
rset.close();
}
if (st != null) {
st.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException se) {
throw se;
}
}
returnValueD.setDataValue(new Integer(bresult));
return returnValueD;
}
/**
* update AttachmentFile by sql
* @return boolean
* @exception Exception Exception for information of other errors
* @since 2004/08/02
*/
public ReturnValue updateAttachmentFile(GBS_RfpAttachList_stBean in_st, Connection conn) throws Exception {
//Add by Gxk 2004/09/10 Start
in_st.setTitle(BaseCommonCheck.convertSql(in_st.getTitle()));
in_st.setFiletype(BaseCommonCheck.convertSql(in_st.getFiletype()));
in_st.setAttachmentFileName(BaseCommonCheck.convertSql(in_st.getAttachmentFileName()));
in_st.setUpdateDate(BaseCommonCheck.convertSql(in_st.getUpdateDate()));
in_st.setUpdateUser(BaseCommonCheck.convertSql(in_st.getUpdateUser()));
in_st.setCreateDate(BaseCommonCheck.convertSql(in_st.getCreateDate()));
in_st.setCreateUser(BaseCommonCheck.convertSql(in_st.getCreateUser()));
in_st.setLineNo(BaseCommonCheck.convertSql(in_st.getLineNo()));
in_st.setCustomerID(BaseCommonCheck.convertSql(in_st.getCustomerID()));
in_st.setRfpNo(BaseCommonCheck.convertSql(in_st.getRfpNo()));
in_st.setAttachDate(BaseCommonCheck.convertSql(in_st.getAttachDate()));
in_st.setUpdateUserName(BaseCommonCheck.convertSql(in_st.getUpdateUserName()));
in_st.setCreateUserName(BaseCommonCheck.convertSql(in_st.getCreateUserName()));
in_st.setSeqNo(BaseCommonCheck.convertSql(in_st.getSeqNo()));
//Add by Gxk 2004/09/10 End
StringBuffer sql = new StringBuffer();
ReturnValue returnValueD = new ReturnValue();
MessageList messageList = new MessageList();
returnValueD.setMessageList(messageList);
int ret = 0;
String fileName = "";
//SQL DEFINE
// start sql ===========================
sql.append(" SELECT");
sql.append(" ATTACHMENT_FILE_NAME, ATTACHMENT_FILE");
sql.append(" FROM ");
sql.append(" T_TEMP ");
sql.append(" WHERE ");
sql.append(" SEQ_NO =");
sql.append("'" + in_st.getSeqNo() + "'");
System.out.println("[INFO] sql = " + sql);
// start sql ===========================
ResultSet rset = null;
ResultSet rsetEx = null;
Statement st = null;
PreparedStatement pstmt = null;
BLOB blob = null;
BLOB blobIn = null;
try {
ReturnValue returnValue = deleteAttachmentFile(in_st, conn);
if (returnValue.isError()) {
if (returnValue.isBussinessError()) {
messageList.addAll(returnValue.getMessageList());
returnValueD.setBussinessError();
}
returnValueD.setErrorCode(returnValue.getErrorCode());
returnValueD.setErrorMessage(returnValue.getErrorMessage());
}
st = conn.createStatement();
rset = st.executeQuery(sql.toString());
if (rset.next()) {
blob = (BLOB) rset.getBlob("ATTACHMENT_FILE");
fileName = rset.getString("ATTACHMENT_FILE_NAME");
} else {
ret = -1;
}
if (ret >= 0) {
sql = new StringBuffer();
sql.append(" SELECT");
sql.append(" ATTACHMENT_FILE_NAME, ATTACHMENT_FILE");
sql.append(" FROM ");
sql.append(" T_RFP_ATTACH ");
sql.append(" WHERE ");
sql.append(" CUSTOMER_ID = '" + in_st.getCustomerID() + "'");
sql.append(" AND RFP_NO = '" + in_st.getRfpNo() + "'");
sql.append(" AND LINE_NO = '" + in_st.getLineNo() + "'");
sql.append(" FOR UPDATE ");
//sql.append( " SEQ_NO =" );
//sql.append( "'" + RfpAttachList_stBean.getSeqNo() + "'" );
System.out.println("[INFO] sql = " + sql);
rsetEx = st.executeQuery(sql.toString());
if (rsetEx.next()) {
blobIn = (BLOB) rsetEx.getBlob("ATTACHMENT_FILE");
} else {
messageList.setMessage("", "", "10000004", Integer.MIN_VALUE);
returnValueD.setBussinessError();
ret = -1;
}
}
//read
if (ret == 0) {
sql = new StringBuffer();
sql.append(" UPDATE T_RFP_ATTACH ");
sql.append(" SET ");
sql.append(" ATTACHMENT_FILE = ? ,");
sql.append(" ATTACHMENT_FILE_NAME = ?");
sql.append(" WHERE ");
sql.append(" CUSTOMER_ID = ?");
sql.append(" AND RFP_NO = ?");
sql.append(" AND LINE_NO = ?");
pstmt = conn.prepareStatement(sql.toString());
OutputStream out = blobIn.getBinaryOutputStream();
InputStream in = blob.getBinaryStream();
byte[] buf = new byte[1024];
int byteRead = 0;
while ((byteRead = in.read(buf)) > 0) {
out.write(buf, 0, byteRead);
}
out.close();
in.close();
pstmt.setBlob(1, blobIn);
pstmt.setString(2, fileName);
pstmt.setString(3, in_st.getCustomerID());
pstmt.setInt(4, new Integer(in_st.getRfpNo()).intValue());
pstmt.setInt(5, new Integer(in_st.getLineNo()).intValue());
ret = pstmt.executeUpdate();
if (ret <= 0) {
messageList.setMessage("", "", "10000014", Integer.MIN_VALUE);
returnValueD.setBussinessError();
}
}
} catch (Exception exception) {
System.out.println("[Error Happen!]");
System.out.println("[Start Trace]");
exception.printStackTrace();
System.out.println("[End Trace]");
throw exception;
} finally {
returnValueD.setDataValue(new Integer(ret));
//release db
try {
if (rset != null) {
rset.close();
}
if (rsetEx != null) {
rsetEx.close();
}
if (pstmt != null) {
pstmt.close();
}
if (st != null) {
st.close();
}
} catch (SQLException se) {
System.out.println("[Error Happen!]");
System.out.println("[Start Trace]");
se.printStackTrace();
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -