📄 gbs_mserviceinfo_db.java
字号:
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.Calendar;
import java.util.List;
import java.util.Locale;
import java.util.Vector;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
import oracle.sql.BLOB;
import org.apache.struts.upload.FormFile;
import COMMON.BaseCommonCheck;
import COMMON.MessageList;
import COMMON.ReturnValue;
import COMMON.SystemConstants;
import DataBean.GBS_File_stBean;
import DataBean.GBS_ServiceInfoList_Bean;
/**
* GBS_MServiceInfo_DB implements SystemConstants
* created on 29-07-2004
* @author mxx
* @version 1.0
*/
public class GBS_MServiceInfo_DB implements SystemConstants {
private DataSource datasource = null;
public GBS_MServiceInfo_DB(DataSource datasource) {
this.datasource = datasource;
}
/**
* method getServiceinfoList
* @param
* @return ReturnValue
* @throws Exception
*/
public ReturnValue getServiceinfoList(int plusHour) throws Exception {
ReturnValue returnValueD = new ReturnValue();
MessageList messageList = new MessageList();
returnValueD.setMessageList(messageList);
List retdata = new Vector();
StringBuffer sql = new StringBuffer();
sql.append(" SELECT");
sql.append(" M_SERVICE_INFO.SEQ_NO, "); //
sql.append(" M_SERVICE_INFO.TITLE, "); //
sql.append(" (T_CREATE_USER.MEMBER_NAME_FIRST || ' ' || T_CREATE_USER.MEMBER_NAME_LAST) CREATE_USER, "); //
sql.append(" M_SERVICE_INFO.CREATE_DATE, "); //
sql.append(" (T_UPDATE_USER.MEMBER_NAME_FIRST || ' ' || T_UPDATE_USER.MEMBER_NAME_LAST) UPDATE_USER, "); //
sql.append(" M_SERVICE_INFO.UPDATE_DATE, "); //
sql.append(" M_SERVICE_INFO.ATTACHMENT_FILE_NAME "); //
sql.append(" FROM ");
sql.append(" M_SERVICE_INFO, M_MEMBER T_CREATE_USER, M_MEMBER T_UPDATE_USER ");
sql.append(" WHERE ");
sql.append(" T_CREATE_USER.USER_ID(+) = M_SERVICE_INFO.CREATE_USER ");
sql.append(" AND T_UPDATE_USER.USER_ID(+) = M_SERVICE_INFO.UPDATE_USER ");
sql.append(" ORDER BY ");
sql.append(" M_SERVICE_INFO.SEQ_NO DESC");
System.out.println("[INFO] sql = " + sql.toString());
ResultSet rset = null;
Connection conn = null;
Statement st = null;
try {
conn = this.datasource.getConnection();
st = conn.createStatement();
//System.out.println("[info]GBS_MServiceinfo_DB.getServiceinfoList.sql=" + sql);
rset = st.executeQuery(sql.toString());
while (rset.next()) {
GBS_ServiceInfoList_Bean line = new GBS_ServiceInfoList_Bean();
line.setSeqno(rset.getString("SEQ_NO"));
line.setTitle(rset.getString("TITLE"));
Calendar gmtCal = Calendar.getInstance();
rset.getTimestamp("CREATE_DATE", gmtCal);
gmtCal.add(Calendar.HOUR, plusHour);
//System.out.println("2 " +gmtCal.getTime());
SimpleDateFormat formatter = new SimpleDateFormat("dd-MMM-yyyy", Locale.US);
line.setCreatedate(formatter.format(gmtCal.getTime()));
rset.getTimestamp("UPDATE_DATE", gmtCal);
gmtCal.add(Calendar.HOUR, plusHour);
line.setUpdatedate(formatter.format(gmtCal.getTime()));
line.setCreateuser(rset.getString("CREATE_USER"));
//line.setCreatedate(rset.getString("CREATE_DATE"));
line.setUpdateuser(rset.getString("UPDATE_USER"));
//line.setUpdatedate(rset.getString("UPDATE_DATE"));
String filename = rset.getString("ATTACHMENT_FILE_NAME");
String filetype = BaseCommonCheck.getFileType(filename);
line.setFiletype(filetype);
line.setAttachmentfilename(filename);
retdata.add(line);
}
if (retdata.size() == 0) {
messageList.setMessage("", "", "10000004", Integer.MIN_VALUE);
returnValueD.setBussinessError();
}
returnValueD.setDataValue(retdata);
} 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;
}
/**
* method selectBySeqno
* @param String Seqno
* @return ReturnValue
* @throws Exception
*/
public ReturnValue selectBySeqno(String seqno) throws Exception {
//Add by Gxk 2004/09/10 Start
seqno = BaseCommonCheck.convertSql(seqno);
//Add by Gxk 2004/09/10 End
GBS_ServiceInfoList_Bean retdata = new GBS_ServiceInfoList_Bean();
StringBuffer sql = new StringBuffer();
ReturnValue returnValueD = new ReturnValue();
MessageList messageList = new MessageList();
returnValueD.setMessageList(messageList);
sql.append(" SELECT");
sql.append(" M_SERVICE_INFO.SEQ_NO, "); //
sql.append(" M_SERVICE_INFO.TITLE, "); //
sql.append(" M_SERVICE_INFO.CREATE_USER, "); //
sql.append(" TO_CHAR(M_SERVICE_INFO.CREATE_DATE,'DD-Mon-YYYY') CREATE_DATE, "); //
sql.append(" M_SERVICE_INFO.UPDATE_USER, "); //
sql.append(" TO_CHAR(M_SERVICE_INFO.UPDATE_DATE,'DD-Mon-YYYY') UPDATE_DATE, "); //
sql.append(" M_SERVICE_INFO.ATTACHMENT_FILE_NAME "); //
sql.append(" FROM ");
sql.append(" M_SERVICE_INFO ");
sql.append(" WHERE ");
sql.append(" M_SERVICE_INFO.SEQ_NO =");
sql.append(seqno);
System.out.println("[INFO] sql = " + sql.toString());
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()) {
retdata.setSeqno(rset.getString("SEQ_NO"));
retdata.setTitle(rset.getString("TITLE"));
retdata.setCreatedate(rset.getString("CREATE_DATE"));
retdata.setCreateuser(rset.getString("CREATE_USER"));
retdata.setUpdatedate(rset.getString("UPDATE_DATE"));
retdata.setUpdateuser(rset.getString("UPDATE_USER"));
retdata.setAttachmentfilename(rset.getString("ATTACHMENT_FILE_NAME"));
} else {
//data not found;
messageList.setMessage("", "", "10000001", Integer.MIN_VALUE);
returnValueD.setBussinessError();
}
returnValueD.setDataValue(retdata);
} 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;
}
/**
* method updateService
* @param String Seqno
* @param String Title
* @param String Updatedate
* @param String Updateuser
* @param FormFile AttachmentFile
* @param boolean FileDelete
* @return ReturnValue
* @throws Exception
*/
public ReturnValue updateService(
String seqno,
String title,
String updateDate,
String updateUser,
FormFile AttachmentFile,
boolean FileDelete)
throws Exception {
//Add by Gxk 2004/09/10 Start
seqno = BaseCommonCheck.convertSql(seqno);
title = BaseCommonCheck.convertSql(title);
updateDate = BaseCommonCheck.convertSql(updateDate);
updateUser = BaseCommonCheck.convertSql(updateUser);
//Add by Gxk 2004/09/10 End
GBS_ServiceInfoList_Bean retdata = new GBS_ServiceInfoList_Bean();
StringBuffer sql = new StringBuffer();
ReturnValue returnValueD = new ReturnValue();
MessageList messageList = new MessageList();
returnValueD.setMessageList(messageList);
sql.append(" SELECT ");
sql.append(" SEQ_NO ");
sql.append(" FROM ");
sql.append(" M_SERVICE_INFO");
sql.append(" WHERE ");
sql.append(" SEQ_NO = '" + seqno + "' ");
sql.append(" FOR UPDATE ");
System.out.println("[info]GBS_MServiceinfo_DB.updateService.sql=" + sql.toString());
ResultSet rset = null;
Connection conn = null;
Statement st = null;
PreparedStatement pstmt = null;
int bresult = 0;
String strSql = "";
try {
strSql = sql.toString();
conn = this.datasource.getConnection();
st = conn.createStatement();
rset = st.executeQuery(strSql);
if (rset.next()) {
bresult = 1;
}
if (bresult > 0) {
strSql = "";
sql = new StringBuffer();
sql.append(" UPDATE ");
sql.append(" M_SERVICE_INFO ");
sql.append(" SET ");
sql.append(" M_SERVICE_INFO.SEQ_NO = '"); //
sql.append(seqno + "', ");
sql.append(" M_SERVICE_INFO.TITLE = '"); //
sql.append(title + "', ");
sql.append(" M_SERVICE_INFO.UPDATE_DATE = SYSDATE,"); //
if (FileDelete) {
sql.append(" ATTACHMENT_FILE_NAME = '', ");
sql.append(" ATTACHMENT_FILE = null, ");
}
sql.append(" M_SERVICE_INFO.UPDATE_USER = '"); //
sql.append(updateUser + "' ");
sql.append(" WHERE SEQ_NO = '");
sql.append(seqno + "'");
System.out.println("[info]GBS_MServiceinfo_DB.updateService.sql=" + sql.toString());
strSql = sql.toString();
//st = conn.createStatement();
pstmt = conn.prepareStatement(strSql);
//updateAttachmentFile(Seqno,data);
bresult = pstmt.executeUpdate();
if (!FileDelete && bresult > 0 && AttachmentFile != null && !AttachmentFile.getFileName().equals("")) {
ReturnValue returnValue = updateAttachmentFile(seqno, AttachmentFile, conn);
if (returnValue.isError()) {
if (returnValue.isBussinessError()) {
messageList.addAll(returnValue.getMessageList());
}
returnValueD.setErrorCode(returnValue.getErrorCode());
returnValueD.setMessageList(returnValue.getMessageList());
} else {
bresult = ((Integer) returnValue.getDataValue()).intValue();
}
}
}
} 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(bresult));
try {
//COMMIT DATA
if (bresult > 0 && !returnValueD.isError()) {
conn.commit();
} else {
conn.rollback();
}
//CLOSE DB CONN
if (rset != null) {
rset.close();
}
if (st != null) {
st.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException se) {
return returnValueD;
}
}
return returnValueD;
}
/**
* method updateService
* @param String Seqno
* @param String Title
* @param String Updatedate
* @param String Updateuser
* @param FormFile AttachmentFile
* @return ReturnValue
* @throws Exception
*/
public ReturnValue addService(String seqno, String title, String createdate, String createuser, FormFile AttachmentFile)
throws Exception {
//Add by Gxk 2004/09/10 Start
seqno = BaseCommonCheck.convertSql(seqno);
title = BaseCommonCheck.convertSql(title);
createdate = BaseCommonCheck.convertSql(createdate);
createuser = BaseCommonCheck.convertSql(createuser);
//Add by Gxk 2004/09/10 End
GBS_ServiceInfoList_Bean retdata = new GBS_ServiceInfoList_Bean();
StringBuffer sql = new StringBuffer();
ReturnValue returnValueD = new ReturnValue();
MessageList messageList = new MessageList();
returnValueD.setMessageList(messageList);
ResultSet rset = null;
Connection conn = null;
Statement st = null;
PreparedStatement pstmt = null;
int seq = 1;
String Sql;
int bresult = 0;
try {
String selseq = " SELECT MAX(SEQ_NO) AS SEQ FROM M_SERVICE_INFO";
conn = this.datasource.getConnection();
st = conn.createStatement();
rset = st.executeQuery(selseq);
if (rset.next()) {
seq = rset.getInt("SEQ");
seq = seq + 1;
}
seqno = seq + "";
sql.append(" INSERT INTO M_SERVICE_INFO ");
sql.append("(SEQ_NO,TITLE,ATTACHMENT_FILE,CREATE_USER,CREATE_DATE,UPDATE_USER,UPDATE_DATE)");
sql.append(" VALUES( '");
sql.append(seq + "','" + title + "',");
sql.append("null,'");
sql.append(createuser + "',SYSDATE,'");
sql.append(createuser + "',SYSDATE)");
//sql.append("','','')");
Sql = sql.toString();
System.out.println("[INFO] sql = " + Sql);
//st = conn.createStatement();
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -