BLOB和CLOB字段的存取

2019独角兽企业重金招聘Python工程师标准>>>

对blob和clob插入

tblappresourcemanager 是我的一个实体类， 你可以自己建一个实体

public String insert(Tblappresourcemanager tblappresourcemanager,HttpServletRequest request) {
		 Connection conn = SqlUtil.getJdbcConnection();
		 ResultSet rs=null;
		 PreparedStatement ps = null;  
		 String resourceid = tblappresourcemanager.getResourceid();
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("insert into TBLAPPRESOURCEMANAGER(APPID,APPKEY,APPTYPE,APPPATH,REMARK,RESOURCEID,BACK2,APPCONTEXT) values(?,?,?,?,?,?,?,empty_blob())");
			ps= conn.prepareStatement(sql.toString());
		//通过oralce.sql.BLOB/CLOB.empty_lob()构造空Blob/Clob对象 
			ps.setString(1,tblappresourcemanager.getAppid());
			ps.setString(2,tblappresourcemanager.getAppkey());
			ps.setString(3,tblappresourcemanager.getApptype());
			ps.setString(4,tblappresourcemanager.getApppath());
			ps.setString(5,tblappresourcemanager.getRemark());
			ps.setString(6,resourceid);
			ps.setString(7,tblappresourcemanager.getBack2());
			ps.executeUpdate();
			ps.close();

		//再次对读出Blob/Clob句柄 
		
			ps=(PreparedStatement) conn.prepareStatement("select APPCONTEXT from TBLAPPRESOURCEMANAGER where RESOURCEID=? for update");
			ps.setString(1,resourceid);
			rs=ps.executeQuery();
			oracle.sql.BLOB contextBlob = null;
			
			while(rs.next()){
				 contextBlob=(oracle.sql.BLOB)rs.getBlob("appcontext");
			}
			MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
			MultipartFile mFile =  multipartRequest.getFile("fileId"); 
			if(mFile==null){
				return "获取文件失败";
				
			}
			InputStream inStream = mFile.getInputStream();
			OutputStream outStream=contextBlob.getBinaryOutputStream();

			int count = -1;
			byte[] data = new byte[1024];  

		     
				while ((count = inStream.read(data)) != -1) {


				    outStream.write(data, 0, count);

				  }
		
				inStream.close();
				outStream.close();


			//再将Blob/Clob字段更新到数据库 
			ps=(PreparedStatement)conn.prepareStatement("update TBLAPPRESOURCEMANAGER set APPCONTEXT=?  where RESOURCEID=?");
			ps.setBlob(1, contextBlob);
			ps.setString(2,resourceid);

			ps.executeUpdate();
			ps.close(); 
			conn.commit();
			conn.close();
			//调用读取文件
			readfile(resourceid);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			e.getMessage();
			return "数据库连接异常";
		}catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return "文件流异常";
			
		}catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return "数据流异常";
		}
		

		 return null;
	}

	public String readfile(String resouceid){
		Connection conn = SqlUtil.getJdbcConnection();
		 ResultSet rs=null;
		 PreparedStatement ps = null;  
		 InputStream in=null;
		 oracle.sql.BLOB blob = null;
		 String str = "select APPCONTEXT from TBLAPPRESOURCEMANAGER where RESOURCEID=?";
		 try {
			ps = conn.prepareStatement(str);
			ps.setString(1, resouceid);
			rs= ps.executeQuery();
			while(rs.next()){
				blob = (oracle.sql.BLOB)rs.getBlob("appcontext");
			}
			in = blob.getBinaryStream();
					FileOutputStream outStream = new FileOutputStream("D:\\cesi4.xls");
	            byte[] buf = new byte[1024];
	            int bytesIn = 0;
				while ((bytesIn = in.read(buf, 0, 1024)) != -1) {
					outStream.write(buf, 0, bytesIn);
					}
	            in.close();
	            outStream.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
            try {
                rs.close();
                ps.close();
                conn.close();
            }
            catch(Exception e) {
                e.printStackTrace();
            }
        }
		 
		return null;
	}

 

转载于:https://my.oschina.net/u/2415525/blog/858409