java - 如何修复未找到/损坏的表？
问题描述
您好我目前正在尝试在 Android Studio 中制作一个简单的登录应用程序，其中用户数据是从 SQLite 表中读取的。到目前为止，我已经创建了我的表格并 从头到尾完全按照本教程进行操作。但是，我不断收到未找到表或表损坏错误。这是我的代码：
package com.example.b00694394.granshataxicompany2;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
private static final String DATABASE_NAME = "gransha.db";
private static final int DATABASE_VERSION = 1;
private final Context context;
SQLiteDatabase db;
private static final String DATABASE_PATH = "/data/data/com.example.b00694394.granshataxicompany2/databases/";
private final String DRIVERS_TABLE ="Drivers";
public Database(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
this.context = context;
createDb();
}
@Override
public void onCreate(SQLiteDatabase sqLiteDatabase) {
}
@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
}
public void createDb() {
boolean dbExist = checkDbExist();
if (!dbExist){
this.getReadableDatabase();
copyDatabase();
}
}
private boolean checkDbExist() {
SQLiteDatabase sqLiteDatabase = null;
try {
String path = DATABASE_PATH + DATABASE_NAME;
sqLiteDatabase = SQLiteDatabase.openDatabase(path, null, SQLiteDatabase.OPEN_READONLY);
} catch (Exception ex){
}
if (sqLiteDatabase != null) {
sqLiteDatabase.close();
return true;
}
return false;
}
private void copyDatabase() {
try {
InputStream inputStream = context.getAssets().open(DATABASE_NAME);
String outFileName = DATABASE_PATH + DATABASE_NAME;
OutputStream outputStream = new FileOutputStream(outFileName);
byte[] b = new byte[1024];
int lenght;
while ((lenght = inputStream.read(b)) > 0 ){
outputStream.write(b, 0, lenght);
}
outputStream.flush();
outputStream.close();
inputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
private SQLiteDatabase openDatabase() {
String path = DATABASE_PATH + DATABASE_NAME;
db = SQLiteDatabase.openDatabase(path, null, SQLiteDatabase.OPEN_READWRITE);
return db;
}
public void close(){
if(db !=null){
db.close();
}
}
public boolean checkUserExist (String dNum, String dPass){
String[] columns = {"Driver_Number"};
db = openDatabase();
String selection = "Driver_Number = " + dNum + " and Driver_Password = "+ dPass;
String[] selectionArgs = {dNum, dPass};
Cursor cursor = db.query(DRIVERS_TABLE, columns, selection, selectionArgs, null, null, null);
int count = cursor.getCount();
cursor.close();
close();
if (count > 0) {
return true;
} else {
return false;
}
}
}
这是运行时错误日志：
E/SQLiteLog: (26) file is encrypted or is not a database
E/DefaultDatabaseErrorHandler: Corruption reported by sqlite on database: /data/data/com.example.b00694394.granshataxicompany2/databases/gransha.db
E/DefaultDatabaseErrorHandler: deleting the database file: /data/data/com.example.b00694394.granshataxicompany2/databases/gransha.db
E/SQLiteLog: (14) cannot open file at line 35803 of [605907e73a]
(14) os_unix.c:35803: (2) open(/data/data/com.example.b00694394.granshataxicompany2/databases/gransha.db) -
E/SQLiteDatabase: Failed to open database '/data/data/com.example.b00694394.granshataxicompany2/databases/gransha.db'.
android.database.sqlite.SQLiteCantOpenDatabaseException: unknown error (code 14): Could not open database
at android.database.sqlite.SQLiteConnection.nativeOpen(Native Method)
at android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:210)
at android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:194)
at android.database.sqlite.SQLiteConnectionPool.openConnectionLocked(SQLiteConnectionPool.java:493)
at android.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:200)
at android.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:192)
at android.database.sqlite.SQLiteDatabase.openInner(SQLiteDatabase.java:864)
at android.database.sqlite.SQLiteDatabase.open(SQLiteDatabase.java:852)
at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:750)
at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:699)
at com.example.b00694394.granshataxicompany2.Database.openDatabase(Database.java:100)
at com.example.b00694394.granshataxicompany2.Database.checkUserExist(Database.java:113)
at com.example.b00694394.granshataxicompany2.loginPage$1.onClick(loginPage.java:40)
at android.view.View.performClick(View.java:6294)
at android.view.View$PerformClick.run(View.java:24770)
at android.os.Handler.handleCallback(Handler.java:790)
at android.os.Handler.dispatchMessage(Handler.java:99)
at android.os.Looper.loop(Looper.java:164)
at android.app.ActivityThread.main(ActivityThread.java:6494)
at java.lang.reflect.Method.invoke(Native Method)
at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:438)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:807)
D/AndroidRuntime: Shutting down VM
E/AndroidRuntime: FATAL EXCEPTION: main
Process: com.example.b00694394.granshataxicompany2, PID: 11343
android.database.sqlite.SQLiteCantOpenDatabaseException: unknown error (code 14): Could not open database
at android.database.sqlite.SQLiteConnection.nativeOpen(Native Method)
at android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:210)
at android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:194)
at android.database.sqlite.SQLiteConnectionPool.openConnectionLocked(SQLiteConnectionPool.java:493)
at android.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:200)
at android.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:192)
at android.database.sqlite.SQLiteDatabase.openInner(SQLiteDatabase.java:864)
at android.database.sqlite.SQLiteDatabase.open(SQLiteDatabase.java:852)
at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:750)
at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:699)
at com.example.b00694394.granshataxicompany2.Database.openDatabase(Database.java:100)
at com.example.b00694394.granshataxicompany2.Database.checkUserExist(Database.java:113)
at com.example.b00694394.granshataxicompany2.loginPage$1.onClick(loginPage.java:40)
at android.view.View.performClick(View.java:6294)
at android.view.View$PerformClick.run(View.java:24770)
at android.os.Handler.handleCallback(Handler.java:790)
at android.os.Handler.dispatchMessage(Handler.java:99)
at android.os.Looper.loop(Looper.java:164)
at android.app.ActivityThread.main(ActivityThread.java:6494)
at java.lang.reflect.Method.invoke(Native Method)
at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:438)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:807)
Application terminated.
任何帮助是极大的赞赏。谢谢！
解决方案
您的问题是文件gransha.db不是有效的数据库文件
E/SQLiteLog: (26) file is encrypted or is not a database
然后发生的事情是 SQLiteDatabase OpenOrCreate 方法（由 SQLiteOpenHelper 的子类调用）删除文件并创建一个空数据库，因此为什么在获取损坏的数据库后运行时您会找不到表。
修复方法是确保在将数据库文件复制到资产文件夹之前，使用 SQLite 工具（例如 Db Browser for SQLite、DBeaver、SQLite Manager ......）创建和保存数据库文件，然后删除应用程序的数据（删除空但有效的数据库）并重新运行应用程序。
实际上测试你的代码你有一个 checkUserExist 方法的问题，因为它失败是因为字符串没有被引号括起来，所以值 test 和 123 虽然是列名（失败， test 不是列名）。
所以而不是： -
String selection = "Driver_Number = " + dNum + " and Driver_Password = "+ dPass;
您需要使用：-
String selection = '"Driver_Number = "' + dNum + " and Driver_Password = '"+ dPass + "'";
但是，由于您已经正确设置selectionArgs，因此应使用以下内容：-
public boolean checkUserExist (String dNum, String dPass){
String[] columns = {"Driver_Number"};
db = openDatabase();
//String selection = "Driver_Number = " + dNum + " and Driver_Password = "+ dPass; <<<< commented out
String selection = "Driver_Number=? AND Driver_Password=?"; //<<<< ?'s replaced by values in selectionArgs
String[] selectionArgs = {dNum, dPass};
Cursor cursor = db.query(DRIVERS_TABLE, columns, selection, selectionArgs, null, null, null);
int count = cursor.getCount();
cursor.close();
close();
if (count > 0) {
return true;
} else {
return false;
}
}
这是推荐的方式（你不会让 SQL Injection Police 介入）。那是你的代码？在根据 args 中的值（代表您转义）按顺序替换的字符串中。即第一个？将被第一个 arg，第二个 ? 通过第二个 arg ....
我还建议不要使用硬编码的数据库路径：-
private static final String DATABASE_PATH = "/data/data/com.example.b00694394.granshataxicompany2/databases/";
而是将上面的内容替换为
private static DATABASE_PATH;
除了将构造函数更改为：-
public Database(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
this.context = context;
DATABASE_PATH = context.getDatabasePath(DATABASE_NAME).getParent()+File.separator;
createDb();
}
另一个建议是简化 checkDBExists，它还具有在无法打开数据库时不产生堆栈跟踪的好处。
因此，这里不是 checkDBExists，而是一种检查文件是否存在的替代方法：-
private boolean altCheckDBExist() {
File chkdb = new File(context.getDatabasePath(DATABASE_NAME).getPath());
return chkdb.exists();
}
因此，您可以通过以下方式使用它：-
public void createDb() {
/*
boolean dbExist = checkDbExist();
if (!dbExist){
this.getReadableDatabase();
copyDatabase();
}
*/
if (!altCheckDBExist()) {
this.getWritableDatabase();
copyDatabase();
}
}
- 请注意留下但已注释掉的旧代码。
使用 Navicat 测试
我下载了 Navicat 免费试用版（Navicat for SQLite3），创建数据库，添加表和数据，保存（关闭连接），将文件复制到 assets 文件夹，运行应用程序（删除应用程序的数据后），它工作.
所以Navicat可以用，更多的是怎么用的问题。
推荐阅读
- python-3.x - 列表索引如何超出范围？
- php - 使用“woocommerce_product_add_to_cart_text”过滤器挂钩时的问题
- google-translate - 谷歌翻译：UI 翻译但 API 失败
- typescript - TypeScript 枚举索引验证
- javascript - 如何使用 vanilla javascript 将视觉希伯来语转换为逻辑希伯来语？
- python - 重新连接后如何在jupyter中继续打印中间结果？
- reactjs - 无法调用导出默认值中定义的函数
- java - 将水印添加到自定义录像机
- c++ - glfw 程序在 WSL2 中崩溃
- windows - “docker start”不会使停止容器“up”