java postgresql 数据类型_JAVA存取PostgreSQL二进制类型bytea

经过测试：JAVA存取PostgreSQL的bytea类型均存在内存的限制问题(存取的数据过大会出现out of memory内存溢出的问题)，在EnterpriseDB对此做了优化。

取PostgreSQL中的bytea，并存储到硬盘上.

/**

* @author Liu Yuanyuan

*/

private void getBytea

{

Connection conn = null;

Statement stmt = 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("find class");

conn = DriverManager.getConnection(url, "lyy", "lyy");

System.out.println("connected");

stmt = conn.createStatement();

String sql = "select obj from lyy.rawtable2 where id = 1";

rs = stmt.executeQuery(sql);

System.out.println("sql=" + sql);

while (rs.next())

{

System.out.println(rs.getMetaData().getColumnTypeName(1));

OutputStream ops = null;

InputStream ips = null;

File file = new File("e:" + File.separator + “binary”);

try

{

ips = rs.getBinaryStream(1);

byte[] buffer = new byte[ips.available()];//or other value like 1024

ops = new FileOutputStream(file);

for (int i; (i = ips.read(buffer)) > 0;)

{

ops.write(buffer, 0, i);

ops.flush();

}

}

catch (Exception ex)

{

ex.printStackTrace(System.out);

}

finally

{

ips.close();

ops.close();

}

}

}

catch (Exception ex)

{

ex.printStackTrace(System.out);

}

finally

{

try

{

If(rs!=null)

rs.close();

if(stmt!=null)

stmt.close();

if(conn!=null)

conn.close();

}

catch (SQLException ex)

{

Logger.getLogger(GetBlob.class.getName()).log(Level.SEVERE, null, ex);

}

}

}

向bytea直接插入二进制文件

Private void  insertByteaByBase64()

{

String driver = "org.postgresql.Driver";

String url = "jdbc:postgresql://" + "127.0.0.1" + ":" + "5432" + "/" + "postgres";

Connection conn = null;

PreparedStatement ps = null;

ResultSet st = null;

try

{

Class.forName(driver);

System.out.println("success find class");

conn = DriverManager.getConnection(url, "postgres", "pg");

System.out.println("success connect");

String sql = "insert into blobtable(id,obj) values(?,?)";

ps = conn.prepareStatement(sql);

String fpath = “d:”+File. Separator + “image.jpg”;

File file = new File(fpath);

InputStream ips = new FileInputStream(file);

ps.setInt(1, 400);

ps.setBinaryStream(2, ips, (int) file.length());

ps.executeUpdate();

System.out.println("insert");

}

catch (Exception ex)

{

ex.printStackTrace(System.out);

}

finally

{

try

{

if(ps!=null)

ps.close();

if(conn!= null)

conn.close();

}

catch (SQLException ex)

{

ex.printStackTrace(System.out);

}

}

}

通过base64向bytea插入二进制文件

Private void  insertByteaByBase64()

{

String driver = "org.postgresql.Driver";

String url = "jdbc:postgresql://" + "127.0.0.1" + ":" + "5432" + "/" + "postgres";

Connection conn = null;

PreparedStatement ps = null;

ResultSet st = null;

try

{

Class.forName(driver);

System.out.println("success find class");

conn = DriverManager.getConnection(url, "postgres", "pg");

System.out.println("success connect");

byte[] b = ByteaClass.getBytes();//得到数组byte[]

System.out.println("Length = " + b.length);

String s = Base64.encodeBytes(b, 0, b.length);

System.out.println("s = " + s.length());

String sql = "insert into blobtable(id,obj) values(?,?)";

String c = "decode(\'" + s + "\',\'base64\')";

sql = sql.replace("?,?", "?," + c);

System.out.println("sql = " + sql);

ps = conn.prepareStatement(sql);

ps.setInt(1, 400);

ps.executeUpdate();

System.out.println("insert");

}

catch (Exception ex)

{

ex.printStackTrace(System.out);

}

finally

{

try

{

if(ps!=null)

ps.close();

if(conn!= null)

conn.close();

}

catch (SQLException ex)

{

ex.printStackTrace(System.out);

}

}

}