gbs_tpresentation_db.java

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

		//bresult= st.execute(Sql);			

		bresult = pstmt.executeUpdate();
		//if succeed in insert and  AttachmentFile is not null
		//then update the AttachmentFile
		if (bresult > 0 && AttachmentFile != null && !AttachmentFile.getFileName().equals("")) {
			ReturnValue returnValue =
				updateAttachmentFile(strCustomerId, strRfpNo, String.valueOf(lineNo), AttachmentFile, conn);
			if (returnValue.isError()) {
				if (returnValue.isBussinessError()) {
					//SET ERROR MESSAGELIST	
					messageList.addAll(returnValue.getMessageList());
				}
				//SET RETURNVALUE	
				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(lineNo));
		//release db
		try {
			if (bresult > 0 && !returnValueD.isError()) {
				//if no error then commit 
				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 updatePresentation  
 * @param String strCustomerId
 * @param String strRfpNo
 * @param String strLineNo
 * @param String strTitle
 * @param String strPresentationDate
 * @param String strPresentationBy
 * @param String strCreateUser
 * @param FormFile AttachmentFile
 * @param boolean FileDelete
 * @return ReturnValue
 * @throws Exception
 */	

public ReturnValue updatePresentation(	String strCustomerId,
										String strRfpNo,
										String strLineNo,
										String strTitle,
										String strPresentationDate,
										String strPresentationBy,								
										String strListener,
										String strUpdateUser,
										FormFile AttachmentFile,
										boolean FileDelete) throws Exception {
	  //Add by YM 2004/09/10 Start
	  strCustomerId = BaseCommonCheck.convertSql(strCustomerId);
	  strRfpNo = BaseCommonCheck.convertSql(strRfpNo);
	  strLineNo = BaseCommonCheck.convertSql(strLineNo);
	  strListener = BaseCommonCheck.convertSql(strListener);
	  strTitle = BaseCommonCheck.convertSql(strTitle);
	  strPresentationDate = BaseCommonCheck.convertSql(strPresentationDate);
	  strPresentationBy = BaseCommonCheck.convertSql(strPresentationBy);
	  strUpdateUser = BaseCommonCheck.convertSql(strUpdateUser);
	  //Add by YM 2004/09/10 End	  
	  StringBuffer sql = new StringBuffer();
	  ReturnValue returnValueD = new ReturnValue();
	  MessageList messageList = new MessageList();
	  returnValueD.setMessageList(messageList);	
	  ResultSet rset = null;
	  Connection conn = null;
	  Statement st = null;		    
	  int bresult = 0;	
/////////////////////////////////////////////////////////////////
//EDIT SQL START
	   sql.append(" UPDATE ");
	   sql.append(" T_PRESENTATION ");
	   sql.append(" SET ");
	   sql.append("  TITLE = '");//
	   sql.append(strTitle + "', ");	
	   sql.append("  LISTENER = '");//
	   sql.append(strListener+"',");
	   sql.append("  PRESENTATION_DATE = to_date('");//	
	  //sql += " DUE_DATE = to_date('" + dueDate + "','yyyy/MM/dd'),";
	   sql.append(strPresentationDate+"','yyyy/MM/dd'),");	  
	   sql.append("  PRESENTATION_BY = '");//
	   sql.append(strPresentationBy + "',");
	   sql.append("  UPDATE_DATE = SYSDATE,");//
	   if ( FileDelete ) {
		   sql.append("   ATTACHMENT_FILE_NAME = '', ");
		   sql.append("   ATTACHMENT_FILE = null, ");
	   }  		  
		  
	   sql.append("  UPDATE_USER = '");//
	   sql.append(strUpdateUser+"'");		  
	   sql.append(" WHERE  CUSTOMER_ID = '");
	   sql.append(strCustomerId+"'");
	   sql.append(" AND  RFP_NO = '");
	   sql.append(strRfpNo+"'");
	   sql.append(" AND  LINE_NO = '");
	   sql.append(strLineNo+"'");
	   
//EDIT SQL END
////////////////////////////////////////////////////////////////
 	   System.out.println( "[INFO] sql = " + sql.toString() );	  
	  
	   
	try{		  
		  conn = this.datasource.getConnection();		  
		  PreparedStatement pstmt = conn.prepareStatement(sql.toString());	
		  //update db except AttachmentFile and FileName  
		  bresult= pstmt.executeUpdate();
		  //if succeed in update and AttachmentFile is not null
		  //then update the AttachmentFile
		if (!FileDelete &&  bresult > 0 && AttachmentFile != null && !AttachmentFile.getFileName().equals( "" )){
			ReturnValue returnValue = updateAttachmentFile(	strCustomerId, 
															strRfpNo,
															strLineNo,			
															AttachmentFile, 
															conn );
			if ( returnValue.isError() ) {
				if ( returnValue.isBussinessError()){
					//SET ERROR MESSAGELIST	
					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(strLineNo));	
			  //RELEASE DB
	  try {
		//COMMIT DATA
		if ( bresult>0 && !returnValueD.isError()){
			conn.commit();
		}else{
			conn.rollback();
		}
		//CLOSE DB CONN
		if (rset != null) {
		  rset.close();
		}
		if (st != null) {
		  st.close();
		}
		if (conn != null) {
		  conn.close();
		}
	  } catch (SQLException se) {
			return returnValueD;
	  }			
	}
	
	return returnValueD;
}

/**
 * method getAttachmentFile  
 * @param String strCustomerId
 * @param String strRfpNo
 * @param String strLineNo
 * @param HttpServletResponse response
 * @return ReturnValue
 * @throws Exception
 */	

public ReturnValue getAttachmentFile( String strCustomerId,
									  String strRfpNo,
									  String strLineNo,
									  HttpServletResponse response  )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();
			GBS_File_stBean  GbsFilestBean = new GBS_File_stBean();
			ReturnValue returnValueD = new ReturnValue();
			//EDIT SQL			
			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 );
			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;
	}
/**
 * method updateAttachmentFile  
 * @param String strCustomerId
 * @param String strRfpNo
 * @param String strLineNo
 * @param FormFile attachmentFile
 * @param Connection conn 
 * @return ReturnValue
 * @throws Exception
 */	

public ReturnValue updateAttachmentFile(String strCustomerId,
										String strRfpNo,
										String strLineNo,
										FormFile attachmentFile ,
										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);
			int ret = 0;
			//EDIT SQL
			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());
			
			ResultSet rset = null;			
			Statement st = null;
			PreparedStatement pstmt = null;