📄 gbs_reply_db.java
字号:
sql.append(" '" + replyListBean.getSubCode() + "', ");
sql.append(" ?, ");
sql.append(" SYSDATE, ");
sql.append(" '" + replyListBean.getTitle() + "', ");
sql.append(" '" + replyListBean.getComment() + "', ");
sql.append(" '', ");
sql.append("EMPTY_BLOB(),");
sql.append(" '" + replyListBean.getCreateuser() + "', ");
sql.append(" SYSDATE, ");
sql.append(" '" + replyListBean.getCreateuser() + "', ");
sql.append(" SYSDATE ) ");
System.out.println("[INFO] sql = " + sql.toString());
// end sql ===========================
ResultSet rset = null;
Statement st = null;
PreparedStatement pstmt = null;
BLOB blob = null;
int ret = 0;
int lineNo = 1;
//excution result
int bresult = 0;
try {
//conn = this.datasource.getConnection();
st = conn.createStatement();
rset = st.executeQuery(sqlMax.toString());
if (rset.next()) {
lineNo = rset.getInt("SEQ");
lineNo = lineNo + 1;
}
//conn = this.datasource.getConnection();
pstmt = conn.prepareStatement(sql.toString());
pstmt.setInt(1, lineNo);
bresult = pstmt.executeUpdate();
if (bresult == 0) {
messageList.setMessage("", "", "10000013", Integer.MIN_VALUE);
returnValueD.setBussinessError();
}
if (bresult > 0 && !replyListBean.getSeqNo().equals("")) {
replyListBean.setLineNo(String.valueOf(lineNo));
returnValueD = updateAttachmentFile(replyListBean, 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();
}
// if (conn != null) {
// conn.close();
// }
} catch (SQLException se) {
throw se;
}
}
if (bresult > 0) {
returnValueD.setDataValue(new Integer(lineNo));
return returnValueD;
}
returnValueD.setDataValue(new Integer(bresult));
return returnValueD;
}
/**
* get AttachmentFile by sql
* @return byte[]
* @exception Exception Exception for information of other errors
* @since 2004/08/02
*/
public ReturnValue getAttachmentFile(GBS_ReplyList_stBean replyListBean, HttpServletResponse response)
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
ReturnValue returnValueD = new ReturnValue();
MessageList messageList = new MessageList();
returnValueD.setMessageList(messageList);
StringBuffer sql = new StringBuffer();
//Service info
if (!replyListBean.getSeqNo().equals("")) {
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() + "'");
} else {
sql.append(" SELECT");
sql.append(" ATTACHMENT_FILE_NAME, ATTACHMENT_FILE"); //
sql.append(" FROM ");
sql.append(" T_REPLY ");
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() + "'");
}
GBS_File_stBean GbsFilestBean = new GBS_File_stBean();
ResultSet rset = null;
Connection conn = null;
Statement st = null;
try {
conn = this.datasource.getConnection();
st = conn.createStatement();
rset = st.executeQuery(sql.toString());
if (rset.next()) {
BLOB blob = (BLOB) rset.getBlob("ATTACHMENT_FILE");
String fileName = rset.getString("ATTACHMENT_FILE_NAME");
response.setContentType("APPLICATION/OCTET-STREAM");
fileName = new String(fileName.getBytes("shift_jis"),"ISO8859-1");
String disHeader = "Attachment;Filename=" + fileName;
response.setHeader("Content-Disposition", disHeader);
ServletOutputStream os = response.getOutputStream();
InputStream is = blob.getBinaryStream();
byte[] data = new byte[blob.getBufferSize()];
int bytesRead = 0;
int byteSum = 0;
while ((bytesRead = is.read(data)) != -1) {
byteSum += bytesRead;
os.write(data, 0, bytesRead);
}
response.setContentLength(byteSum);
os.flush();
response.flushBuffer();
is.close();
os.close();
}
returnValueD.setDataValue(GbsFilestBean);
} 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();
}
if (conn != null) {
conn.close();
}
} catch (SQLException se) {
}
}
return returnValueD;
}
public ReturnValue updateReplyAttachment(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
ReturnValue returnValueD = new ReturnValue();
MessageList messageList = new MessageList();
returnValueD.setMessageList(messageList);
StringBuffer sql = new StringBuffer();
//SQL DEFINE
// start sql ===========================
sql.append(" SELECT ");
sql.append(" CUSTOMER_ID ");
sql.append(" FROM ");
sql.append(" T_REPLY ");
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() + "'");
sql.append(" FOR UPDATE ");
//end sql ===========================
ResultSet rset = null;
Statement st = null;
PreparedStatement pstmt = null;
BLOB blob = null;
int ret = 0;
int lineNo = 1;
int bresult = 0;
try {
st = conn.createStatement();
rset = st.executeQuery(sql.toString());
if (rset.next()) {
bresult = 1;
}
if (bresult > 0) {
sql = new StringBuffer();
sql.append(" UPDATE T_REPLY SET ");
sql.append(" TITLE=");
sql.append("'" + replyListBean.getTitle() + "',");
sql.append(" COMMENT1=");
sql.append("'" + replyListBean.getComment() + "',");
sql.append(" UPDATE_USER=");
sql.append("'" + replyListBean.getUpdateuser() + "',");
sql.append(" UPDATE_DATE=SYSDATE");
if (replyListBean.getFdelflag()) {
sql.append(", ATTACHMENT_FILE_NAME=''");
sql.append(", ATTACHMENT_FILE=EMPTY_BLOB()");
}
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.toString());
pstmt = conn.prepareStatement(sql.toString());
bresult = pstmt.executeUpdate();
if (bresult == 0) {
messageList.setMessage("", "", "10000014", Integer.MIN_VALUE);
returnValueD.setBussinessError();
}
if (bresult > 0 && !replyListBean.getSeqNo().equals("") && !replyListBean.getFdelflag()) {
returnValueD = updateAttachmentFile(replyListBean, 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();
}
} catch (SQLException se) {
throw se;
}
}
if (bresult > 0) {
returnValueD.setDataValue(new Integer(lineNo));
return returnValueD;
}
returnValueD.setDataValue(new Integer(bresult));
return returnValueD;
}
/**
* delete record by sql
* @return int
* @exception Exception Exception for information of other errors
* @since 2004/08/14
*/
public ReturnValue deleteReplyAttachment(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
List retdata = new Vector();
StringBuffer sql = new StringBuffer();
ReturnValue returnValueD = new ReturnValue();
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 ");
ResultSet rset = null;
Statement st = null;
PreparedStatement pstmt = null;
int bresult = 0;
String Sql = sql.toString();
try {
//conn = this.datasource.getConnection();
st = conn.createStatement();
System.out.println("[info]sql=" + Sql);
//pstmt = conn.prepareStatement(Sql);
//bresult = pstmt.executeUpdate();
rset = st.executeQuery(Sql);
if (rset.next()) {
bresult = 1;
}
if (bresult > 0) {
Sql = "";
sql = new StringBuffer();
sql.append(" DELETE FROM T_REPLY ");
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());
Sql = sql.toString();
System.out.println("[info]sql=" + Sql);
pstmt = conn.prepareStatement(Sql);
bresult = 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 {
//release db
try {
if (rset != null) {
rset.close();
}
if (pstmt != null) {
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -