Question

将图片插入SQL SERVER2005数据库表中

Answer 1

如何将图片,Mp3 ,或是一些二进制类型的数据插入到sqlserver,或是 Oracle 数据库 . 方法是通过流进行操作.

创建一张测试表(sqlserver2000)

create table [pictable] (
[id] [int] identity (1, 1) not null ,
[img] [image] not null
) on [primary] textimage_on [primary]
go

1,插入数据库的方法(sqlserver2000)

this.getConnection() 为获得连接的方法.

public void insertPic(String path)...{
Connection con = this.getConnection();
String sql = "insert into picTable values(?)" ;
try ...{
PreparedStatement pstm = con.prepareStatement(sql);
InputStream is = new FileInputStream(path);

pstm.setBinaryStream(1, is, is.available());
int count = pstm.executeUpdate();
if(count>0)...{
System.out.println("插入成功");
}else...{
System.out.println("插入失败");
}
is.close();
pstm.close();
con.close();

} catch (Exception e) ...{
e.printStackTrace();
}
}
2,从数据库中读出来的方法.(sqlserver2000)

public void readPic(int id)...{
Connection con = this.getConnection();
String sql = "select * from picTable where id=?" ;
try ...{
PreparedStatement pstm = con.prepareStatement(sql);
pstm.setInt(1, id);
ResultSet rs = pstm.executeQuery();
rs.next();
InputStream is = rs.getBinaryStream(2);

OutputStream os = new FileOutputStream("f:/temp.jpg");
byte[] buff = new byte[1024];
int len = is.read(buff);
while( len !=-1 )...{
os.write(buff);
len = is.read(buff);
}
System.out.println("写入成功");
is.close();
os.close();
pstm.close();
con.close();
} catch (Exception e) ...{
e.printStackTrace();
}
}
3,插入数据库的方法(Oracle)

public void insertBinary() ...{
Connection con = MyConnection.getORACLEConnection();
String sql = "insert into testBinary values(?,?)";
try ...{
con.setAutoCommit(false);
PreparedStatement pstm = con.prepareStatement(sql);
pstm.setString(1, "a1");
pstm.setBlob(2, oracle.sql.BLOB.empty_lob());
int count = pstm.executeUpdate();
pstm.close();
pstm = con.prepareStatement("select * from testBinary where id=?");
pstm.setString(1, "a1");
ResultSet rs = pstm.executeQuery();
rs.next();
oracle.sql.BLOB blob = (BLOB) rs.getBlob(2);
OutputStream os = blob.getBinaryOutputStream();
FileInputStream fi = new FileInputStream("E:\test.mp3");
byte[] buff = new byte[1024];

int len = fi.read(buff);

while (len != -1) ...{
os.write(buff);
len = fi.read(buff);
}
pstm = con.prepareStatement(sql);
pstm.setString(1, "a1");
pstm.setBlob(2, blob);
int res = pstm.executeUpdate();
con.commit();
pstm.close();
con.close();

if (res > 0) ...{
System.out.println("success");
}
} catch (Exception ex) ...{
ex.printStackTrace();
}

}
4,从数据库中读出来的方法.(Oracle)

public void readerBinaryStream() ...{
Connection con = MyConnection.getORACLEConnection();
try ...{
java.sql.PreparedStatement pstm = con.prepareStatement(
"select * from testBinary where id='a1'");
ResultSet rs = pstm.executeQuery();
rs.next();
oracle.sql.BLOB blob = (BLOB) rs.getBlob(2);
InputStream is = blob.getBinaryStream();
FileOutputStream fi = new FileOutputStream("f:\aaaa.mp3");
byte[] buff = new byte[1024];
int len = is.read(buff);
while (len != -1) ...{
fi.write(buff);
len = is.read(buff);

}
fi.close();

} catch (SQLException ex) ...{
} catch (FileNotFoundException ex) ...{
ex.printStackTrace();
} catch (IOException ex) ...{
ex.printStackTrace();
}

}

Answer 2

那位高人太高了。