Virgin Man learns Android (14)---The Android heavyweight data store SQLite

Preface

Unconsciously the Android Basic series has written 13, this is the 14th article ~ the previous blog recorded a data storage program in Android, That is, the use of shared parameters (Sharedpreferences) (Virgin Male Learning Android (13)---Android Lightweight data storage sharedpreferences). Recently how to learn how to apply SQLite in Android, wrote a ListView based on the deletion of a small example, this blog will record how I learned how to operate SQLite persisted client data in Android.

Initialize SQLite

Basic knowledge about SQLite This article does not introduce, we just need to know that it is also a relational lightweight database, and is an embedded database engine, more suitable for mobile device data storage, details can refer to the encyclopedia: Http://baike.baidu.com/link?url =_rnkz-r1fbwem4ivvyxlqzcukrdr12rrhntuua2nhspilvuyt3g8jxvmbqzwmrhauarpybem04hwmyom3kvx0a

This section records initialization, that is nothing more than building a database and building a table, the following first look at the initialization of 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 (Contex T, db_name, NULL, VERSION);//TODO auto-generated constructor stub} @Overridepublic void OnCreate (Sqlitedatabase DB) {//To Do auto-generated method stubstring SQL1 = "CREATE Table T_order (" + "ID integer primary key autoincrement," + "Count Integ Er, "+" money real,date date,balance real,orderid varchar (20)) "; String SQL2 = "CREATE Table T_order_detail (" + "ID integer primary key autoincrement," + "cigname varchar), Cigprice Real, Cigcount integer,orderid varchar) ";d b.execsql (SQL1);d b.execsql (SQL2);} @Overridepublic void Onupgrade (sqlitedatabase db, int oldversion, int newversion) {//TODO auto-generated method Stubsyste M.OUT.PRINTLN ("------onupGrade called------"+" oldversion--> "+ oldversion +", "+" newversion--> "+ NewVersion);}} 

This is the code to initialize the database, fully refer to the sample code in the official documentation, the following line to explain the key content.

Line 7 defines a DB helper class and inherits the Sqliteopenhelper class, which is a tool class for managing databases provided by Android, and encapsulates a number of easy-to-operate DB methods that we typically choose to expand in real-world development. Sqliteopenhelper to initialize our own db, so I'm ignoring the underlying API approach, even though the underlying API is still used by Sqliteopenhelper.

Line calls the parent class with a 4-parameter construction method and takes a look at the explanations in the document:

The context parameter does not have to be said to open or create a database. The name parameter is the file name of the database file. The factory parameter is used to create a cursor object, which is generally null by default. The last version parameter is the database version number we have specified , starting from 1, or throw an exception, you can refer to the 100th line of the source code:

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

Line initializes the Oncreat (Sqlitedatabase db) method when initializing the database, and we typically create a data table in the Oncreat method.

Line when the database version changes when the callback method, such as upgrading the software needs to update the table structure, then we will write some ALTER table in this method to execute the statement, in fact, personally think this method slightly chicken, I prefer to recreate the database file.

The above is our initial chemical work, completed the building and the table, then the next is how to use the DML language to operate the db.

Operation SQLite

The previous section completed the initialization of the database, followed by the SQL statement to perform the update or query, the operation of SQLite in Android the first thing to do is to open the DB, we have two methods to choose from, namely:

getreadabledatabase (),getwritabledatabase ()

Read the explanation of Getwritabledatabase () in the official documentation ~ translation for reference only ~

Create and/or open a database that would be used for reading and writing. The first time this is called, the database would be opened and OnCreate (Sqlitedatabase), Onupgrade (Sqlitedatabase, Int., in T) and/or OnOpen (sqlitedatabase) 'll be called. (Create or open a writable database, after the first call, the database will be opened and the OnCreate method, Onupgrade method, or OnOpen method will be called)

Once opened successfully, the database is cached, so you can call this method every time you need to write to the data Base. (Make sure to call Close () if you no longer need the database.) Errors such as bad permissions or a full disk could cause this method to fail, but future attempts may succeed if the proble M is fixed(once the database is successfully opened, the database will be cached, so you can call this method every time you need to write data to the database, and when you no longer need to use the database, be sure to call the close () method to close the database. Some errors may cause the method call to fail, such as the wrong permissions, the hard disk is full, but if the error is fixed then the subsequent attempt may succeed)

After reading the instructions of Getwritabledatabase (), look at the instructions of Getreadabledatabase (), and then make a comparison .

Create and/or open a database. This would be is the same object returned by Getwritabledatabase () unless some problem, such as a full disk, requires the data Base to be opened read-only. In the case, a Read-only database object would be returned. If the problem is fixed, a future call to Getwritabledatabase () may succeed, in which case the Read-only database object W Ill be closed and the Read/write object would be a returned in the future. (Create or open a database, call this method will return and getwritabledatabase the same object, unless there are some problems, such as the hard disk is full, you need to read-only way to open the database.) If that's the case, then a read-only database object is returned. If the problem is fixed then the subsequent call to Getwritabledatabase may succeed, in which case the read-only database object will close and then return a writable database object )

It is not difficult to find that, under normal circumstances, the two methods are not different, the return is the same Sqlitedatabase object, only in some abnormal situation of the processing method is different, so for us to use which actually does not matter. After opening the database, it can be added to the search operation, in order to facilitate the use of our general will encapsulate a DAO layer to operate the DB, the following first put on my simple package of a DAO layer code and then the key part of the explanation:

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 DBHelper (context);d atabase = Helper.getwritabledatabase ();}  /** * UPDATE * * @param SQL * @param param * @return */public boolean updatebysql (String sql, object[] param) {Boolean flag = False;database.begintransaction (); try {database.execsql (sql, param); flag = True;database.settransactionsuccessful ( );} catch (SQLException e) {//TODO auto-generated catch Blocke.printstacktrace ();} finally {database.endtransaction (); if ( Database! = null) Database.close ();} return flag;} /** * BULK INSERT * * @param sqls * @return */public boolean insertbatch (List<string> sqls) {Boolean flag = False;database . BeginTransaction(); try {for (String sql:sqls) {database.execsql (SQL);} Flag = True;database.settransactionsuccessful ();} catch (SQLException e) {//TODO auto-generated catch Blocke.printstacktrace ();} finally {database.endtransaction (); if ( Database! = null) Database.close ();} return flag;} /** * Query * * @param table * @param columns * @param wherecause * @param selectionargs * @param groupBy * @param having * @param by * @param limit * @return */public list<map<string, string>> query (string table, String columns[ ],string Wherecause, string[] Selectionargs, string groupby,string Having, string-by-clause, string limit) {list<map< String, string>> list = new arraylist<map<string, string>> (); try {cursor cursor = Null;cursor = Database . query (table, columns, Wherecause, Selectionargs,groupby, have, by-clause, limit), while (Cursor.movetonext ()) {map< string, string> map = new hashmap<string, string> (), for (int i = 0; i < Cursor.getcolumncount (); i++) {StringColumnName = Cursor.getcolumnname (i); String Columnvalue = cursor.getstring (Cursor.getcolumnindex (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 here encapsulates an SQL-based Update method, including add or remove, because the Sqlitedatabase method directly provides a Execsql method, so we can easily execute DML statements by passing in the SQL statement and the parameter values of the placeholder.

Line Si here according to the requirements of a bulk insert method, this method is not the highest efficiency, so it is not recommended. If there is a need for students in this area I recommend a very good blog post reference: Android Bulk insert data into the SQLite database

Line in here encapsulates a common query method, not in the way of SQL encapsulation is because the sqlitedatabase comes with the query method more general, according to the parameters can be seen. The cursor object is used similar to the resultset in JDBC, and it should be easy to see and understand.

Once the DAO layer has been encapsulated, we can call it in the service layer, and query as an example, the following is only a service layer Query method:

/** * Get Order list * * @param context * @return list<orderentity> */public list<orderentity> getorderlist (context C Ontext) {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, NULL, NULL, 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;}

In general, the service layer will be the data of the query for some encapsulation and processing, when we call the service layer of activity or fragment method should return the encapsulated data, this is the most basic MVC application, the whole process is basically the case.

export and view data in SQLite

When we persist the data, we usually want to know whether the data is saved successfully, or if we want to see if the data changes after the update operation, then the simplest way is to export the database file and look it up with the relevant tools of SQLite.

When our application is running, switch to the DDMS view, click on the File Explorer tab, where you can see the list of files as shown ( if you don't open the First Data folder, check if the Android phone is rooted, Permission to view only after root ):

Click on the folder I marked, in the second layer of data to find the package name of the project, after the opening can see the following structure of the directory:

The name of the database folder under the package name is our database file, which is the file name that we first defined in the Dbhelp class xxx.db. Finally click on the icon in the top right to export the database to Local:

When we are done we can see a file like this:

After successfully exporting the database file, we can view the database data through the SQLite related Operation tool, I use sqlitespy, very small one tool, of course, if need some advanced function, it is recommended to use Sqliteexpertpro. Very simple, click Open Database opens our databases file is OK, the following simple query SQL to see the data in the database:

Summary

This blog record some of the basic methods of working with SQLite on Android, the basic operation of the personal feeling relational database is very similar, so that if you have the development experience of JDBC, learning SQLite is very easy, Recently has been to do peer to the service end of the Android Learning Progress and blog update progress, noon to drink a pulse, pulse back, understand the more things, the more I will feel the difference between the other big God, so I have to work harder just, a new year, refueling!

Virgin Man learns Android (14)---The Android heavyweight data store SQLite