Uploading file from Struts/JSP into PostgreSQL large objects ("lo"/"oid") in the DB

Question

I have an application that uses PostgreSQL, JSP and the STRUTS Framework

I want to insert a file into a table in PostgreSQL using the OID type, so it's stored as a large object in the database.

My table definition is this one:

CREATE TABLE mensaje
(
  id serial NOT NULL,
  file oid,
  CONSTRAINT pk_mensaje PRIMARY KEY (id)
)
WITH (
  OIDS=TRUE
);
ALTER TABLE mensaje
 OWNER TO postgres;

Anybody know an example of how the Action, the ActionForm and the .jsp should be?

If not, is there any other example that explains how to do it without using the OID type?

There are three ways to store files in PostgreSQL: (a) with the oid type as references to `pg_largeobject`, [preferably using the `lo` contrib module to manage them](http://www.postgresql.org/docs/current/interactive/lo.html); (b) as `bytea` values in table columns, preferably only for smaller files; (c) externally on the file system, storing only the file names in the database. See this prior answer: http://stackoverflow.com/a/11929369/398670 — Craig Ringer, Nov 04 '12 at 02:19

score 2 · Accepted Answer · edited May 23 '17 at 12:27

This is a two step process to solve the problem:

File upload using Struts 2
PostgreSQL Java tutorial, check the Writing images section.

Additional note: Once the file has been received in your Action, you should use the byte array data to save it in your OID field.

From your comment, this should be the way in Struts 1.x

In the JSP

<html:form action="fileUploadAction" method="post" enctype="multipart/form-data">
    File : <html:file property="upload" /> 
    <br />
    <html:submit />
</html:form>

In your action class

YourForm uploadForm = (YourForm) form;
FormFile file = null;
try {
    file = uploadForm.getFile();
    //FormFile#getFileData() returns the byte array containing the file data
    //You can use it to save the file in your database and other things you want/need
    int id = 9001; //assuming this is a valid id in the mensaje table
    MensajeService mensajeService = new MensajeService();
    mensajeService.saveFile(id, file.getFileData());
} catch (Exception e) {
    //log the errors for maintenance purposes (bugs, fixes, etc) 
}

The MensajeService class will connect to your Postgre database and save the file

public class MensajeService {

    public MensajeService() {
    }

    public void saveFile(int id, byte[] fileData) throws SQLException {
        //this is a very simple skeleton, you have to adapt this to 
        //your needs, the way you're connecting to dabatase, etc...
        Connection con = null;
        PreparedStatement pstmt = null;
        try {
            con = ... //get the connection to your postgre db

            //Initialize a new transaction
            con.setAutoCommit(false);
            // Get the Large Object Manager to perform operations with
            LargeObjectManager lobj = ((org.postgresql.PGConnection)conn)
                .getLargeObjectAPI();
            // Create a new large object
            int oid = lobj.create(LargeObjectManager.READ | LargeObjectManager.WRITE);
            // Open the large object for writing
            LargeObject obj = lobj.open(oid, LargeObjectManager.WRITE);
            //in the provided example, the code shows a way to get the byte array data
            //from the file (using the File and FileInputStream classes)
            //you don't need all that because you already have the byte array (good!)
            //so you only write the binary data in your LargeObject (OID) object
            obj.write(fileData);

            //creating the SQL statement to insert the OID
            String sql = "INSERT INTO mensaje VALUES (?, ?)";
            pstmt = con.prepareStatement(sql);
            pstmt.setInt(1, id);
            ps.setInt(2, oid);
            //
            pstmt.setBinaryStream(2, fin, (int) img.length());
            //saving the file
            pstmt.executeUpdate();
            //closing the transaction successfully
            con.commit();
        } catch (SQLException e) {
            //error in the transaction, start a rollback
            if (con != null) {
                con.rollback();
            }
            throw e;
        } finally {
            //don't forget to free the resources after using them
            pstmt.close();
            con.close();
        }
    }
}

Struts 1 code adapted from: Uploading a file in struts1.

PostreSQL code adapted from here.

Sorry, i edited the tags. Does this work with Struts 1 as well? — camelCase, Nov 04 '12 at 19:00

Uploading file from Struts/JSP into PostgreSQL large objects ("lo"/"oid") in the DB

1 Answers1