android - 找不到 sql 表 - android

Question

我的 sql 数据库有问题。

我正在使用现有的 sql 数据库，但程序未读取它。

我的sql表如下:

BEGIN TRANSACTION;
CREATE TABLE "android_metadata" ("locale" TEXT DEFAULT 'en_US');
INSERT INTO android_metadata VALUES('en_US');
CREATE TABLE bucketitems (
    "_id" INTEGER PRIMARY KEY NOT NULL,
    "bucketNo" INTEGER NOT NULL,
    "bucketItemName" TEXT NOT NULL,
    "bucketItemImg" TEXT,
    "bucketItemDesc" TEXT,
    "completed" INTEGER NOT NULL
);
INSERT INTO bucketitems VALUES(1,1,'Test',NULL,NULL,0);
COMMIT;

我把sql文件放在asset文件夹下

然后使用这段代码

public class DatabaseHandler extends SQLiteOpenHelper {

    // Contacts table name
    private static final String TABLE_BUCKET_ITEMS = "bucketitems";

    // Contacts Table Columns names
    private static final String KEY_ID = "_id";
    private static final String KEY_BUCKET = "bucketNo";
    private static final String KEY_NAME = "bucketItemName";
    private static final String KEY_FLAG = "completed";

    private static String DB_PATH = "/data/data/com.example.bucketlist/databases/";

    private static String DB_NAME = "bucketList";

    private SQLiteDatabase myDataBase;

    private final Context myContext;

    public DatabaseHandler(Context context) {
        super(context, DB_NAME, null, 1);
        this.myContext = context;
    }


    //Creates a empty database on the system and rewrites it with your own database.
    public void createDataBase() throws IOException {
        boolean dbExist = checkDataBase();
        if (dbExist) {
        } else {
            this.getReadableDatabase();
            try {
                this.close();
                copyDataBase();
            } catch (IOException e) {
                throw new Error("Error copying database");
            }
        }
    }

    // Check if the database already exist to avoid re-copying the file
    private boolean checkDataBase() {
        SQLiteDatabase checkDB = null;
        try {
            String myPath = DB_PATH + DB_NAME;
            checkDB = SQLiteDatabase.openDatabase(myPath, null,
                    SQLiteDatabase.OPEN_READWRITE);
        } catch (SQLiteException e) {
        }
        if (checkDB != null) {
            checkDB.close();
        }
        return checkDB != null ? true : false;
    }

    // Copies your database from your local assets-folder to the just created
    private void copyDataBase() throws IOException {
        InputStream myInput = myContext.getAssets().open(DB_NAME);
        String outFileName = DB_PATH + DB_NAME;
        OutputStream myOutput = new FileOutputStream(outFileName);
        byte[] buffer = new byte[1024];
        int length;
        while ((length = myInput.read(buffer)) > 0) {
            myOutput.write(buffer, 0, length);
        }
        myOutput.flush();
        myOutput.close();
        myInput.close();
    }

    public void openDataBase() throws SQLException {
        String myPath = DB_PATH + DB_NAME;
        myDataBase = SQLiteDatabase.openDatabase(myPath, null,
                SQLiteDatabase.OPEN_READWRITE);
    }

    @Override
    public synchronized void close() {
        if (myDataBase != null)
            myDataBase.close();
        super.close();
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_BUCKET_ITEMS);
        onCreate(db);
    }

    // Getting All Contacts
    public Bucket[] getAllContacts(int bucketNo) {
        List<BucketItem> bucketList = new ArrayList<BucketItem>();

        String selectQuery = "SELECT * FROM " + TABLE_BUCKET_ITEMS + " WHERE "
                + KEY_BUCKET + " = " + bucketNo;

        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);

        if (cursor.moveToFirst()) {
            do {
                BucketItem bucket = new BucketItem();
                bucket.setBucketItemNo(Integer.parseInt(cursor.getString(0)));
                bucket.setBucketItemName(cursor.getString(2));
                Boolean isCompleted = false;
                if (Integer.parseInt(cursor.getString(5)) == 0) {
                    isCompleted = true;
                }
                bucket.setCompleted(isCompleted);
                bucketList.add(bucket);
            } while (cursor.moveToNext());
        }

        Bucket[] buckets = bucketList.toArray(new Bucket[bucketList.size()]);

        return buckets;
    }

    //Updating BucketItem
    public int updateContact(BucketItem bucketItem) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(KEY_NAME, bucketItem.getBucketItemName());
        values.put(KEY_FLAG, bucketItem.isCompleted());

        // updating row
        return db.update(TABLE_BUCKET_ITEMS, values, KEY_ID + " = ?",
                new String[] { String.valueOf(bucketItem.getBucketItemNo()) });
    }

然后在我的 Activity 课上，我的代码如下。

@覆盖

protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_bucket_item);
        int value = getIntent().getExtras().getInt("bucketno");
        DatabaseHandler myDbHelper = new DatabaseHandler(this);
        try {
                myDbHelper.createDataBase();
        } catch (IOException ioe) {
            throw new Error("Unable to create database");
        }

        try {
            myDbHelper.openDataBase();
        }catch(SQLException sqle){
            throw sqle;
        }
        Bucket[] buckets = myDbHelper.getAllContacts(value);
        System.out.println(buckets);

    }

但是我有一个错误。

12-26 09:34:17.667: E/SQLiteLog(792): (1) no such table: bucketitems
12-26 09:34:24.030: D/AndroidRuntime(792): Shutting down VM
12-26 09:34:24.030: W/dalvikvm(792): threadid=1: thread exiting with uncaught exception (group=0x40a70930)
12-26 09:34:24.166: E/AndroidRuntime(792): FATAL EXCEPTION: main
12-26 09:34:24.166: E/AndroidRuntime(792): java.lang.RuntimeException: Unable to start activity ComponentInfo{com.example.bucketlist/com.example.bucketlist.BucketItemActivity}: android.database.sqlite.SQLiteException: no such table: bucketitems (code 1): , while compiling: SELECT * FROM bucketitems WHERE bucketNo = 1
12-26 09:34:24.166: E/AndroidRuntime(792):  at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2180)
12-26 09:34:24.166: E/AndroidRuntime(792):  at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2230)

请帮忙。谢谢。

Answer 1

从 Assets 中复制时忘记附加“.db”。
输入应该像这样 InputStream is = getContext().getAssets().open("MyDatabaseFile.db");
输出应该是这样的 OutputStream os = new FileOutputStream("/data/data/YOUR_PACKAGE_NAME/databases/MyDatabaseFile.db");

 // Copies your database from your local assets-folder to the just created
    private void copyDataBase() throws IOException {
        InputStream myInput = myContext.getAssets().open(DB_NAME + ".db"); // add .db here and see
        String outFileName = DB_PATH + DB_NAME + ".db"; // same here
        OutputStream myOutput = new FileOutputStream(outFileName);
        byte[] buffer = new byte[1024];
        int length;
        while ((length = myInput.read(buffer)) > 0) {
            myOutput.write(buffer, 0, length);
        }
        myOutput.flush();
        myOutput.close();
        myInput.close();
    }