How to get all tables from .accdb file and parse it to return in Java Spring Boot MVC

Question

There is .accdb(Microsoft Office Access) file that has 100 tables and I made a function that read one table from the file uploaded by Ajax Request. It works well and I want to update it to read all tables without specifying table names(like module). I just started Java Programming 3 weeks ago and couldn't find right solution. Could you please help me?

Backend

    @PostMapping("/upload")
    @ResponseBody
    public String upload(@RequestParam("file") MultipartFile file) {
    {
        try {
            String url = "jdbc:ucanaccess://" + tempFile.getAbsolutePath();
            Connection conn = DriverManager.getConnection(url);

            // Example: Querying data from a table
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM Car_Model_List");
            ResultSetMetaData rsmd = rs.getMetaData();
            int columnsNumber = rsmd.getColumnCount();
            DatabaseMetaData metaData = conn.getMetaData();
            ResultSet tables = metaData.getTables(null, null, null, new String[]{"TABLE"});

            // Get data rows
            while (rs.next()) {
                Map<String, Object> row = new HashMap<>();
                for (int i = 1; i <= columnsNumber; i++) {
                    String columnName = rsmd.getColumnName(i);
                    Object value = rs.getObject(i);
                    row.put(columnName, value);
 
                }
                resultList.add(row);
            }

            conn.close();
        } catch (IOException | SQLException e) {
            e.printStackTrace();
            // You may want to handle the exception more gracefully here
        }
        result.put("content", resultList);

        return result;
    }

Ajax Request

                    $.ajax({
                        url: '/data_visualization/upload',
                        type: 'POST',
                        processData: false,
                        contentType: false,
                        data: formData,
                        success: function (res) {
                            console.log(res, typeof res)
                            if (typeof res === "object") {
                                toastr.success("All data has been fetched successfully!", "Success")
                                let dataArr = res["content"];
                                let table = $('#kt_table').DataTable({ data: dataArr, ... });
                            }
                            else toastr.error("Something went wrong!");
                        },
                        error: function (err) {
                            console.log(err);
                            toastr.error("Failed!");
                        }
                    })

Answer 1

This will work for you, hope you like it

public String upload(@RequestParam("file") MultipartFile file) {
{
    try {
        String url = "jdbc:ucanaccess://" + tempFile.getAbsolutePath();
        Connection conn = DriverManager.getConnection(url);

        DatabaseMetaData metaData = conn.getMetaData();
        ResultSet tables = metaData.getTables(null, null, null, new String[]{"TABLE"});

        while (tables.next()) {
            String tableName = tables.getString("TABLE_NAME");

            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM " + tableName);

            List<Map<String, Object>> tableContent = new ArrayList<>();
            ResultSetMetaData rsmd = rs.getMetaData();
            int columnsNumber = rsmd.getColumnCount();

            while (rs.next()) {
                Map<String, Object> row = new HashMap<>();
                for (int i = 1; i <= columnsNumber; i++) {
                    String columnName = rsmd.getColumnName(i);
                    Object value = rs.getObject(i);
                    row.put(columnName, value);
                }
                tableContent.add(row);
            }

            result.put(tableName, tableContent);
        }

        conn.close();
    } catch (IOException | SQLException e) {
        e.printStackTrace();
        // You may want to handle the exception more gracefully here
    }

    return result;
}