postgresql数据库插入和读取图片

postgresql插入和读取图片

postgresql存储图片需要二进制类型bytea，创建一张测试表：

postgres=# create table test_image(img bytea);

CREATE TABLE

使用jdbc插入1.jpg

try {

    connection = JDBCUtil.getConnection();

    connection.setAutoCommit(false);

    String sql = "INSERT INTO test_image(img) VALUES(?)";

    preparedStatement = connection.prepareStatement(sql);

    InputStream inputStream = new FileInputStream(img_name);

    preparedStatement.setBinaryStream(1,inputStream);

    preparedStatement.executeUpdate();

    connection.commit();

} catch (SQLException | FileNotFoundException e) {

    throw new RuntimeException(e);

} finally {

    JDBCUtil.release(connection);

}

查看字段长度

postgres=# select length(img) from test_image;

 length

--------

  50726

(1 row)

读取图片

try {

    connection = JDBCUtil.getConnection();

    String sql = "SELECT img from test_image";

    preparedStatement = connection.prepareStatement(sql);

    resultSet = preparedStatement.executeQuery();

    while (resultSet.next()) {

        InputStream in = resultSet.getBinaryStream(1);

        byte[] buffer = new byte[1024*1024];

        OutputStream out = new FileOutputStream("new_1.jpg");

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

        {

            out.write(buffer, 0, i);

            out.flush();

        }

        out.close();

        in.close();

    }

} catch (Exception e) {

    e.printStackTrace();

} finally {

    JDBCUtil.release(connection);

}

巴特西

postgresql数据库插入和读取图片

postgresql插入和读取图片

最新文章

热门文章