My Android app's Ucanaccess connection to a huge MS Access database takes up too much heap space on the Android device. Any alternatives?

I am developing an Android application that needs to fetch data from a huge 120MB MS Access database.

I wrote some code to establish a connection and execute a simple query on the database. I am running the same java code on my laptop and my android device. Here is the code: p

ackage practiceDB;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;


import java.util.Scanner;

import net.ucanaccess.converters.TypesMap.AccessType;
import net.ucanaccess.ext.FunctionType;
import net.ucanaccess.jdbc.UcanaccessConnection;
import net.ucanaccess.jdbc.UcanaccessDriver;

public class Example {
    private Connection ucaConn;
    public Example() {
        try {
            this.ucaConn = getUcanaccessConnection("VehicleDatabase2.mdb");
        } catch (SQLException e) {
            e.printStackTrace();
        } catch(IOException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        System.out.println("Please enter an int");
        new Scanner(System.in).nextInt();

        try {
            Example example = new Example();

            example.executeQuery();
        } catch (Exception ex) {
            System.out.println("An exception : " + ex.getMessage());
        }
    }

    private void executeQuery() throws SQLException {
        Statement st = null;
        try {
            System.out.println("Please enter an int");
            new Scanner(System.in).nextInt();
            st = this.ucaConn.createStatement();
            System.out.println("Please enter an int");
            new Scanner(System.in).nextInt();
            ResultSet rs = st.executeQuery("Select * from PersonData where EngNo = '1544256'");
            System.out.println(" result:");
            dump (rs, "executeQuery");
        } catch(Exception ex) {
            System.out.println("Sarah exception: " + ex.getMessage());
        } finally {
            if ( st != null ) {
                st.close();
            }
        }
    }

    private Connection getUcanaccessConnection(String pathNewDB) throws SQLException, IOException {
        String url  = UcanaccessDriver.URL_PREFIX + "VehicleDatabase2.mdb;newDatabaseVersion=V2003";

        return DriverManager.getConnection(url);
    }

    private void dump(ResultSet rs, String exName) 
            throws SQLException {
        System.out.println("-------------------------------------------------");
        System.out.println();

        System.out.println();
        int jk = 0;
        while (rs.next()) {

            System.out.print("| ");
            int j=rs.getMetaData().getColumnCount();
            for (int i = 1; i <=j ; ++i) {
                Object o = rs.getObject(i);
                System.out.print(o + " | ");
            }
        System.out.println();
        System.out.println();
        }
    }
}

      

When it works on my laptop, it takes about a minute to connect. But when it starts on my android device it takes over 10 minutes to connect and takes up all the heap space, and when the device runs out of memory the app crashes

What should I do?

Note:
I made some small changes to this code to get it running on android, like add toasts instead of System.out.println for debugging, I removed the static main function for android, used Environment.getAbsolutePath () to find the database, etc. .d. Also, the code I'm running on Android, I first used a 9MB database to check if it works. The code fetches data as expected from the 9MB database without any problem. It takes about 10 seconds to install on Android within 9 seconds to connect to 9MB database (on desktop it takes less than a second to establish a 9MB database connection).

+3


source to share


1 answer


Yes I know it should work on medium sized db. With a huge ...

First, note that the time you are measuring, the time of the very first database connection in the life of the VM, the next (if necessary) will be instantaneous.

Never tried something like this on Android because your experiment is tricky, but if it suits your requirements, you can try:

-use the MirrorFolder (or keepMirror) parameter (see the ucanaccess website for details). In this case, the very first connection to db will be very slow, all subsequent ones (even if vm ends) will be instant. But the access database only needs to be updated with ucanaccess and your android



or alternatively

-use a database of filters (configure it on windows) that links the real database in a subset of externally linked tables which are very necessary for your application (memory usage can be omitted). In this case, you will need to use the connection reassignment option because you are on a Linux based OS.

See another suggestion related to jackcess (basic I / O library) here and use the latest ucanaccess release.

+2


source







All Articles