placing existing sqlite database and reading
you should put it in the assets folder.
This way you can make sure it will be attached to your apk.
this is how you can copy the database file from the assets folder to your working directory:
private void copyDataBase() throws IOException{
//Open your local db as the input stream
InputStream myInput = myContext.getAssets().open(DB_NAME);
// Path to the just created empty db
String outFileName = DB_PATH + DB_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();
}
now to read the database from the directory:
public void openDataBase() throws SQLException{
//Open the database
String myPath = DB_PATH + DB_NAME;
myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
}
How to access an existing sqlite database in Android?
Take a look at the documentation for android.database.sqlite.SQLiteDatabase.
In particular, there's an openDatabase() command that will access a database given a file path.
SQLiteDatabase db = SQLiteDatabase.openDatabase(path, null, 0);
Just specify the path to your database as the path variable, and it should work.
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
Insert, Delete, Retrieve data from and into existing sqlite database in assests folder
Issue 1 - Not calling the DBOperator copyDB method.
You need to call the copyDB method for the Database to be copied from the assets folder. Otherwise an empty database will be created.
This should be done before an attempt is made to get a DBOperator Instance. A convenient place for this call would be immediately after the Activity's ContentView is set e.g.
public class NewpActivity extends AppCompatActivity {
String PaFirstName, PaLastName, PaDOB, PaGender, PaContact, PaStreetAPT, PaCity, PaState, country, PaPincode, PaInsurance;
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
getSupportActionBar().setDisplayHomeAsUpEnabled(true);
setContentView(R.layout.activity_newp);
Button signUpBtn = (Button) findViewById(R.id.checkout_btn);
try {
DBOperator.copyDB(this);
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException("DB Copy Failed. Issuing runtime exception");
}
signUpBtn.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
//Running method for updating string variables from input boxes
//getValues();
//DBOperator.getInstance().execSQL(SQLCommand.NEW_USER, getArgs());
Toast.makeText(getBaseContext(), "Checkout successfully", Toast.LENGTH_SHORT).show();
}
});
}
}
Note before running the corrected code you should delete the App's data or uninstall the App so that the database is deleted (otherwise the copyDB method will not copy the database as it exists.)
Lines commented out for convenience.
Issue 2 - (not an error)
It is much better to not hard code the database path but to retrieve it via the_context.getDatabasePath(database_name).getPath method.
As such it is suggested that you change :-
String path = DBConstant.DATABASE_PATH + "/" + DBConstant.DATABASE_FILE;
to instead be :-
String path = context.getDatabasePath(DBConstant.DATABASE_FILE).getPath();
Additional Stuff
- At first it might be an idea to confirm that the database contains the expected table(s), adding code (in the Activit's onCreate method after everything else)
such as "-
public class NewpActivity extends AppCompatActivity {
String PaFirstName, PaLastName, PaDOB, PaGender, PaContact, PaStreetAPT, PaCity, PaState, country, PaPincode, PaInsurance;
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
getSupportActionBar().setDisplayHomeAsUpEnabled(true);
setContentView(R.layout.activity_newp);
Button signUpBtn = (Button) findViewById(R.id.checkout_btn);
try {
DBOperator.copyDB(this);
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException("DB Copy Failed. Issuing runtime exception");
}
signUpBtn.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
//Running method for updating string variables from input boxes
//getValues();
//DBOperator.getInstance().execSQL(SQLCommand.NEW_USER, getArgs());
Toast.makeText(getBaseContext(), "Checkout successfully", Toast.LENGTH_SHORT).show();
}
});
//<<<<<<<<<< ADDED TO CONFIRM DATABASE IS AS EXPECTED
DBOperator checkit = DBOperator.getInstance();
Cursor csr = checkit.execQuery("SELECT * FROM sqlite_master");
while (csr.moveToNext()) {
Log.d(
"DBINFO",
"Item with a name of " + csr.getString(csr.getColumnIndex("name")) +
" and a type of " + csr.getString(csr.getColumnIndex("type")) +
" found in the Database."
);
}
}
}
Note the above is an example of how you can get data from a table and process it and it uses your execQuery method (which works).
- Should the database not have been copied to the assets folder and the code above was used then you would get an error along the lines of
:-
11-12 10:17:01.494 2025-2025/? W/System.err: java.io.FileNotFoundException: fail.db
11-12 10:17:01.494 2025-2025/? W/System.err: at android.content.res.AssetManager.openAsset(Native Method)
11-12 10:17:01.494 2025-2025/? W/System.err: at android.content.res.AssetManager.open(AssetManager.java:315)
11-12 10:17:01.494 2025-2025/? W/System.err: at android.content.res.AssetManager.open(AssetManager.java:289)
11-12 10:17:01.494 2025-2025/? W/System.err: at test.activity.test.DBOperator.copyDB(DBOperator.java:54)
11-12 10:17:01.494 2025-2025/? W/System.err: at test.activity.test.NewpActivity.onCreate(NewpActivity.java:21)
11-12 10:17:01.494 2025-2025/? W/System.err: at android.app.Activity.performCreate(Activity.java:5008)
11-12 10:17:01.494 2025-2025/? W/System.err: at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1079)
11-12 10:17:01.494 2025-2025/? W/System.err: at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2023)
11-12 10:17:01.494 2025-2025/? W/System.err: at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2084)
11-12 10:17:01.494 2025-2025/? W/System.err: at android.app.ActivityThread.access$600(ActivityThread.java:130)
11-12 10:17:01.494 2025-2025/? W/System.err: at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1195)
11-12 10:17:01.494 2025-2025/? W/System.err: at android.os.Handler.dispatchMessage(Handler.java:99)
11-12 10:17:01.494 2025-2025/? W/System.err: at android.os.Looper.loop(Looper.java:137)
11-12 10:17:01.494 2025-2025/? W/System.err: at android.app.ActivityThread.main(ActivityThread.java:4745)
11-12 10:17:01.494 2025-2025/? W/System.err: at java.lang.reflect.Method.invokeNative(Native Method)
11-12 10:17:01.494 2025-2025/? W/System.err: at java.lang.reflect.Method.invoke(Method.java:511)
11-12 10:17:01.494 2025-2025/? W/System.err: at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:786)
11-12 10:17:01.494 2025-2025/? W/System.err: at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:553)
11-12 10:17:01.494 2025-2025/? W/System.err: at dalvik.system.NativeStart.main(Native Method)
11-12 10:17:01.494 2025-2025/? D/AndroidRuntime: Shutting down VM
11-12 10:17:01.494 2025-2025/? W/dalvikvm: threadid=1: thread exiting with uncaught exception (group=0xa62a3288)
11-12 10:17:01.494 2025-2025/? E/AndroidRuntime: FATAL EXCEPTION: main
java.lang.RuntimeException: Unable to start activity ComponentInfo{test.activity.test/test.activity.test.NewpActivity}: java.lang.RuntimeException: DB Copy Failed. Issuing runtime exception
at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2059)
at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2084)
at android.app.ActivityThread.access$600(ActivityThread.java:130)
at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1195)
at android.os.Handler.dispatchMessage(Handler.java:99)
at android.os.Looper.loop(Looper.java:137)
at android.app.ActivityThread.main(ActivityThread.java:4745)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:511)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:786)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:553)
at dalvik.system.NativeStart.main(Native Method)
Caused by: java.lang.RuntimeException: DB Copy Failed. Issuing runtime exception
at test.activity.test.NewpActivity.onCreate(NewpActivity.java:24)
at android.app.Activity.performCreate(Activity.java:5008)
at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1079)
at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2023)
at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2084)
at android.app.ActivityThread.access$600(ActivityThread.java:130)
at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1195)
at android.os.Handler.dispatchMessage(Handler.java:99)
at android.os.Looper.loop(Looper.java:137)
at android.app.ActivityThread.main(ActivityThread.java:4745)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:511)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:786)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:553)
at dalvik.system.NativeStart.main(Native Method)
- The failure was forced by changing the database name to fail.db
Note that there are two exceptions the original caught exception () and then the runtime exception that was issued (you have to have a try/catch as the copyDB method throws an exception so might as well throw a runtime exception)
- A good run (that copies the database or doesn't if it already exists) would log the following output (perhaps more if you have more tables or other items wuch an indexes, triggers, views)
:-
11-12 09:59:08.910 1917-1917/test.activity.test D/DBINFO: Item with a name of patient and a type of table found in the Database.
11-12 09:59:08.910 1917-1917/test.activity.test D/DBINFO: Item with a name of android_metadata and a type of table found in the Database.
I also want to add Retrieve data (on button click) from database on
basis of a search box value provided by user functionality in this
application.
That you should now try doing, if you have issues then you should ask a new question. You'd need to decide how you would want to do it. Would you want to search on various columns? would you want the results to change as you typed or would you want to a click to initiate the search. Perhaps a drop-down selector (Spinner) could be used for some columns e.g. say for Gender when there will be a specific set of values.
Fetch data from existing sqlite database
The onCreate and onUpgrade methods will be empty since you already have the database. There is a great tutorial on how to achieve this here.
You could then access the database like such (example):
public ArrayList<String> getValues(String table) {
ArrayList<String> values = new ArrayList<String>();
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.rawQuery("SELECT value FROM " + table, null);
if(cursor.moveToFirst()) {
do {
values.add(cursor.getString(cursor.getColumnIndex("value")));
}while(cursor.moveToNext());
}
cursor.close();
db.close();
return values;
}
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) :-
Then the query SELECT * FROM dataset WHERE FOOD LIKE '%mash%' would result in :-
- 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
getColumnIndexmethod (as used in the main activity) is case-sensitive (IMO a bug). AUTOINCREMENTdoesn't do what it implies, ratherINTEGER PRIMARY KEYitself makes a column one that increments addingAUTOINCREMENTis a special case that ensures that the id is greater at the expense of overheads. HenceAUTOINCREMENThas been removed.
Two additional methods have been added
insertFoodto insert(add) data to the dataset table.getFoodsWithProvidedFoodas 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
Related Topics
Android Alarmmanager Setexact() Is Not Exact
Android: Converting Color Image to Grayscale
Changing Values from Cursor Using Simplecursoradapter
Isvalidfragment Android API 19
Creating a Seterror() for The Spinner
How to Get a Button's Height to Match Another Element's Height
Selecting Contact from Autocomplete Textview
Get Application Installed Date on Android
How to Replace a String for a Buildvariant with Gradle in Android Studio
Httpurlconnection.Openconnection Fails Second Time
How to Programmatically Pair a Bluetooth Device on Android
How to Overlap Items in Linearlayoutmanager - Recyclerview (Like Stacking Cards)