?? gbs_mcountry_db.java
字號:
/**
* method GBS_MCountry_DB.java
* created on 07-30-2004
*
* @author wxf
* @version 1.0
*/
package DB;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;
import javax.sql.DataSource;
import COMMON.ReturnValue;
import COMMON.SystemConstants;
import DataBean.GBS_CodeName_stBean;
import DataBean.GBS_LittleCountry_stBean;
import DataBean.GBS_MCountry_stBean;
import DataBean.GBS_RfpCountry_stBean;
public class GBS_MCountry_DB implements SystemConstants {
private DataSource datasource = null;
/**
* get datasource from logic
* @param datasource
*/
public GBS_MCountry_DB(DataSource datasource) {
this.datasource = datasource;
}
/**
* select all data from M_COUNTRY
* @return Arraylist of GBS_MCountry_stBean
* @exception Exception Exception for information of other errors
* @since 2004/07/30
*/
public ReturnValue seletAllCountry() throws Exception {
String sql = new String();
/////////////////////////////////////////////////////////////////////////////
//EDIT SQL
sql = " SELECT ";
sql += " COUNTRY_CODE, "; //崙僐乕僪
sql += " DISP_ORDER, "; //昞帵弴彉
sql += " COUNTRY_NAME, "; //崙柤
sql += " REGION_CODE, "; //抧堟僐乕僪
sql += " SUBSIDIARY_CODE, "; //尰朄僐乕僪
//sql += " PLUS_HOUR, "; //擔杮帪娫亄帪娫
sql += " CREATE_USER, "; //嶌惉儐乕僓乕
sql += " CREATE_DATE, "; //嶌惉擔帪
sql += " UPDATE_USER, "; //峏怴儐乕僓乕
sql += " UPDATE_DATE "; //峏怴擔帪
sql += " FROM ";
sql += " M_COUNTRY";
sql += " ORDER BY ";
sql += " DISP_ORDER, COUNTRY_CODE ";
//END EDIT SQL
//DEFINE RETURNVALUE
ArrayList countryList = new ArrayList();
//END
/////////////////////////////////////////////////////////////////////////////
ResultSet rset = null;
Connection conn = null;
Statement st = null;
ReturnValue returnValueD = new ReturnValue();
try {
conn = this.datasource.getConnection();
st = conn.createStatement();
rset = st.executeQuery(sql.toString());
////////////////////////////////////////////////////////////////////////////
//GET DATA FROM DB RESULTSET
while (rset.next()) {
GBS_MCountry_stBean mCountry_st = new GBS_MCountry_stBean();
mCountry_st.setCountryCode(rset.getString("COUNTRY_CODE"));
mCountry_st.setDispOrder(rset.getString("DISP_ORDER"));
mCountry_st.setCountryName(rset.getString("COUNTRY_NAME"));
mCountry_st.setRegionCode(rset.getString("REGION_CODE"));
mCountry_st.setSubsidiaryCode(rset.getString("SUBSIDIARY_CODE"));
//mCountry_st.setPlusHour(rset.getString("PLUS_HOUR"));
mCountry_st.setCreateUser(rset.getString("CREATE_USER"));
mCountry_st.setCreateDate(rset.getString("CREATE_DATE"));
mCountry_st.setUpdateUser(rset.getString("UPDATE_USER"));
mCountry_st.setUpdateDate(rset.getString("UPDATE_DATE"));
countryList.add(mCountry_st);
}
returnValueD.setDataValue(countryList);
//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 returnValueD;
}
/**
* select name from M_COUNTRY
* @return Arraylist of GBS_MCountry_stBean
* @exception Exception Exception for information of other errors
* @since 2004/07/30
*/
public ReturnValue seletCountryName(String countryCode) throws Exception {
//countryCode = BaseCommonCheck.convertSql(countryCode);
String sql = new String();
/////////////////////////////////////////////////////////////////////////////
//EDIT SQL
sql = " SELECT ";
sql += " COUNTRY_CODE,";
sql += " COUNTRY_NAME ";
sql += " FROM ";
sql += " M_COUNTRY";
sql += " WHERE ";
sql += " M_COUNTRY.COUNTRY_CODE IN (" + countryCode + " )";
sql += " ORDER BY ";
sql += " DISP_ORDER, COUNTRY_CODE";
//END EDIT SQL
//DEFINE RETURNVALUE
ArrayList countryList = new ArrayList();
//END
/////////////////////////////////////////////////////////////////////////////
ResultSet rset = null;
Connection conn = null;
Statement st = null;
ReturnValue returnValueD = new ReturnValue();
try {
conn = this.datasource.getConnection();
st = conn.createStatement();
rset = st.executeQuery(sql.toString());
////////////////////////////////////////////////////////////////////////////
//GET DATA FROM DB RESULTSET
while (rset.next()) {
GBS_LittleCountry_stBean littleCountry_st = new GBS_LittleCountry_stBean();
littleCountry_st.setCountryCode(rset.getString("COUNTRY_CODE"));
littleCountry_st.setCountryName(rset.getString("COUNTRY_NAME"));
countryList.add(littleCountry_st);
}
returnValueD.setDataValue(countryList);
//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 returnValueD;
}
/**
* get Country by sql
* @return ReturnValue
* @exception ExceptionException for information of other errors
* @since 2004/08/10
*/
public ReturnValue getCountry() throws Exception {
StringBuffer sql = new StringBuffer();
ReturnValue returnValue = new ReturnValue();
/////////////////////////////////////////////////////////////////////////////
//EDIT SQL
sql.append(" SELECT");
sql.append(" COUNTRY_NAME,");
sql.append(" COUNTRY_CODE ");
sql.append(" FROM ");
sql.append(" M_COUNTRY ");
sql.append(" ORDER BY");
sql.append(" DISP_ORDER,COUNTRY_CODE");
//END EDIT SQL
//DEFINE RETURNVALUE
Map returnMap = new HashMap();
//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
ArrayList lstCountryCode = new ArrayList();
ArrayList lstCountryName = new ArrayList();
lstCountryCode.add("-1");
lstCountryName.add("");
while (rset.next()) {
lstCountryCode.add(rset.getString("COUNTRY_CODE"));
lstCountryName.add(rset.getString("COUNTRY_NAME"));
}
returnMap.put(COUNTRY_CODE, lstCountryCode);
returnMap.put(COUNTRY_NAME, lstCountryName);
//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) {
}
}
returnValue.setDataValue(returnMap);
return returnValue;
}
/**
* get CountryCode and SubsCode by sql
* @return ReturnValue
* @exception ExceptionException for information of other errors
* @since 2004/08/10
*/
public ReturnValue getCountryCodeAndSubsCode() throws Exception {
StringBuffer sql = new StringBuffer();
ReturnValue returnValue = new ReturnValue();
//--------------------------------------------------------------------------
//EDIT SQL
sql.append(" SELECT");
sql.append(" SUBSIDIARY_CODE,");
sql.append(" COUNTRY_CODE ");
sql.append(" FROM ");
sql.append(" M_COUNTRY ");
sql.append(" ORDER BY");
sql.append(" DISP_ORDER,COUNTRY_CODE");
//END EDIT SQL
//DEFINE RETURNVALUE
ArrayList returnList = 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()) {
GBS_CodeName_stBean bean = new GBS_CodeName_stBean();
bean.setCode("country" + rset.getString("COUNTRY_CODE"));
bean.setName(rset.getString("SUBSIDIARY_CODE"));
returnList.add(bean);
}
//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) {
}
}
returnValue.setDataValue(returnList);
return returnValue;
}
public ReturnValue seletAllCountryNameByCountrySubsidiary() throws Exception {
StringBuffer sql = new StringBuffer();
/////////////////////////////////////////////////////////////////////////////
//EDIT SQL
sql.append(" SELECT");
sql.append(" M_COUNTRY.COUNTRY_CODE, ");
sql.append(" M_COUNTRY.COUNTRY_NAME,");
sql.append(" M_SUBSIDIARY.SUBSIDIARY_CODE, ");
sql.append(" M_SUBSIDIARY.SUBSIDIARY_NAME");
sql.append(" FROM ");
sql.append(" M_COUNTRY, ");
sql.append(" M_SUBSIDIARY ");
sql.append(" WHERE ");
sql.append(" M_COUNTRY.SUBSIDIARY_CODE = M_SUBSIDIARY.SUBSIDIARY_CODE(+) ");
sql.append(" ORDER BY ");
sql.append(" M_COUNTRY.DISP_ORDER,M_COUNTRY.COUNTRY_CODE");
//END EDIT SQL
//DEFINE RETURNVALUE
ArrayList countryList = new ArrayList();
//END
/////////////////////////////////////////////////////////////////////////////
ResultSet rset = null;
Connection conn = null;
Statement st = null;
ReturnValue returnValueD = new ReturnValue();
try {
conn = this.datasource.getConnection();
st = conn.createStatement();
rset = st.executeQuery(sql.toString());
////////////////////////////////////////////////////////////////////////////
//GET DATA FROM DB RESULTSET
while (rset.next()) {
GBS_RfpCountry_stBean RfpCountry_stBean = new GBS_RfpCountry_stBean();
RfpCountry_stBean.setCountryCode(rset.getString("COUNTRY_CODE"));
RfpCountry_stBean.setCountryName(rset.getString("COUNTRY_NAME"));
RfpCountry_stBean.setSubsidiaryCode(rset.getString("SUBSIDIARY_CODE"));
RfpCountry_stBean.setSubsidiaryName(rset.getString("SUBSIDIARY_NAME"));
countryList.add(RfpCountry_stBean);
}
returnValueD.setDataValue(countryList);
//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 returnValueD;
}
}?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -