删除后，Android的SQLite变化的rowid [英] Android SQLite change rowids after deleting
问题描述
我有一个与Android程序SQLite数据库的麻烦。
我在做它有一个列表我希望能够通过滚动窗口小部件，所以当我点击下一步按钮，将显示在数据库中的下一行。
直到我从数据库中删除的东西，我有我的rowid缺号也能正常工作。我已经成立了AUTOINCREMENT的rowid，但有些数据被删除后，我有缺号，当我试图通过返回一个错误的号码中滚动。
我只是想知道是否有另一种方式改变的rowid为顺序行被删除了。还是有更好的方式，通过每一行滚动不参照ROWID？
编辑：这是code我有我的下一步活动
@覆盖
保护无效的onCreate（捆绑savedInstanceState）{
super.onCreate（savedInstanceState）;
C =这一点; //获取共享数据
myShared = getShared preferences（文件名，0）;
//从myShared获取NUM_LOC数据，或者如果失败数量1负载
将myNum = myShared.getInt（NUM_LOC，0）; //检查是否将myNum是大于总数等于或更大
//如果是的话，将其设置回一
FlashDataBase TESTDB =新FlashDataBase（本）;
testDB.open（）;
INT testNum = testDB.getNumber（）;
testDB.close（）;
如果（myNum的＆GT; = testNum）{
myNum的= 1;
}
其他{
//设置为下一个值
将myNum ++;
}
//设置appwidgetmanager于此上下文
AWM = AppWidgetManager.getInstance（C）; //让小部件一个remoteviews
RemoteViews V =新的RemoteViews（c.getPackageName（），R.layout.widget）;
组件名thisWidget =新的组件名（C，VanillaProvider.class）; //从数据库中获取字
FlashDataBase FDB =新FlashDataBase（本）;
fdb.open（）;
字符串someWord = fdb.getWord（myNum的）;
fdb.close（）; //商店目前的myNum到共享preF
共享preferences.Editor编辑= myShared.edit（）;
editor.putInt（NUM_LOC中，myNum）;
editor.commit（）; //改变窗口小部件的remoteviews
//并更新appwidgemanager
v.setTextViewText（R.id.middle_button，someWord）;
awm.updateAppWidget（thisWidget，ⅴ）; 完（）;
}
这似乎很粗糙，但我想是没有用的方式
cursor.moveToNext（）;
在我的数据库类来获取下一个数字？
这是我的数据库类BTW
私有静态类DbHelper扩展SQLiteOpenHelper {
公共DbHelper（上下文的背景下）{
超（背景下，DATABASE_NAME，空，SCHEMA_VERSION）;
} @覆盖
公共无效的onCreate（SQLiteDatabase DB）{
//执行一些SQL code
db.execSQL（CREATE TABLE+ DATABASE_TABLE +（+ KEY_ROWID
+INTEGER PRIMARY KEY AUTOINCREMENT
// + KEY_NUM +INTEGER NOT NULL，
+ KEY_WORD +TEXT NOT NULL，
+ KEY_MEANING +TEXT NOT NULL）;）;
} @覆盖
公共无效onUpgrade（SQLiteDatabase分贝，INT oldVersion，诠释静态网页）{
db.execSQL（DROP TABLE IF EXISTS+ DATABASE_TABLE）;
的onCreate（DB）;
}}公共FlashDataBase（上下文C）{
myContext = C;
}//打开我们的数据库
公共FlashDataBase的open（）抛出的SQLException {
myHelper =新DbHelper（myContext）;
MYDATABASE = myHelper.getWritableDatabase（）;
返回此;
}//这个关闭它
公共无效的close（）{
myHelper.close（）;
}众长createMethod（串词，串意思）{
ContentValues CV =新ContentValues（）;
//cv.put(KEY_NUM，NUM）;
cv.put（KEY_WORD，字）;
cv.put（KEY_MEANING，意）; //并把这些字符串放到我们的数据库
//插入CV到表
返回myDatabase.insert（DATABASE_TABLE，空，CV）;
}公共字符串的getData（）{
的String [] =列新的String [] {KEY_ROWID，KEY_WORD，KEY_MEANING}; //设置一个游标来读取信息
光标C = myDatabase.query（DATABASE_TABLE，列，NULL，NULL，NULL，
NULL，NULL）;
字符串结果=; //这些整数的相同屏幕取词的方法
分别//换句话说0 1,2 INT iWord = c.getColumnIndex（KEY_WORD）;
INT iMeaning = c.getColumnIndex（KEY_MEANING）; 为（c.moveToFirst（）;！c.isAfterLast（）; c.moveToNext（））{
结果+ = c.getInt（0）++ c.getString（iWord）++ c.getString（iMeaning）+\\ n; }
返回结果;
}
公共字符串屏幕取词（长升）{ 的String [] =列新的String [] {KEY_ROWID，KEY_WORD，KEY_MEANING};
光标C = myDatabase.query（DATABASE_TABLE，列KEY_ROWID +=
+ 1，NULL，NULL，NULL，NULL）;
如果（C！= NULL）{
//因为我们设置KEY_ROWID本身加上长（L），则
// moveToFirst开始于L（升）^如上面所看到的
c.moveToFirst（）;
//那是一个1号就不信L以下
//这是第二列，又名INT iWord从类的getData
串字= c.getString（1）;
返回字;
}
返回null;
}公共字符串getMeaning（长升）{ 的String [] =列新的String [] {KEY_ROWID，KEY_WORD，KEY_MEANING};
光标C = myDatabase.query（DATABASE_TABLE，列KEY_ROWID +=
+ 1，NULL，NULL，NULL，NULL）;
如果（C！= NULL）{
//因为我们设置KEY_ROWID本身加上长（L），则
// moveToFirst开始于L（升）^如上面所看到的
c.moveToFirst（）;
//那下方的2号
//这是第二列，又名INT iWord从类的getData
串含义= c.getString（2）;
回归之意;
}
返回null;
}公众诠释getNumber（）{
的String [] =列新的String [] {KEY_ROWID，KEY_WORD，KEY_MEANING}; //设置一个游标来读取信息
光标C = myDatabase.query（DATABASE_TABLE，列，NULL，NULL，NULL，
NULL，NULL）;
INT结果为0; 为（c.moveToFirst（）;！c.isAfterLast（）; c.moveToNext（））{
结果++;
}
返回结果;
}
感谢所有帮助还
*最后编辑：
我计算出来有点不同比被告知我。我保持在共享整数值计数假若比行的总量更复位，并没有在使用c.moveToNext（数据库类本身的循环中），而不是共享整数像这样：
公共字符串屏幕取词（长升）{ 的String [] =列新的String [] {KEY_ROWID，KEY_WORD，KEY_MEANING};
光标C = myDatabase.query（DATABASE_TABLE，列，KEY_ROWID，NULL，NULL，NULL，NULL）;
字符串结果; c.moveToFirst（）;
的for（int i = 1; I＆LT; = 1;我++）{
如果（c.isLast（））{
c.moveToFirst（）;
结果= c.getString（1）;
返回结果;
} c.moveToNext（）;
}
结果= c.getString（1）;
返回结果;
}
的方式SQL工作的自增ID将是一致的行被删除后，如此反复做的ID顺序的唯一方式是手动周围或复制移动条目行，删除表，创建一个新的，并再次插入行。
您可以但是只使用限制子句中的SQL查询来告诉你希望获取哪些和多少行。具体而言，你可以使用类似这样的查询：
SELECT * FROM`your_table` LIMIT first_row_to_fetch，number_of_rows_to_fetch
例如，如果你想10行，首先是17日（ID的独立的），你可以使用
SELECT * FROM`your_table` LIMIT 17，10
编辑：
由于自己的编辑，你不执行自己的查询字符串，所以你的情况，你应该使用查询（）方法与添加的属性， LIMIT / OFFSET （请参阅<一个href=\"http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html#query%28java.lang.String%2C%20java.lang.String%5B%5D%2C%20java.lang.String%2C%20java.lang.String%5B%5D%2C%20java.lang.String%2C%20java.lang.String%2C%20java.lang.String%2C%20java.lang.String%29\"相对=nofollow>文档）。
这你就已经在使用查询（）方法的重载采用应包含一个数字（这是查询限）用逗号分隔的两个数字的字符串（这是第一偏移，然后为查询的限制）。实际上，这意味着你的
光标C = myDatabase.query（DATABASE_TABLE，列，NULL，NULL，NULL，NULL，NULL）;
变为
光标C = myDatabase.query（DATABASE_TABLE，列，NULL，NULL，NULL，NULL，NULL，偏移限制）;
在这里偏移和限制就像我上面的第一个例子。
此外，您可能想要把在排序依据属性的东西（一个刚限制属性之前），所以你知道你的结果出来，每次以同样的方式。我会推荐使用 KEY_ROWID 对于这一点，导致：
光标C = myDatabase.query（DATABASE_TABLE，列，NULL，NULL，NULL，NULL，KEY_ROWID，偏移限制）;
I am having trouble with the sqlite database with an android program. I'm making a widget which has a list I want to be able to scroll through, so when I click the "next" button it will show the next row in the database.
This works fine until I delete something from the database and I have missing numbers for my rowids. I have the rowids set up to AUTOINCREMENT, but after some data is deleted I have missing numbers, and when I try to scroll through those numbers an error is returned.
I just wanted to know if there is another way change the rowids to sequential order after a row is deleted. Or is there a better way to scroll through each row without referring to the rowid?
edit: this is the code I have for my "Next" activity
@Override
protected void onCreate(Bundle savedInstanceState){
super.onCreate(savedInstanceState);
c = this;
//get the shared data
myShared = getSharedPreferences(FILENAME, 0);
//gets NUM_LOC data from myShared or if that fails loads in number 1
myNum = myShared.getInt(NUM_LOC, 0);
//check to see if myNum is equal to or bigger than the total number
//and if it is, then set it back to one
FlashDataBase testDB = new FlashDataBase(this);
testDB.open();
int testNum = testDB.getNumber();
testDB.close();
if(myNum >= testNum ){
myNum = 1;
}
else{
//set to next value
myNum ++;
}
//set the appwidgetmanager to this context
awm = AppWidgetManager.getInstance(c);
//make a remoteviews for the widget
RemoteViews v = new RemoteViews(c.getPackageName(), R.layout.widget);
ComponentName thisWidget = new ComponentName(c, VanillaProvider.class);
//get word from database
FlashDataBase fdb = new FlashDataBase(this);
fdb.open();
String someWord = fdb.getWord(myNum);
fdb.close();
//store current myNum into sharedpref
SharedPreferences.Editor editor = myShared.edit();
editor.putInt(NUM_LOC, myNum);
editor.commit();
//change the widget's remoteviews
//and update the appwidgemanager
v.setTextViewText(R.id.middle_button, someWord);
awm.updateAppWidget(thisWidget, v);
finish();
}
Which seems crude, but I am thinking is there a way to use cursor.moveToNext(); in my Database class to get the next number?
this is my Database class btw private static class DbHelper extends SQLiteOpenHelper {
public DbHelper(Context context) {
super(context, DATABASE_NAME, null, SCHEMA_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
// execute some SQL code
db.execSQL("CREATE TABLE " + DATABASE_TABLE + " (" + KEY_ROWID
+ " INTEGER PRIMARY KEY AUTOINCREMENT, "
//+ KEY_NUM + " INTEGER NOT NULL, "
+ KEY_WORD + " TEXT NOT NULL, "
+ KEY_MEANING + " TEXT NOT NULL);");
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE);
onCreate(db);
}
}
public FlashDataBase(Context c) {
myContext = c;
}
// open our db
public FlashDataBase open() throws SQLException {
myHelper = new DbHelper(myContext);
myDatabase = myHelper.getWritableDatabase();
return this;
}
// and this closes it
public void close() {
myHelper.close();
}
public long createMethod(String word, String meaning) {
ContentValues cv = new ContentValues();
//cv.put(KEY_NUM, num);
cv.put(KEY_WORD, word);
cv.put(KEY_MEANING, meaning);
// and put these strings into our database
// inserts the cv into the table
return myDatabase.insert(DATABASE_TABLE, null, cv);
}
public String getData() {
String[] columns = new String[] { KEY_ROWID, KEY_WORD, KEY_MEANING };
// set a cursor to read the info
Cursor c = myDatabase.query(DATABASE_TABLE, columns, null, null, null,
null, null);
String result = "";
// these ints are the same as getWord's method
// in other words, 0 1 and 2 respectively
int iWord = c.getColumnIndex(KEY_WORD);
int iMeaning = c.getColumnIndex(KEY_MEANING);
for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()) {
result += c.getInt(0) + " " + c.getString(iWord) + " " + c.getString(iMeaning) + "\n";
}
return result;
}
public String getWord(long l) {
String[] columns = new String[] { KEY_ROWID, KEY_WORD, KEY_MEANING };
Cursor c = myDatabase.query(DATABASE_TABLE, columns, KEY_ROWID + "="
+ l, null, null, null, null);
if (c != null) {
// since we set KEY_ROWID to itself plus Long (l), then the
// moveToFirst starts at L (l) ^as seen above
c.moveToFirst();
// thats a number 1 not letter l below
// which is the second column, aka int iWord from the getData class
String word = c.getString(1);
return word;
}
return null;
}
public String getMeaning(long l) {
String[] columns = new String[] { KEY_ROWID, KEY_WORD, KEY_MEANING };
Cursor c = myDatabase.query(DATABASE_TABLE, columns, KEY_ROWID + "="
+ l, null, null, null, null);
if (c != null) {
// since we set KEY_ROWID to itself plus Long (l), then the
// moveToFirst starts at L (l) ^as seen above
c.moveToFirst();
// thats a number 2 below
// which is the second column, aka int iWord from the getData class
String meaning = c.getString(2);
return meaning;
}
return null;
}
public int getNumber() {
String[] columns = new String[] { KEY_ROWID, KEY_WORD, KEY_MEANING };
// set a cursor to read the info
Cursor c = myDatabase.query(DATABASE_TABLE, columns, null, null, null,
null, null);
int result = 0;
for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()) {
result++;
}
return result;
}
Thanks for all the help also
*final edit: I figured it out a little differently than what was told to me; I kept count in a shared integer value which would reset if more than the total amount of rows, and did a loop in the database class itself using c.moveToNext() instead of the shared integer as so:
public String getWord(long l) {
String[] columns = new String[] { KEY_ROWID, KEY_WORD, KEY_MEANING };
Cursor c = myDatabase.query(DATABASE_TABLE, columns, KEY_ROWID, null, null, null, null);
String result;
c.moveToFirst();
for(int i=1; i<=l; i++){
if(c.isLast()){
c.moveToFirst();
result = c.getString(1);
return result;
}
c.moveToNext();
}
result = c.getString(1);
return result;
}
The way SQL works an autoincrementing ID will be consistent after rows are deleted, so the only ways of making the IDs sequential again is to move entries manually around or copy the rows, drop the table, create a new one and inserting the rows again.
You could however just use the LIMIT clause in your SQL query to tell which and how many rows you want to fetch. To be specific you could use a query similar to this:
SELECT * FROM `your_table` LIMIT first_row_to_fetch, number_of_rows_to_fetch
for instance, if you want 10 rows, starting with the 17th (independant of IDs), you would use
SELECT * FROM `your_table` LIMIT 17, 10
EDIT:
Given your own edit you don't execute your own query string, so in your case you should use the query() method with an added attribute for LIMIT/OFFSET (see the docs).
This overload of the query() method you 're already using takes a string that should contain a number (which is the limit for the query) or two numbers separated by a comma (which is first the offset and then the limit for the query). In effect this means that your
Cursor c = myDatabase.query(DATABASE_TABLE, columns, null, null, null, null, null);
becomes
Cursor c = myDatabase.query(DATABASE_TABLE, columns, null, null, null, null, null, "offset,limit");
where offset and limit works like my first example above.
Also, you might want to put something in the orderBy attribute (the one just before the limit attribute), so you know your result comes out the same way every time. I would recommend using the KEY_ROWID for this, resulting in:
Cursor c = myDatabase.query(DATABASE_TABLE, columns, null, null, null, null, KEY_ROWID, "offset,limit");
这篇关于删除后，Android的SQLite变化的rowid的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
