Android整合SQLite数据库进行基本的增删改查

简言

使用Android整合SQLite数据库进行数据存储，大致可以划分为三步：

①继承 SQLiteOpenHelper，创建数据库

②继承 ContentProvider 类，重写方法

③在清单文件AndroidManifest中声明 ContentProvider

继承 SQLiteOpenHelper类

1.  
   /**
2.  
   * 数据库常量声明
3.  
   */
4.  
   private SQLiteDatabase db;
5.  
   private static final String DATABASE_NAME = "College";
6.  
   private static final String STUDENTS_TABLE_NAME = "students";
7.  
   private static final int DATABASE_VERSION = 1;
8.  
   private static final String CREATE_DB_TABLE =
9.  
   "CREATE TABLE " STUDENTS_TABLE_NAME "("
10.  
    _ID " INTEGER PRIMARY KEY AUTOINCREMENT,"
11.  
    NAME " TEXT NOT NULL,"
12.  
    GRADE " TEXT NOT NULL)";
13.  
     
14.  
     
15.  
    private static class DataBaseHelper extends SQLiteOpenHelper {
16.  
     
17.  
     
18.  
    public DataBaseHelper(Context context) {
19.  
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
20.  
    }
21.  
     
22.  
    @Override //创建数据库
23.  
    public void onCreate(SQLiteDatabase db) {
24.  
    db.execSQL(CREATE_DB_TABLE);
25.  
    }
26.  
     
27.  
    @Override //数据库更新会被调用(用的比较少)
28.  
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
29.  
    db.execSQL("DROP TABLE IF EXISTS " STUDENTS_TABLE_NAME);
30.  
    }
31.  
    }

实现 ContentProvider 类

1.  
   public class StudentsProvider extends ContentProvider {
2.  
   private static final String TAG = "StudentsProvider";
3.  
    
4.  
   private static final String PROVIDER_NAME = "com.example.xxx.StudentsProvider";
5.  
   private static final String URL = "content://" PROVIDER_NAME "/students";
6.  
   //uri:统一资源标识符,用来指定查询某个应用程序下的某一张表, parse():将URL字符串转换成Uri对象
7.  
   public static final Uri CONTENT_URI = Uri.parse(URL);
8.  
    
9.  
   /** 表格字段 */
10.  
    public static final String _ID = "_id";
11.  
    public static final String NAME = "name";
12.  
    public static final String GRADE = "grade";
13.  
     
14.  
    private static HashMap<String, String> STUDENTS_PROJECTION_MAP;
15.  
     
16.  
    //匹配码,自定义
17.  
    private static final int STUDENTS = 1;
18.  
    private static final int STUDENTS_ID = 2;
19.  
     
20.  
    private static final UriMatcher uriMatcher;
21.  
     
22.  
    static {
23.  
    uriMatcher = new UriMatcher(UriMatcher.NO_MATCH);
24.  
    //添加需要匹配的Uri,如果匹配到就返回匹配码(例如:match()方法匹配到PROVIDER_NAME/students,则返回STUDENTS匹配码) #是通配符
25.  
    uriMatcher.addURI(PROVIDER_NAME, "students", STUDENTS);
26.  
    uriMatcher.addURI(PROVIDER_NAME, "students/#", STUDENTS_ID);
27.  
    }
28.  
     
29.  
     
30.  
     
31.  
    @Override
32.  
    public boolean onCreate() {
33.  
    Context context = getContext();
34.  
    DataBaseHelper dbHelper = new DataBaseHelper(context);
35.  
    //getWritableDatabase:返会一个读写SQLiteDatabase数据库,磁盘满了之后会报错,getReadableDatabase()不会
36.  
    db = dbHelper.getWritableDatabase();
37.  
    return (db == null) ? false : true;
38.  
    }
39.  
     
40.  
     
41.  
    /**
42.  
    * 查询语句
43.  
    *
44.  
    * @param uri 查询哪个应用哪张表
45.  
    * @param projection 指定查询的列名
46.  
    * @param selection 指定where的约束条件
47.  
    * @param selectionArgs 为where中的占位符提供具体的值
48.  
    * @param sortOrder 指定查询结果的排序方式
49.  
    * @return 查询的结果封装到 Cursor对象返回
50.  
    */
51.  
    @Nullable
52.  
    @Override
53.  
    public Cursor query(@NonNull Uri uri, @Nullable String[] projection, @Nullable String selection, @Nullable String[] selectionArgs, @Nullable String sortOrder) {
54.  
    SQLiteQueryBuilder qb = new SQLiteQueryBuilder();
55.  
    qb.setTables(STUDENTS_TABLE_NAME);
56.  
     
57.  
    switch (uriMatcher.match(uri)) {
58.  
    case STUDENTS:
59.  
    qb.setProjectionMap(STUDENTS_PROJECTION_MAP);
60.  
    break;
61.  
    case STUDENTS_ID: //根据ID查询
62.  
    qb.appendWhere(_ID "=" uri.getPathSegments().get(1));
63.  
    break;
64.  
    default:
65.  
    }
66.  
     
67.  
    if (sortOrder == null || sortOrder == "") {
68.  
    sortOrder = NAME; //默认根据name排序
69.  
    }
70.  
     
71.  
    Cursor c = qb.query(db, projection, selection, selectionArgs, null, null, sortOrder);
72.  
    //注册一个监听器,监视 URI 的变化
73.  
    c.setNotificationUri(getContext().getContentResolver(), uri);
74.  
    Log.d(TAG, " query: 执行了");
75.  
    return c;
76.  
    }
77.  
     
78.  
    @Nullable
79.  
    @Override
80.  
    public String getType(@NonNull Uri uri) {
81.  
    switch (uriMatcher.match(uri)) {
82.  
    /**
83.  
    * Get all student records
84.  
    */
85.  
    case STUDENTS:
86.  
    return "vnd.android.cursor.dir/vnd.example.students";
87.  
    /**
88.  
    * Get a particular student
89.  
    */
90.  
    case STUDENTS_ID:
91.  
    return "vnd.android.cursor.item/vnd.example.students";
92.  
    default:
93.  
    throw new IllegalArgumentException("Unsupported URI: " uri);
94.  
    }
95.  
    }
96.  
     
97.  
     
98.  
    /**
99.  
    * 插入语句
100.  
     *
101.  
     * @param uri 统一资源标识符
102.  
     * @param values 插入的属性值
103.  
     * @return
104.  
     */
105.  
     @Nullable
106.  
     @Override
107.  
     public Uri insert(@NonNull Uri uri, @Nullable ContentValues values) {
108.  
     long rowID = db.insert(STUDENTS_TABLE_NAME, "", values);
109.  
     Log.d(TAG, " insert: 执行了");
110.  
     /**
111.  
     * 判读记录是否添加成功
112.  
     */
113.  
     if (rowID > 0) {
114.  
     //withAppendedId():负责把 CONTENT_URI 和 rowID拼接成一个新的uri
115.  
     Uri _uri = ContentUris.withAppendedId(CONTENT_URI, rowID);
116.  
     //如果 uri发生变化, null:表示则通知所有人
117.  
     getContext().getContentResolver().notifyChange(_uri, null);
118.  
     return _uri;
119.  
     }
120.  
     throw new SQLException("Failed to add a record into " uri);
121.  
     }
122.  
      
123.  
      
124.  
     /**
125.  
     * 删除语句
126.  
     *
127.  
     * @param uri 统一资源标识符(说百了就是查询table表的具体位置)
128.  
     * @param selection 字段
129.  
     * @param selectionArgs 字段值
130.  
     * @return 成功条数
131.  
     */
132.  
     @Override
133.  
     public int delete(@NonNull Uri uri, @Nullable String selection, @Nullable String[] selectionArgs) {
134.  
     int count = 0;
135.  
     Log.d(TAG, " delete: 执行了");
136.  
     switch (uriMatcher.match(uri)) {
137.  
     case STUDENTS:
138.  
     count = db.delete(STUDENTS_TABLE_NAME, selection, selectionArgs);
139.  
     break;
140.  
     case STUDENTS_ID:
141.  
     String id = uri.getPathSegments().get(1);
142.  
     db.delete(STUDENTS_TABLE_NAME, _ID "=" id
143.  
     (!TextUtils.isEmpty(selection) ? " AND (" selection ')' : ""), selectionArgs);
144.  
     default:
145.  
     throw new IllegalArgumentException("Unknown URI " uri);
146.  
     }
147.  
     getContext().getContentResolver().notifyChange(uri, null);
148.  
     return count;
149.  
     }
150.  
      
151.  
      
152.  
     /**
153.  
     * 更新语句
154.  
     *
155.  
     * @param uri 统一资源标识符
156.  
     * @param values 要更新的字段和值
157.  
     * @param selection where字句
158.  
     * @param selectionArgs where字句的值
159.  
     * @return
160.  
     */
161.  
     @Override
162.  
     public int update(@NonNull Uri uri, @Nullable ContentValues values, @Nullable String selection, @Nullable String[] selectionArgs) {
163.  
     int count = 0;
164.  
     Log.d(TAG, " update: 执行了");
165.  
     switch (uriMatcher.match(uri)) {
166.  
     case STUDENTS:
167.  
     count = db.update(STUDENTS_TABLE_NAME, values, selection, selectionArgs);
168.  
     break;
169.  
     case STUDENTS_ID:
170.  
     count = db.update(STUDENTS_TABLE_NAME, values,
171.  
     _ID "=" uri.getPathSegments().get(1)
172.  
     (!TextUtils.isEmpty(selection) ? "AND (" selection ")" : ""), selectionArgs);
173.  
     break;
174.  
     default:
175.  
     throw new IllegalArgumentException("Unknown URI " uri);
176.  
     }
177.  
     getContext().getContentResolver().notifyChange(uri, null);
178.  
     return count;
179.  
     }
180.  
      
181.  
     }

在清单文件AndroidManifest中声明 ContentProvider

1.  
   <provider
2.  
   android:name=".StudentsProvider"
3.  
   android:authorities="com.example.xxx.StudentsProvider"/>

调用测试

1.  
   public void onClickAddName(View view) {
2.  
   ContentValues values = new ContentValues();
3.  
   values.put(StudentsProvider.NAME, ((EditText) findViewById(R.id.editText2)).getText().toString());
4.  
   values.put(StudentsProvider.GRADE, ((EditText) findViewById(R.id.editText3)).getText().toString());
5.  
    
6.  
   Uri uri = getContentResolver().insert(StudentsProvider.CONTENT_URI, values);
7.  
   Toast.makeText(getBaseContext(),
8.  
   uri.toString(), Toast.LENGTH_LONG).show();
9.  
   System.out.println(uri);
10.  
    }

后续可以通过：getContentResolver() 数据库操作 来实现调用SQLite进行增删改查

完整代码

1.  
   package com.example.xxx;
2.  
    
3.  
   import android.content.ContentProvider;
4.  
   import android.content.ContentUris;
5.  
   import android.content.ContentValues;
6.  
   import android.content.Context;
7.  
   import android.content.UriMatcher;
8.  
   import android.database.Cursor;
9.  
   import android.database.SQLException;
10.  
    import android.database.sqlite.SQLiteDatabase;
11.  
    import android.database.sqlite.SQLiteOpenHelper;
12.  
    import android.database.sqlite.SQLiteQueryBuilder;
13.  
    import android.net.Uri;
14.  
    import android.text.TextUtils;
15.  
    import android.util.Log;
16.  
    import android.widget.Toast;
17.  
     
18.  
    import androidx.annotation.NonNull;
19.  
    import androidx.annotation.Nullable;
20.  
     
21.  
    import java.util.HashMap;
22.  
     
23.  
     
24.  
    /**
25.  
    * 内容提供者（提供数据）
26.  
    * 该类已经在清单中进行了注册,项目启动的时候,清单中声明的类都会进行自动装载
27.  
    */
28.  
    public class StudentsProvider extends ContentProvider {
29.  
    private static final String TAG = "StudentsProvider";
30.  
     
31.  
    private static final String PROVIDER_NAME = "com.example.xxx.StudentsProvider";
32.  
    private static final String URL = "content://" PROVIDER_NAME "/students";
33.  
    //uri:统一资源标识符,用来指定查询某个应用程序下的某一张表, parse():将URL字符串转换成Uri对象
34.  
    public static final Uri CONTENT_URI = Uri.parse(URL);
35.  
     
36.  
    public static final String _ID = "_id";
37.  
    public static final String NAME = "name";
38.  
    public static final String GRADE = "grade";
39.  
     
40.  
    private static HashMap<String, String> STUDENTS_PROJECTION_MAP;
41.  
     
42.  
    //匹配码,自定义
43.  
    private static final int STUDENTS = 1;
44.  
    private static final int STUDENTS_ID = 2;
45.  
     
46.  
    private static final UriMatcher uriMatcher;
47.  
     
48.  
    static {
49.  
    uriMatcher = new UriMatcher(UriMatcher.NO_MATCH);
50.  
    //添加需要匹配的Uri,如果匹配到就返回匹配码(例如:match()方法匹配到PROVIDER_NAME/students,则返回STUDENTS匹配码) #是通配符
51.  
    uriMatcher.addURI(PROVIDER_NAME, "students", STUDENTS);
52.  
    uriMatcher.addURI(PROVIDER_NAME, "students/#", STUDENTS_ID);
53.  
    }
54.  
     
55.  
    /**
56.  
    * 数据库常量声明
57.  
    */
58.  
    private SQLiteDatabase db;
59.  
    private static final String DATABASE_NAME = "College";
60.  
    private static final String STUDENTS_TABLE_NAME = "students";
61.  
    private static final int DATABASE_VERSION = 1;
62.  
    private static final String CREATE_DB_TABLE =
63.  
    "CREATE TABLE " STUDENTS_TABLE_NAME "("
64.  
    _ID " INTEGER PRIMARY KEY AUTOINCREMENT,"
65.  
    NAME " TEXT NOT NULL,"
66.  
    GRADE " TEXT NOT NULL)";
67.  
     
68.  
     
69.  
    private static class DataBaseHelper extends SQLiteOpenHelper {
70.  
     
71.  
     
72.  
    public DataBaseHelper(Context context) {
73.  
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
74.  
    }
75.  
     
76.  
    @Override //创建数据库
77.  
    public void onCreate(SQLiteDatabase db) {
78.  
    db.execSQL(CREATE_DB_TABLE);
79.  
    }
80.  
     
81.  
    @Override //数据库更新会被调用(用的比较少)
82.  
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
83.  
    db.execSQL("DROP TABLE IF EXISTS " STUDENTS_TABLE_NAME);
84.  
    }
85.  
    }
86.  
     
87.  
     
88.  
    @Override
89.  
    public boolean onCreate() {
90.  
    Context context = getContext();
91.  
    DataBaseHelper dbHelper = new DataBaseHelper(context);
92.  
    //getWritableDatabase:返会一个读写SQLiteDatabase数据库,磁盘满了之后会报错,getReadableDatabase()不会
93.  
    db = dbHelper.getWritableDatabase();
94.  
    return (db == null) ? false : true;
95.  
    }
96.  
     
97.  
     
98.  
    /**
99.  
    * 查询语句
100.  
     *
101.  
     * @param uri 查询哪个应用哪张表
102.  
     * @param projection 指定查询的列名
103.  
     * @param selection 指定where的约束条件
104.  
     * @param selectionArgs 为where中的占位符提供具体的值
105.  
     * @param sortOrder 指定查询结果的排序方式
106.  
     * @return 查询的结果封装到 Cursor对象返回
107.  
     */
108.  
     @Nullable
109.  
     @Override
110.  
     public Cursor query(@NonNull Uri uri, @Nullable String[] projection, @Nullable String selection, @Nullable String[] selectionArgs, @Nullable String sortOrder) {
111.  
     SQLiteQueryBuilder qb = new SQLiteQueryBuilder();
112.  
     qb.setTables(STUDENTS_TABLE_NAME);
113.  
      
114.  
     switch (uriMatcher.match(uri)) {
115.  
     case STUDENTS:
116.  
     qb.setProjectionMap(STUDENTS_PROJECTION_MAP);
117.  
     break;
118.  
     case STUDENTS_ID: //根据ID查询
119.  
     qb.appendWhere(_ID "=" uri.getPathSegments().get(1));
120.  
     break;
121.  
     default:
122.  
     }
123.  
      
124.  
     if (sortOrder == null || sortOrder == "") {
125.  
     sortOrder = NAME; //默认根据name排序
126.  
     }
127.  
      
128.  
     Cursor c = qb.query(db, projection, selection, selectionArgs, null, null, sortOrder);
129.  
     //注册一个监听器,监视 URI 的变化
130.  
     c.setNotificationUri(getContext().getContentResolver(), uri);
131.  
     Log.d(TAG, " query: 执行了");
132.  
     return c;
133.  
     }
134.  
      
135.  
     @Nullable
136.  
     @Override
137.  
     public String getType(@NonNull Uri uri) {
138.  
     switch (uriMatcher.match(uri)) {
139.  
     /**
140.  
     * Get all student records
141.  
     */
142.  
     case STUDENTS:
143.  
     return "vnd.android.cursor.dir/vnd.example.students";
144.  
     /**
145.  
     * Get a particular student
146.  
     */
147.  
     case STUDENTS_ID:
148.  
     return "vnd.android.cursor.item/vnd.example.students";
149.  
     default:
150.  
     throw new IllegalArgumentException("Unsupported URI: " uri);
151.  
     }
152.  
     }
153.  
      
154.  
      
155.  
     /**
156.  
     * 插入语句
157.  
     *
158.  
     * @param uri 统一资源标识符
159.  
     * @param values 插入的属性值
160.  
     * @return
161.  
     */
162.  
     @Nullable
163.  
     @Override
164.  
     public Uri insert(@NonNull Uri uri, @Nullable ContentValues values) {
165.  
     long rowID = db.insert(STUDENTS_TABLE_NAME, "", values);
166.  
     Log.d(TAG, " insert: 执行了");
167.  
     /**
168.  
     * 判读记录是否添加成功
169.  
     */
170.  
     if (rowID > 0) {
171.  
     //withAppendedId():负责把 CONTENT_URI 和 rowID拼接成一个新的uri
172.  
     Uri _uri = ContentUris.withAppendedId(CONTENT_URI, rowID);
173.  
     //如果 uri发生变化, null:表示则通知所有人
174.  
     getContext().getContentResolver().notifyChange(_uri, null);
175.  
     return _uri;
176.  
     }
177.  
     throw new SQLException("Failed to add a record into " uri);
178.  
     }
179.  
      
180.  
      
181.  
     /**
182.  
     * 删除语句
183.  
     *
184.  
     * @param uri 统一资源标识符(说百了就是查询table表的具体位置)
185.  
     * @param selection 字段
186.  
     * @param selectionArgs 字段值
187.  
     * @return 成功条数
188.  
     */
189.  
     @Override
190.  
     public int delete(@NonNull Uri uri, @Nullable String selection, @Nullable String[] selectionArgs) {
191.  
     int count = 0;
192.  
     Log.d(TAG, " delete: 执行了");
193.  
     switch (uriMatcher.match(uri)) {
194.  
     case STUDENTS:
195.  
     count = db.delete(STUDENTS_TABLE_NAME, selection, selectionArgs);
196.  
     break;
197.  
     case STUDENTS_ID:
198.  
     String id = uri.getPathSegments().get(1);
199.  
     db.delete(STUDENTS_TABLE_NAME, _ID "=" id
200.  
     (!TextUtils.isEmpty(selection) ? " AND (" selection ')' : ""), selectionArgs);
201.  
     default:
202.  
     throw new IllegalArgumentException("Unknown URI " uri);
203.  
     }
204.  
     getContext().getContentResolver().notifyChange(uri, null);
205.  
     return count;
206.  
     }
207.  
      
208.  
      
209.  
     /**
210.  
     * 更新语句
211.  
     *
212.  
     * @param uri 统一资源标识符
213.  
     * @param values 要更新的字段和值
214.  
     * @param selection where字句
215.  
     * @param selectionArgs where字句的值
216.  
     * @return
217.  
     */
218.  
     @Override
219.  
     public int update(@NonNull Uri uri, @Nullable ContentValues values, @Nullable String selection, @Nullable String[] selectionArgs) {
220.  
     int count = 0;
221.  
     Log.d(TAG, " update: 执行了");
222.  
     switch (uriMatcher.match(uri)) {
223.  
     case STUDENTS:
224.  
     count = db.update(STUDENTS_TABLE_NAME, values, selection, selectionArgs);
225.  
     break;
226.  
     case STUDENTS_ID:
227.  
     count = db.update(STUDENTS_TABLE_NAME, values,
228.  
     _ID "=" uri.getPathSegments().get(1)
229.  
     (!TextUtils.isEmpty(selection) ? "AND (" selection ")" : ""), selectionArgs);
230.  
     break;
231.  
     default:
232.  
     throw new IllegalArgumentException("Unknown URI " uri);
233.  
     }
234.  
     getContext().getContentResolver().notifyChange(uri, null);
235.  
     return count;
236.  
     }
237.  
      
238.  
     }

只需要把完整代码拷贝到你的项目中,然后新建一个测试类，通过 getContentResolver() 数据库操作，就可以对SQLIte数据库进行增删改查了

这篇好文章是转载于：学新通技术网