?? gbs_nda_db.java
字號:
package DB;
/**
* method GBS_NDA_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_Nda_stBean;
public class GBS_Nda_DB implements SystemConstants {
private DataSource datasource = null;
private ReturnValue returnValue = new ReturnValue();
/**
* get datasource from logic
* @param datasource
*/
public GBS_Nda_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);
//Edit SQL ===============(start)========================
StringBuffer sql = new StringBuffer();
sql.append(" SELECT T_RFP.CUSTOMER_ID, "); //屭媞ID
sql.append(" M_CUSTOMER.CUSTOMER_NAME, "); //屭媞柤
sql.append(" T_RFP.RFP_NO, "); //RFP斣崋
sql.append(" TO_CHAR(T_RFP.NDA_DATE, 'DD-Mon-YYYY') NDA_DATE, "); //婡枾曐帩宊栺擔
sql.append(" T_RFP.INFO_COUNTRY, "); //崙
sql.append(" T_RFP.INFO_HARD_CATEGORY, "); //僴乕僪僂僃傾暘椶
sql.append(" T_RFP.INFO_LS_RT_OP, "); //儕乕僗丒儗儞僞儖丒尰嬥
sql.append(" T_RFP.QUANTITY, "); //悢検
sql.append(" T_RFP.REMARKS, "); //儕儅乕僋僗
sql.append(" T_RFP.COMMENT1, "); //僐儊儞
sql.append(" T_RFP.RFP_SUBSIDIARY_CODE "); //
sql.append(" FROM T_RFP, M_CUSTOMER ");
sql.append(" WHERE T_RFP.CUSTOMER_ID = '" + customerId + "' ");
//Add by Gxk 2004/08/23 add deleteFlg
sql.append(" AND (M_CUSTOMER.DELETE_FLG<>'D' OR M_CUSTOMER.DELETE_FLG IS NULL)");
sql.append(" AND T_RFP.RFP_NO = '" + rfpNo + "' ");
sql.append(" AND T_RFP.CUSTOMER_ID = M_CUSTOMER.CUSTOMER_ID(+) ");
sql.append(" ORDER BY CUSTOMER_ID ");
//edit SQL
System.out.println("[INFO] sql = " + sql.toString());
//Edit SQL ===============(end)========================
//define rerurnvalue
GBS_Nda_stBean out_st = new GBS_Nda_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.getInt("RFP_NO"));
out_st.setNdaDate(rset.getString("NDA_DATE"));
out_st.setInfoCountry(rset.getString("INFO_COUNTRY"));
out_st.setInfoHardCategory(rset.getString("INFO_HARD_CATEGORY"));
out_st.setInfoDelivery(rset.getString("INFO_LS_RT_OP"));
out_st.setQuantity(rset.getString("QUANTITY"));
out_st.setRemarks(rset.getString("REMARKS"));
out_st.setComment(rset.getString("COMMENT1"));
out_st.setRfpSubsidiaryCode(rset.getString("RFP_SUBSIDIARY_CODE"));
hitCount++;
}
if (hitCount == 0) {
//set error information.
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_NDA_stBean in_st,
* @param String customerID,
* @param int rfpNo
* @return ReturnValue
* @exception Exception Exception for information of other errors
* @since 2004/08/09
*/
public ReturnValue updateByCustomerId(GBS_Nda_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.setNdaDate(BaseCommonCheck.convertSql(in_st.getNdaDate()));
in_st.setInfoCountry(BaseCommonCheck.convertSql(in_st.getInfoCountry()));
in_st.setInfoHardCategory(BaseCommonCheck.convertSql(in_st.getInfoHardCategory()));
in_st.setQuantity(BaseCommonCheck.convertSql(in_st.getQuantity()));
in_st.setInfoDelivery(BaseCommonCheck.convertSql(in_st.getInfoDelivery()));
in_st.setRemarks(BaseCommonCheck.convertSql(in_st.getRemarks()));
in_st.setComment(BaseCommonCheck.convertSql(in_st.getComment()));
in_st.setUpdateUser(BaseCommonCheck.convertSql(in_st.getUpdateUser()));
in_st.setRepUpdateDate(BaseCommonCheck.convertSql(in_st.getRepUpdateDate()));
in_st.setNdaStatus(BaseCommonCheck.convertSql(in_st.getNdaStatus()));
in_st.setNdaSubsidiaryCode(BaseCommonCheck.convertSql(in_st.getNdaSubsidiaryCode()));
in_st.setNdaUserId(BaseCommonCheck.convertSql(in_st.getNdaUserId()));
in_st.setNdaUpdateDate(BaseCommonCheck.convertSql(in_st.getNdaUpdateDate()));
//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();
//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(" NDA_DATE = to_date('" + in_st.getNdaDate() + "','yyyy/MM/dd'), "); //夋柺丏婡枾曐帩宊栺擔
sql2.append(" UPDATE_USER = '" + in_st.getUpdateUser() + "', "); //Login儐乕僓乕
sql2.append(" UPDATE_DATE = SYSDATE, "); //DB僒乕僶乕偺SYSDATE
sql2.append(" NDA_STATUS = '" + in_st.getNdaStatus() + "', "); //"1"
sql2.append(" NDA_SUBSIDIARY_CODE = '" + in_st.getNdaSubsidiaryCode() + "', "); //儘僌僀儞儐乕僓乕偺尰朄僐乕僪
sql2.append(" NDA_USER_ID= '" + in_st.getNdaUserId() + "', "); //Login儐乕僓乕
sql2.append(" NDA_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 + -