简介这是一份面向Android初学者与进阶开发者的完整项目实践资源聚焦SQLite本地数据库在Android Studio环境下的工程化应用解决移动端用户管理与数据持久化核心问题。项目实现标准登录注册流程并基于SQLite完成用户信息的增删改查CRUD全操作涵盖DatabaseHelper封装、SQL语句执行、Cursor数据解析及UI层联动等关键环节适用于课程设计、毕业项目或技能实训场景。压缩包共1287个文件主体为515个flat资源文件、205个dex字节码、203个class类文件、120个json配置及76个xml布局/清单文件辅以42个java源码和16个png图标整体9.1MB结构完整可直接导入Android Studio运行调试。已有1764人学习下载资源包含可运行的完整工程目录、清晰的数据库表设计逻辑、密码加密存储示意及deposit相关业务模块如寄存记录表助开发者快速掌握Android本地数据管理实战路径。1. 用 Android Studio 搭建本地 SQLite 驱动的 Android 应用从登录注册到完整 CRUD 操作你不需要后端服务器也不依赖网络请求——一个纯离线、基于 SQLite 的 Android 应用就能完成用户账户管理登录/注册和业务数据维护增删改查。这正是 Android Studio 原生开发中最具实操价值的入门闭环它绕开复杂网络栈与权限配置直击 Android 数据持久化核心能力。适合刚学完 Activity 生命周期、准备动手写第一个可运行 App 的开发者也适合需要快速验证业务逻辑、做原型演示或嵌入式设备本地管理模块的工程师。关键在于SQLite 不是“玩具数据库”它是 Android 系统级内置组件所有 API 都经过数十年迭代验证支持事务、外键、触发器、加密扩展需额外集成且在低端机型上仍保持毫秒级响应。本文不讲抽象概念只聚焦如何用 Android Studio 4.2兼容 AGP 7.4写出可编译、可调试、可复用的完整代码——包括 AccountDBHelper 封装、LoginActivity 与 RegisterActivity 的状态校验逻辑、RecyclerView 列表页的数据绑定技巧以及最易被忽略的 Cursor 关闭时机与主线程阻塞规避方案。2. 构建 SQLite 数据库助手类AccountDBHelper 的设计与实现2.1 为什么必须封装 DBHelper 而非直接使用 SQLiteDatabaseAndroid 提供的SQLiteDatabase是底层操作接口但直接调用execSQL()或rawQuery()会带来三类问题一是 SQL 字符串硬编码导致拼接错误难定位如name username引发注入风险二是表结构变更时需手动修改所有CREATE TABLE语句缺乏版本迁移能力三是多线程并发访问时未加锁易引发database locked异常。SQLiteOpenHelper正是为解决这些问题而存在——它通过onCreate()和onUpgrade()抽象出数据库生命周期自动管理版本号递增与 schema 迁移并保证getReadableDatabase()/getWritableDatabase()返回的实例线程安全。常见误用是每次操作都新建 Helper 实例这会导致连接池失效正确做法是单例持有或 Application Context 中初始化。2.2 AccountDBHelper 完整代码与关键参数说明public class AccountDBHelper extends SQLiteOpenHelper { private static final String DATABASE_NAME account_manager.db; private static final int DATABASE_VERSION 3; // 版本号必须递增 // 用户表结构定义含注释字段 private static final String TABLE_USERS users; private static final String COLUMN_ID id; private static final String COLUMN_USERNAME username; private static final String COLUMN_PASSWORD password; private static final String COLUMN_EMAIL email; private static final String COLUMN_CREATED_AT created_at; public AccountDBHelper(Nullable Context context) { super(context, DATABASE_NAME, null, DATABASE_VERSION); } Override public void onCreate(SQLiteDatabase db) { // 使用 TEXT NOT NULL DEFAULT 避免空值陷阱 String CREATE_USERS_TABLE CREATE TABLE TABLE_USERS ( COLUMN_ID INTEGER PRIMARY KEY AUTOINCREMENT, COLUMN_USERNAME TEXT UNIQUE NOT NULL, COLUMN_PASSWORD TEXT NOT NULL, COLUMN_EMAIL TEXT, COLUMN_CREATED_AT INTEGER DEFAULT (strftime(%s,now)) // Unix 时间戳 ); db.execSQL(CREATE_USERS_TABLE); } Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { // 版本升级策略保留旧数据仅添加新字段 if (oldVersion 2) { db.execSQL(ALTER TABLE TABLE_USERS ADD COLUMN COLUMN_EMAIL TEXT); } if (oldVersion 3) { db.execSQL(ALTER TABLE TABLE_USERS ADD COLUMN COLUMN_CREATED_AT INTEGER DEFAULT (strftime(%s,now))); } } }提示DATABASE_VERSION必须为整数且严格递增。当onUpgrade()被触发时系统会按oldVersion到newVersion的差值执行对应if分支因此不能用switch或跳过中间版本。strftime(%s,now)是 SQLite 内置函数生成秒级时间戳比 JavaSystem.currentTimeMillis()更可靠避免时区转换错误。2.3 插入、查询、更新、删除的标准化方法封装// 在 AccountDBHelper 类中添加以下方法 public long insertUser(String username, String password, String email) { SQLiteDatabase db this.getWritableDatabase(); ContentValues values new ContentValues(); values.put(COLUMN_USERNAME, username); values.put(COLUMN_PASSWORD, password); // 生产环境应使用 BCrypt 加密 values.put(COLUMN_EMAIL, email); long result db.insert(TABLE_USERS, null, values); db.close(); // 必须关闭否则连接泄漏 return result; } public User getUserByUsername(String username) { SQLiteDatabase db this.getReadableDatabase(); String[] columns {COLUMN_ID, COLUMN_USERNAME, COLUMN_PASSWORD, COLUMN_EMAIL, COLUMN_CREATED_AT}; Cursor cursor db.query(TABLE_USERS, columns, COLUMN_USERNAME ?, new String[]{username}, null, null, null); User user null; if (cursor.moveToFirst()) { user new User( cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_ID)), cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_USERNAME)), cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_PASSWORD)), cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_EMAIL)), cursor.getLong(cursor.getColumnIndexOrThrow(COLUMN_CREATED_AT)) ); } cursor.close(); // 关键Cursor 不关闭会导致内存泄漏 db.close(); return user; } public int updateUserEmail(String username, String newEmail) { SQLiteDatabase db this.getWritableDatabase(); ContentValues values new ContentValues(); values.put(COLUMN_EMAIL, newEmail); int rows db.update(TABLE_USERS, values, COLUMN_USERNAME ?, new String[]{username}); db.close(); return rows; } public int deleteUser(String username) { SQLiteDatabase db this.getWritableDatabase(); int rows db.delete(TABLE_USERS, COLUMN_USERNAME ?, new String[]{username}); db.close(); return rows; }注意ContentValues是 Android 提供的安全参数容器自动处理字符串转义杜绝 SQL 注入。cursor.getColumnIndexOrThrow()比getColumnIndex()更健壮——当字段名拼错时直接抛异常而非返回 -1 导致崩溃。所有Cursor和SQLiteDatabase实例必须显式close()尤其在Activity的onDestroy()中未释放时App 可能因SQLiteFullException崩溃。3. 登录与注册界面实现Activity 交互逻辑与输入校验3.1 布局文件 activity_login.xml 的关键约束使用ConstraintLayout替代LinearLayout可减少嵌套层级提升渲染性能。重点控制输入框行为com.google.android.material.textfield.TextInputLayout android:idid/til_username android:layout_width0dp android:layout_heightwrap_content app:layout_constraintTop_toTopOfparent app:layout_constraintStart_toStartOfparent app:layout_constraintEnd_toEndOfparent app:hintTextAppearancestyle/TextAppearance.MaterialComponents.Caption app:errorEnabledtrue com.google.android.material.textfield.TextInputEditText android:idid/et_username android:layout_widthmatch_parent android:layout_heightwrap_content android:hintstring/hint_username android:inputTypetextPersonName android:maxLength20 / /com.google.android.material.textfield.TextInputLayout提示android:maxLength20限制用户名长度避免 SQLiteTEXT字段溢出android:inputTypetextPersonName触发软键盘姓名模式提升用户体验TextInputLayout的app:errorEnabledtrue支持动态显示错误提示如“用户名已存在”无需自定义 Toast。3.2 LoginActivity 中的登录流程与异步处理public class LoginActivity extends AppCompatActivity { private AccountDBHelper dbHelper; Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_login); dbHelper new AccountDBHelper(this); findViewById(R.id.btn_login).setOnClickListener(v - { String username ((TextInputEditText) findViewById(R.id.et_username)).getText().toString().trim(); String password ((TextInputEditText) findViewById(R.id.et_password)).getText().toString(); if (username.isEmpty() || password.isEmpty()) { showError(用户名和密码不能为空); return; } // 切换到后台线程避免阻塞 UI new AsyncTaskVoid, Void, User() { Override protected User doInBackground(Void... voids) { return dbHelper.getUserByUsername(username); } Override protected void onPostExecute(User user) { if (user null) { showError(用户名不存在); } else if (!user.getPassword().equals(password)) { showError(密码错误); } else { // 登录成功跳转主界面 Intent intent new Intent(LoginActivity.this, MainActivity.class); intent.putExtra(user_id, user.getId()); startActivity(intent); finish(); } } }.execute(); }); } private void showError(String message) { TextInputLayout til findViewById(R.id.til_username); til.setError(message); til.requestFocus(); } }注意AsyncTask在 Android 11 已废弃但对本场景仍适用无复杂生命周期依赖。若目标 SDK ≥ 33应改用ExecutorServiceHandler(Looper.getMainLooper())组合。doInBackground()中调用dbHelper.getUserByUsername()是唯一耗时操作必须剥离 UI 线程onPostExecute()在主线程执行可安全操作 View。3.3 RegisterActivity 的唯一性校验与密码强度检查// 在 RegisterActivity 中添加注册按钮点击逻辑 findViewById(R.id.btn_register).setOnClickListener(v - { String username ((TextInputEditText) findViewById(R.id.et_username)).getText().toString().trim(); String password ((TextInputEditText) findViewById(R.id.et_password)).getText().toString(); String email ((TextInputEditText) findViewById(R.id.et_email)).getText().toString().trim(); if (!isValidUsername(username)) { showError(用户名只能包含字母、数字和下划线长度 3-15 位); return; } if (!isValidPassword(password)) { showError(密码至少8位需含大小写字母和数字); return; } if (!android.util.Patterns.EMAIL_ADDRESS.matcher(email).matches()) { showError(邮箱格式不正确); return; } // 检查用户名是否已存在同步查询因注册频次低 User existing dbHelper.getUserByUsername(username); if (existing ! null) { showError(用户名已被占用); return; } long result dbHelper.insertUser(username, password, email); if (result ! -1) { Toast.makeText(this, 注册成功, Toast.LENGTH_SHORT).show(); finish(); } else { showError(注册失败请重试); } }); private boolean isValidUsername(String s) { return s.length() 3 s.length() 15 s.matches([a-zA-Z0-9_]); } private boolean isValidPassword(String s) { return s.length() 8 s.matches(.*[a-z].*) s.matches(.*[A-Z].*) s.matches(.*\\d.*); }提示Patterns.EMAIL_ADDRESS是 Android 内置正则比手写^[a-zA-Z0-9._%-][a-zA-Z0-9.-]\\.[a-zA-Z]{2,}$更可靠密码强度检查用多个matches()分散验证避免复杂正则难以调试insertUser()返回-1表示插入失败如 UNIQUE 约束冲突这是 SQLite 的标准返回值。4. 主界面 RecyclerView 与数据操作实现完整的增删改查交互链4.1 创建 UserAdapter 适配器并绑定点击事件public class UserAdapter extends RecyclerView.AdapterUserAdapter.ViewHolder { private ListUser users; private AccountDBHelper dbHelper; private OnUserActionListener listener; public interface OnUserActionListener { void onEdit(User user); void onDelete(User user); } public UserAdapter(ListUser users, AccountDBHelper dbHelper, OnUserActionListener listener) { this.users users; this.dbHelper dbHelper; this.listener listener; } NonNull Override public ViewHolder onCreateViewHolder(NonNull ViewGroup parent, int viewType) { View view LayoutInflater.from(parent.getContext()) .inflate(R.layout.item_user, parent, false); return new ViewHolder(view); } Override public void onBindViewHolder(NonNull ViewHolder holder, int position) { User user users.get(position); holder.tvUsername.setText(user.getUsername()); holder.tvEmail.setText(user.getEmail()); holder.tvCreatedAt.setText(new Date(user.getCreatedAt() * 1000L).toLocaleString()); holder.btnEdit.setOnClickListener(v - listener.onEdit(user)); holder.btnDelete.setOnClickListener(v - listener.onDelete(user)); } Override public int getItemCount() { return users.size(); } static class ViewHolder extends RecyclerView.ViewHolder { TextView tvUsername, tvEmail, tvCreatedAt; Button btnEdit, btnDelete; ViewHolder(View itemView) { super(itemView); tvUsername itemView.findViewById(R.id.tv_username); tvEmail itemView.findViewById(R.id.tv_email); tvCreatedAt itemView.findViewById(R.id.tv_created_at); btnEdit itemView.findViewById(R.id.btn_edit); btnDelete itemView.findViewById(R.id.btn_delete); } } }注意item_user.xml中每个TextView必须设置android:maxLines1防止文本撑开布局btnEdit和btnDelete使用android:background?attr/selectableItemBackgroundBorderless实现水波纹效果new Date(...).toLocaleString()仅用于演示生产环境应使用SimpleDateFormat或java.time格式化。4.2 MainActivity 中加载数据与响应操作public class MainActivity extends AppCompatActivity implements UserAdapter.OnUserActionListener { private RecyclerView recyclerView; private UserAdapter adapter; private ListUser userList; private AccountDBHelper dbHelper; Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); dbHelper new AccountDBHelper(this); userList new ArrayList(); adapter new UserAdapter(userList, dbHelper, this); recyclerView findViewById(R.id.rv_users); recyclerView.setLayoutManager(new LinearLayoutManager(this)); recyclerView.setAdapter(adapter); loadUsers(); // 初始化加载 // 添加浮动按钮触发新增 findViewById(R.id.fab_add).setOnClickListener(v - { Intent intent new Intent(MainActivity.this, AddUserActivity.class); startActivityForResult(intent, REQUEST_CODE_ADD_USER); }); } private void loadUsers() { userList.clear(); Cursor cursor dbHelper.getReadableDatabase().rawQuery( SELECT * FROM AccountDBHelper.TABLE_USERS ORDER BY AccountDBHelper.COLUMN_CREATED_AT DESC, null); while (cursor.moveToNext()) { userList.add(new User( cursor.getInt(cursor.getColumnIndexOrThrow(AccountDBHelper.COLUMN_ID)), cursor.getString(cursor.getColumnIndexOrThrow(AccountDBHelper.COLUMN_USERNAME)), cursor.getString(cursor.getColumnIndexOrThrow(AccountDBHelper.COLUMN_PASSWORD)), cursor.getString(cursor.getColumnIndexOrThrow(AccountDBHelper.COLUMN_EMAIL)), cursor.getLong(cursor.getColumnIndexOrThrow(AccountDBHelper.COLUMN_CREATED_AT)) )); } cursor.close(); adapter.notifyDataSetChanged(); } Override protected void onActivityResult(int requestCode, int resultCode, Nullable Intent data) { super.onActivityResult(requestCode, resultCode, data); if (requestCode REQUEST_CODE_ADD_USER resultCode RESULT_OK) { loadUsers(); // 新增后刷新列表 } } Override public void onEdit(User user) { Intent intent new Intent(this, EditUserActivity.class); intent.putExtra(user_id, user.getId()); startActivityForResult(intent, REQUEST_CODE_EDIT_USER); } Override public void onDelete(User user) { new AlertDialog.Builder(this) .setTitle(确认删除) .setMessage(确定要删除用户 user.getUsername() 吗) .setPositiveButton(删除, (dialog, which) - { int rows dbHelper.deleteUser(user.getUsername()); if (rows 0) { Toast.makeText(this, 删除成功, Toast.LENGTH_SHORT).show(); loadUsers(); } }) .setNegativeButton(取消, null) .show(); } }提示loadUsers()中使用rawQuery()直接执行 SQL因其需按时间倒序排列而query()方法不支持ORDER BY子句的灵活组合startActivityForResult()在 Android 11 已弃用但本例中AddUserActivity和EditUserActivity为简单页面无复杂返回数据故仍适用AlertDialog的setPositiveButton回调中执行dbHelper.deleteUser()确保删除操作原子性。4.3 AddUserActivity 与 EditUserActivity 的数据提交逻辑// AddUserActivity.java public class AddUserActivity extends AppCompatActivity { private AccountDBHelper dbHelper; Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_add_user); dbHelper new AccountDBHelper(this); findViewById(R.id.btn_save).setOnClickListener(v - { String username ((TextInputEditText) findViewById(R.id.et_username)).getText().toString().trim(); String password ((TextInputEditText) findViewById(R.id.et_password)).getText().toString(); String email ((TextInputEditText) findViewById(R.id.et_email)).getText().toString().trim(); if (username.isEmpty() || password.isEmpty()) { Toast.makeText(this, 用户名和密码不能为空, Toast.LENGTH_SHORT).show(); return; } long result dbHelper.insertUser(username, password, email); if (result ! -1) { setResult(RESULT_OK); finish(); } else { Toast.makeText(this, 保存失败, Toast.LENGTH_SHORT).show(); } }); } }// EditUserActivity.java public class EditUserActivity extends AppCompatActivity { private AccountDBHelper dbHelper; private int userId; Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_edit_user); dbHelper new AccountDBHelper(this); userId getIntent().getIntExtra(user_id, -1); if (userId -1) { Toast.makeText(this, 无效用户ID, Toast.LENGTH_SHORT).show(); finish(); return; } User user loadUserById(userId); if (user null) { Toast.makeText(this, 用户不存在, Toast.LENGTH_SHORT).show(); finish(); return; } ((TextInputEditText) findViewById(R.id.et_username)).setText(user.getUsername()); ((TextInputEditText) findViewById(R.id.et_email)).setText(user.getEmail()); findViewById(R.id.btn_save).setOnClickListener(v - { String username ((TextInputEditText) findViewById(R.id.et_username)).getText().toString().trim(); String email ((TextInputEditText) findViewById(R.id.et_email)).getText().toString().trim(); int rows dbHelper.updateUserEmail(username, email); if (rows 0) { setResult(RESULT_OK); finish(); } else { Toast.makeText(this, 更新失败, Toast.LENGTH_SHORT).show(); } }); } private User loadUserById(int id) { Cursor cursor dbHelper.getReadableDatabase().query( AccountDBHelper.TABLE_USERS, null, AccountDBHelper.COLUMN_ID ?, new String[]{String.valueOf(id)}, null, null, null ); User user null; if (cursor.moveToFirst()) { user new User( cursor.getInt(cursor.getColumnIndexOrThrow(AccountDBHelper.COLUMN_ID)), cursor.getString(cursor.getColumnIndexOrThrow(AccountDBHelper.COLUMN_USERNAME)), cursor.getString(cursor.getColumnIndexOrThrow(AccountDBHelper.COLUMN_PASSWORD)), cursor.getString(cursor.getColumnIndexOrThrow(AccountDBHelper.COLUMN_EMAIL)), cursor.getLong(cursor.getColumnIndexOrThrow(AccountDBHelper.COLUMN_CREATED_AT)) ); } cursor.close(); return user; } }注意EditUserActivity中未提供修改密码功能因密码字段通常不可逆需哈希存储此处仅演示邮箱更新loadUserById()使用query()方法其参数顺序为table, columns, selection, selectionArgs, groupBy, having, orderBy务必按此顺序传参否则selectionArgs会被误认为groupBysetResult(RESULT_OK)是startActivityForResult()的约定返回信号触发MainActivity.onActivityResult()。5. SQLite 性能优化与常见坑点排查从 Cursor 泄漏到事务一致性5.1 使用事务批量插入提升 10 倍写入速度当需要一次性插入 100 条用户记录时逐条insertUser()会触发 100 次磁盘 I/O。正确做法是包裹在事务中public void batchInsertUsers(ListUser users) { SQLiteDatabase db this.getWritableDatabase(); db.beginTransaction(); try { for (User user : users) { ContentValues values new ContentValues(); values.put(COLUMN_USERNAME, user.getUsername()); values.put(COLUMN_PASSWORD, user.getPassword()); values.put(COLUMN_EMAIL, user.getEmail()); db.insert(TABLE_USERS, null, values); } db.setTransactionSuccessful(); } finally { db.endTransaction(); db.close(); } }提示beginTransaction()后必须配对endTransaction()否则数据库将处于锁定状态setTransactionSuccessful()表示事务成功若未调用则endTransaction()会回滚所有操作批量插入时db.insert()返回值无需检查因事务内单条失败不影响整体除非手动抛异常。5.2 Cursor 清单式检查表避免内存泄漏的 5 个关键节点场景错误写法正确写法原因query()后未关闭Cursor c db.query(...); c.moveToFirst();try (Cursor c db.query(...)) { ... }Cursor实现AutoCloseabletry-with-resources 自动关闭多层嵌套 Cursorwhile(c1.moveToNext()) { Cursor c2 db.query(...); }外层c1关闭前内层c2必须先关闭多个 Cursor 共享同一数据库连接未关闭会耗尽句柄Adapter 中复用 Cursorcursor dbHelper.getCursor(); adapter.swapCursor(cursor);adapter.changeCursor(cursor);并在onDetachedFromRecyclerView()中关闭swapCursor()不关闭旧 Cursor需手动管理异步线程中使用 Cursornew Thread(() - { Cursor c db.query(...); }).start();改用LoaderManager或CursorLoaderCursor 绑定主线程 Looper子线程访问会崩溃rawQuery()未指定 projectiondb.rawQuery(SELECT * FROM users, null)db.rawQuery(SELECT id,username,email FROM users, null)SELECT *在表结构变更时易引发IllegalArgumentException5.3 验证 SQLite 数据库文件是否正常生成Android Studio 提供 Database Inspector 工具View → Tool Windows → Database Inspector但需满足设备为 API 26 的模拟器或真机App 进程正在运行非停止状态build.gradle中启用debuggable true若无法连接可手动导出数据库文件验证# 连接设备后执行 adb shell run-as com.yourpackage cat /data/data/com.yourpackage/databases/account_manager.db account.db # 在 PC 上用 DB Browser for SQLite 打开 account.db 查看表结构与数据注意run-as命令仅对 debug 包有效release 包因签名不同无法访问/data/data/目录导出的.db文件可用开源工具 DB Browser for SQLite 直接打开查看users表是否包含预期记录验证INSERT逻辑是否生效。5.4 解决 “no such table: users” 错误的三步定位法该错误 90% 源于onCreate()未被调用原因有三数据库版本未重置首次安装后修改DATABASE_VERSION但未卸载 ApponCreate()不会再次执行。解决方案adb uninstall com.yourpackage彻底清除数据。Helper 实例未正确初始化在Application类中全局初始化AccountDBHelper避免Activity中多次new导致版本混乱。表名拼写不一致CREATE TABLE users与db.query(Users, ...)中大小写不匹配Linux 文件系统区分大小写。解决方案统一使用小写表名或在query()中用TABLE_USERS常量。验证方法在onCreate()中添加Log.d(DB, Creating table...);运行 App 后Logcat过滤DB标签确认日志是否输出。本文还有配套的精品资源点击获取
Android SQLite本地数据库开发实战：登录注册与CRUD完整实现
简介这是一份面向Android初学者与进阶开发者的完整项目实践资源聚焦SQLite本地数据库在Android Studio环境下的工程化应用解决移动端用户管理与数据持久化核心问题。项目实现标准登录注册流程并基于SQLite完成用户信息的增删改查CRUD全操作涵盖DatabaseHelper封装、SQL语句执行、Cursor数据解析及UI层联动等关键环节适用于课程设计、毕业项目或技能实训场景。压缩包共1287个文件主体为515个flat资源文件、205个dex字节码、203个class类文件、120个json配置及76个xml布局/清单文件辅以42个java源码和16个png图标整体9.1MB结构完整可直接导入Android Studio运行调试。已有1764人学习下载资源包含可运行的完整工程目录、清晰的数据库表设计逻辑、密码加密存储示意及deposit相关业务模块如寄存记录表助开发者快速掌握Android本地数据管理实战路径。1. 用 Android Studio 搭建本地 SQLite 驱动的 Android 应用从登录注册到完整 CRUD 操作你不需要后端服务器也不依赖网络请求——一个纯离线、基于 SQLite 的 Android 应用就能完成用户账户管理登录/注册和业务数据维护增删改查。这正是 Android Studio 原生开发中最具实操价值的入门闭环它绕开复杂网络栈与权限配置直击 Android 数据持久化核心能力。适合刚学完 Activity 生命周期、准备动手写第一个可运行 App 的开发者也适合需要快速验证业务逻辑、做原型演示或嵌入式设备本地管理模块的工程师。关键在于SQLite 不是“玩具数据库”它是 Android 系统级内置组件所有 API 都经过数十年迭代验证支持事务、外键、触发器、加密扩展需额外集成且在低端机型上仍保持毫秒级响应。本文不讲抽象概念只聚焦如何用 Android Studio 4.2兼容 AGP 7.4写出可编译、可调试、可复用的完整代码——包括 AccountDBHelper 封装、LoginActivity 与 RegisterActivity 的状态校验逻辑、RecyclerView 列表页的数据绑定技巧以及最易被忽略的 Cursor 关闭时机与主线程阻塞规避方案。2. 构建 SQLite 数据库助手类AccountDBHelper 的设计与实现2.1 为什么必须封装 DBHelper 而非直接使用 SQLiteDatabaseAndroid 提供的SQLiteDatabase是底层操作接口但直接调用execSQL()或rawQuery()会带来三类问题一是 SQL 字符串硬编码导致拼接错误难定位如name username引发注入风险二是表结构变更时需手动修改所有CREATE TABLE语句缺乏版本迁移能力三是多线程并发访问时未加锁易引发database locked异常。SQLiteOpenHelper正是为解决这些问题而存在——它通过onCreate()和onUpgrade()抽象出数据库生命周期自动管理版本号递增与 schema 迁移并保证getReadableDatabase()/getWritableDatabase()返回的实例线程安全。常见误用是每次操作都新建 Helper 实例这会导致连接池失效正确做法是单例持有或 Application Context 中初始化。2.2 AccountDBHelper 完整代码与关键参数说明public class AccountDBHelper extends SQLiteOpenHelper { private static final String DATABASE_NAME account_manager.db; private static final int DATABASE_VERSION 3; // 版本号必须递增 // 用户表结构定义含注释字段 private static final String TABLE_USERS users; private static final String COLUMN_ID id; private static final String COLUMN_USERNAME username; private static final String COLUMN_PASSWORD password; private static final String COLUMN_EMAIL email; private static final String COLUMN_CREATED_AT created_at; public AccountDBHelper(Nullable Context context) { super(context, DATABASE_NAME, null, DATABASE_VERSION); } Override public void onCreate(SQLiteDatabase db) { // 使用 TEXT NOT NULL DEFAULT 避免空值陷阱 String CREATE_USERS_TABLE CREATE TABLE TABLE_USERS ( COLUMN_ID INTEGER PRIMARY KEY AUTOINCREMENT, COLUMN_USERNAME TEXT UNIQUE NOT NULL, COLUMN_PASSWORD TEXT NOT NULL, COLUMN_EMAIL TEXT, COLUMN_CREATED_AT INTEGER DEFAULT (strftime(%s,now)) // Unix 时间戳 ); db.execSQL(CREATE_USERS_TABLE); } Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { // 版本升级策略保留旧数据仅添加新字段 if (oldVersion 2) { db.execSQL(ALTER TABLE TABLE_USERS ADD COLUMN COLUMN_EMAIL TEXT); } if (oldVersion 3) { db.execSQL(ALTER TABLE TABLE_USERS ADD COLUMN COLUMN_CREATED_AT INTEGER DEFAULT (strftime(%s,now))); } } }提示DATABASE_VERSION必须为整数且严格递增。当onUpgrade()被触发时系统会按oldVersion到newVersion的差值执行对应if分支因此不能用switch或跳过中间版本。strftime(%s,now)是 SQLite 内置函数生成秒级时间戳比 JavaSystem.currentTimeMillis()更可靠避免时区转换错误。2.3 插入、查询、更新、删除的标准化方法封装// 在 AccountDBHelper 类中添加以下方法 public long insertUser(String username, String password, String email) { SQLiteDatabase db this.getWritableDatabase(); ContentValues values new ContentValues(); values.put(COLUMN_USERNAME, username); values.put(COLUMN_PASSWORD, password); // 生产环境应使用 BCrypt 加密 values.put(COLUMN_EMAIL, email); long result db.insert(TABLE_USERS, null, values); db.close(); // 必须关闭否则连接泄漏 return result; } public User getUserByUsername(String username) { SQLiteDatabase db this.getReadableDatabase(); String[] columns {COLUMN_ID, COLUMN_USERNAME, COLUMN_PASSWORD, COLUMN_EMAIL, COLUMN_CREATED_AT}; Cursor cursor db.query(TABLE_USERS, columns, COLUMN_USERNAME ?, new String[]{username}, null, null, null); User user null; if (cursor.moveToFirst()) { user new User( cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_ID)), cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_USERNAME)), cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_PASSWORD)), cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_EMAIL)), cursor.getLong(cursor.getColumnIndexOrThrow(COLUMN_CREATED_AT)) ); } cursor.close(); // 关键Cursor 不关闭会导致内存泄漏 db.close(); return user; } public int updateUserEmail(String username, String newEmail) { SQLiteDatabase db this.getWritableDatabase(); ContentValues values new ContentValues(); values.put(COLUMN_EMAIL, newEmail); int rows db.update(TABLE_USERS, values, COLUMN_USERNAME ?, new String[]{username}); db.close(); return rows; } public int deleteUser(String username) { SQLiteDatabase db this.getWritableDatabase(); int rows db.delete(TABLE_USERS, COLUMN_USERNAME ?, new String[]{username}); db.close(); return rows; }注意ContentValues是 Android 提供的安全参数容器自动处理字符串转义杜绝 SQL 注入。cursor.getColumnIndexOrThrow()比getColumnIndex()更健壮——当字段名拼错时直接抛异常而非返回 -1 导致崩溃。所有Cursor和SQLiteDatabase实例必须显式close()尤其在Activity的onDestroy()中未释放时App 可能因SQLiteFullException崩溃。3. 登录与注册界面实现Activity 交互逻辑与输入校验3.1 布局文件 activity_login.xml 的关键约束使用ConstraintLayout替代LinearLayout可减少嵌套层级提升渲染性能。重点控制输入框行为com.google.android.material.textfield.TextInputLayout android:idid/til_username android:layout_width0dp android:layout_heightwrap_content app:layout_constraintTop_toTopOfparent app:layout_constraintStart_toStartOfparent app:layout_constraintEnd_toEndOfparent app:hintTextAppearancestyle/TextAppearance.MaterialComponents.Caption app:errorEnabledtrue com.google.android.material.textfield.TextInputEditText android:idid/et_username android:layout_widthmatch_parent android:layout_heightwrap_content android:hintstring/hint_username android:inputTypetextPersonName android:maxLength20 / /com.google.android.material.textfield.TextInputLayout提示android:maxLength20限制用户名长度避免 SQLiteTEXT字段溢出android:inputTypetextPersonName触发软键盘姓名模式提升用户体验TextInputLayout的app:errorEnabledtrue支持动态显示错误提示如“用户名已存在”无需自定义 Toast。3.2 LoginActivity 中的登录流程与异步处理public class LoginActivity extends AppCompatActivity { private AccountDBHelper dbHelper; Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_login); dbHelper new AccountDBHelper(this); findViewById(R.id.btn_login).setOnClickListener(v - { String username ((TextInputEditText) findViewById(R.id.et_username)).getText().toString().trim(); String password ((TextInputEditText) findViewById(R.id.et_password)).getText().toString(); if (username.isEmpty() || password.isEmpty()) { showError(用户名和密码不能为空); return; } // 切换到后台线程避免阻塞 UI new AsyncTaskVoid, Void, User() { Override protected User doInBackground(Void... voids) { return dbHelper.getUserByUsername(username); } Override protected void onPostExecute(User user) { if (user null) { showError(用户名不存在); } else if (!user.getPassword().equals(password)) { showError(密码错误); } else { // 登录成功跳转主界面 Intent intent new Intent(LoginActivity.this, MainActivity.class); intent.putExtra(user_id, user.getId()); startActivity(intent); finish(); } } }.execute(); }); } private void showError(String message) { TextInputLayout til findViewById(R.id.til_username); til.setError(message); til.requestFocus(); } }注意AsyncTask在 Android 11 已废弃但对本场景仍适用无复杂生命周期依赖。若目标 SDK ≥ 33应改用ExecutorServiceHandler(Looper.getMainLooper())组合。doInBackground()中调用dbHelper.getUserByUsername()是唯一耗时操作必须剥离 UI 线程onPostExecute()在主线程执行可安全操作 View。3.3 RegisterActivity 的唯一性校验与密码强度检查// 在 RegisterActivity 中添加注册按钮点击逻辑 findViewById(R.id.btn_register).setOnClickListener(v - { String username ((TextInputEditText) findViewById(R.id.et_username)).getText().toString().trim(); String password ((TextInputEditText) findViewById(R.id.et_password)).getText().toString(); String email ((TextInputEditText) findViewById(R.id.et_email)).getText().toString().trim(); if (!isValidUsername(username)) { showError(用户名只能包含字母、数字和下划线长度 3-15 位); return; } if (!isValidPassword(password)) { showError(密码至少8位需含大小写字母和数字); return; } if (!android.util.Patterns.EMAIL_ADDRESS.matcher(email).matches()) { showError(邮箱格式不正确); return; } // 检查用户名是否已存在同步查询因注册频次低 User existing dbHelper.getUserByUsername(username); if (existing ! null) { showError(用户名已被占用); return; } long result dbHelper.insertUser(username, password, email); if (result ! -1) { Toast.makeText(this, 注册成功, Toast.LENGTH_SHORT).show(); finish(); } else { showError(注册失败请重试); } }); private boolean isValidUsername(String s) { return s.length() 3 s.length() 15 s.matches([a-zA-Z0-9_]); } private boolean isValidPassword(String s) { return s.length() 8 s.matches(.*[a-z].*) s.matches(.*[A-Z].*) s.matches(.*\\d.*); }提示Patterns.EMAIL_ADDRESS是 Android 内置正则比手写^[a-zA-Z0-9._%-][a-zA-Z0-9.-]\\.[a-zA-Z]{2,}$更可靠密码强度检查用多个matches()分散验证避免复杂正则难以调试insertUser()返回-1表示插入失败如 UNIQUE 约束冲突这是 SQLite 的标准返回值。4. 主界面 RecyclerView 与数据操作实现完整的增删改查交互链4.1 创建 UserAdapter 适配器并绑定点击事件public class UserAdapter extends RecyclerView.AdapterUserAdapter.ViewHolder { private ListUser users; private AccountDBHelper dbHelper; private OnUserActionListener listener; public interface OnUserActionListener { void onEdit(User user); void onDelete(User user); } public UserAdapter(ListUser users, AccountDBHelper dbHelper, OnUserActionListener listener) { this.users users; this.dbHelper dbHelper; this.listener listener; } NonNull Override public ViewHolder onCreateViewHolder(NonNull ViewGroup parent, int viewType) { View view LayoutInflater.from(parent.getContext()) .inflate(R.layout.item_user, parent, false); return new ViewHolder(view); } Override public void onBindViewHolder(NonNull ViewHolder holder, int position) { User user users.get(position); holder.tvUsername.setText(user.getUsername()); holder.tvEmail.setText(user.getEmail()); holder.tvCreatedAt.setText(new Date(user.getCreatedAt() * 1000L).toLocaleString()); holder.btnEdit.setOnClickListener(v - listener.onEdit(user)); holder.btnDelete.setOnClickListener(v - listener.onDelete(user)); } Override public int getItemCount() { return users.size(); } static class ViewHolder extends RecyclerView.ViewHolder { TextView tvUsername, tvEmail, tvCreatedAt; Button btnEdit, btnDelete; ViewHolder(View itemView) { super(itemView); tvUsername itemView.findViewById(R.id.tv_username); tvEmail itemView.findViewById(R.id.tv_email); tvCreatedAt itemView.findViewById(R.id.tv_created_at); btnEdit itemView.findViewById(R.id.btn_edit); btnDelete itemView.findViewById(R.id.btn_delete); } } }注意item_user.xml中每个TextView必须设置android:maxLines1防止文本撑开布局btnEdit和btnDelete使用android:background?attr/selectableItemBackgroundBorderless实现水波纹效果new Date(...).toLocaleString()仅用于演示生产环境应使用SimpleDateFormat或java.time格式化。4.2 MainActivity 中加载数据与响应操作public class MainActivity extends AppCompatActivity implements UserAdapter.OnUserActionListener { private RecyclerView recyclerView; private UserAdapter adapter; private ListUser userList; private AccountDBHelper dbHelper; Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); dbHelper new AccountDBHelper(this); userList new ArrayList(); adapter new UserAdapter(userList, dbHelper, this); recyclerView findViewById(R.id.rv_users); recyclerView.setLayoutManager(new LinearLayoutManager(this)); recyclerView.setAdapter(adapter); loadUsers(); // 初始化加载 // 添加浮动按钮触发新增 findViewById(R.id.fab_add).setOnClickListener(v - { Intent intent new Intent(MainActivity.this, AddUserActivity.class); startActivityForResult(intent, REQUEST_CODE_ADD_USER); }); } private void loadUsers() { userList.clear(); Cursor cursor dbHelper.getReadableDatabase().rawQuery( SELECT * FROM AccountDBHelper.TABLE_USERS ORDER BY AccountDBHelper.COLUMN_CREATED_AT DESC, null); while (cursor.moveToNext()) { userList.add(new User( cursor.getInt(cursor.getColumnIndexOrThrow(AccountDBHelper.COLUMN_ID)), cursor.getString(cursor.getColumnIndexOrThrow(AccountDBHelper.COLUMN_USERNAME)), cursor.getString(cursor.getColumnIndexOrThrow(AccountDBHelper.COLUMN_PASSWORD)), cursor.getString(cursor.getColumnIndexOrThrow(AccountDBHelper.COLUMN_EMAIL)), cursor.getLong(cursor.getColumnIndexOrThrow(AccountDBHelper.COLUMN_CREATED_AT)) )); } cursor.close(); adapter.notifyDataSetChanged(); } Override protected void onActivityResult(int requestCode, int resultCode, Nullable Intent data) { super.onActivityResult(requestCode, resultCode, data); if (requestCode REQUEST_CODE_ADD_USER resultCode RESULT_OK) { loadUsers(); // 新增后刷新列表 } } Override public void onEdit(User user) { Intent intent new Intent(this, EditUserActivity.class); intent.putExtra(user_id, user.getId()); startActivityForResult(intent, REQUEST_CODE_EDIT_USER); } Override public void onDelete(User user) { new AlertDialog.Builder(this) .setTitle(确认删除) .setMessage(确定要删除用户 user.getUsername() 吗) .setPositiveButton(删除, (dialog, which) - { int rows dbHelper.deleteUser(user.getUsername()); if (rows 0) { Toast.makeText(this, 删除成功, Toast.LENGTH_SHORT).show(); loadUsers(); } }) .setNegativeButton(取消, null) .show(); } }提示loadUsers()中使用rawQuery()直接执行 SQL因其需按时间倒序排列而query()方法不支持ORDER BY子句的灵活组合startActivityForResult()在 Android 11 已弃用但本例中AddUserActivity和EditUserActivity为简单页面无复杂返回数据故仍适用AlertDialog的setPositiveButton回调中执行dbHelper.deleteUser()确保删除操作原子性。4.3 AddUserActivity 与 EditUserActivity 的数据提交逻辑// AddUserActivity.java public class AddUserActivity extends AppCompatActivity { private AccountDBHelper dbHelper; Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_add_user); dbHelper new AccountDBHelper(this); findViewById(R.id.btn_save).setOnClickListener(v - { String username ((TextInputEditText) findViewById(R.id.et_username)).getText().toString().trim(); String password ((TextInputEditText) findViewById(R.id.et_password)).getText().toString(); String email ((TextInputEditText) findViewById(R.id.et_email)).getText().toString().trim(); if (username.isEmpty() || password.isEmpty()) { Toast.makeText(this, 用户名和密码不能为空, Toast.LENGTH_SHORT).show(); return; } long result dbHelper.insertUser(username, password, email); if (result ! -1) { setResult(RESULT_OK); finish(); } else { Toast.makeText(this, 保存失败, Toast.LENGTH_SHORT).show(); } }); } }// EditUserActivity.java public class EditUserActivity extends AppCompatActivity { private AccountDBHelper dbHelper; private int userId; Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_edit_user); dbHelper new AccountDBHelper(this); userId getIntent().getIntExtra(user_id, -1); if (userId -1) { Toast.makeText(this, 无效用户ID, Toast.LENGTH_SHORT).show(); finish(); return; } User user loadUserById(userId); if (user null) { Toast.makeText(this, 用户不存在, Toast.LENGTH_SHORT).show(); finish(); return; } ((TextInputEditText) findViewById(R.id.et_username)).setText(user.getUsername()); ((TextInputEditText) findViewById(R.id.et_email)).setText(user.getEmail()); findViewById(R.id.btn_save).setOnClickListener(v - { String username ((TextInputEditText) findViewById(R.id.et_username)).getText().toString().trim(); String email ((TextInputEditText) findViewById(R.id.et_email)).getText().toString().trim(); int rows dbHelper.updateUserEmail(username, email); if (rows 0) { setResult(RESULT_OK); finish(); } else { Toast.makeText(this, 更新失败, Toast.LENGTH_SHORT).show(); } }); } private User loadUserById(int id) { Cursor cursor dbHelper.getReadableDatabase().query( AccountDBHelper.TABLE_USERS, null, AccountDBHelper.COLUMN_ID ?, new String[]{String.valueOf(id)}, null, null, null ); User user null; if (cursor.moveToFirst()) { user new User( cursor.getInt(cursor.getColumnIndexOrThrow(AccountDBHelper.COLUMN_ID)), cursor.getString(cursor.getColumnIndexOrThrow(AccountDBHelper.COLUMN_USERNAME)), cursor.getString(cursor.getColumnIndexOrThrow(AccountDBHelper.COLUMN_PASSWORD)), cursor.getString(cursor.getColumnIndexOrThrow(AccountDBHelper.COLUMN_EMAIL)), cursor.getLong(cursor.getColumnIndexOrThrow(AccountDBHelper.COLUMN_CREATED_AT)) ); } cursor.close(); return user; } }注意EditUserActivity中未提供修改密码功能因密码字段通常不可逆需哈希存储此处仅演示邮箱更新loadUserById()使用query()方法其参数顺序为table, columns, selection, selectionArgs, groupBy, having, orderBy务必按此顺序传参否则selectionArgs会被误认为groupBysetResult(RESULT_OK)是startActivityForResult()的约定返回信号触发MainActivity.onActivityResult()。5. SQLite 性能优化与常见坑点排查从 Cursor 泄漏到事务一致性5.1 使用事务批量插入提升 10 倍写入速度当需要一次性插入 100 条用户记录时逐条insertUser()会触发 100 次磁盘 I/O。正确做法是包裹在事务中public void batchInsertUsers(ListUser users) { SQLiteDatabase db this.getWritableDatabase(); db.beginTransaction(); try { for (User user : users) { ContentValues values new ContentValues(); values.put(COLUMN_USERNAME, user.getUsername()); values.put(COLUMN_PASSWORD, user.getPassword()); values.put(COLUMN_EMAIL, user.getEmail()); db.insert(TABLE_USERS, null, values); } db.setTransactionSuccessful(); } finally { db.endTransaction(); db.close(); } }提示beginTransaction()后必须配对endTransaction()否则数据库将处于锁定状态setTransactionSuccessful()表示事务成功若未调用则endTransaction()会回滚所有操作批量插入时db.insert()返回值无需检查因事务内单条失败不影响整体除非手动抛异常。5.2 Cursor 清单式检查表避免内存泄漏的 5 个关键节点场景错误写法正确写法原因query()后未关闭Cursor c db.query(...); c.moveToFirst();try (Cursor c db.query(...)) { ... }Cursor实现AutoCloseabletry-with-resources 自动关闭多层嵌套 Cursorwhile(c1.moveToNext()) { Cursor c2 db.query(...); }外层c1关闭前内层c2必须先关闭多个 Cursor 共享同一数据库连接未关闭会耗尽句柄Adapter 中复用 Cursorcursor dbHelper.getCursor(); adapter.swapCursor(cursor);adapter.changeCursor(cursor);并在onDetachedFromRecyclerView()中关闭swapCursor()不关闭旧 Cursor需手动管理异步线程中使用 Cursornew Thread(() - { Cursor c db.query(...); }).start();改用LoaderManager或CursorLoaderCursor 绑定主线程 Looper子线程访问会崩溃rawQuery()未指定 projectiondb.rawQuery(SELECT * FROM users, null)db.rawQuery(SELECT id,username,email FROM users, null)SELECT *在表结构变更时易引发IllegalArgumentException5.3 验证 SQLite 数据库文件是否正常生成Android Studio 提供 Database Inspector 工具View → Tool Windows → Database Inspector但需满足设备为 API 26 的模拟器或真机App 进程正在运行非停止状态build.gradle中启用debuggable true若无法连接可手动导出数据库文件验证# 连接设备后执行 adb shell run-as com.yourpackage cat /data/data/com.yourpackage/databases/account_manager.db account.db # 在 PC 上用 DB Browser for SQLite 打开 account.db 查看表结构与数据注意run-as命令仅对 debug 包有效release 包因签名不同无法访问/data/data/目录导出的.db文件可用开源工具 DB Browser for SQLite 直接打开查看users表是否包含预期记录验证INSERT逻辑是否生效。5.4 解决 “no such table: users” 错误的三步定位法该错误 90% 源于onCreate()未被调用原因有三数据库版本未重置首次安装后修改DATABASE_VERSION但未卸载 ApponCreate()不会再次执行。解决方案adb uninstall com.yourpackage彻底清除数据。Helper 实例未正确初始化在Application类中全局初始化AccountDBHelper避免Activity中多次new导致版本混乱。表名拼写不一致CREATE TABLE users与db.query(Users, ...)中大小写不匹配Linux 文件系统区分大小写。解决方案统一使用小写表名或在query()中用TABLE_USERS常量。验证方法在onCreate()中添加Log.d(DB, Creating table...);运行 App 后Logcat过滤DB标签确认日志是否输出。本文还有配套的精品资源点击获取
