JAVA存取PG大对象类型OID数据
转载地址：http://my.oschina.net/liuyuanyuangogo/blog/151537
pg用大对象存储二进制数据的老文档：http://jdbc.postgresql.org/documentation/80/binary-data.html
//VM配置:256M-512M
//通过lo_import（‘文件路径’）函数向oid字段插入二进制文件，通过（不会内存溢出）。
/***
* @author Liu Yuanyuan
*/
private void insertOid()
{
String driver = "org.postgresql.Driver";//"com.highgo.jdbc.Driver";//192.168.100.125
String url = "jdbc:postgresql://" + "127.0.0.1" + ":" + "5866" + "/" + "db1";
Connection conn = null;
Statement stmt = null;
try
{
Class.forName(driver);
System.out.println("success find class");
conn = DriverManager.getConnection(url, "highgo", "hg");
System.out.println("success connect");
stmt = conn.createStatement();
//driectly insert
String f = "d:/1.jpg";
stmt = conn.prepareStatement("INSERT INTO oidtable VALUES (11, lo_import(\'"+f+"\'))");
//or by update
//String f = "d://2.jpg";
//PreparedStatement ps = conn.prepareStatement("update oidtable set obj = lo_import(\'"+f+"\') where id=?");
//ps.setInt(1,11);
ps.executeUpdate();
}
catch(Exception ex)
{
ex.printStackTrace(System.out);
}
finally
{
try
{
if(stmt!=null)
stmt.close();
if(conn!=null)
conn.close();
}
catch(Exception ex)
{
ex.printStackTrace(System.out);
}
finally
{
System.out.println("finally");
}
}
}
//VM配置:256M-512M
//直接通过setLong()向oid插入1GB的文件，通过（2分钟之内插入完毕）;
public void insertOid(){
Connection conn = null;
PreparedStatement ps = null;
try
{
String driver = "org.postgresql.Driver";
String url = "jdbc:postgresql://" + "127.0.0.1" + ":" + "5432" + "/" + "db1";
Class.forName(driver);
System.out.println("class");
conn = DriverManager.getConnection(url, "postgres", "pg");
System.out.println("connect");
// All LargeObject API calls must be within a transaction block
conn.setAutoCommit(false);
// Get the Large Object Manager to perform operations with
LargeObjectManager lobj = ((org.postgresql.PGConnection) conn).getLargeObjectAPI();
// Create a new large object
long oid = lobj.createLO(LargeObjectManager.READ | LargeObjectManager.WRITE);
// Open the large object for writing
LargeObject obj = lobj.open(oid, LargeObjectManager.WRITE);
//Now open the file
File file = new File("d://1.jpg");
FileInputStream fis = new FileInputStream(file);
// Copy the data from the file to the large object
byte buf[] = new byte[2048];
int s, tl = 0;
while ((s = fis.read(buf, 0, 2048)) > 0)
{
obj.write(buf, 0, s);
tl += s;
}
// Close the large object
obj.close();
// Now insert the row into imageslo
ps = conn.prepareStatement("INSERT INTO lob.oidtable VALUES (?, ?)");
ps.setInt(1, 1);
ps.setLong(2, oid);
ps.executeUpdate();
fis.close();
// Finally, commit the transaction.
conn.commit();
conn.setAutoCommit(true);
}
catch (Exception ex)
{
ex.printStackTrace(System.out);
}
finally
{
try
{
if (ps != null)
{
ps.close();
}
if(conn != null)
{
conn.close();
}
System.out.println("close all");
}
catch (SQLException ex)
{
ex.printStackTrace(System.out);
}
}
}
//VM配置:256M-512M
//直接通过getLong()从oid取出1GB的文件，通过（2分钟之内取出完毕）；
public void getBinaryFile(){
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try
{
String driver = "org.postgresql.Driver";
String url = "jdbc:postgresql://" + "127.0.0.1" + ":" + "5866" + "/" + "db1";
Class.forName(driver);
System.out.println("class");
conn = DriverManager.getConnection(url, "highgo", "hg");
System.out.println("connect");
// All LargeObject API calls must be within a transaction block
conn.setAutoCommit(false);
// Get the Large Object Manager to perform operations with
LargeObjectManager lobj = ((org.postgresql.PGConnection) conn).getLargeObjectAPI();
ps = conn.prepareStatement("SELECT obj FROM lob.oidtable WHERE id = ?");
ps.setInt(1, 1);
rs = ps.executeQuery();
while (rs.next())
{
// Open the large object for reading
long oid = rs.getLong(1);
LargeObject obj = lobj.open(oid, LargeObjectManager.READ);
// Read the data
// obj.read(buf, 0, obj.size());//its read method
// Do something with the data read here
//for example:load the file to disk
OutputStream ops = new FileOutputStream(new File("d:\\111.jpg"));
byte buf[] = new byte[1024];//当文件很大时，用obj.size()将内存溢出，所以可以自定义一个合适的值
for (int i; (i = obj.read(buf, 0,1024)) > 0;)
{
ops.write(buf, 0, i);
ops.flush();
}
// Close the object
obj.close();
ops.close();
}
// Finally, commit the transaction
conn.commit();
}
catch (Exception ex)
{
ex.printStackTrace(System.out);
}
finally
{
try
{
if (rs != null)
{
rs.close();
}
if (ps != null)
{
ps.close();
}
if(conn != null)
{
conn.close();
}
System.out.println("close all");
}
catch (SQLException ex)
{
ex.printStackTrace(System.out);
}
}
}
以上是 JAVA存取PG大对象类型OID数据 的全部内容， 来源链接： utcz.com/z/390149.html
