How to dump byte array to INSERT INTO SQL script (Java + PostgreSQL)

Question

How can I populate Postgres SQL base INSERT INTO SQL script with following Java Entity class based data collection. Issue is how can I write content of the byte array as a INSERT INTO value ('content of the byte[]') on SQL script.

That mean it can not feed this data via java application, according to requirement it needs some row SQL script for populate existing data base on production environment. Thanks.

Entity class

@Entity 
@Table(name="image_table")
public class ImageData implements Serializable {

  @Id
  @GeneratedValue 
  @Column(name = "id")
  private Integer id;

  @Column(name = "content")
  private byte[] content;
}

Format of the row SQL script need to generate

 INSERT INTO image_table (id, content) VALUES ('1', '<content of the byte[]>');
 INSERT INTO image_table (id, content) VALUES ('2', '<content of the byte[]>');

content.toCharArray or something like that? – Ashalynd Oct 28 '14 at 07:50 — Ashalynd, Oct 28 '14 at 07:50
use JPA and setParameter – Scary Wombat Oct 28 '14 at 08:55 — Scary Wombat, Oct 28 '14 at 08:55

score 4 · Accepted Answer · edited May 23 '17 at 12:10

To answer your question literally: You can write a SQL INSERT script for an integer and a blob value, but that would be rather horrible with hex escaped strings for bytes and you could easily run into problems with long statements for larger blobs; PostgreSQL itself has no practical limit, but most regular text editors do.

As of PostgreSQL 9.0 you can use the hex format (documentation here). Basically, a hex value in text representation looks like E'\x3FB5419C' etc. So you output from your method E'\x, then the byte[] as a hex string, then the closing '. Writing the hex string from the byte[] content can be done with org.apache.commons.codec.binary.Hex.encodeHexString(content) or use this SO answer for a plain Java solution. Depending on your production environment you may have to escape the backslash \\ and fiddle with the newline character. I suggest you give this a try with a small blob to see what works for you.

A better approach is direct insertion. Assuming that you are using the PostgreSQL JDBC driver, the documentation has a worked out example. Given that you have a byte[] class member, you should use the setBytes() method instead of setBinaryStream() (which expects an InputStream instance):

PreparedStatement ps = conn.prepareStatement("INSERT INTO image_table (id, content) VALUES (?, ?)");
ps.setInteger(1, 1);
ps.setBytes(2, content);
ps.executeUpdate();
ps.setInteger(1, 2);
ps.executeUpdate();
ps.close();

Hi Patrick! Thanks for the worth full feedback. Yes as you mention it is a bad practice to write Blob value to row SQL script but according to requirement it need some row SQL script for use in production environment. Therefore I wan to know how can I write Blob value to row SQL script file? What is the data format? — Channa, Oct 29 '14 at 01:23
Hi Patrick! Thanks for the worth full answer. As you mention I have converted byte array to hex (with plain Java solution) and add that value to SQL script as bellow; String hexVal = "'\\x" + bytesToHex(content) + "'"; INSERT INTO image_table (id, content) VALUES ('1', hexVal); — Channa, Oct 30 '14 at 02:25

score 1 · Answer 2 · answered Oct 28 '14 at 10:34

1

You have to place the @Lob annotation on top of your content column. Your final result would be something like this:

import javax.persistence.Lob;
.
.
.
@Lob
@Column(name = "content")
private byte[] content;

answered Oct 28 '14 at 10:34

zaerymoghaddam

3,037
1
27
33

How to dump byte array to INSERT INTO SQL script (Java + PostgreSQL)

2 Answers2