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[]>');
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();