?? gbs_tproductprice_db.java
字號:
package DB;
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.ArrayList;
import java.util.Calendar;
import java.util.Locale;
import javax.sql.DataSource;
import COMMON.BaseCommonCheck;
import COMMON.MessageList;
import COMMON.ReturnValue;
import COMMON.SystemConstants;
import DataBean.GBS_CPCPrice_stBean;
import DataBean.GBS_HardwarePrice_stBean;
import DataBean.GBS_TProductPrice_stBean;
/**
* public class GBS_TProductPrice_DB implements SystemConstants
* created on 07-30-2004
* @author wxf
* @version 1.0
*/
public class GBS_TProductPrice_DB implements SystemConstants{
private DataSource datasource = null;
/**
* get datasource from logic
* @param datasource
*/
public GBS_TProductPrice_DB(DataSource datasource) {
this.datasource = datasource;
}
/**
* selectOneByMaxDate
* @param GBS_TProductPrice_stBean in
* @exception Exception Exception for information of other errors
* @since 2004/07/30
*/
public ReturnValue selectOneByMaxDate(GBS_TProductPrice_stBean in)throws Exception{
//Add by YM 2004/09/10 Start
in.setProductID(BaseCommonCheck.convertSql(in.getProductID()));
in.setCountryCode(BaseCommonCheck.convertSql(in.getCountryCode()));
in.setPriceCategory(BaseCommonCheck.convertSql(in.getPriceCategory()));
//Add by YM 2004/09/10 End
String sql = new String();
ReturnValue ret =new ReturnValue();
/////////////////////////////////////////////////////////////////////////////
//EDIT SQL
sql = " SELECT ";
sql += " PRODUCT_ID ,"; //惢昳扨壙僩儔儞僓僋僔儑儞. 惢昳ID
sql += " COUNTRY_CODE ,"; //惢昳扨壙僩儔儞僓僋僔儑儞. 崙僐乕僪
sql += " PRICE_CATEGORY ,"; //惢昳扨壙僩儔儞僓僋僔儑儞. 扨壙暘椶
sql += " DATE_TIME ,"; //惢昳扨壙僩儔儞僓僋僔儑儞. 擔帪
sql += " LOCAL_CURRENCY ,"; //惢昳扨壙僩儔儞僓僋僔儑儞. 尰抧捠壿
sql += " LOCAL_PRICE ,"; //惢昳扨壙僩儔儞僓僋僔儑儞. 尰抧扨壙
sql += " US_PRICE ,"; //惢昳扨壙僩儔儞僓僋僔儑儞. US$扨壙
sql += " EURO_PRICE ,"; //惢昳扨壙僩儔儞僓僋僔儑儞. Euro扨壙
sql += " RATE_US ,"; //惢昳扨壙僩儔儞僓僋僔儑儞. 愝掕帪偺儗乕僩乮懳US)
sql += " RATE_EURO ,"; //惢昳扨壙僩儔儞僓僋僔儑儞. 愝掕帪偺儗乕僩乮懳EURO)
sql += " USER_ID ,"; //惢昳扨壙僩儔儞僓僋僔儑儞. 儐乕僓乕ID
sql += " CREATE_USER ,"; //惢昳扨壙僩儔儞僓僋僔儑儞. 嶌惉儐乕僓乕
sql += " CREATE_DATE ,"; //惢昳扨壙僩儔儞僓僋僔儑儞. 嶌惉擔帪
sql += " UPDATE_USER ,"; //惢昳扨壙僩儔儞僓僋僔儑儞. 峏怴儐乕僓乕
sql += " UPDATE_DATE "; //惢昳扨壙僩儔儞僓僋僔儑儞. 峏怴擔帪
sql += " FROM ";
sql += " T_PRODUCT_PRICE ";//惢昳扨壙僩儔儞僓僋僔儑儞
sql += " WHERE ";
sql += " T_PRODUCT_PRICE.PRODUCT_ID = '"+ in.getProductID()+"' ";//惢昳ID = in.惢昳ID
sql += " AND T_PRODUCT_PRICE.COUNTRY_CODE = '"+ in.getCountryCode()+"' ";//崙僐乕僪 = in.ID崙僐乕僪
sql += " AND T_PRODUCT_PRICE.PRICE_CATEGORY = '"+ in.getPriceCategory()+"'";//扨壙暘椶 = in.扨壙暘椶
sql += " ORDER BY ";
sql += " T_PRODUCT_PRICE.DATE_TIME DESC ";//擔帪
//END EDIT SQL
//DEFINE RETURNVALUE
GBS_TProductPrice_stBean tProductPrice_st = new GBS_TProductPrice_stBean();
//END
/////////////////////////////////////////////////////////////////////////////
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());
////////////////////////////////////////////////////////////////////////////
//GET DATA FROM DB RESULTSET
while (rset.next()) {
tProductPrice_st.setProductID(rset.getString("PRODUCT_ID")==null?"":rset.getString("PRODUCT_ID"));
tProductPrice_st.setCountryCode(rset.getString("COUNTRY_CODE")==null?"":rset.getString("COUNTRY_CODE"));
tProductPrice_st.setPriceCategory(rset.getString("PRICE_CATEGORY")==null?"":rset.getString("PRICE_CATEGORY"));
tProductPrice_st.setDateTime(rset.getString("DATE_TIME")==null?"":rset.getString("DATE_TIME"));
tProductPrice_st.setLocalCurregcy(rset.getString("LOCAL_CURRENCY")==null?"":rset.getString("LOCAL_CURRENCY"));
tProductPrice_st.setLocalPrice(rset.getString("LOCAL_PRICE")==null?"":rset.getString("LOCAL_PRICE"));
tProductPrice_st.setUSPrice(rset.getString("US_PRICE")==null?"":rset.getString("US_PRICE"));
tProductPrice_st.setEUROPrice(rset.getString("EURO_PRICE")==null?"":rset.getString("EURO_PRICE"));
tProductPrice_st.setRateUS(rset.getString("RATE_US")==null?"":rset.getString("RATE_US"));
tProductPrice_st.setRateEURO(rset.getString("RATE_EURO")==null?"":rset.getString("RATE_EURO"));
tProductPrice_st.setUserID(rset.getString("USER_ID")==null?"":rset.getString("USER_ID"));
tProductPrice_st.setCreateUser(rset.getString("CREATE_USER")==null?"":rset.getString("CREATE_USER"));
tProductPrice_st.setCreateDate(rset.getString("CREATE_DATE")==null?"":rset.getString("CREATE_DATE"));
tProductPrice_st.setUpdateUser(rset.getString("UPDATE_USER")==null?"":rset.getString("UPDATE_USER"));
tProductPrice_st.setUpdateDate(rset.getString("UPDATE_DATE")==null?"":rset.getString("UPDATE_DATE"));
break;
}
//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) {
}
}
ret.setDataValue(tProductPrice_st);
return ret;
}
/**
* insert data to T_PRODUCT_PRICE
* @param String userID
* @param GBS_TProductPrice_stBean in
* @exception Exception Exception for information of other errors
* @since 2004/07/30
*/
public void insertByPK(String userID,GBS_TProductPrice_stBean in)throws Exception{
//Add by YM 2004/09/10 Start
in.setProductID(BaseCommonCheck.convertSql(in.getProductID()));
in.setCountryCode(BaseCommonCheck.convertSql(in.getCountryCode()));
in.setPriceCategory(BaseCommonCheck.convertSql(in.getPriceCategory()));
in.setDateTime(BaseCommonCheck.convertSql(in.getDateTime()));
in.setLocalCurregcy(BaseCommonCheck.convertSql(in.getPriceCategory()));
in.setLocalPrice(BaseCommonCheck.convertSql(in.getLocalPrice()));
in.setUSPrice(BaseCommonCheck.convertSql(in.getUSPrice()));
in.setEUROPrice(BaseCommonCheck.convertSql(in.getEUROPrice()));
in.setRateEURO(BaseCommonCheck.convertSql(in.getRateEURO()));
in.setRateUS(BaseCommonCheck.convertSql(in.getRateUS()));
in.setUserID(BaseCommonCheck.convertSql(in.getUserID()));
userID = BaseCommonCheck.convertSql(userID);
//Add by YM 2004/09/10 End
String sql = new String();
ReturnValue ret =new ReturnValue();
/////////////////////////////////////////////////////////////////////////////
//EDIT SQL
sql = " INSERT INTO ";
sql += " T_PRODUCT_PRICE (" ;
sql += " PRODUCT_ID ,";
sql += " COUNTRY_CODE ,";
sql += " PRICE_CATEGORY ,";
sql += " DATE_TIME ,";
sql += " LOCAL_CURRENCY ,";
sql += " LOCAL_PRICE ,";
sql += " US_PRICE ,";
sql += " EURO_PRICE ,";
sql += " RATE_US ,";
sql += " RATE_EURO ,";
sql += " USER_ID ,";
sql += " CREATE_USER ,";
sql += " CREATE_DATE )";
sql += " VALUES ( '";
sql += in.getProductID() +"','";
sql += in.getCountryCode() +"','";
sql += in.getPriceCategory()+"','";
sql += in.getDateTime() +"','";
sql += in.getLocalCurregcy()+"','";
sql += in.getLocalPrice() +"','";
sql += in.getUSPrice() +"','";
sql += in.getEUROPrice() +"','";
sql += in.getRateUS() +"','";
sql += in.getRateEURO() +"','";
sql += in.getUserID() +"','";
sql += userID +"',";
sql += "SYSDATE";
sql += " )";
//END EDIT SQL
//DEFINE RETURNVALUE
//END
/////////////////////////////////////////////////////////////////////////////
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());
conn.commit();
////////////////////////////////////////////////////////////////////////////
//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 (conn != null) {
conn.close();
}
} catch (SQLException se) {
}
}
}
/**
* update data to T_PRODUCT_PRICE
* @param String userID
* @param GBS_TProductPrice_stBean in
* @exception Exception Exception for information of other errors
* @since 2004/07/30
*/
public void updateByPK(String userID,GBS_TProductPrice_stBean in)throws Exception{
//Add by YM 2004/09/10 Start
in.setProductID(BaseCommonCheck.convertSql(in.getProductID()));
in.setCountryCode(BaseCommonCheck.convertSql(in.getCountryCode()));
in.setPriceCategory(BaseCommonCheck.convertSql(in.getPriceCategory()));
in.setDateTime(BaseCommonCheck.convertSql(in.getDateTime()));
in.setLocalCurregcy(BaseCommonCheck.convertSql(in.getPriceCategory()));
in.setLocalPrice(BaseCommonCheck.convertSql(in.getLocalPrice()));
in.setUSPrice(BaseCommonCheck.convertSql(in.getUSPrice()));
in.setEUROPrice(BaseCommonCheck.convertSql(in.getEUROPrice()));
in.setRateEURO(BaseCommonCheck.convertSql(in.getRateEURO()));
in.setRateUS(BaseCommonCheck.convertSql(in.getRateUS()));
in.setUserID(BaseCommonCheck.convertSql(in.getUserID()));
userID = BaseCommonCheck.convertSql(userID);
//Add by YM 2004/09/10 End
String sql = new String();
String sql1 = new String();
ReturnValue ret =new ReturnValue();
/////////////////////////////////////////////////////////////////////////////
//EDIT SQL
sql = " SELECT ";
sql += " PRODUCT_ID";
sql += " FROM ";
sql += " T_PRODUCT_PRICE ";
sql += " WHERE " ;
sql += " PRODUCT_ID ='"+in.getProductID() +"'";
sql += " AND COUNTRY_CODE ='"+in.getCountryCode() +"'";
sql += " AND PRICE_CATEGORY ='"+in.getPriceCategory()+"'";
sql += " AND DATE_TIME ='"+in.getDateTime() +"'";
sql += " FOR UPDATE ";
System.out.println( "[INFO] sql = " + sql );
sql1 = " UPDATE ";
sql1 += " T_PRODUCT_PRICE " ;
sql1 += " SET " ;
sql1 += " LOCAL_CURRENCY = '"+in.getLocalCurregcy() +"',";
sql1 += " LOCAL_PRICE = '"+in.getLocalPrice() +"',";
sql1 += " US_PRICE = '"+in.getUSPrice() +"',";
sql1 += " EURO_PRICE = '"+in.getEUROPrice() +"',";
sql1 += " RATE_US = '"+in.getRateUS() +"',";
sql1 += " RATE_EURO = '"+in.getRateEURO() +"',";
sql1 += " USER_ID = '"+in.getUserID() +"',";
sql1 += " UPDATE_USER = '"+userID +"',";
sql1 += " UPDATE_DATE = SYSDATE ";
sql1 += " WHERE " ;
sql1 += " PRODUCT_ID ='"+in.getProductID() +"'";
sql1 += " AND COUNTRY_CODE ='"+in.getCountryCode() +"'";
sql1 += " AND PRICE_CATEGORY ='"+in.getPriceCategory()+"'";
sql1 += " AND DATE_TIME ='"+in.getDateTime() +"'";
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.toString());
if ( rset.next() ){
bresult = 1;
}
if ( bresult > 0 ){
pstmt = conn.prepareStatement( sql1 );
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -