SQLite in Android

Overview

SQLite is an opensource SQL database that is used to store data into a text file on a device.

SQLite only supports

1. NULL
2. INTEGER
3. REAL
4. TEXT
5. BLOB

SQLite with MVVM

• Add the required dependency in build.gradle (Module) file.
• Create a data class

 data class User(
    val name : String , 
    val dob : String , 
    val address : String , 
    val pin : String , 
    val username : String , 
    val password : String , 
    val isLoggedIn : Int
    )

• Create a class name DBHelper and extend it with SQLiteOpenHelper
• Create the repositories

Some Syntax of SQL commands

Do's and Dont's

• You cannot store a BOOLEAN value in SQLite, you have to use INTEGER for this purpose.

Code to Open Database from external file

public class DatabaseHelper extends SQLiteOpenHelper {
public static final String DATABASE_NAME = "appDatabase.db";
private static final int DATABASE_VERSION = 1;
private static String DATABASE_PATH;
private final Context context;
private SQLiteDatabase appDatabase;
private static final String TABLE_NAME = "customer_master";
private static final String COLUMN_NAME = "customer_name";
private static final String COLUMN_MOBILE = "customer_mobile_number";

public DatabaseHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
this.context = context;
// DATABASE_PATH = "/data/data/" + context.getPackageName() + "/databases/";
DATABASE_PATH = context.getFilesDir().getPath() + context.getPackageName() + "/databases/";
File file = context.getDatabasePath(DATABASE_NAME);
if (!file.exists()) {
copyDatabase();
}
}

private void copyDatabase() {
String destinationFilePath = DATABASE_PATH + DATABASE_NAME;
try {
InputStream inputStream;
OutputStream outputStream;
if (Build.VERSION.SDK_INT >= Build.VERSION_CODES.O) {
inputStream = Files.newInputStream(new File(context.getExternalFilesDir(Environment.DIRECTORY_DOWNLOADS) + File.separator, DATABASE_NAME).toPath());
outputStream = Files.newOutputStream(Paths.get(destinationFilePath));
} else {
inputStream = new FileInputStream(new File(context.getExternalFilesDir(Environment.DIRECTORY_DOWNLOADS) + File.separator + DATABASE_NAME));
outputStream = new FileOutputStream(destinationFilePath);
}
File destinationFileDirectory = new File(destinationFilePath);
destinationFileDirectory.getParentFile().mkdirs();
byte[] buffer = new byte[1024];
int length;
while ((length = inputStream.read(buffer)) > -1) {
outputStream.write(buffer, 0, length);
}
outputStream.flush();
outputStream.close();
inputStream.close();
} catch (Exception e) {
e.printStackTrace();
}
}

private void openDatabase() {
String databasePath = context.getDatabasePath(DATABASE_NAME).getPath();
appDatabase = SQLiteDatabase.openDatabase(databasePath, null, SQLiteDatabase.OPEN_READWRITE);
}

private void closeDatabase() {
if (appDatabase != null) appDatabase.close();
}

@SuppressWarnings("Range")
public List<DataModel> getData() {
List<DataModel> data = new ArrayList<>();
try {
openDatabase();
Cursor cursor = appDatabase.rawQuery("select * from " + TABLE_NAME, null);
if (cursor.moveToFirst()) {
do {
String customerName = cursor.getString(cursor.getColumnIndex(COLUMN_NAME));
String customerMobile = cursor.getString(cursor.getColumnIndex(COLUMN_MOBILE));
data.add(new DataModel(customerName, customerMobile));
} while (cursor.moveToNext());
}
cursor.close();
closeDatabase();
} catch (Exception e) {
e.printStackTrace();
}
return data;
}

@Override
public void onCreate(SQLiteDatabase sqLiteDatabase) {
// Do not override.
}

@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
// Do not override
}
}


//Reference generated by Google Gemini
//private void copyDatabase() {
// try {
// // Read paths from resources or environment variables
// String dbName = getStringFromResource(R.string.database_name);
// String destinationPath = getApplicationContext().getDatabasePath(dbName).getPath();
//
// // Check permissions
// if (ContextCompat.checkSelfPermission(context, Manifest.permission.WRITE_EXTERNAL_STORAGE) != PackageManager.PERMISSION_GRANTED) {
// ActivityCompat.requestPermissions(context, new String[]{Manifest.permission.WRITE_EXTERNAL_STORAGE}, 123);
// return;
// }
//
// // Validate source file
// File sourceFile = new File(context.getExternalFilesDir(Environment.DIRECTORY_DOWNLOADS), dbName);
// if (!sourceFile.exists() || !sourceFile.canRead()) {
// throw new IOException("Source file not found or inaccessible");
// }
//
// // Create destination directory if needed
// File destinationFile = new File(destinationPath);
// destinationFile.getParentFile().mkdirs();
//
// // Efficient file copying
// try (FileInputStream inputStream = new FileInputStream(sourceFile);
// FileOutputStream outputStream = new FileOutputStream(destinationFile)) {
// FileChannel sourceChannel = inputStream.getChannel();
// FileChannel destinationChannel = outputStream.getChannel();
// sourceChannel.transferTo(0, sourceChannel.size(), destinationChannel);
// }
//
// } catch (IOException e) {
// Log.e("DatabaseCopy", "Error copying database", e);
// // Handle specific exceptions here (e.g., SecurityException)
// }
//}

References

1. https://www.tutorialspoint.com/android/android_sqlite_database.htm
   
2. https://abhiandroid.com/database/sqlite
   
3. https://github.com/sqlcipher/sqlcipher
   
4. https://github.com/sqlcipher/android-database-sqlcipher
   
5. https://www.zetetic.net/sqlcipher/sqlcipher-for-android/