📄 gbs_mproduct_db.java
字号:
package DB;
import java.io.InputStream;
import java.io.OutputStream;
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 java.util.HashMap;
import java.util.List;
import java.util.Vector;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
import oracle.sql.BLOB;
import org.apache.struts.upload.FormFile;
import COMMON.BaseCommonCheck;
import COMMON.MessageList;
import COMMON.ReturnValue;
import COMMON.SystemConstants;
import DataBean.GBS_CodeName_stBean;
import DataBean.GBS_ProductMasterConditionList_Bean;
/**
* <p>Title: struts sample </p>
* <p>Description: process db access<br>
* </p>
* <p>Copyright: lp Copyright (c) 2003</p>
* @author shaohua
* @version 1.0
*/
public class GBS_MProduct_DB implements SystemConstants {
private DataSource datasource = null;
//private ReturnValue returnValueD = new ReturnValue();
/**
* get datasource from logic
* @param datasource
*/
public GBS_MProduct_DB(DataSource datasource) {
this.datasource = datasource;
}
/**
* Get product info from DB by ProductId
* @param ProductId String
* @return ReturnValue
* @throws Exception
*/
public ReturnValue selectByProductId(String productId) throws Exception {
StringBuffer sql = new StringBuffer();
//Add by Gxk 2004/09/10 Start
productId = BaseCommonCheck.convertSql(productId);
//Add by Gxk 2004/09/10 End
ReturnValue returnValueD = new ReturnValue();
MessageList messageList = new MessageList();
returnValueD.setMessageList(messageList);
// ///////////////////////////////////////////////////////////////////////////
// EDIT SQL
sql.append(" SELECT ");
sql.append(" PRODUCT_ID, ");
sql.append(" DISP_ORDER, ");
sql.append(" PRODUCT_NAME, ");
sql.append(" PRODUCT_ABBR_NAME, ");
sql.append(" KIND, ");
sql.append(" PRODUCT_CATEGORY, ");
sql.append(" OPTION_CATEGORY, ");
sql.append(" COLOR_CPM, ");
sql.append(" BW_CPM, ");
sql.append(" MONOCOLOR_CPM, ");
sql.append(" OTHER_CPM, ");
sql.append(" VERSION_NAME, ");
sql.append(" DELETE_FLG ");
sql.append(" FROM ");
sql.append(" M_PRODUCT ");
sql.append(" WHERE ");
sql.append(" PRODUCT_ID = '" + productId + "' ");
//Add by Gxk 2004/08/23 add delete_flg
//sql.append(" AND (DELETE_FLG<>'D' OR DELETE_FLG IS NULL)");
//END ADD
sql.append(" ORDER BY ");
sql.append(" PRODUCT_ID ");
// END EDIT SQL
// DEFINE RETURNVALUE
GBS_ProductMasterConditionList_Bean out_st = new GBS_ProductMasterConditionList_Bean();
// 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.setProductId(rset.getString("PRODUCT_ID"));
out_st.setProductName(rset.getString("PRODUCT_NAME"));
out_st.setDispOrder(rset.getString("DISP_ORDER"));
out_st.setProductAbbrName(rset.getString("PRODUCT_ABBR_NAME"));
out_st.setKind(rset.getString("KIND"));
out_st.setProductCategory(rset.getString("PRODUCT_CATEGORY"));
out_st.setOptionCategory(rset.getString("OPTION_CATEGORY"));
out_st.setColorCPM(rset.getString("COLOR_CPM"));
out_st.setBwCPM(rset.getString("BW_CPM"));
out_st.setMonoColorCPM(rset.getString("MONOCOLOR_CPM"));
out_st.setOtherCPM(rset.getString("OTHER_CPM"));
out_st.setAttachmentfilename(rset.getString("VERSION_NAME"));
out_st.setDeleteFlag(rset.getString("DELETE_FLG"));
}
// 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) {
}
}
returnValueD.setDataValue(out_st);
return returnValueD;
}
// ****************************************************************
/**
* get usermaster info by sql
* @param UserName
* @param Subsidiary
* @param Committe
* @param Support
* @param Operation
* @param LocalMember
* @return
* @throws Exception
*/
public ReturnValue getProductMasterConditionList(
String ProductID,
String ProductName,
String McKind,
String OptionKind,
String ProductCategory,
String OptionCategory,
String ColorCPM,
String ColorCPM1,
String MonoColorCPM,
String MonoColorCPM1,
String BWCPM,
String BWCPM1,
String OthersCPM,
String OthersCPM1,
String admins)
throws Exception {
//Add by Gxk 2004/09/10 Start
ProductID = BaseCommonCheck.convertSql(ProductID);
ProductName = BaseCommonCheck.convertSql(ProductName);
McKind = BaseCommonCheck.convertSql(McKind);
OptionKind = BaseCommonCheck.convertSql(OptionKind);
ColorCPM = BaseCommonCheck.convertSql(ColorCPM);
ColorCPM1 = BaseCommonCheck.convertSql(ColorCPM1);
MonoColorCPM = BaseCommonCheck.convertSql(MonoColorCPM);
MonoColorCPM1 = BaseCommonCheck.convertSql(MonoColorCPM1);
BWCPM = BaseCommonCheck.convertSql(BWCPM);
BWCPM1 = BaseCommonCheck.convertSql(BWCPM1);
OthersCPM = BaseCommonCheck.convertSql(OthersCPM);
OthersCPM1 = BaseCommonCheck.convertSql(OthersCPM1);
//Add by Gxk 2004/09/10 End
List retdata = new Vector();
ReturnValue returnValueD = new ReturnValue();
MessageList messageList = new MessageList();
returnValueD.setMessageList(messageList);
//Getting DB Connection
StringBuffer sql = new StringBuffer();
sql.append(" SELECT ");
sql.append(" M_PRODUCT.PRODUCT_ID, "); // PRODUCT_ID
sql.append(" M_PRODUCT.PRODUCT_NAME, "); // PRODUCT_NAME
sql.append(" DECODE( M_PRODUCT.KIND, 'P', 'M/C', 'O', 'Option') KIND, "); // KIND
sql.append(" T_PRODUCTCATEGORY.STRING1 PRODUCT_CATEGORY, "); // PRODUCT_CATEGORY
sql.append(" T_OPTIONCATEGORY.STRING1 OPTION_CATEGORY, "); // OPTION_CATEGORY
sql.append(" M_PRODUCT.COLOR_CPM, "); // COLOR_CPM
sql.append(" M_PRODUCT.BW_CPM, "); // BW_CPM
sql.append(" M_PRODUCT.MONOCOLOR_CPM, "); // MONOCOLOR_CPM
sql.append(" M_PRODUCT.OTHER_CPM, "); // OTHER_CPM
sql.append(" M_PRODUCT.VERSION_NAME, ");
sql.append(" M_PRODUCT.DELETE_FLG ");
sql.append("FROM ");
sql.append(" M_PRODUCT, M_WIDE_USE T_PRODUCTCATEGORY, M_WIDE_USE T_OPTIONCATEGORY");
//sql.append(" WHERE ( M_PRODUCT.DELETE_FLG IS NULL OR M_PRODUCT.DELETE_FLG <> 'D' ) ");
sql.append(" WHERE T_PRODUCTCATEGORY.TARGET_USE(+) = 'PRODUCT_CATEGORY' ");
if (!ProductID.equals("")) {
sql.append(" AND M_PRODUCT.PRODUCT_ID LIKE '%" + ProductID + "%' ");
}
if (!ProductName.equals("")) {
sql.append(" AND UPPER(M_PRODUCT.PRODUCT_NAME) LIKE '%" + ProductName.toUpperCase() + "%' ");
}
if (McKind.equals("") && !OptionKind.equals("")) {
sql.append(" AND M_PRODUCT.KIND = '" + OptionKind + "' ");
} else if (!McKind.equals("") && OptionKind.equals("")) {
sql.append(" AND M_PRODUCT.KIND = '" + McKind + "' ");
} else if (!McKind.equals("") && !OptionKind.equals("")) {
sql.append(" AND M_PRODUCT.KIND IN ( '" + McKind + "','" + OptionKind + "' ) ");
}
if (!ProductCategory.equals("")) {
sql.append(" AND M_PRODUCT.PRODUCT_CATEGORY IN ( " + ProductCategory + " ) ");
}
if (!OptionCategory.equals("")) {
sql.append(" AND M_PRODUCT.OPTION_CATEGORY IN ( " + OptionCategory + " ) ");
}
if (!"".equals(ColorCPM)) {
sql.append(" AND M_PRODUCT.COLOR_CPM >= '" + ColorCPM + "' ");
}
if (!"".equals(ColorCPM1)) {
sql.append(" AND M_PRODUCT.COLOR_CPM <= '" + ColorCPM1 + "' ");
}
if (!"".equals(BWCPM)) {
sql.append(" AND M_PRODUCT.BW_CPM >= '" + BWCPM + "' ");
}
if (!"".equals(BWCPM1)) {
sql.append(" AND M_PRODUCT.BW_CPM <= '" + BWCPM1 + "' ");
}
if (!"".equals(MonoColorCPM)) {
sql.append(" AND M_PRODUCT.MONOCOLOR_CPM >= '" + MonoColorCPM + "' ");
}
if (!"".equals(MonoColorCPM1)) {
sql.append(" AND M_PRODUCT.MONOCOLOR_CPM <= '" + MonoColorCPM1 + "' ");
}
if (!"".equals(OthersCPM)) {
sql.append(" AND M_PRODUCT.OTHER_CPM >= '" + OthersCPM + "' ");
}
if (!"".equals(OthersCPM1)) {
sql.append(" AND M_PRODUCT.OTHER_CPM <= '" + OthersCPM1 + "' ");
}
sql.append(" AND T_PRODUCTCATEGORY.MAIN_KEY(+) = M_PRODUCT.PRODUCT_CATEGORY ");
sql.append(" AND T_OPTIONCATEGORY.TARGET_USE(+) = 'OPTION_CATEGORY' ");
sql.append(" AND T_OPTIONCATEGORY.MAIN_KEY(+) = M_PRODUCT.OPTION_CATEGORY ");
sql.append(" ORDER BY ");
sql.append(" M_PRODUCT.DISP_ORDER, M_PRODUCT.PRODUCT_ID ");
System.out.println("[INFO] sql = " + sql.toString());
ResultSet rset = null;
Connection conn = null;
Statement st = null;
try {
//M_SUBSIDIARY setting select data
conn = this.datasource.getConnection();
st = conn.createStatement();
System.out.println("[info]GBS_MProduct_DB.getProductMasterConditionList.sql=" + sql);
rset = st.executeQuery(sql.toString());
while (rset.next()) {
//add by YM 2004/09/13 start
if ((admins != null && admins.equals("1"))
|| ( !BaseCommonCheck.convertNullToSpace(rset.getString("DELETE_FLG")).equals("D"))
) {
//add by YM 2004/09/13 end
GBS_ProductMasterConditionList_Bean line = new GBS_ProductMasterConditionList_Bean();
line.setProductId(rset.getString("PRODUCT_ID"));
line.setProductName(rset.getString("PRODUCT_NAME"));
line.setKind(rset.getString("KIND"));
line.setProductCategory(rset.getString("PRODUCT_CATEGORY"));
line.setOptionCategory(rset.getString("OPTION_CATEGORY"));
line.setColorCPM(rset.getString("COLOR_CPM"));
line.setBwCPM(rset.getString("BW_CPM"));
line.setMonoColorCPM(rset.getString("MONOCOLOR_CPM"));
line.setOtherCPM(rset.getString("OTHER_CPM"));
//add by YM 2004/09/13 start
if (BaseCommonCheck.convertNullToSpace(rset.getString("DELETE_FLG")).equals("D")) {
line.setDeleteFlag("仜");
}
//line.setDeleteFlag(rset.getString("DELETE_FLG"));
//add by YM 2004/09/13 end
String filename = rset.getString("VERSION_NAME");
String filetype = BaseCommonCheck.getFileType(filename);
//line.setAttachmentFile(filetype);
line.setFiletype(filetype);
line.setAttachmentfilename(filename);
retdata.add(line);
}
}
if (retdata.size() == 0) {
messageList.setMessage("", "", "10000004", Integer.MIN_VALUE);
returnValueD.setBussinessError();
}
} catch (Exception exception) {
System.out.println("[Error Happen!]");
System.out.println("[Start Trace]");
exception.printStackTrace();
System.out.println("[End Trace]");
throw exception;
} finally {
//release db
try {
if (rset != null) {
rset.close();
}
if (st != null) {
st.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException se) {
}
}
returnValueD.setDataValue(retdata);
return returnValueD;
}
// ****************************************************************
// INSERT VALUE START
public ReturnValue insertDB(
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 DelFlag,
String CreateUser)
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);
DelFlag = BaseCommonCheck.convertSql(DelFlag);
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 PRODUCT_ID = '" + ProductID + "' ");
System.out.println("[INFO] sql = " + sql1.toString());
//edit sql2:update date at DB by in_st
sql2.append(" INSERT INTO ");
sql2.append(" M_PRODUCT ");
sql2.append(" (PRODUCT_ID, ");
sql2.append(" DISP_ORDER, ");
sql2.append(" PRODUCT_NAME, ");
sql2.append(" PRODUCT_ABBR_NAME, ");
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -