如何使用where子句在sqlite中检索数据？
如何使用where子句在sqlite中检索数据？
提问于 2018-06-03 01:44:03
我已经将数据从CSV文件导入到SQLite表中。当我尝试使用特定列检索记录时，它返回0条记录。然而，数据存在于表中。表的模式：
public static final class Vehicle2Wheel implements BaseColumns{
public static final String TABLE_NAME="Vehicle2Wheel";
public static final String VEH_ID = "VEH_ID";
public static final String VEH_REG_NUM = "VEH_REG_NUM";
public static final String VEH_ENGINE_NO = "VEH_ENGINE_NO";
public static final String VEH_CHASIS_NO= "VEH_CHASIS_NO";
public static final String VEH_MAKE= "VEH_MAKE";
public static final String VEH_CC = "VEH_CC";
public static final String VEH_COLOR = "VEH_COLOR";
public static final String VEH_MODEL = "VEH_MODEL";
public static final String OWN_NAME = "OWN_NAME";
public static final String OWN_FNAME = "OWN_FNAME";
public static final String CNIC = "OWN_CNIC";
public static final String OWN_ADDRESS = "OWN_ADDRESS";
}表的创建：
String tableVeh2Wheel = "CREATE TABLE "+ Schema.Vehicle2Wheel.TABLE_NAME+
" ("+ Schema.Vehicle2Wheel.VEH_ID+" TEXT,"
+ Schema.Vehicle2Wheel.VEH_REG_NUM+" TEXT,"
+ Schema.Vehicle2Wheel.VEH_ENGINE_NO+" TEXT,"
+ Schema.Vehicle2Wheel.VEH_CHASIS_NO+" TEXT,"
+ Schema.Vehicle2Wheel.VEH_MAKE+" TEXT,"
+ Schema.Vehicle2Wheel.VEH_CC+" TEXT,"
+ Schema.Vehicle2Wheel.VEH_COLOR+" TEXT,"
+ Schema.Vehicle2Wheel.VEH_MODEL+ " TEXT,"
+ Schema.Vehicle2Wheel.OWN_NAME+" TEXT,"
+ Schema.Vehicle2Wheel.OWN_FNAME+" TEXT,"
+ Schema.Vehicle2Wheel.CNIC+" TEXT,"
+ Schema.Vehicle2Wheel.OWN_ADDRESS+" TEXT"
+ ");";从CSV文件导入数据的代码：
public void setData(String filePath, String tableName, int colCount, String[] colNames){
SQLiteDatabase db = getWritableDatabase();
db.execSQL("delete from " + tableName);
try {
FileReader file = new FileReader(filePath);
BufferedReader buffer = new BufferedReader(file);
ContentValues contentValues = new ContentValues();
String line = "";
db.beginTransaction();
while ((line = buffer.readLine()) != null) {
String[] str = line.split(",",colCount);
int length = str.length;
for (int i = 0; i < length; i++) {
contentValues.put(colNames[i], str[i].toString());
}
db.insert(tableName, null, contentValues);
}
db.setTransactionSuccessful();
db.endTransaction();
} catch (IOException e) {
if (db.inTransaction())
db.endTransaction();
Toast.makeText(context,""+e.getMessage().toString(), Toast.LENGTH_SHORT).show();
}
if (db.inTransaction())
db.endTransaction();
}此代码(代码1)返回游标计数0：代码1:
database = new Database(getApplicationContext());
SQLiteDatabase db = database.getWritableDatabase();
Cursor cursor = null;
String vehId = "";
try {
String value = "KIL-3396";
cursor = db.rawQuery("SELECT VEH_ID FROM Vehicle2Wheel WHERE VEH_REG_NUM=?", new String[] {value + ""});
if(cursor.getCount() > 0) {
cursor.moveToFirst();
vehId = cursor.getString(cursor.getColumnIndex("VEH_ID"));
Log.v(TAG,"Vehicle ID is "+vehId);
}else
Log.v(TAG,"no record, size of cursor is "+cursor.getCount());
}finally {
cursor.close();
}此代码的输出如图片p1所示
然而，这个(下面)代码返回注册表号"KIL-3396“。这意味着该记录存在于表中，但是为什么不使用代码(即代码1)来检索它。为什么注册号"KIL-3396“不匹配？不使用除VEH_ID之外的任何列检索数据。
String query = "SELECT * FROM Vehicle2Wheel LIMIT 5;";
Cursor mcursor = db.rawQuery(query, null);
if (mcursor.getCount()>0) {
mcursor.moveToFirst();
while (mcursor.moveToNext()) {
for (int i = 0; i < mcursor.getColumnCount(); i++) {
Log.v(TAG, "Data is " + mcursor.getString(i));
}
}
}此代码的输出如图片p2所示
回答 1
Stack Overflow用户
回答已采纳
发布于 2018-06-03 07:02:06
我强烈怀疑如果您将第二个代码更改为:-
String query = "SELECT * FROM Vehicle2Wheel LIMIT 5;";
Cursor mcursor = db.rawQuery(query, null);
if (mcursor.getCount()>0) {
mcursor.moveToFirst();
while (mcursor.moveToNext()) {
for (int i = 0; i < mcursor.getColumnCount(); i++) {
Log.v(TAG, "Data is " + mcursor.getString(i));
}
}
}
Cursor cursor = null;
String vehId = "";
try {
String value = "KIL-3396";
cursor = db.rawQuery("SELECT VEH_ID FROM Vehicle2Wheel WHERE VEH_REG_NUM=?", new String[] {value + ""});
if(cursor.getCount() > 0) {
cursor.moveToFirst();
vehId = cursor.getString(cursor.getColumnIndex("VEH_ID"));
Log.v(TAG,"Vehicle ID is "+vehId);
} else
Log.v(TAG,"no record, size of cursor is "+cursor.getCount());
}finally {
cursor.close();
}您将获得预期的结果。即，代码1的位置/位置/顺序是问题，并且从提供的代码不能确定具体的问题。
测试我得到的代码：
06-02 22:21:00.399 1672-1672/va.vehicleapp V/SEARCHACTIVITY:车辆ID是3数据是3数据是KIL-3396数据是12345678数据是12345678数据是铃木数据是00.399数据是红色数据是GL数据是弗雷德数据是弗雷德数据是12345678数据是1弗雷德，弗雷迪克斯敦
- 在测试中我已经虚构了数据，除了注册号(2行，注意moveToFirst跳过了第一行，只显示了1行)
以下代码用于测试：
private void lookAtRecords(SQLiteDatabase db) {
String TAG = "SEARCHACTIVITY";
Cursor cursor = null;
String vehId = "";
try {
String value = "KIL-3396";
cursor = db.rawQuery("SELECT VEH_ID FROM Vehicle2Wheel WHERE VEH_REG_NUM=?", new String[] {value + ""});
if(cursor.getCount() > 0) {
cursor.moveToFirst();
vehId = cursor.getString(cursor.getColumnIndex("VEH_ID"));
Log.v(TAG,"Vehicle ID is "+vehId);
}else
Log.v(TAG,"no record, size of cursor is "+cursor.getCount());
}finally {
if (cursor != null) {
cursor.close();
}
}
String query = "SELECT * FROM Vehicle2Wheel LIMIT 5;";
Cursor mcursor = db.rawQuery(query, null);
if (mcursor.getCount()>0) {
mcursor.moveToFirst();
while (mcursor.moveToNext()) {
for (int i = 0; i < mcursor.getColumnCount(); i++) {
Log.v(TAG, "Data is " + mcursor.getString(i));
}
}
}
}页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/50659577
复制相关文章
相似问题
腾讯云开发者
