What Is a Full Android Database Helper Class for an Existing SQLite Database

What is a Full Android Database Helper class for an existing SQLite database?

This is what I came up with, hope it helps others that were having troubles.

package com.MyPackage;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.UUID;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class AnyDBAdapter {

private static final String TAG = "AnyDBAdapter";
private DatabaseHelper mDbHelper;
private static SQLiteDatabase mDb;

//make sure this matches the
//package com.MyPackage;
//at the top of this file
private static String DB_PATH = "/data/data/com.MyPackage/databases/";

//make sure this matches your database name in your assets folder
// my database file does not have an extension on it
// if yours does
// add the extention
private static final String DATABASE_NAME = "data";

//Im using an sqlite3 database, I have no clue if this makes a difference or not
private static final int DATABASE_VERSION = 3;

private final Context adapterContext;

public AnyDBAdapter(Context context) {
this.adapterContext = context;
}

public AnyDBAdapter open() throws SQLException {
mDbHelper = new DatabaseHelper(adapterContext);

try {
mDbHelper.createDataBase();
} catch (IOException ioe) {
throw new Error("Unable to create database");
}

try {
mDbHelper.openDataBase();
} catch (SQLException sqle) {
throw sqle;
}
return this;
}
//Usage from outside
// AnyDBAdapter dba = new AnyDBAdapter(contextObject); //in my case contextObject is a Map
// dba.open();
// Cursor c = dba.ExampleSelect("Rawr!");
// contextObject.startManagingCursor(c);
// String s1 = "", s2 = "";
// if(c.moveToFirst())
// do {
// s1 = c.getString(0);
// s2 = c.getString(1);
// } while (c.moveToNext());
// dba.close();
public Cursor ExampleSelect(string myVariable)
{
String query = "SELECT locale, ? FROM android_metadata";
return mDb.rawQuery(query, new String[]{myVariable});
}

//Usage
// AnyDBAdatper dba = new AnyDBAdapter(contextObjecT);
// dba.open();
// dba.ExampleCommand("en-CA", "en-GB");
// dba.close();
public void ExampleCommand(String myVariable1, String myVariable2)
{
String command = "INSERT INTO android_metadata (locale) SELECT ? UNION ALL SELECT ?";
mDb.execSQL(command, new String[]{ myVariable1, myVariable2});
}

public void close() {
mDbHelper.close();
}

private static class DatabaseHelper extends SQLiteOpenHelper {

Context helperContext;

DatabaseHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
helperContext = context;
}

@Override
public void onCreate(SQLiteDatabase db) {
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
Log.w(TAG, "Upgrading database!!!!!");
//db.execSQL("");
onCreate(db);
}

public void createDataBase() throws IOException {
boolean dbExist = checkDataBase();
if (dbExist) {
} else {

//make sure your database has this table already created in it
//this does not actually work here
/*
* db.execSQL("CREATE TABLE IF NOT EXISTS \"android_metadata\" (\"locale\" TEXT DEFAULT 'en_US')"
* );
* db.execSQL("INSERT INTO \"android_metadata\" VALUES ('en_US')"
* );
*/
this.getReadableDatabase();
try {
copyDataBase();
} catch (IOException e) {
throw new Error("Error copying database");
}
}
}

public SQLiteDatabase getDatabase() {
String myPath = DB_PATH + DATABASE_NAME;
return SQLiteDatabase.openDatabase(myPath, null,
SQLiteDatabase.OPEN_READONLY);
}

private boolean checkDataBase() {
SQLiteDatabase checkDB = null;
try {
String myPath = DB_PATH + DATABASE_NAME;
checkDB = SQLiteDatabase.openDatabase(myPath, null,
SQLiteDatabase.OPEN_READONLY);
} catch (SQLiteException e) {
}
if (checkDB != null) {
checkDB.close();
}
return checkDB != null ? true : false;
}

private void copyDataBase() throws IOException {

// Open your local db as the input stream
InputStream myInput = helperContext.getAssets().open(DATABASE_NAME);

// Path to the just created empty db
String outFileName = DB_PATH + DATABASE_NAME;

// Open the empty db as the output stream
OutputStream myOutput = new FileOutputStream(outFileName);

// transfer bytes from the inputfile to the outputfile
byte[] buffer = new byte[1024];
int length;
while ((length = myInput.read(buffer)) > 0) {
myOutput.write(buffer, 0, length);
}

// Close the streams
myOutput.flush();
myOutput.close();
myInput.close();
}

public void openDataBase() throws SQLException {
// Open the database
String myPath = DB_PATH + DATABASE_NAME;
mDb = SQLiteDatabase.openDatabase(myPath, null,
SQLiteDatabase.OPEN_READWRITE);
}

@Override
public synchronized void close() {

if (mDb != null)
mDb.close();

super.close();

}
}

}

Database Helper class?

Edit

Now that you added the log. The issue is that column SEENON_DTM doesn't exist.

As per :-

  • table ANIMALS has no column named SEENON_DTM

You have probably added the column since running the App. Just amending the SQL used by the onCreate method isn't enough to change the structure of the database. That is onCreate is only run once when the database is created, the database is then saved to a file and hence how it stays around. Subequent instantation of the DatabaseHelper, find the file and doesn't call onCreate.

The fix, if losing any data (no useful data would likely exist) isn't an issue is to do one of the following;

  • delete the App's data (from settings), or
  • uninstall the App, or
  • increase the database version i.e. change super(context, Table_Name, null, 1); to super(context, Table_Name, null, 2); (1 to 2)

    • (this will result in the onUpgrade method running which should drop the table (see note below about using DROP TABLE IF EXISTS and not DROP IF TABLE EXISTS))

and then rerun the App.


I believe that you code is not an issue (the only issue in the code is with DROP IF TABLE EXISTS, it should be DROP TABLE IF EXISTS ).

Your code works as expected (see below), so the issue is either how you are checking the data or that the Animal class is the cause.

Below is code that uses you DatabaseHelper code and invokes it from an Activity (the Animal class may be different to yours).

I'd suggest trying the code below, suitably adjusted for your DatabaseHelper class (i.e. change AnimalDBhelper to DatabaseHelper) and then checking the log. The output should be as below (perhaps with more rows if you have added rows and the database hasn't been deleted).

Testing

Using a copy of your code (renaming the class to AnimalDBhelper to suit my testing environment) and then using the following code in an activity (the invoking code) :-

public class MainActivity extends AppCompatActivity {

AnimalDBhelper mADBHlpr;

@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.main_activity);

// Create two animals
mADBHlpr = new AnimalDBhelper(this);
Animal myanimal1 = new Animal(1,0,"Yes","Raining");
Animal myanimal2 = new Animal(1,0,"No","");

mADBHlpr.addData(myanimal1); // Add the first animal
Cursor csr = mADBHlpr.getData(); get the data
DatabaseUtils.dumpCursor(csr); dump the data (write it to the log)
mADBHlpr.addData(myanimal2); // Add the 2nd
csr = mADBHlpr.getData();
DatabaseUtils.dumpCursor(csr);
csr.close(); // Done with the cursor so close
}
}

Result :-

12-02 19:37:54.040 1172-1172/? D/DatabaseHelper: addData: Adding ?.Animal@534af570 to ANIMALS
12-02 19:37:54.044 1172-1172/? I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@534a5df8
12-02 19:37:54.044 1172-1172/? I/System.out: 0 {
12-02 19:37:54.044 1172-1172/? I/System.out: ID=1
12-02 19:37:54.044 1172-1172/? I/System.out: ANIMAL_TYPE_CD=0
12-02 19:37:54.044 1172-1172/? I/System.out: COUNT_NO=null
12-02 19:37:54.044 1172-1172/? I/System.out: SEENON_DTM=null
12-02 19:37:54.044 1172-1172/? I/System.out: COMMENTS_TXT=Raining
12-02 19:37:54.044 1172-1172/? I/System.out: }
12-02 19:37:54.044 1172-1172/? I/System.out: <<<<<
12-02 19:37:54.044 1172-1172/? D/DatabaseHelper: addData: Adding ?.Animal@534af5c4 to ANIMALS
12-02 19:37:54.048 1172-1172/? I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@534ab7d4
12-02 19:37:54.048 1172-1172/? I/System.out: 0 {
12-02 19:37:54.048 1172-1172/? I/System.out: ID=1
12-02 19:37:54.048 1172-1172/? I/System.out: ANIMAL_TYPE_CD=0
12-02 19:37:54.048 1172-1172/? I/System.out: COUNT_NO=null
12-02 19:37:54.048 1172-1172/? I/System.out: SEENON_DTM=null
12-02 19:37:54.048 1172-1172/? I/System.out: COMMENTS_TXT=Raining
12-02 19:37:54.048 1172-1172/? I/System.out: }
12-02 19:37:54.048 1172-1172/? I/System.out: 1 {
12-02 19:37:54.048 1172-1172/? I/System.out: ID=2
12-02 19:37:54.048 1172-1172/? I/System.out: ANIMAL_TYPE_CD=0
12-02 19:37:54.048 1172-1172/? I/System.out: COUNT_NO=null
12-02 19:37:54.048 1172-1172/? I/System.out: SEENON_DTM=null
12-02 19:37:54.048 1172-1172/? I/System.out: COMMENTS_TXT=
12-02 19:37:54.048 1172-1172/? I/System.out: }
12-02 19:37:54.048 1172-1172/? I/System.out: <<<<<

Using existing database to display data in Android app

set your data path directly as a string, hope it will work

 private final static String DATABASE_PATH ="/data/data/com.yourpackagename/databases/";
public SQLiteDatabase openDatabase() throws SQLException
{ String myPath = DATABASE_PATH + "DB_NAME";myDataBase = SQLiteDatabase.openOrCreateDatabase(myPath, null, null);
return myDataBase;
}`

How to use an existing database with an Android application

NOTE:
Before trying this code, please find this line in the below code:

private static String DB_NAME ="YourDbName"; // Database name

DB_NAME here is the name of your database. It is assumed that you have a copy of the database in the assets folder, so for example, if your database name is ordersDB, then the value of DB_NAME will be ordersDB,

private static String DB_NAME ="ordersDB";

Keep the database in assets folder and then follow the below:

DataHelper class:

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;

import android.content.Context;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DataBaseHelper extends SQLiteOpenHelper {

private static String TAG = "DataBaseHelper"; // Tag just for the LogCat window
private static String DB_NAME ="YourDbName"; // Database name
private static int DB_VERSION = 1; // Database version
private final File DB_FILE;
private SQLiteDatabase mDataBase;
private final Context mContext;

public DataBaseHelper(Context context) {
super(context, DB_NAME, null, DB_VERSION);
DB_FILE = context.getDatabasePath(DB_NAME);
this.mContext = context;
}

public void createDataBase() throws IOException {
// If the database does not exist, copy it from the assets.
boolean mDataBaseExist = checkDataBase();
if(!mDataBaseExist) {
this.getReadableDatabase();
this.close();
try {
// Copy the database from assests
copyDataBase();
Log.e(TAG, "createDatabase database created");
} catch (IOException mIOException) {
throw new Error("ErrorCopyingDataBase");
}
}
}

// Check that the database file exists in databases folder
private boolean checkDataBase() {
return DB_FILE.exists();
}

// Copy the database from assets
private void copyDataBase() throws IOException {
InputStream mInput = mContext.getAssets().open(DB_NAME);
OutputStream mOutput = new FileOutputStream(DB_FILE);
byte[] mBuffer = new byte[1024];
int mLength;
while ((mLength = mInput.read(mBuffer)) > 0) {
mOutput.write(mBuffer, 0, mLength);
}
mOutput.flush();
mOutput.close();
mInput.close();
}

// Open the database, so we can query it
public boolean openDataBase() throws SQLException {
// Log.v("DB_PATH", DB_FILE.getAbsolutePath());
mDataBase = SQLiteDatabase.openDatabase(DB_FILE, null, SQLiteDatabase.CREATE_IF_NECESSARY);
// mDataBase = SQLiteDatabase.openDatabase(DB_FILE, null, SQLiteDatabase.NO_LOCALIZED_COLLATORS);
return mDataBase != null;
}

@Override
public synchronized void close() {
if(mDataBase != null) {
mDataBase.close();
}
super.close();
}

}

Write a DataAdapter class like:

import java.io.IOException;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

public class TestAdapter {

protected static final String TAG = "DataAdapter";

private final Context mContext;
private SQLiteDatabase mDb;
private DataBaseHelper mDbHelper;

public TestAdapter(Context context) {
this.mContext = context;
mDbHelper = new DataBaseHelper(mContext);
}

public TestAdapter createDatabase() throws SQLException {
try {
mDbHelper.createDataBase();
} catch (IOException mIOException) {
Log.e(TAG, mIOException.toString() + " UnableToCreateDatabase");
throw new Error("UnableToCreateDatabase");
}
return this;
}

public TestAdapter open() throws SQLException {
try {
mDbHelper.openDataBase();
mDbHelper.close();
mDb = mDbHelper.getReadableDatabase();
} catch (SQLException mSQLException) {
Log.e(TAG, "open >>"+ mSQLException.toString());
throw mSQLException;
}
return this;
}

public void close() {
mDbHelper.close();
}

public Cursor getTestData() {
try {
String sql ="SELECT * FROM myTable";
Cursor mCur = mDb.rawQuery(sql, null);
if (mCur != null) {
mCur.moveToNext();
}
return mCur;
} catch (SQLException mSQLException) {
Log.e(TAG, "getTestData >>"+ mSQLException.toString());
throw mSQLException;
}
}
}

Now you can use it like:

TestAdapter mDbHelper = new TestAdapter(urContext);
mDbHelper.createDatabase();
mDbHelper.open();

Cursor testdata = mDbHelper.getTestData();

mDbHelper.close();

EDIT: Thanks to JDx

For Android 4.1 (Jelly Bean), change:

DB_PATH = "/data/data/" + context.getPackageName() + "/databases/";

to:

DB_PATH = context.getApplicationInfo().dataDir + "/databases/";

in the DataHelper class, this code will work on Jelly Bean 4.2 multi-users.

EDIT: Instead of using hardcoded path, we can use

DB_PATH = context.getDatabasePath(DB_NAME).getAbsolutePath();

which will give us the full path to the database file and works on all Android versions

Android Studio search existing sqlite db

The basis of your search will be a query based upon the FOOD column (I believe).

e.g. SELECT * FROM dataset WHERE FOOD LIKE '%your_food%'

For example assume that your database has data such as (note only food and id columns have been populated with data) :-

Sample Image

Then the query SELECT * FROM dataset WHERE FOOD LIKE '%mash%' would result in :-

Sample Image

  • i.e. Foods with an id of 2 and 3 contain the food mash

You could run this query using the SQLiteDatabase query method. The query method returns a Cursor with the extracted data. So a method in your DatabaseHelper could be :-

public Cursor getFoodsWithProvidedFood(String provided_food) {
return this.getWritableDatabase().query(
TABLE_NAME,
null,
COL_2 + " LIKE '%" + provided_food + "%' ",
null,
null,
null,
null
);
}

Converting this into a complete but very basic App you could have :-

The Database Helper - FoodDatabaseHelper.java

public class FoodDatabaseHelper extends SQLiteOpenHelper {
public static final String DATABASE_NAME = "FoodDatabase.db";
public static final String TABLE_NAME = "dataset";
public static final String COL_1 = "ID";
public static final String COL_2 = "Food";
public static final String COL_3 = "Description";
public static final String COL_4 = "Protein";
public static final String COL_5 = "Fat";
public static final String COL_6 = "Carbohydrate";
public static final String COL_7 = "Energy";
public static final String COL_8 = "Starch";
public static final String COL_9 = "Sugar";
public static final String COL_10 = "Cholesterol";

SQLiteDatabase sqLiteDatabase; //<<<< Added

public FoodDatabaseHelper(Context context) {
super(context, DATABASE_NAME, null, 1);
sqLiteDatabase = this.getWritableDatabase(); //<<<< Amended
}

//@Override
public void onNotRecommendedCreate(SQLiteDatabase sqLiteDatabase) {
sqLiteDatabase.execSQL("create table " + TABLE_NAME +
" (ID INTEGER PRIMARY KEY AUTOINCREMENT," +
"FOOD TEXT," +
"DESCRIPTION TEXT," +
"PROTEIN BLOB," +
"FAT BLOB," +
"CARBOHYDRATE BLOB," +
"ENERGY BLOB," +
"STARCH BLOB," +
"SUGAR BLOB," +
"CHOLESTEROL BLOB)");
}

@Override
public void onCreate(SQLiteDatabase db) {
String crtsql = "CREATE TABLE IF NOT EXISTS " + TABLE_NAME +
"(" +
COL_1 + " INTEGER PRIMARY KEY, " +
COL_2 + " TEXT, " +
COL_3 + " TEXT, " +
COL_4 + " BLOB, " +
COL_5 + " BLOB, " +
COL_6 + " BLOB, " +
COL_7 + " BLOB, " +
COL_8 + " BLOB, " +
COL_9 + " BLOB, " +
COL_10 + " BLOB " +
")";
db.execSQL(crtsql);
}

@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " +TABLE_NAME);
onCreate(sqLiteDatabase);
}

public void insertFood(
String food,
String description,
byte[] protien,
byte[] fat,
byte[] carbohydrate,
byte[] energy,
byte[] starch,
byte[] sugar,
byte[] cholesterol) {
ContentValues cv = new ContentValues();
cv.put(COL_2,food);
cv.put(COL_3,description);
cv.put(COL_4,protien);
cv.put(COL_5,fat);
cv.put(COL_6,carbohydrate);
cv.put(COL_7,energy);
cv.put(COL_8,starch);
cv.put(COL_9,sugar);
cv.put(COL_10,cholesterol);
SQLiteDatabase db = this.getWritableDatabase();
db.insert(TABLE_NAME,null,cv);
}

public Cursor getFoodsWithProvidedFood(String provided_food) {
return this.getWritableDatabase().query(
TABLE_NAME,
null,
COL_2 + " LIKE '%" + provided_food + "%' ",
null,
null,
null,
null
);
}
}
  • Notes
  • It will likely cause fewer issue if you have just one place where you define table and column names.

    • hence the changed onCreate
    • although column names in SQLite are case-insensitive the Cursor's getColumnIndex method (as used in the main activity) is case-sensitive (IMO a bug).
    • AUTOINCREMENT doesn't do what it implies, rather INTEGER PRIMARY KEY itself makes a column one that increments adding AUTOINCREMENT is a special case that ensures that the id is greater at the expense of overheads. Hence AUTOINCREMENT has been removed.
  • Two additional methods have been added

    • insertFood to insert(add) data to the dataset table.
    • getFoodsWithProvidedFood as described above.

The Activity - MainActivity.java

public class MainActivity extends AppCompatActivity {

FoodDatabaseHelper foodDBHlpr;
Cursor mCsr;

@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);

foodDBHlpr = new FoodDatabaseHelper(this);
byte[] dummy = new byte[]{0,1,2,3,4,5,6,7,8,9};

// Add some data oif none exists
if (DatabaseUtils.queryNumEntries(foodDBHlpr.getWritableDatabase(),FoodDatabaseHelper.TABLE_NAME) < 1) {
foodDBHlpr.insertFood("Fish and Chips", "The English Seaside meal",
dummy, dummy, dummy, dummy, dummy, dummy, dummy);
foodDBHlpr.insertFood("Bangers and Mash", "Yummy!!",
dummy, dummy, dummy, dummy, dummy, dummy, dummy);
foodDBHlpr.insertFood("Mashed Potatoe", "Boring",
dummy, dummy, dummy, dummy, dummy, dummy, dummy);
}

// get a Cursor with the extracted foods
mCsr = foodDBHlpr.getFoodsWithProvidedFood("Mash");
// Loop Through the Cursor
while (mCsr.moveToNext()) {
Log.d("FOODFOUND","You found the food called - " + mCsr.getString(mCsr.getColumnIndex(FoodDatabaseHelper.COL_2)));
}
if (mCsr.getCount() < 1) {
Log.d("FOODFOUND","No foods found that match the search criteria.");
}
mCsr.close(); //<<<< Should always close Cursors when done with them
}
}

Result in the Log :-

03-15 21:48:21.170 1702-1702/foodsdb.so49307874_foodsdb D/FOODFOUND: You found the food called - Bangers and Mash
03-15 21:48:21.170 1702-1702/foodsdb.so49307874_foodsdb D/FOODFOUND: You found the food called - Mashed Potatoe

How to upgrade an SQLite table in app without SQLiteOpenHelper

You should have used SQLiteOpenHelper in the first place.
Anyway, I recommend that you start using SQLiteOpenHelper now.

Since your old app did not set a version (with PRAGMA user_version), the existing database file will have version 0, which is interpreted by SQLiteOpenHelper as empty, so it will call onCreate.

So your onCreate method must check if the attendance table already exists. If not, create the database from scratch; if yes, upgrade the table from version 0 to version 1.
(To add a column, use ALTER TABLE.)

All future upgrades can then use the normal onUpgrade mechanism.

how to use predefined sqlite database

Follow the instructions from this blog

It will walk you through all the steps you need to use to get a pre-created db into your app.

EDIT

You didn't mention that your db was large in the initial post. In that case you might want to try android-sqlite-asset-helper

I don't see where it says it, but I know I've seen Mark from CommonsWare comment that it will transfer any size file for you. You zip up the db, put it in your assets/databases folder, and follow their directions to code your db class and you should be good to go.

SQLite database for Android App

You can use SQlite3 database as an internal storage in android.For that you can use SQLite3 Manager.You can get it here http://sqlitemanager.en.softonic.com/. You have to extend SQLiteOpenHelper class to use it.For more detail to use database in android please refer this link http://www.androidhive.info/2011/11/android-sqlite-database-tutorial/.



Related Topics



Leave a reply



Submit