java - Android SQL 数据库错误(代码 1)

Question

如果单击下一个按钮，我正在尝试一一显示数据库中的数据。我只是想检查程序是否可以从数据库中选择第一个数据，但我收到了错误消息。

Caused by: android.database.sqlite.SQLiteException: no such column: color (code 1): , while compiling: SELECT DISTINCT _id, brand, model, price, color, img FROM car WHERE _id=1

DBAdapter.java

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

/**
 * Created by Asus on 24.04.2015.
 */
public class DBAdapter {private DatabaseHelper DBHelper;
    private SQLiteDatabase db;
    private final Context context;

    public static final String KEY_ROWID = "_id";
    public static final String KEY_BRAND = "brand";
    public static final String KEY_MODEL = "model";
    public static final String KEY_PRICE = "price";
    public static final String KEY_COLOR = "color";
    public static final String KEY_PHOTO = "img";


    private static final String TAG = "DBAdapter";

    private static final String DATABASE_NAME = "testDB";
    private static final String DATABASE_TABLE = "car";
    private static final int DATABASE_VERSION = 1;

    private static final String DATABASE_CREATE = "create table car (_id integer primary key autoincrement, "
            + "brand text, model text, price integer"
            + "color text, img blob);";

    // Constructor
    public DBAdapter(Context context) {
        this.context = context;
        DBHelper = new DatabaseHelper(context);
    }

    // To create and upgrade a database in an Android application SQLiteOpenHelper subclass is usually created
    private static class DatabaseHelper extends SQLiteOpenHelper {
        DatabaseHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            // onCreate() is only called by the framework, if the database does not exist
            Log.d("Create", "Creating the database");

            try {
                db.execSQL(DATABASE_CREATE);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            // onUpgrade() is only called by the framework, if one changes the database version number

            // Sends a Warn log message
            Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
                    + newVersion + ", which will destroy all old data");

            // Method to execute an SQL statement directly
            db.execSQL("DROP TABLE IF EXISTS contacts");
            onCreate(db);
        }
    }

    // Opens the database
    public DBAdapter open() throws SQLException {
        // Create and/or open a database that will be used for reading and writing
        //db = DBHelper.getWritableDatabase();

        // Use if you only want to read data from the database
        db = DBHelper.getReadableDatabase();
        return this;
    }

    // Closes the database
    public void close() {
        // Closes the database
        DBHelper.close();
    }

    // Insert a contact into the database
    public long insertContact(int rowid, String brand, String model, int price, String color, byte [] photo) {
        // The class ContentValues allows to define key/values. The "key" represents the
        // table column identifier and the "value" represents the content for the table
        // record in this column. ContentValues can be used for inserts and updates of database entries.
        ContentValues initialValues = new ContentValues();
        initialValues.put(KEY_ROWID, rowid);
        initialValues.put(KEY_BRAND, brand);
        initialValues.put(KEY_MODEL, model);
        initialValues.put(KEY_PRICE, price);
        initialValues.put(KEY_COLOR, color);
        initialValues.put(KEY_PHOTO, photo);
        return db.insert(DATABASE_TABLE, null, initialValues);
    }

    // Retrieves a particular contact
    public Cursor getContact(int id) throws SQLException {
        // rawQuery() directly accepts an SQL select statement as input.
        // query() provides a structured interface for specifying the SQL query.

        // A query returns a Cursor object. A Cursor represents the result of a query
        // and basically points to one row of the query result. This way Android can buffer
        // the query results efficiently; as it does not have to load all data into memory

        Cursor mCursor = db.query(true, DATABASE_TABLE, new String[] {
                KEY_ROWID,KEY_BRAND, KEY_MODEL, KEY_PRICE, KEY_COLOR, KEY_PHOTO}, KEY_ROWID + "=" + id, null, null, null, null, null);

        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;
    }
}

DisplayActivity.java

import android.app.Activity;
import android.database.Cursor;
import android.graphics.Bitmap;
import android.graphics.BitmapFactory;
import android.graphics.Color;
import android.support.v7.app.ActionBarActivity;
import android.os.Bundle;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.view.Window;
import android.widget.EditText;
import android.widget.ImageView;
import android.widget.TableLayout;
import android.widget.TableRow;
import android.widget.TextView;
import android.widget.Toast;

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;


public class DisplayActivity extends Activity {
    private String []  car_images;
    private TextView carid;
    private TextView brand;
    private TextView model;
    private TextView price;
    private TextView color;
    private ImageView photo;
    private DBAdapter db;
    private Bitmap mBitmap;

    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        requestWindowFeature(Window.FEATURE_NO_TITLE);
        setContentView(R.layout.display);

        car_images = getResources().getStringArray(R.array.car_images);

        carid = (TextView) findViewById(R.id.caridDB);
        brand = (TextView) findViewById(R.id.brandDB);
        model = (TextView) findViewById(R.id.modelDB);
        price = (TextView) findViewById(R.id.priceDB);
        color = (TextView) findViewById(R.id.colorDB);
        photo = (ImageView) findViewById(R.id.photoDB);

        // Database will be created using the DBAdapter
        db = new DBAdapter(this);

        // Add a contact
        // A Bitmap must be converted to a byte array so that it can be stored in a blob
        mBitmap = BitmapFactory.decodeResource(getResources(), R.drawable.mito);
        // mBitmap = BitmapFactory.decodeResource(getResources(),R.drawable.google);

        ByteArrayOutputStream mByteArrayOutputStream = new ByteArrayOutputStream();
        mBitmap.compress(Bitmap.CompressFormat.JPEG, 100, mByteArrayOutputStream);
        byte[] image = mByteArrayOutputStream.toByteArray();

        db.open();
         long id = db.insertContact(1, "Alfa Romeo", "Mito", 2900, "Red",image);
        // long id = db.insertContact("Larry", "page", 3000000, image);
        // long id = db.insertContact("Steve", "Ballmer", 5000000, image);


        Cursor c = db.getContact(1);
        if (c.moveToFirst())
            DisplayContact(c);
        else
            Toast.makeText(this, "No record found", Toast.LENGTH_LONG)
                    .show();

        db.close();




    }

    public void onClick(View view) {


            // Get a contact
            db.open();
            Cursor c = db.getContact(Integer.parseInt(carid.getText().toString()));
            if (c.moveToFirst())
                DisplayContact(c);
            else
                Toast.makeText(this, "No record found", Toast.LENGTH_LONG)
                        .show();
            db.close();


    }

    public void DisplayContact(Cursor c) {
        carid.setText(c.getString(0));
        brand.setText(c.getString(1));
        model.setText(c.getString(2));
        price.setText(c.getString(3));
        color.setText(c.getString(4));


        if (Integer.parseInt(c.getString(0))==0){
        // Add a contact
        // A Bitmap must be converted to a byte array so that it can be stored in a blob
        mBitmap = BitmapFactory.decodeResource(getResources(), R.drawable.mito);
        // mBitmap = BitmapFactory.decodeResource(getResources(),R.drawable.google);

        ByteArrayOutputStream mByteArrayOutputStream = new ByteArrayOutputStream();
        mBitmap.compress(Bitmap.CompressFormat.JPEG, 100, mByteArrayOutputStream);
        byte[] image = mByteArrayOutputStream.toByteArray();
        image = c.getBlob(5);
        Bitmap mBitmap = BitmapFactory.decodeByteArray(image, 0, image.length);
        photo.setImageBitmap(mBitmap); }
        else if (Integer.parseInt(c.getString(0))==1){
            // Add a contact
            // A Bitmap must be converted to a byte array so that it can be stored in a blob
            mBitmap = BitmapFactory.decodeResource(getResources(), R.drawable.giulettaa);
            // mBitmap = BitmapFactory.decodeResource(getResources(),R.drawable.google);

            ByteArrayOutputStream mByteArrayOutputStream = new ByteArrayOutputStream();
            mBitmap.compress(Bitmap.CompressFormat.JPEG, 100, mByteArrayOutputStream);
            byte[] image = mByteArrayOutputStream.toByteArray();
            image = c.getBlob(5);
            Bitmap mBitmap = BitmapFactory.decodeByteArray(image, 0, image.length);
            photo.setImageBitmap(mBitmap);
        }
        else if (Integer.parseInt(c.getString(0))==2){
            // Add a contact
            // A Bitmap must be converted to a byte array so that it can be stored in a blob
            mBitmap = BitmapFactory.decodeResource(getResources(), R.drawable.alfa4c);
            // mBitmap = BitmapFactory.decodeResource(getResources(),R.drawable.google);

            ByteArrayOutputStream mByteArrayOutputStream = new ByteArrayOutputStream();
            mBitmap.compress(Bitmap.CompressFormat.JPEG, 100, mByteArrayOutputStream);
            byte[] image = mByteArrayOutputStream.toByteArray();
            image = c.getBlob(5);
            Bitmap mBitmap = BitmapFactory.decodeByteArray(image, 0, image.length);
            photo.setImageBitmap(mBitmap);
        }
        else if (Integer.parseInt(c.getString(0))==3){
            // Add a contact
            // A Bitmap must be converted to a byte array so that it can be stored in a blob
            mBitmap = BitmapFactory.decodeResource(getResources(), R.drawable.gran);
            // mBitmap = BitmapFactory.decodeResource(getResources(),R.drawable.google);

            ByteArrayOutputStream mByteArrayOutputStream = new ByteArrayOutputStream();
            mBitmap.compress(Bitmap.CompressFormat.JPEG, 100, mByteArrayOutputStream);
            byte[] image = mByteArrayOutputStream.toByteArray();
            image = c.getBlob(5);
            Bitmap mBitmap = BitmapFactory.decodeByteArray(image, 0, image.length);
            photo.setImageBitmap(mBitmap);
        }
        else if (Integer.parseInt(c.getString(0))==4){
            // Add a contact
            // A Bitmap must be converted to a byte array so that it can be stored in a blob
            mBitmap = BitmapFactory.decodeResource(getResources(), R.drawable.coupe);
            // mBitmap = BitmapFactory.decodeResource(getResources(),R.drawable.google);

            ByteArrayOutputStream mByteArrayOutputStream = new ByteArrayOutputStream();
            mBitmap.compress(Bitmap.CompressFormat.JPEG, 100, mByteArrayOutputStream);
            byte[] image = mByteArrayOutputStream.toByteArray();
            image = c.getBlob(5);
            Bitmap mBitmap = BitmapFactory.decodeByteArray(image, 0, image.length);
            photo.setImageBitmap(mBitmap);
        }
        else if (Integer.parseInt(c.getString(0))==5){
            // Add a contact
            // A Bitmap must be converted to a byte array so that it can be stored in a blob
            mBitmap = BitmapFactory.decodeResource(getResources(), R.drawable.c3);
            // mBitmap = BitmapFactory.decodeResource(getResources(),R.drawable.google);

            ByteArrayOutputStream mByteArrayOutputStream = new ByteArrayOutputStream();
            mBitmap.compress(Bitmap.CompressFormat.JPEG, 100, mByteArrayOutputStream);
            byte[] image = mByteArrayOutputStream.toByteArray();
            image = c.getBlob(5);
            Bitmap mBitmap = BitmapFactory.decodeByteArray(image, 0, image.length);
            photo.setImageBitmap(mBitmap);
        }
        else if (Integer.parseInt(c.getString(0))==6){
            // Add a contact
            // A Bitmap must be converted to a byte array so that it can be stored in a blob
            mBitmap = BitmapFactory.decodeResource(getResources(), R.drawable.c4);
            // mBitmap = BitmapFactory.decodeResource(getResources(),R.drawable.google);

            ByteArrayOutputStream mByteArrayOutputStream = new ByteArrayOutputStream();
            mBitmap.compress(Bitmap.CompressFormat.JPEG, 100, mByteArrayOutputStream);
            byte[] image = mByteArrayOutputStream.toByteArray();
            image = c.getBlob(5);
            Bitmap mBitmap = BitmapFactory.decodeByteArray(image, 0, image.length);
            photo.setImageBitmap(mBitmap);
        }
        else if (Integer.parseInt(c.getString(0))==7){
            // Add a contact
            // A Bitmap must be converted to a byte array so that it can be stored in a blob
            mBitmap = BitmapFactory.decodeResource(getResources(), R.drawable.gla);
            // mBitmap = BitmapFactory.decodeResource(getResources(),R.drawable.google);

            ByteArrayOutputStream mByteArrayOutputStream = new ByteArrayOutputStream();
            mBitmap.compress(Bitmap.CompressFormat.JPEG, 100, mByteArrayOutputStream);
            byte[] image = mByteArrayOutputStream.toByteArray();
            image = c.getBlob(5);
            Bitmap mBitmap = BitmapFactory.decodeByteArray(image, 0, image.length);
            photo.setImageBitmap(mBitmap);
        }
        else if (Integer.parseInt(c.getString(0))==8){
            // Add a contact
            // A Bitmap must be converted to a byte array so that it can be stored in a blob
            mBitmap = BitmapFactory.decodeResource(getResources(), R.drawable.smart);
            // mBitmap = BitmapFactory.decodeResource(getResources(),R.drawable.google);

            ByteArrayOutputStream mByteArrayOutputStream = new ByteArrayOutputStream();
            mBitmap.compress(Bitmap.CompressFormat.JPEG, 100, mByteArrayOutputStream);
            byte[] image = mByteArrayOutputStream.toByteArray();
            image = c.getBlob(5);
            Bitmap mBitmap = BitmapFactory.decodeByteArray(image, 0, image.length);
            photo.setImageBitmap(mBitmap);
        }
        else if (Integer.parseInt(c.getString(0))==9){
            // Add a contact
            // A Bitmap must be converted to a byte array so that it can be stored in a blob
            mBitmap = BitmapFactory.decodeResource(getResources(), R.drawable.node);
            // mBitmap = BitmapFactory.decodeResource(getResources(),R.drawable.google);

            ByteArrayOutputStream mByteArrayOutputStream = new ByteArrayOutputStream();
            mBitmap.compress(Bitmap.CompressFormat.JPEG, 100, mByteArrayOutputStream);
            byte[] image = mByteArrayOutputStream.toByteArray();
            image = c.getBlob(5);
            Bitmap mBitmap = BitmapFactory.decodeByteArray(image, 0, image.length);
            photo.setImageBitmap(mBitmap);
        }
        else {
            // Add a contact
            // A Bitmap must be converted to a byte array so that it can be stored in a blob
            mBitmap = BitmapFactory.decodeResource(getResources(), R.drawable.juke);
            // mBitmap = BitmapFactory.decodeResource(getResources(),R.drawable.google);

            ByteArrayOutputStream mByteArrayOutputStream = new ByteArrayOutputStream();
            mBitmap.compress(Bitmap.CompressFormat.JPEG, 100, mByteArrayOutputStream);
            byte[] image = mByteArrayOutputStream.toByteArray();
            image = c.getBlob(5);
            Bitmap mBitmap = BitmapFactory.decodeByteArray(image, 0, image.length);
            photo.setImageBitmap(mBitmap);
        }
    }
}

Answer 1

你的问题就在这里

private static final String DATABASE_CREATE = "create table car (_id integer primary key autoincrement, "
        + "brand text, model text, price integer" // <-- NO COMMA after 'integer'!
        + "color text, img blob);";

你少了一个逗号。应该是:

private static final String DATABASE_CREATE = "create table car (_id integer primary key autoincrement, "
        + "brand text, model text, price integer," // <-- COMMA
        + "color text, img blob);";