?? gbs_result_db.java
字號:
package DB;
/**
* method GBS_Result_DB.java
* created on 08-09-2004
* @author xusheng
* @version 1.0
*/
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.sql.DataSource;
import COMMON.BaseCommonCheck;
import COMMON.MessageList;
import COMMON.ReturnValue;
import COMMON.SystemConstants;
import DataBean.GBS_Result_stBean;
public class GBS_Result_DB implements SystemConstants {
private DataSource datasource = null;
private ReturnValue returnValue = new ReturnValue();
/**
* get datasource from logic
* @param datasource
*/
public GBS_Result_DB(DataSource datasource) {
this.datasource = datasource;
}
/**
* get RFP customer data form T_RFP,M_CUSTOMER
* @param customerID,rfpNo
* @return ReturnValue
* @exception Exception Exception for information of other errors
* @since 2004/08/09
*/
public ReturnValue selectByRFPNo(String customerId ,int rfpNo) throws Exception {
//Add by Gxk 2004/09/10 Start
customerId = BaseCommonCheck.convertSql(customerId);
//Add by Gxk 2004/09/10 End
ReturnValue returnValue = new ReturnValue();
MessageList messageList = new MessageList();
returnValue.setMessageList( messageList );
StringBuffer sql = new StringBuffer();
//edit SQL============(start)================
sql.append(" SELECT ");
sql.append(" T_RFP.CUSTOMER_ID, "); //屭媞ID
sql.append(" M_CUSTOMER.CUSTOMER_NAME, "); //屭媞柤
sql.append(" T_RFP.RFP_NO, "); //RFP斣崋
sql.append(" T_RFP.RESULT, "); //寢壥
sql.append(" T_RFP.WON_REASON, "); //Won棟桼
sql.append(" T_RFP.LOST_REASON, "); //Lost棟桼
sql.append(" T_RFP.LOST_COMMENT, "); //Lost僐儊儞僩
sql.append(" T_RFP.LOST_WHOM "); //Lost扤偵
sql.append(" FROM ");
sql.append(" T_RFP, ");
sql.append(" M_CUSTOMER ");
sql.append(" WHERE ");
//Add by Gxk 2004/08/23 add deleteFlg
sql.append(" (M_CUSTOMER.DELETE_FLG<>'D' OR M_CUSTOMER.DELETE_FLG IS NULL) AND");
sql.append(" T_RFP.CUSTOMER_ID = '" + customerId + "' ");
sql.append(" AND T_RFP.RFP_NO = '" + rfpNo + "' ");
sql.append(" AND T_RFP.CUSTOMER_ID = M_CUSTOMER.CUSTOMER_ID(+) ");
System.out.println("[INFO] sql = " + sql.toString() );
//end edit SQL===========(end)================
//define rerurnvalue
GBS_Result_stBean out_st = new GBS_Result_stBean();
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 T_RFP,M_CUSTOMER
int hitCount = 0;
while (rset.next()) {
out_st.setCustomerId(rset.getString("CUSTOMER_ID"));
out_st.setCustomerName(rset.getString("CUSTOMER_NAME"));
out_st.setRfpNo(rset.getString("RFP_NO"));
out_st.setResult(rset.getString("RESULT"));
out_st.setWonReason(rset.getString("WON_REASON"));
out_st.setLostReason(rset.getString("LOST_REASON"));
out_st.setLostComment(rset.getString("LOST_COMMENT"));
out_st.setLostWhom(rset.getString("LOST_WHOM"));
out_st.setWonComment( "" );
if( out_st.getResult() != null && out_st.getResult().equals( "W" ) ){
out_st.setWonComment( out_st.getLostComment() );
out_st.setLostComment( "" );
}
hitCount++;
}
//end get data
if ( hitCount==0 ) {
messageList.setMessage( "", "", "10000004", Integer.MIN_VALUE);
returnValue.setBussinessError();
} else {
returnValue.setDataValue(out_st);
}
} 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) {
throw se;
}
}
return returnValue;
}
/**
* update T_RFP by CUSTOMER_ID
* @param GBS_Result_stBean in_st,
* @param String customerID,
* @param int rfpNo
* @return boolean
* @exception Exception Exception for information of other errors
* @since 2004/08/09
*/
public ReturnValue updateByCustomerId(GBS_Result_stBean in_st) throws Exception {
//Add by Gxk 2004/09/10 Start
in_st.setCustomerId(BaseCommonCheck.convertSql(in_st.getCustomerId()));
in_st.setCustomerName(BaseCommonCheck.convertSql(in_st.getCustomerName()));
in_st.setResult(BaseCommonCheck.convertSql(in_st.getResult()));
in_st.setWonReason(BaseCommonCheck.convertSql(in_st.getWonReason()));
in_st.setWonComment(BaseCommonCheck.convertSql(in_st.getWonComment()));
in_st.setLostReason(BaseCommonCheck.convertSql(in_st.getLostReason()));
in_st.setLostComment(BaseCommonCheck.convertSql(in_st.getLostComment()));
in_st.setLostWhom(BaseCommonCheck.convertSql(in_st.getLostWhom()));
in_st.setCreateUser(BaseCommonCheck.convertSql(in_st.getCreateUser()));
in_st.setCreateDate(BaseCommonCheck.convertSql(in_st.getCreateDate()));
in_st.setUpdateUser(BaseCommonCheck.convertSql(in_st.getUpdateUser()));
in_st.setUpdateDate(BaseCommonCheck.convertSql(in_st.getUpdateDate()));
//Add by Gxk 2004/09/10 End
ReturnValue returnValue = new ReturnValue();
MessageList messageList = new MessageList();
returnValue.setMessageList(messageList);
StringBuffer sql1 = new StringBuffer();
StringBuffer sql2 = new StringBuffer();
//deit SQL
//start edit sql 1 ===========(start)===============
sql1.append(" SELECT ");
sql1.append(" CUSTOMER_ID, ");
sql1.append(" RFP_NO ");
sql1.append(" FROM T_RFP ");
sql1.append(" WHERE ");
sql1.append(" CUSTOMER_ID = '" + in_st.getCustomerId() + "' ");
sql1.append(" AND RFP_NO = '" + in_st.getRfpNo() + "' ");
sql1.append(" FOR UPDATE ");
System.out.println("[INFO] sql = " + sql1.toString());
//end edit sql 1 ============(end)================
//start edit sql 2 ============(start)==============
sql2.append(" UPDATE T_RFP SET ");
sql2.append(" RESULT = '" + in_st.getResult() + "', "); //夋柺丏寢壥
sql2.append(" WON_REASON = '" + in_st.getWonReason() + "', "); //夋柺丏Won棟桼
sql2.append(" LOST_REASON = '" + in_st.getLostReason() + "', "); //夋柺丏Lost棟桼
sql2.append(" LOST_COMMENT= '" + in_st.getLostComment() + "', "); //夋柺丏寢壥 = L ? 夋柺丏Lost僐儊儞僩:夋柺丏Won僐儊儞僩
sql2.append(" LOST_WHOM = '" + in_st.getLostWhom() + "', "); //夋柺丏Lost扤偵
sql2.append(" RES_STATUS = '1', "); //僗僥乕僞僗(Result) = 1
sql2.append(" RES_USER_ID = '" + in_st.getUpdateUser() + "', "); //Login儐乕僓乕
sql2.append(" RES_UPDATE_DATE = SYSDATE, "); //DB僒乕僶乕偺SYSDATE
sql2.append(" UPDATE_USER = '" + in_st.getUpdateUser() + "', "); //Login儐乕僓乕
sql2.append(" UPDATE_DATE = SYSDATE "); //DB僒乕僶乕偺SYSDATE
sql2.append(" WHERE ");
sql2.append(" CUSTOMER_ID = '" + in_st.getCustomerId() + "' ");
sql2.append(" AND RFP_NO = '" + in_st.getRfpNo() + "' ");
System.out.println("[INFO] sql =" + sql2.toString());
//end edit sql 2 ============(end)================
//define returnValue
ResultSet rset = null;
Connection conn = null;
Statement st = null;
PreparedStatement pstmt = null;
try {
conn = this.datasource.getConnection();
st = conn.createStatement();
rset = st.executeQuery(sql1.toString());
boolean isExist = false;
while (rset.next()) {
isExist = true;
}
if (isExist) {
pstmt = conn.prepareStatement(sql2.toString());
int countUpdate = pstmt.executeUpdate();
if (countUpdate > 0) {
conn.commit();
} else {
conn.rollback();
}
} else {
messageList.setMessage("", "", "10000004", Integer.MIN_VALUE);
returnValue.setBussinessError();
}
//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 {
//clost DB conn
try {
if (rset != null) {
rset.close();
}
if (pstmt != null) {
pstmt.close();
}
if (st != null) {
st.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException se) {
throw se;
}
}
return returnValue;
}
}?? 快捷鍵說明
復(fù)制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -