Reload address: http://www.cnblogs.com/ldq2016/p/5237694.html

SQLite plays an important role in Android development. There are many online tutorials for SQLite, but most of them are not very comprehensive.I have summarized some common methods of SQLite and shared them with you through the competition in the forum.

Introduction to SQLite

1. Introduction to SQLite

SQLite is a lightweight database and an ACID-compliant relational database management system. It is designed to be embedded and is currently used in many embedded products. It consumes very low resources. In embedded devices, only a few hundred K s of memory may be needed.It supports mainstream operating systems such as Windows/Linux/Unix, and can be combined with many programming languages, such as Tcl, PHP, Java, C++,.Net, and ODBC interfaces. It also processes faster than Mysql and PostgreSQL, two of the world-famous open source database management systems.

2. Features of SQLite:

• Lightweight
  
  

Unlike database software in C/S mode, SQLite is an in-process database engine, so there are no clients or servers for the database.Using SQLite generally requires only a dynamic library with it. Enjoy all its functions.And the size of that dynamic library is very small. Take version 3.6.11 for example, 487KB for Windows and 347KB for Linux.

• Installation is not required
  
  

The core engine of SQLite itself does not rely on third-party software and does not require "installation" to use it.A little like that green software.

• Single file
  
  

All the information in the database (such as tables, views, etc.) is contained in one file.This file can be freely copied to another directory or other machine.

• Cross Platform/Portability
  
  

In addition to the mainstream operating systems windows and linux, SQLite supports other operating systems that are not commonly used.

• Weakly typed fields
  
  

Data in the same column can be of different types

• Open Source
  
  

This trust everybody understands!!!!!!!!!!!

3.SQLite data type

Generally, data is of a fixed static data type, while SQLite is of a dynamic data type, which is automatically determined by the stored values.SQLite has the following five common data types:

NULL: This value is null

VARCHAR(n): A string of variable length with a maximum length of n, n not exceeding 4000.

CHAR(n): A string of fixed length n, n cannot exceed 254.

INTEGER: Values are identified as integers and can be stored as 1,2,3,4,5,6,7,8, in turn, depending on their size.

REAL: All values are floating numbers stored as 8 bytes of IEEE floating tag ordinal.

TEXT: Value is a text string, stored using database encoding (TUTF-8, UTF-16BE or UTF-16-LE).

BLOB: Values are BLOB data blocks that are stored in the input data format.Store as you type without changing formatting.

DATA: Include year, month, date.

TIME: Includes hours, minutes, seconds.

Believe that children's shoes who have learned database are not unfamiliar with these data types!!!!!!!!

Introduction to SQLiteDatabase

Android provides API s for creating and using SQLite databases.SQLiteDatabase represents a database object and provides some ways to manipulate the database.There is the sqlite3 tool in Android's SDK directory that we can use to create databases, create tables, and execute some SQL statements.Below are common methods for SQLiteDatabase.(

Common methods of SQLiteDatabase

The names of these methods are very graphic for Google.For example, openOrCreateDatabase, we can see from the literal English meaning that it is a way to open or create a database.




1. Open or create a database

Open or create a database in Android using the static method openOrCreateDatabase(String path,SQLiteDatabae.CursorFactory) of SQLiteDatabase.It automatically detects whether the database exists, opens if it exists, and creates one if it does not; a successful creation returns a SQLiteDatabase object, or an exception FileNotFoundException is thrown.

Here is the code to create a database named "stu.db":
openOrCreateDatabase(String  path,SQLiteDatabae.CursorFactory  factory)
Parameter 1. Path to database creation

Parameter 2. Normally set to null

1. db=SQLiteDatabase.openOrCreateDatabase("/data/data/com.lingdududu.db/databases/stu.db",null);  

2. Create tables

The steps to create a table are simple:

• Write SQL statements to create tables
• Call the execSQL() method of SQLiteDatabase to execute the SQL statement
  
  

The following code creates a user table with attributes listed as id (primary key and auto-increment), sname (student name), and snumber (school number)

1. private void createTable(SQLiteDatabase db){   
2. //Create Table SQL Statement
3. String stu_table="create table usertable(_id integer primary key autoincrement,sname text,snumber text)";   
4. //Execute SQL Statement
5. db.execSQL(stu_table);   
6. }  

3. Insert Data
There are two ways to insert data:
(1) SQLiteDatabase insert(String table,String nullColumnHack,ContentValues) method,
Parameter 1 table name,
Parameter 2 Default values for empty columns
Parameter 3 A Map of type ContentValues encapsulating the column name and value;
(2) Write SQL statements to insert data and call the execSQL() method of SQLiteDatabase directly to execute them.
Code for the first method:

1. private void insert(SQLiteDatabase db){   
2. //Instantiate Constant Value
3. ContentValues cValue = new ContentValues();   
4. //Add User Name)
5. cValue.put("sname","xiaoming");   
6. //Add a password)
7. cValue.put("snumber","01005");   
8. //callinsert()Method Insert Data   
9. db.insert("stu_table",null,cValue);   
10. }   

Code for the second method:

1. private void insert(SQLiteDatabase db){   
2. //Insert Data SQL Statement
3. String stu_sql="insert into stu_table(sname,snumber) values('xiaoming','01005')";   
4. //Execute SQL Statement
5. db.execSQL(sql);   
6. }   

4. Delete Data

There are also two ways to delete data:

(1) Call the delete (String table, String where Clause, String[], where Args) method of SQLiteDatabase
Parameter 1 table name
Parameter 2. Delete condition
Parameter 3. Delete condition value array

(2) Write a delete SQL statement and call the execSQL() method of SQLiteDatabase to execute the delete.

Code for the first method:

1. private void delete(SQLiteDatabase db) {   
2. //Delete Conditions)
3. String whereClause = "id=?";   
4. //Delete Conditional Parameters)
5. String[] whereArgs = {String.valueOf(2)};   
6. //Execute Delete
7. db.delete("stu_table",whereClause,whereArgs);   
8. }   

Code for the second method:

1. private void delete(SQLiteDatabase db) {   
2. //Delete SQL Statement
3. String sql = "delete from stu_table where _id = 6";   
4. //Execute SQL Statement
5. db.execSQL(sql);   
6. }   

5. Modify Data

There are two ways to modify data:

(1) Call the update(String table,ContentValues values,String, whereClause, String[], whereArgs) method of SQLiteDatabase
Parameter 1 table name
Parameter 2. Key-Value pairs with row and column ContentValues
Parameter 3. Update condition (where clause)
Parameter 4. Update condition array

(2) Write updated SQL statements and call execSQL of SQLiteDatabase to perform the update.

Code for the first method:

1. private void update(SQLiteDatabase db) {   
2. //Instantiate content value ContentValuesvalues = new ContentValues();   
3. //stayvalues Add content to   
4. values.put("snumber","101003");   
5. //Modify Conditions
6. String whereClause = "id=?";   
7. //Modify Add Parameters
8. String[] whereArgs={String.valuesOf(1)};   
9. //Modify *
10. db.update("usertable",values,whereClause,whereArgs);   
11. }   

Code for the second method:

1. private void update(SQLiteDatabase db){   
2. //Modify SQL Statement
3. String sql = "update stu_table set snumber = 654321 where id = 1";   
4. //Execute SQL
5. db.execSQL(sql);   
6. }   

6. Query Data

Querying data in Android is done through the Cursor class, and when we use the SQLiteDatabase.query() method, we get a Cursor object, which points to each data.It provides many ways to query, as follows:

public  Cursor query(String table,String[] columns,String selection,String[]  selectionArgs,String groupBy,String having,String orderBy,String limit);

The meaning of each parameter explains:

Parameter table:Table name

Parameter columns:Column name array

Parameter selection:Conditional clause, equivalent to where

Parameter selectionArgs: Conditional clause, parameter array

Parameter groupBy:Grouped Column

Parameter has:Grouping condition

Parameter orderBy:Sort Column

Parameter limit:Paging query limit

Parameter Cursor: Return value, equivalent to ResultSet of result set

Cursor is a cursor interface that provides methods to traverse query results, such as moving the pointer method move(), getting the column value method getString(), and so on.

Common Cursor Cursor Methods

Here's how to query the data in the database using Cursor, with the following code:

1. private void query(SQLiteDatabase db) {   
2. //Query Get Cursor
3. Cursor cursor = db.query ("usertable",null,null,null,null,null,null);   
4.   
5. //Determine if the cursor is empty
6. if(cursor.moveToFirst() {   
7. //Traverse Cursor
8. for(int i=0;i<cursor.getCount();i++){   
9. cursor.move(i);   
10. //Get ID)
11. int id = cursor.getInt(0);   
12. //Get User Name)
13. String username=cursor.getString(1);   
14. //Get the password)
15. String password=cursor.getString(2);   
16. //Output User Information System.out.println(id+":"+sname+":"+snumber);   
17. }   
18. }   
19. }  

Code for the second method:

1. private void update(SQLiteDatabase db){   
2. //Modify SQL Statement
3. String sql = "select name frome uset_table while id = 65 group by id  having id<10 order by  ";   
4. //Execute SQL
5. Cursor cursor ＝db.execSQL(sql);   
6. }   

select * from person
select * from person order by id desc
select name from person group by name having count(*)>1
 Paging SQL is similar to mysql in that the following SQL statement takes five records and skips the first three
 select * from Account limit 5 offset 3 or select * from Account limit 3,5

7. Delete the specified table
Write SQL statements that insert data and call the execSQL() method of SQLiteDatabase directly to execute them

1. private void drop(SQLiteDatabase db){   
2. //Delete SQL statements from tables
3. String sql ="DROP TABLE stu_table";   
4. //Execute SQL
5. db.execSQL(sql);   
6. }   

3. SQLiteOpenHelper
This class is an auxiliary class for SQLiteDatabase.This class mainly generates one database and manages the version of the database.When the getWritableDatabase() or getReadableDatabase() methods of this class are called in a program, if there is no data at that time, the Android system will automatically generate a database.SQLiteOpenHelper is an abstract class that we usually inherit and implement three functions within:

1.onCreate(SQLiteDatabase)

This method is called the first time a database is generated, that is, only when a database is created, but there are other cases in which we generally generate database tables.

2.  onUpgrade(SQLiteDatabase，int，int) 
The Android system actively calls this method when the database needs to be upgraded.In general, we delete data tables and create new data tables in this method. Of course, whether we need to do other operations depends entirely on the requirements of the application.

3.  onOpen(SQLiteDatabase):

This is a callback function when opening a database and is not commonly used in programs.

So much has been written, I will use actual examples to illustrate the above.The following example of an operation database implements the creation of a database, the creation of tables, and the addition, deletion, and alteration of a database.
The instance has two classes:
com.lingdududu.testSQLite debugging class
Com.lingdudu.testSQLiteDb Database Auxiliary Class

SQLiteActivity.java

1. package com.lingdududu.testSQLite;  
2.   
3. import com.lingdududu.testSQLiteDb.StuDBHelper;  
4.   
5. import android.app.Activity;  
6. import android.content.ContentValues;  
7. import android.database.Cursor;  
8. import android.database.sqlite.SQLiteDatabase;  
9. import android.os.Bundle;  
10. import android.view.View;  
11. import android.view.View.OnClickListener;  
12. import android.widget.Button;  
13. /* 
14. * @author lingdududu 
15. */  
16. public class SQLiteActivity extends Activity {  
17. /** Called when the activity is first created. */  
18. //Declare buttons
19. private Button createBtn;  
20. private Button insertBtn;  
21. private Button updateBtn;  
22. private Button queryBtn;  
23. private Button deleteBtn;  
24. private Button ModifyBtn;  
25. @Override  
26. public void onCreate(Bundle savedInstanceState) {  
27. super.onCreate(savedInstanceState);  
28. setContentView(R.layout.main);  
29.   
30. //Call creatView method
31. creatView();  
32. //setListener method
33. setListener();   
34. }  
35.   
36. //How to get Button objects from findViewById
37. private void creatView(){  
38. createBtn = (Button)findViewById(R.id.createDatabase);  
39. updateBtn = (Button)findViewById(R.id.updateDatabase);  
40. insertBtn = (Button)findViewById(R.id.insert);  
41. ModifyBtn = (Button)findViewById(R.id.update);  
42. queryBtn = (Button)findViewById(R.id.query);  
43. deleteBtn = (Button)findViewById(R.id.delete);  
44. }  
45.   
46. //Register listening methods for buttons
47. private void setListener(){  
48. createBtn.setOnClickListener(new CreateListener());  
49. updateBtn.setOnClickListener(new UpdateListener());  
50. insertBtn.setOnClickListener(new InsertListener());  
51. ModifyBtn.setOnClickListener(new ModifyListener());  
52. queryBtn.setOnClickListener(new QueryListener());  
53. deleteBtn.setOnClickListener(new DeleteListener());  
54. }  
55.   
56. //How to create a database
57. class CreateListener implements OnClickListener{  
58.   
59. @Override  
60. public void onClick(View v) {  
61. //Create StuDBHelper Object
62. StuDBHelper dbHelper = new StuDBHelper(SQLiteActivity.this,"stu_db",null,1);  
63. //Get a readable SQLiteDatabase object
64. SQLiteDatabase db =dbHelper.getReadableDatabase();  
65. }   
66. }  
67.   
68. //How to update the database
69. class UpdateListener implements OnClickListener{  
70.   
71. @Override  
72. public void onClick(View v) {  
73. //Update of database version, from 1 to 2
74. StuDBHelper dbHelper = new StuDBHelper(SQLiteActivity.this,"stu_db",null,2);  
75. SQLiteDatabase db =dbHelper.getReadableDatabase();  
76. }   
77. }  
78.   
79. //Method of inserting data
80. class InsertListener implements OnClickListener{  
81.   
82. @Override  
83. public void onClick(View v) {  
84.   
85. StuDBHelper dbHelper = new StuDBHelper(SQLiteActivity.this,"stu_db",null,1);  
86. //Get a writable database
87. SQLiteDatabase db =dbHelper.getWritableDatabase();  
88.   
89. //Generate ContentValues object //key:column name, value:value to insert
90. ContentValues cv = new ContentValues();  
91. //Store data in ContentValues object, key-value pair mode
92. cv.put("id", 1);  
93. cv.put("sname", "xiaoming");  
94. cv.put("sage", 21);  
95. cv.put("ssex", "male");  
96. //Call insert method to insert data into database
97. db.insert("stu_table", null, cv);  
98. //Close the database.
99. db.close();  
100. }   
101. }  
102.   
103. //Methods of querying data
104. class QueryListener implements OnClickListener{  
105.   
106. @Override  
107. public void onClick(View v) {  
108.   
109. StuDBHelper dbHelper = new StuDBHelper(SQLiteActivity.this,"stu_db",null,1);  
110. //Get a writable database
111. SQLiteDatabase db =dbHelper.getReadableDatabase();  
112. //Parameter 1: Table Name
113. //Parameter 2: Column to display
114. //Parameter 3:where clause)
115. //The conditional value corresponding to the parameter 4:where clause
116. //Parameter 5: Grouping Method
117. //Parameter 6:having condition
118. //Parameter 7: Sort order
119. Cursor cursor = db.query("stu_table", new String[]{"id","sname","sage","ssex"}, "id=?", new String[]{"1"}, null, null, null);  
120. while(cursor.moveToNext()){  
121. String name = cursor.getString(cursor.getColumnIndex("sname"));  
122. String age = cursor.getString(cursor.getColumnIndex("sage"));  
123. String sex = cursor.getString(cursor.getColumnIndex("ssex"));  
124. System.out.println("query------->" + "Full name:"+name+" "+"Age:"+age+" "+"Gender:"+sex);  
125. }  
126. //Close the database.
127. db.close();  
128. }   
129. }  
130.   
131. //Method of modifying data
132. class ModifyListener implements OnClickListener{  
133.   
134. @Override  
135. public void onClick(View v) {  
136.   
137. StuDBHelper dbHelper = new StuDBHelper(SQLiteActivity.this,"stu_db",null,1);  
138. //Get a writable database
139. SQLiteDatabase db =dbHelper.getWritableDatabase();  
140. ContentValues cv = new ContentValues();  
141. cv.put("sage", "23");  
142. //where clause'?'is a placeholder, corresponding to the following'1',.
143. String whereClause="id=?";  
144. String [] whereArgs = {String.valueOf(1)};  
145. //Parameter 1 is the name of the table to be updated
146. //Parameter 2 is a ContentValeus object
147. //Parameter 3 is the where clause
148. db.update("stu_table", cv, whereClause, whereArgs);  
149. }  
150. }  
151.   
152. //Method of deleting data
153. class DeleteListener implements OnClickListener{  
154.   
155. @Override  
156. public void onClick(View v) {  
157.   
158. StuDBHelper dbHelper = new StuDBHelper(SQLiteActivity.this,"stu_db",null,1);  
159. //Get a writable database
160. SQLiteDatabase db =dbHelper.getReadableDatabase();  
161. String whereClauses = "id=?";  
162. String [] whereArgs = {String.valueOf(2)};  
163. //Call delete method to delete data
164. db.delete("stu_table", whereClauses, whereArgs);  
165. }   
166. }  
167. }  

StuDBHelper.java

1. package com.lingdududu.testSQLiteDb;  
2.   
3. import android.content.Context;  
4. import android.database.sqlite.SQLiteDatabase;  
5. import android.database.sqlite.SQLiteDatabase.CursorFactory;  
6. import android.database.sqlite.SQLiteOpenHelper;  
7. import android.util.Log;  
8.   
9. public class StuDBHelper extends SQLiteOpenHelper {  
10.   
11. private static final String TAG = "TestSQLite";  
12. public static final int VERSION = 1;  
13.   
14. //A constructor is required.
15. public StuDBHelper(Context context, String name, CursorFactory factory,  
16. int version) {  
17. super(context, name, factory, version);  
18. }  
19.   
20. //Call this method when the database is first created
21. public void onCreate(SQLiteDatabase db) {  
22. String sql = "create table stu_table(id int,sname varchar(20),sage int,ssex varchar(10))";  
23. //Output log information for database creation
24. Log.i(TAG, "create Database------------->");  
25. //The execSQL function is used to execute the SQL statement
26. db.execSQL(sql);  
27. }  
28.   
29. //Execute this method when updating the database.
30. public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {  
31. //Output Update Database Log Information
32. Log.i(TAG, "update Database------------->");  
33. }  
34. }  

main.xml

1. <?xml version="1.0" encoding="utf-8"?>  
2. <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"  
3. android:orientation="vertical"  
4. android:layout_width="fill_parent"  
5. android:layout_height="fill_parent"  
6. >  
7. <TextView   
8. android:layout_width="fill_parent"   
9. android:layout_height="wrap_content"   
10. android:text="@string/hello"  
11. />  
12. <Button  
13. android:id="@+id/createDatabase"  
14. android:layout_width="fill_parent"   
15. android:layout_height="wrap_content"   
16. android:text="Create a database"  
17. />   
18. <Button  
19. android:id="@+id/updateDatabase"  
20. android:layout_width="fill_parent"   
21. android:layout_height="wrap_content"   
22. android:text="Update database"  
23. />   
24. <Button  
25. android:id="@+id/insert"  
26. android:layout_width="fill_parent"   
27. android:layout_height="wrap_content"   
28. android:text="insert data"  
29. />  
30. <Button  
31. android:id="@+id/update"  
32. android:layout_width="fill_parent"   
33. android:layout_height="wrap_content"   
34. android:text="Update Data"  
35. />  
36. <Button  
37. android:id="@+id/query"  
38. android:layout_width="fill_parent"   
39. android:layout_height="wrap_content"   
40. android:text="Query Data"  
41. />  
42. <Button  
43. android:id="@+id/delete"  
44. android:layout_width="fill_parent"   
45. android:layout_height="wrap_content"   
46. android:text="Delete data"  
47. />  
48. </LinearLayout>  

1. Method for sqlite database to determine whether a table exists

A method to determine whether a table exists in sqlite and post it for your reference (java, android)

1. public boolean tabbleIsExist(String tableName){  
2.         boolean result = false;  
3.         if(tableName == null){  
4.                 return false;  
5.         }  
6.         SQLiteDatabase db = null;  
7.         Cursor cursor = null;  
8.         try {  
9.                 db = this.getReadableDatabase();  
10.                 String sql = "select count(*) as c from "+AppConstant.DataBaseName+" where type ='table' and name ='"+tableName.trim()+"' ";  
11.                 cursor = db.rawQuery(sql, null);  
12.                 if(cursor.moveToNext()){  
13.                         int count = cursor.getInt(0);  
14.                         if(count>0){  
15.                                 result = true;  
16.                         }  
17.                 }  
18.                   
19.         } catch (Exception e) {  
20.                 // TODO: handle exception  
21.         }                 
22.         return result;  
23. }  

Effects of program operation:

Use the adb command to view the database:

1. Enter adb shell in the command line window and enter the Linux command line. Now you can use Linux commands.

2.ls Enter and show everything, including one data.

3.cd data carriage return, ls carriage return, CD data carriage return, ls carriage return will see a lot of com................ That is, the application package name on the system, find the package name of your database program, and then enter.

4. When you go in and look at all, you will see databases, go into databases, and show all you will find your database name, using "stu_db".

5.sqlite3 stu_db enters your database on return, and ".schema" will see all the tables and table-building statements for the application.

6. You can then use standard SQL statements to view the database you just generated and perform add-delete checks on the data.
Note: ls,cd and other commands are the basic commands of linux, students who do not know can see information about this.

Here are a few adb commands commonly used in SQLLite:

See
.database displays database information;
tables displays the name of the table;
The.schema command allows you to view the SQL commands used to create the data tables.
.schema table_name View commands for SQL when table_name is created;

insert record
insert into table_name values (field1, field2, field3...);

query
select * from table_name; view all records in table_name table;
Select * from table_name where field1='xxxx'; query records that meet specified criteria;

delete
drop table_name; delete table;
drop index_name; Delete index;
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

# sqlite3 stu_db
sqlite3 stu_db
SQLite version 3.6.22
Enter  ".help" for instructions
Enter SQL statements terminated with a  ";"
sqlite> .schema
.schema
CREATE TABLE  android_metadata (locale TEXT);
CREATE TABLE stu_table(id int,sname varchar(20),sage int,ssex varchar(10)); --->Table created
sqlite> select * from stu_table;
select * from  stu_table;
1|xiaoming|21|male
sqlite>

insert data

sqlite> insert into stu_table  values(2,'xiaohong',20,'female');

Remember to have a one-to-one correspondence between the inserted data and the attributes in the table
insert into stu_table  values(2,'xiaohong',20,'female');
sqlite> select * from  stu_table;
select * from  stu_table;
1|xiaoming|21|male
2|xiaohong|20|female   --------------> Inserted data
sqlite>

When clicking the button to modify data

sqlite> select * from stu_table;
select * from  stu_table;
1|xiaoming|23|male --------------> Age was modified to 23
2|xiaohong|20|female
sqlite>


When clicking the Delete Data button

sqlite> select * from stu_table;
select * from  stu_table;
Data for 1|xiaoming|23|male* id=2 has been deleted

In summary, we can perform database add-delete checks in our code or on the adb command line.However, since SQLite does not have a client to view the information directly after the database changes, it is common to use the adb command line to view the information after the database changes.