Android 14th --- SQLite of Android heavyweight data storage --- androidsqlite

Android 14th --- SQLite of Android heavyweight data storage --- androidsqlite

Preface

Unconsciously, the Android basic series has written 13 articles, which are 14th articles ~ The previous blog recorded a data storage solution in Android, that is, the use of the shared parameters (Sharedpreferences) (virgins Android (13th) --- SharedPreferences of Android lightweight data storage ). Recently, I learned how to apply SQLite in Android and wrote a small example of adding, deleting, and querying Based on ListView. This blog will record how I learned to operate SQLite persistent client data in Android.

Initialize SQLite

This article will not introduce the basic knowledge of SQLite. We only need to know that it is also a relational lightweight database and an embedded database engine, which is more suitable for data storage on mobile devices, for details, see Encyclopedia: http://baike.baidu.com/link? Url =_RNKz-r1FBwEm4iVvyxLQzCuKRdR12RrHNtUUa2nhSpILvUyT3g8jxVMbQzWmRHAUaRPYBem04hwMyom3kVx0a

In this section, record Initialization is nothing more than creating a database and a table. Let's take a look at the initialization code:

Package com. wl. cigrec. dao; import android. content. context; import android. database. sqlite. SQLiteDatabase; import android. database. sqlite. SQLiteOpenHelper; public class DBHelper extends SQLiteOpenHelper {// database name private static final String DB_NAME = "mycigrec. db "; // database VERSION private static final int VERSION = 1; public DBHelper (Context context) {super (context, DB_NAME, null, VERSION ); // TODO Auto-generated constructor stub} @ Overridepublic void onCreate (SQLiteDatabase db) {// TODO Auto-generated method stubString sql1 = "create table t_order (" + "id integer primary key autoincrement," + "count integer," + "money real, date, balance real, orderid varchar (20) "; String sql2 =" create table t_order_detail ("+" id integer primary key autoincrement, "+" cigname varchar (20), cigprice real, cigcount integer, orderid varchar (20) "mongodb.execsql(sql1mongodb.exe cSQL (sql2) ;}@ Overridepublic void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion) {// TODO Auto-generated method stubSystem. out. println ("------ onUpgrade called ------" + "oldVersion -->" + oldVersion + "," + "newVersion -->" + newVersion );}}

This is the code for initializing the database. For details, refer to the sample code in the official documentation. The following describes the key content in line by line.

Line 7Defines a DB help class and inherits the SQLiteOpenHelper class. This class is a tool class provided by Android for database management and encapsulates many methods for convenient database operations, in actual development, we usually choose to expand SQLiteOpenHelper to initialize our own DB, So I ignore the basic API methods here, although the underlying layer of SQLiteOpenHelper still uses those basic APIs.

Line 15The constructor with four parameters of the parent class is called. Let's take a look at the explanation in this document:

The context parameter is needless to say and used to open or create a database. The name parameter is the name of the database file. The factory parameter is used to create a cursor object. The default value is NULL. The last version parameter is the database version number specified by us. It must start from 1. Otherwise, an exception will be thrown. For details, refer to the 100th line of the source code:

  if (version < 1) throw new IllegalArgumentException("Version must be >= 1, was " + version);

Line 20When the database is initialized, The onCreat (SQLiteDatabase db) method is called back. Generally, data tables are created in the onCreat method.

Line 33This method is called back when the database version is changed. For example, if you need to update the table structure when upgrading the software, we will write some alter table statements in this method for execution, in fact, I personally think this method is a little tricky. I prefer to recreate database files.

The above is our initialization work. After the database creation and table creation are completed, the next step is how to use the DML language to operate the database.

Operate SQLite

The database Initialization is completed in the previous section. The SQL statement is used to execute updates or queries. To operate SQLite in Android, you must first open the database. We have two methods to choose from, they are:

GetReadableDatabase (), getWritableDatabase ()

Read the explanation of getWritableDatabase () in the official document ~ Translation is for reference only ~

Create and/or open a database that will be used for reading and writing. the first time this is called, the database will be opened and onCreate (SQLiteDatabase), onUpgrade (SQLiteDatabase, int, int) and/or onOpen (SQLiteDatabase) will be called. (create or open a database that can be read and written. After the first call, the database will be opened and the onCreate, onUpgrade, or onOpen methods will also be called)

Once opened successfully, the database is cached, so you can call this method every time you need to write to the database. (Make sure to call close () when you no longer need the database .) errors such as bad permissions or a full disk may cause this method to fail, but future attempts may succeed if the problem is fixed (once the database is successfully opened, the database will be cached, therefore, you can call this method every time you need to write data to the database. when you no longer need to use the database, you must call the close () method to close the database, some errors may cause this method to fail to be called. For example, the wrong permission or hard disk is full, but if the error is fixed, the subsequent attempt may succeed)

After reading the description of getWritableDatabase (), let's take a look at the description of getReadableDatabase (). Then we can make a comparison ~

Create and/or open a database. this will be the same object returned by getWritableDatabase () unless some problem, such as a full disk, requires the database to be opened read-only. in that case, a read-only database object will be returned. if the problem is fixed, a future call to getWritableDatabase () may succeed, in which case the read-only database object will be closed and the read/write obje Ct will be returned in the future. (When you create or open a database, calling this method will return the same object as getWritableDatabase. Unless there are some problems, such as the hard disk is full, you need to open the database in read-only mode. If that is the case, a read-only database object will be returned. If the problem is fixed, the subsequent call to getWritableDatabase may succeed. In this case, the read-only database object will be closed and a read/write database object will be returned)

It is not difficult to find that, under normal circumstances, there is no difference between the two methods and the same SQLiteDatabase object is returned, but the processing methods are different in some abnormal situations, so it doesn't matter which one we use. After you open the database, you can add, delete, modify, and query it. For ease of use, we generally encapsulate a DAO layer to operate the database, the following is a simple DAO-Layer Code encapsulated by me and then I will explain the key parts one by one:

Package com. wl. cigrec. dao; import java. util. arrayList; import java. util. hashMap; import java. util. list; import java. util. map; import android. content. context; import android. database. cursor; import android. database. SQLException; import android. database. sqlite. SQLiteDatabase; public class DBManager {private DBHelper helper; private SQLiteDatabase database; public DBManager (Context context) {helper = new DBHelpe R (context); database = helper. getWritableDatabase ();}/*** update ** @ param SQL * @ param * @ return */public boolean updateBySQL (String SQL, Object [] param) {boolean flag = false; database. beginTransaction (); try again database.exe cSQL (SQL, param); flag = true; database. setTransactionSuccessful ();} catch (SQLException e) {// TODO Auto-generated catch blocke. printStackTrace ();} finally {database. endTransac Tion (); if (database! = Null) database. close ();} return flag;}/*** batch insert ** @ param sqls * @ return */public boolean insertBatch (List <String> sqls) {boolean flag = false; database. beginTransaction (); try {for (String SQL: sqls) Export database.exe cSQL (SQL);} flag = true; database. setTransactionSuccessful ();} catch (SQLException e) {// TODO Auto-generated catch blocke. printStackTrace ();} finally {database. endTransaction (); if (databa Se! = Null) database. close () ;}return flag ;} /*** query ** @ param table * @ param columns * @ param whereCause * @ param selectionArgs * @ param groupBy * @ param having * @ param orderBy * @ param limit *@ return */public List <Map <String, string> query (String table, String columns [], String whereCause, String [] selectionArgs, String groupBy, String having, String orderBy, String limit) {List <Map <String, string> List = new ArrayList <Map <String, String> (); try {Cursor cursor = null; cursor = database. query (table, columns, whereCause, selectionArgs, groupBy, having, orderBy, limit); while (cursor. moveToNext () {Map <String, String> map = new HashMap <String, String> (); for (int I = 0; I <cursor. getColumnCount (); I ++) {String columnName = cursor. getColumnName (I); String columnValue = cursor. getString (cursor. getCol UmnIndex (columnName); map. put (columnName, columnValue);} list. add (map) ;}} catch (Exception e) {// TODO Auto-generated catch blocke. printStackTrace ();} finally {if (database! = Null) database. close () ;}return list ;}}

Line 30Here, an SQL-based update method is encapsulated, including adding or deleting data. Because the SQLiteDatabase method directly provides an execSQL method, therefore, you can pass in SQL statements and placeholder parameter values to conveniently execute DML statements.

Line 54Here we have written a batch insert method as needed. This method is not the most efficient, so it is not recommended. If you have such requirements, I recommend a very good blog reference: Android batch inserts data to the SQLite database.

Line 88A general query method is encapsulated here. The SQL method is not used to encapsulate it because the query method provided by SQLiteDatabase is more generic. We can see from the parameters. The usage of the Cursor object is similar to the ResultSet in JDBC. It is easy to understand.

After the DAO layer is encapsulated, we can call it at the Service layer. The following describes how to query only one service layer:

/*** Get order List *** @ param context * @ return List <OrderEntity> */public List <OrderEntity> getOrderList (Context context) {DBManager dm = new DBManager (context); List <Map <String, String> result = dm. query ("t_order", new String [] {"id", "count", "money", "money", "date", "balance", "orderid "}, null, null); ArrayList <OrderEntity> list = new ArrayList <OrderEntity> (); if (result. size ()> 0) {for (Map <String, String> map: result) {OrderEntity entity = new OrderEntity (); String id = map. get ("id"); String count = map. get ("count"); String money = map. get ("money"); String date = map. get ("date "). toString (); String balance = map. get ("balance"); String orderid = map. get ("orderid"); entity. setId (Integer. parseInt (id); entity. setCount (Integer. parseInt (count); entity. setMoney (Double. parseDouble (money); entity. setDate (date); entity. setBalance (Double. parseDouble (balance); entity. setOrderid (orderid); list. add (entity) ;}} return list ;}

Generally, the Service layer encapsulates and processes the queried data. When the Activity or Fragment calls the method at the Service layer, the encapsulated data should be returned, this is also the most basic MVC application, and the whole process is basically like this.

Export and view data in SQLite

After we persist the data, we usually want to know whether the data is successfully saved or whether the data changes after performing the update operation. The simplest way to do this is to export the database file, you can use SQLite related tools to view the information.

After the application is running, switch to the DDMS view and click the File Explorer tab, the file list shown in is displayed here. (If you do not open the first data folder, check whether the Android mobile phone is ROOT. You can only view the file after ROOT ):

Open the folder I marked in sequence, and find the package name of the project under the second-level data. After clicking it, you can see the directory of the following structure:

The xxx. db in the database folder under the package name is our database file. This file name is the database file name we first defined in the DBHelp class. Finally, click the icon in the upper-right corner to export the database to the local device:

After that, we can see a file like this:

After successfully exporting the database file, we can use the SQLite related operation tools to view the database data. I use SQLiteSpy, a very small tool, SQLiteExpertPro is recommended if you need some advanced functions. It's easy to click open database to open our database file. The following shows the data in the database through a simple SQL query:

Summary

This blog record some basic methods for operating SQLite on Android. I personally feel that the basic operations of relational databases are similar. Therefore, it is easy to learn SQLite with JDBC development experience, recently, the P2P server has delayed the Android learning progress and blog update progress. At noon, the more things you know, the more I feel that the gap between myself and other great gods is bigger, the more I need to work harder. In the new year, come on!