Aspose.Words

How-to: Load and Save a Document to Database

You can download the complete source code of the DocumentInDB sample here.

One of the tasks you may need to perform when working with documents is storing and retrieving Document objects to and from a database. For example, this would be necessary if you were implementing any type of content management system. The storage of all previous versions of documents would be required to be stored in a database system. The ability to store documents in the database is also extremely useful when your application provides a web-based service.

This sample shows how to store a document into a database and then load it back into a Document object for working with. For the sake of simplicity, the name of the file is the key used to store and fetch documents from the database. The database contains two columns. The first column “FileName is stored as a String and is used to identify the documents. The second column “FileContent is stored as a BLOB object which stores the document object is byte form.

Solution

We will do the following to store, read and delete a document in the database:

Steps to Store a Document into the Database:

1.       Save the source document into a ByteArrayOutputStream. This allows us to get the content of the document as an array of bytes.

2.       Store the array of bytes into a database field.

Steps to Read a Document from the Database:

1.       Select the record that contains the document data as an array of bytes.

2.       Load the array of bytes from the data record into a ByteArrayInputStream.

3.       Create a Document object that will load the document from the input stream.

The following Word document is used in this sample:

The Code

There are three methods implemented in this sample and are described in detail below:

·          The StoreToDatabase method that stores the Document object into the database.

·          The ReadFromDatabase method that reads the stored Document object from the database.

·          The DeleteFromDatabase method that deletes the record containing the specified Document from the database.

Example DocumentInDB_DatabaseHelpers

Helper methods used to connect to and execute queries on a database.

[Java]

 

/**

* Utility function that creates a connection to the Database.

*/

public static void createConnection(String dataBasePath) throws Exception

{

    //  Load a DB driver that is used by the demos

    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

 

    // The path to the database on the disk.

    File dataBase = new File(dataBasePath);

 

    // Compose connection string.

    String connectionString = "jdbc:odbc:DRIVER={Microsoft Access Driver (*.mdb)};" +

            "DBQ=" + dataBase + ";UID=Admin";

    // Create a connection to the database.

    mConnection = DriverManager.getConnection(connectionString);

}

 

/**

* Executes a query on the database.

*/

protected static ResultSet executeQuery(String query) throws Exception

{

    return createStatement().executeQuery(query);

}

 

/**

* Creates a new database statement.

*/

public static Statement createStatement() throws Exception

{

    return mConnection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

}

 

 

Firstly, a connection to the database is created by calling the createConnection method. In this sample we are using a Microsoft Access .mdb database to store an Aspose.Words document. A document is loaded into memory and then converted into a byte array using the general method described in the article here.

Storing a Document into the Database

Example DocumentInDB_StoreToDB

Stores the document to the specified database.

[Java]

 

public static void storeToDatabase(Document doc) throws Exception

{

    // Save the document to a OutputStream object.

    ByteArrayOutputStream outputStream = new ByteArrayOutputStream();

    doc.save(outputStream, SaveFormat.DOC);

 

    // Get the filename from the document.

    String fileName = new File(doc.getOriginalFileName()).getName();

 

    // Create the SQL command.

    String commandString = "INSERT INTO Documents (FileName, FileContent) VALUES(?, ?)";

 

    // Prepare the statement to store the data into the database.

    PreparedStatement statement = mConnection.prepareStatement(commandString);

 

    // Add the parameter value for FileName.

    statement.setString(1, fileName);

 

    // Add the parameter value for FileContent.

    statement.setBinaryStream(2, new ByteArrayInputStream(outputStream.toByteArray()), outputStream.size());

 

    // Execute and commit the changes.

    statement.execute();

    mConnection.commit();

}

 

 

The next step is the most important: specify the commandString, which is an SQL expression that will do all the work. To store the Document into the database the “INSERT INTO” command is used and the table specified along with the values of two record fields – FileName and FileContent. To avoid additional parameters, the file name is acquired from the Document object itself. The FileContent field value is assigned the bytes from the memory stream, which contains the binary representation of the stored document.

The remaining line of code executes the command which stores the Aspose.Words document in the database.

Retrieving a Document from the Database

Example DocumentInDB_ReadFromDB

Retrieves and returns the document from the specified database using the filename as a key to fetch the document.

[Java]

 

public static Document readFromDatabase(String fileName) throws Exception

{

    // Create the SQL command.

    String commandString = "SELECT * FROM Documents WHERE FileName='" + fileName + "'";

 

    // Retrieve the results from the database.

    ResultSet resultSet = executeQuery(commandString);

 

    // Check there was a matching record found from the database and throw an exception if no record was found.

    if(!resultSet.isBeforeFirst())

        throw new IllegalArgumentException(MessageFormat.format("Could not find any record matching the document \"{0}\" in the database.", fileName));

 

    // Move to the first record.

    resultSet.next();

 

    // The document is stored in byte form in the FileContent column.

    // Retrieve these bytes of the first matching record to a new buffer.

    byte[] buffer = resultSet.getBytes("FileContent");

 

    // Wrap the bytes from the buffer into a new ByteArrayInputStream object.

    ByteArrayInputStream newStream = new ByteArrayInputStream(buffer);

 

    // Read the document from the input stream.

    Document doc = new Document(newStream);

 

    // Return the retrieved document.

    return doc;

 

}

 

 

Firstly the SQL command “SELECT * FROM” is used to fetch the appropriate record based off the filename.

The data is then populated from the database into a ResultSet object using the database adapter initialized at the start of the application. The populated ResultSet object is checked to ensure the requested data has actually been extracted. For the final steps, the process used in storing the document is reversed and the bytes are deserialized to be loaded back into a Document object. The Document object is then returned from the method and saved with the appropriate name to disk.

Deleting a Document from the Database

The final method is DeleteFromDatabase. This is quite straightforward as there are no manipulations with the Document object.

Example DocumentInDB_DeleteFromDB

Delete the document from the database, using filename to fetch the record.

[Java]

 

public static void deleteFromDatabase(String fileName) throws Exception

{

    // Create the SQL command.

    String commandString = "DELETE * FROM Documents WHERE FileName='" + fileName + "'";

 

    // Execute the command.

    createStatement().executeUpdate(commandString);

}

 

 

A connection to the database is created and the SQL command “DELETE * FROM” is specified using the filename to seek the appropriate record containing the Document. The document is deleted from the database.

To illustrate all three methods we have called them consecutively:

Example DocumentInDB_Main

Stores the document to a database, then reads the same document back again, and finally deletes the record containing the document from the database.

[Java]

 

// Store the document to the database.

storeToDatabase(doc);

// Read the document from the database and store the file to disk.

Document dbDoc = readFromDatabase(FILE_NAME);

 

// Save the retrieved document to disk.

String newFileName = new File(FILE_NAME).getName() + " from DB" + FILE_NAME.substring(FILE_NAME.lastIndexOf("."));

dbDoc.save(dataDir + newFileName);

 

// Delete the document from the database.

deleteFromDatabase(FILE_NAME);

 

 

End Result

When the code above is executed the following document is stored and then retrieved from the database and will appear in the Data folder: