?? gbs_rfpcountry_db.java
字號:
/**
* method GBS_MMember_DB.java
* created on 07-29-2004
*
* @author GXK
* @version 1.0
*/
package DB;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import javax.sql.DataSource;
import COMMON.BaseCommonCheck;
import COMMON.ReturnValue;
import COMMON.SystemConstants;
import DataBean.GBS_RfpCountry_stBean;
public class GBS_RfpCountry_DB implements SystemConstants {
private DataSource datasource = null;
/**
* get datasource from logic
* @param datasource
*/
public GBS_RfpCountry_DB(DataSource datasource) {
this.datasource = datasource;
}
public ReturnValue selectByCustomerIDRfpID(String CustomerID, String RfpNo) throws Exception {
//Add by Gxk 2004/09/10 Start
CustomerID = BaseCommonCheck.convertSql(CustomerID);
RfpNo = BaseCommonCheck.convertSql(RfpNo);
//Add by Gxk 2004/09/10 End
StringBuffer sql = new StringBuffer();
// ///////////////////////////////////////////////////////////////////////////
// EDIT SQL
sql.append(" SELECT ");
sql.append(" T_RFP_COUNTRY.COUNTRY_CODE, ");
sql.append(" T_RFP_COUNTRY.SUBSIDIARY_CODE, ");
sql.append(" M_COUNTRY.COUNTRY_NAME, ");
sql.append(" M_SUBSIDIARY.SUBSIDIARY_NAME ");
sql.append(" FROM ");
sql.append(" T_RFP_COUNTRY, ");
sql.append(" M_COUNTRY, ");
sql.append(" M_SUBSIDIARY ");
sql.append(" WHERE ");
sql.append(" T_RFP_COUNTRY.CUSTOMER_ID = '" + CustomerID + "' ");
sql.append(" AND T_RFP_COUNTRY.RFP_NO = '" + RfpNo + "' ");
sql.append(" AND T_RFP_COUNTRY.SUBSIDIARY_CODE = M_SUBSIDIARY.SUBSIDIARY_CODE(+) ");
sql.append(" AND T_RFP_COUNTRY.COUNTRY_CODE = M_COUNTRY.COUNTRY_CODE(+) ");
sql.append("ORDER BY ");
sql.append(" M_COUNTRY.DISP_ORDER, T_RFP_COUNTRY.COUNTRY_CODE, ");
sql.append(" M_SUBSIDIARY.DISP_ORDER, T_RFP_COUNTRY.SUBSIDIARY_CODE ");
System.out.println("[INFO] sql = " + sql.toString());
// END EDIT SQL
// DEFINE RETURNVALUE
ReturnValue returnValue = new ReturnValue();
GBS_RfpCountry_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_RfpCountry_stBean();
out_st.setCountryCode(BaseCommonCheck.convertNullToSpace(rset.getString("COUNTRY_CODE")));
out_st.setCountryName(BaseCommonCheck.convertNullToSpace(rset.getString("COUNTRY_NAME")));
out_st.setSubsidiaryCode(BaseCommonCheck.convertNullToSpace(rset.getString("SUBSIDIARY_CODE")));
out_st.setSubsidiaryName(BaseCommonCheck.convertNullToSpace(rset.getString("SUBSIDIARY_NAME")));
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;
}
public void deleteByCustomerIDRfpNo(String customerID, String rfpNo) throws Exception {
//Add by Gxk 2004/09/10 Start
customerID = BaseCommonCheck.convertSql(customerID);
rfpNo = BaseCommonCheck.convertSql(rfpNo);
//Add by Gxk 2004/09/10 End
String sql = new String();
String sql1 = new String();
ReturnValue ret = new ReturnValue();
/////////////////////////////////////////////////////////////////////////////
//EDIT SQL
sql += " SELECT ";
sql += " CUSTOMER_ID ";
sql += " FROM ";
sql += " T_RFP_COUNTRY ";
sql += " WHERE ";
sql += " CUSTOMER_ID ='" + customerID + "'";
sql += " AND RFP_NO ='" + rfpNo + "'";
sql += " FOR UPDATE ";
System.out.println("[INFO] sql = " + sql);
sql1 = " DELETE FROM ";
sql1 += " T_RFP_COUNTRY ";
sql1 += " WHERE ";
sql1 += " CUSTOMER_ID ='" + customerID + "'";
sql1 += " AND RFP_NO ='" + rfpNo + "'";
System.out.println("[INFO] sql = " + sql1);
//END EDIT SQL
//DEFINE RETURNVALUE
//END
/////////////////////////////////////////////////////////////////////////////
ResultSet rset = null;
Connection conn = null;
Statement st = null;
PreparedStatement pstmt = null;
int bresult = 0;
try {
conn = this.datasource.getConnection();
st = conn.createStatement();
rset = st.executeQuery(sql);
if (rset.next()) {
bresult = 1;
}
if (bresult > 0) {
pstmt = conn.prepareStatement(sql1);
bresult = pstmt.executeUpdate();
}
if (bresult > 0) {
conn.commit();
} else {
conn.rollback();
}
////////////////////////////////////////////////////////////////////////////
//GET DATA FROM DB RESULTSET
//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 (pstmt != null) {
pstmt.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException se) {
}
}
}
public ReturnValue update(String customerID, String rfpNo, String createUserID, ArrayList list) throws Exception {
//Add by Gxk 2004/09/10 Start
customerID = BaseCommonCheck.convertSql(customerID);
rfpNo = BaseCommonCheck.convertSql(rfpNo);
createUserID = BaseCommonCheck.convertSql(createUserID);
//Add by Gxk 2004/09/10 End
StringBuffer sql = new StringBuffer();
StringBuffer sql1 = new StringBuffer();
String sql2 = new String();
int bresult = 0;
String countryCode = "";
ReturnValue returnValue = new ReturnValue();
// ///////////////////////////////////////////////////////////////////////////
// EDIT SQL
sql.append(" SELECT ");
sql.append(" CUSTOMER_ID");
sql.append(" FROM ");
sql.append(" T_RFP_COUNTRY ");
sql.append(" WHERE ");
sql.append(" CUSTOMER_ID = '" + customerID + "'");
sql.append(" AND RFP_NO = '" + rfpNo + "'");
sql.append(" FOR UPDATE ");
System.out.println("[INFO]sql=" + sql.toString());
sql1.append("DELETE ");
sql1.append(" T_RFP_COUNTRY ");
sql1.append("WHERE ");
sql1.append(" CUSTOMER_ID = '" + customerID + "' ");
sql1.append("AND RFP_NO = '" + rfpNo + "' ");
System.out.println("[INFO]sql=" + sql1);
// END EDIT SQL
// ///////////////////////////////////////////////////////////////////////////
ResultSet rset = null;
Connection conn = null;
Statement st = null;
PreparedStatement pstmt = null;
GBS_RfpCountry_stBean RfpCountry_stBean = new GBS_RfpCountry_stBean();
try {
conn = this.datasource.getConnection();
st = conn.createStatement();
rset = st.executeQuery(sql.toString());
if (rset.next()) {
bresult = 1;
}
if (bresult > 0) {
pstmt = conn.prepareStatement(sql1.toString());
bresult = pstmt.executeUpdate();
}
// if ( bresult > 0 ){
for (int i = 0; i < list.size(); i++) {
RfpCountry_stBean = (GBS_RfpCountry_stBean) list.get(i);
sql2 = "";
sql2 += " INSERT INTO ";
sql2 += " T_RFP_COUNTRY (";
sql2 += " CUSTOMER_ID ,";
sql2 += " RFP_NO ,";
sql2 += " COUNTRY_CODE ,";
sql2 += " SUBSIDIARY_CODE ,";
sql2 += " CREATE_USER ,";
sql2 += " CREATE_DATE )";
sql2 += " VALUES ( ";
sql2 += " '" + customerID + "', ";
sql2 += " '" + rfpNo + "', ";
sql2 += " '" + BaseCommonCheck.convertSql(RfpCountry_stBean.getCountryCode()) + "', ";
sql2 += " '" + BaseCommonCheck.convertSql(RfpCountry_stBean.getSubsidiaryCode()) + "', ";
sql2 += " '" + createUserID + "', ";
sql2 += " SYSDATE ) ";
System.out.println("[INFO]sql=" + sql2);
pstmt = conn.prepareStatement(sql2.toString());
bresult = pstmt.executeUpdate();
if (bresult < 0) {
break;
}
}
// }
if (bresult > 0) {
conn.commit();
} else {
conn.rollback();
}
} 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 selectCountryInfo(String countryCode) throws Exception {
//Add by Gxk 2004/09/10 Start
countryCode = BaseCommonCheck.convertSql(countryCode);
//Add by Gxk 2004/09/10 End
StringBuffer sql = new StringBuffer();
// ///////////////////////////////////////////////////////////////////////////
// EDIT SQL
sql.append(" SELECT ");
sql.append(" M_COUNTRY.COUNTRY_CODE, ");
sql.append(" M_COUNTRY.SUBSIDIARY_CODE ");
sql.append(" FROM ");
sql.append(" M_COUNTRY ");
sql.append(" WHERE ");
sql.append(" M_COUNTRY.COUNTRY_CODE = '" + countryCode + "' ");
// END EDIT SQL
// DEFINE RETURNVALUE
ReturnValue returnValue = new ReturnValue();
GBS_RfpCountry_stBean out_st = new GBS_RfpCountry_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
while (rset.next()) {
out_st.setCountryCode(rset.getString("COUNTRY_CODE"));
out_st.setSubsidiaryCode(rset.getString("SUBSIDIARY_CODE"));
}
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;
}
/**
* Select the mail address list for send mail
* @param CustomerID String
* @param RfpNo String
* @return ReturnValue
* @throws Exception
*/
public ReturnValue selectEmailList(String CustomerID, String RfpNo) throws Exception {
//Add by Gxk 2004/09/10 Start
CustomerID = BaseCommonCheck.convertSql(CustomerID);
RfpNo = BaseCommonCheck.convertSql(RfpNo);
//Add by Gxk 2004/09/10 End
StringBuffer sql = new StringBuffer();
// ///////////////////////////////////////////////////////////////////////////
// EDIT SQL
sql.append(" SELECT");
sql.append(" DISTINCT M_MEMBER.E_MAIL");
sql.append(" FROM");
sql.append(" T_RFP_COUNTRY,");
sql.append(" M_MEMBER");
sql.append(" WHERE");
sql.append(" M_MEMBER.E_MAIL IS NOT NULL AND ");
sql.append(" ( (T_RFP_COUNTRY.CUSTOMER_ID='" + CustomerID + "'");
sql.append(" AND T_RFP_COUNTRY.RFP_NO=" + RfpNo);
sql.append(" AND M_MEMBER.SUBSIDIARY_CODE=T_RFP_COUNTRY.SUBSIDIARY_CODE");
sql.append(" ) OR M_MEMBER.MEMBER_KIND IN ('2','3') )");
System.out.println("[INFO] sql = " + sql.toString());
// END EDIT SQL
// DEFINE RETURNVALUE
ReturnValue returnValue = new ReturnValue();
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()) {
list.add(rset.getString("E_MAIL"));
}
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;
}
}?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -