时间戳

首页 > 解决方案 > 向/从 SQLite 插入和检索数据

android - 向/从 SQLite 插入和检索数据

问题描述

我是android中sqllite的新手。我正在向sql数据库添加记录，插入和选择查询都没有给出运行时错误，除非我在获取数据时有空光标（在选择查询上）。让我知道在插入值或获取它们时是否有问题。与选择查询一样，我得到列名但没有记录。这是我的代码。

public class SQLHelper extends SQLiteOpenHelper {
private static final String DATABASE_NAME = "MyDb.db";
private static final int DATABASE_VERSION = 1;
private SQLiteDatabase database;

public String stringArray[];
private ArrayList<tasksList> arrayList;

public final static String PROCESS_TABLE = "Process";
public final static String DB_P_ID = "db_process_id";
public final static String PROCESS_ASSIGNMENT_ID = "process_id";
public final static String CASETITLE = "case_title";
public final static String APPEARANCE_DATE = "appearance_date";
public final static String APPEARANCE_TIME = "appearance_time";
public final static String APPEARANCE_REASON = "appearance_reason"; 
public final static String COURT_NAME = "court_name";
public final static String RESPONDENT_NAME = "respondent_name";
public final static String ADDRESS = "address";
public final static String CONTACT_NO = "contact_no";
public final static String CNIC = "cnic";
public final static String NOTICE_NUMBER = "notice_number";

private static final String DATABASE_CREATE_PROCESS_TABLE = " CREATE TABLE "+ PROCESS_TABLE + " (db_process_id integer primary key autoincrement," +
        " process_id text not null," +
        " case_title text not null,appearanc e_date text not null ,appearance_time text not null, court_name text not null , respondent_name text not null ," +
        " address text not null ,contact_no text not null ,cnic text not null , appearance_reason text not null , notice_number text not null );";

public SQLHelper(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
}


@Override
public void onCreate(SQLiteDatabase sqLiteDatabase) {
    sqLiteDatabase.execSQL(DATABASE_CREATE_PROCESS_TABLE);
}

@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int oldVersion, int newVersion) {
    Log.w(SQLHelper.class.getName(),"Upgrading database from version " + oldVersion + " to "
            + newVersion + ", which will destroy all old data");
    sqLiteDatabase.execSQL("DROP TABLE IF EXISTS Process");
    onCreate(sqLiteDatabase);
}

public Boolean insertProcess(String id, String case_Title,String app_date, String app_time, String app_reason,
                           String court_name,String name,String location,String cnic,String notice_no,String contact_no) {

    database = this.getWritableDatabase();
    ContentValues values = new ContentValues();
    values.put(PROCESS_ASSIGNMENT_ID, id);
    values.put(CASETITLE, case_Title);
    values.put(APPEARANCE_DATE, app_date);
    values.put(APPEARANCE_TIME, app_time);
    values.put(APPEARANCE_REASON, app_reason);
    values.put(COURT_NAME, court_name);
    values.put(RESPONDENT_NAME, name);
    values.put(ADDRESS, location);
    values.put(CONTACT_NO, contact_no);
    values.put(CNIC, cnic);
    values.put(NOTICE_NUMBER, notice_no);
    database.insert(PROCESS_TABLE, null, values);

    return true;

}

public Cursor getCompletedTask(Context applicationContext) {
    Context context = applicationContext;
    database = this.getReadableDatabase();
    String[] cols = new String[] { CASETITLE};

    Cursor mCursor = database.rawQuery(" Select * from " + PROCESS_TABLE , null);

 //        Cursor mCursor = database.query(true, PROCESS_TABLE, cols, null, null, null, null,  null, null);
    int size = mCursor.getCount();
    stringArray = new String[size];
    if (mCursor != null) {
        mCursor.moveToFirst();
        while (!mCursor.isAfterLast()) {
            String id = mCursor.getString(0);
            String title = mCursor.getString(1);

            tasksList fooditem=new tasksList();
            fooditem.setCase_id(id);
            fooditem.setCase_title(title);
            arrayList.add(fooditem);
            mCursor.moveToNext();
        }
    }
    return mCursor; // iterate to get each value.
}

标签： androiddatabasesqliteandroid-sqlite

解决方案

你的问题是你有一个空间，appearanc e_date text not null即这应该是appearance_date text not null

说这本身不会导致失败，只是列名是appearanc而不是appearance_date。这很可能会导致随之而来的混乱（例如，您可能遇到的问题如下）。

这是一个很好的例子，说明了为什么建议依赖单一来源来获取表名和列名。因此，而不是更正：-

private static final String DATABASE_CREATE_PROCESS_TABLE = " CREATE TABLE "+ PROCESS_TABLE + " (db_process_id integer primary key autoincrement," +
        " process_id text not null," +
        " case_title text not null,appearance_date text not null ,appearance_time text not null, court_name text not null , respondent_name text not null ," +
        " address text not null ,contact_no text not null ,cnic text not null , appearance_reason text not null , notice_number text not null );";

我建议类似：-

private static final String CREATE_PROCESS_TABLE = 
        "CREATE TABLE IF NOT EXISTS " +
                PROCESS_TABLE + 
                "(" +
                PROCESS_ASSIGNMENT_ID + " INTEGER PRIMARY KEY," + //NOTE you very likely don't want autoincrement
                CASETITLE + " TEXT NOT NULL," +
                APPEARANCE_DATE + " TEXT NOT NULL," +
                APPEARANCE_TIME + " TEXT NOt NULL," +
                COURT_NAME + " TEXT NOT NULL, " +
                RESPONDENT_NAME + " TEXT NOT NULL," +
                ADDRESS + " TEXT NOT NULL," +
                CONTACT_NO + " TEXT NOT NULL, " +
                CNIC + " TEXT NOT NULL," +
                APPEARANCE_REASON + " TEXT NOT NULL," +
                NOTICE_NUMBER + " TEXT NOT NULL" +
                ")";

您的问题可能是由于意外的列名appearan而不是预期的列名的结果，appearance_date因为尝试插入记录将不知道列外观日期并且不插入行。所以你的桌子将是空的。

如果您查看日志，您会看到类似于：-

05-07 21:13:54.724 1285-1285/? E/SQLiteLog: (1) table Process has no column named appearance_date

如上所述更正（删除不需要的空间）删除应用程序的数据，然后重新运行应用程序应该可以解决该问题。

推荐阅读