pg>bytea存储文件
2021-09-24 11:27:13    30    0    0
ivan

    1、创建表

        create TABLE t_bytea( f_name varchar(100), f_content bytea )

    2、Java示例

public class TestBytea {

       public static void main(String[] args) {

        //insert_BinaryStream();

        //insert_Bytes();

        select();

    }

    private static void select() {

        Connection conn = null;

        PreparedStatement ps = null;

        ResultSet rs = null;

        try {

            Class.forName("org.postgresql.Driver");

            String url = "jdbc:postgresql://192.168.17.18:5432/postgres";

            conn = DriverManager.getConnection(url, "postgres", "Admin@123");

            ps = conn.prepareStatement(" select f_name, f_content from t_bytea ");

            rs = ps.executeQuery();

            while(rs.next()){

                byte[] be = rs.getBytes(2);

                File file = new File("E:\\temp\\1-"+rs.getString(1));

                FileOutputStream fo = new FileOutputStream(file);

                fo.write(be);

            }

        }catch (Exception ex){

            ex.printStackTrace();

        }finally {

            try {

                if(rs != null) { rs.close(); }

                if(ps != null) { ps.close(); }

                if(conn != null) { conn.close(); }    

            } catch (SQLException e) { e.printStackTrace(); }

        }

    }

 

    private static void insert_Bytes(){

        Connection conn = null;

        PreparedStatement ps = null;

        FileInputStream fi = null;

        try {

            Class.forName("org.postgresql.Driver");

            String url = "jdbc:postgresql://192.168.17.18:5432/postgres";

            conn = DriverManager.getConnection(url, "postgres", "Admin@123");

            ps = conn.prepareStatement(" insert into t_bytea values ( ?, ?) ");

            File file = new File("E:\\temp\\51aad.jpeg");

            ps.setString(1, file.getName());

            byte[] by = new byte[(int)file.length()];

            fi = new FileInputStream(file);

            fi.read(by);

            ps.setBytes(2, by);

            ps.executeUpdate();

        } catch (Exception e) {

            e.printStackTrace();

        }finally {

            try {

                if(fi != null) { fi.close(); }

                if(ps != null) { ps.close(); }

                if(conn != null) { conn.close(); }

            } catch (Exception e) { e.printStackTrace(); }

        }

    }

 

    private static void insert_BinaryStream(){

        Connection conn = null;

        PreparedStatement ps = null;

        FileInputStream fi = null;

        try {

            Class.forName("org.postgresql.Driver");

            String url = "jdbc:postgresql://192.168.17.18:5432/postgres";

            conn = DriverManager.getConnection(url, "postgres", "Admin@123");

            ps = conn.prepareStatement(" insert into t_bytea values ( ?, ?) ");

            File file = new File("E:\\temp\\c0bb.jpeg");

            ps.setString(1, file.getName());

            fi = new FileInputStream(file);

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

            ps.executeUpdate();

        } catch (Exception e) {

            e.printStackTrace();

        }finally {

            try {

                if(fi != null) { fi.close(); }

                if(ps != null) { ps.close(); }

                if(conn != null) { conn.close(); }

            } catch (Exception e) { e.printStackTrace(); }

       }

    }

}

Pre: pg>mysql>on update字段

Next: pg>oracle>regexp_like

30
Sign in to leave a comment.
No Leanote account? Sign up now.
0 comments
Table of content