gbs_mserviceinfo_db.java

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

			pstmt = conn.prepareStatement(Sql);
			//bresult= st.execute(Sql);
			bresult = pstmt.executeUpdate();

			if (bresult > 0 && AttachmentFile != null && !AttachmentFile.getFileName().equals("")) {
				ReturnValue returnValue = updateAttachmentFile(seqno, AttachmentFile, conn);
				if (returnValue.isError()) {
					if (returnValue.isBussinessError()) {
						messageList.addAll(returnValue.getMessageList());
					}
					returnValueD.setErrorCode(returnValue.getErrorCode());
					returnValueD.setMessageList(returnValue.getMessageList());
				} else {
					bresult = ((Integer) returnValue.getDataValue()).intValue();
				}
			}

		} 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(bresult));
			//release db
			try {

				if (bresult > 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) {
				throw se;
			}
		}
		return returnValueD;
	}

	/**
	 * method updateService  
	 * @param String Seqno
	 * @return ReturnValue
	 * @throws Exception
	 */

	public ReturnValue deleteService(String seqno) throws Exception {
		//Add by Gxk 2004/09/10 Start
		seqno = BaseCommonCheck.convertSql(seqno);
		//Add by Gxk 2004/09/10 End
		List retdata = new Vector();
		StringBuffer sql = new StringBuffer();
		ReturnValue returnValueD = new ReturnValue();
		sql.append(" SELECT ");
		sql.append(" 		SEQ_NO ");
		sql.append(" FROM   ");
		sql.append("  		M_SERVICE_INFO");
		sql.append(" WHERE	 ");
		sql.append(" 		SEQ_NO = '" + seqno + "' ");
		sql.append(" FOR UPDATE ");
		System.out.println("[INFO] SQL = " + sql.toString());

		ResultSet rset = null;
		Connection conn = null;
		Statement st = null;
		PreparedStatement pstmt = null;
		int bresult = 0;
		String strSql = "";
		try {
			strSql = sql.toString();
			conn = this.datasource.getConnection();
			st = conn.createStatement();
			rset = st.executeQuery(strSql);
			if (rset.next()) {
				bresult = 1;
			}
			if (bresult > 0) {
				strSql = "";
				sql = new StringBuffer();
				sql.append(" DELETE FROM M_SERVICE_INFO ");
				sql.append(" WHERE ");
				sql.append(" M_SERVICE_INFO.SEQ_NO ='");
				sql.append(seqno + "'");

				strSql = sql.toString();
				System.out.println("[info]GBS_MServiceinfo_DB.deleteService.sql=" + strSql);
				pstmt = conn.prepareStatement(strSql);
				bresult = pstmt.executeUpdate();
				if (bresult > 0) {
					conn.commit();
				} else {
					conn.rollback();
				}
			}
		} 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 (pstmt != null) {
					pstmt.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException se) {
			}
		}
		returnValueD.setDataValue(new Integer(bresult));
		return returnValueD;
	}

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

	public ReturnValue updateAttachmentFile(String strSeqNo, FormFile AttachmentFile, Connection conn) throws Exception {
		//Add by Gxk 2004/09/10 Start
		strSeqNo = BaseCommonCheck.convertSql(strSeqNo);
		//Add by Gxk 2004/09/10 End
		StringBuffer sql = new StringBuffer();
		ReturnValue returnValueD = new ReturnValue();
		MessageList messageList = new MessageList();
		returnValueD.setMessageList(messageList);

		int ret = 0;

		sql.append(" SELECT");
		sql.append("  M_SERVICE_INFO.ATTACHMENT_FILE, ATTACHMENT_FILE_NAME"); //
		sql.append(" FROM ");
		sql.append("  M_SERVICE_INFO ");
		sql.append(" WHERE ");
		sql.append("  M_SERVICE_INFO.SEQ_NO =");
		sql.append(strSeqNo);
		sql.append(" FOR UPDATE ");
		System.out.println("[INFO] SQL = " + sql.toString());
		ResultSet rset = null;
		Statement st = null;
		PreparedStatement pstmt = null;
		BLOB blob = null;

		try {

			ReturnValue returnValue = deleteAttachmentFile(strSeqNo, 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("ATTACHMENT_FILE");
			} else {
				ret = -1;
			}
			if (ret == 0) {
				sql = new StringBuffer();
				sql.append(" UPDATE M_SERVICE_INFO SET ATTACHMENT_FILE = ? ,");
				sql.append("  ATTACHMENT_FILE_NAME = ?");
				sql.append(" WHERE ");
				sql.append("  M_SERVICE_INFO.SEQ_NO = ?");
				System.out.println("[INFO] SQL = " + sql.toString());
				pstmt = conn.prepareStatement(sql.toString());
				OutputStream out = blob.getBinaryOutputStream();
				out.write(AttachmentFile.getFileData());
				out.close();
				pstmt.setBlob(1, blob);
				pstmt.setString(2, AttachmentFile.getFileName());
				pstmt.setString(3, strSeqNo);
				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;
	}

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

	public ReturnValue deleteAttachmentFile(String strSeqNo, Connection conn) throws Exception {
		//Add by Gxk 2004/09/10 Start
		strSeqNo = BaseCommonCheck.convertSql(strSeqNo);
		//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("  M_SERVICE_INFO.ATTACHMENT_FILE, ATTACHMENT_FILE_NAME"); //
		sql.append(" FROM ");
		sql.append("  M_SERVICE_INFO ");
		sql.append(" WHERE ");
		sql.append("  M_SERVICE_INFO.SEQ_NO =");
		sql.append(strSeqNo);
		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_SERVICE_INFO SET ATTACHMENT_FILE = EMPTY_BLOB() ,");
				sql.append("  ATTACHMENT_FILE_NAME = ''");
				sql.append(" WHERE ");
				sql.append("  M_SERVICE_INFO.SEQ_NO = ?");
				System.out.println("[INFO] SQL = " + sql.toString());
				pstmt = conn.prepareStatement(sql.toString());
				pstmt.setString(1, strSeqNo);
				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;
	}

	/**
	 * method updateService  
	 * @param String Seqno
	 * @param HttpServletResponse response
	 * @return ReturnValue
	 * @throws Exception
	 */

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

		GBS_File_stBean GbsFilestBean = new GBS_File_stBean();
		ReturnValue returnValueD = new ReturnValue();
		//FormFile formFile = new FormFile();
		sql.append(" SELECT");
		sql.append("   M_SERVICE_INFO.ATTACHMENT_FILE, ATTACHMENT_FILE_NAME"); //
		sql.append(" FROM ");
		sql.append("   M_SERVICE_INFO ");
		sql.append(" WHERE ");
		sql.append("   M_SERVICE_INFO.SEQ_NO =");
		sql.append(strSeqNo);
		ResultSet rset = null;
		Connection conn = null;
		Statement st = null;
		try {
			conn = this.datasource.getConnection();
			st = conn.createStatement();
			rset = st.executeQuery(sql.toString());

			if (rset.next()) {
				BLOB blob = (BLOB) rset.getBlob("ATTACHMENT_FILE");
				String fileName = rset.getString("ATTACHMENT_FILE_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();
			}
			//returnValueD.setDataValue(GbsFilestBean);			
		} 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;
	}

}