android - 只有一张表创建了 sql Lite Android
问题描述
我将使用 DataHelper.java 创建一些表，但只创建了一个表（位置表），这里是我的代码
public class DataHelper extends SQLiteOpenHelper {
private static final String LOGCAT = null;
private static final String DATABASE_NAME = "layerfarm-mobile.db";
private static final int DATABASE_VERSION = 1;
public static final String locationSQL = "CREATE TABLE IF NOT EXISTS location(id integer primary key, name text, address text, rid integer);";
public static final String eqq_qualitySQL = "CREATE TABLE IF NOT EXISTS eqq_quality(id integer primary key, name text);";
public DataHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
Log.d(LOGCAT,"Created");
// TODO Auto-generated constructor stub
}
@Override
public void onCreate(SQLiteDatabase db) {
try {
//Create table
db.execSQL(locationSQL);
db.execSQL(eqq_qualitySQL);
String InsertlocationSQL = "INSERT INTO location (id, name, address, rid) VALUES " +
"(1,'Location A','Blitar',1)," +
"(2,'Location B','Blitar',2)," +
"(3,'Location C','Blitar',3)," +
"(4,'Location D','Blitar',4);";
db.execSQL(InsertlocationSQL);
String InsertEggQualitySQL = "INSERT INTO egg_quality (id, name) VALUES " +
"(1,'Quality A'), " +
"(2,'Quality B'), " +
"(3,'Quality C'), " +
"(4,'Cracked');";
db.execSQL(InsertEggQualitySQL);
}catch (Exception e){
e.printStackTrace();
}
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// Drop table if existed, all data will be gone!!!
db.execSQL("DROP TABLE IF EXISTS location;");
db.execSQL("DROP TABLE IF EXISTS eqq_quality;"); onCreate(db);
}
}
为位置表创建表和插入成功，但其他表失败，错误日志显示在这里：
Caused by: android.database.sqlite.SQLiteException: no such table:
egg_quality (code 1): , while compiling: SELECT * FROM egg_quality
我该如何解决？
谢谢
解决方案
您的问题是您编写了不同的表名。该表是使用 eqq_table 创建的，而查询尝试访问 egg_table。
减少此类错误可能性的一种方法是为此类值定义 CONSTANT，然后始终通过相应的 CONSTANT 引用项目（表名、列名、索引名等）。
因此，我建议更改：-
public class DataHelper extends SQLiteOpenHelper {
private static final String LOGCAT = null;
private static final String DATABASE_NAME = "layerfarm-mobile.db";
private static final int DATABASE_VERSION = 1;
public static final String locationSQL = "CREATE TABLE IF NOT EXISTS location(id integer primary key, name text, address text, rid integer);";
public static final String eqq_qualitySQL = "CREATE TABLE IF NOT EXISTS eqq_quality(id integer primary key, name text);";
public DataHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
Log.d(LOGCAT,"Created");
// TODO Auto-generated constructor stub
}
@Override
public void onCreate(SQLiteDatabase db) {
try {
//Create table
db.execSQL(locationSQL);
db.execSQL(eqq_qualitySQL);
String InsertlocationSQL = "INSERT INTO location (id, name, address, rid) VALUES " +
"(1,'Location A','Blitar',1)," +
"(2,'Location B','Blitar',2)," +
"(3,'Location C','Blitar',3)," +
"(4,'Location D','Blitar',4);";
db.execSQL(InsertlocationSQL);
String InsertEggQualitySQL = "INSERT INTO egg_quality (id, name) VALUES " +
"(1,'Quality A'), " +
"(2,'Quality B'), " +
"(3,'Quality C'), " +
"(4,'Cracked');";
db.execSQL(InsertEggQualitySQL);
}catch (Exception e){
e.printStackTrace();
}
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// Drop table if existed, all data will be gone!!!
db.execSQL("DROP TABLE IF EXISTS location;");
db.execSQL("DROP TABLE IF EXISTS eqq_quality;"); onCreate(db);
}
}
到 ：-
public class DataHelper extends SQLiteOpenHelper {
private static final String LOGCAT = null;
private static final String DATABASE_NAME = "layerfarm-mobile.db";
private static final int DATABASE_VERSION = 1;
public static final String TABLE_LOCATION = "location";
public static final String TABLE_EGGQUALITY = "eqq_quality";
public static final String COL_LOCATION_ID = "id";
public static final String COL_LOCATION_NAME = "name";
public static final String COL_LOCATION_ADDRESS = "address";
public static final String COL_LOCATION_RID = "rid";
public static final String COl_EGGQUALITY_ID = "id";
public static final String COL_EGGQUALITY_NAME = "name";
private String locationSQL = "CREATE TABLE IF NOT EXISTS " + TABLE_LOCATION + "(" +
COL_LOCATION_ID + " INTEGER PRIMARY KEY, " +
COL_LOCATION_NAME + " TEXT, " +
COL_LOCATION_ADDRESS + " TEXT," +
COL_LOCATION_RID + " INTEGER" +
")";
private String egg_qualitySQL = "CREATE TABLE IF NOT EXISTS " + TABLE_EGGQUALITY + "(" +
COl_EGGQUALITY_ID + " INTEGER PRIMARY KEY, " +
COL_EGGQUALITY_NAME + " TEXT" +
")";
SQLiteDatabase mDB;
public DataHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
mDB = this.getWritableDatabase();
//Log.d(LOGCAT,"Created"); //????? Not correct as the database doesn't get created until an attempt is made to open it
// TODO Auto-generated constructor stub
}
@Override
public void onCreate(SQLiteDatabase db) {
mDB = db;
/* USING TRY CATCH MAY CAUSE A GREAT DEAL OF CONFUSION
try {
//Create table
db.execSQL(locationSQL);
db.execSQL(eqq_qualitySQL);
String InsertlocationSQL = "INSERT INTO location (id, name, address, rid) VALUES " +
"(1,'Location A','Blitar',1)," +
"(2,'Location B','Blitar',2)," +
"(3,'Location C','Blitar',3)," +
"(4,'Location D','Blitar',4);";
db.execSQL(InsertlocationSQL);
String InsertEggQualitySQL = "INSERT INTO egg_quality (id, name) VALUES " +
"(1,'Quality A'), " +
"(2,'Quality B'), " +
"(3,'Quality C'), " +
"(4,'Cracked');";
db.execSQL(InsertEggQualitySQL);
}catch (Exception e){
e.printStackTrace();
}
*/
db.execSQL(locationSQL);
db.execSQL(egg_qualitySQL);
insertLocation("Location A","Blitar",1);
insertLocation("Location B", "Blitar",2);
insertLocation("Location C","Blitar ",3);
insertLocation("Location D","Blitar",4);
insertEggQuality("Quality A");
insertEggQuality("Quality B");
insertEggQuality("Quality C");
insertEggQuality("Quality B");
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// Drop table if existed, all data will be gone!!!
db.execSQL("DROP TABLE IF EXISTS " + TABLE_LOCATION);
db.execSQL("DROP TABLE IF EXISTS " + TABLE_EGGQUALITY);
onCreate(db);
}
/**
* NOTE as ID column is defined as INTEGER PRIMARY KEY, then SQlite will,
* if no value is specified when inserting a row,
* generate a unique value for the id column
* (initially 1, then likely 2, then likely 3 ......... )
*/
public long insertLocation(String name, String address, long rid) {
ContentValues cv = new ContentValues();
cv.put(COL_LOCATION_NAME,name);
cv.put(COL_LOCATION_ADDRESS,address);
cv.put(COL_LOCATION_RID,rid);
return mDB.insert(TABLE_LOCATION,null,cv);
}
public long insertEggQuality(String name) {
ContentValues cv = new ContentValues();
cv.put(COL_EGGQUALITY_NAME,name);
return mDB.insert(TABLE_EGGQUALITY,null,cv);
}
public Cursor getAllLocations() {
return getAll(TABLE_LOCATION);
}
public Cursor getAllEggQuality() {
return getAll(TABLE_EGGQUALITY);
}
public Cursor getAll(String table) {
return mDB.query(table,null,null,null,null,null,null);
}
}
- 包括其他建议的改进
- 注意如果添加或更改表作为 onCreate 方法仅在首次创建数据库时运行，您应该删除应用程序的数据，卸载应用程序或增加版本号，然后重新运行应用程序（注意所有三种方法都会导致数据丢失）。
可以使用上述内容，例如在 Activity 中如下所示：-
public class MainActivity extends AppCompatActivity {
DataHelper mDBHlpr;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
mDBHlpr = new DataHelper(this);
Cursor csr = mDBHlpr.getAllLocations();
DatabaseUtils.dumpCursor(csr);
csr = mDBHlpr.getAllEggQuality();
DatabaseUtils.dumpCursor(csr);
}
}
dumpCursor 的结果类似于：-
12-12 04:32:23.854 1248-1248/so53700541.so53700541updatewithexpression I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@534aac14
12-12 04:32:23.854 1248-1248/so53700541.so53700541updatewithexpression I/System.out: 0 {
12-12 04:32:23.854 1248-1248/so53700541.so53700541updatewithexpression I/System.out: id=1
12-12 04:32:23.854 1248-1248/so53700541.so53700541updatewithexpression I/System.out: name=Location A
12-12 04:32:23.854 1248-1248/so53700541.so53700541updatewithexpression I/System.out: address=Blitar
12-12 04:32:23.854 1248-1248/so53700541.so53700541updatewithexpression I/System.out: rid=1
12-12 04:32:23.854 1248-1248/so53700541.so53700541updatewithexpression I/System.out: }
12-12 04:32:23.854 1248-1248/so53700541.so53700541updatewithexpression I/System.out: 1 {
12-12 04:32:23.854 1248-1248/so53700541.so53700541updatewithexpression I/System.out: id=2
12-12 04:32:23.854 1248-1248/so53700541.so53700541updatewithexpression I/System.out: name=Location B
12-12 04:32:23.854 1248-1248/so53700541.so53700541updatewithexpression I/System.out: address=Blitar
12-12 04:32:23.854 1248-1248/so53700541.so53700541updatewithexpression I/System.out: rid=2
12-12 04:32:23.854 1248-1248/so53700541.so53700541updatewithexpression I/System.out: }
12-12 04:32:23.854 1248-1248/so53700541.so53700541updatewithexpression I/System.out: 2 {
12-12 04:32:23.854 1248-1248/so53700541.so53700541updatewithexpression I/System.out: id=3
12-12 04:32:23.854 1248-1248/so53700541.so53700541updatewithexpression I/System.out: name=Location C
12-12 04:32:23.854 1248-1248/so53700541.so53700541updatewithexpression I/System.out: address=Blitar
12-12 04:32:23.854 1248-1248/so53700541.so53700541updatewithexpression I/System.out: rid=3
12-12 04:32:23.854 1248-1248/so53700541.so53700541updatewithexpression I/System.out: }
12-12 04:32:23.854 1248-1248/so53700541.so53700541updatewithexpression I/System.out: 3 {
12-12 04:32:23.854 1248-1248/so53700541.so53700541updatewithexpression I/System.out: id=4
12-12 04:32:23.854 1248-1248/so53700541.so53700541updatewithexpression I/System.out: name=Location D
12-12 04:32:23.854 1248-1248/so53700541.so53700541updatewithexpression I/System.out: address=Blitar
12-12 04:32:23.854 1248-1248/so53700541.so53700541updatewithexpression I/System.out: rid=4
12-12 04:32:23.854 1248-1248/so53700541.so53700541updatewithexpression I/System.out: }
12-12 04:32:23.854 1248-1248/so53700541.so53700541updatewithexpression I/System.out: <<<<<
12-12 04:32:23.854 1248-1248/so53700541.so53700541updatewithexpression I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@534c5e0c
12-12 04:32:23.854 1248-1248/so53700541.so53700541updatewithexpression I/System.out: 0 {
12-12 04:32:23.854 1248-1248/so53700541.so53700541updatewithexpression I/System.out: id=1
12-12 04:32:23.854 1248-1248/so53700541.so53700541updatewithexpression I/System.out: name=Quality A
12-12 04:32:23.854 1248-1248/so53700541.so53700541updatewithexpression I/System.out: }
12-12 04:32:23.854 1248-1248/so53700541.so53700541updatewithexpression I/System.out: 1 {
12-12 04:32:23.854 1248-1248/so53700541.so53700541updatewithexpression I/System.out: id=2
12-12 04:32:23.854 1248-1248/so53700541.so53700541updatewithexpression I/System.out: name=Quality B
12-12 04:32:23.854 1248-1248/so53700541.so53700541updatewithexpression I/System.out: }
12-12 04:32:23.854 1248-1248/so53700541.so53700541updatewithexpression I/System.out: 2 {
12-12 04:32:23.854 1248-1248/so53700541.so53700541updatewithexpression I/System.out: id=3
12-12 04:32:23.854 1248-1248/so53700541.so53700541updatewithexpression I/System.out: name=Quality C
12-12 04:32:23.854 1248-1248/so53700541.so53700541updatewithexpression I/System.out: }
12-12 04:32:23.854 1248-1248/so53700541.so53700541updatewithexpression I/System.out: 3 {
12-12 04:32:23.854 1248-1248/so53700541.so53700541updatewithexpression I/System.out: id=4
12-12 04:32:23.854 1248-1248/so53700541.so53700541updatewithexpression I/System.out: name=Quality B
12-12 04:32:23.854 1248-1248/so53700541.so53700541updatewithexpression I/System.out: }
12-12 04:32:23.854 1248-1248/so53700541.so53700541updatewithexpression I/System.out: <<<<<
推荐阅读
- amazon-web-services - 通过 HTTPS 调用使用用户名和密码的 AWS Cognito 用户池 InitiateAuth 的示例代码？
- r - 提取 R data.frame rowid 标签以从 data.frame 中捕获字符 rowid 值
- ruby - Ruby gem 应用程序的本地安装特定配置
- python - 如何在时间之间过滤 Pandas 中的数据？
- php - 试图获取非对象的属性（查看：C:\xampp\htdocs\preecom\resources\views\layouts\slider.blade.php）
- reactjs - 尝试在 create-react-app 项目中扩展 ESLint 的问题
- python - groupby python格式化输出
- arrays - 使用基于索引列表的值将新列添加到 numpy 数组
- mysql - MySQL存储过程：如何比较两个数字并递增计数具有较高数字的计数器
- sql - 如果其他列不为空，则更新 3 列