java - Android sqlite 列未找到错误

Question

好的，我已经制作了常用的数据库助手类，如下所示。我还创建了一个类，它使用 getData 和 insertData 方法与另一个类来获取特定的内容，例如使用我的 User 类来获取用户名。但是，当我在主 Activity 中调用该 Controller 类时，我想在其中使用它，它告诉我我尝试访问的列不存在。我已经尝试了几个小时了，现在已经累了......

在 logcat 中说...sqlite 返回:

error code = 1, msg = table userinfo has no column named username

此外，我还在数据库中添加了诸如 _id 和 android_metadata 之类的内容。谢谢。

    public class DatabaseHelper extends SQLiteOpenHelper{

            //The Android's default system path of your application database.
            private static final String DB_PATH = "/data/data/com.cslearn/databases/";
            private static final String DB_NAME = "example.db";
            private static final int DB_VERSION = 1; 
            private final Context myContext;
            private SQLiteDatabase myDatabase;

            public DatabaseHelper(Context context) {
                super(context, DB_NAME, null, DB_VERSION);
                this.myContext = context;
                System.out.println(context.getDatabasePath("myDatabase"));
            }   
            public void createDataBase() throws IOException{
                System.out.println("database creating...");
                boolean dbExist = checkDataBase();

                if(dbExist){
                    //do nothing - database already exist
                    System.out.println("db exists");
                }else{
                    //By calling this method and empty database will be created into the default system path
                       //of your application so we are gonna be able to overwrite that database with our database.
                    this.getReadableDatabase();
                    System.out.println("path = "+this.getReadableDatabase().getPath());
                    System.out.println("get database");

                    try {
                        this.close();
                        copyDataBase(); 
                    } catch (IOException e) {
                        throw new Error("Error copying database");
                    }
                }
                System.out.println("database created");
                this.close();
            }
            private boolean checkDataBase(){

                SQLiteDatabase checkDB = null;

                try{
                    String myPath = DB_PATH + DB_NAME;
                    checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
                }catch(SQLiteException e){
                    //database does't exist yet.
                }

                if(checkDB != null){
                    checkDB.close();
                }
                return checkDB != null ? true : false;
            }
            private void copyDataBase() throws IOException{


                System.out.println("Copying database....");
                //Open your local db as the input stream
                InputStream myInput = myContext.getAssets().open(DB_NAME);
                System.out.println("input > get assets");
                // Path to the just created empty db
                String outFileName = DB_PATH + DB_NAME;

                //Open the empty db as the output stream
                OutputStream myOutput = new FileOutputStream(outFileName);

                //transfer bytes from the inputfile to the outputfile
                byte[] buffer = new byte[1024];
                int length;
                while ((length = myInput.read(buffer))>0){
                    myOutput.write(buffer, 0, length);
                    System.out.println("output write...");

                }
                System.out.println("Database copied!!");
                //Close the streams
                myOutput.flush();
                myOutput.close();
                myInput.close();

            }
         /**   public void openReadonlyDataBase() throws SQLException{

                //Open the database
                String myPath = DB_PATH + DB_NAME;
                myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);

            }*/
            public void openDataBase() throws SQLException{
                String myPath = DB_PATH + DB_NAME;
                myDatabase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.NO_LOCALIZED_COLLATORS);
            }

            @Override
            public synchronized void close() {

                    if(myDatabase != null)
                        myDatabase.close();

                    super.close();
            }


            public void insertData (String sql){
                try {
                    this.createDataBase();
                } catch (IOException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
                try {
                    this.openDataBase();
                    System.out.println("database opened");
                }catch(SQLException e){

                    throw e;

                }
                myDatabase.execSQL(sql); //separate values with ,
                this.close();
            }



            public ArrayList<String> getData (String table,String [] columns, String selection){
                try {
                    this.createDataBase();
                } catch (IOException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
                try {
                    this.openDataBase();
                    System.out.println("database opened");

                }catch(SQLException e){
                    throw e;
                }
                System.out.println("getting data");
                ArrayList<String> results = new ArrayList<String>();
                Cursor c = myDatabase.query(table, columns, selection, null, null, null, null);
                System.out.println(c.getColumnCount());
                System.out.println(c.getColumnNames());
                System.out.println("got cursor c");     
                     if (c != null) {
                         /* Check if at least one Result was returned. */
                         if (c.moveToFirst()) {
                                 do {
                                         /* Retrieve the values of the Entry
                                          * the Cursor is pointing to. */
                                            String[] row = new String[c.getColumnCount()];      
                                            for(int i=0; i<c.getColumnCount(); i++){    
                                               row[i] = c.getString(i);
                                               System.out.println("getting data");
                                               results.add(row[i]);
                                               System.out.println("adding string");
                                    }

                                 } while (c.moveToNext());
                         }
                     }
                close();
                return results;
            }
            @Override
            public void onCreate(SQLiteDatabase db) {
            }
            @Override
            public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            }

                // Add your public helper methods to access and get content from the database.
               // You could return cursors by doing "return myDataBase.query(....)" so it'd be easy
               // to you to create adapters for your views.
}

Answer 1

您已重写 onCreate(SQLiteDatabase db) 方法，但它没有执行任何操作。您需要使用此方法创建表，如下所示:

// SQL statements to create new tables.
private static final String TBL_FRIENDS = "friends";
private static final String CREATE_TABLE_FRIENDS = "create table " +
    TBL_FRIENDS + " (" + KEY_ID + " integer primary key autoincrement, " +
    FRIEND_ID + " integer not null, " + FRIEND_MARKER + " integer not null, " +
    FRIEND_MOBILE + " text not null, " + FRIEND_NAME + " text not null);";

@Override
    public void onCreate(SQLiteDatabase db)
    {           
        db.execSQL("DROP TABLE IF EXISTS " + TBL_FRIENDS);          
        db.execSQL(CREATE_TABLE_FRIENDS);           

    }

显示的其他静态字符串，例如KEY_ID 是在其他方法中使用的列名称。