Android learning Demo (16) Use SqliteDatabase with existing data in Android

When using a database in Android, in some cases, you need to import a database with data, and then call it in the program to implement our functions.

For example, in a small demo of querying the ownership of a credit card, enter the first six digits of the credit card in the input box (these six digits determine the ownership of the credit card), and click the query button, the corresponding bank can be displayed.

The data structure in the card table is as follows:

Let's take a look at how this function is implemented in the program.

1) Asset folder

Put our database under the Asset Folder:

2) create a CreditCardDb class.

In this class, we need to implement the following functions:

2.1) copy the database from the assets folder to the storage of the mobile phone, that is,/data/package/databases /.

public void copyDatabase(){String databasePath = mContext.getApplicationContext().getFilesDir().getAbsolutePath();databasePath = databasePath.substring(0, databasePath.lastIndexOf("/")) + "/databases";mDatabasePath = databasePath + "/" + DB_NAME;File dir = new File(databasePath);if(!dir.exists()){dir.mkdir();}File file = new File(mDatabasePath);if(!file.exists() || file.length() == 0){try {InputStream is = mContext.getAssets().open("database/"+DB_NAME);FileOutputStream fos = new FileOutputStream(mDatabasePath);byte[] buffer = new byte[8192];int count = 0;while ((count = is.read(buffer)) > 0) {fos.write(buffer, 0, count);}fos.close();is.close();} catch (IOException e) {e.printStackTrace();}}}

In the above method,

A) First, find the location where the database is stored in the memory, under "/data/package name/databases/", and create the corresponding file, as shown below:

B) Use the getAssets method of the Context class to obtain the AssetManager, call its open method, open the database in the corresponding path, and write it to the file found in the previous step.

2.2) create two methods, one for opening the database and the other for closing the database, as shown below:

private void openDatabase(){Log.v(TAG, "Open DataBase : " + mDatabasePath);mCreditCardDb = SQLiteDatabase.openDatabase(mDatabasePath, null,SQLiteDatabase.NO_LOCALIZED_COLLATORS);}private void closeDatabase() {SQLiteDatabase.releaseMemory();mCreditCardDb.close();}

2.3) the rest is the operation on the database table. Find the bank according to the first 6 digits of the card number:

public String getBankByCreditCardNumber(String creditCardNumber){String sql = "select * from t_bank where card_bin = ? ";String bankName = "";openDatabase();Cursor cursor = mCreditCardDb.rawQuery(sql, new String[] {creditCardNumber});if(cursor == null){Log.e(TAG, "Cursor is null.");}else if(!cursor.moveToFirst()){Log.e(TAG, "Cursor has no records.");}else{bankName = cursor.getString(cursor.getColumnIndex(COL_BANK_NAME));}closeDatabase();return bankName;}

3) In the onCreate method of MainActivity, MainActivity constructs the CreditCardDb Class Object and calls its copyDatabase method as follows:

mCreditCardDb = new CreditCardDb(this);mCreditCardDb.copyDatabase();

Create an AsyncTask, query the data in the database based on the value entered by EditText, and display the query results on TextView, as shown below:

class CheckCreditCard extends AsyncTask
 
  {protected void onPostExecute(String result){tvBankName.setText(result);}@Overrideprotected String doInBackground(String... params) {String creditCardNumber = params[0];String bankName = "";if(creditCardNumber.length() >= 6){bankName = mCreditCardDb.getBankByCreditCardNumber(creditCardNumber.substring(0,6));}return bankName;}}
 

Here is a simple example of how to use an existing data database and download the source code. End!