How can I update a BLOB from an InputStream via an EntityManager?

Question

I have a table:

mysql> desc documents;
+---------+--------------+------+-----+---------------------+----------------+
| Field   | Type         | Null | Key | Default             | Extra          |
+---------+--------------+------+-----+---------------------+----------------+
| id      | int(11)      | NO   | PRI | NULL                | auto_increment |
| item_id | int(11)      | YES  | MUL | NULL                |                |
| doctype | int(11)      | YES  | MUL | NULL                |                |
| name    | varchar(128) | YES  | MUL | NULL                |                |
| descr   | text         | YES  |     | NULL                |                |
| created | timestamp    | NO   |     | CURRENT_TIMESTAMP   |                |
| changed | timestamp    | NO   |     | 0000-00-00 00:00:00 |                |
| doc     | longblob     | YES  |     | NULL                |                |
+---------+--------------+------+-----+---------------------+----------------+

Which I have mapped as an Entity like this:

@Entity
@Table(name = "documents", schema = "office_db")
@XmlRootElement
@NamedQueries({
    ...,
    @NamedQuery(name = "Documents.updateDocById", query = "UPDATE Documents d SET d.doc = :document WHERE d.id = :id")
})
public class Documents implements Serializable {

    @Column(name = "name", length = 128)
    private String name;
    @Lob
    @Column(name = "descr", length = 65535)
    private String descr;
    @Lob
    @Column(name = "doc")
    private byte[] doc;
    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Basic(optional = false)
    @Column(name = "id")
    private Integer id;
    @Basic(optional = false)
    @Column(name = "created")
    @Temporal(TemporalType.TIMESTAMP)
    private Date created;
    @Basic(optional = false)
    @Column(name = "changed")
    @Temporal(TemporalType.TIMESTAMP)
    private Date changed;
    ...
}

And I want to update the LONGBLOB DOC column with the contents of a file. To that end, I have acquired an InputStream and want to associate it with my NamedQuery - I have naively tried the following, expecting it to fail (which it does, of course):

public String updateDocument(Integer id,InputStream is){
    String msg="";
    try{
        Query stmt=em.createNamedQuery("Documents.updateDocById")
                .setParameter("document",is);
        stmt.executeUpdate();
    }
    ...
}

If I had used a PreparedStatement, I could have used the setBinaryStream method, but is that really the right way?

score 2 · Accepted Answer · edited Jan 06 '18 at 10:20

So, to answer my own question: It seems that JPA does not support streaming, at least not until JPA 2.2 according to What’s new in JPA 2.2 – Stream the result of a Query execution | Vlad Mihalcea's Blog - and it looks like it is only for streaming the results of a query, not for updating a BLOB, which is what I was looking for.

It looks like it is back to using a PreparedStatement and setBinaryStream(). I havent tried it yet, but based on How to get DataSource or Connection from JPA2 EntityManager in Java EE 6, my strategy will be to:

Start a transaction.
Extract the connection from the EntityManager.
Create a PreparedStatement.
...
Commit or rollback the transaction.

I think it is a bit nasty, but there you are.

How can I update a BLOB from an InputStream via an EntityManager?

1 Answers1