实验四：SQLite和SQLiteDatabase应用
实验四：SQLite和SQLiteDatabase应用
一、实验目的
1、本次实验的目的是让大家熟悉Android中对数据库进行操作的相关的接口、类等。SQLiteDatabase这个是在android中数据库操作使用最频繁的一个类。通过它可以实现数据库的创建或打开、创建表、插入数据、删除数据、查询数据、修改数据等操作。
2、实现添加用户名，爱好小例程。
二、实验要求
1、完成Android开发平台的搭建及相关配置
2、创建项目并熟悉文件目录结构
3、实现例程添加用户名，爱好实验步骤
三、实验步骤
一、核心实现步骤
# 1. 创建数据库帮助类
java
// UserDbHelper.java
public class UserDbHelper extends SQLiteOpenHelper {
private static final String DATABASE_NAME = "UserInfo.db";
private static final int DATABASE_VERSION = 1;
// 表结构定义
private static final String SQL_CREATE_ENTRIES =
"CREATE TABLE " + UserContract.UserEntry.TABLE_NAME + " (" +
UserContract.UserEntry._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
UserContract.UserEntry.COLUMN_NAME + " TEXT NOT NULL," +
UserContract.UserEntry.COLUMN_HOBBY + " TEXT)";
public UserDbHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(SQL_CREATE_ENTRIES);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("DROP TABLE IF EXISTS " + UserContract.UserEntry.TABLE_NAME);
onCreate(db);
}
}
// 数据表契约类
public final class UserContract {
private UserContract() {}
public static class UserEntry implements BaseColumns {
public static final String TABLE_NAME = "users";
public static final String COLUMN_NAME = "username";
public static final String COLUMN_HOBBY = "hobby";
}
}
# 2. 主界面布局设计
xml
<!-- activity_main.xml -->
<LinearLayout
xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:orientation="vertical"
android:padding="16dp">
<EditText
android:id="@+id/et_username"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:hint="输入用户名"/>
<EditText
android:id="@+id/et_hobby"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:hint="输入爱好"/>
<Button
android:id="@+id/btn_save"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="保存数据"/>
<TextView
android:id="@+id/tv_display"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:paddingTop="16dp"/>
</LinearLayout>
# 3. 数据库操作实现
java
// MainActivity.java
public class MainActivity extends AppCompatActivity {
private UserDbHelper dbHelper;
private TextView tvDisplay;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
dbHelper = new UserDbHelper(this);
tvDisplay = findViewById(R.id.tv_display);
Button btnSave = findViewById(R.id.btn_save);
btnSave.setOnClickListener(v -> saveToDatabase());
displayAllData();
}
// 数据保存方法
private void saveToDatabase() {
EditText etName = findViewById(R.id.et_username);
EditText etHobby = findViewById(R.id.et_hobby);
String username = etName.getText().toString().trim();
String hobby = etHobby.getText().toString().trim();
if (username.isEmpty()) {
Toast.makeText(this, "用户名不能为空", Toast.LENGTH_SHORT).show();
return;
}
SQLiteDatabase db = dbHelper.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(UserContract.UserEntry.COLUMN_NAME, username);
values.put(UserContract.UserEntry.COLUMN_HOBBY, hobby);
try {
long newRowId = db.insert(
UserContract.UserEntry.TABLE_NAME,
null,
values);
if (newRowId == -1) {
Toast.makeText(this, "保存失败", Toast.LENGTH_SHORT).show();
} else {
Toast.makeText(this, "数据已保存，ID: " + newRowId, Toast.LENGTH_SHORT).show();
clearInputs();
displayAllData();
}
} finally {
db.close();
}
}
// 显示所有数据
private void displayAllData() {
SQLiteDatabase db = dbHelper.getReadableDatabase();
String[] projection = {
UserContract.UserEntry._ID,
UserContract.UserEntry.COLUMN_NAME,
UserContract.UserEntry.COLUMN_HOBBY
};
Cursor cursor = db.query(
UserContract.UserEntry.TABLE_NAME,
projection,
null,
null,
null,
null,
null
);
StringBuilder builder = new StringBuilder();
try {
while (cursor.moveToNext()) {
long id = cursor.getLong(
cursor.getColumnIndexOrThrow(UserContract.UserEntry._ID));
String name = cursor.getString(
cursor.getColumnIndexOrThrow(UserContract.UserEntry.COLUMN_NAME));
String hobby = cursor.getString(
cursor.getColumnIndexOrThrow(UserContract.UserEntry.COLUMN_HOBBY));
builder.append("ID:").append(id)
.append(" 用户名:").append(name)
.append(" 爱好:").append(hobby)
.append("\n\n");
}
} finally {
cursor.close();
db.close();
}
tvDisplay.setText(builder.toString());
}
private void clearInputs() {
((EditText)findViewById(R.id.et_username)).setText("");
((EditText)findViewById(R.id.et_hobby)).setText("");
}
@Override
protected void onDestroy() {
dbHelper.close();
super.onDestroy();
}
}
二、扩展功能实现
# 1. 数据删除功能
java
// 在MainActivity中添加
private void deleteData(long id) {
SQLiteDatabase db = dbHelper.getWritableDatabase();
String selection = UserContract.UserEntry._ID + " = ?";
String[] selectionArgs = { String.valueOf(id) };
int deletedRows = db.delete(
UserContract.UserEntry.TABLE_NAME,
selection,
selectionArgs);
if (deletedRows > 0) {
Toast.makeText(this, "删除成功", Toast.LENGTH_SHORT).show();
displayAllData();
}
}
# 2. 数据更新功能
java
private void updateData(long id, String newHobby) {
SQLiteDatabase db = dbHelper.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(UserContract.UserEntry.COLUMN_HOBBY, newHobby);
String selection = UserContract.UserEntry._ID + " = ?";
String[] selectionArgs = { String.valueOf(id) };
int count = db.update(
UserContract.UserEntry.TABLE_NAME,
values,
selection,
selectionArgs);
if (count > 0) {
Toast.makeText(this, "更新成功", Toast.LENGTH_SHORT).show();
displayAllData();
}
}
3. 使用AsyncTask处理数据库操作
java
private class DatabaseTask extends AsyncTask<Void, Void, String> {
protected String doInBackground(Void... voids) {
// 在此执行数据库操作
return queryResult;
}
protected void onPostExecute(String result) {
// 更新UI
}
}
4. 添加数据分页功能
java
// 在查询时添加分页参数
String limit = "10";
String offset = String.valueOf(currentPage * 10);
cursor = db.query(..., null, null, null, null, offset + "," + limit);
5 使用数据库事务
java
db.beginTransaction();
try {
// 批量操作
db.setTransactionSuccessful();
} finally {
db.endTransaction();
}
问题解决方案
1. 数据库表未创建
- 检查SQL语句语法是否正确
- 卸载应用重新安装触发onCreate
- 使用`adb shell`查看数据库文件
2. 数据重复插入
java
// 添加唯一性约束
"CREATE TABLE ... ("
+ UserContract.UserEntry.COLUMN_NAME + " TEXT UNIQUE NOT NULL,"
+ "... );"
浙公网安备 33010602011771号