Shulou(Shulou.com)06/02 Report--

Today, I would like to share with you the relevant knowledge points about how to use Android's database. The content is detailed and the logic is clear. I believe most people still know too much about this knowledge, so share this article for your reference. I hope you can get something after reading this article. Let's take a look.

I. use of Android database

Android uses android.database.sqlite.SQLiteDatabase to represent a database object, which provides two modes to help developers to add, delete, modify, query and other basic database operations.

Using SQL statement to describe operation

Use SQL statements to call SQLiteDatabase.execSql or SQLiteDatabase.rawQuery to perform the operation.

/ / use sql to query data Cursor data = db.rawQuery ("select id,name from table"); / / insert data db.execSql using sql ("insert into contacts (id,name) values (2 recording cpacm')")

Anyone who has slightly learned the sql sentence should be able to understand the above code (in fact, you can get a rough idea of the meaning of the sentence).

Here I would like to explain the role of Cursor (cursor), cursors can not be as the name implies (up master once studied the database as a pointer variable in the C language, although a little right, but the idea is still misunderstood), Cursor it is a data buffer set up by the system for users, yes, it is a data area that stores the execution results of SQL statements. But it also provides a mechanism to extract one record at a time from a result set that includes multiple data records, much like a pointer. Cursors are always associated with a SQL select statement because cursors consist of a result set (which can be zero, one, or multiple records retrieved by an associated selection statement) and a cursor location that points to a particular record in the result set. When you decide to process the result set, you must declare a cursor that points to the result set. Compared with C language, if you have written a program to process a file, the cursor is like the file handle you get when you open the file. As long as the file is opened, the file handle can represent the file. In short, remember that a cursor is a data area with a unique token that allows the user to read the data one by one.

Describe the operation of the database in a structured way

In this way, even if we are not familiar with SQL statements, we can still use the most familiar object-oriented way to operate the database.

/ / query data in a structured way

Cursor data = db.query ("contacts", new String [] {"id", "name"}, null,null,null,null,null)

/ / insert data in a structured way

ContentValue values = new ContentValues ()

Values.put ("id", 2)

Values.put ("name", "cpacm")

Db.insert ("table", null,values)

/ * Parameter description * table: Datasheet name, columns: column name to be displayed. If null, it is equivalent to * * selection: where condition equivalent to sql statement; selectionArgs array contains the where condition to be replaced? Group, orderBy: sort of the * groupBy:SQL statement. Default asc * * / public Cursor query (String table, String [] columns, String selection, String [] selectionArgs, String groupBy, String having, String orderBy) {}

For example, the SQL statement I want to query is

SELECT CustomerName, SUM (OrderPrice) FROM Orders WHERE Country=? GROUP BY CustomerName HAVING SUM (OrderPrice) > 500 ORDER BY CustomerName

So the code I wrote is as follows

/ / Datasheet name String table = "Orders"; / / column name to be displayed: String [] columns = new String [] {"CustomerName", "SUM (OrderPrice)"}; / / Select condition String selection = "Country=?"; / / the variables in the condition correspond to the question mark in the condition. Please take your seat one by one when there are more than one. String [] selectionArgs = new String [] {"China"}; / / Group name String groupBy = "CustomerName"; / / condition of the group String having = "SUM (OrderPrice) > 500"; / / sort by field String orderBy =" CustomerName "; Cursor c = db.query (table, columns, selection, selectionArgs, groupBy, having, orderBy)

In this way, we can query the database. The parameters of other statements are all the same, so I won't introduce them all here.

Public long insert (String table, String nullColumnHack, ContentValues values) public int delete (String table, String whereClause, String [] whereArgs) public int update (String table, ContentValues values, String whereClause, String [] whereArgs)

Extracurricular knowledge: about the use of GroupBy and Having

As the name implies, group by is grouped according to xxx. It must be used with an "aggregate function" and requires at least one grouping identification field. Aggregate functions are: sum (), count (), avg (), and so on. The purpose of using group by is to aggregate data groups. For example, the CustomerName of the above sql statement, if it has four lines {"Zhang San", "Li Si", "Zhang San", "Li Si"}, then it will be divided into two groups, namely Zhang San group and Li 4 group, and then count the sum of the orderprice they use.

The function of HAVING is to specify conditions for each group, just as where specifies conditions, that is, rows can be selected according to the conditions you specify. If you want to use the HAVING clause, it must come after the GROUP BY clause. Or the above SQL statement, if Zhang San's SUM (OrderPrice) does not exceed 500, then Zhang three groups will not be displayed.

Pre-compilation of SQL sentences

In practice, some SQL statements need to be used repeatedly. In order to avoid the overhead of repeatedly parsing SQL statements, we can precompile the SQL statements that need to be reused to improve the execution efficiency of database operations.

/ / compile complex SQL statements SQLiteStatement compiledSql = db.compileStatement (aSQL); / / execute SQLcompiledSql.execute (); / / compile complex SQL statements SQLiteStatement compiledSql = db.compileStatement (aSQL); / / execute SQLcompiledSql.execute ()

Extracurricular knowledge: the so-called transaction is a user-defined sequence of database operations, these operations are either done or not done, is an indivisible unit of work. For example, in a relational database, a transaction can be a SQL statement, a set of SQL statements, or an entire program. A simple example is that when you want to modify two different tables in the database at the same time, if they are not the same transaction, when the table has been modified, but the second table is abnormal and cannot be modified, only the second table returns to its unmodified state, and the * tables have been modified. When you set them as a transaction, when the table is modified, but the second table is abnormal and cannot be modified, both the table and the second table will return to the unmodified state! This is called transaction rollback.

SQLiteOpenHelper

In SQLiteOpenHelper, you encapsulate a SqliteDatabase object that uses this class to perform database operations.

Package com.example.notebook; import android.content.Context; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; import android.database.sqlite.SQLiteDatabase.CursorFactory; public class DBHelper extends SQLiteOpenHelper {private static final int VERSION=1 / * * in a subclass of SQLiteOpenHelper, you must have the constructor * @ param context context object * @ param name database name * @ param factory * @ param version's current database version The value must be an integer and be in an incremental state * / public DBHelper (Context context,String name,CursorFactory factory,int version) {super (context,name,factory,version) } public DBHelper (Context context, String name, int version) {this (context,name,null,version);} public DBHelper (Context context, String name) {this (context,name,VERSION) This function is called when} @ Override public void onCreate (SQLiteDatabase db) {/ / database * * is constructed, where you can construct tables, indexes, and so on System.out.println ("create a database") / / execSQL is used to execute SQL statement db.execSQL ("create table notebook (_ id integer primary key autoincrement,pic varchar (50), title varchar (20), content text,time varchar)") } @ Override public void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion) {/ / if the current database version is higher than the existing database version, call this function System.out.println ("upgrade a database");}}

Application of SQLiteOpenHelper

Create a new database management class DBManager

Package com.example.notebook; import android.content.ContentValues; import android.content.Context; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteException; import android.util.Log; public class DBManager {private Context mContext = null; private SQLiteDatabase mSQLiteDatabase = null;// objects used to manipulate the database private DBHelper dh = null;// objects used to create the database private String dbName = "note.db" / / name of the database private int dbVersion = 1 new DBHelper / version of the database public DBManager (Context context) {mContext = context;} public void open () {try {dh = new DBHelper (mContext, dbName, null, dbVersion); / / set up the database if (dh = = null) {Log.v ("msg", "is null") Return;} mSQLiteDatabase = dh.getWritableDatabase (); / / Open database / / dh.onOpen (mSQLiteDatabase) in writable mode;} catch (SQLiteException se) {se.printStackTrace ();}} public void close () {mSQLiteDatabase.close (); / / close database dh.close () } public Cursor selectAll () {Cursor cursor = null; try {/ / sql statement operates String sql = "select * from notebook"; cursor = mSQLiteDatabase.rawQuery (sql, null);} catch (Exception ex) {ex.printStackTrace (); cursor = null;} return cursor;} public Cursor selectById (int id) {/ / String result [] = {}; Cursor cursor = null Try {/ / sql statement operates String sql = "select * from notebook where _ id='" + id + "'"; cursor = mSQLiteDatabase.rawQuery (sql, null);} catch (Exception ex) {ex.printStackTrace (); cursor = null;} return cursor;} public long insert (String title, String content,String pic) {long datetime = System.currentTimeMillis () Long l =-1; try {/ / structured operation ContentValues cv = new ContentValues (); cv.put ("title", title); cv.put ("content", content); cv.put ("time", datetime); cv.put ("pic", pic); l = mSQLiteDatabase.insert ("notebook", null, cv) / / Log.v ("datetime", datetime+ "" + l);} catch (Exception ex) {ex.printStackTrace (); l =-1;} return 1;} public int delete (int id) {int affect = 0; try {/ / structured operation affect = mSQLiteDatabase.delete ("notebook", "_ id=?", new String [] {String.valueOf (id)}) } catch (Exception ex) {ex.printStackTrace (); affect =-1;} return affect;} public int update (int id, String title, String content,String pic) {int affect = 0; try {/ / structured operation ContentValues cv = new ContentValues (); cv.put ("title", title); cv.put ("content", content) Cv.put ("pic", pic); String w [] = {String.valueOf (id)}; affect = mSQLiteDatabase.update ("notebook", cv, "_ id=?", w);} catch (Exception ex) {ex.printStackTrace (); affect =-1;} return affect;}}

Get data example

Private DBManager dm = null;// database management object rivate Cursor cursor = null; dm = new DBManager (this); / / database operation object dm.open (); / / Open database operation object cursor = dm.selectAll (); / / get all data cursor.moveToFirst () / / to move the cursor to * data, you must call int count = cursor.getCount (); / / number of ArrayList contents = new ArrayList (); / / all collections of pictures ArrayList imgs = new ArrayList (); / / all collections of pictures ArrayList items = new ArrayList (); / / all collections of titles ArrayList times = new ArrayList () / / all collections of time for (int I = 0; I

< count; i++){ contents.add(cursor.getString(cursor.getColumnIndex("content"))); imgs.add(cursor.getString(cursor.getColumnIndex("pic"))); items.add(cursor.getString(cursor.getColumnIndex("title"))); times.add(cursor.getString(cursor.getColumnIndex("time"))); //cursor.getInt(cursor.getColumnIndex("_id")) cursor.moveToNext();//将游标指向下一个 } dm.close();//关闭数据操作对象 数据库的并发问题 并发问题是使用数据库过程中最容易碰到的问题，如果在开发中碰到了android.database.SQLException异常，并提示"database is locked"，那很有可能是出现了数据库的死锁导致无法访问。原因是Sqlite会对文件的读写进行加锁，防止数据被破坏。而在Android框架层SqliteDatabase会对所有数据库对象进行加锁保护，一旦出现了指向同一个数据库的多个SqliteDatabase对象同时在多个线程中被使用，那就跳脱了SqliteDatabase锁保护，就会导致数据库出现被锁的异常。因此在实践中，需要保证同时访问数据库的SqliteDatabase对象仅有一个。（可以使用全局变量来保存数据库对象，在整个数据源对象中使用同一个连接） 课外小知识：在Android SDK中提供了工具Sqlite3，在shell模式下，可以对数据库进行增删改查。 cmd->

Adb shell-> sqlite3 /-> sqlite > select * from sqmple

Cloud services for Android data

In essence, cloud storage is to store data on mobile devices to remote servers through the network. In Android, some auxiliary functions are added to make the implementation of the whole process easier. The first is to identify the user through the Google account. In android, the Google account is supported as the identity of the user by default, and each application on the system can obtain the user's login information through the account system. Secondly, with the Google account, developers do not need to build their own background service system.

Cloud data access of Android is uniformly managed by the system service BackupManagerService. When the application submits a backup data request, BackupManagerService will put the request into the backup queue, which will be submitted to the cloud at a certain time according to certain control logic. When a new application is installed on the system, a data recovery event will be triggered. BackupManagerService will extract the corresponding backup data from the cloud based on the application package name and user account to try to recover.

In practice, Android constructs an object derived from android.app.backup.BackupAgentHelper, a subclass of the BackupAgent class, to make it easier to build cloud storage components.

Android does not submit data to the cloud on its own. Developers need to explicitly call android.app.backup.BackupManager 's dataChanged function to trigger.

Like all components, cloud storage components are hosted by the system. This requires putting information about the component in the configuration file.

These are all the contents of the article "how to use Android's database". Thank you for reading! I believe you will gain a lot after reading this article. The editor will update different knowledge for you every day. If you want to learn more knowledge, please pay attention to the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.