请选择 进入手机版 | 继续访问电脑版

技术控

    今日:53| 主题:54663
收藏本版 (1)
最新软件应用技术尽在掌握

[其他] Using a simple SQLite database in your Android app

[复制链接]
素颜马尾好菇凉 发表于 2016-10-6 14:23:28
342 7

立即注册CoLaBug.com会员,免费获得投稿人的专业资料,享用更多功能,玩转个人品牌!

您需要 登录 才可以下载或查看,没有帐号?立即注册

x

Using a simple SQLite database in your Android app

Using a simple SQLite database in your Android app-1-技术控-Android,management,different,published,available

   I recently published a tutorial describing the different available methods to store data locally in an Android app . However, due to article length constraints, I could not adequately cover creating and using an SQLite database for data persistence. In this tutorial, I delve into how to use an SQLite database in more detail.
  What is SQLite.

   SQLite is a relational database management system, similar to Oracle, MySQL, PostgreSQL and SQL Server. It implements most of the SQL standard, but unlike the four database engines mentioned above, it is not a client-server database engine. Rather, it is embedded into the end program. What this means is that you can bundle a SQLite database with your application, and get access to all the power of a relational database within your application. SQLite is native to both Android and iOS, and every app can create and use an SQLite database if they so desire. In fact, in Android, device contacts, and media are stored and referenced using SQLite databases. An exciting tidbit of information is that SQLite is the most used database engine in the world, and quite possibly the most widely deployed software ever . To read more about SQLite databases, visit the SQLite web site .
  Preparation

   Regular readers of this series will notice that we’ve recently begun using the Android data binding techniques for tutorials. If you are not familiar with the concept, check out my previous article discussingAndroid data binding. Also, we use recyclerview to display lists, as alsodiscussed previously. You may want to read both if you aren’t familiar with the concepts.
  To enable data binding in your app, you must add the following to your app build.gradle file
  1. dataBinding.enabled = true
复制代码
Also, to use both recyclerview and cardview to display lists, you must include the relevant libraries to the dependencies section in your app build.gradle file (24.2.1 was the current library version at the time of writing).
  1. dependencies {
  2.         ...
  3.     compile 'com.android.support:design:24.2.1'
  4.     compile 'com.android.support:cardview-v7:24.2.1'
  5. }
复制代码
Finally, some SQL experience will be very helpful, although you will still be able to follow the tutorial without previous experience with SQL. However, to unlock the full possibilities of using an SQLite database, you must study SQL syntax.
   

Using a simple SQLite database in your Android app

Using a simple SQLite database in your Android app-2-技术控-Android,management,different,published,available
   See also: Data Binding in Android     3     Sample app description

  For the sample app, we are going to create two tables, an Employer table and an Employee table. The Employee table will contain a foreign key reference to the Employer table, and we will show how to insert, select, update and delete rows from tables. We will also demonstrate how easy it can be to display items selected from an SQLite database in a RecyclerView (list) and in a Spinner.
  We’ve got a MainActivity, from which we can navigate to either the EmployerActivity (for operating on the Employer table), or the EmployeeActivity (for operating on the Employee table).
   

Using a simple SQLite database in your Android app

Using a simple SQLite database in your Android app-3-技术控-Android,management,different,published,available

  SQLite database storage classes

  Storage classes refer to how stuff is stored within the database. SQLite databases store values in one of five possible storage classes:
  
       
  • NULL – For null value.   
  • INTEGER – For integers containing as much as 8 bytes (thats from byte to long).   
  • REAL – Numbers with floating point.   
  • TEXT – Text strings, stored using the database encoding (UTF-8 or UTF-16).   
  • BLOB – Binary data, stored exactly as input.  
  With this in mind, lets create some tables.
  Define your tables

  Since the SQLite database is local to your application, you will have to ensure your app creates database tables and drops them as needed. You have to maintain your database through code. Let’s begin by creating the Employer table first, along with the EmployerActivity. It’s good practice to abstract your SQLite database creation logic into a class. This helps with maintainability. We call our class SampleDBContract.
  1. public final class SampleDBContract {
  2.     private SampleDBContract() {
  3.     }
  4.     public static class Employer implements BaseColumns {
  5.         public static final String TABLE_NAME = "employer";
  6.         public static final String COLUMN_NAME = "name";
  7.         public static final String COLUMN_DESCRIPTION = "description";
  8.         public static final String COLUMN_FOUNDED_DATE = "date";
  9.         public static final String CREATE_TABLE = "CREATE TABLE IF NOT EXISTS " +
  10.                 TABLE_NAME + " (" +
  11.                 _ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
  12.                 COLUMN_NAME + " TEXT, " +
  13.                 COLUMN_DESCRIPTION + " TEXT, " +
  14.                 COLUMN_FOUNDED_DATE + " INTEGER" + ")";
  15.     }
  16. }
复制代码
We define a private constructor for SampleDBContract so that it won’t be accidentally instantiated, and then we create a class to represent the Employer table. Note that Employer class implements the BaseColumns interface. The BaseColumns interface provides two very useful columns to our table. These are the _ID column, which will be auto incremented whenever a new row is inserted in the table, and a _COUNT column, which can be used by ContentProviders to return a count of the number of records returned from a query. We don’t require the _COUNT column. The CREATE_TABLE string, compiles to the following SQL statement:
  1. CREATE TABLE IF NOT EXISTS employer (_id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, description TEXT, date INTEGER)
复制代码
So far, we have simply defined the Employer table schema.
   

Using a simple SQLite database in your Android app

Using a simple SQLite database in your Android app-4-技术控-Android,management,different,published,available
   See also: How to build an image gallery app – full tutorial with code     6     Create database with SQLiteOpenHelper

  The easiest way to manage database creation and versioning, is to create a subclass of SQLiteOpenHelper. SQLiteOpenHelper will ease the management of your SQLite database tremendously, by opening databases when needed, creating databases if they do not exist as well as upgrading or downgrading as necessary. You only have to override the onCreate() and onUpgrade() methods, to specify the actions you need performed to create and/or update the database.
  1. public class SampleDBSQLiteHelper extends SQLiteOpenHelper {
  2.     private static final int DATABASE_VERSION = 1;
  3.     public static final String DATABASE_NAME = "sample_database";
  4.     public SampleDBSQLiteHelper(Context context) {
  5.         super(context, DATABASE_NAME, null, DATABASE_VERSION);
  6.     }
  7.     @Override
  8.     public void onCreate(SQLiteDatabase sqLiteDatabase) {
  9.         sqLiteDatabase.execSQL(SampleDBContract.Employer.CREATE_TABLE);
  10.     }
  11.     @Override
  12.     public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
  13.         sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + SampleDBContract.Employer.TABLE_NAME);
  14.         onCreate(sqLiteDatabase);
  15.     }
  16. }
复制代码
It is that easy. The database version starts at 1, and we give our SQLite database a name (sample_database). The constructor simply calls the super class’ constructor, with the database name and version. In onCreate, we tell the SQLiteDatabase object to execute the Employer CREATE_TABLE SQL statement. Finally, onUpgrade, we drop the Employer table and re create it. In your case, you may wish to run ALTER statements or whatever complex steps your app requires to move add/remove the necessary columns and tables.
  Insert data into your SQLite database

  The Employer table has three main columns, the name, description and founded_date columns. Clicking the save button calls the saveToDB() method.
   

Using a simple SQLite database in your Android app

Using a simple SQLite database in your Android app-5-技术控-Android,management,different,published,available

  Within saveToDB(), we get a reference to a SQLiteDatabase object, using SQLiteOpenHelper’s getWritableDatabase() method. This method will create the database, if it doesn’t yet exist, or open it, if it has already been created. getWritableDatabase returns an SQLiteDatabase object, which allows read/write access.
  1. private void saveToDB() {
  2.         SQLiteDatabase database = new SampleDBSQLiteHelper(this).getWritableDatabase();
  3.         ContentValues values = new ContentValues();
  4.         values.put(SampleDBContract.Employer.COLUMN_NAME, binding.nameEditText.getText().toString());
  5.         values.put(SampleDBContract.Employer.COLUMN_DESCRIPTION, binding.descEditText.getText().toString());
  6.         try {
  7.             Calendar calendar = Calendar.getInstance();
  8.             calendar.setTime((new SimpleDateFormat("dd/MM/yyyy")).parse(
  9.                     binding.foundedEditText.getText().toString()));
  10.             long date = calendar.getTimeInMillis();
  11.             values.put(SampleDBContract.Employer.COLUMN_FOUNDED_DATE, date);
  12.         }
  13.         catch (Exception e) {
  14.             Log.e(TAG, "Error", e);
  15.             Toast.makeText(this, "Date is in the wrong format", Toast.LENGTH_LONG).show();
  16.             return;
  17.         }
  18.         long newRowId = database.insert(SampleDBContract.Employer.TABLE_NAME, null, values);
  19.         Toast.makeText(this, "The new Row Id is " + newRowId, Toast.LENGTH_LONG).show();
  20.     }
复制代码
There are four important things to note in the code snippet above:
  
       
  • We get a SQLiteDatabase object that permits write access to the database.   
  • Values to be stored in the database are placed in a ContentValue object, with the column name as the key.   
  • We put Date in the ContentValue object as a long, which will translate to SQLite database storage class INTEGER. You can actually save Date as a string if you please, but we use long so that we can easily perform greater than and/or less than comparisons while querying the database.   
  • Inserting a row in the database using database.insert() method returns the row id.  
  Select data from your SQLite database

  Similar to the getWritableDatabase() method above, we can call the SQLiteOpenHelper object’s getReadableDatabase() to get an SQLiteDatabase object that can be used for read access to the database. It is worth mentioning that the SQLiteDatabase object returned by getReadableDatabase() is the exact same read/write database returned by getWritableDatabase(), except if there is a constraint such as the file system containing the database being full, that forces the database to open read only.
  The readFromDB method is going to query the database, and return all rows from the Employer table where the Employer name matches part or all of the value in the nameEditText, and the same with the description, and where the company was founded after the date input in the Found Date EditText.
  1. private void readFromDB() {
  2.         String name = binding.nameEditText.getText().toString();
  3.         String desc = binding.descEditText.getText().toString();
  4.         long date = 0;
  5.         try {
  6.             Calendar calendar = Calendar.getInstance();
  7.             calendar.setTime((new SimpleDateFormat("dd/MM/yyyy")).parse(
  8.                     binding.foundedEditText.getText().toString()));
  9.             date = calendar.getTimeInMillis();
  10.         }
  11.         catch (Exception e) {}
  12.         SQLiteDatabase database = new SampleDBSQLiteHelper(this).getReadableDatabase();
  13.         String[] projection = {
  14.                 SampleDBContract.Employer._ID,
  15.                 SampleDBContract.Employer.COLUMN_NAME,
  16.                 SampleDBContract.Employer.COLUMN_DESCRIPTION,
  17.                 SampleDBContract.Employer.COLUMN_FOUNDED_DATE
  18.         };
  19.         String selection =
  20.                 SampleDBContract.Employer.COLUMN_NAME + " like ? and " +
  21.                         SampleDBContract.Employer.COLUMN_FOUNDED_DATE + " > ? and " +
  22.                         SampleDBContract.Employer.COLUMN_DESCRIPTION + " like ?";
  23.         String[] selectionArgs = {"%" + name + "%", date + "", "%" + desc + "%"};
  24.         Cursor cursor = database.query(
  25.                 SampleDBContract.Employer.TABLE_NAME,     // The table to query
  26.                 projection,                               // The columns to return
  27.                 selection,                                // The columns for the WHERE clause
  28.                 selectionArgs,                            // The values for the WHERE clause
  29.                 null,                                     // don't group the rows
  30.                 null,                                     // don't filter by row groups
  31.                 null                                      // don't sort
  32.         );
  33.         Log.d(TAG, "The total cursor count is " + cursor.getCount());
  34.         binding.recycleView.setAdapter(new SampleRecyclerViewCursorAdapter(this, cursor));
  35.     }
复制代码
In the final query method above, projection is a String array, representing the columns we want to fetch, selection is a String representation of the SQL WHERE clause, formatted such that the ‘?’ character will be replaced by arguments in the selectionArgs String array. You can also group, filter and/or sort the query results, none of which we do here. Inserting data into an SQLite database using the method above protects against SQL injection.
  Take note of the object returned by the query. A Cursor object. In the next section, we are going to show how easy it is to display the results of a Cursor using RecyclerView.
  Display Cursor contents in a RecyclerView

  A Cursor provides random access to the result set returned by a database query. This means that you can access values at any location in the cursor, much like Java Lists and/or Arrays. With this knowledge, we can implement a RecyclerView using a Cursor in pretty much the same way we implement RecyclerView using ArrayLists. Rather than calling List.get(i), with a Cursor, you move the Curosr to the desired position, using moveToPosition(). After moving the Cursor to the desired position, you call the appropriate getXXX(int columnIndex) method, where XXX is either one of Blob, Double, Float, Int, Long, Short or String.
  However, instead of bothering about the correct column indices from our readFromDB() method above, we use the helpfully provided getColumnIndexOrThrow() method, which fetches the index of the named column, or throws an Exception if the column name doesn’t exist within the Cursor.
  1. public class SampleRecyclerViewCursorAdapter extends RecyclerView.Adapter<SampleRecyclerViewCursorAdapter.ViewHolder> {
  2.     Context mContext;
  3.     Cursor mCursor;
  4.     public SampleRecyclerViewCursorAdapter(Context context, Cursor cursor) {
  5.         mContext = context;
  6.         mCursor = cursor;
  7.     }
  8.     public static class ViewHolder extends RecyclerView.ViewHolder {
  9.         EmployerListItemBinding itemBinding;
  10.         public ViewHolder(View itemView) {
  11.             super(itemView);
  12.             itemBinding = DataBindingUtil.bind(itemView);
  13.         }
  14.         public void bindCursor(Cursor cursor) {
  15.             itemBinding.nameLabel.setText(cursor.getString(
  16.                     cursor.getColumnIndexOrThrow(SampleDBContract.Employer.COLUMN_NAME)
  17.             ));
  18.             itemBinding.descLabel.setText(cursor.getString(
  19.                     cursor.getColumnIndexOrThrow(SampleDBContract.Employer.COLUMN_DESCRIPTION)
  20.             ));
  21.             Calendar calendar = Calendar.getInstance();
  22.             calendar.setTimeInMillis(cursor.getLong(
  23.                     cursor.getColumnIndexOrThrow(SampleDBContract.Employer.COLUMN_FOUNDED_DATE)));
  24.             itemBinding.foundedLabel.setText(new SimpleDateFormat("dd/MM/yyyy").format(calendar.getTime()));
  25.         }
  26.     }
  27.     @Override
  28.     public int getItemCount() {
  29.         return mCursor.getCount();
  30.     }
  31.     @Override
  32.     public void onBindViewHolder(ViewHolder holder, int position) {
  33.         mCursor.moveToPosition(position);
  34.         holder.bindCursor(myCursor);
  35.     }
  36.     @Override
  37.     public ViewHolder onCreateViewHolder(ViewGroup parent, int viewType) {
  38.         View view = LayoutInflater.from(parent.getContext()).inflate(
  39.                 R.layout.employer_list_item, parent, false);
  40.         ViewHolder viewHolder = new ViewHolder(view);
  41.         return viewHolder;
  42.     }
  43. }
复制代码
  

Using a simple SQLite database in your Android app

Using a simple SQLite database in your Android app-6-技术控-Android,management,different,published,available

  Defining foreign keys

  To recap, thus far, we’ve created an Employer table, which we have populated with rows of content. We, now intend to create an Employee table, which is related to the Employer table through the Employer _ID column.We define an Employee class that extends BaseColumns, in the SampleDBContract class (similar to how we defined the Employer class). Note that in the create table String for Employee, we included the String “FOREIGN KEY(employer_id) REFERENCES employer(_id)”
  1. public static class Employee implements BaseColumns {
  2.         public static final String TABLE_NAME = "employee";
  3.         public static final String COLUMN_FIRSTNAME = "firstname";
  4.         public static final String COLUMN_LASTNAME = "lastname";
  5.         public static final String COLUMN_DATE_OF_BIRTH = "date_of_birth";
  6.         public static final String COLUMN_EMPLOYER_ID = "employer_id";
  7.         public static final String COLUMN_JOB_DESCRIPTION = "job_description";
  8.         public static final String COLUMN_EMPLOYED_DATE = "employed_date";
  9.         public static final String CREATE_TABLE = "CREATE TABLE " +
  10.                 TABLE_NAME + " (" +
  11.                 _ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
  12.                 COLUMN_FIRSTNAME + " TEXT, " +
  13.                 COLUMN_LASTNAME + " TEXT, " +
  14.                 COLUMN_DATE_OF_BIRTH + " INTEGER, " +
  15.                 COLUMN_EMPLOYER_ID + " INTEGER, " +
  16.                 COLUMN_JOB_DESCRIPTION + " TEXT, " +
  17.                 COLUMN_EMPLOYED_DATE + " INTEGER, " +
  18.                 "FOREIGN KEY(" + COLUMN_EMPLOYER_ID + ") REFERENCES " +
  19.                 Employer.TABLE_NAME + "(" + Employer._ID + ") " + ")";
  20.     }
复制代码
Upgrading the SQLiteOpenHelper

  If you have followed the tutorial up to this point, you will have built an Employer table and inserted values already. If you do not change your database version, the new Employee table will never be created. Unfortunately, if you change the version, recall that the onUpgrade() method, as currently defined, drops the Employer table. To guard against this, you can comment out (or delete) the drop statement in onUpgrade(), and add an execSQL() statement to create the Employee table. Since the Employee table references the Employer table, you must create the Employer table first.
  1. public class SampleDBSQLiteHelper extends SQLiteOpenHelper {
  2.     private static final int DATABASE_VERSION = 2;
  3.     public static final String DATABASE_NAME = "sample_database";
  4.     public SampleDBSQLiteHelper(Context context) {
  5.         super(context, DATABASE_NAME, null, DATABASE_VERSION);
  6.     }
  7.     @Override
  8.     public void onCreate(SQLiteDatabase sqLiteDatabase) {
  9.         sqLiteDatabase.execSQL(SampleDBContract.Employer.CREATE_TABLE);
  10.         sqLiteDatabase.execSQL(SampleDBContract.Employee.CREATE_TABLE);
  11.     }
  12.         // We don't want to delete user data.
  13.     @Override
  14.     public void onUpgrade(SQLiteDatabase sqLiteDatabase, int oldVersion, int newVersion) {
  15.                 if(oldVersion == 0 && newVersion == 2) {
  16.                         sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + SampleDBContract.Employee.TABLE_NAME);
  17.                 }
  18.                 else {
  19.                         sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + SampleDBContract.Employer.TABLE_NAME);
  20.                         sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + SampleDBContract.Employee.TABLE_NAME);
  21.                 }
  22.         onCreate(sqLiteDatabase);
  23.     }
  24. }
复制代码
Display data from an SQLite database query in a Spinner

  To create an Employee, the user needs to select the corresponding Employer. A good way to do this is to present a Spinner to the app user. Displaying the contents of a Cursor in a Spinner is pretty straightforward.
  First, we perform a query as discussed above, fetching just the Employer name and id (queryCols). Next, we create a SimpleCursorAdapter instance passing it the Cursor, an array of columns to display (adapterCols), and an array of views that the columns should be displayed in (adapterRowViews). Then, we set the Spinner’s Adapter to the SimpleCursorAdapter.
  1. dependencies {
  2.         ...
  3.     compile 'com.android.support:design:24.2.1'
  4.     compile 'com.android.support:cardview-v7:24.2.1'
  5. }0
复制代码
  

Using a simple SQLite database in your Android app

Using a simple SQLite database in your Android app-7-技术控-Android,management,different,published,available

  Insert foreign key in database

  Inserting a row containing a foreign key is completely identical to inserting rows in tables without foreign key constraints. The difference here is that we get a reference to the selected Cursor from the Spinner, and then get the value of the Employer _ID column.
  1. dependencies {
  2.         ...
  3.     compile 'com.android.support:design:24.2.1'
  4.     compile 'com.android.support:cardview-v7:24.2.1'
  5. }1
复制代码
  

Using a simple SQLite database in your Android app

Using a simple SQLite database in your Android app-8-技术控-Android,management,different,published,available

  Select from SQLite database using JOIN

  Unfortunately, we can’t use the SQLiteDatabase’s query() method to query multiple tables. To query multiple tables, we must provide our own SQL query. In the sample, the query is defined in the SampleDBContract class.
  1. dependencies {
  2.         ...
  3.     compile 'com.android.support:design:24.2.1'
  4.     compile 'com.android.support:cardview-v7:24.2.1'
  5. }2
复制代码
Notice that we use the ‘?’ character in the WHERE clause in much the same way as described above for the query() method. To execute the provided SQL, we’ll need to define a selectionArgs String[] containing values that will replace the ‘?’s in our provided SQL query.
  1. dependencies {
  2.         ...
  3.     compile 'com.android.support:design:24.2.1'
  4.     compile 'com.android.support:cardview-v7:24.2.1'
  5. }3
复制代码
  

Using a simple SQLite database in your Android app

Using a simple SQLite database in your Android app-9-技术控-Android,management,different,published,available

  Finally

   As usual, the complete source code is available on github , for use, reuse and misuse as you see fit. SQLite databases are very powerful, and it is amazing that it is available to all android apps, always. While it might be an overkill for most data storage needs, it’s an absolute godsend when you need it.



上一篇:Spring Data Series: Spring Data in a Nutshell
下一篇:换7记:小博PHP7上试跑
cjkfa 发表于 2016-10-6 16:54:15
这是一个KB的故事,当你在半夜12点的时候穿着黑色的衣服对着镜子用梳子梳下就会看到…头皮…屑!
回复 支持 反对

使用道具 举报

﹌花祭ゞ 发表于 2016-10-22 02:48:19
我是﹌花祭ゞ,我是新手~~
回复 支持 反对

使用道具 举报

kena69 发表于 2016-10-26 10:41:30
路过。。。。
回复 支持 反对

使用道具 举报

vmqve 发表于 2016-11-9 22:03:51
元芳你怎么看?
回复 支持 反对

使用道具 举报

杜丽 发表于 2016-11-16 22:33:49
绘一场生死契阔的游戏,为我们的故事写一个结局。
回复 支持 反对

使用道具 举报

离开我伱才会幸福 发表于 2016-11-18 14:22:53
我回帖楼主给加积分吗?
回复 支持 反对

使用道具 举报

glue_ak47 发表于 2016-11-19 22:31:34
永远不要给背叛过自己的朋友第二次背叛的机会。
回复 支持 反对

使用道具 举报

*滑动验证:
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

我要投稿

推荐阅读


回页顶回复上一篇下一篇回列表
手机版/CoLaBug.com ( 粤ICP备05003221号 | 文网文[2010]257号 )

© 2001-2017 Comsenz Inc. Design: Dean. DiscuzFans.

返回顶部 返回列表