java - 数据库助手类？
问题描述
我无法让我的数据库有效地存储信息。我正在使用 Android Studio，我正在尝试动态创建一个 sqlite 数据库并将信息插入其中。
我无法将信息插入到我的表中（我返回 -1），但是，我不确定问题是插入语句还是表/数据库创建。
这是代码。任何帮助将不胜感激..我已经为此苦苦挣扎了一段时间。
public class DatabaseHelper extends SQLiteOpenHelper {
private static final String TAG = "DatabaseHelper";
SQLiteDatabase database;
private static final String Database_Name = "CISP_340.db";
private static final String Table_Name = "ANIMALS";
private static final String col1 = "_ID";
private static final String col2 = "ANIMAL_TYPE_CD";
private static final String col3 = "COUNT_NO";
private static final String col4 = "SEENON_DTM";
private static final String col5 = "COMMENTS_TXT";
public DatabaseHelper(Context context){
super(context, Table_Name, null, 1);
database = getWritableDatabase();
}
@Override
public void onCreate(SQLiteDatabase db) {
String createTable = "CREATE TABLE " + Table_Name + " (ID INTEGER PRIMARY KEY AUTOINCREMENT, "+
col2 + " INTEGER DEFAULT 0, " +
col3 +" INTEGER, " +
col4 + " TEXT, " +
col5 + " TEXT)";
db.execSQL(createTable);
}
@Override
public void onUpgrade(SQLiteDatabase db, int i, int i1) {
db.execSQL("DROP IF TABLE EXISTS " + Table_Name);
onCreate(db);
}
public boolean addData(Animal animal){
ContentValues contentValues = new ContentValues();
contentValues.put(col5, animal.getComments());
Log.d(TAG, "addData: Adding " + animal.toString() + " to " + Table_Name);
long result = database.insert(Table_Name, null, contentValues);
if (result == -1) {
return false;
}else{
return true;
}
}
public Cursor getData(){
SQLiteDatabase db = this.getWritableDatabase();
String query = "SELECT * FROM " + Table_Name;
Cursor data = db.rawQuery(query, null);
return data;
}
}
这是我的动物课：
public class Animal {
Animal(){
//Blank
}
Animal(int animalType, int count, String seenOn, String comments){
this.animalType = animalType;
this.count = count;
this.seenOn = seenOn;
this.comments = comments;
}
private int animalType;
private int count;
private String seenOn;
private String comments;
public int getAnimalType() {
return animalType;
}
public void setAnimalType(int type){
this.animalType = type;
}
public int getCount() {
return count;
}
public void setCount(int count){
this.count = count;
}
public String getSeenOn() {
return seenOn;
}
public void setSeenOn(String seenOn){
this.seenOn = seenOn;
}
public String getComments() {
return comments;
}
public void setComments(String comments){
this.comments = comments;
}
@Override
public String toString(){
return "Animal type: " + animalType + ", Animal count: " + count + ", Animal was seen on: " + seenOn + ", Comments about encounter: " + comments;
}
}
这是我的主要活动：
public class MainActivity extends AppCompatActivity {
private static final String TAG = "MainActivity";
DatabaseHelper dataBaseHelper;
private Button btnAdd, btnView;
private EditText editText;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
editText = (EditText) findViewById(R.id.editText);
btnAdd = (Button) findViewById(R.id.btnAdd);
btnView = (Button) findViewById(R.id.btnView);
dataBaseHelper = new DatabaseHelper(this);
Animal myAnimal = new Animal(1, 0, "Yes", "Raining");
Animal myAnimal2 = new Animal(1, 0, "No", "");
addAnimal(myAnimal);
addAnimal(myAnimal2);
btnAdd.setOnClickListener(new View.OnClickListener(){
@Override
public void onClick(View v){
Animal animal = new Animal();
animal.setComments(editText.getText().toString());
if (editText.length() != 0){
addAnimal(animal);
editText.setText("");
}else{
returnMessage("You must put something into this field");
}
}
});
btnView.setOnClickListener(new View.OnClickListener(){
@Override
public void onClick(View v){
Intent intent = new Intent(MainActivity.this, ListDataActivity.class);
startActivity(intent);
}
});
}
public void addAnimal( Animal animal ){
boolean insertData = dataBaseHelper.addData(animal);
if (insertData){
returnMessage("Success");
}else{
returnMessage("Fail");
}
}
private void returnMessage(String message){
Toast.makeText(this, message, Toast.LENGTH_SHORT).show();
}
}
尝试在构造函数中插入时在 LogCat 中的输出：
12-02 17:20:23.624 4100-4100/edu.davenport.cisp340.animaltracker E/SQLiteDatabase: Error inserting COUNT_NO=0 ANIMAL_TYPE_CD=1 SEENON_DTM=Yes COMMENTS_TXT=Raining
android.database.sqlite.SQLiteException: table ANIMALS has no column named SEENON_DTM (code 1): , while compiling: INSERT INTO ANIMALS(COUNT_NO,ANIMAL_TYPE_CD,SEENON_DTM,COMMENTS_TXT) VALUES (?,?,?,?)
at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:31)
at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1472)
at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1343)
at edu.davenport.cisp340.animaltracker.DatabaseHelper.addData(DatabaseHelper.java:59)
at edu.davenport.cisp340.animaltracker.MainActivity.addAnimal(MainActivity.java:65)
at edu.davenport.cisp340.animaltracker.MainActivity.onCreate(MainActivity.java:32)
at android.app.Activity.performCreate(Activity.java:6980)
at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1213)
at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2770)
at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2892)
at android.app.ActivityThread.-wrap11(Unknown Source:0)
at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1593)
at android.os.Handler.dispatchMessage(Handler.java:105)
at android.os.Looper.loop(Looper.java:164)
at android.app.ActivityThread.main(ActivityThread.java:6540)
at java.lang.reflect.Method.invoke(Native Method)
at com.android.internal.os.Zygote$MethodAndArgsCaller.run(Zygote.java:240)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:767)
解决方案
编辑
现在您添加了日志。问题是SEENON_DTM列不存在。
按照 ：-
table ANIMALS has no column named SEENON_DTM
自运行应用程序以来，您可能已经添加了该列。仅仅修改 onCreate 方法使用的 SQL 并不足以改变数据库的结构。也就是说，onCreate 仅在创建数据库时运行一次，然后将数据库保存到文件中，因此它是如何保留的。DatabaseHelper 的后续实例化，找到文件并且不调用 onCreate。
如果丢失任何数据（可能不存在有用的数据）不是问题，则修复是执行以下操作之一；
- 删除应用程序的数据（从设置中），或
- 卸载应用程序，或
- 增加数据库版本，即更改
super(context, Table_Name, null, 1);为super(context, Table_Name, null, 2);（1到2）- （这将导致 onUpgrade 方法运行，该方法应该删除表（请参阅下面关于使用 DROP TABLE IF EXISTS 而不是 DROP IF TABLE EXISTS 的注释））
然后重新运行应用程序。
我相信您的代码不是问题（代码中唯一的问题是 with DROP IF TABLE EXISTS，应该是DROP TABLE IF EXISTS）。
您的代码按预期工作（见下文），因此问题要么是您如何检查数据，要么是 Animal 类是原因。
下面是使用您的 DatabaseHelper 代码并从 Activity 调用它的代码（Animal 类可能与您的不同）。
我建议尝试下面的代码，针对您的 DatabaseHelper 类进行适当调整（即将 AnimalDBhelper 更改为 DatabaseHelper），然后检查日志。输出应如下所示（如果您已添加行且数据库尚未删除，则可能会有更多行）。
测试
使用您的代码副本（将类重命名为 AnimalDBhelper 以适应我的测试环境），然后在活动中使用以下代码（调用代码）：-
public class MainActivity extends AppCompatActivity {
AnimalDBhelper mADBHlpr;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.main_activity);
// Create two animals
mADBHlpr = new AnimalDBhelper(this);
Animal myanimal1 = new Animal(1,0,"Yes","Raining");
Animal myanimal2 = new Animal(1,0,"No","");
mADBHlpr.addData(myanimal1); // Add the first animal
Cursor csr = mADBHlpr.getData(); get the data
DatabaseUtils.dumpCursor(csr); dump the data (write it to the log)
mADBHlpr.addData(myanimal2); // Add the 2nd
csr = mADBHlpr.getData();
DatabaseUtils.dumpCursor(csr);
csr.close(); // Done with the cursor so close
}
}
结果 ：-
12-02 19:37:54.040 1172-1172/? D/DatabaseHelper: addData: Adding ?.Animal@534af570 to ANIMALS
12-02 19:37:54.044 1172-1172/? I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@534a5df8
12-02 19:37:54.044 1172-1172/? I/System.out: 0 {
12-02 19:37:54.044 1172-1172/? I/System.out: ID=1
12-02 19:37:54.044 1172-1172/? I/System.out: ANIMAL_TYPE_CD=0
12-02 19:37:54.044 1172-1172/? I/System.out: COUNT_NO=null
12-02 19:37:54.044 1172-1172/? I/System.out: SEENON_DTM=null
12-02 19:37:54.044 1172-1172/? I/System.out: COMMENTS_TXT=Raining
12-02 19:37:54.044 1172-1172/? I/System.out: }
12-02 19:37:54.044 1172-1172/? I/System.out: <<<<<
12-02 19:37:54.044 1172-1172/? D/DatabaseHelper: addData: Adding ?.Animal@534af5c4 to ANIMALS
12-02 19:37:54.048 1172-1172/? I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@534ab7d4
12-02 19:37:54.048 1172-1172/? I/System.out: 0 {
12-02 19:37:54.048 1172-1172/? I/System.out: ID=1
12-02 19:37:54.048 1172-1172/? I/System.out: ANIMAL_TYPE_CD=0
12-02 19:37:54.048 1172-1172/? I/System.out: COUNT_NO=null
12-02 19:37:54.048 1172-1172/? I/System.out: SEENON_DTM=null
12-02 19:37:54.048 1172-1172/? I/System.out: COMMENTS_TXT=Raining
12-02 19:37:54.048 1172-1172/? I/System.out: }
12-02 19:37:54.048 1172-1172/? I/System.out: 1 {
12-02 19:37:54.048 1172-1172/? I/System.out: ID=2
12-02 19:37:54.048 1172-1172/? I/System.out: ANIMAL_TYPE_CD=0
12-02 19:37:54.048 1172-1172/? I/System.out: COUNT_NO=null
12-02 19:37:54.048 1172-1172/? I/System.out: SEENON_DTM=null
12-02 19:37:54.048 1172-1172/? I/System.out: COMMENTS_TXT=
12-02 19:37:54.048 1172-1172/? I/System.out: }
12-02 19:37:54.048 1172-1172/? I/System.out: <<<<<
推荐阅读
- python - Gunicorn 配置 - 我想我可能做错了？
- sed - 如何使用 sed 替换所有文件中的字符串？
- php - 如何将混乱的数组排序为排列更整齐的数组？
- php - 如果表为空，如何显示“未找到记录”？
- android - 适用于 Android 上的视频的 S3 SignatureDoesNotMatch
- asp.net-mvc - google recaptcha v2 中的内容安全策略问题
- firebase - MissingPluginException（在通道 plugins.flutter.io/firebase_auth 上找不到方法 signInWithCredential 的实现）
- sharepoint - SharePoint 2013“查找项目”搜索返回 null 的日期值
- c++ - 如何理解使用 std::memory_order_acquire 调用 compare_exchange_strong
- html - 如何处理角度应用程序中的本地化