sqlite 搜索不将光标返回到调用活动

Question

如果您有以下方面的帮助，我将不胜感激： 已解决我是个白痴！ - 我有 2 个日志标签，一个在界面中，另一个在活动中。我在调试中监视了接口标签，认为它包含活动标签。因此我只看到界面中的结果，而不是活动的结果。因此，我认为该活动不是“从界面获取返回” - 抱歉。感谢那些试图提供帮助的人。

我在sqlite上调用搜索（ dbHelper.getNumberOfRecords(); - 获取记录数 - 和 - dbHelper.findManyRecords(aNameDateRecord, "name", "Sarah"); - 从数据库中获取一些记录）通过databaseHelper和另一个“接口”类从活动中获取数据库。搜索有效（我可以在调试“TAG”中看到结果。 - 问题是结果应该“返回”到调用活动，但事实并非如此。任何关于为什么会发生这种情况的帮助将不胜感激。谢谢。请参阅下面的代码：

“接口”类的一部分...

public class DatabaseInterface{

private static final String TAG = "HelloDatePicker";

private static final String TableName = "t_namesAndDates";
private static final String RowId = "_id";
private static final String Col_name = "name";
private static final String Col_date = "date";
private Context context;
private DatabaseHelper dbHelper;
private SQLiteDatabase database;

public DatabaseInterface(Context context){
    this.context = context;
}
    //open the database
public DatabaseInterface open(){
    dbHelper = new DatabaseHelper(context);
    database = dbHelper.getWritableDatabase();
    return this;
}
    //close the database
public DatabaseInterface close(){
    dbHelper.close();
    return this;
}

//get number of records in database
public int getNumberOfRecords(){        
    open(); 
    String sql = "SELECT * FROM " + TableName;      
    Cursor cursor = database.rawQuery(sql, null);
    int numberOfRecords = cursor.getCount();
    cursor.close();
    close();
    Log.i(TAG, "HelloDatePicker-getNumberOfRecords **** " + numberOfRecords);
    return numberOfRecords;
}

//search for many records
public Cursor findManyRecords(NameDateRecord aNameDateRecord, String columnName, String whereClause){
    open(); 

    Log.i(TAG, "HelloDatePicker-column aNameDateRecord = " + aNameDateRecord + " columnName = " + columnName + " whereClause = " + whereClause);
    /*
    String selection = columnName;
    Log.i(TAG, "HelloDatePicker-column name = " + columnName + " whereClause = " + whereClause);
    String[] selectionArgs = new String[] {"Sarah"};//searches on a name
    String[] columns = new String[] {"_id","name","date"};
    for(int i=0;i<selectionArgs.length;i++)
        Log.i(TAG, "HelloDatePicker-selectionArgs = " + selectionArgs[i]);
    //Cursor cursor = database.query(TableName, columns, " name = ", selectionArgs, null, null, null);
    */

    String sql = "SELECT * FROM " + TableName + " WHERE name = 'Sarah'";
    Cursor c = database.rawQuery(sql, null);
    Log.i(TAG, "HelloDatePicker-getNumberOfRecords @@@@@@ " + c.getCount());

    c.moveToFirst();
    while (c.moveToNext()) {
        // Your code
        Log.i(TAG, "HelloDatePicker- id = " + c.getInt(c.getColumnIndex(RowId)) + " name " + c.getString(c.getColumnIndex(Col_name)) + " date = " +
                c.getInt(c.getColumnIndex(Col_date)));
    }
    Log.i(TAG, "HelloDatePicker-findManyRecords");
    close();

    return c;

}

帮助程序类的一部分...

public class DatabaseHelper extends SQLiteOpenHelper{

private static final String TAG = "HelloDatePicker";    
private static final String DBname = "db_date";
private static final String TableName = "t_namesAndDates";
private static int versionNumber = 1;   

    //the constructor
public DatabaseHelper(Context context) {
    super(context, DBname, null, versionNumber);
    // TODO Auto-generated constructor stub 
}

@Override
public void onCreate(SQLiteDatabase database) {
    //create table once on installation of app
    String columns = "(_id integer primary key autoincrement,name text not null,date integer)";     
    String sql = "CREATE TABLE " + TableName + columns;
    Log.i(TAG, "HelloDatePicker-create database-sql = " + sql);
    database.execSQL(sql);      
}

@Override
public void onUpgrade(SQLiteDatabase database, int oldVersion, int newVersion) {
    // TODO Auto-generated method stub
    // Logs that the database is being upgraded
    Log.i(TAG, "Upgrading database from version " + oldVersion + " to "
            + newVersion + ", which will destroy all old data");

    // Kills the table and existing data
    database.execSQL("DROP TABLE IF EXISTS notes");

    // Recreates the database with a new version
    onCreate(database);     
}   

}

以及最后调用搜索的活动...

public class HelloDatePicker extends Activity {

private static final String TAG = "datepicker";

private static final String RowId = "_id";
private static final String Col_name = "name";
private static final String Col_date = "date";

private TextView mDateDisplay;
private Button mPickDate;
private int mYear;
private int mMonth;
private int mDay;

private DatabaseInterface dbHelper;

NameDateRecord aNameDateRecord;



static final int DATE_DIALOG_ID = 0;
/** Called when the activity is first created. */
@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.main);

    Log.i(TAG, "in onCreate HelloDatePicker");

    dbHelper = new DatabaseInterface(this);
        //to get number of records in database
    int numberRecords = dbHelper.getNumberOfRecords();
    Log.i(TAG, "number of records in database  &&&&&& " + numberRecords);
        //to search for records
    dbHelper = new DatabaseInterface(this);
    Cursor c = dbHelper.findManyRecords(aNameDateRecord, "name", "Sarah");
    Log.i(TAG, "HelloDatePicker-getNumberOfRecords +++++ " + c.getCount());
    c.moveToFirst();
    while (c.moveToNext()) {
        // Your code
        Log.i(TAG,"HelloDatePicker- id = " + c.getInt(c.getColumnIndex(RowId)) + " name "
                        + c.getString(c.getColumnIndex(Col_name))
                        + " date = " + c.getInt(c.getColumnIndex(Col_date)));

提前致谢

。

Answer 1

看起来您在关闭数据库后返回了游标，这可能会使游标无效。

Answer 2

这是糟糕的 SQL 风格：

String sql = "SELECT * FROM " + TableName;      
Cursor cursor = database.rawQuery(sql, null);
int numberOfRecords = cursor.getCount();

请使用 "count"-function 代替！另外，为什么要创建“dbHelper”两次？而这个：

String sql = "CREATE TABLE " + TableName + columns;
database.execSQL(sql); 

是垃圾。使用“rawQuery()”、“query()”或“PreparedStatement”对象来创建查询。

最后但并非最不重要的一点是，在“findManyRecords”方法中，您将遍历整个 Cursor 然后返回它。所以光标位于返回字段的末尾。不要这样做或使用“moveToFirst”方法。