【例5-6】数据库记录的添加、查询、删除
完善eBook.db数据库的演示程序，增加添加、查询、删除记录的功能。
教学视频
步骤一、布局文件 diarydb.xml的源代码
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:tools="http://schemas.android.com/tools"
android:layout_width="fill_parent"
android:layout_height="fill_parent"
android:layout_margin="20dp"
android:orientation="vertical">
<Button
android:id="@+id/querydb"
android:layout_width="200dp"
android:layout_height="wrap_content"
android:layout_marginTop="10dp"
android:text="查询数据记录"
android:textSize="24sp" />
<Button
android:id="@+id/deleteItemBtn"
android:layout_width="200dp"
android:layout_height="wrap_content"
android:text="删除记录"
android:textSize="24sp" />
<Button
android:id="@+id/insertdb"
android:layout_width="200dp"
android:layout_height="wrap_content"
android:text="插入两条记录"
android:textSize="24sp" />
<Button
android:id="@+id/deletedb"
android:layout_width="200dp"
android:layout_height="wrap_content"
android:text="删除数据库"
android:textSize="24sp" />
<Button
android:id="@+id/createdb"
android:layout_width="200dp"
android:layout_height="wrap_content"
android:text="重建数据库"
android:textSize="24sp" />
<TextView
android:id="@+id/txt"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:textSize="22sp"
tools:text="运行" />
</LinearLayout>
步骤二、
接下来以DBActivity程序为基础，控制文件命名为DB2Activity.java。
（1）将数据库的名称等信息作为成员变量。
（2）完善内部类MySQLDatabase的功能，实现查询、添加、删除记录等方法。
（3）创建一个内部类DatabaseHelper管理数据库的版本。
DB2Activity.java 的源代码:
package com.example.chap05;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.TextView;
import android.widget.Toast;
import androidx.appcompat.app.AppCompatActivity;
public class DB2Activity extends AppCompatActivity {
Button createdb,deletedb,queryBtn,deleteItemBtn,insertBtn;
TextView txt;
MySQLDatabase myc;
DatabaseHelper myOpenHelper;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.diarydb);
createdb = (Button)findViewById(R.id.createdb);
deletedb = (Button)findViewById(R.id.deletedb);
queryBtn =(Button)findViewById(R.id.querydb);
deleteItemBtn = (Button)findViewById(R.id.deleteItemBtn);
insertBtn = (Button)findViewById(R.id.insertdb);
txt = (TextView)findViewById(R.id.txt);
createdb.setOnClickListener(new mClick());
deletedb.setOnClickListener(new mClick());
queryBtn.setOnClickListener(new mClick());
deleteItemBtn.setOnClickListener(new mClick());
insertBtn.setOnClickListener(new mClick());
myOpenHelper = new DatabaseHelper(this);
}
class mClick implements View.OnClickListener {
@Override
public void onClick(View view) {
if(view==createdb){
myc= new MySQLDatabase();
Toast.makeText(DB2Activity.this, "数据库和数据表创建成功", Toast.LENGTH_SHORT).show();
}else if(view==deletedb){
deleteDatabase(Database_name);
txt.setText("删除数据库成功");
Toast.makeText(DB2Activity.this, "数据库和数据表删除成功", Toast.LENGTH_SHORT).show();
}else if(view==queryBtn){
myc.showItems();
}else if(view==deleteItemBtn){
myc.deleteItem();
}else if(view==insertBtn){
myc.insertItem();
}
}
}
class DatabaseHelper extends SQLiteOpenHelper {
public DatabaseHelper(Context context){
super(context,Database_name,null,Database_Version);
}
@Override
public void onCreate(SQLiteDatabase sqLiteDatabase) {
}
@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
}
}
private static final String Database_name = "eBook.db";//数据库名
private static final String TABLE_NAME = "diary"; //数据表名
private static final String ID = "nid"; //ID编号
private static final String TITLE = "title"; //标题
private static final String BODY = "body"; //正文
private static final int Database_Version = 1;
private class MySQLDatabase{
SQLiteDatabase db;
private MySQLDatabase(){
try {
int mode = Context.MODE_PRIVATE;
//创建数据库
db = openOrCreateDatabase(Database_name, mode, null);
//SQL语句
String DATABASE_CREATE="CREATE TABLE IF NOT EXISTS " + TABLE_NAME + " (" +
ID + " INTEGER primary key autoincrement," +
TITLE + " text not null, " +
BODY + " text not null);";
db.execSQL(DATABASE_CREATE);//创建数据表
txt.setText("创建数据库成功");
}catch (SQLException e){
e.printStackTrace();
txt.setText("创建数据库失败");
}
}
/*
* 插入两条数据
*/
private void insertItem() {
try {
ContentValues values = new ContentValues();
values.put("TITLE", "Android");
values.put("BODY", "发展真是迅速啊");
db.insert(TABLE_NAME, ID, values);
values.put("TITLE", "城市");
values.put("BODY", "发展真是迅速啊");
db.insert(TABLE_NAME, ID, values);
txt.setText("插入两条数据成功");
}catch (SQLException e){txt.setText("插入两条数据失败");}
}
/*
* 删除符合条件的数据
*/
private void deleteItem() {
try {
db.delete(TABLE_NAME, " TITLE='城市'", null);
txt.setText("删除数据成功");
}catch (SQLException e){txt.setText("删除数据失败");}
}
/*
* 查询数据表
*/
private void showItems() {
try {
String[] col = {TITLE, BODY};
Cursor cursor = db.query(TABLE_NAME, col, null, null, null, null, null);
Integer num = cursor.getCount();
String str = "";
if (num > 0) {
cursor.moveToFirst();
str = "第一条记录：\n" + cursor.getString(0) + " " + cursor.getString(1);
}
String result = "共有" + Integer.toString(num) + "条记录 \n" + str;
txt.setText(result);
}catch (SQLException e){txt.setText("查询数据失败");}
}
}
}
步骤三、
接下来将程序DB2Activity.java 拆分成两个了文件：DB3Activity.java和MySQLDatabase.java。
数据库操作的类 MySQLDatabase.java，源代码如下：
package com.example.chap05;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
public class MySQLDatabase {
private static final String Database_name = "eBook.db";//数据库名
private static final String TABLE_NAME = "diary"; //数据表名
private static final String ID = "nid"; //ID编号
private static final String TITLE = "title"; //标题
private static final String BODY = "body"; //正文
private static final int Database_Version = 1;
private SQLiteDatabase db;
private Context context;
private DatabaseHelper myOpenHelper;
/*
* 创建数据库
*/
public void CreateDB(Context c){
context = c;
myOpenHelper = new DatabaseHelper(context);
try {
int mode = Context.MODE_PRIVATE;
//创建数据库
db = context.openOrCreateDatabase(Database_name, mode, null);
//SQL语句
String DATABASE_CREATE="CREATE TABLE IF NOT EXISTS " + TABLE_NAME + " (" +
ID + " INTEGER primary key autoincrement," +
TITLE + " text not null, " +
BODY + " text not null);";
db.execSQL(DATABASE_CREATE);//创建数据表
Log.i("SQLite","创建数据库成功");
}catch (SQLException e){
Log.i("SQLite","创建数据库失败");
}
}
/*
* 删除数据库
*/
public void deleteDB() {
try {
context.deleteDatabase(Database_name);
Log.i("SQLite","删除数据库成功");
}catch (SQLException e){Log.i("SQLite","删除数据库失败");}
}
/*
* 插入两条数据
*/
public void insertItem() {
try {
ContentValues values = new ContentValues();
values.put("TITLE", "Android");
values.put("BODY", "发展真是迅速啊");
db.insert(TABLE_NAME, ID, values);
values.put("TITLE", "城市");
values.put("BODY", "发展真是迅速啊");
db.insert(TABLE_NAME, ID, values);
Log.i("SQLite","插入两条数据成功");
}catch (SQLException e){Log.i("SQLite","插入两条数据失败");}
}
/*
* 删除符合条件的数据
*/
public void deleteItem() {
try {
db.delete(TABLE_NAME, " TITLE='城市'", null);
Log.i("SQLite","删除数据成功");
}catch (SQLException e){Log.i("SQLite","删除数据失败");}
}
/*
* 查询数据表
*/
public String showItems() {
try {
String[] col = {TITLE, BODY};
Cursor cursor = db.query(TABLE_NAME, col, null, null, null, null, null);
Integer num = cursor.getCount();
String str = "";
if (num > 0) {
cursor.moveToFirst();
str = "第一条记录：\n" + cursor.getString(0) + " " + cursor.getString(1);
}
String result = "共有" + Integer.toString(num) + "条记录 \n" + str;
return result;
}catch (SQLException e){Log.i("SQLite","查询数据失败");}
return null;
}
private class DatabaseHelper extends SQLiteOpenHelper {
public DatabaseHelper(Context context){
super(context,Database_name,null,Database_Version);
}
@Override
public void onCreate(SQLiteDatabase sqLiteDatabase) {
}
@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
}
}
}
步骤三的控制文件DB3Activity.java源代码：
package com.example.chap05;
import androidx.appcompat.app.AppCompatActivity;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.TextView;
public class DB3Activity extends AppCompatActivity {
Button createdb,deletedb,queryBtn,deleteItemBtn,insertBtn;
TextView txt;
MySQLDatabase myc;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.diarydb);
createdb = (Button)findViewById(R.id.createdb);
deletedb = (Button)findViewById(R.id.deletedb);
queryBtn =(Button)findViewById(R.id.querydb);
deleteItemBtn = (Button)findViewById(R.id.deleteItemBtn);
insertBtn = (Button)findViewById(R.id.insertdb);
txt = (TextView)findViewById(R.id.txt);
createdb.setOnClickListener(new mClick());
deletedb.setOnClickListener(new mClick());
queryBtn.setOnClickListener(new mClick());
deleteItemBtn.setOnClickListener(new mClick());
insertBtn.setOnClickListener(new mClick());
myc = new MySQLDatabase();
}
class mClick implements View.OnClickListener {
@Override
public void onClick(View view) {
if(view==createdb){
myc.CreateDB(DB3Activity.this);
txt.setText("创建数据库成功");
}else if(view==deletedb){
myc.deleteDB();
txt.setText("删除数据库成功");
}else if(view==queryBtn){
txt.setText(myc.showItems());
}else if(view==deleteItemBtn){
myc.deleteItem();
txt.setText("删除数据记录成功");
}else if(view==insertBtn){
myc.insertItem();
txt.setText("添加数据记录成功");
}
}
}
}
打开项目配置文件AndroidManifest.xml，配置要启动的Activity类名
<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
package="com.example.chap05">
<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=".DB3Activity">
<intent-filter>
<action android:name="android.intent.action.MAIN" />
<category android:name="android.intent.category.LAUNCHER" />
</intent-filter>
</activity>
</application>
</manifest>
