在android中正确设置sqlite db [英] set sqlite db properly in android
问题描述
我有一个包含不同列的 sqlite db，我正在对 db 进行试验，碰巧将版本从 2 改回 1.我现在已经撤消了所有更改，但 db 无法正常工作.我已经删除并重新安装了该应用程序，但仍然没有结果.
公共类 DBHelper 扩展 SQLiteOpenHelper {private static final String DATABASE_NAME = "Manager.db";public static final String TABLE_NAME = "BTN_TABLE";私有静态最终字符串_ID =_id"；private static final String LAST_BTN = "LAST_BTN";private static final String BUTTON_NO = "button_no";公共 DBHelper(@Nullable 上下文上下文){超级(上下文，DATABASE_NAME，空，2)；}@覆盖公共无效onCreate(SQLiteDatabase db){String create_table = "CREATE TABLE";+ TABLE_NAME + "( "+ID 整数主键"，+ LAST_BTN + "文本非空，"+ BUTTON_NO + "文本非空，"+ _ID + "文本非空)";db.execSQL(create_table);}@覆盖public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {if (oldVersion == 1 && newVersion == 2) {db.execSQL(ALTER TABLE"+ TABLE_NAME +添加列"+ BUTTON_NO+ "文本 ");}}public boolean addPos(String _id,int last_btn){SQLiteDatabase 数据库 = this.getWritableDatabase();ContentValues contentValues = new ContentValues();contentValues.put(_ID,_id);contentValues.put(LAST_BTN,last_btn);长结果 = database.insert(TABLE_NAME,null,contentValues);如果(结果 == -1){返回假；} 别的 {返回真；}}公共布尔updatePos(字符串_id，int last_btn){SQLiteDatabase 数据库 = this.getWritableDatabase();ContentValues contentValues = new ContentValues();contentValues.put(_ID,_id);contentValues.put(LAST_BTN,last_btn);long result = database.update(TABLE_NAME , contentValues , _ID + " =? " ,新字符串[]{_id});如果(结果 == -1){返回假；} 别的 {返回真；}}公共 int readPos(String _id,int last_btn){SQLiteDatabase db = this.getReadableDatabase();Cursor cursor = db.rawQuery("SELECT " + LAST_BTN + " FROM "+TABLE_NAME+"哪里"+ _ID +"=?",new String[]{_id});if(cursor.moveToFirst()){返回 cursor.getInt(cursor.getColumnIndex(LAST_BTN));} 别的 {返回0；}}}上述代码在更改之前运行良好.现在 updatePos 给出了正确的值，但 readPos 总是返回零，所以它不会给出任何错误，而是选择零而不是保存的值.我不知道发生了什么任何建议都会有所帮助.
在 readPos 方法中，而不是 :-
Cursor cursor = db.rawQuery("SELECT " + LAST_BTN + " FROM "+TABLE_NAME+"哪里"+ _ID +"=?"});你应该有:-
Cursor cursor = db.rawQuery("SELECT " + LAST_BTN + " FROM "+TABLE_NAME+"哪里"+ _ID +"=?",new String[]{_id});<块引用>
上述代码在更改之前运行良好.现在 updatePos 给出了正确的值，但 readPos 总是返回零......
使用 cursor.getInt(cursor.getColumnIndex(LAST_BTN)) 将返回 0，除非 LAST_BTN 列中的值是数字，否则返回 0(无法将字符串更改为数字，因此返回 0).根据您对问题的描述，LAST_BTN 列中存储的值似乎不是完全数字.
- 如果您希望获得唯一标识行的值，则返回主键 id id 列.
您也不需要将 last_btn 传递给 readPos 方法，因此可以使用 public int readPos(String _id) 而不是public int readPos(String _id, int last_btn).
此外，您将光标保持打开状态，打开的光标过多，应用程序将崩溃.我建议考虑以下几点:-
public int readPos(String _id) {INT RV = 0;SQLiteDatabase db = this.getReadableDatabase();Cursor cursor = db.rawQuery("SELECT " + LAST_BTN + " FROM "+TABLE_NAME+"哪里"+ _ID +"=?",new String[]{_id});if(cursor.moveToFirst()) {rv = cursor.getInt(cursor.getColumnIndex(LAST_BTN));}游标.关闭()；返回房车；}但是，如果存储在 LAST_BTN 列中的值是非数字的，则上述更改不会解决 readPos 将返回 0 的问题，例如如果是A1234"如果结果是1234"，则结果将为 0则返回 1234.
示例
使用您的代码(但使用建议的 readPos 方法)然后使用以下内容:-
DBHelper dbHelper = new DBHelper(this);SQLiteDatabase db = dbHelper.getWritableDatabase();db.execSQL("插入" + DBHelper.TABLE_NAME+ "(_id,LAST_BTN,button_no)";+价值观"+ "('test1','last_button1','button1')";+ ",('test2','last_button2','button2')";+ ",('test3','last_button3','button3')";+ ",('test4','199','button4')";+;");Log.d(DBINFO",test1 的 readPos 的结果是+ dbHelper.readPos(test1"));//0 因为 last_button1 不是数字Log.d(DBINFO",test2 的 readPos 的结果是+ dbHelper.readPos(test2"));//0 因为 last_button2 不是数字Log.d(DBINFO",test3 的 readPos 的结果是+ dbHelper.readPos(test3"));//0 因为 last_button3 不是数字Log.d(DBINFO",test4 的 readPos 的结果是+ dbHelper.readPos(test4"));//199 因为 199 是一个数字Log.d(DBINFO",test5 的 readPos 的结果是+ dbHelper.readPos(test5"));//0 表示没有找到行结果:-
D/DBINFO: test1 的 readPos 结果为 0D/DBINFO:test2 的 readPos 结果为 0D/DBINFO:test3 的 readPos 结果为 0D/DBINFO:test4 的 readPos 结果是 199D/DBINFO:test5 的 readPos 结果为 0即根据评论 test1-test3 返回 0 不是因为找不到行，而是因为存储在 LAST_BTN 列中的字符串无法转换为数字，因此 SQLite API 不是崩溃而是将其转换为 0.test4 被提取并返回一个非 0 值，因为存储在 LAST_BTN 中的值可以转换(表示)为数字.test5 在数据库中不存在，因此返回 0，因为未找到该行.
I have an sqlite db containing different columns and I was experimenting with the db and happen to change back the version to 1 from 2. I have now reversed all the changes but the db is not working. I have deleted and reinstalled the app but still no result.
public class DBHelper extends SQLiteOpenHelper {
private static final String DATABASE_NAME = "Manager.db";
public static final String TABLE_NAME = "BTN_TABLE";
private static final String _ID = "_id";
private static final String LAST_BTN = "LAST_BTN";
private static final String BUTTON_NO = "button_no";
public DBHelper(@Nullable Context context) {
super(context, DATABASE_NAME, null, 2);
}
@Override
public void onCreate(SQLiteDatabase db) {
String create_table = "CREATE TABLE " + TABLE_NAME + "( "
+ "ID INTEGER PRIMARY KEY ,"
+ LAST_BTN + " TEXT NOT NULL, "
+ BUTTON_NO + " TEXT NOT NULL, "
+ _ID + " TEXT NOT NULL)";
db.execSQL(create_table);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
if (oldVersion == 1 && newVersion == 2) {
db.execSQL("ALTER TABLE " + TABLE_NAME + " ADD COLUMN " + BUTTON_NO
+ " TEXT ");
}
}
public boolean addPos(String _id,int last_btn){
SQLiteDatabase database = this.getWritableDatabase();
ContentValues contentValues = new ContentValues();
contentValues.put(_ID,_id);
contentValues.put(LAST_BTN,last_btn);
long result = database.insert(TABLE_NAME,null,contentValues);
if (result == -1){
return false;
} else {
return true;
}
}
public boolean updatePos(String _id,int last_btn){
SQLiteDatabase database = this.getWritableDatabase();
ContentValues contentValues = new ContentValues();
contentValues.put(_ID,_id);
contentValues.put(LAST_BTN,last_btn);
long result = database.update(TABLE_NAME , contentValues , _ID + " =? " ,
new String[]{_id});
if (result == -1){
return false;
} else {
return true;
}
}
public int readPos(String _id,int last_btn)
{
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.rawQuery("SELECT " + LAST_BTN + " FROM "
+TABLE_NAME+" WHERE " + _ID +" =? ",new String[]{_id});
if(cursor.moveToFirst()){
return cursor.getInt(cursor.getColumnIndex(LAST_BTN));
} else {
return 0;
}
}
}
The above code was working perfectly fine before the changes. Now the updatePos gives the correct value but the readPos always returns zero so it does not gives any error but select zero rather than the saved one. I don't know what is happening any suggestion would be helpful.
In the readPos method, instead of :-
Cursor cursor = db.rawQuery("SELECT " + LAST_BTN + " FROM "
+TABLE_NAME+" WHERE " + _ID +" =? " });
You should have :-
Cursor cursor = db.rawQuery("SELECT " + LAST_BTN + " FROM "
+TABLE_NAME+" WHERE " + _ID +" =? ",new String[]{_id});
The above code was working perfectly fine before the changes. Now the updatePos gives the correct value but the readPos always returns zero ....
Using cursor.getInt(cursor.getColumnIndex(LAST_BTN)) will, unless the value in the LAST_BTN column is numeric, return 0 (unable to change string into a number so returns 0). From your description of the issue, then it seems likely that the values stored in the LAST_BTN column are not fully numeric.
- If you want the get a value that uniquely identifies the row then return the primary key id the id column.
You also don't need to pass last_btn to the readPos method, so could use public int readPos(String _id) instead of public int readPos(String _id, int last_btn).
Additionally, you are leaving the cursor open, too many open Cursors and the App will crash. I'd suggest considering the following:-
public int readPos(String _id) {
int rv = 0;
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.rawQuery("SELECT " + LAST_BTN + " FROM "
+TABLE_NAME+" WHERE " + _ID +" =? ",new String[]{_id});
if(cursor.moveToFirst()) {
rv = cursor.getInt(cursor.getColumnIndex(LAST_BTN));
}
cursor.close();
return rv;
}
However, the above changes WILL NOT resolve the issue that readPos will return 0 if the value stored in the LAST_BTN column is non-numeric e.g. if it is "A1234" then result will be 0, if it is "1234" then 1234 will be returned.
Example
Using your code (but with the suggested readPos method) then using the following:-
DBHelper dbHelper = new DBHelper(this);
SQLiteDatabase db = dbHelper.getWritableDatabase();
db.execSQL("INSERT INTO " + DBHelper.TABLE_NAME
+ "(_id,LAST_BTN,button_no)"
+ "VALUES "
+ "('test1','last_button1','button1')"
+ ",('test2','last_button2','button2')"
+ ",('test3','last_button3','button3')"
+ ",('test4','199','button4')"
+ ";"
);
Log.d("DBINFO","Result of readPos for test1 is " + dbHelper.readPos("test1")); // 0 as last_button1 is not a number
Log.d("DBINFO","Result of readPos for test2 is " + dbHelper.readPos("test2")); // 0 as last_button2 is not a number
Log.d("DBINFO","Result of readPos for test3 is " + dbHelper.readPos("test3")); // 0 as last_button3 is not a number
Log.d("DBINFO","Result of readPos for test4 is " + dbHelper.readPos("test4")); // 199 as 199 is a number
Log.d("DBINFO","Result of readPos for test5 is " + dbHelper.readPos("test5")); // 0 as no row found
Results in :-
D/DBINFO: Result of readPos for test1 is 0
D/DBINFO: Result of readPos for test2 is 0
D/DBINFO: Result of readPos for test3 is 0
D/DBINFO: Result of readPos for test4 is 199
D/DBINFO: Result of readPos for test5 is 0
i.e. as per the comments test1-test3 return 0 not because a row wasn't found but because the string stored in the LAST_BTN column cannot be converted to a number so instead of crashing the SQLite API converts it 0. test4 is extracted and a non 0 value is returned because the value stored in the LAST_BTN can be converted(represents) to a number. test5 doesn't exist in the database so 0 is returned because the row wasn't found.
这篇关于在android中正确设置sqlite db的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
