java - 使用 SQLite 在 Android Studio 中创建 2 个表失败
问题描述
我创建了 2 个表,但仍然找不到其他表。该表不会创建,但我已使用 execSQL 在数据库助手中创建
2019-12-28 23:42:18.971 30262-30262/com.example.ppdb E/SQLiteLog: (1) no such table: peserta
2019-12-28 23:42:18.972 30262-30262/com.example.ppdb E/SQLiteDatabase: Error inserting tgl_lahir= agama= jk= tmp_lahir= alamat= nama=Anto
android.database.sqlite.SQLiteException: no such table: peserta (code 1): , while compiling: INSERT INTO peserta(tgl_lahir,agama,jk,tmp_lahir,alamat,nama) VALUES (?,?,?,?,?,?)
这是我使用 TABLE_PESERTA = 'peserta' 的 DatabaseHelper
public class DatabasePesertaHelper extends SQLiteOpenHelper implements Database {
// Database Version
private static final int DATABASE_VERSION = 1;
// Database Name
private static final String DATABASE_NAME = DatabaseContents.DATABASE.toString();
public DatabasePesertaHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
// Creating Tables
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL("CREATE TABLE peserta" + "("
+ "_id INTEGER PRIMARY KEY,"
+ "nama TEXT(100),"
+ "jenis_kelamin TEXT(1),"
+ "tempat_lahir TEXT(20),"
+ "tgl_lahir TEXT(12),"
+ "alamat TEXT(30),"
+ "agama TEXT(30),"
+ "id_ortu INTEGER,"
+ "FOREIGN KEY (id_ortu) REFERENCES " + DatabaseContents.TABLE_USERS + "(_id)"
+ ");");
Log.d("CREATE DATABASE", "Create " + DatabaseContents.TABLE_PESERTA +" Successfully.");
}
// Upgrading database
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// Drop older table if existed
db.execSQL("DROP TABLE IF EXISTS " + DatabaseContents.TABLE_PESERTA);
// Create tables again
onCreate(db);
}
任何人都可以帮助我吗?谢谢!
解决方案
解释
您似乎正在尝试使用多个数据库助手,每个表一个用于同一数据库。只有一个 Helper,第一个,在数据库的生命周期中遇到,将调用onCreate方法,因此您可能遇到的问题。
典型的解决方法是使用单个数据库助手(它不是表助手)。
假设您有一个用于Users表的数据库助手:-
public class DatabaseUserHelper extends SQLiteOpenHelper {
private static final int DATABASE_VERSION = 1;
private static final String DATABASE_NAME = DatabaseContents.DATABASE.toString();
public DatabaseUserHelper(Context context) {
super(context, DATABASE_NAME, null,DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
Log.d("CREATEUSER","Creating User Table");
db.execSQL("CREATE TABLE " + DatabaseContents.TABLE_USERS +
"(" +
"_id INTEGER PRIMARY KEY," +
" username text" +
")");
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
}
并且您拥有问题中包含的 DatabasePesertaHelper,然后使用:-
public class MainActivity extends AppCompatActivity {
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
DatabaseUserHelper userHelper = new DatabaseUserHelper(this);
logTables(userHelper.getWritableDatabase());
DatabasePesertaHelper pesertaHelper = new DatabasePesertaHelper(this);
logTables(pesertaHelper.getWritableDatabase());
}
private void logTables(SQLiteDatabase db) {
Cursor c = db.query("sqlite_master",null,null,null,null,null,null);
DatabaseUtils.dumpCursor(c);
c.close();
}
}
并且应用程序从新运行,然后日志包含:-
12-29 09:34:28.582 2511-2511/? D/CREATEUSER: Creating User Table
12-29 09:34:28.587 2511-2511/? I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@1351978
12-29 09:34:28.587 2511-2511/? I/System.out: 0 {
12-29 09:34:28.587 2511-2511/? I/System.out: type=table
12-29 09:34:28.587 2511-2511/? I/System.out: name=android_metadata
12-29 09:34:28.587 2511-2511/? I/System.out: tbl_name=android_metadata
12-29 09:34:28.587 2511-2511/? I/System.out: rootpage=3
12-29 09:34:28.587 2511-2511/? I/System.out: sql=CREATE TABLE android_metadata (locale TEXT)
12-29 09:34:28.587 2511-2511/? I/System.out: }
12-29 09:34:28.587 2511-2511/? I/System.out: 1 {
12-29 09:34:28.587 2511-2511/? I/System.out: type=table
12-29 09:34:28.588 2511-2511/? I/System.out: name=users
12-29 09:34:28.588 2511-2511/? I/System.out: tbl_name=users
12-29 09:34:28.588 2511-2511/? I/System.out: rootpage=4
12-29 09:34:28.588 2511-2511/? I/System.out: sql=CREATE TABLE users(_id INTEGER PRIMARY KEY, username text)
12-29 09:34:28.588 2511-2511/? I/System.out: }
12-29 09:34:28.588 2511-2511/? I/System.out: <<<<<
12-29 09:34:28.590 2511-2511/? I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@fdb49b7
12-29 09:34:28.590 2511-2511/? I/System.out: 0 {
12-29 09:34:28.590 2511-2511/? I/System.out: type=table
12-29 09:34:28.590 2511-2511/? I/System.out: name=android_metadata
12-29 09:34:28.590 2511-2511/? I/System.out: tbl_name=android_metadata
12-29 09:34:28.590 2511-2511/? I/System.out: rootpage=3
12-29 09:34:28.590 2511-2511/? I/System.out: sql=CREATE TABLE android_metadata (locale TEXT)
12-29 09:34:28.590 2511-2511/? I/System.out: }
12-29 09:34:28.590 2511-2511/? I/System.out: 1 {
12-29 09:34:28.590 2511-2511/? I/System.out: type=table
12-29 09:34:28.590 2511-2511/? I/System.out: name=users
12-29 09:34:28.590 2511-2511/? I/System.out: tbl_name=users
12-29 09:34:28.590 2511-2511/? I/System.out: rootpage=4
12-29 09:34:28.590 2511-2511/? I/System.out: sql=CREATE TABLE users(_id INTEGER PRIMARY KEY, username text)
12-29 09:34:28.590 2511-2511/? I/System.out: }
12-29 09:34:28.590 2511-2511/? I/System.out: <<<<<
即已创建用户表,尽管 DatabasePesertaHelper 没有问题,但未创建 Peserta 表(如果交换了实例化 usersHelper 和 pesertaHelper 的顺序并且 pesertaHelper 是第一个,则只会创建 Peserta 表,然后仅当数据库不存在)。
建议修复
典型的解决方法是将两个表合并到一个助手中,然后卸载应用程序并重新运行。
- 请注意,这将删除所有现有数据,这在开发应用程序时通常是可以接受的。
因此,如上所示的 DatabaseUserHelper 可能变为:-
public class DatabaseUserHelper extends SQLiteOpenHelper {
private static final int DATABASE_VERSION = 1;
private static final String DATABASE_NAME = DatabaseContents.DATABASE.toString();
public DatabaseUserHelper(Context context) {
super(context, DATABASE_NAME, null,DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
Log.d("CREATEUSER","Creating User Table");
db.execSQL("CREATE TABLE " + DatabaseContents.TABLE_USERS +
"(" +
"_id INTEGER PRIMARY KEY," +
" username text" +
")");
Log.d("CREATEPESERTA","Creating Perserta Table");
db.execSQL("CREATE TABLE peserta" + "("
+ "_id INTEGER PRIMARY KEY,"
+ "nama TEXT(100),"
+ "jenis_kelamin TEXT(1),"
+ "tempat_lahir TEXT(20),"
+ "tgl_lahir TEXT(12),"
+ "alamat TEXT(30),"
+ "agama TEXT(30),"
+ "id_ortu INTEGER,"
+ "FOREIGN KEY (id_ortu) REFERENCES " + DatabaseContents.TABLE_USERS + "(_id)"
+ ");");
Log.d("CREATE DATABASE", "Create " + DatabaseContents.TABLE_PESERTA + " Successfully.");
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
}
删除应用程序的数据(或卸载应用程序)并重新运行上面的活动代码后:-
12-29 10:01:37.265 D/CREATEUSER: Creating User Table
12-29 10:01:37.265 D/CREATEPESERTA: Creating Perserta Table
12-29 10:01:37.265 D/CREATE DATABASE: Create peserta Successfully.
12-29 10:01:37.270 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@1351978
12-29 10:01:37.270 I/System.out: 0 {
12-29 10:01:37.270 I/System.out: type=table
12-29 10:01:37.270 I/System.out: name=android_metadata
12-29 10:01:37.270 I/System.out: tbl_name=android_metadata
12-29 10:01:37.270 I/System.out: rootpage=3
12-29 10:01:37.270 I/System.out: sql=CREATE TABLE android_metadata (locale TEXT)
12-29 10:01:37.270 I/System.out: }
12-29 10:01:37.270 I/System.out: 1 {
12-29 10:01:37.270 I/System.out: type=table
12-29 10:01:37.270 I/System.out: name=users
12-29 10:01:37.270 I/System.out: tbl_name=users
12-29 10:01:37.270 I/System.out: rootpage=4
12-29 10:01:37.270 I/System.out: sql=CREATE TABLE users(_id INTEGER PRIMARY KEY, username text)
12-29 10:01:37.270 I/System.out: }
12-29 10:01:37.270 I/System.out: 2 {
12-29 10:01:37.270 I/System.out: type=table
12-29 10:01:37.270 I/System.out: name=peserta
12-29 10:01:37.270 I/System.out: tbl_name=peserta
12-29 10:01:37.270 I/System.out: rootpage=5
12-29 10:01:37.270 I/System.out: sql=CREATE TABLE peserta(_id INTEGER PRIMARY KEY,nama TEXT(100),jenis_kelamin TEXT(1),tempat_lahir TEXT(20),tgl_lahir TEXT(12),alamat TEXT(30),agama TEXT(30),id_ortu INTEGER,FOREIGN KEY (id_ortu) REFERENCES users(_id))
12-29 10:01:37.270 I/System.out: }
12-29 10:01:37.270 I/System.out: <<<<<
12-29 10:01:37.273 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@fdb49b7
12-29 10:01:37.273 I/System.out: 0 {
12-29 10:01:37.273 I/System.out: type=table
12-29 10:01:37.273 I/System.out: name=android_metadata
12-29 10:01:37.273 I/System.out: tbl_name=android_metadata
12-29 10:01:37.273 I/System.out: rootpage=3
12-29 10:01:37.273 I/System.out: sql=CREATE TABLE android_metadata (locale TEXT)
12-29 10:01:37.273 I/System.out: }
12-29 10:01:37.273 I/System.out: 1 {
12-29 10:01:37.273 I/System.out: type=table
12-29 10:01:37.273 I/System.out: name=users
12-29 10:01:37.273 I/System.out: tbl_name=users
12-29 10:01:37.273 I/System.out: rootpage=4
12-29 10:01:37.273 I/System.out: sql=CREATE TABLE users(_id INTEGER PRIMARY KEY, username text)
12-29 10:01:37.273 I/System.out: }
12-29 10:01:37.273 I/System.out: 2 {
12-29 10:01:37.274 I/System.out: type=table
12-29 10:01:37.274 I/System.out: name=peserta
12-29 10:01:37.274 I/System.out: tbl_name=peserta
12-29 10:01:37.274 I/System.out: rootpage=5
12-29 10:01:37.274 I/System.out: sql=CREATE TABLE peserta(_id INTEGER PRIMARY KEY,nama TEXT(100),jenis_kelamin TEXT(1),tempat_lahir TEXT(20),tgl_lahir TEXT(12),alamat TEXT(30),agama TEXT(30),id_ortu INTEGER,FOREIGN KEY (id_ortu) REFERENCES users(_id))
12-29 10:01:37.274 I/System.out: }
12-29 10:01:37.274 I/System.out: <<<<<
- 请注意,不需要 DatabasePesertahelper,因为 DatabaseUserHelper 提供了对这两个表的访问。
推荐阅读
- javascript - 如何使用引导程序使表格具有响应性和吸引力?
- azure - 我无法在 azure 上部署我的 nextjs 应用程序
- alias - 如何使用别名来简化 CUDA_VISIBLE_DEVICES
- javascript - 如何在java中构造一个类似json的body对象?
- laravel - 从控制器渲染图表以查看
- php - CodeIgniter 4 重定向功能不起作用
- python - Django manage.py:错误:无法识别的参数:runserver
- python - 使用 lmfit 和差分进化方法设置迭代限制
- javascript - 如何根据 Redux 上按下的按钮来实现从 API 接收不同数据的按钮?
- javascript - 带有 JQuery 的 IE6 Ajax