📄 gbs_rfpattach_db.java
字号:
/**
* method GBS_MMember_DB.java
* created on 07-29-2004
*
* @author GXK
* @version 1.0
*/
package DB;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import java.util.Locale;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
import oracle.sql.BLOB;
import COMMON.BaseCommonCheck;
import COMMON.MessageList;
import COMMON.ReturnValue;
import COMMON.SystemConstants;
import DataBean.GBS_RfpAttachList_stBean;
public class GBS_RfpAttach_DB implements SystemConstants {
private DataSource datasource = null;
/**
* get datasource from logic
* @param datasource
*/
public GBS_RfpAttach_DB(DataSource datasource) {
this.datasource = datasource;
}
public ReturnValue selectByCustomerIDRfpID(String CustomerID, String RfpID, int plusHour) throws Exception {
//Add by Gxk 2004/09/10 Start
CustomerID = BaseCommonCheck.convertSql(CustomerID);
RfpID = BaseCommonCheck.convertSql(RfpID);
//Add by Gxk 2004/09/10 End
ReturnValue returnValue = new ReturnValue();
MessageList messageList = new MessageList();
returnValue.setMessageList(messageList);
StringBuffer sql = new StringBuffer();
// ///////////////////////////////////////////////////////////////////////////
// EDIT SQL
sql.append(" SELECT ");
sql.append(" T_RFP_ATTACH.CUSTOMER_ID, ");
sql.append(" T_RFP_ATTACH.RFP_NO, ");
sql.append(" T_RFP_ATTACH.LINE_NO, ");
sql.append(" TO_CHAR(T_RFP_ATTACH.ATTACH_DATE,'DD-Mon-YYYY') ATTACH_DATE, ");
sql.append(" T_RFP_ATTACH.TITLE, ");
sql.append(" UPDATEUSR.MEMBER_NAME_FIRST || ' ' || UPDATEUSR.MEMBER_NAME_LAST UPDATE_MEMBER_NAME, ");
sql.append(" T_RFP_ATTACH.UPDATE_DATE, ");
sql.append(" CREATEUSR.MEMBER_NAME_FIRST || ' ' || CREATEUSR.MEMBER_NAME_LAST CREATE_MEMBER_NAME, ");
sql.append(" T_RFP_ATTACH.CREATE_DATE, ");
sql.append(" T_RFP_ATTACH.ATTACHMENT_FILE_NAME ");
sql.append(" FROM ");
sql.append(" T_RFP_ATTACH, ");
sql.append(" M_MEMBER CREATEUSR, ");
sql.append(" M_MEMBER UPDATEUSR ");
sql.append(" WHERE T_RFP_ATTACH.CUSTOMER_ID = '" + CustomerID + "' ");
sql.append(" AND T_RFP_ATTACH.RFP_NO = '" + RfpID + "' ");
sql.append(" AND T_RFP_ATTACH.CREATE_USER = CREATEUSR.USER_ID(+) ");
sql.append(" AND T_RFP_ATTACH.UPDATE_USER = UPDATEUSR.USER_ID(+) ");
sql.append("ORDER BY ");
sql.append(" T_RFP_ATTACH.LINE_NO ");
System.out.println("[INFO] sql = " + sql.toString());
// END EDIT SQL
// DEFINE RETURNVALUE
GBS_RfpAttachList_stBean out_st = null;
ArrayList list = new ArrayList();
// END
// ///////////////////////////////////////////////////////////////////////////
ResultSet rset = null;
Connection conn = null;
Statement st = null;
try {
conn = this.datasource.getConnection();
st = conn.createStatement();
rset = st.executeQuery(sql.toString());
// //////////////////////////////////////////////////////////////////////////
// GET DATA FROM DB RESULTSET
while (rset.next()) {
out_st = new GBS_RfpAttachList_stBean();
out_st.setCustomerID(rset.getString("CUSTOMER_ID"));
out_st.setRfpNo(rset.getString("RFP_NO"));
out_st.setLineNo(rset.getString("LINE_NO"));
out_st.setAttachDate(rset.getString("ATTACH_DATE"));
out_st.setTitle(rset.getString("TITLE"));
out_st.setUpdateUserName(rset.getString("UPDATE_MEMBER_NAME"));
Calendar date = Calendar.getInstance();
SimpleDateFormat format = new SimpleDateFormat("dd-MMM-yyyy", Locale.US);
rset.getTimestamp("CREATE_DATE", date);
date.add(Calendar.HOUR, plusHour);
out_st.setCreateDate(format.format(date.getTime()));
rset.getTimestamp("UPDATE_DATE", date);
date.add(Calendar.HOUR, plusHour);
out_st.setUpdateDate(format.format(date.getTime()));
out_st.setCreateUserName(rset.getString("CREATE_MEMBER_NAME"));
String filename = rset.getString("ATTACHMENT_FILE_NAME");
out_st.setAttachmentFileName(filename);
out_st.setFiletype(BaseCommonCheck.getFileType(filename));
list.add(out_st);
}
returnValue.setDataValue(list);
// END GET DATA
// //////////////////////////////////////////////////////////////////////////
} catch (Exception exception) {
System.out.println("[Error Happen!]");
System.out.println("[Start Trace]");
exception.printStackTrace();
System.out.println("[End Trace]");
throw exception;
} finally {
//CLOSE DB CONN
try {
if (rset != null) {
rset.close();
}
if (st != null) {
st.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException se) {
}
}
return returnValue;
}
/**
* get AttachmentFile by sql
* @return byte[]
* @exception Exception Exception for information of other errors
* @since 2004/08/02
*/
public ReturnValue getAttachmentFile(
String customerID,
String rfpNo,
String lineNo,
String seqNo,
HttpServletResponse response)
throws Exception {
//Add by Gxk 2004/09/10 Start
customerID = BaseCommonCheck.convertSql(customerID);
rfpNo = BaseCommonCheck.convertSql(rfpNo);
lineNo = BaseCommonCheck.convertSql(lineNo);
seqNo = BaseCommonCheck.convertSql(seqNo);
//Add by Gxk 2004/09/10 End
StringBuffer sql = new StringBuffer();
ReturnValue returnValueD = new ReturnValue();
if (!seqNo.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 = '" + seqNo + "'");
} else {
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 = '" + customerID + "'");
sql.append(" AND RFP_NO = '" + rfpNo + "'");
sql.append(" AND LINE_NO = '" + lineNo + "'");
}
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();
}
} 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 selectByCustomerIDRfpIDLineNo(String customerID, String rfpNo,String lineNo) throws Exception {
StringBuffer sql = new StringBuffer();
// ///////////////////////////////////////////////////////////////////////////
// EDIT SQL
sql.append("SELECT ");
sql.append("TITLE ");
sql.append("ATTACHMENT_FILE_NAME ");
sql.append("FROM ");
sql.append("T_RFP_ATTACH ");
sql.append("WHERE T_RFP_ATTACH.CUSTOMER_ID = '" + customerID + "' ");
sql.append(" AND T_RFP_ATTACH.RFP_NO = '" + rfpNo + "' ");
sql.append(" AND T_RFP_ATTACH.UPDATE_USER = M_MEMBER.USER_ID ");
sql.append("ORDER BY ");
sql.append(" CUSTOMER_ID, RFP_NO, LINE_NO ");
// END EDIT SQL
// DEFINE RETURNVALUE
ReturnValue returnValue = new ReturnValue();
GBS_RfpAttachList_stBean out_st = new GBS_RfpAttachList_stBean();
// END
// ///////////////////////////////////////////////////////////////////////////
ResultSet rset = null;
Connection conn = null;
Statement st = null;
try {
conn = this.datasource.getConnection();
st = conn.createStatement();
rset = st.executeQuery(sql.toString());
// //////////////////////////////////////////////////////////////////////////
// GET DATA FROM DB RESULTSET
out_st.setTitle(rset.getString("TITLE"));
out_st.setAttachmentFileName(rset.getString("ATTACHMENT_FILE_NAME"));
returnValue.setDataValue(out_st);
// END GET DATA
// //////////////////////////////////////////////////////////////////////////
} catch (Exception exception) {
System.out.println("[Error Happen!]");
System.out.println("[Start Trace]");
exception.printStackTrace();
System.out.println("[End Trace]");
throw exception;
} finally {
//CLOSE DB CONN
try {
if (rset != null) {
rset.close();
}
if (st != null) {
st.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException se) {
}
}
return returnValue;
}
*/
public ReturnValue updateRfpAttachment(GBS_RfpAttachList_stBean in_st, Connection conn, String userID)
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()));
userID = BaseCommonCheck.convertSql(userID);
//Add by Gxk 2004/09/10 End
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -