gbs_ttemp_db.java

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

/** 
 * method GBS_MMember_DB.java
 * created on 07-29-2004
 * 
 * @author   GXK
 * @version  1.0
 */

package DB;

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 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;

public class GBS_TTemp_DB implements SystemConstants {

	private DataSource datasource = null;

	/**
	 * get datasource from logic
	 * @param datasource
	 */
	public GBS_TTemp_DB(DataSource datasource) {
		this.datasource = datasource;
	}

	public ReturnValue insertRfpAttachmentIntoTempTable(
		String strSissionID,
		String createUser,
		FormFile attachmentFile)
		throws Exception {

		//Add by Gxk 2004/09/10 Start
		strSissionID = BaseCommonCheck.convertSql(strSissionID);
		createUser = BaseCommonCheck.convertSql(createUser);
		//Add by Gxk 2004/09/10 End
		ReturnValue returnValue = new ReturnValue();
		MessageList messageList = new MessageList();
		returnValue.setMessageList(messageList);

		StringBuffer sql = new StringBuffer();
		int ret = 0;
		int seqNo = 1;
		String selseq = " SELECT MAX(SEQ_NO) AS SEQ  FROM T_TEMP ";

		//曄悢掕媊
		ResultSet rset = null;
		Connection conn = null;
		Statement st = null;
		PreparedStatement pstmt = null;

		try {
			conn = this.datasource.getConnection();
			st = conn.createStatement();
			rset = st.executeQuery(selseq);
			if (rset.next()) {
				seqNo = rset.getInt("SEQ");
				seqNo = seqNo + 1;
			}

			//start sql edit ==========================================		
			sql.append(" INSERT INTO T_TEMP  ");
			sql.append(" VALUES( ");
			sql.append("'" + seqNo + "', ");
			sql.append("'" + strSissionID + "', ");
			sql.append("'', ");
			sql.append("EMPTY_BLOB(), ");
			sql.append("'" + createUser + "', ");
			sql.append("SYSDATE ) ");
			System.out.println(sql.toString());
			//end sql edit ==========================================
			pstmt = conn.prepareStatement(sql.toString());
			ret = pstmt.executeUpdate();

			//AttachFile insert
			if (ret > 0 && attachmentFile != null && !attachmentFile.getFileName().equals("")) {
				ret = updateRfpAttachmentFileInTempTable(seqNo, attachmentFile, conn);
			}
		} 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 (ret > 0) {
					conn.commit();
				} else {
					conn.rollback();
				}
				if (rset != null) {
					rset.close();
				}
				if (pstmt != null) {
					pstmt.close();
				}
				if (st != null) {
					st.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException se) {
				throw se;
			}
		}
		returnValue.setDataValue(new Integer(seqNo));
		return returnValue;
	}

	public int updateRfpAttachmentFileInTempTable(int intSeqNo, FormFile attachmentFile, Connection conn)
		throws Exception {

		StringBuffer sql = new StringBuffer();
		ReturnValue returnValueD = new ReturnValue();
		MessageList messageList = new MessageList();
		returnValueD.setMessageList(messageList);
		int ret = 0;
		sql.append(" SELECT");
		sql.append("   ATTACHMENT_FILE_NAME, ATTACHMENT_FILE");
		sql.append(" FROM ");
		sql.append("   T_TEMP ");
		sql.append(" WHERE ");
		sql.append("   SEQ_NO =");
		sql.append(intSeqNo);
		sql.append(" FOR UPDATE ");

		ResultSet rset = null;
		Statement st = null;
		PreparedStatement pstmt = null;
		BLOB blob = null;
		try {
			ReturnValue returnValue = deleteAttachmentFile(intSeqNo, 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 T_TEMP SET ATTACHMENT_FILE = ? ,");
				sql.append("  ATTACHMENT_FILE_NAME = ?");
				sql.append(" WHERE ");
				sql.append("   SEQ_NO = ?");
				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.setInt(3, intSeqNo);
				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 {
			//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 ret;
	}
	public int delBySessionId(String sessionId) throws Exception {
		//Add by Gxk 2004/09/10 Start
		sessionId = BaseCommonCheck.convertSql(sessionId);
		//Add by Gxk 2004/09/10 End
		StringBuffer sql = new StringBuffer();
		int ret = 0;
		sql.append(" DELETE");
		sql.append(" FROM");
		sql.append(" T_TEMP");
		sql.append(" WHERE");
		sql.append(" SESSIONID=");
		sql.append("'" + sessionId + "'");
		Connection conn = null;
		ResultSet rset = null;
		Statement st = null;
		PreparedStatement pstmt = null;
		BLOB blob = null;
		try {
			conn = this.datasource.getConnection();
			pstmt = conn.prepareStatement(sql.toString());
			ret = pstmt.executeUpdate();
			conn.commit();
		} 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) {
				System.out.println("[Error Happen!]");
				System.out.println("[Start Trace]");
				se.printStackTrace();
				System.out.println("[End Trace]");
				throw se;
			}
		}
		return ret;
	}

	/**
	 * method deleteAttachmentFile  
	 * @param int intSeqNo
	 * @param Connection conn 
	 * @return ReturnValue
	 * @throws Exception
	 */

	public ReturnValue deleteAttachmentFile(int intSeqNo, Connection conn) throws Exception {

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

		sql.append(" SELECT");
		sql.append("  ATTACHMENT_FILE, ATTACHMENT_FILE_NAME");
		sql.append(" FROM ");
		sql.append("   T_TEMP ");
		sql.append(" WHERE ");
		sql.append("   SEQ_NO =");
		sql.append(intSeqNo);
		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 T_TEMP SET ATTACHMENT_FILE = EMPTY_BLOB() ,");
				sql.append("  ATTACHMENT_FILE_NAME = ''");
				sql.append(" WHERE ");
				sql.append("   SEQ_NO =");
				sql.append(intSeqNo);
				System.out.println("[INFO] SQL = " + sql.toString());
				pstmt = conn.prepareStatement(sql.toString());
				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;
	}
}