Java 面试必问：MySQL 索引优化、执行计划、MVCC、锁机制与主从复制全解析
2026/9/11 18:14:32
简介：这是一份面向Android初学者与移动开发入门者的完整SQLite本地数据管理实战项目，聚焦寄存系统核心功能实现，涵盖用户登录注册、本地数据库增删改查（CRUD）全流程。资源基于Android Studio官方IDE开发，深度整合SQLiteOpenHelper封装、SQL语句执行、Database Inspector调试等关键技能，适用于课程设计、毕业设计或小型App原型开发场景。压缩包共1287个文件，主体为515个flat资源文件（含布局与资源映射）、205个dex与203个class字节码（体现完整编译结构）、120个json配置及76个xml界面/配置文件，辅以42个Java源码文件和1个可直接安装的app-debug.apk，整体9.1MB，结构规范、模块清晰。已有1764人学习下载，读者可直接运行调试、对照源码理解SQLite事务处理与用户凭证加密存储逻辑，并复用登录验证、数据表建模及DAO层封装等成熟代码结构。
1. 这不是“带登录的记账App”，而是一个可落地的本地数据闭环系统
你打开一个 Android Studio 项目，看到app-debug.apk、gradlew.bat和一串 Base64 字符（如2Q9xUd_xJfBTGWD7QMvAlb4qIEQ=），第一反应可能是“又一个学生课设”。但真正拆进去会发现：它用纯 SQLite 实现了从用户凭证校验到业务数据持久化的完整链路——没有 Retrofit、没有 Firebase、不依赖任何远程服务。所有逻辑跑在SQLiteOpenHelper封装的单例数据库里，注册时密码经MessageDigest.getInstance("SHA-256")哈希后存入users表；登录时比对哈希值而非明文；增删改查操作全部通过ContentValues+SQLiteDatabase原生 API 完成，连 Cursor 的getColumnIndexOrThrow()都做了空指针防护。这种架构适合离线场景强、数据敏感度高、或需快速验证 MVP 的项目，比如内部工具、考场终端、医疗巡检端。如果你正被 Jetpack DataStore 的迁移成本卡住，或想搞清onUpgrade()中ALTER TABLE ADD COLUMN和CREATE TEMP TABLE重建的取舍边界，这个项目就是现成的调试沙盒。
2. SQLiteOpenHelper 的实战封装与表结构设计
2.1 为什么不用 Room？先看底层契约如何定义
这个项目没引入任何 ORM 框架，核心在于DatabaseHelper.java继承自SQLiteOpenHelper。它的构造函数强制传入Context和数据库名（"deposit.db"），版本号固定为1——注意，这不是偷懒，而是明确拒绝自动迁移：当业务需要新增字段时，开发者必须手动编写onUpgrade()逻辑，避免 Room 自动生成的ALTER TABLE在低版本 Android 上因语法不兼容导致崩溃（例如 Android 4.4 的 SQLite 不支持ALTER TABLE ... RENAME COLUMN）。onCreate()中建表语句直接硬编码 SQL，而非用 Entity 注解生成：
@Override public void onCreate(SQLiteDatabase db) { // 用户表：含 id, username, password_hash, created_at db.execSQL("CREATE TABLE users (" + "_id INTEGER PRIMARY KEY AUTOINCREMENT," + "username TEXT UNIQUE NOT NULL," + "password_hash TEXT NOT NULL," + "created_at INTEGER DEFAULT (strftime('%s','now'))" + ")"); // 存储记录表：关联用户，含金额、类型、备注、时间戳 db.execSQL("CREATE TABLE deposits (" + "_id INTEGER PRIMARY KEY AUTOINCREMENT," + "user_id INTEGER NOT NULL," + "amount REAL NOT NULL CHECK(amount > 0)," + "type TEXT CHECK(type IN ('in', 'out'))," + "note TEXT," + "timestamp INTEGER DEFAULT (strftime('%s','now'))," + "FOREIGN KEY(user_id) REFERENCES users(_id) ON DELETE CASCADE" + ")"); }提示：
ON DELETE CASCADE是关键设计。当删除某个用户时，其所有deposits记录自动清除，避免外键残留。但 Android SQLite 默认不启用外键约束（PRAGMA foreign_keys=OFF），因此必须在getWritableDatabase()后显式开启：
SQLiteDatabase db = getWritableDatabase(); db.execSQL("PRAGMA foreign_keys=ON"); // 必须加这一行！否则FOREIGN KEY仅作注释，毫无约束力。
2.2 登录注册的密码安全实现细节
注册流程中，密码未用BCrypt（因无第三方依赖），而是采用SHA-256+ 盐值（salt）哈希。盐值并非全局常量，而是为每个用户生成唯一随机字符串：
private String hashPassword(String plainPassword) { String salt = UUID.randomUUID().toString().replace("-", "").substring(0, 16); String salted = plainPassword + salt; try { MessageDigest md = MessageDigest.getInstance("SHA-256"); byte[] hash = md.digest(salted.getBytes(StandardCharsets.UTF_8)); StringBuilder hexString = new StringBuilder(); for (byte b : hash) { String hex = Integer.toHexString(0xff & b); if (hex.length() == 1) hexString.append('0'); hexString.append(hex); } return hexString.toString() + ":" + salt; // 拼接盐值，存入数据库 } catch (NoSuchAlgorithmException e) { throw new RuntimeException(e); } }登录验证时，先根据用户名查出password_hash字段（格式为hash:salt），再用相同逻辑重算哈希比对：
// 查询语句示例 String query = "SELECT _id, password_hash FROM users WHERE username = ?"; Cursor cursor = db.rawQuery(query, new String[]{username}); if (cursor.moveToFirst()) { String storedHashSalt = cursor.getString(1); // "a1b2c3...:xyz789" String[] parts = storedHashSalt.split(":"); String storedHash = parts[0]; String salt = parts[1]; String inputHash = hashPasswordWithSalt(plainPassword, salt); // 复用哈希逻辑 if (storedHash.equals(inputHash)) { // 登录成功 } }注意：
hashPasswordWithSalt()是提取出的复用方法，避免重复代码。盐值存储在数据库中虽增加泄露风险，但比无盐哈希（如纯 MD5）抗彩虹表攻击强得多。若需更高安全性，应迁移到PBKDF2WithHmacSHA256并设置迭代次数（如 10000 次），但本项目为轻量级设计，SHA-256 已满足基础需求。
2.3 表结构演进：从 v1 到 v2 的 onUpgrade 实践
假设上线后需为deposits表增加category字段（如 “工资”、“报销”、“借款”），不能简单执行ALTER TABLE deposits ADD COLUMN category TEXT——因为 Android 4.0~4.3 的 SQLite 版本不支持该语法。正确做法是创建新表、迁移数据、删除旧表：
@Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { if (oldVersion == 1 && newVersion == 2) { // 步骤1：重命名原表 db.execSQL("ALTER TABLE deposits RENAME TO deposits_old"); // 步骤2：创建新表（含 category 字段） db.execSQL("CREATE TABLE deposits (" + "_id INTEGER PRIMARY KEY AUTOINCREMENT," + "user_id INTEGER NOT NULL," + "amount REAL NOT NULL CHECK(amount > 0)," + "type TEXT CHECK(type IN ('in', 'out'))," + "note TEXT," + "category TEXT DEFAULT 'other'," + "timestamp INTEGER DEFAULT (strftime('%s','now'))," + "FOREIGN KEY(user_id) REFERENCES users(_id) ON DELETE CASCADE" + ")"); // 步骤3：迁移数据（category 设为默认值） db.execSQL("INSERT INTO deposits (user_id, amount, type, note, timestamp) " + "SELECT user_id, amount, type, note, timestamp FROM deposits_old"); // 步骤4：删除旧表 db.execSQL("DROP TABLE deposits_old"); } }此方案兼容所有 Android 版本，且DEFAULT 'other'确保旧数据不会因字段缺失而插入失败。
3. CRUD 操作的原子性封装与 UI 层调用链
3.1 增删改查的 DAO 层抽象
项目将数据库操作封装在DepositDAO.java和UserDAO.java中，避免 Activity 直接调用SQLiteDatabase。以添加存款记录为例，DepositDAO.insert()方法确保事务完整性：
public long insert(Deposit deposit) { SQLiteDatabase db = dbHelper.getWritableDatabase(); db.beginTransaction(); // 开启事务 try { ContentValues values = new ContentValues(); values.put("user_id", deposit.getUserId()); values.put("amount", deposit.getAmount()); values.put("type", deposit.getType()); values.put("note", deposit.getNote()); values.put("category", deposit.getCategory()); long result = db.insert("deposits", null, values); db.setTransactionSuccessful(); // 标记事务成功 return result; } finally { db.endTransaction(); // 无论成功失败都结束事务 } }关键点：
beginTransaction()+setTransactionSuccessful()+endTransaction()是 Android SQLite 事务的标准三件套。若省略setTransactionSuccessful()，endTransaction()会回滚所有操作。此处未捕获异常，因上层（如 ViewModel）需感知失败并提示用户。
3.2 查询操作的 Cursor 管理与内存安全
读取所有存款记录时，DepositDAO.getAllByUserId()返回List<Deposit>，而非直接传递Cursor。这是为避免 Cursor 泄漏——Activity 销毁时若 Cursor 未关闭，会导致SQLiteCursor对象长期驻留内存：
public List<Deposit> getAllByUserId(int userId) { List<Deposit> list = new ArrayList<>(); SQLiteDatabase db = dbHelper.getReadableDatabase(); String query = "SELECT * FROM deposits WHERE user_id = ? ORDER BY timestamp DESC"; Cursor cursor = db.rawQuery(query, new String[]{String.valueOf(userId)}); try { while (cursor.moveToNext()) { Deposit deposit = new Deposit(); deposit.setId(cursor.getInt(cursor.getColumnIndexOrThrow("_id"))); deposit.setUserId(cursor.getInt(cursor.getColumnIndexOrThrow("user_id"))); deposit.setAmount(cursor.getDouble(cursor.getColumnIndexOrThrow("amount"))); deposit.setType(cursor.getString(cursor.getColumnIndexOrThrow("type"))); deposit.setNote(cursor.getString(cursor.getColumnIndexOrThrow("note"))); deposit.setCategory(cursor.getString(cursor.getColumnIndexOrThrow("category"))); deposit.setTimestamp(cursor.getLong(cursor.getColumnIndexOrThrow("timestamp"))); list.add(deposit); } } finally { cursor.close(); // 必须显式关闭！ } return list; }getColumnIndexOrThrow()比getColumnIndex()更安全：当字段名拼写错误时抛出IllegalArgumentException，便于早期发现 SQL 与 Java 字段映射问题。
3.3 UI 层的异步调用与生命周期绑定
在MainActivity的登录按钮点击事件中，调用UserDAO.login()时使用AsyncTask（项目基于较老 Android SDK，未用 Coroutines）：
new LoginAsyncTask().execute(username, password); private class LoginAsyncTask extends AsyncTask<String, Void, Boolean> { @Override protected Boolean doInBackground(String... params) { String username = params[0]; String password = params[1]; UserDAO dao = new UserDAO(MainActivity.this); return dao.login(username, password); // 在后台线程执行 DB 查询 } @Override protected void onPostExecute(Boolean success) { if (success) { Intent intent = new Intent(MainActivity.this, HomeActivity.class); startActivity(intent); finish(); } else { Toast.makeText(MainActivity.this, "用户名或密码错误", Toast.LENGTH_SHORT).show(); } } }注意：
AsyncTask在 Android 11+ 已废弃，但本项目目标 API 为 28（Android 9），仍属有效方案。若升级至新版本，应替换为ExecutorService+Handler或ViewModel+LiveData组合。
4. Database Inspector 调试技巧与常见坑点排查
4.1 实时查看数据库内容的三步法
Android Studio 自带的 Database Inspector 是验证 SQLite 操作的利器，但需满足三个条件才能生效：
- 应用必须运行在 Android 10+ 设备或模拟器上（Database Inspector 依赖
adb shell sqlite3的新特性）； build.gradle中debug构建类型启用debuggable true（默认已开启）；- 在
DatabaseHelper的getWritableDatabase()调用前，添加StrictMode临时禁用磁盘读写检测（否则可能因主线程访问 DB 被拦截）：
if (BuildConfig.DEBUG) { StrictMode.ThreadPolicy policy = new StrictMode.ThreadPolicy.Builder().permitAll().build(); StrictMode.setThreadPolicy(policy); }启动应用后，在 Android Studio 右下角点击Database Inspector→ 选择正在运行的进程 → 展开deposit.db→ 可直接浏览users和deposits表，甚至双击单元格编辑数据（仅限 debug 版本）。
4.2 典型报错与定位路径
| 报错信息 | 根本原因 | 排查步骤 |
|---|---|---|
android.database.sqlite.SQLiteException: no such table: users | onCreate()未被调用，或数据库名拼写错误 | 检查DatabaseHelper构造函数传入的 dbName 是否为"deposit.db"；确认首次启动时getWritableDatabase()是否触发onCreate()（可在onCreate()中加Log.d验证） |
android.database.CursorIndexOutOfBoundsException: Index 0 requested, with a size of 0 | Cursor为空时未检查moveToFirst()返回值 | 所有Cursor操作前必须if (cursor.moveToFirst()) { ... }，不可直接cursor.getString(0) |
android.database.sqlite.SQLiteConstraintException: UNIQUE constraint failed: users.username | 注册时用户名已存在，但未做INSERT OR IGNORE或前置查询 | 在UserDAO.register()中，先SELECT COUNT(*) FROM users WHERE username = ?，返回 0 再插入 |
android.database.sqlite.SQLiteException: near "NOT": syntax error | SQL 语句中CHECK约束在低版本 SQLite 不被支持（Android 7.0 以下） | 移除CHECK(amount > 0)，改用 Java 层校验；或降级为amount REAL，业务逻辑保证正数 |
4.3 数据库文件导出与跨平台验证
有时需在 PC 上用 DB Browser for SQLite 分析数据。导出步骤如下：
- 在 Device File Explorer 中定位：
/data/data/com.yourpackage.name/databases/deposit.db - 右键 →Save As…保存到本地
- 用 DB Browser for SQLite 打开，执行
SELECT * FROM users;验证哈希值是否符合预期
提示：
/data/data/目录需 root 权限才能访问。若设备未 root，可通过adb backup导出（需应用android:allowBackup="true"）：adb backup -f deposit.ab com.yourpackage.name # 将 .ab 文件用 abe.jar 解包，提取 databases/deposit.db java -jar abe.jar unpack deposit.ab deposit.tar
5. 从 SQLite 迁移到 DataStore 的平滑过渡策略
5.1 识别迁移临界点：何时该换？
当出现以下任一情况时，应考虑弃用原生 SQLite，转向DataStore（Preference DataStore 用于键值对，Proto DataStore 用于类型化数据）：
- 多模块共享数据：当前
DatabaseHelper单例被UserDAO、DepositDAO等多个类持有，易引发并发修改冲突； - 需要响应式监听：UI 需实时响应数据库变更（如存款列表自动刷新），而
CursorLoader已废弃，手动ContentObserver维护成本高； - 数据加密需求升级：
DataStore支持EncryptedFile，比手动 AES 加密password_hash更可靠。
5.2 Proto DataStore 的增量迁移示例
以Deposit实体为例，先定义 Protocol Buffer schema（deposit.proto）：
syntax = "proto3"; option java_package = "com.example.deposit"; option java_multiple_files = true; message Deposit { int32 id = 1; int32 user_id = 2; double amount = 3; string type = 4; // "in" or "out" string note = 5; string category = 6; int64 timestamp = 7; }生成 Java 类后，在DepositRepository中封装读写：
public class DepositRepository { private final DataStore<Deposit> dataStore; public DepositRepository(Context context) { dataStore = new ProtoDataStoreBuilder<Deposit>(context, "deposits.pb") .setSerializer(new DepositSerializer()) .build(); } public Flowable<List<Deposit>> getAllByUserId(int userId) { return dataStore.data .map(data -> data.getDepositsList().stream() .filter(d -> d.getUserId() == userId) .collect(Collectors.toList())); } public Completable insert(Deposit deposit) { return dataStore.updateData(current -> { List<Deposit> list = new ArrayList<>(current.getDepositsList()); list.add(deposit.toBuilder().setId(list.size() + 1).build()); // 简单 ID 生成 return DepositList.newBuilder().addAllDeposits(list).build(); }); } }注意：
DepositList是自定义容器消息，因 DataStore 不支持直接存储 List，需包装一层。此方案牺牲了 SQL 的复杂查询能力（如GROUP BY、JOIN），但换来线程安全、协程友好、自动序列化三大优势。
5.3 混合使用策略：SQLite 与 DataStore 共存
不必全量迁移。可保留 SQLite 存储核心业务数据（如用户凭证、交易流水），用 DataStore 管理 UI 状态（如夜间模式开关、最近搜索词）：
// DataStore 仅存配置 val themePrefs = context.createDataStore("theme_prefs", produceMigrations = { it }) val isDarkMode = themePrefs.data.map { it[IS_DARK_MODE_KEY] ?: false }.asLiveData() // SQLite 仍管业务 val depositDao = DepositDAO(context) val deposits = depositDao.getAllByUserId(userId) // 返回 List<Deposit>这种分层让架构更清晰：SQLite 负责强一致性、关系型数据；DataStore 负责高频率读写、松散耦合的状态。
本文还有配套的精品资源，点击获取