gbs_mcustomer_db.java

对日软件外包 为东芝做的一个全球商业管理系统

		return returnValue;
	}

	/**
				 * update customer info
				 * @param st_Bean GBS_Customer_stBean
				 * @return ReturnValue
				 * @throws Exception
				 */
	public ReturnValue updateByCustmerID(GBS_Customer_stBean st_Bean) throws Exception {
		StringBuffer sql = new StringBuffer();
		StringBuffer sql1 = new StringBuffer();
		ReturnValue returnValue = new ReturnValue();
		MessageList messageList = new MessageList();
		/////////////////////////////////////////////////////////////////////////////
		//EDIT SQL
		sql.append(" SELECT");
		sql.append(" CUSTOMER_ID");
		sql.append(" FROM");
		sql.append(" M_CUSTOMER");
		sql.append(" WHERE");
		sql.append(" CUSTOMER_ID='" + BaseCommonCheck.convertSql(st_Bean.getCustomerID()) + "'");
		//sql.append(" AND (DELETE_FLG <> 'D' OR DELETE_FLG IS NULL)");
		sql.append(" FOR UPDATE");
		System.out.println("[INFO] sql = " + sql.toString());
		Connection conn = null;
		ResultSet rset = null;
		Statement st = null;
		PreparedStatement pstmt = null;
		String strSql = "";
		int bresult = 0;
		try {
			conn = this.datasource.getConnection();
			st = conn.createStatement();
			strSql = sql.toString();
			rset = st.executeQuery(strSql);
			if (!rset.next()) {
				bresult = -1;
				messageList.setMessage("customerId", st_Bean.getCustomerID(), "10000001", Integer.MIN_VALUE);
			}
			if (bresult == 0) {

				sql1.append(" UPDATE M_CUSTOMER SET");
				sql1.append(" DISP_ORDER='" + BaseCommonCheck.convertSql(st_Bean.getDispOrder()) + "',");
				sql1.append(" CUSTOMER_CATEGORY='" + BaseCommonCheck.convertSql(st_Bean.getCustomerCategory()) + "',");
				sql1.append(" CUSTOMER_CODE='" + BaseCommonCheck.convertSql(st_Bean.getCustomerCode()) + "',");
				sql1.append(" CUSTOMER_NAME='" + BaseCommonCheck.convertSql(st_Bean.getCustomerName()) + "',");
				sql1.append(" CUSTOMER_ABBR='" + BaseCommonCheck.convertSql(st_Bean.getCustomerAbbr()) + "',");
				sql1.append(" INDUSTRY='" + BaseCommonCheck.convertSql(st_Bean.getIndustry()) + "',");
				sql1.append(" ADDR1='" + BaseCommonCheck.convertSql(st_Bean.getAddr1()) + "',");
				sql1.append(" ADDR2='" + BaseCommonCheck.convertSql(st_Bean.getAddr2()) + "',");
				sql1.append(" ZIP_CODE='" + BaseCommonCheck.convertSql(st_Bean.getZipCode()) + "',");
				sql1.append(" COUNTRY_CODE='" + BaseCommonCheck.convertSql(st_Bean.getCountryCode()) + "',");
				sql1.append(" TELEPHONE='" + BaseCommonCheck.convertSql(st_Bean.getTelephone()) + "',");
				sql1.append(" FAX='" + BaseCommonCheck.convertSql(st_Bean.getFax()) + "',");
				sql1.append(" E_MAIL='" + BaseCommonCheck.convertSql(st_Bean.getEmail()) + "',");
				sql1.append(" CONTACT_PERSON='" + BaseCommonCheck.convertSql(st_Bean.getContactPerson()) + "',");
				sql1.append(" URL='" + BaseCommonCheck.convertSql(st_Bean.getUrl()) + "',");
				//		sql1.append(" DELETE_FLG='" + st_Bean.getDeleteFlg() + "',");
				sql1.append(" NUMBER_OF_EMPLOYEE='" + BaseCommonCheck.convertSql(st_Bean.getNumberOfEmployee()) + "',");
				sql1.append(" UPDATE_USER='" + BaseCommonCheck.convertSql(st_Bean.getUpdateUser()) + "',");
				sql1.append(" UPDATE_DATE= SYSDATE,");
				sql1.append(" DELETE_FLG= ''");
				sql1.append(" WHERE");
				sql1.append(" CUSTOMER_ID='" + BaseCommonCheck.convertSql(st_Bean.getCustomerID()) + "'");
				System.out.println(sql1.toString());
			} //END EDIT SQL		
			/////////////////////////////////////////////////////////////////////////////
			pstmt = conn.prepareStatement(sql1.toString());
			bresult = pstmt.executeUpdate();
			////////////////////////////////////////////////////////////////////////////
			//GET DATA FROM DB RESULTSET
			if (bresult > 0) {
				conn.commit();
			} else {
				conn.rollback();
			} //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) {
			}
			if (messageList.size() > 0) {
				returnValue.setMessageList(messageList);
				returnValue.setBussinessError();
			}
		}
		return returnValue;
	} /**
								 * insert customer info
								 * @param st_Bean GBS_Customer_stBean
								 * @return ReturnValue
								 * @throws Exception
								 */
	public ReturnValue insertByCustmerID(GBS_Customer_stBean st_Bean) throws Exception {
		StringBuffer sql = new StringBuffer();
		StringBuffer sql1 = new StringBuffer();
		ReturnValue returnValue = new ReturnValue();
		MessageList messageList = new MessageList();
		/////////////////////////////////////////////////////////////////////////////
		//EDIT SQL
		sql.append(" SELECT");
		sql.append(" CUSTOMER_ID");
		sql.append(" FROM");
		sql.append(" M_CUSTOMER");
		sql.append(" WHERE");
		sql.append(" CUSTOMER_ID='" + BaseCommonCheck.convertSql(st_Bean.getCustomerID()) + "'");
		sql1.append(" INSERT INTO   ");
		sql1.append(" M_CUSTOMER (  ");
		sql1.append(" 				CUSTOMER_ID,");
		//屭媞ID
		sql1.append(" 				DISP_ORDER,");
		//昞帵弴彉
		sql1.append(" 				CUSTOMER_CATEGORY,");
		//屭媞暘椶
		sql1.append(" 				CUSTOMER_CODE,");
		//屭媞僐乕僪
		sql1.append(" 				CUSTOMER_NAME,");
		//屭媞柤
		sql1.append(" 				CUSTOMER_ABBR,");
		//屭媞棯柤
		sql1.append(" 				INDUSTRY,");
		//嬈奅
		sql1.append(" 				ADDR1,");
		//廧強侾
		sql1.append(" 				ADDR2,");
		//廧強俀
		sql1.append(" 				ZIP_CODE,");
		//梄曋斣崋
		sql1.append(" 				COUNTRY_CODE,");
		//崙僐乕僪
		sql1.append(" 				TELEPHONE,");
		//揹榖斣崋
		sql1.append(" 				FAX,");
		//FAX斣崋
		sql1.append(" 				E_MAIL,");
		//E_MAIL傾僪儗僗
		sql1.append(" 				CONTACT_PERSON,");
		//愭曽扴摉幰
		sql1.append(" 				URL,");
		//URL
		sql1.append(" 				DELETE_FLG,");
		//DELETE僼儔僌
		sql1.append(" 				NUMBER_OF_EMPLOYEE,");
		//廬嬈堳悢
		sql1.append(" 				CREATE_USER,");
		//嶌惉儐乕僓乕
		sql1.append(" 				CREATE_DATE");
		//嶌惉擔帪
		sql1.append("     		 )  ");
		sql1.append(" VALUES     (  ");
		sql1.append("'" + BaseCommonCheck.convertSql(st_Bean.getCustomerID()) + "',");
		sql1.append("'" + BaseCommonCheck.convertSql(st_Bean.getDispOrder()) + "',");
		sql1.append("'" + BaseCommonCheck.convertSql(st_Bean.getCustomerCategory()) + "',");
		sql1.append("'" + BaseCommonCheck.convertSql(st_Bean.getCustomerCode()) + "',");
		sql1.append("'" + BaseCommonCheck.convertSql(st_Bean.getCustomerName()) + "',");
		sql1.append("'" + BaseCommonCheck.convertSql(st_Bean.getCustomerAbbr()) + "',");
		sql1.append("'" + BaseCommonCheck.convertSql(st_Bean.getIndustry()) + "',");
		sql1.append("'" + BaseCommonCheck.convertSql(st_Bean.getAddr1()) + "',");
		sql1.append("'" + BaseCommonCheck.convertSql(st_Bean.getAddr2()) + "',");
		sql1.append("'" + BaseCommonCheck.convertSql(st_Bean.getZipCode()) + "',");
		sql1.append("'" + BaseCommonCheck.convertSql(st_Bean.getCountryCode()) + "',");
		sql1.append("'" + BaseCommonCheck.convertSql(st_Bean.getTelephone()) + "',");
		sql1.append("'" + BaseCommonCheck.convertSql(st_Bean.getFax()) + "',");
		sql1.append("'" + BaseCommonCheck.convertSql(st_Bean.getEmail()) + "',");
		sql1.append("'" + BaseCommonCheck.convertSql(st_Bean.getContactPerson()) + "',");
		sql1.append("'" + BaseCommonCheck.convertSql(st_Bean.getUrl()) + "',");
		sql1.append("'',");
		sql1.append("'" + BaseCommonCheck.convertSql(st_Bean.getNumberOfEmployee()) + "',");
		sql1.append("'" + BaseCommonCheck.convertSql(st_Bean.getCreateUser()) + "',");
		sql1.append("SYSDATE");
		sql1.append(" 			 )  ");
		System.out.println(sql1.toString());
		//END EDIT SQL		
		/////////////////////////////////////////////////////////////////////////////
		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
			GBS_Customer_stBean stBean = new GBS_Customer_stBean();
			int hitCount = 0;
			while (rset.next()) {
				hitCount++;
			}
			if (hitCount > 0) {
				messageList.setMessage("customerId", stBean.getCustomerID(), "10000002", Integer.MIN_VALUE);
			} else {
				PreparedStatement pstmt = conn.prepareStatement(sql1.toString());
				int countUpdate = pstmt.executeUpdate();
				if (countUpdate > 0) {
					conn.commit();
				} else {
					conn.rollback();
				}
			} //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) {
			}
			if (messageList.size() > 0) {
				returnValue.setMessageList(messageList);
				returnValue.setBussinessError();
			}
		}
		return returnValue;
	} /**
								 * delete the customer info
								 * @param st_Bean GBS_Customer_stBean
								 * @return ReturnValue
								 * @throws Exception
								 */
	public ReturnValue deleteByCustmerID(GBS_Customer_stBean st_Bean) throws Exception {
		StringBuffer sql = new StringBuffer();
		StringBuffer sql1 = new StringBuffer();
		ReturnValue returnValue = new ReturnValue();
		MessageList messageList = new MessageList();
		returnValue.setMessageList(messageList);
		/////////////////////////////////////////////////////////////////////////////
		//EDIT SQL
		sql.append(" SELECT");
		sql.append(" 		CUSTOMER_ID");
		sql.append(" FROM");
		sql.append(" 		M_CUSTOMER");
		sql.append(" WHERE");
		sql.append(" 		CUSTOMER_ID='" + BaseCommonCheck.convertSql(st_Bean.getCustomerID()) + "'");
		//sql.append(" AND 	(DELETE_FLG <> 'D' OR DELETE_FLG IS NULL)");
		sql.append(" FOR UPDATE ");
		sql1.append(" UPDATE M_CUSTOMER SET");
		sql1.append(" DELETE_FLG='D',");
		sql1.append(" UPDATE_USER='" + BaseCommonCheck.convertSql(st_Bean.getUpdateUser()) + "',");
		sql1.append(" UPDATE_DATE= SYSDATE");
		sql1.append(" WHERE");
		sql1.append(" CUSTOMER_ID='" + BaseCommonCheck.convertSql(st_Bean.getCustomerID()) + "'");
		//		System.out.println(sql.toString());
		//END EDIT SQL		
		/////////////////////////////////////////////////////////////////////////////
		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
			GBS_Customer_stBean stBean = new GBS_Customer_stBean();
			int hitCount = 0;
			while (rset.next()) {
				hitCount++;
			}
			if (hitCount == 0) {
				messageList.setMessage("customerId", stBean.getCustomerID(), "10000001", Integer.MIN_VALUE);
				returnValue.setBussinessError();
			} else {
				PreparedStatement pstmt = conn.prepareStatement(sql1.toString());
				int countUpdate = pstmt.executeUpdate();
				if (countUpdate > 0) {
					conn.commit();
				} else {
					conn.rollback();
				}
			} //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) {
			}
			if (messageList.size() > 0) {
				returnValue.setMessageList(messageList);
				returnValue.setBussinessError();
			}
		}
		return returnValue;
	}
}