Java JTable Exported to Existing MS Access Table

Question

I scoured the web and found nothing for this. I have a group of records in a JTable and I need to export these records to an existing table in an MS Access database. I need to be able to append records to this table as there may already be data present.

I've spent the last two days reading up and learning about Jackcess and UcanAccess libraries. I'm absolutely tapped out at this point so if someone would be kind enough to post some code I would really appreciate it.

Edit: 5:15 PM PT Monolithic task for sure. Thanks to all for the helpful advice. I just now managed to arrive at a solution. I read a post that helped me understand the contents of a jTable are really only there for display purposes and not the ideal source to be using to export data sets to other databases. So I used the ImportUtil in the Jackcess library to directly export the ResultSet to my Access database. The answer was right in front of me the whole time: http://jackcess.sourceforge.net/ (it's the fourth code sample from the top)

So here is the AccessExporter.java class I created for this. It takes three parameters a ResultSet object, "TableName" and a File object defining where the database file is located. Here's the code:

import com.healthmarketscience.jackcess.Database;
import com.healthmarketscience.jackcess.DatabaseBuilder;
import com.healthmarketscience.jackcess.util.ImportUtil;
import java.io.File;
import java.sql.ResultSet;

/**
 *
 * @author petehahn
 */
public class AccessExporter {
    void fillData(ResultSet jTableResults, String dbTableName, File dbFile){
    try {
        Database dbTarget = DatabaseBuilder.open(dbFile);
        new ImportUtil.Builder(dbTarget, dbTableName).importResultSet(jTableResults);

    dbTarget.close();

        } catch (Exception ex) {
            System.out.println(ex.getMessage());
        }   
        }
}

Answer 1

The SimpleTableDemo from The Java™ Tutorials, which creates and displays a JTable like this

contains a method named printDebugData that writes the table data to the console. We can tweak that code to write the table data to an Access database instead.

We'll assume that you've downloaded UCanAccess and added the required references to your project as illustrated in

Manipulating an Access database from Java without ODBC

We'll also assume that you already have a table named [SimpleTableDemo] in your Access database with fields

ID - AutoNumber, Primary Key
FirstName - Text(255)
LastName - Text(255)
Sport - Text(255)
NumYears - Number(Long Integer)
Vegetarian - Yes/No

The first line in public class SimpleTableDemo sets the DEBUG variable. We need that set to true:

private boolean DEBUG = true;

Then we can modify the printDebugData method to update the database

private void printDebugData(JTable table) {
    // modified to write table data to database instead of printing to console
    int numRows = table.getRowCount();
    javax.swing.table.TableModel model = table.getModel();

    try (Connection conn = DriverManager.getConnection(
            "jdbc:ucanaccess://C:/Users/Public/Database1.accdb")) {
        try (PreparedStatement ps = conn.prepareStatement(
                "INSERT INTO SimpleTableDemo (" +
                "FirstName, LastName, Sport, NumYears, Vegetarian " +
                ") VALUES (?,?,?,?,?)")) {
            for (int i=0; i < numRows; i++) {
                ps.setString(1, (String)model.getValueAt(i, 0));  // FirstName
                ps.setString(2, (String)model.getValueAt(i, 1));  // LastName
                ps.setString(3, (String)model.getValueAt(i, 2));  // Sport
                ps.setInt(4, (int)model.getValueAt(i, 3));  // NumYears
                ps.setBoolean(5, (boolean)model.getValueAt(i, 4));  // Vegetarian
                ps.executeUpdate();
            }
        }
        System.out.println("Database updated.");
    } catch (Exception e) {
        e.printStackTrace(System.err);
    }
}