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?

Are you absolutely sure the type_file is of type bytea? The behaviour you describe sounds like the column is defined as oid — user330315
– user330315, Commented Aug 30, 2012 at 12:15
yes 100% im sure the type_file is the cloumn name of the bytea datatype — Sathish
– Sathish, Commented Aug 30, 2012 at 12:17
Possible duplicate of How to download bytea column as file using Java — Gord Thompson
– Gord Thompson, Commented Oct 13, 2015 at 17:50

Mark Durbin · Accepted Answer · 2012-10-01 13:24:06Z

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.

answered Oct 1, 2012 at 13:24

Mark Durbin

261 bronze badge

Sign up to request clarification or add additional context in comments.

1 Comment

Sathish Over a year ago

do you have any idea, that how to implement this?

Andrea Ligios · Accepted Answer · 2013-04-08 15:53:40Z

1

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"));
    //....

answered Apr 8, 2013 at 15:53

Andrea Ligios

50.4k29 gold badges127 silver badges256 bronze badges

2 Comments

oligofren Over a year ago

Q: "How do I do this in Javascript?" A: "Not sure, but here is how you do it using jQuery". There's no spring tag in that question.

Andrea Ligios Over a year ago

@oligofren So what? It's another way, which works. Don't go answer-nazi for nothing, dude ;) Cheers

Collectives™ on Stack Overflow

Retrieving file from bytea in PostgreSQL using java

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related