📄 gbs_reply_db.java
字号:
pstmt.close();
}
} catch (SQLException se) {
}
}
returnValueD.setDataValue(new Integer(bresult));
return returnValueD;
}
public ReturnValue saveReplyAttachment(
ArrayList replyData,
String updaterUserID,
String updaterSubsidiaryCode,
String customerId,
String rfpNo)
throws Exception {
//Add by Gxk 2004/09/10 Start
updaterUserID = BaseCommonCheck.convertSql(updaterUserID);
updaterSubsidiaryCode = BaseCommonCheck.convertSql(updaterSubsidiaryCode);
customerId = BaseCommonCheck.convertSql(customerId);
rfpNo = BaseCommonCheck.convertSql(rfpNo);
//Add by Gxk 2004/09/10 End
ReturnValue returnValueD = new ReturnValue();
MessageList messageList = new MessageList();
returnValueD.setMessageList(messageList);
Connection conn = null;
try {
conn = this.datasource.getConnection();
for (int i = 0; replyData != null && i < replyData.size(); i++) {
GBS_ReplyList_stBean replyListBean = (GBS_ReplyList_stBean) replyData.get(i);
String lineNo = replyListBean.getLineNo();
if (lineNo.charAt(0) == 'A') {
lineNo = "";
}
//no modify
if (!lineNo.equals("") && !replyListBean.getRowUpdateFlg()) {
continue;
}
//update
if (!lineNo.equals("") && replyListBean.getRowUpdateFlg() && !replyListBean.getRecordDeleteFlg()) {
returnValueD = updateReplyAttachment(replyListBean, conn);
if (returnValueD.isError()) {
break;
}
continue;
}
//delete
if (!lineNo.equals("") && replyListBean.getRowUpdateFlg() && replyListBean.getRecordDeleteFlg()) {
returnValueD = deleteReplyAttachment(replyListBean, conn);
if (returnValueD.isError()) {
break;
}
continue;
}
//insert
if (lineNo.equals("") && !replyListBean.getRecordDeleteFlg()) {
returnValueD = insertReplyAttachment(replyListBean, conn);
if (returnValueD.isError()) {
break;
}
continue;
}
}
StringBuffer sql = new StringBuffer();
StringBuffer sql1 = new StringBuffer();
sql1.append("SELECT PAR_STATUS FROM T_RFP WHERE ");
sql1.append(" CUSTOMER_ID='" + customerId + "'");
sql1.append(" AND RFP_NO='" + rfpNo + "' FOR UPDATE");
Statement st = conn.createStatement();
ResultSet rset = st.executeQuery(sql1.toString());
boolean isFirst = false;
while (rset.next()) {
if (BaseCommonCheck.isEmpty(rset.getString("PAR_STATUS"))) {
isFirst = true;
}
break;
}
sql.append("UPDATE T_RFP SET ");
if (isFirst) {
sql.append(" PAR_STATUS ='1',");
sql.append(" PAR_USER_ID='" + updaterUserID + "',");
sql.append(" PAR_UPDATE_DATE=SYSDATE,");
sql.append(" PAR_SUBSIDIARY_CODE='" + updaterSubsidiaryCode + "', ");
}
sql.append(" REP_STATUS ='1',");
sql.append(" REP_USER_ID='" + updaterUserID + "',");
sql.append(" REP_UPDATE_DATE=SYSDATE,");
sql.append(" REP_SUBSIDIARY_CODE='" + updaterSubsidiaryCode + "', ");
sql.append(" UPDATE_USER='" + updaterUserID + "',");
sql.append(" UPDATE_DATE=SYSDATE ");
sql.append("WHERE ");
sql.append(" CUSTOMER_ID='" + customerId + "'");
sql.append(" AND RFP_NO='" + rfpNo + "'");
System.out.println("[INFO SQL:] " + sql.toString());
PreparedStatement pstmt = conn.prepareStatement(sql.toString());
int hitcount = pstmt.executeUpdate();
if (hitcount == 0) {
messageList.setMessage("", "", "10000001", Integer.MIN_VALUE);
returnValueD.setBussinessError();
}
pstmt.close();
} 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.rollback();
} else {
conn.commit();
}
if (conn != null) {
conn.close();
}
} catch (SQLException se) {
throw se;
}
}
return returnValueD;
}
/**
* update AttachmentFile by sql
* @return boolean
* @exception Exception Exception for information of other errors
* @since 2004/08/02
*/
public ReturnValue updateAttachmentFile(GBS_ReplyList_stBean replyListBean, Connection conn) throws Exception {
//Add by Gxk 2004/09/10 Start
replyListBean.setReplyKbn(BaseCommonCheck.convertSql(replyListBean.getReplyKbn()));
replyListBean.setSubCode(BaseCommonCheck.convertSql(replyListBean.getSubCode()));
replyListBean.setLineNo(BaseCommonCheck.convertSql(replyListBean.getLineNo()));
replyListBean.setTitle(BaseCommonCheck.convertSql(replyListBean.getTitle()));
replyListBean.setComment(BaseCommonCheck.convertSql(replyListBean.getComment()));
replyListBean.setSubName(BaseCommonCheck.convertSql(replyListBean.getSubName()));
replyListBean.setCreateuser(BaseCommonCheck.convertSql(replyListBean.getCreateuser()));
replyListBean.setCreateuserName(BaseCommonCheck.convertSql(replyListBean.getCreateuserName()));
replyListBean.setUpdateuserName(BaseCommonCheck.convertSql(replyListBean.getUpdateuserName()));
replyListBean.setCreatedate(BaseCommonCheck.convertSql(replyListBean.getCreatedate()));
replyListBean.setUpdateuser(BaseCommonCheck.convertSql(replyListBean.getUpdateuser()));
replyListBean.setUpdatedate(BaseCommonCheck.convertSql(replyListBean.getUpdatedate()));
replyListBean.setAttachmentfilename(BaseCommonCheck.convertSql(replyListBean.getAttachmentfilename()));
replyListBean.setFiletype(BaseCommonCheck.convertSql(replyListBean.getFiletype()));
replyListBean.setSeqNo(BaseCommonCheck.convertSql(replyListBean.getSeqNo()));
replyListBean.setCustomerID(BaseCommonCheck.convertSql(replyListBean.getCustomerID()));
replyListBean.setRfpNo(BaseCommonCheck.convertSql(replyListBean.getRfpNo()));
//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;
ResultSet rset = null;
ResultSet rsetEx = null;
Statement st = null;
PreparedStatement pstmt = null;
BLOB blob = null;
BLOB blobIn = null;
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("'" + replyListBean.getSeqNo() + "'");
System.out.println("[info]sql=" + sql);
// start sql ===========================
try {
ReturnValue returnValue = deleteAttachmentFile(replyListBean, 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_REPLY ");
sql.append(" WHERE ");
sql.append(" CUSTOMER_ID = '" + replyListBean.getCustomerID() + "'");
sql.append(" AND RFP_NO = '" + replyListBean.getRfpNo() + "'");
sql.append(" AND REPLY_KBN = '" + replyListBean.getReplyKbn() + "'");
sql.append(" AND SUBSIDIARY_CODE = '" + replyListBean.getSubCode() + "'");
sql.append(" AND LINE_NO =");
sql.append("'" + replyListBean.getLineNo() + "'");
sql.append(" FOR UPDATE ");
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;
}
}
if (ret == 0) {
sql = new StringBuffer();
sql.append(" UPDATE T_REPLY SET ATTACHMENT_FILE = ? ,");
sql.append(" ATTACHMENT_FILE_NAME = ?");
sql.append(" WHERE ");
sql.append(" CUSTOMER_ID = ");
sql.append("'" + replyListBean.getCustomerID() + "'");
sql.append(" AND RFP_NO = ");
sql.append("'" + replyListBean.getRfpNo() + "'");
sql.append(" AND REPLY_KBN = ");
sql.append("'" + replyListBean.getReplyKbn() + "'");
sql.append(" AND SUBSIDIARY_CODE = ");
sql.append("'" + replyListBean.getSubCode() + "'");
sql.append(" AND LINE_NO = ");
sql.append("'" + replyListBean.getLineNo() + "'");
System.out.println("[info]sql=" + sql);
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);
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 (pstmt != null) {
pstmt.close();
}
if (st != null) {
st.close();
}
} catch (SQLException se) {
System.out.println("[Error Happen!]");
System.out.println("[Start Trace]");
se.printStackTrace();
System.out.println("[End Trace]");
throw se;
}
}
return returnValueD;
}
/**
* method deleteAttachmentFile
* @param String Seqno
* @param FormFile AttachmentFile
* @param Connection conn
* @return ReturnValue
* @throws Exception
*/
public ReturnValue deleteAttachmentFile(GBS_ReplyList_stBean replyListBean, Connection conn) throws Exception {
//Add by Gxk 2004/09/10 Start
replyListBean.setReplyKbn(BaseCommonCheck.convertSql(replyListBean.getReplyKbn()));
replyListBean.setSubCode(BaseCommonCheck.convertSql(replyListBean.getSubCode()));
replyListBean.setLineNo(BaseCommonCheck.convertSql(replyListBean.getLineNo()));
replyListBean.setTitle(BaseCommonCheck.convertSql(replyListBean.getTitle()));
replyListBean.setComment(BaseCommonCheck.convertSql(replyListBean.getComment()));
replyListBean.setSubName(BaseCommonCheck.convertSql(replyListBean.getSubName()));
replyListBean.setCreateuser(BaseCommonCheck.convertSql(replyListBean.getCreateuser()));
replyListBean.setCreateuserName(BaseCommonCheck.convertSql(replyListBean.getCreateuserName()));
replyListBean.setUpdateuserName(BaseCommonCheck.convertSql(replyListBean.getUpdateuserName()));
replyListBean.setCreatedate(BaseCommonCheck.convertSql(replyListBean.getCreatedate()));
replyListBean.setUpdateuser(BaseCommonCheck.convertSql(replyListBean.getUpdateuser()));
replyListBean.setUpdatedate(BaseCommonCheck.convertSql(replyListBean.getUpdatedate()));
replyListBean.setAttachmentfilename(BaseCommonCheck.convertSql(replyListBean.getAttachmentfilename()));
replyListBean.setFiletype(BaseCommonCheck.convertSql(replyListBean.getFiletype()));
replyListBean.setSeqNo(BaseCommonCheck.convertSql(replyListBean.getSeqNo()));
replyListBean.setCustomerID(BaseCommonCheck.convertSql(replyListBean.getCustomerID()));
replyListBean.setRfpNo(BaseCommonCheck.convertSql(replyListBean.getRfpNo()));
//Add by Gxk 2004/09/10 End
StringBuffer sql = new StringBuffer();
ReturnValue returnValueD = new ReturnValue();
MessageList messageList = new MessageList();
returnValueD.setMessageList(messageList);
sql.append(" SELECT");
sql.append(" ATTACHMENT_FILE, ATTACHMENT_FILE_NAME"); //
sql.append(" FROM ");
sql.append(" T_REPLY ");
sql.append(" WHERE ");
sql.append(" CUSTOMER_ID = '" + replyListBean.getCustomerID() + "'");
sql.append(" AND RFP_NO = '" + replyListBean.getRfpNo() + "'");
sql.append(" AND REPLY_KBN = '" + replyListBean.getReplyKbn() + "'");
sql.append(" AND SUBSIDIARY_CODE = '" + replyListBean.getSubCode() + "'");
sql.append(" AND LINE_NO =");
sql.append("'" + replyListBean.getLineNo() + "'");
sql.append(" FOR UPDATE ");
System.out.println("[INFO] SQL = " + sql.toString());
int ret = 0;
ResultSet rset = null;
Statement st = null;
PreparedStatement pstmt = null;
try {
st = conn.createStatement();
rset = st.executeQuery(sql.toString());
if (rset.next()) {
} else {
ret = -1;
}
if (ret == 0) {
sql = new StringBuffer();
sql.append(" UPDATE T_REPLY SET ATTACHMENT_FILE = EMPTY_BLOB() ,");
sql.append(" ATTACHMENT_FILE_NAME = ''");
sql.append(" WHERE ");
sql.append(" CUSTOMER_ID = '" + replyListBean.getCustomerID() + "'");
sql.append(" AND RFP_NO = '" + replyListBean.getRfpNo() + "'");
sql.append(" AND REPLY_KBN = '" + replyListBean.getReplyKbn() + "'");
sql.append(" AND SUBSIDIARY_CODE = '" + replyListBean.getSubCode() + "'");
sql.append(" AND LINE_NO =");
sql.append("'" + replyListBean.getLineNo() + "'");
System.out.println("[INFO] SQL = " + sql.toString());
pstmt = conn.prepareStatement(sql.toString());
ret = pstmt.executeUpdate();
}
} 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 (pstmt != null) {
pstmt.close();
}
if (st != null) {
st.close();
}
} catch (SQLException se) {
System.out.println("[Error Happen!]");
System.out.println("[Start Trace]");
se.printStackTrace();
System.out.println("[End Trace]");
throw se;
}
}
return returnValueD;
}
}
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -