Export and import SQLite database

Question

Good evening, I'm developing an android app, it has SQLite database, I need a way to copy the database to the external storage of the device, where I can copy to another device and so I can import the database into the another device.

For example:

Suppose the application calls "example", the database is in the "/data/data/com.gnd.example/databases" folder and it is called data.db, need to copy it to the "example / backup" folder ", for example" / storage / emulated / 0 / Example / Backup ". This is the first part.

The second part is the import, where the application should copy the file from the "example / import" folder to the folder "/data/data/com.gnd.example/databases"

For this I have a two button activity, btn_export and btn_import.

I have already relied on the following solutions:

import / export to android sqlite database Simple export and import of SQLite database on Android

I already inserted it in AndroidManifest

How do I ask the user for permission?

I tried copying using this code that I took in one of the examples

private void backupDatabase () throws IOException {
   String inFileName = "/data/data/com.gnd.example/databases/dados.db";
   File dbFile = new File (inFileName);
   FileInputStream fis = new FileInputStream (dbFile);

   String outFileName = Environment.getExternalStorageDirectory () + "/ example / backup / data.db";
   OutputStream output = new FileOutputStream (outFileName);
   byte [] buffer = new byte [1024];
   int length;
   while ((length = fis.read (buffer))> 0) {
       output.write (buffer, 0, length);
   }
   output.flush ();
   output.close ();
   fis.close ();

}

The button looks like this:

   @Override
   public void onClick (View view) {
       try {
           backupDatabase ();
       } catch (IOException e1) {
           e1.printStackTrace ();
       }
   
});

Log when I press the button:

07/01 19:35:39: Launching app
$ adb shell am start -n "com.gnd.keepkey / com.gnd.keepkey.Telephone" -a android.intent.action.MAIN -c android.intent.category.LAUNCHER
Client not ready yet..Waiting for process to come online
Waiting for process to come online
Connected to process 27724 on device motorola-moto_z2_play-0039635857
Capturing and displaying logcat messages from application. This behavior can be disabled in the "Logcat output" section of the "Debugger" settings page.
I / zygote: Do partial code cache collection, code = 20KB, data = 29KB
I / zygote: After code cache collection, code = 20KB, data = 29KB
    Increasing code cache capacity to 128KB
I / zygote: Do partial code cache collection, code = 20KB, data = 47KB
I / zygote: After code cache collection, code = 20KB, data = 47KB
    Increasing code cache capacity to 256KB
I / zygote: Compiler allocated 4MB to compile void android.widget.TextView. <Init> (android.content.Context, android.util.AttributeSet, int, int)
I / zygote: Full code cache collection, code = 120KB, data = 82KB
I / zygote: After code cache collection, code = 117KB, data = 62KB
I / zygote: Do partial code cache collection, code = 125KB, date = 79KB
I / zygote: After code cache collection, code = 125KB, data = 79KB
    Increasing code cache capacity to 512KB
W / System.err: java.io.FileNotFoundException: /storage/emulated/0/teste/dados.db (No such file or directory)
        at java.io.FileOutputStream.open0 (Native Method)
W / System.err: at java.io.FileOutputStream.open (FileOutputStream.java:287)
        at java.io.FileOutputStream. <init> (FileOutputStream.java:223)
        at java.io.FileOutputStream. <init> (FileOutputStream.java:110)
        at com.gnd.keepkey.funcoes.Exportar_Importar.backupDatabase (Export_Importar.java:87)
        at com.gnd.keepkey.funcoes.Exportar_Importar.access $ 000 (Export_Importar.java:42)
        at com.gnd.keepkey.funcoes.Export_Import $ 1.onClick (Export_Import.java:69)
W / System.err: at android.view.View.performClick (View.java:6259)
        at android.view.View $ PerformClick.run (View.java:24732)
        at android.os.Handler.handleCallback (Handler.java:789)
        at android.os.Handler.dispatchMessage (Handler.java:98)
        at android.os.Looper.loop (Looper.java:164)
        at android.app.ActivityThread.main (ActivityThread.java:6592)
        at java.lang.reflect.Method.invoke (Native Method)
W / System.err: at com.android.internal.os.Zygote $ MethodAndArgsCaller.run (Zygote.java:240)
        at com.android.internal.os.ZygoteInit.main (ZygoteInit.java:769)

Answer 1

FileNotFoundException is likely due to the directory teste not existing, perhaps due to permissions.

Using :-

private void backupDatabase () throws IOException {
    String inFileName = "/data/data/com.gnd.example/databases/dados.db";
    File dbFile = new File (inFileName);
    FileInputStream fis = new FileInputStream (dbFile);

    String outFileName = Environment.getExternalStorageDirectory () + "/ example / backup / data.db";
    //<<<<<<<<<<< CODE ADDED >>>>>>>>>>
    File os = new File(outFileName); 
    if (!os.getParentFile().exists()) {
        os.getParentFile().mkdirs();
    }
    //<<<<<<<<<< END Of ADDED CODE >>>>>>>>>>
    OutputStream output = new FileOutputStream(os); //<<<<<<<<<< CHANGED
    byte [] buffer = new byte [1024];
    int length;
    while ((length = fis.read (buffer))> 0) {
        output.write (buffer, 0, length);
    }
    output.flush ();
    output.close ();
    fis.close ();
}

will create the directories if they do not exist (assuming permissions are correct)

Working Example :-

The following is a working app

AndroidManifest.xml

<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
    package="aso.so56843045backup">
    <uses-permission android:name="android.permission.WRITE_EXTERNAL_STORAGE"/>
    <application
        android:allowBackup="true"
        android:icon="@mipmap/ic_launcher"
        android:label="@string/app_name"
        android:roundIcon="@mipmap/ic_launcher_round"
        android:supportsRtl="true"
        android:theme="@style/AppTheme">
        <activity android:name=".MainActivity">
            <intent-filter>
                <action android:name="android.intent.action.MAIN" />

                <category android:name="android.intent.category.LAUNCHER" />
            </intent-filter>
        </activity>
    </application>
</manifest> 

• Note the <uses-permission android:name="android.permission.WRITE_EXTERNAL_STORAGE"/> (for earlier devices)

ExternalStoragePermissions.java

class ExternalStoragePermissions {

    public int API_VERSION = Build.VERSION.SDK_INT;
    private static final int REQUEST_EXTERNAL_STORAGE = 1;
    private static String[] PERMISSIONS_STORAGE = {

            //Manifest.permission.READ_EXTERNAL_STORAGE,
            Manifest.permission.WRITE_EXTERNAL_STORAGE
    };
    public static final String THISCLASS = ExternalStoragePermissions.class.getSimpleName();
    private static final String LOGTAG = "SW_ESP";

    public ExternalStoragePermissions() {}
    // Note call this method
    public static void verifyStoragePermissions(Activity activity) {
        int permission = ActivityCompat.checkSelfPermission(
                activity,
                Manifest.permission.WRITE_EXTERNAL_STORAGE);

        if(permission != PackageManager.PERMISSION_GRANTED) {
            ActivityCompat.requestPermissions(
                    activity,
                    PERMISSIONS_STORAGE,
                    REQUEST_EXTERNAL_STORAGE
            );
        }
    }
}

• If permissions not given then the directories cannot be created resulting in FileNotFoundException.

DBHelper.java

public class DBHelper extends SQLiteOpenHelper {

    public static final String DBNAME = "dados.db";
    public static final int DBVERSION = 1;

    public DBHelper(Context context) {
        super(context, DBNAME, null, DBVERSION);
        this.getWritableDatabase();
    }

    @Override
    public void onCreate(SQLiteDatabase db) {

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }
}

• A Very basic empty database (exception android_metadata table), enough to check backup.

MainActivity.java

public class MainActivity extends AppCompatActivity {

    DBHelper mDBHlpr;
    Button mBackup;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        mBackup = this.findViewById(R.id.backup);
        mBackup.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                mDBHlpr.close();
                try {
                    backupDatabase();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        });
        ExternalStoragePermissions.verifyStoragePermissions(this);
        mDBHlpr = new DBHelper(this);
    }

    private void backupDatabase () throws IOException {
        FileInputStream fis = new FileInputStream (this.getDatabasePath("dados.db").getPath());
        String outFileName = Environment.getExternalStorageDirectory () + "/example/backup/" + String.valueOf(System.currentTimeMillis()) + "data.db";
        Log.d("OSFILEPATH",outFileName);
        File os = new File(outFileName);
        if (!os.getParentFile().exists()) {
            os.getParentFile().mkdirs();
        }
        OutputStream output = new FileOutputStream(os);
        byte [] buffer = new byte [1024];
        int length;
        while ((length = fis.read (buffer))> 0) {
            output.write (buffer, 0, length);
        }
        output.flush ();
        output.close ();
        fis.close ();
    }
}

Notes

• When first run after install permission will be requested for later devices (click allow).

• Backup has been named with a timestamp so multiple backups can exist.

• Database is closed, (this should cope with Android Pie+ wehere default is WAL mode, the close should empty (commit the changes) the -wal and -shm files, thus negating the need to backup the additional files).

Result

Answer 2

With respect to your crash:

• You have not created the directory, at least not via your code. Create a File object pointing to the directory that you want, then call mkdirs() on that object.

• You might not be holding the WRITE_EXTERNAL_STORAGE permission, including requesting it at runtime. See https://developer.android.com/training/permissions/requesting.

Other problems here include:

• You will not have the ability to write to your requested location in Android Q (by default) and Android R (for all apps). I recommend that you write to getExternalFilesDir() (found on Context) or use the Storage Access Framework.

• You are doing disk I/O on the main application thread. This will cause your UI to freeze while that I/O is occurring. Users may think that your app is broken. There are many ways to address this, though the Jetpack approach would be to use a ViewModel and LiveData.

• You are not arranging to get your file indexed by the MediaStore, so the user will not be able to see it in their desktop file manager. Use MediaScannerConnection.scanFile() to index the file.

• "/data/data/com.gnd.example/databases/dados.db" is the wrong path on many Android devices. Never hardcode paths. Use getDatabasePath() on Context to get the path to your database.

This sample Java app shows how a lot of this is done, in the context of a text editor instead of a database backup solution.

In general, I recommend that you put this project aside for a while and read an up-to-date book on Android app development. Most of the problems that I mention here are on topics that would be covered in a decent book on Android.