4.5.1 示例代码

4.5.1.1 创建/操作数据库

在 Android 应用中处理数据库时，可以通过使用SQLiteOpenHelper [10] 来实现数据库文件的适当安排和访问权限设置（拒绝其他应用访问的设置）。 下面是一个简单的应用示例，它在启动时创建数据库，并通过 UI 执行搜索/添加/更改/删除数据。 示例代码完成了 SQL 注入的防范，来避免来自外部的输入执行不正确的 SQL。

[10] 对于文件存储，可以将绝对文件路径指定为SQLiteOpenHelper构造函数的第二个参数（名称）。 因此，如果指定了 SD 卡路径，则需要注意，存储的文件可以被其他应用读取和写入。

4.5.1.md - 图1

  1. SQLiteOpenHelper应该用于创建数据库。

  2. 使用占位符。

  3. 根据应用要求验证输入值。

SampleDbOpenHelper.java

  1. package org.jssec.android.sqlite;
  3. import android.content.Context;
  4. import android.database.SQLException;
  5. import android.database.sqlite.SQLiteDatabase;
  6. import android.database.sqlite.SQLiteOpenHelper;
  7. import android.util.Log;
  8. import android.widget.Toast;
  10. public class SampleDbOpenHelper extends SQLiteOpenHelper {
  12.     private SQLiteDatabase mSampleDb; //Database to store the data to be handled
  14.     public static SampleDbOpenHelper newHelper(Context context) {
  15.         //*** POINT 1 *** SQLiteOpenHelper should be used for database creation.
  16.         return new SampleDbOpenHelper(context);
  17.     }
  19.     public SQLiteDatabase getDb() {
  20.         return mSampleDb;
  21.     }
  23.     //Open DB by Writable mode
  24.     public void openDatabaseWithHelper() {
  25.         try {
  26.             if (mSampleDb != null && mSampleDb.isOpen()) {
  27.                 if (!mSampleDb.isReadOnly())// Already opened by writable mode
  28.                     return;
  29.                 mSampleDb.close();
  30.             }
  31.             mSampleDb = getWritableDatabase(); //It's opened here.
  32.         } catch (SQLException e) {
  33.             //In case fail to construct database, output to log
  34.             Log.e(mContext.getClass().toString(), mContext.getString(R.string.DATABASE_OPEN_ERROR_MESSAGE));
  35.             Toast.makeText(mContext, R.string.DATABASE_OPEN_ERROR_MESSAGE, Toast.LENGTH_LONG).show();
  36.         }
  37.     }
  39.     //Open DB by ReadOnly mode.
  40.     public void openDatabaseReadOnly() {
  41.         try {
  42.             if (mSampleDb != null && mSampleDb.isOpen()) {
  43.                 if (mSampleDb.isReadOnly())// Already opened by ReadOnly.
  44.                     return;
  45.                 mSampleDb.close();
  46.             }
  47.             SQLiteDatabase.openDatabase(mContext.getDatabasePath(CommonData.DBFILE_NAME).getPath(), null, SQLiteDatabase.OPEN_READONLY);
  48.         } catch (SQLException e) {
  49.             //In case failed to construct database, output to log
  50.             Log.e(mContext.getClass().toString(), mContext.getString(R.string.DATABASE_OPEN_ERROR_MESSAGE));
  51.             Toast.makeText(mContext, R.string.DATABASE_OPEN_ERROR_MESSAGE, Toast.LENGTH_LONG).show();
  52.         }
  53.     }
  55.     //Database Close
  56.     public void closeDatabase() {
  57.         try {
  58.             if (mSampleDb != null && mSampleDb.isOpen()) {
  59.                 mSampleDb.close();
  60.             }
  61.         } catch (SQLException e) {
  62.             //In case failed to construct database, output to log
  63.             Log.e(mContext.getClass().toString(), mContext.getString(R.string.DATABASE_CLOSE_ERROR_MESSAGE));
  64.             Toast.makeText(mContext, R.string.DATABASE_CLOSE_ERROR_MESSAGE, Toast.LENGTH_LONG).show();
  65.         }
  66.     }
  68.     //Remember Context
  69.     private Context mContext;
  70.     //Table creation command
  71.     private static final String CREATE_TABLE_COMMANDS
  72.         = "CREATE TABLE " + CommonData.TABLE_NAME + " ("
  73.         + "_id INTEGER PRIMARY KEY AUTOINCREMENT, "
  74.         + "idno INTEGER UNIQUE, "
  75.         + "name VARCHAR(" + CommonData.TEXT_DATA_LENGTH_MAX + ") NOT NULL, "
  76.         + "info VARCHAR(" + CommonData.TEXT_DATA_LENGTH_MAX + ")"
  77.         + ");";
  79.     public SampleDbOpenHelper(Context context) {
  80.         super(context, CommonData.DBFILE_NAME, null, CommonData.DB_VERSION);
  81.         mContext = context;
  82.     }
  84.     @Override
  85.     public void onCreate(SQLiteDatabase db) {
  86.         try {
  87.             db.execSQL(CREATE_TABLE_COMMANDS); //Execute DB construction command
  88.         } catch (SQLException e) {
  89.             //In case failed to construct database, output to log
  90.             Log.e(this.getClass().toString(), mContext.getString(R.string.DATABASE_CREATE_ERROR_MESSAGE));
  91.         }
  92.     }
  94.     @Override
  95.     public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) {
  96.         // It's to be executed when database version up. Write processes like data transition.
  97.     }
  98. }

DataSearchTask.java（SQLite 数据库项目）

  1. package org.jssec.android.sqlite.task;
  3. import org.jssec.android.sqlite.CommonData;
  4. import org.jssec.android.sqlite.DataValidator;
  5. import org.jssec.android.sqlite.MainActivity;
  6. import org.jssec.android.sqlite.R;
  7. import android.database.Cursor;
  8. import android.database.SQLException;
  9. import android.database.sqlite.SQLiteDatabase;
  10. import android.os.AsyncTask;
  11. import android.util.Log;
  13. //Data search task
  14. public class DataSearchTask extends AsyncTask<String, Void, Cursor> {
  16.     private MainActivity mActivity;
  17.     private SQLiteDatabase mSampleDB;
  19.     public DataSearchTask(SQLiteDatabase db, MainActivity activity) {
  20.         mSampleDB = db;
  21.         mActivity = activity;
  22.     }
  24.     @Override
  25.     protected Cursor doInBackground(String... params) {
  26.         String idno = params[0];
  27.         String name = params[1];
  28.         String info = params[2];
  29.         String cols[] = {"_id", "idno","name","info"};
  30.         Cursor cur;
  31.         //*** POINT 3 *** Validate the input value according the application requirements.
  32.         if (!DataValidator.validateData(idno, name, info)){
  33.             return null;
  34.         }
  35.         //When all parameters are null, execute all search
  36.         if ((idno == null || idno.length() == 0) &&
  37.             (name == null || name.length() == 0) &&
  38.             (info == null || info.length() == 0) ) {
  39.             try {
  40.                 cur = mSampleDB.query(CommonData.TABLE_NAME, cols, null, null, null, null, null);
  41.             } catch (SQLException e) {
  42.                 Log.e(DataSearchTask.class.toString(), mActivity.getString(R.string.SEARCHING_ERROR_MESSAGE));
  43.                 return null;
  44.             }
  45.             return cur;
  46.         }
  47.         //When No is specified, execute searching by No
  48.         if (idno != null && idno.length() > 0) {
  49.             String selectionArgs[] = {idno};
  50.             try {
  51.                 //*** POINT 2 *** Use place holder.
  52.                 cur = mSampleDB.query(CommonData.TABLE_NAME, cols, "idno = ?", selectionArgs, null, null, null);
  53.             } catch (SQLException e) {
  54.                 Log.e(DataSearchTask.class.toString(), mActivity.getString(R.string.SEARCHING_ERROR_MESSAGE));
  55.                 return null;
  56.             }
  57.             return cur;
  58.         }
  59.         //When Name is specified, execute perfect match search by Name
  60.         if (name != null && name.length() > 0) {
  61.             String selectionArgs[] = {name};
  62.             try {
  63.                 //*** POINT 2 *** Use place holder.
  64.                 cur = mSampleDB.query(CommonData.TABLE_NAME, cols, "name = ?", selectionArgs, null, null, null);
  65.             } catch (SQLException e) {
  66.                 Log.e(DataSearchTask.class.toString(), mActivity.getString(R.string.SEARCHING_ERROR_MESSAGE));
  67.                 return null;
  68.             }
  69.             return cur;
  70.         }
  71.         //Other than above, execute partly match searching with the condition of info.
  72.         String argString = info.replaceAll("@", "@@"); //Escape $ in info which was received as input.
  73.         argString = argString.replaceAll("%", "@%"); //Escape % in info which was received as input.
  74.         argString = argString.replaceAll("_", "@_"); //Escape _ in info which was received as input.
  75.         String selectionArgs[] = {argString};
  76.         try {
  77.             //*** POINT 2 *** Use place holder.
  78.             cur = mSampleDB.query(CommonData.TABLE_NAME, cols, "info LIKE '%' || ? || '%' ESCAPE '@'", selectionArgs, null, null, null);
  79.         } catch (SQLException e) {
  80.             Log.e(DataSearchTask.class.toString(), mActivity.getString(R.string.SEARCHING_ERROR_MESSAGE));
  81.             return null;
  82.         }
  83.         return cur;
  84.     }
  86.     @Override
  87.     protected void onPostExecute(Cursor resultCur) {
  88.         mActivity.updateCursor(resultCur);
  89.     }
  90. }

DataValidator.java

  1. package org.jssec.android.sqlite;
  3. public class DataValidator {
  5.     //Validate the Input value
  6.     //validate numeric characters
  7.     public static boolean validateNo(String idno) {
  8.         //null and blank are OK
  9.         if (idno == null || idno.length() == 0) {
  10.             return true;
  11.         }
  12.         //Validate that it's numeric character.
  13.         try {
  14.                     if (!idno.matches("[1-9][0-9]*")) {
  15.                 //Error if it's not numeric value
  16.                 return false;
  17.             }
  18.         } catch (NullPointerException e) {
  19.             //Detected an error
  20.             return false;
  21.         }
  22.         return true;
  23.     }
  25.     // Validate the length of a character string
  26.     public static boolean validateLength(String str, int max_length) {
  27.         //null and blank are OK
  28.         if (str == null || str.length() == 0) {
  29.             return true;
  30.         }
  31.         //Validate the length of a character string is less than MAX
  32.         try {
  33.             if (str.length() > max_length) {
  34.                 //When it's longer than MAX, error
  35.                 return false;
  36.             }
  37.         } catch (NullPointerException e) {
  38.             //Bug
  39.             return false;
  40.         }
  41.         return true;
  42.     }
  44.     // Validate the Input value
  45.     public static boolean validateData(String idno, String name, String info) {
  46.         if (!validateNo(idno)) {
  47.             return false;
  48.         }
  49.         if (!validateLength(name, CommonData.TEXT_DATA_LENGTH_MAX)) {
  50.             return false;
  51.         }else if(!validateLength(info, CommonData.TEXT_DATA_LENGTH_MAX)) {
  52.             return false;
  53.         }
  54.         return true;
  55.     }
  56. }