📄 gbs_mproduct_db.java
字号:
sql2.append(" KIND, ");
sql2.append(" PRODUCT_CATEGORY, ");
sql2.append(" OPTION_CATEGORY, ");
sql2.append(" COLOR_CPM, ");
sql2.append(" BW_CPM, ");
sql2.append(" MONOCOLOR_CPM, ");
sql2.append(" OTHER_CPM, ");
sql2.append(" VERSION_NAME, ");
sql2.append(" VERSION_LIST, ");
sql2.append(" DELETE_FLG, ");
sql2.append(" CREATE_USER, ");
sql2.append(" CREATE_DATE ");
sql2.append(" ) VALUES ( ");
sql2.append(" '" + ProductID + "', "); // PRODUCT_ID=
sql2.append(" '" + DispOrder + "', "); //DISP_ORDER=
sql2.append(" '" + ProductName + "', "); // PRODUCT_NAME=
sql2.append(" '" + ProductAbbrName + "', "); // PRODUCT_ABBR_NAME
sql2.append(" '" + Kind + "', "); // KIND=
sql2.append(" '" + ProductCategory + "', "); // PRODUCT_CATEGORY=
sql2.append(" '" + OptionCategory + "', "); // OPTION_CATEGORY=
sql2.append(" '" + ColorCpm + "', "); // COLOR_CPM
sql2.append(" '" + BwCpm + "', "); // BW_CPM
sql2.append(" '" + MonoColorCpm + "', "); // MONOCOLOR_CPM
sql2.append(" '" + OtherCpm + "', "); // OTHER_CPM
sql2.append(" '' , "); // VERSION_NAME
sql2.append(" null,"); // VERSION_LIST
sql2.append(" '" + DelFlag + "', "); // DELETE_FLG
sql2.append(" '" + CreateUser + "', ");
sql2.append(" SYSDATE"); //UPDATE DATE
sql2.append(" )");
System.out.println("[INFO] sql = " + sql2.toString());
// END EDIT SQL
// DEFINE RETURNVALUE
boolean isNotExist = true;
int retCode = 0;
ResultSet rset = null;
Connection conn = null;
Statement st = null;
// END
// ///////////////////////////////////////////////////////////////////////////
try {
conn = this.datasource.getConnection();
st = conn.createStatement();
rset = st.executeQuery(sql1.toString());
// //////////////////////////////////////////////////////////////////////////
// GET DATA FROM DB RESULTSET
while (rset.next()) {
isNotExist = false;
}
if (isNotExist) {
PreparedStatement pstmt = conn.prepareStatement(sql2.toString());
retCode = pstmt.executeUpdate();
if (retCode > 0 && VersionList != null && !VersionList.getFileName().equals("")) {
ReturnValue returnValue = updateAttachmentFile(ProductID, VersionList, conn);
if (returnValue.isError()) {
if (returnValue.isBussinessError()) {
messageList.addAll(returnValue.getMessageList());
}
returnValueD.setErrorCode(returnValue.getErrorCode());
returnValueD.setMessageList(returnValue.getMessageList());
} else {
retCode = ((Integer) returnValue.getDataValue()).intValue();
}
}
} else {
messageList.setMessage("productId", ProductID, "10000002", Integer.MIN_VALUE);
returnValueD.setBussinessError();
returnValueD.setMessageList(messageList);
}
// 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 {
returnValueD.setDataValue(new Integer(retCode));
try {
//Commit data
if (retCode > 0 && !returnValueD.isError()) {
conn.commit();
} else {
conn.rollback();
}
if (rset != null) {
rset.close();
}
if (st != null) {
st.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException se) {
}
}
return returnValueD;
}
// INSERT VALUE END
// UPDATE DATA START
public ReturnValue updateByProductId(
String ProductID,
String DispOrder,
String ProductName,
String ProductAbbrName,
String Kind,
String ProductCategory,
String OptionCategory,
String ColorCpm,
String BwCpm,
String MonoColorCpm,
String OtherCpm,
FormFile VersionList,
String CreateUser,
boolean FileDelete)
throws Exception {
//Add by Gxk 2004/09/10 Start
ProductID = BaseCommonCheck.convertSql(ProductID);
ProductName = BaseCommonCheck.convertSql(ProductName);
DispOrder = BaseCommonCheck.convertSql(DispOrder);
ProductAbbrName = BaseCommonCheck.convertSql(ProductAbbrName);
ProductCategory = BaseCommonCheck.convertSql(ProductCategory);
OptionCategory = BaseCommonCheck.convertSql(OptionCategory);
Kind = BaseCommonCheck.convertSql(Kind);
ColorCpm = BaseCommonCheck.convertSql(ColorCpm);
MonoColorCpm = BaseCommonCheck.convertSql(MonoColorCpm);
OtherCpm = BaseCommonCheck.convertSql(OtherCpm);
BwCpm = BaseCommonCheck.convertSql(BwCpm);
CreateUser = BaseCommonCheck.convertSql(CreateUser);
//Add by Gxk 2004/09/10 End
StringBuffer sql1 = new StringBuffer();
StringBuffer sql2 = new StringBuffer();
ReturnValue returnValueD = new ReturnValue();
MessageList messageList = new MessageList();
returnValueD.setMessageList(messageList);
// ///////////////////////////////////////////////////////////////////////////
// EDIT SQL
//edit sql1:select date from DB by userid
sql1.append(" SELECT ");
sql1.append(" PRODUCT_ID ");
sql1.append(" FROM ");
sql1.append(" M_PRODUCT ");
//sql1.append(" WHERE (DELETE_FLG<>'D' OR DELETE_FLG IS NULL) ");
sql1.append(" WHERE PRODUCT_ID = '" + ProductID + "' ");
sql1.append(" FOR UPDATE");
System.out.println("[INFO] sql = " + sql1.toString());
//edit sql2:update date at DB by in_st
sql2.append(" UPDATE M_PRODUCT SET ");
sql2.append(" DISP_ORDER = '" + DispOrder + "', ");
sql2.append(" PRODUCT_NAME = '" + ProductName + "', ");
sql2.append(" PRODUCT_ABBR_NAME = '" + ProductAbbrName + "', ");
sql2.append(" PRODUCT_CATEGORY = '" + ProductCategory + "', ");
sql2.append(" OPTION_CATEGORY = '" + OptionCategory + "', ");
sql2.append(" COLOR_CPM = '" + ColorCpm + "', ");
sql2.append(" BW_CPM = '" + BwCpm + "', ");
sql2.append(" MONOCOLOR_CPM = '" + MonoColorCpm + "', ");
sql2.append(" KIND = '" + Kind + "', ");
sql2.append(" OTHER_CPM = '" + OtherCpm + "', ");
if (FileDelete) {
sql2.append(" VERSION_NAME = '', ");
sql2.append(" VERSION_LIST = null, ");
}
sql2.append(" DELETE_FLG = '', ");
sql2.append(" UPDATE_USER = '" + CreateUser + "', ");
sql2.append(" UPDATE_DATE = SYSDATE ");
sql2.append(" WHERE ");
sql2.append(" PRODUCT_ID = '" + ProductID + "'");
System.out.println("[INFO] sql = " + sql2.toString());
// END EDIT SQL
// DEFINE RETURNVALUE
boolean isExist = false;
int retCode = 0;
// END
// ///////////////////////////////////////////////////////////////////////////
ResultSet rset = null;
Connection conn = null;
Statement st = null;
try {
conn = this.datasource.getConnection();
st = conn.createStatement();
rset = st.executeQuery(sql1.toString());
// //////////////////////////////////////////////////////////////////////////
// GET DATA FROM DB RESULTSET
while (rset.next()) {
isExist = true;
}
if (isExist) {
PreparedStatement pstmt = conn.prepareStatement(sql2.toString());
retCode = pstmt.executeUpdate();
if (retCode > 0 && VersionList != null && !VersionList.getFileName().equals("")) {
ReturnValue returnValue = updateAttachmentFile(ProductID, VersionList, conn);
if (returnValue.isError()) {
if (returnValue.isBussinessError()) {
messageList.addAll(returnValue.getMessageList());
}
returnValueD.setErrorCode(returnValue.getErrorCode());
returnValueD.setMessageList(returnValue.getMessageList());
} else {
retCode = ((Integer) returnValue.getDataValue()).intValue();
}
}
} else {
messageList.setMessage("", "", "10000001", Integer.MIN_VALUE);
returnValueD.setMessageList(messageList);
returnValueD.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 {
returnValueD.setDataValue(new Integer(retCode));
try {
//Commit data
if (retCode > 0 && !returnValueD.isError()) {
conn.commit();
} else {
conn.rollback();
}
//CLOSE DB CONN
if (rset != null) {
rset.close();
}
if (st != null) {
st.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException se) {
}
}
return returnValueD;
}
/**
* get product code and name list by sql
* @return ArrayList
* @exception ExceptionException for information of other errors
* @since 2004/08/02
* @author Gxk
*/
public ReturnValue getProductCodeNameList() throws Exception {
ReturnValue returnValueD = new ReturnValue();
MessageList messageList = new MessageList();
returnValueD.setMessageList(messageList);
StringBuffer sql = new StringBuffer();
/////////////////////////////////////////////////////////////////////////////
//EDIT SQL
//SELECT PRODUCT_ID,PRODUCT_ABBR_NAME FROM M_PRODUCT ORDER BY PRODUCT_ID
sql.append("SELECT ");
sql.append(" PRODUCT_ID, "); //惢昳ID
sql.append(" PRODUCT_ABBR_NAME, "); //惢昳柤
sql.append(" COLOR_CPM,"); //僇儔乕
sql.append(" BW_CPM "); //敀崟
sql.append("FROM ");
sql.append(" M_PRODUCT ");
sql.append("WHERE ");
sql.append(" DELETE_FLG <> 'D' OR DELETE_FLG IS NULL ");
//Add by Gxk 2004/09/14 Start
sql.append(" AND KIND = 'P' ");
//Add by Gxk 2004/09/14 End
sql.append("ORDER BY ");
sql.append(" DISP_ORDER, PRODUCT_ID ");
System.out.println("SQL: " + sql.toString());
//END EDIT SQL
//DEFINE RETURNVALUE
ArrayList returnList = new ArrayList();
//END
/////////////////////////////////////////////////////////////////////////////
ResultSet rset = null;
Connection conn = null;
Statement st = null;
returnValueD = new ReturnValue();
try {
conn = this.datasource.getConnection();
st = conn.createStatement();
rset = st.executeQuery(sql.toString());
////////////////////////////////////////////////////////////////////////////
//GET DATA FROM DB RESULTSET
HashMap map = new HashMap();
ArrayList bwList = new ArrayList();
ArrayList colorList = new ArrayList();
while (rset.next()) {
GBS_CodeName_stBean codeName_st = new GBS_CodeName_stBean();
codeName_st.setCode(rset.getString("PRODUCT_ID"));
codeName_st.setName(rset.getString("PRODUCT_ABBR_NAME"));
returnList.add(codeName_st);
if (BaseCommonCheck.isEmpty(rset.getString("BW_CPM"))) {
bwList.add("0");
} else {
bwList.add(rset.getString("BW_CPM"));
}
if (BaseCommonCheck.isEmpty(rset.getString("COLOR_CPM"))) {
colorList.add("0");
} else {
colorList.add(rset.getString("COLOR_CPM"));
}
}
map.put(PRODUCTCODENAMELIST, returnList);
map.put(PRODUCTBWLIST, bwList);
map.put(PRODUCTCOLORLIST, colorList);
returnValueD.setDataValue(map);
//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;
}
// *******************************************************************
// deleteByProductID add by:sl START 2004-08-03
public ReturnValue deleteByProductID(String productID, String userId) throws Exception {
//Add by Gxk 2004/09/10 Start
productID = BaseCommonCheck.convertSql(productID);
userId = BaseCommonCheck.convertSql(userId);
//Add by Gxk 2004/09/10 End
StringBuffer sql1 = new StringBuffer();
StringBuffer sql2 = new StringBuffer();
ReturnValue returnValueD = new ReturnValue();
MessageList messageList = new MessageList();
returnValueD.setMessageList(messageList);
// ///////////////////////////////////////////////////////////////////////////
// EDIT SQL
//edit sql1:select date from DB by productID
sql1.append(" SELECT ");
sql1.append(" PRODUCT_ID, ");
sql1.append(" DELETE_FLG ");
sql1.append(" FROM ");
sql1.append(" M_PRODUCT ");
sql1.append(" WHERE PRODUCT_ID = '" + productID + "' ");
//sql1.append(" AND (DELETE_FLG<>'D' OR DELETE_FLG IS NULL)");
//sql1.append(" FOR UPDATE");
//edit sql2:update date at DB by in_st
sql2.append(" UPDATE M_PRODUCT SET DELETE_FLG = 'D',");
//Add by Gxk 2004/08/23
sql2.append(" UPDATE_USER = '" + userId + "',");
sql2.append(" UPDATE_DATE = SYSDATE");
//end add
sql2.append(" WHERE ");
sql2.append(" PRODUCT_ID = '" + productID + "' ");
// END EDIT SQL
// DEFINE RETURNVALUE
boolean isExist = false;
// END
// ///////////////////////////////////////////////////////////////////////////
ResultSet rset = null;
Connection conn = null;
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -