EJB and preparedStatement?

Question

I am developing a web application, where, among other things, I need to upload a file to a BLOB column in a mysql table. From what I can see this can be done with JDBC calls (PrepareStatement() etc), but I would like to be able to do this in an EJB class - what I have cobbled together looks like this:

@Stateless
public class ItemsSession {
    @PersistenceContext(unitName ="officePU")
    private EntityManager em;
    private List<Items> itl;
    private static final Logger logger=
            Logger.getLogger(ItemsSession.class.getName());
...
    public String updateDocument(Integer id,InputStream is) throws SQLException{
        String msg="";
        try{
            java.sql.Connection conn = em.unwrap(java.sql.Connection.class);
            PreparedStatement pstmt=conn.prepareStatement("UPDATE Documents SET doc = ? WHERE id = ?");
            pstmt.setBinaryStream(1, is);
            pstmt.setLong(2, id);
            pstmt.executeUpdate();
            pstmt.close();
        }
        catch (PersistenceException e){
            msg=e.getMessage();
        }

        return msg;
    }
...
}

I have two questions, though:

• I would like not to use JDBC directly - is there a way to do this that is 'pure JPA' (edit: not EJB)?

• If I have to do it this way, is the PreparedStatement included in the container managed transaction?

---

Another edit: the code above does the job - I have now tested it. But it isn't pretty, I think.

Answer 1

The first thing you have to do to persist BLOB values the JPA way is you define an entity. The following an example pseodo code:

@Entity
public class Documents {
    @Id
    private Long id;

    @Lob
    private byte[] doc;

    // .... getters + setters 
}

Then you modify your EJB as follows:

@Stateless
public class ItemsSession {
    @PersistenceContext(unitName ="officePU")
    private EntityManager em;
    // ... the rest of your code

    public String updateDocument(Integer id,InputStream is) throws SQLException{
        String msg = "";
        Documents docs = em.find(Documents.class, id); // fetch record from DB
        // Assuming your InputStream is a ByteArrayInputStream
        byte[] doc = new byte[is.available()]; // create target array
        is.read(doc, 0, doc.length);  // read bytes into byte array
        docs.setDoc(doc); // 

        return msg; // returning exception message from method call?????
    }
    ...
}

If you don't change the defaults EJB methods are invoked in a transaction by default. So when your method exits, the update should be synchronized with the database.

This answer kann only help you if you read and understand the basics of the JPA. And here is an official tutorial to JPA persistence among other lots of tutorials on the web.

Update

I would like not to use JDBC directly - is there a way to do this that is 'pure JPA'

No.

If I have to do it this way, is the PreparedStatement included in the container managed transaction?

No. But you can use bean managed transaction. If you want to use BMT, the following pseudocode might help you:

@Stateless
@TransactionManagement(TransactionManagementType.BEAN)
public class ItemsSession {

    @Resource UserTransaction ut;
    @Resource DataSource datasource;  // you should define datasource on your application server

    ...
    public String updateDocument(Integer id,InputStream is) throws SQLException{
        // ...
        try (java.sql.Connection conn = datasource.getConnection();
            PreparedStatement pstmt=conn.prepareStatement("UPDATE Documents SET doc = ? WHERE id = ?")) {

            pstmt.setBinaryStream(1, is);
            pstmt.setLong(2, id);

            ut.begin();
            pstmt.executeUpdate();
            ut.commit();

        } catch (PersistenceException e){
            // ... error handling
        }

        return ...;
    }
...
}