安卓操作SQLite数据库主要是使用SQLiteOpenHelper帮助类，他是一个抽象类，所以要有一个类继承他实现他，有两个抽象方法onCreate()和onUpgrade()，前者是创建数据库时调用，后者可以通过控制更新版本来调用。还有两个实例方法用来读写数据库，分别是getReadableDatebase()和getWritableDatebase()。
现在以一个小项目SQLiteTest来看具体调用方法，以备后用。如果要封装更高级功能，推荐使用第三方的LitePal。
AndroidManifest.xml
<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
package="com.example.sqlitetest">
<application
android:allowBackup="true"
android:icon="@mipmap/ic_launcher"
android:label="@string/app_name"
android:roundIcon="@mipmap/ic_launcher_round"
android:supportsRtl="true"
android:theme="@style/AppTheme">
<activity android:name=".MainActivity">
<intent-filter>
<action android:name="android.intent.action.MAIN" />
<category android:name="android.intent.category.LAUNCHER" />
</intent-filter>
</activity>
</application>
</manifest>
新建实现类
package com.example.sqlitetest;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.widget.Toast;
public class MyDatabaseHelper extends SQLiteOpenHelper {
public static final String CREATE_BOOK = "create table Book (" +
"id integer primary key autoincrement, " +
"author text, " +
"price real, " +
"pages integer, " +
"name text)";
public static final String CREATE_CATEGORY = "create table Category (" +
"id integer primary key autoincrement, " +
"category_name text, " +
"category_code integer)";
private Context mContext;
public MyDatabaseHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
super(context, name, factory, version);
mContext = context;
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(CREATE_BOOK);
db.execSQL(CREATE_CATEGORY);
Toast.makeText(mContext, "创建数据表成功！", Toast.LENGTH_SHORT).show();
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("DROP TABLE IF EXISTS Book");
db.execSQL("DROP TABLE IF EXISTS Category");
onCreate(db);
}
}
layout activity_main.xml
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:orientation="vertical"
android:layout_height="match_parent"
android:layout_width="match_parent"
>
<Button
android:id="@+id/create_database"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="创建数据库"/>
<Button
android:id="@+id/add_data"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="添加数据"/>
<Button
android:id="@+id/update_data"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="更新数据"/>
<Button
android:id="@+id/delete_data"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="删除数据"/>
<Button
android:id="@+id/query_data"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="查询数据"/>
</LinearLayout>
入口 MainActivity.class
package com.example.sqlitetest;
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;
public class MainActivity extends AppCompatActivity {
private MyDatabaseHelper dbHelper;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
dbHelper = new MyDatabaseHelper(this, "BooksStore.db", null , 2);
Button createDatabase = findViewById(R.id.create_database);
createDatabase.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
dbHelper.getWritableDatabase();
}
});
Button addDate = findViewById(R.id.add_data);
addDate.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
SQLiteDatabase db = dbHelper.getWritableDatabase();
ContentValues values = new ContentValues();
// 插入第一条数据
values.put("name", "第一本书");
values.put("author", "第一本书的作者");
values.put("pages", 454);
values.put("price", 16.96);
db.insert("Book", null, values);
values.clear();
// 插入第二条数据
values.put("name", "第二本书");
values.put("author", "第二本书的作者");
values.put("pages", 510);
values.put("price", 19.95);
db.insert("Book", null, values);
}
});
Button updateDate = findViewById(R.id.update_data);
updateDate.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
SQLiteDatabase db = dbHelper.getWritableDatabase();
ContentValues values = new ContentValues();
values.put("name", "更新过的书");
db.update("Book", values, "name = ?", new String[]{"第一本书"});
}
});
Button deleteDate = findViewById(R.id.delete_data);
deleteDate.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
SQLiteDatabase db = dbHelper.getWritableDatabase();
db.delete("Book", "pages > ?", new String[]{"500"});
}
});
Button queryDate = findViewById(R.id.query_data);
queryDate.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
SQLiteDatabase db = dbHelper.getReadableDatabase();
Cursor cursor = db.query("Book", null, null, null, null, null, null);
if(cursor.moveToFirst()) {
do {
String name = cursor.getString(cursor.getColumnIndex("name"));
String author = cursor.getString(cursor.getColumnIndex("author"));
int pages = cursor.getInt(cursor.getColumnIndex("pages"));
double price = cursor.getDouble(cursor.getColumnIndex("price"));
Log.d("MainActivity", "书名：" + name);
Log.d("MainActivity", "作者：" + author);
Log.d("MainActivity", "页数：" + pages);
Log.d("MainActivity", "价格：" + price);
} while (cursor.moveToNext());
}
}
});
}
}
