JDBC连接资源和流的释放示例

ゝ一世哀愁。 2022-01-12 16:57 223阅读 0赞

public void uploadProductAttachments(ProductAttachment pa, File infile) throws Exception{
    		Connection con = null;
    		Statement stmt = null;
    		ResultSet rs = null;
    		
    		Timestamp ts = new Timestamp(System.currentTimeMillis());
    		String tsStr = ts.toString();
    		tsStr = tsStr.substring(0, tsStr.indexOf("."));
    		
    		StringBuilder insertSql = new StringBuilder();
    		insertSql.append(" insert into SPR_APP_PROD_ATTACHMENT (PROD_ID, FILE_NAME, FILE_CONTENT, CREATED_BY, CREATED_DT, DESCRIPTION)");
    		insertSql.append(" values('" + pa.getProductId() + "','" + pa.getFileName() + "',empty_blob(),");
    		insertSql.append("'" + pa.getCreatedBy() + "',to_date('" + tsStr + "','yyyy-mm-dd hh24:mi:ss'),'" + pa.getDescription() + "')");
    
    		StringBuilder selectSql = new StringBuilder();
    		selectSql.append(" select FILE_CONTENT from SPR_APP_PROD_ATTACHMENT");
    		selectSql.append(" where PROD_ID='" + pa.getProductId() + "' and FILE_NAME='" + pa.getFileName() + "' ");
    		selectSql.append(" and CREATED_DT=to_date('" + tsStr + "','yyyy-mm-dd hh24:mi:ss') FOR UPDATE");
    
    		try
    		{
    			con = ServiceLocator.getInstance().getConnection(JNDINames.ORACLE_DATASOURCE);
    			con.setAutoCommit(false);
    			
    			stmt = con.createStatement();
    			stmt.executeUpdate(insertSql.toString());
    			rs = stmt.executeQuery(selectSql.toString());
    			
    			oracle.sql.BLOB blob = null;
    			BufferedOutputStream out = null;
    			BufferedInputStream in = null;
    			int c = -1;
    			
    			while (rs.next())
    			{
    				try
    				{
    					blob = (oracle.sql.BLOB) rs.getBlob("FILE_CONTENT");
    					out = new BufferedOutputStream(blob.getBinaryOutputStream());
    					in = new BufferedInputStream(new FileInputStream(infile));
    					
    					while ((c = in.read()) != -1)
    					{
    						out.write(c);
    					}
    				}
    				catch (Exception e)
    				{
    					e.printStackTrace();
    				}
    				finally
    				{
    					if (in != null)
    					{
    						try
    						{
    							in.close();
    						}
    						catch (IOException e)
    						{
    							e.printStackTrace();
    						}
    					}
    					if (out != null)
    					{
    						try
    						{
    							out.close();
    						}
    						catch (IOException e)
    						{
    							e.printStackTrace();
    						}
    					}
    				}
    			}
    			
    			con.commit();
    		}
    		catch (Exception e)
    		{
    			e.printStackTrace();
    			if (con != null)
    			{
    				try
    				{
    					con.rollback();
    				}
    				catch (SQLException e1)
    				{
    					e1.printStackTrace();
    				}
    			}
    		}
    		finally
    		{
    			if (rs != null)
    			{
    				try
    				{
    					rs.close();
    				}
    				catch (SQLException e)
    				{
    					e.printStackTrace();
    				}
    			}
    			if (stmt != null)
    			{
    				try
    				{
    					stmt.close();
    				}
    				catch (SQLException e)
    				{
    					e.printStackTrace();
    				}
    			}
    			if (con != null)
    			{
    				try
    				{
    					con.close();
    				}
    				catch (SQLException e)
    				{
    					e.printStackTrace();
    				}
    			}
    		}
    	}
    	
    	public void downloadProductAttachments(String productId, String fileName, String createDt, String filePath) throws Exception{
    		Connection con = null;
    		Statement stmt = null;
    		ResultSet rs = null;
    		String tsStr = createDt.substring(0, createDt.indexOf("."));
    		
    		
    		StringBuilder selectSql = new StringBuilder();
    		selectSql.append(" select FILE_CONTENT from SPR_APP_PROD_ATTACHMENT");
    		selectSql.append(" where PROD_ID='"+productId+"' and FILE_NAME='"+fileName+"' ");
    		selectSql.append(" and CREATED_DT=to_date('"+tsStr+"','yyyy-mm-dd hh24:mi:ss')");
    		
    		try
    		{
    			con = ServiceLocator.getInstance().getConnection(JNDINames.ORACLE_DATASOURCE);
    			stmt = con.createStatement();
    			rs = stmt.executeQuery(selectSql.toString());
    						
    			oracle.sql.BLOB blob = null;
    			InputStream in = null;
    			FileOutputStream fos = null;
    			
    			while (rs.next())
    			{
    				try
    				{
    					blob = (oracle.sql.BLOB) rs.getBlob("FILE_CONTENT");
    					in = blob.getBinaryStream();
    					fos = new FileOutputStream(new File(ConfigParam.getString("UploadFolder"),fileName));
    					
    					int len = (int) blob.length();
    					byte[] buffer = new byte[len];
    					while ((len = in.read(buffer)) != -1)
    					{
    						fos.write(buffer, 0, len);
    					}
    				}
    				catch (Exception e)
    				{
    					e.printStackTrace();
    				}
    				finally
    				{
    					if (in != null)
    					{
    						try
    						{
    							in.close();
    						}
    						catch (IOException e)
    						{
    							e.printStackTrace();
    						}
    					}
    					if (fos != null)
    					{
    						try
    						{
    							fos.close();
    						}
    						catch (IOException e)
    						{
    							e.printStackTrace();
    						}
    					}
    				}
    			}
    		}
    		catch (Exception e)
    		{
    			e.printStackTrace();
    			if (con != null)
    			{
    				try
    				{
    					con.rollback();
    				}
    				catch (SQLException e1)
    				{
    					e1.printStackTrace();
    				}
    			}
    		}
    		finally
    		{
    			if (rs != null)
    			{
    				try
    				{
    					rs.close();
    				}
    				catch (SQLException e)
    				{
    					e.printStackTrace();
    				}
    			}
    			if (stmt != null)
    			{
    				try
    				{
    					stmt.close();
    				}
    				catch (SQLException e)
    				{
    					e.printStackTrace();
    				}
    			}
    			if (con != null)
    			{
    				try
    				{
    					con.close();
    				}
    				catch (SQLException e)
    				{
    					e.printStackTrace();
    				}
    			}
    		}
    	}
    	
    	public void deleteProductAttachments(String productId, String[] times, String[] fileNames){
    		Connection con = null;
    		Statement stmt = null;
    		
    		try
    		{
    			con = ServiceLocator.getInstance().getConnection(JNDINames.ORACLE_DATASOURCE);
    			con.setAutoCommit(false);
    			stmt = con.createStatement();
    
    			for (int i = 0; i < times.length; i++)
    			{
    				String time = times[i];
    				time = time.substring(0, time.indexOf("."));
    				String fileName = fileNames[i];
    				StringBuilder deleteSql = new StringBuilder();
    				deleteSql.append(" delete from SPR_APP_PROD_ATTACHMENT");
    				deleteSql.append(" where PROD_ID='" + productId + "' and FILE_NAME='" + fileName + "' and CREATED_DT=to_date('" + time + "','yyyy-mm-dd hh24:mi:ss')");
    				stmt.executeUpdate(deleteSql.toString());
    			}
    			
    			con.commit();
    		}
    		catch (Exception e)
    		{
    			e.printStackTrace();
    			if (con != null)
    			{
    				try
    				{
    					con.rollback();
    				}
    				catch (SQLException e1)
    				{
    					e1.printStackTrace();
    				}
    			}
    		}
    		finally
    		{
    			if (stmt != null)
    			{
    				try
    				{
    					stmt.close();
    				}
    				catch (SQLException e)
    				{
    					e.printStackTrace();
    				}
    			}
    			if (con != null)
    			{
    				try
    				{
    					con.close();
    				}
    				catch (SQLException e)
    				{
    					e.printStackTrace();
    				}
    			}
    		}
    	}