Android SQLite数据库的操作CRUD(案例)移动开发weixin43671437的博客-

1、知识点总结

1、动态添加组件(查询数据时)
2、对SQLite数据库的创建以及CRUD的操作

2、案例介绍

CRUD:增删改查

此案例主要是对数据库进行CRUD的操作总结。

• 数据库名：product.db
• 表名：information
• 字段：id(integer类型 自增，主键)， name(varchar(20))，price(integer)

3、布局文件

页面展示：
底部数据为点击查询后出现的数据，默认没有

<?xml version="1.0" encoding="utf-8"?> <LinearLayout xmlns:android="https://schemas.android.com/apk/res/android" xmlns:app="https://schemas.android.com/apk/res-auto" xmlns:tools="https://schemas.android.com/tools" android:layout_width="match_parent" android:layout_height="match_parent" android:orientation="vertical" tools:context=".SqliteActivity"> <EditText android:id="@+id/et_name" android:layout_width="match_parent" android:layout_height="wrap_content" android:hint="请输入Name" /> <EditText android:id="@+id/et_passwd" android:layout_width="match_parent" android:layout_height="wrap_content" android:inputType="number" android:hint="请输入Price" /> <LinearLayout android:layout_width="match_parent" android:layout_height="wrap_content" android:orientation="vertical"> <Button android:id="@+id/bt_insert" android:layout_width="match_parent" android:layout_height="wrap_content" android:text="插入" /> <Button android:id="@+id/bt_delete" android:layout_width="match_parent" android:layout_height="wrap_content" android:text="删除" /> <Button android:id="@+id/bt_update" android:layout_width="match_parent" android:layout_height="wrap_content" android:text="修改" /> <Button android:id="@+id/bt_select" android:layout_width="match_parent" android:layout_height="wrap_content" android:text="查询" /> </LinearLayout> <!-- 动态添加组件, 查询时用到 --> <LinearLayout android:id="@+id/linearLayoutOut" android:layout_width="match_parent" android:layout_height="wrap_content" android:orientation="vertical"/> </LinearLayout> 

4、代码

4.1、实体类：

Priduct.java

package com.example.sqlitedemo; import android.content.Intent; public class Product { private int id; private String name; private int price; @Override public String toString() { return "Product{" + "id=" + id + ", name='" + name + ''' + ", price=" + price + '}'; } public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public int getPrice() { return price; } public void setPrice(int price) { this.price = price; } public Product() { } public Product(int id, String name, int price) { this.id = id; this.name = name; this.price = price; } } 

4.2、创建数据库

MySqLiteHelper.java

package com.example.sqlitedemo; import android.content.Context; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; import android.widget.Toast; import androidx.annotation.Nullable; public class MySqLiteHelper extends SQLiteOpenHelper { private Context context; /**      *      * 第二个参数:  name 数据库的名字      * 第三个参数: cursorFactory :游标工厂“对象nu1l      * 第四个参数: version 版本号1,2      *      * */ public MySqLiteHelper(@Nullable Context context) { super(context, "product.db",null, 1); this.context = context; } /**      * 第一次创建数据库时, 才会调用      *      * 将建表语句写在这个方法里。      * */ @Override public void onCreate(SQLiteDatabase db) {         db.execSQL("CREATE TABLE information(" + "id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," + "name VARCHAR(20)," + "price INTEGER)");          Toast.makeText(context, "数据表创建成功",Toast.LENGTH_SHORT).show(); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { } } 

4.3、CRUD操作

SqliteActivity.java
各个参数具体说明在底部。

package com.example.sqlitedemo; import androidx.appcompat.app.AppCompatActivity; import android.content.ContentValues; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.os.Bundle; import android.util.Log; import android.view.View; import android.widget.Button; import android.widget.EditText; import android.widget.LinearLayout; import android.widget.TextView; import android.widget.Toast; public class SqliteActivity extends AppCompatActivity { private MySqLiteHelper helper; private Button bt_insert; private Button bt_delete; private Button bt_update; private Button bt_select; private EditText editName; private EditText editPasswd; private SQLiteDatabase db; private ContentValues values; private LinearLayout linearLayoutOut; private LinearLayout linearLayoutIn; private LinearLayout.LayoutParams layoutParams; private TextView textName; private TextView textPrice; private TextView textId; /**      * 初始化     * */ public void init(){         editName = findViewById(R.id.et_name);         editPasswd = findViewById(R.id.et_passwd);         bt_insert = findViewById(R.id.bt_insert);         bt_delete = findViewById(R.id.bt_delete);         bt_update = findViewById(R.id.bt_update);         bt_select = findViewById(R.id.bt_select);         linearLayoutOut = findViewById(R.id.linearLayoutOut); //为linearLayout设置属性         layoutParams = new LinearLayout.LayoutParams(                 LinearLayout.LayoutParams.WRAP_CONTENT,                 LinearLayout.LayoutParams.WRAP_CONTENT         ); } @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_sqlite); init(); //创建数据表         helper = new MySqLiteHelper(this); //调用getWritableDatabase() 或getReadableDatabase()创建数据库         db = helper.getWritableDatabase(); /**         * 插入数据         * the row ID of the newly inserted row, or -1 if an error occurred         * 插入成功返回新的行数, 失败-1         *         * */         bt_insert.setOnClickListener(new View.OnClickListener() { @Override public void onClick(View v) {                 values = new ContentValues(); //判断输入框内是否有值 if (editName.getText().length() != 0 &&  editPasswd.getText().length() != 0) {                     values.put("name",editName.getText().toString());                     values.put("price",editPasswd.getText().toString()); long id = db.insert("information", null, values); if (id != -1) {                         Toast.makeText(SqliteActivity.this,"successful",Toast.LENGTH_SHORT).show(); }else {                         Toast.makeText(SqliteActivity.this,"failure",Toast.LENGTH_SHORT).show(); } }else {                     Toast.makeText(SqliteActivity.this,"Name或Price不能为空",Toast.LENGTH_SHORT).show(); } } }); /**         * 删除数据         *         * 方式一: db.delete("information", "id =1 ", null);         * 方式二: db.delete("information","name = ?", new String[]{"1"});         * 第一个参数：表名         * 第二个参数：where子句         * 第二个参数：where子句         * 返回行数, 0 删除失败         * 根据name名删除数据         * */         bt_delete.setOnClickListener(new View.OnClickListener() { @Override public void onClick(View v) { // int information = db.delete("information","name = ?", new String[]{editName.getText().toString()}); if (information != 0 ){                     Toast.makeText(SqliteActivity.this,"delete successful", Toast.LENGTH_LONG).show(); }else{                     Toast.makeText(SqliteActivity.this,"delete failure", Toast.LENGTH_LONG).show(); } } }); /*         * 更新数据         * 根据name修改price         * 第一个参数：数据表         * 第二个参数：values         * 第三个参数：where子句的内容         *         * */         bt_update.setOnClickListener(new View.OnClickListener() { @Override public void onClick(View v) {                 values = new ContentValues();                 values.put("price",editPasswd.getText().toString()); int information = db.update("information", values, "name = ?", new String[]{editName.getText().toString()}); if (information != 0 ){                     Toast.makeText(SqliteActivity.this,"delete successful", Toast.LENGTH_LONG).show(); }else{                     Toast.makeText(SqliteActivity.this,"delete failure", Toast.LENGTH_LONG).show(); } } }); /*         * 查找数据         * 查找所有数据         *         * */         bt_select.setOnClickListener(new View.OnClickListener() { @Override public void onClick(View v) {                 values = new ContentValues(); //查询                 Cursor cursor = db.query("information", null, null, null, null, null, null); /*                * cursor中一些方法的解释                * 可以通过列号获取某一条 数据当中该列上的数据getXXX(                * 可以通过列名获取列的索引号 getColumnIndex(String columnName)                * moveToFirst(),moveToLast(), moveToNext()                * 如果moveToFirst返回值为false,意味着什么?没有查询到数据                * 如果moveToNext返回值为false,意味着什么?查询结果已经遍历完了, 没有下一个了                *                * */ //判断有没有查询出来 if (cursor != null && cursor.moveToFirst()) { //判断是否已有数据,若有就删除 if (textId != null){                         Log.i("已清空", "Okk");                         linearLayoutOut.removeAllViews(); } //查询结果 //通过游标遍历查询结果 do { //获取数据 int dateId = cursor.getInt(0);                         String dateName = cursor.getString(cursor.getColumnIndex("name")); int datePrice = cursor.getInt(2);                         Product product = new Product(dateId,dateName,datePrice);                         Log.i("获取的数据",product.toString());                         linearLayoutIn = new LinearLayout(SqliteActivity.this);                         textId = new TextView(SqliteActivity.this);                         textName = new TextView(SqliteActivity.this);                         textPrice= new TextView(SqliteActivity.this);                         linearLayoutIn.setOrientation(LinearLayout.HORIZONTAL); //显示Id                         textId.setText("Id:"+product.getId()+"    "); //显示name                         textName.setText("Name:"+product.getName()+"    "); //显示价格                         textPrice.setText("Price:"+product.getPrice()); //将textView添加到linearLayoutIn中                         linearLayoutIn.addView(textId,layoutParams);                         linearLayoutIn.addView(textName,layoutParams);                         linearLayoutIn.addView(textPrice,layoutParams); //将linearLayoutIn添加到LinearLayoutOut中                         linearLayoutOut.addView(linearLayoutIn); } while (cursor.moveToNext()); } } }); } } 

5、扩展

5.1、getWritableDatabase()和getReadableDatabase()方法区别：

Android使用getWritableDatabase()和getReadableDatabase()方法都可以获取一个
用于操作数据库的SQLiteDatabase实例。(getReadableDatabase()方法中会调用getWritableDatabase()方法）

• getWritableDatabase() 方法以读写方式打开数据库，一旦数据库的磁盘空间满了，数据库就只能读而不能写，倘若使用的是getWritableDatabase() 方法就会出错。

• getReadableDatabase()方法则是先以读写方式打开数据库，如果数据库的磁盘空间满了，就会打开失败，当打开失败后会继续尝试以只读方式打开数据库。如果该问题成功解决，则只读数据库对象就会关闭，然后返回一个可读写的数据库对象。

5.2、SQLiteDataBase对象各接口说明

• 1、SQLiteDataBase对象的query()接口：

 /**      * Query the given table, returning a {@link Cursor} over the result set.      *      * @param table The table name to compile the query against.(要查询的表名.)      * @param columns A list of which columns to return. Passing null will      *            return all columns, which is discouraged to prevent reading      *            data from storage that isn't going to be used.(想要显示的列，若为空则返回所有列，不建议设置为空，如果不是返回所有列)      * @param selection A filter declaring which rows to return, formatted as an      *            SQL WHERE clause (excluding the WHERE itself). Passing null      *            will return all rows for the given table.      *     (where子句，声明要返回的行的要求，如果为空则返回表的所有行。)      * @param selectionArgs You may include ?s in selection, which will be      *         replaced by the values from selectionArgs, in order that they      *         appear in the selection. The values will be bound as Strings.      *    ( where子句对应的条件值)      * @param groupBy A filter declaring how to group rows, formatted as an SQL      *            GROUP BY clause (excluding the GROUP BY itself). Passing null      *            will cause the rows to not be grouped.      *     (分组方式，若为空则不分组.)      * @param having A filter declare which row groups to include in the cursor,      *            if row grouping is being used, formatted as an SQL HAVING      *            clause (excluding the HAVING itself). Passing null will cause      *            all row groups to be included, and is required when row      *            grouping is not being used.      *     (having条件，若为空则返回全部（不建议）)      * @param orderBy How to order the rows, formatted as an SQL ORDER BY clause      *            (excluding the ORDER BY itself). Passing null will use the      *            default sort order, which may be unordered.      *            (排序方式，为空则为默认排序方式)      * @return A {@link Cursor} object, which is positioned before the first entry. Note that      * {@link Cursor}s are not synchronized, see the documentation for more details.      * @see Cursor      */ public Cursor query(String table, String[] columns, String selection,             String[] selectionArgs, String groupBy, String having,             String orderBy) { return query(false, table, columns, selection, selectionArgs, groupBy,                 having, orderBy, null /* limit */); } 

• 2、SQLiteDataBase对象的insert()接口：

 /**      * Convenience method for inserting a row into the database.      *      * @param table the table to insert the row into(表名)      * @param nullColumnHack optional; may be <code>null</code>.      *            SQL doesn't allow inserting a completely empty row without      *            naming at least one column name.  If your provided <code>values</code> is      *            empty, no column names are known and an empty row can't be inserted.      *            If not set to null, the <code>nullColumnHack</code> parameter      *            provides the name of nullable column name to explicitly insert a NULL into      *            in the case where your <code>values</code> is empty.      *      ( 当values参数为空或者里面没有内容的时候，我们insert是会失败的（底层数据库不允许插入一个空行），为了防止这种情况，我们要在这里指定一个 列名，到时候如果发现将要插入的行为空行时，就会将你指定的这个列名的值设为null，然后再向数据库中插入。)      * @param values this map contains the initial column values for the      *            row. The keys should be the column names and the values the      *            column values      *         (一个ContentValues对象，类似一个map.通过键值对的形式存储值。)      * @return the row ID of the newly inserted row, or -1 if an error occurred      */ public long insert(String table, String nullColumnHack, ContentValues values) { try { return insertWithOnConflict(table, nullColumnHack, values, CONFLICT_NONE); } catch (SQLException e) {             Log.e(TAG, "Error inserting " + values, e); return -1; } } 

• 3、SQLiteDataBase对象的update()接口：

 /**      * Convenience method for updating rows in the database.      *      * @param table the table to update in(要更新的表名)      * @param values a map from column names to new column values. null is a      *            valid value that will be translated to NULL.      *                  * @param whereClause the optional WHERE clause to apply when updating.      *            Passing null will update all rows.      *    (可选的where语句)      * @param whereArgs You may include ?s in the where clause, which      *            will be replaced by the values from whereArgs. The values      *            will be bound as Strings.      *           (whereClause语句中表达式的？占位参数列表)      * @return the number of rows affected      */ public int update(String table, ContentValues values, String whereClause, String[] whereArgs) { return updateWithOnConflict(table, values, whereClause, whereArgs, CONFLICT_NONE); } 

• 4、SQLiteDataBase对象的delete()接口：

 /**      * Convenience method for deleting rows in the database.      *      * @param table the table to delete from(表名)      * @param whereClause the optional WHERE clause to apply when deleting.      *            Passing null will delete all rows.      *            (可选的where语句)      * @param whereArgs You may include ?s in the where clause, which      *            will be replaced by the values from whereArgs. The values      *            will be bound as Strings.      *            (whereClause语句中表达式的？占位参数列表)      * @return the number of rows affected if a whereClause is passed in, 0      *         otherwise. To remove all rows and get a count pass "1" as the      *         whereClause.      */ public int delete(String table, String whereClause, String[] whereArgs) { acquireReference(); try {             SQLiteStatement statement = new SQLiteStatement(this, "DELETE FROM " + table + (!TextUtils.isEmpty(whereClause) ? " WHERE " + whereClause : ""), whereArgs); try { return statement.executeUpdateDelete(); } finally {                 statement.close(); } } finally { releaseReference(); } }