java 存贮二进制数据到sqlserver数据库

oracle blob ，sqlserver （image，varbinary），mysql（blob）等都是用来存数二进制对象的。一些文件excel ，图像，pdf等等，此处演示，cexcel操作过程。

存入数据：spring中jdbc和原生jdbc

spring中jdbc

String sql = "update Hr2oaEntry set ff =? where id=1 "; 
		jpm.getJdbcTemplate().update(sql,new PreparedStatementSetter() {
			
			public void setValues(PreparedStatement ps) throws SQLException {
				 try {
					ps.setBinaryStream(1, fis, fis.available());
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		});

 原生jdbc

psm=conn.prepareStatement("update Hr2oaEntry set ff =? where id=1 ");
        FileInputStream fis;
        File file = new File("c:/students.xls");
		fis = new FileInputStream(file);
		final FileInputStream f=fis;
        psm.setBinaryStream(1, f, f.available());
        psm.executeUpdate();

读取数据：

String sql = "select * from  Hr2oaEntry  where id=1 "; 
			
			List<File> list=jpm.getJdbcTemplate().query(sql,new RowMapper() {
				
				public File mapRow(ResultSet rs, int arg1) throws SQLException {
					InputStream in=rs.getBinaryStream("ff");
					long temp=System.currentTimeMillis();
					File f=new File("c:/"+temp+".xls");
					OutputStream os=null;
					try{
						os=new FileOutputStream(f);
						byte buffer[]=new byte[1024];
						while((in.read(buffer))!=-1){
							os.write(buffer);
						}
						os.flush();
					}
					catch(Exception e){
						e.printStackTrace();
					}finally{
						try{
							os.close();
							in.close();
						}
						catch(Exception e){
							e.printStackTrace();
						}
					}
					return f;
				}
			});