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

最新推荐文章于 2024-03-25 22:11:11 发布
原创最新推荐文章于 2024-03-25 22:11:11 发布 · 2.4k 阅读
0 ·
CC 4.0 BY-SA版权
Java 专栏收录该内容
39 篇文章
订阅专栏
本文详细介绍了如何使用数据库操作实现产品附件的上传、下载和删除，包括创建插入语句、读取文件内容到数据库、下载数据库中的文件以及删除指定时间戳的附件。
摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >
	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();
				}
			}
		}
	}