SQLite database for Android Development

SQLite database for Android Development

SQLite database for Android Development

Please respect others' labor achievements. Reprinted with the source: detailed explanation of SQLite database for Android Development

Http://blog.csdn.net/fengyuzhengfan/article/details/40194393

The Android system integrates a lightweight Database: SQLite, SQLite does not want to become a professional database like Oracle or MySQL. SQLite is only an embedded database engine. It is applicable to the access to a proper amount of data on devices with limited resources (such as mobile phones and PDAs.

Although SQLite supports the vast majority of SQL92 syntaxes and allows developers to use SQL statements to operate data in the database, SQLite does not need to install or start the server process like Orade or MySQL databases. SQLite database is just a file.

Essentially, SQLite is only a more convenient file operation method. Later we will see that when an application creates or opens an SQLite database, it simply opens a file to prepare for reading and writing, some people say that SQLite is a bit like Microsoft's Access (in fact, SQLite is much more powerful ).

1. SQLiteDatabase Introduction

Android provides SQLiteDatabase to represent a database (the underlying layer is a database file>. Once the application obtains the SQLiteDatabase object representing the specified database, it can manage and operate the database through the SQLiteDatabase object.

2. Open the method for obtaining the SQLiteDatabase creation:

1) staticSQLiteDatabase openDatabase (Stringpath, SQLiteDatabase. CursorFactory factory, intflags): Open the SQLite database represented by the path file.

2) staticSQLiteDatabase openOrCreateDatabase (File file, SQLiteDatabase. CursorFactory factory): Open or create (if not exist) The SQLite database represented by the file File.

3) staticSQLiteDatabase openOrCreateDatabase (String path, SQLiteDatabase. CursorFactory factory): Open or create (if not exist) The SQLite database represented by the path file.

3. The methods for operating SQLiteDatabase are as follows:

1) execSQL (Stringsql, Object [] bindArgs): Execute an SQL statement with a placeholder.

2) execSQL (String SQL): Execute an SQL statement.

3) insert (Stringtable, String nullColumnHack, ContentValues values): insert data to the execution table.

4) update (Stringtable, ContentValues values, String whereClause, String [] whereArgs): Specify the data in the new table.

5) delete (Stringtable, String whereClause, String [] whareArgs): deletes specific data from a specified table.

6) Cursorquery (String table, String [] columns, String selection, String [] selection Args, String groupBy, String having, String orderBy): queries the execution data table.

7) Cursorquery (String table, String [] columns, String selection, String [] selectionArgs, String groupBy, String having, String orderBy, String limit}: queries the execution data table. The Limit parameter controls a maximum of several query records (used to control paging parameters ).

8) Cursorquery (boolean distinct, String table, String [] columns, String selection, String [] selectionArgs, String groupBy, String having, String orderBy, String limit ): execute the explain query statement on the specified table. The first parameter controls whether to remove duplicate values.

9) rawQuery (Stringsql, String [] selectionArgs): executes an SQL query with placeholders.

10) beginTransaction (): Start the transaction.

11) endTransaction (): ends the transaction.

From the above method, it is not difficult to see that SQLiteDatabase is actually a bit similar to the JDBC Connection interface, but SQLiteDatabase provides more methods: such as insert, update, delete, query, etc, in fact, these methods can be completed by executing SQL statements, but Android helps developers operate data in a simpler way considering that some developers are not familiar with SQL syntax.

4. Operate Cursor

The preceding query method returns a Cursor object. In Android, Cursor performs the JDBC ResultSet. Cursor also provides the following method to move the record pointer of the query result.

1) move (int offset): move the record pointer up or down to the specified number of rows. If the offset value is positive, it moves down. If the offset value is negative, it moves up.

2) booleanmoveToFirst (): moves the record pointer to the first row. If the row is successfully moved, true is returned.

3) booleanmoveToLast (): moves the record pointer to the last row. If the row is successfully moved, true is returned.

4) booleanmoveToNext (): moves the record pointer to the next row. If the row is successfully moved, true is returned.

5) booleanmoveToPosition (int position): moves the record pointer to the specified row. If the row is successfully moved, true is returned.

6) booleanmoveToPrevious (): Move the record pointer to the previous row. If the row is successfully moved, true is returned.

-Once the record pointer is moved to the specified row, you can call the getXxx () method of Cursor to obtain the data of the specified column of the row.

In fact, if you have JDBC programming experience, you can use SQLiteDatabase as a JDBC: a mix of Connection and Statement in SQLiteDatabase because it represents a Connection to the database and can be directly used to execute SQL operations; in Android, Cursor can be used as a ResultSet, and Cursor provides more convenient methods to operate the result set.

5. create databases and tables

As mentioned above, you can use the static method of SQLiteDatabase to open or create a database, for example, the following code:

SQLiteDatabase. openOrCreateDatabase ("/mnt/db/temp. db3", null );

The above code is used to open or create an SQLite database, if temp is in the/mnt/db/directory. db3 files (this file is a database) exist, so the program opens the database: if the file does not exist, the above Code will create temp under this directory. db3 file (corresponding to the database ).

The above Code does not specify the SQLiteDatabase. CursorFactory parameter. this parameter is a factory used to return Cursor. If this parameter is specified as null, it means that the default factory is used.

The above code returns a SQLiteDatabase object. execSQL of this object can execute any SQL statement.

Use the following code to create a data table in the program:

// Define the table creation statement

Create tabletb_news (

Id integer primarykey autoincrement,

Title varchar (100) not null,

Content varchar (2000 ));

Execute the above Code in the program to create a data table in the database.

6. use SQL statements to operate SQLite Databases

As mentioned above, the execSQL method of SQLiteDatabase can execute arbitrary SQL statements, including SQL statements with placeholders. However, because this method does not return values, it is generally used to execute DDL statements or DML statements. to execute an explain query statement, you can call the rawQuery (String SQL, String [] selectionArgs) method of SQLiteDatabase. For example, the following code can be used to execute DML statements,

// Execute the insert statement

String SQL = "insert into tb_news (title, content, publishDate) values (?,?,?) ";

Db.exe cSQL (SQL,NewObject [] {news. getTitle (), news. getContent (), news. getPublishDate ()});

Tip: SQLite allows you to save various types of data to any type field. Developers do not need to declare the data type used by this field. For example, a program can store string-type values in INTEGER fields, or store numeric values in Boolean fields ...... One exception is that fields defined as integerprimary key can only store 64-bit integers. SQLite produces errors when saving data of other types than Integers to such fields.

Since SQLite allows you to ignore the actual data type of the underlying data column when storing data, you can use the type declaration following the data column when writing the table creation statement, for example, the following SQL statement is correct for SQLite.

Create table my_test

(_ Id integer primary key autoincrement,

Name, pwd)

7. database operations using the Android Database

Android SQLiteDatabase provides insert, upate, delete, or query statements to operate databases.

1. insert a record using the insert method

The insert method signature of SQLiteDatabase is longinsert (String table, String nullColumnHack, ContentValuesvalues). The parameters of this insert method are described as follows.

Table: the name of the table to be inserted.

NullColumnHack: name of the column that forcibly inserts a null value.

Values: indicates the data record by a row.

The insert method inserts a row of records stored using ComentValues. ContentValues is similar to Map, which provides put Key is the column name of the data column. This method is used to store data and the getAsXxx (String key) method is used to retrieve data.

For example, the following statement:

ContentValues values = new ContentValues ();

Values. put ("name", "Sun Wukong ");

Values. put ("age", 500 );

// Return the row number of the newly added record. The row number is an internal value and has nothing to do with the primary key 1 ^ 1. If the column number is incorrect, return 1.

Long rowid = db. insert ("person_in", null, values );

No matter whether the third parameter contains data, the insert () method always adds a record. If the third parameter is null, a record with null values except the primary key is added.

The bottom layer of the insert () method is still inserted by constructing an insert SQL statement. Therefore, the generated SQL statement is always the following statement:

// The value of the key-value Pair in ContentValue determines the following key-value pair.

Insert <表名> (Key1, key 2...) values (valuel, value2 ...)

If the third parameter is null or the number of key-value pairs is 0, an insert statement will be generated according to the insert method. The insert statement is:

Insert <表名> () Values ()

The preceding SQL statement is obviously faulty. To meet the SQL syntax requirements, the insert statement must specify a column name, for example, insert into person (name) values (null ), the name column is specified by the second parameter. It can be seen that when ComentValues is null or it contains the number of key-value pairs is (), the second parameter will take effect.

Generally, the column name specified by the second parameter should not be the column name of the primary key column, nor should it be the column name of non-empty columns. Otherwise, inserting nul into these data columns will cause an exception.

2. update records using the update Method

The signature of the update method of SQLiteDatabase is update (String table, ContentValues values, String whereClause, String [] whereArs). The parameters of this update method are described as follows:

Table: name of the table to update data.

Values: indicates the data to be updated.

WhereClause: records that meet the whereClause clause will be updated.

WhereArgs: Used to input parameters for the whereClause clause.

This method returns the number of records affected by this update statement.

Instance:

ContentValues values =NewContentValues ();

Values. put ("title", news. getTitle ());

Values. put ("content", news. getContent ());

Db. update ("tb_news", values, "id =? ",NewString [] {String.ValueOf(News. getId ())});

3. delete a record using the delete Method

The delete method signature of SQLiteDatabase is delete (String table, String whereClause, String [] whereArgs). The deletion parameters are described as follows.

Table: the name of the table to be deleted.

WhereClause: records that meet the whereClause clause will be deleted.

WhereArgs: Used to input parameters for the whereClause clause.

This method returns the number of records affected by this delete statement.

Instance:

Db = helper. getWritableDatabase ();

Db.exe cSQL ("delete from tb_news where id =? ",NewString [] {String.ValueOf(NewsId )});

Query records using the query method

The signature of the query method of SQLiteDatabase is Cursorquery (boolean distinct, String table, String []

Columns, String selection, Stringl] selecrionArgs, String groupBy, String having. String orderBy, String limit). The parameters of this query method are described as follows,

Distinct: whether to remove duplicate records,

Table: the name of the table where the query data is executed.

Columns: name of the column to be queried. It is equivalent to the part s after the select keyword of the select statement.

Selection: The Condition Clause of the explain query, which is equivalent to the part after the where keyword of the select statement.

Placeholders are allowed in a sentence.

SelectionArgs: Used to input a parameter value for the placeholder in the selection clause. The position of the value in the array must be the same as that of the placeholder in the statement. Otherwise, an exception occurs.

GroupBy: used to control groups? It is equivalent to the part following the group by keyword of the select statement.

Having: used to filter groups. It is equivalent to the part after the having keyword of the select statement.

OrderBy: used to sort records. It is equivalent to the part after the select statement order by keyword, such as: personid desc, age asc;

Limit: used for paging, which is equivalent to the part after the select statement limit keyword.

Instance:

/**

* Retrieve paging data

*@ ParamCurrent page number of currentPage

*@ ParamPageSize data volume per page

*@ Return

*/

PublicList GetNewsByPage ( IntCurrentPage, IntPageSize ){

Db = helper. getReadableDatabase ();

List List = NewArrayList ();

String start = String.ValueOf(CurrentPage-1) * pageSize );

Cursor cursor = db. query (True, "Tb_news ",Null,Null,Null,Null,Null, "Id asc", start + "," + String.ValueOf(PageSize ));

// StringSQL= "Select * from tb_news order by idAscLimit ?,? ";

// Cursorcursor = db. rawQuery (SQL, New String [] {start, String. valueOf (pageSize )});

While(Cursor. moveToNext ()){

IntId = cursor. getInt (cursor. getColumnIndex ("id "));

Stringtitle = cursor. getString (cursor. getColumnIndex ("title "));

Stringcontent = cursor. getString (cursor. getColumnIndex ("content "));

StringdateString = cursor. getString (cursor. getColumnIndex ("publishDate "));

Date publishDate =Null;

Try{

PublishDate = Utils.ConvertStr2Date(DateString );

}Catch(ParseException e ){

//TODOAuto-generated catch block

E. printStackTrace ();

}

List. add (NewNews (id, title, content, publishDate ));

}

ReturnList;

}

8. Transactions

SQLiteDatabase contains the following two methods to control transactions.

BeginTransaction (): starts the transaction.

EndTransaction (): ends the transaction.

In addition, SQLileDatabase also provides the following methods to determine whether the current context is in the transaction environment

InTransaction (): if the current context is in the transaction, true is returned. Otherwise, false is returned.

When the program executes the endTiansaction () method, it will end the transaction-whether to commit the transaction or roll back the transaction? This depends on whether SQLiteDatabase has called the setTransactionSuccessful () method to set the transaction flag. if the transaction is successfully set when the method is called during the execution of the program transaction, the transaction is committed; otherwise, the program will return to the commit transaction. The sample code is as follows:

// Transaction Processing

PublicVoidTran (IntId1,IntId2,IntDes ){

SQLiteDatabase db = helper. getWritableDatabase ();

// Write transactions in this syntax format

Db. beginTransaction (); // start the transaction

Try{

Db.exe cSQL ("update tb_news set account = account -? Whereid =? ",NewInteger [] {des, id1 });

//IntI = 1/0;

Db.exe cSQL ("update tb_news set account = account +? Whereid =? ",NewInteger [] {des, id2 });

Db. setTransactionSuccessful (); // sets the transaction success flag.

}Finally{

Db. endTransaction (); // end transaction: Check whether the transaction is successful. if the transaction is committed, roll back if the transaction fails.

}

}

9. SQLiteOpenHelper

SQLiteOpenHelper is a tool class provided by Android for database management. It can be used to manage database creation and revision wood update. The general usage is to create a subclass of SQLiteOpenHelper and extend its onCreate (SQLiteDatabasedb) and onUpgrade (SQLiteDatabasedb, intoldVersion, intnewVersion) methods.

SQLiteOpenHelper contains the following common methods.

Synchronized SQLiteDatabase getReadableDatabase (): Open the SQLiteDatabase object of the database in read/write mode.

Synchronized SQLiteDatabase getWritableDatabase (): Open the SQLiteDatabase object of the database in write mode.

Abstract void onCreate (SQLiteDatabase db): This method is called back when a database is created for the first time.

Abstract void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion): calls back this method when the database version is updated.

Synchronized void close (): close all opened sqlitedatabases.

From the above method introduction, it is not difficult to see that SQLiteOpenHelper provides the getReadableDatabase () and getWritableDatabase () Methods to open database connections, and provides the close method to close database connections, what developers need to do is to rewrite the two abstract methods.

OnCreate (SQLiteDatabase db ):

Used to generate a database table when you use the software for the first time. If the database does not exist when you call the getWritableDatabase () or getReadableDalabase () method of SQLiteOpenHelper to obtain the SQLiteDatabase instance used to operate the database, the Android system automatically generates a database and calls the onCreateO method. The onCreateO method is called only when the database is generated for the first time, in the onCreateO method, you can generate the database table structure and add the initialization data used by some applications.

OnUpgrade (SQLiteDatabase db, int oldVersion, int newVersion ):

Update the database table structure when upgrading the software. The method is called when the database version changes. oldVersion indicates the previous version number and newVersion indicates the current version number of the database. So where can I specify the database version number? When a program creates a SQLiteOpenHelper object, it must specify a version parameter, which determines the version of the database used-that is, the database version is controlled by the programmer. The onUpgrade (SQLiteDatabase db, int oldVersion, int new Version) method is automatically triggered as long as the specified database Version number is higher than the previous Version number when creating SQLiteOpenHdper, in the onUpgrade () method, the program can judge based on the original number and the target version number to update the required table structure based on the version number.

Tip: In fact, when the application updates the table structure, the upgrade may fail because of the existing data. In this case, the program may need to convert the data to clear the records in the data table first, next to the data! Update the table. After the data table is updated, save the data.

Once the SQLiteOpenHelper object is obtained, the program does not need to use the static method of SQLiteDatabase to create the SQLiteDatabase instance, and you can use the getWritableDatabase () or getReadableDatabase () method to obtain a SQLiteDatabase instance used to operate the database.

The getWritableDatabase () method opens the database in read/write mode. Once the disk space of the database is full, the database can only read but cannot write. If getWritableDatabase () is used to open the database, an error occurs. The getReadableDatabase () method first opens the database in read/write mode. If the disk space of the database is full, the database fails to be opened. When the disk space of the database is fully occupied, the database will continue to be opened in read-only mode.

Please respect others' labor achievements. Reprinted with the source: detailed explanation of SQLite database for Android Development

Http://blog.csdn.net/fengyuzhengfan/article/details/40194393