JAVA存取PG大对象类型OID数据

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

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);
            }
        }  
}

转载于:https://my.oschina.net/liuyuanyuangogo/blog/151537