gbs_tpresentation_db.java

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

			BLOB blob = null ;
			try{		
				ReturnValue returnValue = deleteAttachmentFile( strCustomerId,
																strRfpNo,
																strLineNo, 
																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" );
					//OutputStream out = blob.getBinaryOutputStream();
					//out.write( attachmentFile.getFileData() );
					//out.close();
				}else{
					ret = -1;
				}
				if ( ret == 0 ){
					sql = new StringBuffer();
					sql.append( " UPDATE T_PRESENTATION SET ATTACHMENT_FILE = ? ," );
					sql.append( "  ATTACHMENT_FILE_NAME = ?");
					sql.append( " WHERE " );
					sql.append( "  T_PRESENTATION.CUSTOMER_ID = ? " );
					//sql.append( strCustomerId );
					sql.append( "  AND  T_PRESENTATION.RFP_NO = ? " );
					//sql.append( strRfpNo );
					sql.append( "  AND  T_PRESENTATION.LINE_NO = ? " );
					//sql.append( strLineNo );
					//PrepareStatement pstmt = null;
					System.out.println("[INFO] sql = " + sql.toString());
					
					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, strCustomerId );
					pstmt.setString(4, strRfpNo );
					pstmt.setString(5, strLineNo );					
					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 getCustomerName  
 * @param String strCustomerId
 * @return ReturnValue
 * @throws Exception
 */	
	
	
public ReturnValue getCustomerName(String strCustomerId) throws Exception {
				//Add by YM 2004/09/10 Start
				strCustomerId = BaseCommonCheck.convertSql(strCustomerId);
				//Add by YM 2004/09/10 End
				String retData = new String();
				StringBuffer sql = new StringBuffer();
				ReturnValue returnValueD = new ReturnValue();
				MessageList messageList = new MessageList();
				returnValueD.setMessageList(messageList);
////////////////////////////////////////////////////////////////////
//EDIT SQL START
			  sql.append(" SELECT ");
			  sql.append(" UNIQUE M_CUSTOMER.CUSTOMER_ID, ");
			  sql.append(" CUSTOMER_NAME ");
			  sql.append(" FROM  ");
			  sql.append(" T_PRESENTATION,M_CUSTOMER  ");
			  sql.append(" WHERE ");
			  sql.append(" M_CUSTOMER.CUSTOMER_ID ='");
			  sql.append(strCustomerId );
			  sql.append("' AND M_CUSTOMER.CUSTOMER_ID = T_PRESENTATION.CUSTOMER_ID(+)  ");
			  //Add by Gxk 2004/08/23 add deleteFlg
			  sql.append(" AND (M_CUSTOMER.DELETE_FLG<>'D' OR M_CUSTOMER.DELETE_FLG IS NULL)");
//EDIT SQL END
//////////////////////////////////////////////////////////////////
				System.out.println("[info] sql=" + sql.toString());
				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()){
						//GET DATA FROM DB RESULTSET
						retData = rset.getString ( "CUSTOMER_NAME" ) ;											
						
					}else{						
						//SET ERROR MESSAGELIST	
						messageList.setMessage("", "", "10000001", 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 {
				//SET RETURNVALUE
					returnValueD.setDataValue(retData);		
				//RELEASE DB
				try {
					  if (rset != null) {
						rset.close();
					  }
					  if (st != null) {
						st.close();
					  }
					  if (conn != null) {
						conn.close();
					  }
					 }catch (SQLException se) {
						}
			  }
			  return returnValueD;
			} 
/**
 * method deletePresentation  
 * @param String strCustomerId
 * @param String strRfpNo
 * @param String strLineNo
 * @return ReturnValue
 * @throws Exception
 */	
		
	public ReturnValue deletePresentation(String strCustomerId,
										String strRfpNo,
										String strLineNo) throws Exception {
		//Add by YM 2004/09/10 Start
		strCustomerId = BaseCommonCheck.convertSql(strCustomerId);
		strRfpNo = BaseCommonCheck.convertSql(strRfpNo);
		strLineNo = BaseCommonCheck.convertSql(strLineNo);
		//Add by YM 2004/09/10 End
		List retdata = new Vector();
		StringBuffer sql = new StringBuffer();
		ReturnValue returnValueD = new ReturnValue();
		sql.append(" DELETE FROM T_PRESENTATION ");	
		sql.append("WHERE ");
		sql.append(" CUSTOMER_ID ='");
		sql.append(strCustomerId+"'  AND ");
		sql.append(" RFP_NO ='");
		sql.append(strRfpNo+"' AND ");
		sql.append(" LINE_NO ='");
		sql.append(strLineNo+"' ");
		ResultSet rset = null;
		Connection conn = null;
		Statement st = null;
		PreparedStatement pstmt = null;
		int bresult = 0;
		String Sql = sql.toString();
		try{
			conn = this.datasource.getConnection();			
			System.out.println("[info]GBS_TPresentation_DB.deletePresentation.sql=" + Sql);
			pstmt = conn.prepareStatement(Sql);
			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 deleteAttachmentFile  
	 * @param String strCustomerId,String strRfpNo,String strLineNo
	 * @param Connection conn 
	 * @return ReturnValue
	 * @throws Exception
	 */		
	public ReturnValue deleteAttachmentFile( String strCustomerId,
											 String strRfpNo,
											 String strLineNo,										
											 Connection conn  )throws Exception{
			//Add by YM 2004/09/10 Start
			strCustomerId = BaseCommonCheck.convertSql(strCustomerId);
			strRfpNo = BaseCommonCheck.convertSql(strRfpNo);
			strLineNo = BaseCommonCheck.convertSql(strLineNo);
			//Add by YM 2004/09/10 End
			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_PRESENTATION " );
			sql.append( " WHERE " );
			sql.append( "  CUSTOMER_ID ='" );
			sql.append( strCustomerId );
			sql.append( "'  AND  RFP_NO ='" );
			sql.append( strRfpNo );
			sql.append( "'  AND  LINE_NO ='" );
			sql.append( strLineNo + "'" );
			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_PRESENTATION SET ATTACHMENT_FILE = EMPTY_BLOB() ," );
					sql.append( "  ATTACHMENT_FILE_NAME = ''");
					sql.append( " WHERE " );
					sql.append( "  CUSTOMER_ID ='" );
					sql.append( strCustomerId );
					sql.append( "'  AND  RFP_NO ='" );
					sql.append( strRfpNo );
					sql.append( "'  AND  LINE_NO ='" );
					sql.append( strLineNo  +"'" );
					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 ;
		}

}