Retrieving file from bytea in PostgreSQL using java

Question

Hi I'm using the below code to retrieve the file from the postgresql bytea using java, but inside the file I'm getting numbers like 314530413142313141

File file = new File("c:/test.doc");
FileOutputStream fos = new FileOutputStream(file);
ResultSet rs = st.executeQuery("SELECT * FROM test_bytea where id=" + 1);
        if (rs != null) {
            while (rs.next()) {

                byte[] fileBytes = new byte[1024];
                InputStream is = rs.getBinaryStream("type_file");
                while (is.read(fileBytes) > 0) {
                    fos.write(fileBytes);
                }

                // use the stream in some way here
            }
            rs.close();
        }    

Please let me know what goes wrong in my code?

Answer 1

The data is escaped ( starts with \x and then is hexadecimal two chars for each byte) this is what comes out of a bytea field. you need to unescape it before storing it in the file.

Answer 2

Instead of doing it manually, you can use Spring:

ResultSet rs = st.executeQuery("SELECT * FROM test_bytea where id=" + 1);
if (rs != null) {
    LobHandler lobHandler = new DefaultLobHandler();
    byte[] myFile = lobHandler.getBlobAsBytes(rs, "type_file"));
    //....