Android SQLite数据库教程
This article is about android sqlite database tutorial.
本文是关于android sqlite数据库教程。
There are several storage options available in android like shared preferences, internal and external storage, sqlite, etc. Here we will see how to use sqlite database as a storage system in android to perform CRUD operations.
android中有几个存储选项，例如共享首选项 ，内部和外部存储，sqlite等。在这里，我们将看到如何在Linux中将sqlite数据库用作存储系统来执行CRUD操作。
SQLite is light weight open source database that stores data in text files. Android already comes with built in sqlite database.
SQLite是轻量级的开源数据库，可将数据存储在文本文件中。 Android已经内置了sqlite数据库。
Also Read: Java SQLite Tutorial
另请阅读： Java SQLite教程
Android SQLite数据库教程 (Android SQLite Database Tutorial)
The database table that I will use in this tutorial has following structure.
我将在本教程中使用的数据库表具有以下结构。
Table Name: record
表名：记录
| Field | Type |
| id | integer, primary key, autoincrement |
| name | text |
| 领域 | 类型 |
| ID | 整数，主键，自动递增 |
| 名称 | 文本 |
-
The android.database.sqlite package contains sqlite specific classes.
android.database.sqlite包包含sqlite特定的类。
-
The SQLiteOpenHelper class provides all the functionality for sqlite database.
SQLiteOpenHelper类提供sqlite数据库的所有功能。
-
The SQLiteDatabase class provides various methods to perform create, read, update and delete operations.
SQLiteDatabase类提供了执行创建，读取，更新和删除操作的各种方法。
创建数据库 (Create Database)
Before working with SQLite database we have to first extend SQLiteOpenHelper class. The example that I have used here contains DBHelper class that extends SQLiteOpenHelper class and perform all database related operations.
在使用SQLite数据库之前，我们必须首先扩展SQLiteOpenHelper类。 我在这里使用的示例包含DBHelper类，该类扩展了SQLiteOpenHelper类并执行所有与数据库有关的操作。
For creating the database we will call constructor of SQLiteOpenHelper class using super().
为了创建数据库，我们将使用super()调用SQLiteOpenHelper类的构造函数。
|
1
2
3
|
public DBHandler(Context context) {
super(context, DB_NAME, null, DB_VERSION);
}
|
|
1
2
3
|
public DBHandler ( Context context ) {
super ( context , DB_NAME , null , DB_VERSION ) ;
}
|
onCreate()和onUpgrade() (onCreate() and onUpgrade())
We have to override two methods onCreate() and onUpgrade().
我们必须重写两个方法onCreate()和onUpgrade() 。
The code required to create table will be written inside onCreate() method.
创建表所需的代码将写在onCreate()方法内部。
|
1
2
3
4
|
public void onCreate(SQLiteDatabase db) {
String query="CREATE TABLE "+TABLE_NAME+" ("+ID_COL+" INTEGER PRIMARY KEY AUTOINCREMENT,"+NAME_COL+" TEXT)";
db.execSQL(query);
}
|
|
1
2
3
4
|
public void onCreate ( SQLiteDatabase db ) {
String query = "CREATE TABLE " + TABLE_NAME + " (" + ID_COL + " INTEGER PRIMARY KEY AUTOINCREMENT," + NAME_COL + " TEXT)" ;
db . execSQL ( query ) ;
}
|
onUpgrade() method contains the code required to update the database.
onUpgrade()方法包含更新数据库所需的代码。
|
1
2
3
4
5
6
7
|
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// Drop older table if existed
db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
// Create table again
onCreate(db);
}
|
|
1
2
3
4
5
6
7
|
public void onUpgrade ( SQLiteDatabase db , int oldVersion , int newVersion ) {
// Drop older table if existed
db . execSQL ( "DROP TABLE IF EXISTS " + TABLE_NAME ) ;
// Create table again
onCreate ( db ) ;
}
|
插 (Insert)
In this example the insert operation is handled by insertRecord() method. It takes name as a string argument and insert it into table. We have to first add all the values in ContentValues object and then finally insert into table using insert() method of SQLiteDatabase class.
在此示例中，插入操作由insertRecord()方法处理。 它以name作为字符串参数，并将其插入表中。 我们必须首先在ContentValues对象中添加所有值，然后最后使用SQLiteDatabase类的insert()方法将其插入表中。
|
1
2
3
4
5
6
7
8
|
public void insertRecord(String name){
SQLiteDatabase db=this.getWritableDatabase();
ContentValues values=new ContentValues();
values.put(NAME_COL,name);
db.insert(TABLE_NAME,null,values);
db.close();
}
|
|
1
2
3
4
5
6
7
8
|
public void insertRecord ( String name ) {
SQLiteDatabase db = this . getWritableDatabase ( ) ;
ContentValues values = new ContentValues ( ) ;
values . put ( NAME_COL , name ) ;
db . insert ( TABLE_NAME , null , values ) ;
db . close ( ) ;
}
|
读 (Read)
For reading from table we just execute our select query using rawQuery() method of SQLiteDatabase class. This method returns Cursor object that will be used to fetch the records one by one.
为了从表中读取，我们只需使用SQLiteDatabase类的rawQuery()方法执行选择查询。 此方法返回Cursor对象，该对象将用于逐个获取记录。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
public String getRecords(){
String query="SELECT * FROM "+TABLE_NAME;
String result="";
SQLiteDatabase db=this.getReadableDatabase();
Cursor cursor=db.rawQuery(query,null);
cursor.moveToFirst();
while(cursor.isAfterLast()==false){
result+=cursor.getString(0)+" "+cursor.getString(1)+"\n";
cursor.moveToNext();
}
db.close();
return result;
}
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
public String getRecords ( ) {
String query = "SELECT * FROM " + TABLE_NAME ;
String result = "" ;
SQLiteDatabase db = this . getReadableDatabase ( ) ;
Cursor cursor = db . rawQuery ( query , null ) ;
cursor . moveToFirst ( ) ;
while ( cursor . isAfterLast ( ) == false ) {
result += cursor . getString ( 0 ) + " " + cursor . getString ( 1 ) + "\n" ;
cursor . moveToNext ( ) ;
}
db . close ( ) ;
return result ;
}
|
更新资料 (Update)
The update() method of SQLiteDatabase class is used to perform update operation according to a primary key. In this example id column is the primary key.
SQLiteDatabase类的update()方法用于根据主键执行更新操作。 在此示例中， id列是主键。
|
1
2
3
4
5
6
7
8
|
public void updateRecord(String id,String name){
SQLiteDatabase db=this.getWritableDatabase();
ContentValues values=new ContentValues();
values.put(NAME_COL,name);
db.update(TABLE_NAME,values,"id=?",new String[]{id});
db.close();
}
|
|
1
2
3
4
5
6
7
8
|
public void updateRecord ( String id , String name ) {
SQLiteDatabase db = this . getWritableDatabase ( ) ;
ContentValues values = new ContentValues ( ) ;
values . put ( NAME_COL , name ) ;
db . update ( TABLE_NAME , values , "id=?" , new String [ ] { id } ) ;
db . close ( ) ;
}
|
删除 (Delete)
The delete operation is performed by delete() method of SQLiteDatabase class according to primary key.
删除操作由SQLiteDatabase类的delete()方法根据主键执行。
|
1
2
3
4
5
6
|
public void deleteRecord(String id){
SQLiteDatabase db=this.getWritableDatabase();
db.delete(TABLE_NAME,"id=?",new String[]{id});
db.close();
}
|
|
1
2
3
4
5
6
|
public void deleteRecord ( String id ) {
SQLiteDatabase db = this . getWritableDatabase ( ) ;
db . delete ( TABLE_NAME , "id=?" , new String [ ] { id } ) ;
db . close ( ) ;
}
|
Android SQLite数据库示例 (Android SQLite Database Example)
First create a new project with name AndroidSQLite and package name com.androidsqlite.
首先创建一个名称为AndroidSQLite和包名称为com.androidsqlite的新项目。
Add following code in respective files and run the project.
在相应的文件中添加以下代码并运行项目。
MainActivity.java
MainActivity.java
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
|
package com.androidsqlite;
import android.app.Activity;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.TextView;
import android.widget.Toast;
public class MainActivity extends Activity {
EditText id,name;
Button insert,view,update,delete;
TextView text;
DBHandler db;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
id=(EditText)findViewById(R.id.id);
name=(EditText)findViewById((R.id.name));
insert=(Button)findViewById(R.id.insert);
view=(Button)findViewById(R.id.view);
update=(Button)findViewById(R.id.update);
delete=(Button)findViewById(R.id.delete);
text=(TextView)findViewById(R.id.text);
db=new DBHandler(getApplicationContext());
}
public void buttonAction(View view){
switch (view.getId()){
case R.id.insert:
db.insertRecord(name.getText().toString());
Toast.makeText(getApplicationContext(),"record inserted",Toast.LENGTH_LONG).show();
break;
case R.id.view:
text.setText(db.getRecords());
break;
case R.id.update:
db.updateRecord(id.getText().toString(),name.getText().toString());
Toast.makeText(getApplicationContext(),"record updated",Toast.LENGTH_LONG).show();
break;
case R.id.delete:
db.deleteRecord(id.getText().toString());
Toast.makeText(getApplicationContext(),"record deleted",Toast.LENGTH_LONG).show();
break;
}
}
}
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
|
package com . androidsqlite ;
import android . app . Activity ;
import android . os . Bundle ;
import android . view . View ;
import android . widget . Button ;
import android . widget . EditText ;
import android . widget . TextView ;
import android . widget . Toast ;
public class MainActivity extends Activity {
EditText id , name ;
Button insert , view , update , delete ;
TextView text ;
DBHandler db ;
@Override
protected void onCreate ( Bundle savedInstanceState ) {
super . onCreate ( savedInstanceState ) ;
setContentView ( R . layout . activity_main ) ;
id = ( EditText ) findViewById ( R . id . id ) ;
name = ( EditText ) findViewById ( ( R . id . name ) ) ;
insert = ( Button ) findViewById ( R . id . insert ) ;
view = ( Button ) findViewById ( R . id . view ) ;
update = ( Button ) findViewById ( R . id . update ) ;
delete = ( Button ) findViewById ( R . id . delete ) ;
text = ( TextView ) findViewById ( R . id . text ) ;
db = new DBHandler ( getApplicationContext ( ) ) ;
}
public void buttonAction ( View view ) {
switch ( view . getId ( ) ) {
case R . id . insert :
db . insertRecord ( name . getText ( ) . toString ( ) ) ;
Toast . makeText ( getApplicationContext ( ) , "record inserted" , Toast . LENGTH_LONG ) . show ( ) ;
break ;
case R . id . view :
text . setText ( db . getRecords ( ) ) ;
break ;
case R . id . update :
db . updateRecord ( id . getText ( ) . toString ( ) , name . getText ( ) . toString ( ) ) ;
Toast . makeText ( getApplicationContext ( ) , "record updated" , Toast . LENGTH_LONG ) . show ( ) ;
break ;
case R . id . delete :
db . deleteRecord ( id . getText ( ) . toString ( ) ) ;
Toast . makeText ( getApplicationContext ( ) , "record deleted" , Toast . LENGTH_LONG ) . show ( ) ;
break ;
}
}
}
|
DBHandler.java
DBHandler.java
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
|
package com.androidsqlite;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import java.util.ArrayList;
public class DBHandler extends SQLiteOpenHelper{
private static final String DB_NAME="demodb";
private static final int DB_VERSION=1;
private static final String TABLE_NAME="record";
private static final String ID_COL="id";
private static final String NAME_COL="name";
public DBHandler(Context context) {
super(context, DB_NAME, null, DB_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
String query="CREATE TABLE "+TABLE_NAME+" ("+ID_COL+" INTEGER PRIMARY KEY AUTOINCREMENT,"+NAME_COL+" TEXT)";
db.execSQL(query);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// Drop older table if existed
db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
// Create table again
onCreate(db);
}
public void insertRecord(String name){
SQLiteDatabase db=this.getWritableDatabase();
ContentValues values=new ContentValues();
values.put(NAME_COL,name);
db.insert(TABLE_NAME,null,values);
db.close();
}
public String getRecords(){
String query="SELECT * FROM "+TABLE_NAME;
String result="";
SQLiteDatabase db=this.getReadableDatabase();
Cursor cursor=db.rawQuery(query,null);
cursor.moveToFirst();
while(cursor.isAfterLast()==false){
result+=cursor.getString(0)+" "+cursor.getString(1)+"\n";
cursor.moveToNext();
}
db.close();
return result;
}
public void updateRecord(String id,String name){
SQLiteDatabase db=this.getWritableDatabase();
ContentValues values=new ContentValues();
values.put(NAME_COL,name);
db.update(TABLE_NAME,values,"id=?",new String[]{id});
db.close();
}
public void deleteRecord(String id){
SQLiteDatabase db=this.getWritableDatabase();
db.delete(TABLE_NAME,"id=?",new String[]{id});
db.close();
}
}
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
|
package com . androidsqlite ;
import android . content . ContentValues ;
import android . content . Context ;
import android . database . Cursor ;
import android . database . sqlite . SQLiteDatabase ;
import android . database . sqlite . SQLiteOpenHelper ;
import java . util . ArrayList ;
public class DBHandler extends SQLiteOpenHelper {
private static final String DB_NAME = "demodb" ;
private static final int DB_VERSION = 1 ;
private static final String TABLE_NAME = "record" ;
private static final String ID_COL = "id" ;
private static final String NAME_COL = "name" ;
public DBHandler ( Context context ) {
super ( context , DB_NAME , null , DB_VERSION ) ;
}
@Override
public void onCreate ( SQLiteDatabase db ) {
String query = "CREATE TABLE " + TABLE_NAME + " (" + ID_COL + " INTEGER PRIMARY KEY AUTOINCREMENT," + NAME_COL + " TEXT)" ;
db . execSQL ( query ) ;
}
@Override
public void onUpgrade ( SQLiteDatabase db , int oldVersion , int newVersion ) {
// Drop older table if existed
db . execSQL ( "DROP TABLE IF EXISTS " + TABLE_NAME ) ;
// Create table again
onCreate ( db ) ;
}
public void insertRecord ( String name ) {
SQLiteDatabase db = this . getWritableDatabase ( ) ;
ContentValues values = new ContentValues ( ) ;
values . put ( NAME_COL , name ) ;
db . insert ( TABLE_NAME , null , values ) ;
db . close ( ) ;
}
public String getRecords ( ) {
String query = "SELECT * FROM " + TABLE_NAME ;
String result = "" ;
SQLiteDatabase db = this . getReadableDatabase ( ) ;
Cursor cursor = db . rawQuery ( query , null ) ;
cursor . moveToFirst ( ) ;
while ( cursor . isAfterLast ( ) == false ) {
result += cursor . getString ( 0 ) + " " + cursor . getString ( 1 ) + "\n" ;
cursor . moveToNext ( ) ;
}
db . close ( ) ;
return result ;
}
public void updateRecord ( String id , String name ) {
SQLiteDatabase db = this . getWritableDatabase ( ) ;
ContentValues values = new ContentValues ( ) ;
values . put ( NAME_COL , name ) ;
db . update ( TABLE_NAME , values , "id=?" , new String [ ] { id } ) ;
db . close ( ) ;
}
public void deleteRecord ( String id ) {
SQLiteDatabase db = this . getWritableDatabase ( ) ;
db . delete ( TABLE_NAME , "id=?" , new String [ ] { id } ) ;
db . close ( ) ;
}
}
|
activity_main.xml
activity_main.xml
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
|
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:tools="http://schemas.android.com/tools" android:layout_width="match_parent"
android:layout_height="match_parent" android:paddingLeft="15dp"
android:paddingRight="15dp"
android:paddingTop="15dp"
android:paddingBottom="15dp" tools:context=".MainActivity">
<EditText
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:hint="Enter id to update or delete"
android:id="@+id/id"
android:onClick="buttonAction"/>
<EditText
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:hint="Enter name to insert or update"
android:id="@+id/name"
android:layout_below="@+id/id"
android:layout_marginTop="10dp"/>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_below="@+id/name"
android:id="@+id/layout1"
android:orientation="vertical">
<Button
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:id="@+id/insert"
android:text="Insert"
android:onClick="buttonAction"/>
<Button
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:id="@+id/view"
android:text="View"
android:onClick="buttonAction"
android:layout_below="@+id/name"/>
<Button
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="Update"
android:onClick="buttonAction"
android:id="@+id/update"
android:layout_below="@+id/name"/>
<Button
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="Delete"
android:onClick="buttonAction"
android:id="@+id/delete" />
</LinearLayout>
<TextView
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_below="@+id/layout1"
android:layout_marginTop="10dp"
android:id="@+id/text"/>
</RelativeLayout>
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
|
<RelativeLayout xmlns : android = "http://schemas.android.com/apk/res/android"
xmlns : tools = "http://schemas.android.com/tools" android : layout_width = "match_parent"
android : layout_height = "match_parent" android : paddingLeft = "15dp"
android : paddingRight = "15dp"
android : paddingTop = "15dp"
android : paddingBottom = "15dp" tools : context = ".MainActivity" >
<EditText
android : layout_width = "match_parent"
android : layout_height = "wrap_content"
android : hint = "Enter id to update or delete"
android : id = "@+id/id"
android : onClick = "buttonAction" />
<EditText
android : layout_width = "match_parent"
android : layout_height = "wrap_content"
android : hint = "Enter name to insert or update"
android : id = "@+id/name"
android : layout_below = "@+id/id"
android : layout_marginTop = "10dp" />
<LinearLayout
android : layout_width = "match_parent"
android : layout_height = "wrap_content"
android : layout_below = "@+id/name"
android : id = "@+id/layout1"
android : orientation = "vertical" >
<Button
android : layout_width = "match_parent"
android : layout_height = "wrap_content"
android : id = "@+id/insert"
android : text = "Insert"
android : onClick = "buttonAction" />
<Button
android : layout_width = "match_parent"
android : layout_height = "wrap_content"
android : id = "@+id/view"
android : text = "View"
android : onClick = "buttonAction"
android : layout_below = "@+id/name" />
<Button
android : layout_width = "match_parent"
android : layout_height = "wrap_content"
android : text = "Update"
android : onClick = "buttonAction"
android : id = "@+id/update"
android : layout_below = "@+id/name" />
<Button
android : layout_width = "match_parent"
android : layout_height = "wrap_content"
android : text = "Delete"
android : onClick = "buttonAction"
android : id = "@+id/delete" />
</LinearLayout>
<TextView
android : layout_width = "match_parent"
android : layout_height = "wrap_content"
android : layout_below = "@+id/layout1"
android : layout_marginTop = "10dp"
android : id = "@+id/text" />
</RelativeLayout>
|
Output
输出量
If you are facing any problem related to above android sqlite database tutorial then feel free to comment below. I will try my best to solve your problem.
如果您遇到与上述android sqlite数据库教程相关的任何问题，请在下面随意评论。 我会尽力解决您的问题。
翻译自: https://www.thecrazyprogrammer.com/2016/06/android-sqlite-database-tutorial.html