数据库助手课？

Question

我在获取数据库以有效存储信息方面遇到了困难。我正在使用Android，我正在尝试创建一个sqlite，并将信息插入其中。

我无法将信息插入到我的表中(我正在返回a-1)，但是，我不确定问题是insert语句还是表/db创建。

这是密码。任何帮助都将不胜感激..。我把头撞在这上面已经有一阵子了。

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;
}

}

这是我的MainActivity：

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)

Answer 1

编辑

现在你添加了日志。问题是列SEENON_DTM不存在。

如下:-

• table ANIMALS has no column named SEENON_DTM

您可能在运行App之后添加了该列。仅仅修改onCreate方法使用的SQL并不足以改变数据库的结构。也就是说，当创建数据库时，onCreate只运行一次，然后将数据库保存到一个文件中，从而保持它的位置。在DatabaseHelper的下实例化，查找文件，不调用onCreate。

如果丢失任何数据(可能不存在有用的数据)，则修复不是一个问题，而是执行以下操作之一；

• 删除应用程序的数据(从设置中)，或
• 卸载应用程序，或
• 增加数据库版本，即将super(context, Table_Name, null, 1);更改为super(context, Table_Name, null, 2); (1到2)
  • (这将导致onUpgrade方法运行，该方法应该删除表(请参阅下面关于使用drop (如果存在)和不使用DROP (存在)的说明)

​

然后重新运行应用程序。

我相信您的代码不是一个问题(代码中唯一的问题是DROP IF TABLE EXISTS，它应该是DROP TABLE IF EXISTS )。

您的代码按预期工作(见下面)，所以问题要么是您如何检查数据，要么是因为类。

下面是使用DatabaseHelper代码并从活动中调用它的代码(动物类可能与您的类不同)。

我建议尝试下面的代码，对您的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: <<<<<

Answer 2

您还没有真正描述出出了什么问题(您是在查询游标而没有找到您期望的数据？)，但是您需要在某个地方关闭游标(see this answer)，并且您不需要在getComment()中获取数据库，因为您已经拥有了它。下面是一个应该在示例类中工作的版本。

public String getComment(){
    String query = "SELECT * FROM " + Table_Name;
    String comments = "";
    Cursor data = database.rawQuery(query, null);
    if( data.moveToFirst() ) { // iterate all the rows of the table
        do {
            String val = data.getString(4); // pull out column 5
            // String val = data.getString(data.getColumnIndex(col5)); // or this way if you prefer
            comments += val; // do something with the data - e.g. rebuild Animal object
        } while (data.moveToNext());
    }
    data.close();
    return comments;
}