gbs_mproduct_db.java

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

		Statement st = null;

		try {
			conn = this.datasource.getConnection();
			st = conn.createStatement();
			rset = st.executeQuery(sql1.toString());
			//	  //////////////////////////////////////////////////////////////////////////
			//	  GET DATA FROM DB RESULTSET
			while (rset.next()) {
				if (productID.equals(rset.getString("PRODUCT_ID"))) {
					PreparedStatement pstmt = conn.prepareStatement(sql2.toString());
					int countDelete = pstmt.executeUpdate();
					if (countDelete > 0) {
						conn.commit();
					} else {
						conn.rollback();
					}
					isExist = true;
				}
			}
			if (isExist == false) {
				messageList.setMessage("", "", "10000001", Integer.MIN_VALUE);
				returnValueD.setMessageList(messageList);
				returnValueD.setBussinessError();
			} else {
				returnValueD.setDataValue(new Boolean(isExist));
			}
			//	  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 AttachmentFile by sql
	 * @return    byte[]
	 * @exception Exception    Exception for information of other errors
	 * @since 2004/08/02
	 */

	public ReturnValue getAttachmentFile(String strProductID, HttpServletResponse response) throws Exception {
		//Add by Gxk 2004/09/10 Start
		strProductID = BaseCommonCheck.convertSql(strProductID);
		//Add by Gxk 2004/09/10 End
		StringBuffer sql = new StringBuffer();
		ReturnValue returnValueD = new ReturnValue();
		MessageList messageList = new MessageList();
		returnValueD.setMessageList(messageList);

		sql.append(" SELECT");
		sql.append("   VERSION_NAME, VERSION_LIST "); //
		sql.append(" FROM ");
		sql.append("   M_PRODUCT ");
		sql.append(" WHERE ");
		sql.append("   PRODUCT_ID ='" + strProductID + "'");
		System.out.println(sql.toString());

		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());
			if (rset.next()) {
				/*
				BLOB blob = (BLOB) rset.getBlob( "VERSION_LIST" );
				
				response.reset();
				response.setContentType("application");
				OutputStream os=response.getOutputStream();
				response.setHeader("Content-disposition",
								   "attachment;filename=" + rset.getString( "VERSION_NAME" ) );
				
					InputStream is = blob.getBinaryStream();
					byte[] data = new byte[ blob.getBufferSize() ];
					int bytesRead = 0;
					int byteSum = 0;
					while (( bytesRead = is.read( data) ) != -1 ){
						byteSum += bytesRead;
						os.write( data, 0, bytesRead );
					}
					os.close();
					is.close();
				*/
				BLOB blob = (BLOB) rset.getBlob("VERSION_LIST");
				String fileName = rset.getString("VERSION_NAME");
				response.setContentType("APPLICATION/OCTET-STREAM");
				fileName = new String(fileName.getBytes("shift_jis"),"ISO8859-1");
				String disHeader = "Attachment;Filename=" + fileName;
				response.setHeader("Content-Disposition", disHeader);
				ServletOutputStream os = response.getOutputStream();
				InputStream is = blob.getBinaryStream();
				byte[] data = new byte[blob.getBufferSize()];
				int bytesRead = 0;
				int byteSum = 0;
				while ((bytesRead = is.read(data)) != -1) {
					byteSum += bytesRead;
					os.write(data, 0, bytesRead);
				}
				response.setContentLength(byteSum);
				os.flush();
				response.flushBuffer();
				is.close();
				os.close();
			}
		} 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) {
			}
		}
		return returnValueD;
	}

	// METHOD: updateAttachmentFile start
	public ReturnValue updateAttachmentFile(String productID, FormFile attachmentFile, Connection conn)
		throws Exception {
		//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);

		StringBuffer sql = new StringBuffer();

		int ret = 0;

		sql.append(" SELECT VERSION_LIST,VERSION_NAME FROM M_PRODUCT ");
		sql.append(" WHERE PRODUCT_ID = '" + productID + "' ");
		sql.append("  FOR UPDATE ");

		ResultSet rset = null;
		Statement st = null;
		PreparedStatement pstmt = null;
		returnValueD = new ReturnValue();
		BLOB blob = null;
		try {
			ReturnValue returnValue = deleteAttachmentFile(productID, conn);
			if (returnValue.isError()) {
				if (returnValue.isBussinessError()) {
					messageList.addAll(returnValue.getMessageList());
					returnValueD.setBussinessError();
				}
				returnValueD.setErrorCode(returnValue.getErrorCode());
				returnValueD.setErrorMessage(returnValue.getErrorMessage());
			}
			st = conn.createStatement();
			rset = st.executeQuery(sql.toString());
			if (rset.next()) {
				blob = (BLOB) rset.getBlob("VERSION_LIST");
			} else {
				ret = -1;
			}
			if (ret == 0) {
				sql = new StringBuffer();
				sql.append("UPDATE M_PRODUCT SET VERSION_LIST = ? ,");
				sql.append("VERSION_NAME = ?");
				sql.append(" WHERE ");
				sql.append("PRODUCT_ID = ?");
				pstmt = conn.prepareStatement(sql.toString());
				OutputStream out = blob.getBinaryOutputStream();
				//out.flush();
				out.write(attachmentFile.getFileData());
				out.close();
				pstmt.setBlob(1, blob);
				pstmt.setString(2, attachmentFile.getFileName());
				pstmt.setString(3, productID);
				ret = pstmt.executeUpdate();
			}

		} 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(ret));
			//release db
			try {
				if (rset != null) {
					rset.close();
				}
				if (pstmt != null) {
					pstmt.close();
				}
				if (st != null) {
					st.close();
				}
				//commit if no error
			} catch (SQLException se) {
				System.out.println("[Error Happen!]");
				System.out.println("[Start Trace]");
				se.printStackTrace();
				System.out.println("[End Trace]");
				throw se;
			}
		}

		return returnValueD;
	}

	//	/**
	//	 * get product code and name list by sql
	//	 * @return ArrayList
	//	 * @exception ExceptionException for information of other errors
	//	 * @since 2004/08/04
	//	 * @author Gxk
	//	 */
	//	public ReturnValue getCanDownload(String productId) throws Exception {
	//		StringBuffer sql = new StringBuffer();
	//		ReturnValue returnValueD = new ReturnValue();
	//		MessageList messageList = new MessageList();
	//		returnValueD.setMessageList( messageList );
	//		
	///////////////////////////////////////////////////////////////////////////////
	////EDIT SQL
	//		//SELECT PRODUCT_ID,PRODUCT_ABBR_NAME FROM M_PRODUCT ORDER BY PRODUCT_ID
	//		sql.append("SELECT ");
	//		sql.append("VERSION_NAME ");//SUBSIDIARY_CODE
	//		sql.append("FROM ");
	//		sql.append("M_PRODUCT ");
	//		sql.append("WHERE ");
	//		sql.append("PRODUCT_ID ='" + productId + "'");
	////END EDIT SQL		
	////DEFINE RETURNVALUE
	//		boolean retValue = false;
	////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
	//			while (rset.next()) {
	//				String tmpStrVersionName = (rset.getString("VERSION_NAME"));//SUBSIDIARY_CODE
	//				if (tmpStrVersionName!=null && !tmpStrVersionName.trim().equals("")){
	//					retValue = true;
	//				}
	//			}
	////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(new Boolean(retValue));
	//		return returnValueD;
	//	}

	/**
	 * method updateAttachmentFile  
	 * @param String Seqno
	 * @param FormFile AttachmentFile
	 * @param Connection conn 
	 * @return ReturnValue
	 * @throws Exception
	 */

	public ReturnValue deleteAttachmentFile(String productID, Connection conn) throws Exception {
		//Add by Gxk 2004/09/10 Start
		productID = BaseCommonCheck.convertSql(productID);
		//Add by Gxk 2004/09/10 End

		StringBuffer sql = new StringBuffer();
		ReturnValue returnValueD = new ReturnValue();
		MessageList messageList = new MessageList();
		returnValueD.setMessageList(messageList);

		sql.append(" SELECT");
		sql.append("  VERSION_LIST, VERSION_NAME"); //
		sql.append(" FROM ");
		sql.append("  M_PRODUCT ");
		sql.append(" WHERE ");
		sql.append("  PRODUCT_ID ='");
		sql.append(productID + "'");
		sql.append(" FOR UPDATE ");
		System.out.println("[INFO] SQL = " + sql.toString());

		int ret = 0;

		ResultSet rset = null;
		Statement st = null;
		PreparedStatement pstmt = null;

		try {
			st = conn.createStatement();
			rset = st.executeQuery(sql.toString());
			if (rset.next()) {
			} else {
				ret = -1;
			}
			if (ret == 0) {
				sql = new StringBuffer();
				sql.append(" UPDATE M_PRODUCT SET VERSION_LIST = EMPTY_BLOB() ,");
				sql.append("  VERSION_NAME = ''");
				sql.append(" WHERE ");
				sql.append("  PRODUCT_ID = ?");
				System.out.println("[INFO] SQL = " + sql.toString());
				pstmt = conn.prepareStatement(sql.toString());
				pstmt.setString(1, productID);
				ret = pstmt.executeUpdate();
			}

		} 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(ret));
			//release db
			try {
				if (rset != null) {
					rset.close();
				}
				if (pstmt != null) {
					pstmt.close();
				}
				if (st != null) {
					st.close();
				}

			} catch (SQLException se) {
				System.out.println("[Error Happen!]");
				System.out.println("[Start Trace]");
				se.printStackTrace();
				System.out.println("[End Trace]");
				throw se;
			}
		}
		return returnValueD;
	}
}