Today i'm gonna share with you how to import and export your database date using sqlite in your android application.
We will see how to create a sqlite database using SQLiteOpenHelper, how to populate it once while initializing and how to import and export it.
First Step: Defining an Entity
For this example we will implement a LanguageEntitty like shown bellow.public class LanguageEntity {
private int id;
private String language;
public int getId() {
return this.id;
}
public void setId(int id) {
this.id = id;
}
public String getLanguage() {
return this.language;
}
public void setLanguage(String language) {
this.language = language;
}
}
Defining our DbFactory
The DbFactory will be responsible for defining all database and table data in a central place. It is also responsible for creating the first database instance and tables if it not exits already. It has the methods to import or export the database as you need.import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.nio.channels.FileChannel;
import android.content.ContentValues;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Environment;
import android.util.Log;
/**
* Class responsible for defining and creating, updating or deleting DB, tables and its columns.
*
* @author Ricardo Ferreira
* @version 1.0
* @since 17/07/2014
*/
public class DbFactory extends SQLiteOpenHelper {
// Database & Name Version
private static final int DATABASE_VERSION = 1;
private static final String DATABASE_NAME = "yourdatabasename.db";
// your table name. In this example language
public static final String LANGUAGE = "language";
public static final String LANGUAGE_ID = "language_id";
public static final String LANGUAGE_NAME = "language_name";
// used SQL statements
private static final String UNIQUE = " UNIQUE ";
private static final String INTEGER = " INTEGER ";
private static final String TEXT = " TEXT ";
private static final String INTEGERC = " INTEGER, ";
private static final String TEXTC = " TEXT, ";
private static final String PARENTHSE_LEFT = " ( ";
private static final String PARENTHSE_RIGHT = " ) ";
private static final String CREATE_TABLE_IF_NOT_EXISTS = "CREATE TABLE IF NOT EXISTS ";
private static final String INTEGER_PRIMARY_KEY = " INTEGER PRIMARY KEY, ";
private static final String DROP_TABLE_IF_EXISTS = "DROP TABLE IF EXISTS ";
private final String DB_FILEPATH;
public DbFactory(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
final String packageName = context.getPackageName();
DB_FILEPATH = "/data/data/" + packageName + "/databases/yourdatabasename.db";
}
@Override
public void onCreate(SQLiteDatabase db) {
createLanguageTable(db);
populateLanguageOnce(db);
}
private void populateLanguageOnce(SQLiteDatabase db) {
String [ ] languages = new String [ ] { "C", "C++", "C#", "Java" };
for (String language : languages) {
insertLanguages(db, language);
}
}
private void insertLanguages(SQLiteDatabase db, String value) {
ContentValues columnValuePair = new ContentValues();
columnValuePair.put(LANGUAGE_NAME, value);
db.insert(LANGUAGE, null, columnValuePair);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
dropTables(db);
recreateTables(db);
}
private void dropTables(SQLiteDatabase db) {
db.execSQL(DROP_TABLE_IF_EXISTS + LANGUAGE);
}
private void recreateTables(SQLiteDatabase db) {
onCreate(db);
}
private void createLanguageTable(SQLiteDatabase db) {
final StringBuilder sql = new StringBuilder();
sql.append(CREATE_TABLE_IF_NOT_EXISTS);
sql.append(LANGUAGE);
sql.append(PARENTHSE_LEFT);
sql.append(LANGUAGE_ID);
sql.append(INTEGER_PRIMARY_KEY);
sql.append(LANGUAGE_NAME);
sql.append(TEXTC);
sql.append(UNIQUE); // ensures uniqueness for languages
sql.append(PARENTHSE_LEFT);
sql.append(LANGUAGE_NAME);
sql.append(PARENTHSE_RIGHT);
sql.append(PARENTHSE_RIGHT);
Log.d("CREATE_TABLE_LANGUAGE", sql.toString());
db.execSQL(sql.toString());
}
/**
* Copies the database file at the specified location
* over the current internal application database.
* */
public boolean importDatabase(String dbPath) throws IOException {
// Close the SQLiteOpenHelper so it will
// commit the created empty database to internal storage.
close();
File newDb = new File(dbPath);
File oldDb = new File(DB_FILEPATH);
if (newDb.exists()) {
copyFile(new FileInputStream(newDb), new FileOutputStream(oldDb));
// Access the copied database so SQLiteHelper
// will cache it and mark it as created.
getWritableDatabase().close();
return true;
}
return false;
}
private void copyFile(FileInputStream fromFile, FileOutputStream toFile) throws IOException {
FileChannel fromChannel = null;
FileChannel toChannel = null;
try {
fromChannel = fromFile.getChannel();
toChannel = toFile.getChannel();
fromChannel.transferTo(0, fromChannel.size(), toChannel);
} finally {
try {
if (fromChannel != null) {
fromChannel.close();
}
} finally {
if (toChannel != null) {
toChannel.close();
}
}
}
}
public void backupDatabase() throws IOException {
if (isSDCardWriteable()) {
// Open your local db as the input stream
String inFileName = DB_FILEPATH;
File dbFile = new File(inFileName);
FileInputStream fis = new FileInputStream(dbFile);
String outFileName = Environment.getExternalStorageDirectory() + "/syntaxionary";
// Open the empty db as the output stream
OutputStream output = new FileOutputStream(outFileName);
// transfer bytes from the inputfile to the outputfile
byte[] buffer = new byte[1024];
int length;
while ((length = fis.read(buffer)) > 0) {
output.write(buffer, 0, length);
}
// Close the streams
output.flush();
output.close();
fis.close();
}
}
private boolean isSDCardWriteable() {
boolean rc = false;
String state = Environment.getExternalStorageState();
if (Environment.MEDIA_MOUNTED.equals(state)) {
rc = true;
}
return rc;
}
}
Creating a DAO (Data Access Object)
Before you can use the DbFactory, we must create a DAO who knows how to open and close the database properly. We will see how to query, insert and delete rows in the database. The DAO is designed as a singleton, because it can be used anywhere in your application and because we don't need many instances of it. Let's do it in the next section:import java.util.ArrayList;
import java.util.List;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;
import com.treslines.syntaxionary.entity.LanguageEntity;
/**
* Use this class to manipulate the DB created over DbFactory.
* Usage example: Db.open(...).insert(...);
* After execution the Db will always be closed automatically.
* @author Ricardo Ferreira
* @version 1.0
* @since 17/07/2014
*/
public class DAO {
private static final String TAG = "TAG";
private static final long INSERT_ERROR = -1;
private static final int UPDATE_ERROR = 0;
private static final String DB_QUERY_SIGN = "= ?";
private static DAO instance;
private DbFactory dbFactory;
private SQLiteDatabase db;//NOPMD
// singleton
private DAO(Context context) {
super();
this.dbFactory = new DbFactory(context);
this.db = this.dbFactory.getWritableDatabase();
}
/** always start a new db transaction over DAO.open(...) */
public synchronized static DAO open(Context context) {
if (instance == null) {
instance = new DAO(context);
}
return instance;
}
public long insertLanguage(LanguageEntity language) {
final String columnName = DbFactory.LANGUAGE_NAME;
ContentValues values = new ContentValues();
values.put(columnName, language.getLanguage());
long newRowId = db.insert(DbFactory.LANGUAGE, null, values);
close();
return newRowId;
}
public int getLanguageCounter() {
final String tableName = DbFactory.LANGUAGE;
final Cursor result = db.query(tableName, null, null,null,null,null,null);
int counter = result.getCount();
close();
return counter;
}
public boolean hasLanguage(String newLanguage) {
final String tableName = DbFactory.LANGUAGE;
String where = DbFactory.LANGUAGE_NAME+DB_QUERY_SIGN;
String [ ] languages = new String [ ] {newLanguage};
final Cursor result = db.query(tableName, null, where,languages,null,null,null);
boolean canInsert = result.moveToNext();
close();
return canInsert;
}
public int queryLanguageIdByName(String language) {
final String tableName = DbFactory.LANGUAGE;
String where = DbFactory.LANGUAGE_NAME+ DB_QUERY_SIGN;
Cursor result = db.query(tableName, null,where,new String [ ] {language},null,null,null,null);
while(result.moveToNext()){
int columnIndex = result.getColumnIndex(DbFactory.LANGUAGE_ID);
return result.getInt(columnIndex);
}
return 0;
}
public boolean insertLanguage(String newLanguage) {
final String tableName = DbFactory.LANGUAGE;
ContentValues tableValues = new ContentValues();
tableValues.put(DbFactory.LANGUAGE_NAME, newLanguage);
long newRowId = db.insert(tableName, null, tableValues);
close();
return newRowId != INSERT_ERROR;
}
public List < String > queryLanguages() {
Cursor result = db.query(DbFactory.LANGUAGE, new String [ ]{DbFactory.LANGUAGE_NAME}, null, null, null, null, null);
List< String > languages = new ArrayList< String >();
while(result.moveToNext()){
int nameIndex = result.getColumnIndex(DbFactory.LANGUAGE_NAME);
languages.add(result.getString(nameIndex));
}
return languages;
}
public int deleteLanguage(String language) {
final String tableName = DbFactory.LANGUAGE;
String where = DbFactory.LANGUAGE_NAME + DB_QUERY_SIGN;
String [ ] languages = new String [ ] { language };
int result = db.delete(tableName, where, languages);
close();
return result;
}
/** Use Db.open(...) to open a new connection */
public void close() {
if (db != null && db.isOpen()) {
db.close();
db = null;
}
if (dbFactory != null) {
dbFactory.close();
dbFactory = null;
}
instance =null;
}
}
Usage of the DbFactory and DAO together
Here we can see how we could use it in the practise. In the DbFactory we have methods to import or export the database as you may need.//... more code omitted ...
DAO.open(getContext()).queryLanguageIdByName("Java");
//... more code omitted ...
Thats all. Hope you like it!
😱👇 PROMOTIONAL DISCOUNT: BOOKS AND IPODS PRO 😱👇
Be sure to read, it will change your life!
Show your work by Austin Kleon: https://amzn.to/34NVmwx
This book is a must read - it will put you in another level! (Expert)
Agile Software Development, Principles, Patterns, and Practices: https://amzn.to/30WQSm2
Write cleaner code and stand out!
Clean Code - A Handbook of Agile Software Craftsmanship: https://amzn.to/33RvaSv
This book is very practical, straightforward and to the point! Worth every penny!
Kotlin for Android App Development (Developer's Library): https://amzn.to/33VZ6gp
Needless to say, these are top right?
Apple AirPods Pro: https://amzn.to/2GOICxy
😱👆 PROMOTIONAL DISCOUNT: BOOKS AND IPODS PRO 😱👆
