使用SQLite存储数据

Question

我不太熟悉使用SQL存储Android数据项，但下面我有两种方法可以插入新的数据项并打印所有现有数据。

我的飞行类具有以下属性：

• 航班号-字符串
• 航空公司-字符串
• date-字符串
• image path-字符串
• reg-字符串
• type-字符串ID
• int(每个实例唯一(

由于某些原因，我无法打印数据，我不确定insertFlight()方法或databaseToString()方法是否存在问题。

public class DatabaseSQ extends SQLiteOpenHelper {
public static final String DATABASE_NAME = "FlightDatabase.db";
public static final String TABLE_FLIGHTS = "flights";
private static final int DATABASE_VERSION = 1;
public static final String FLIGHT_COLUMN_ID = "_id";
public static final String FLIGHT_NUMBER = "number";
public static final String FLIGHT_DATE = "date";
public static final String FLIGHT_REG = "reg";
public static final String FLIGHT_AIRLINE = "airline";
public static final String FLIGHT_TYPE = "type";
public static final String FLIGHT_IMAGE_PATH = "image";
public DatabaseSQ(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}

@Override
public void onCreate(SQLiteDatabase db) {
String query = "CREATE TABLE " + TABLE_FLIGHTS + "(" +
FLIGHT_COLUMN_ID + "INTEGER PRIMARY KEY, " +
FLIGHT_NUMBER + " TEXT, " +
FLIGHT_DATE + " TEXT, " +
FLIGHT_REG + " TEXT, " +
FLIGHT_AIRLINE + " TEXT, " +
FLIGHT_TYPE + " TEXT, " +
FLIGHT_IMAGE_PATH + " TEXT " +
");";
db.execSQL(query);

}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("DROP TABLE IF EXISTS " + TABLE_FLIGHTS);
onCreate(db);
}
public void insertFlight(Flight toADD) {
ContentValues contentValues = new ContentValues();
contentValues.put(FLIGHT_COLUMN_ID, toADD.getId());
contentValues.put(FLIGHT_NUMBER, toADD.getFlightNumber());
contentValues.put(FLIGHT_DATE, toADD.getDate());
contentValues.put(FLIGHT_REG, toADD.getReg());
contentValues.put(FLIGHT_AIRLINE, toADD.getAirline());
contentValues.put(FLIGHT_TYPE, toADD.getType());
contentValues.put(FLIGHT_IMAGE_PATH, toADD.getImagePath());
SQLiteDatabase db = getWritableDatabase();


db.insert(TABLE_FLIGHTS, null, contentValues);
db.close();
}
public String databaseToString() {
String dbString = "";
SQLiteDatabase db = getWritableDatabase();
String query = "SELECT * FROM " + TABLE_FLIGHTS + " WHERE 1";
Cursor c = db.rawQuery(query, null);
c.moveToFirst();
while(!c.isAfterLast()) {
if(c.getString(c.getColumnIndex("number")) != null) {
dbString += c.getString(c.getColumnIndex("number"));
dbString += "n";
}
c.moveToNext();
}
db.close();
return dbString;
}
}

当在另一个活动文件中，单击按钮时，必须存储一个新的航班对象，并将其航班号传递给主活动时，就会调用该数据库。(数据库对象dbHandler之前已实例化(。

public void AddFlight(View view) {
String name = nameText.getText().toString();
String date = dateText.getText().toString();
String airline = airlineText.getText().toString();
String type = typeText.getText().toString();
String reg = regText.getText().toString();

Context context = this;
Class destination = MainActivity.class;
Intent goBackToMain = new Intent(context, destination);

Flight newFlight = new Flight(name, date, reg, airline, type, mCurrentPhotoPath);
dbHandler.insertFlight(newFlight);
String data = dbHandler.databaseToString();
goBackToMain.putExtra(Intent.EXTRA_TEXT, data);
startActivity(goBackToMain);
}

Answer 1

问题是因为您在表上错误地移动了光标。按照尝试

Cursor c = db.rawQuery(query, null);
if(c != null) {
while(c.moveToNext()) {
dbString += c.getString(c.getColumnIndex("number"));
dbString += "n";
}
c.close()
}

Answer 2

FLIGHT_COLUMN_ID + "INTEGER PRIMARY KEY, " +

列名和类型之间缺少一个空格。这本身并不是语法错误，列名只是变成了_idINTEGER。

contentValues.put(FLIGHT_COLUMN_ID, toADD.getId());

db.insert(TABLE_FLIGHTS, null, contentValues);

由于没有FLIGHT_COLUMN_ID列，此插入将失败。使用insertOrThrow()而不是insert()在出现错误时引发异常。

此外，您不需要在INTEGER PRIMARY KEY列中插入值，系统将自动为您生成一个新的rowid。

更正CREATE TABLESQL后，您可以卸载应用程序以使onCreate()再次运行。