java - 将文件从 Struts/JSP 上传到数据库中的 PostgreSQL 大对象 ("lo"/"oid")

Question

我有一个使用 PostgreSQL、JSP 和 STRUTS 框架的应用程序

我想使用 OID 类型将文件插入到 PostgreSQL 中的表中，因此它作为大对象存储在数据库中。

我的表定义是这样的:

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

有人知道 Action、ActionForm 和 .jsp 应该是怎样的示例吗？

如果没有，是否有任何其他示例可以说明如何在不使用 OID 类型的情况下执行此操作？

Answer 1

这是解决问题的两步过程:

1. File upload using Struts 2
2. PostgreSQL Java tutorial ，检查写入图像部分。

附加说明:在您的 Action 中收到文件后，您应该使用字节数组数据将其保存在 OID 字段中。

---

根据您的评论，这应该是 Struts 1.x 中的方式

在 JSP 中

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

在你的 Action 类中

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) 
}

MensajeService 类将连接到您的 Postgre 数据库并保存文件

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 代码改编自:Uploading a file in struts1 .

PostreSQL 代码改编自 here .