For Android, execSQL and rawQuery are used to add, delete, modify, and query data.

Use SQLiteDatabase to operate SQLite Databases
Copy codeThe Code is as follows :/*
Android provides a class named SQLiteDatabase, which encapsulates APIs for database operations. This class can be used to Create, query, and Update data) and Delete operations (CRUD ). For SQLiteDatabase learning, we should master the execSQL () and rawQuery () methods. The execSQL () method can be used to execute SQL statements with changing behaviors such as insert, delete, update, and CREATE TABLE. The rawQuery () method is used to execute select statements.
ExecSQL () method example:
SQLiteDatabase db = ....;
Db.exe cSQL ("insert into person (name, age) values ('test data', 4 )");
Db. close ();
When the preceding SQL statement is executed, a record is added to the person table. In actual application, the "test data" parameter values in the statement are provided on the user input interface, if you splice the content entered by the user into the preceding insert statement as is, when the content entered by the user contains single quotes, the assembled SQL statement will have a syntax error. To solve this problem, you need to escape single quotes, that is, convert single quotes into two single quotes. In some cases, users often enter special SQL symbols such as "&". To ensure that the SQL statement syntax is correct, these special SQL symbols in the SQL statement must be escaped, obviously, it is cumbersome to do this for each SQL statement. The SQLiteDatabase class provides an overloaded execSQL (String SQL, Object [] bindArgs) method. This method can solve the problem mentioned above, this method supports using placeholder parameters (?). Example:
SQLiteDatabase db = ....;
Db.exe cSQL ("insert into person (name, age) values (?,?) ", New Object [] {" Test Data ", 4 });
Db. close ();
The first parameter of the execSQL (String SQL, Object [] bindArgs) method is the SQL statement, and the second parameter is the value of the placeholder parameter in the SQL statement, the order of parameter values in the array must correspond to the position of the placeholder.
*/

Copy codeThe Code is as follows: public class DatabaseHelper extends SQLiteOpenHelper {
// The class is not instantiated. It cannot be used as a parameter of the parent class constructor and must be declared as static.
Private static final String name = "itcast"; // Database name
Private static final int version = 1; // database version
Public DatabaseHelper (Context context ){
// The third parameter CursorFactory specifies the factory class for obtaining a cursor instance during query execution. If it is set to null, it indicates that the default factory class is used.
Super (context, name, null, version );
}
@ Override public void onCreate (SQLiteDatabase db ){
Db.exe cSQL ("create table if not exists person (personid integer primary key autoincrement, name varchar (20), age INTEGER )");
}
@ Override public void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion ){
Db.exe cSQL ("alter table person ADD phone VARCHAR (12) NULL"); // ADD a column to the TABLE
// Drop table if exists person Delete TABLE
}
}
// In actual project development, when the database table structure is updated, you should avoid data loss in the data/database.

Copy codeThe Code is as follows :/*
Android provides a class named SQLiteDatabase, which encapsulates APIs for database operations. This class can be used to Create, query, and Update data) and Delete operations (CRUD ). For SQLiteDatabase learning, we should master the execSQL () and rawQuery () methods. The execSQL () method can be used to execute SQL statements with changing behaviors such as insert, delete, update, and CREATE TABLE. The rawQuery () method is used to execute select statements.
ExecSQL () method example:
SQLiteDatabase db = ....;
Db.exe cSQL ("insert into person (name, age) values ('test data', 4 )");
Db. close ();
When the preceding SQL statement is executed, a record is added to the person table. In actual application, the "test data" parameter values in the statement are provided on the user input interface, if you splice the content entered by the user into the preceding insert statement as is, when the content entered by the user contains single quotes, the assembled SQL statement will have a syntax error. To solve this problem, you need to escape single quotes, that is, convert single quotes into two single quotes. In some cases, users often enter special SQL symbols such as "&". To ensure that the SQL statement syntax is correct, these special SQL symbols in the SQL statement must be escaped, obviously, it is cumbersome to do this for each SQL statement. The SQLiteDatabase class provides an overloaded execSQL (String SQL, Object [] bindArgs) method. This method can solve the problem mentioned above, this method supports using placeholder parameters (?). Example:
SQLiteDatabase db = ....;
Db.exe cSQL ("insert into person (name, age) values (?,?) ", New Object [] {" Test Data ", 4 });
Db. close ();
The first parameter of the execSQL (String SQL, Object [] bindArgs) method is the SQL statement, and the second parameter is the value of the placeholder parameter in the SQL statement, the order of parameter values in the array must correspond to the position of the placeholder.
*/

Copy codeThe Code is as follows :/*
RawQuery () of SQLiteDatabase is used to execute the select statement. The example is as follows: SQLiteDatabase db = ....;
Cursor cursor = db. rawQuery ("select * from person", null );
While (cursor. moveToNext ()){
Int personid = cursor. getInt (0); // obtain the value of the first column. The index of the first column starts from 0.
String name = cursor. getString (1); // obtain the value of the second column
Int age = cursor. getInt (2); // get the value of the third column
}
Cursor. close ();
Db. close ();
The first parameter of the rawQuery () method is the select statement. The second parameter is the value of the placeholder parameter in the select statement. If the select statement does not use a placeholder, this parameter can be set to null. An example of a select statement with placeholder parameters is as follows:
Cursor cursor = db. rawQuery ("select * from person where name like? And age =? ", New String [] {" % Chuanzhi % "," 4 "});
Cursor Is a result set Cursor used for Random Access to the result set. If you are familiar with jdbc, Cursor works very similar to the ResultSet in JDBC. You can use the moveToNext () method to move the cursor from the current row to the next row. If the last row of the result set has been moved, false is returned; otherwise, true is returned. In addition, Cursor also has the commonly used moveToPrevious () method (used to move the Cursor from the current row to the previous row. If the first row of the result set has been moved, the return value is false; otherwise, the return value is true), moveToFirst () method (used to move the cursor to the first row of the result set. If the result set is empty, the return value is false; otherwise, it is true) and moveToLast () method (used to move the cursor to the last row of the result set. If the result set is empty, the return value is false. Otherwise, the return value is true ).

*/

Copy codeThe Code is as follows :/*
In addition to the execSQL () and rawQuery () methods described earlier, SQLiteDatabase also provides operation methods for adding, deleting, updating, and querying: insert (), delete (), update (), and query (). These methods are actually used by cainiao who are not familiar with SQL syntax. For programmers who are familiar with SQL syntax, execSQL () and rawQuery () are used directly () to add, delete, update, and query data.
The Insert () method is used to add data. The data of each field is stored using ContentValues. ContentValues is similar to MAP. Compared with MAP, it provides the put (String key, Xxx value) and getAsXxx (String key) methods for data access. key is the field name and value is the field value, xxx refers to various common data types, such as String and Integer.
SQLiteDatabase db = databaseHelper. getWritableDatabase ();
ContentValues values = new ContentValues ();
Values. put ("name", "Test Data ");
Values. put ("age", 4 );
Long rowid = db. insert ("person", null, values); // returns the row number of the newly added record, regardless of the primary key id.
No matter whether the third parameter contains data, the Insert () method will inevitably add a record. If the third parameter is Null, a record with Null values other than the primary key will be added. The Insert () method actually adds data by constructing an insert SQL statement. The second parameter of the Insert () method is used to specify the name of the null field. I believe you will be confused about this parameter, what is the role of this parameter? If the value of the third parameter is Null or the number of elements is 0, the Insert () method requires that you add a record with Null values in other fields except the primary key, to meet the needs of SQL syntax, the insert statement must be given a field name, for example, insert into person (name) values (NULL). If the field name is not given, the insert statement is like this: insert into person () values (), apparently this does not meet the standard SQL syntax. For field names, we recommend that you use a field other than the primary key. If an INTEGER type primary key field is used, after executing an insert statement similar to insert into person (personid) values (NULL, the value of this primary key field is not NULL. If the value of the third parameter is not Null and the number of elements is greater than 0, you can set the second parameter to null.
*/

Copy codeThe Code is as follows :/*
Use of the delete () method:
SQLiteDatabase db = databaseHelper. getWritableDatabase ();
Db. delete ("person", "personid <? ", New String [] {" 2 "});
Db. close ();
The above code deletes a record whose personid is less than 2 from the person table.
Use of the update () method:
SQLiteDatabase db = databaseHelper. getWritableDatabase ();
ContentValues values = new ContentValues ();
Values. put ("name", "test data"); // The key is the field name, and the value is the value.
Db. update ("person", values, "personid =? ", New String [] {" 1 "});
Db. close ();
The code above is used to change the value of the name field of the record whose personid is equal to 1 in the person table to "test data ".

*/

Copy codeThe Code is as follows :/*
The query () method splits the select statement into several components and serves as the input parameter of the method:
SQLiteDatabase db = databaseHelper. getWritableDatabase ();
Cursor cursor = db. query ("person", new String [] {"personid, name, age"}, "name like? ", New String [] {" % Chuanzhi % "}, null, null," personid desc "," 1, 2 ");
While (cursor. moveToNext ()){
Int personid = cursor. getInt (0); // obtain the value of the first column. The index of the first column starts from 0.
String name = cursor. getString (1); // obtain the value of the second column
Int age = cursor. getInt (2); // get the value of the third column
}
Cursor. close ();
Db. close ();
The above code is used to find records whose name field contains "intelligence" from the person table. matched records are sorted in descending order by personid. The first record is skipped for the sorted results and only two records are obtained.
Meanings of parameters in the query (table, columns, selection, selectionArgs, groupBy, having, orderBy, limit) method:
Table: table name. It is equivalent to the part after the select statement from keyword. For multi-table join queries, you can use commas to separate the two table names.
Columns: name of the column to be queried. It is equivalent to the part after the select keyword of the select statement.
Selection: the query Condition Clause, which is equivalent to the part after the where keyword of the select statement. The placeholder "?" can be used in the Condition Clause.
SelectionArgs: it corresponds to the placeholder value in the selection statement. 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: equivalent to the part after the select statement group by keyword
Having: equivalent to the part after the having keyword of the select statement
OrderBy: equivalent to the part after the select statement order by keyword, such as: personid desc, age asc;
Limit: Specifies the offset and the number of records obtained, which is equivalent to the part following the limit keyword of the select statement.
*/

Copy codeThe Code is as follows: package com. zyq. db;
Import android. app. Activity;
Import android. OS. Bundle;
Public class MainActivity extends Activity
{
@ Override
Public void onCreate (Bundle savedInstanceState)
{
Super. onCreate (savedInstanceState );
SetContentView (R. layout. main );
}
}

Copy codeThe Code is as follows: package com. zyq. db;
Import java. util. List;
Import android. test. AndroidTestCase;
Import android. util. Log;
Import com. zyq. service. DBOpenHelper;
Import com. zyq. service. PersonService;
Import com. zyq. voo. Person;

/**
* The test method passes the Junit unit test.
* 1.> instantiate the test class
* 2.> pass context information related to the application to the test instance.
* 3.> Test Method
* @ Author Administrator
*
*/
Public class PersonServiceTest extends AndroidTestCase
{
Private final static String TAG = "PersonServiceTest ";

/**
* Test database creation
* @ Throws Throwable
*/
Public void testCreateDB () throws Throwable
{
DBOpenHelper dbOpenHelper = new DBOpenHelper (this. getContext ());
DbOpenHelper. getReadableDatabase (); // Create and/or open a database.
}
/**
* Add a record for the test.
* @ Throws Throwable
*/
Public void testSave () throws Throwable
{
PersonService personService = new PersonService (this. getContext ());
PersonService. save (new Person ("zhangsan", "1360215320 "));
PersonService. save (new Person ("lisi", "1123 "));
PersonService. save (new Person ("lili", "232 "));
PersonService. save (new Person ("wangda", "123123 "));
PersonService. save (new Person ("laozhu", "234532 "));
}
/**
* Search for a record
* @ Throws Throwable
*/
Public void testFind () throws Throwable
{
PersonService personService = new PersonService (this. getContext ());
Person person = personService. find (1 );
Log. I (TAG, person. toString ());
}
/**
* Test to update a record
* @ Throws Throwable
*/
Public void testUpdate () throws Throwable
{
PersonService personService = new PersonService (this. getContext ());
Person person = personService. find (1 );
Person. setName ("lisi ");
PersonService. update (person );
}
/**
* Test to obtain the number of all records
* @ Throws Throwable
*/
Public void testGetCount () throws Throwable
{
PersonService personService = new PersonService (this. getContext ());
Log. I (TAG, personService. getCount () + "********");
}
/**
* Test page
* @ Throws Throwable
*/
Public void testScroll () throws Throwable
{
PersonService personService = new PersonService (this. getContext ());
List <Person> persons = personService. getScrollData (3, 3 );
For (Person person: persons)
{
Log. I (TAG, person. toString ());
}
}
/**
* Test the deletion of a record.
* @ Throws Throwable
*/
Public void testDelete () throws Throwable
{
PersonService personService = new PersonService (this. getContext ());
PersonService. delete (5 );
}
}

Copy codeThe Code is as follows: package com. zyq. service;
Import android. content. Context;
Import android. database. sqlite. SQLiteDatabase;
Import android. database. sqlite. SQLiteOpenHelper;
Public class DBOpenHelper extends SQLiteOpenHelper
{
/**
* If you want to add an additional field (required)
* The version number can be changed but must be greater than or equal to 1.
* After the version number is changed, the system checks whether it was last created based on the version number (whether the current version number is consistent with the passed version number)
* If not, the onUpgrade () method will be executed.
* @ Param context
*/
Public DBOpenHelper (Context context)
{
Super (context, "zyq. db", null, 2 );
}
/**
* The first method called during database creation
* Suitable for creating table Structures
*/
@ Override
Public void onCreate (SQLiteDatabase db)
{
Db.exe cSQL ("create table person (personid integer primary key autoincrement, name varchar (20)"); // CREATE a TABLE
}
/**
* Call to update the table structure when the database version number changes
* Application upgrade
*/
@ Override
Public void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion)
{
Db.exe cSQL ("alter table person ADD phone VARCHAR (12) NULL"); // ADD a column to the TABLE
}
}

Copy codeThe Code is as follows: <? Xml version = "1.0" encoding = "UTF-8"?>
<Manifest xmlns: android = "http://schemas.android.com/apk/res/android"
Package = "com. zyq. db"
Android: versionCode = "1"
Android: versionName = "1.0" type = "codeph" text = "/codeph">
<Application android: icon = "@ drawable/icon" android: label = "@ string/app_name">
<Uses-library android: name = "android. test. runner"/>
<Activity android: name = ". MainActivity"
Android: label = "@ string/app_name">
<Intent-filter>
<Action android: name = "android. intent. action. MAIN"/>
<Category android: name = "android. intent. category. LAUNCHER"/>
</Intent-filter>
</Activity>
</Application>
<Uses-sdk android: minSdkVersion = "8"/>
<Instrumentation android: name = "android. test. InstrumentationTestRunner"
Android: targetPackage = "com. zyq. db" android: label = "Tests for My App"/>
</Manifest>

Copy codeThe Code is as follows: package com. zyq. service;
Import java. util. ArrayList;
Import java. util. List;
Import android. content. Context;
Import android. database. Cursor;
Import android. database. sqlite. SQLiteDatabase;
Import com. zyq. voo. Person;
Public class PersonService
{
Private DBOpenHelper helper;
Public PersonService (Context context)
{
Helper = new DBOpenHelper (context );
}
/**
* Adds a record.
* @ Param person
*/
Public void save (Person person)
{
SQLiteDatabase db = helper. getWritableDatabase (); // Create and/or open a database that will be used for reading and writing
Db.exe cSQL ("insert into person (name, phone) values (?,?) ", New Object [] {person. getName (). trim (), person. getPhone (). trim ()}); // use placeholders for translation
// Db. close (); no database connection. It can improve performance because the operation mode during database creation is private.
// Indicates the database, which can only be accessed by a single user by the application and can maintain a persistent connection.
}
/**
* Update a record
* @ Param person
*/
Public void update (Person person)
{
SQLiteDatabase db = helper. getWritableDatabase ();
Db.exe cSQL ("update person set phone = ?, Name =? Where personid =? ",
New Object [] {person. getPhone (). trim (), person. getName (). trim (), person. getId ()});
}
/**
* Query a record by ID
* @ Param id
* @ Return
*/
Public Person find (Integer id)
{
SQLiteDatabase db = helper. getReadableDatabase ();
Cursor cursor = db. rawQuery ("select * from person where personid =? ", New String [] {id. toString ()}); // Cursor and ResultSet are similar
If (cursor. moveToFirst () // Move the cursor to the first row. This method will return false if the cursor is empty.
{
Int personid = cursor. getInt (cursor. getColumnIndex ("personid "));
String name = cursor. getString (cursor. getColumnIndex ("name "));
String phone = cursor. getString (cursor. getColumnIndex ("phone "));

Return new Person (personid, name, phone );
}
Return null;
}
/**
* Delete a record
* @ Param id
*/
Public void delete (Integer id)
{
SQLiteDatabase db = helper. getWritableDatabase ();
Db.exe cSQL ("delete from person where personid =? ",
New Object [] {id });
}

/**
* Get the number of records
* @ Return
*/
Public long getCount ()
{
SQLiteDatabase db = helper. getReadableDatabase ();
Cursor cursor = db. rawQuery ("select count (*) from person", null );
Cursor. moveToFirst ();
Return cursor. getLong (0 );
}
/**
* Paging query method the SQL statement is the same as the MySQL syntax.
* @ Return
*/
Public List <Person> getScrollData (int offset, int maxResult)
{
List <Person> persons = new ArrayList <Person> ();
SQLiteDatabase db = helper. getReadableDatabase ();
Cursor cursor = db. rawQuery ("select * from person limit ?,? ",
New String [] {String. valueOf (offset), String. valueOf (maxResult )});
While (cursor. moveToNext ())
{
Int personid = cursor. getInt (cursor. getColumnIndex ("personid "));
String name = cursor. getString (cursor. getColumnIndex ("name "));
String phone = cursor. getString (cursor. getColumnIndex ("phone "));

Persons. add (new Person (personid, name, phone ));
}

Return persons;
}
}

Copy codeThe Code is as follows: package com. zyq. voo;
Public class Person
{
Private Integer id;
Private String name;
Private String phone;

Public Person (int personid, String name, String phone)
{
This. id = personid;
This. name = name;
This. phone = phone;
}

Public Person (String name, String phone)
{
This. name = name;
This. phone = phone;
}
Public String toString ()
{
Return "Person [id =" + id + ", name =" + name + ", phone =" + phone + "]";
}
Public Integer getId ()
{
Return id;
}
Public void setId (Integer id)
{
This. id = id;
}
Public String getName ()
{
Return name;
}
Public void setName (String name)
{
This. name = name;
}
Public String getPhone ()
{
Return phone;
}
Public void setPhone (String phone)
{
This. phone = phone;
}

}