获取SQLite数据库的下一个AUTO_INCREMENT值

在Android的API中使用典型的SQLiteDatabase对象,我可以做些什么来获得特定列(即id )的下一个AUTO_INCREMENT值,而不会影响值本身。 有没有一种方法呢? 或者我应该执行什么查询来获得结果。 请记住,SQLiteDatabase.query()返回一个Cursor对象,所以我不太清楚如何直接处理,如果我只是想从中获得一个值。

你是对的。 第一个答案(仍然在下面)只能在没有AUTOINCREMENT的情况下工作。 使用AUTOINCREMENT,这些值被存储在一个单独的表中并用于增量。 以下是查找值的示例:

 public void printAutoIncrements(){ String query = "SELECT * FROM SQLITE_SEQUENCE"; Cursor cursor = mDb.rawQuery(query, null); if (cursor.moveToFirst()){ do{ System.out.println("tableName: " +cursor.getString(cursor.getColumnIndex("name"))); System.out.println("autoInc: " + cursor.getString(cursor.getColumnIndex("seq"))); }while (cursor.moveToNext()); } cursor.close(); } 

请参阅: http : //www.sqlite.org/autoinc.html

第一个答案:

您可以查询_id列的最大值,例如:

 String query = "SELECT MAX(id) AS max_id FROM mytable"; Cursor cursor = db.rawQuery(query, null); int id = 0; if (cursor.moveToFirst()) { do { id = cursor.getInt(0); } while(cursor.moveToNext()); } return id; 

这适用于尚未指定为“INTEGER PRIMARY KEY AUTOINCREMENT”的行标识(所有表都有行标识列)。

这是使用SQLITE获取自动递增PRIMARY KEY上的最后一个ID的最佳方法

String query = "select seq from sqlite_sequence WHERE name = 'Table_Name'"

有关SQLITE_SEQUENCE表的一个重要说法。

文件说

SQLITE_SEQUENCE表是在创build包含AUTOINCREMENT列的普通表时自动创build和初始化的。

所以创build了SQLITE_SEQUENCE表,但不是与包含AUTOINCREMENT列的表关联的行。 该行是使用第一个插入查询(“seq”值为1)创build的。

这意味着在查找特定表的下一个自动增量值之前,您必须至less执行一次插入操作。 例如,可以在创build表之后执行插入和删除虚拟行。

在你使用的SQLiteOpenHelper内部,启动一个事务。 插入一些数据,然后回滚。

这样一来,你就可以得到下一行的id,像这样:

 public long nextId() { long rowId = -1; SQLiteDatabase db = getWritableDatabase(); db.beginTransaction(); try { ContentValues values = new ContentValues(); // fill values ... // insert a valid row into your table rowId = db.insert(TABLE_NAME, null, values); // NOTE: we don't call db.setTransactionSuccessful() // so as to rollback and cancel the last changes } finally { db.endTransaction(); } return rowId; } 

这是我用来获取特定表的下一个AUTOINCREMENT值:

 /** * Query sqlite_sequence table and search for the AUTOINCREMENT value for <code>tableName</code> * @param tableName The table name with which the AUTOINCREMENT value is associated. * * @return The next AUTOINCREMENT value for <code>tableName</code> * If an INSERT call was not previously executed on <code>tableName</code>, the value 1 will * be returned. Otherwise, the returned value will be equal to the value of the current * AUTOINCREMENT + 1. */ private long getNextAutoIncrement(String tableName) { /* * From the docs: * SQLite keeps track of the largest ROWID using an internal table named "sqlite_sequence". * The sqlite_sequence table is created and initialized automatically * whenever a normal table that contains an AUTOINCREMENT column is created. */ String sqliteSequenceTableName = "sqlite_sequence"; /* * Relevant columns to retrieve from <code>sqliteSequenceTableName</code> */ String[] columns = {"name", "seq"}; Cursor cursor = mWritableDatabase.query(sqliteSequenceTableName, columns, null, null, null, null, null); long autoIncrement = 0; /* * We can indeed fail here ( If an insert call was not previously executed on <code>tableName</code>), * but no worries: * <code>autoIncrement + 1</code> will be returned either way. */ if (cursor.getCount() > 0) { int indexName = cursor.getColumnIndex(columns[0]); int indexSeq = cursor.getColumnIndex(columns[1]); while (cursor.moveToNext()) { /* * Are we currently looking at <code>tableName</code> ? */ if (cursor.getString(indexName).equals(tableName)) { /* Great, take the AUTOINCREMENT value and get out */ autoIncrement = cursor.getLong(indexSeq); break; } } } cursor.close(); return autoIncrement + 1; } 

你可以使用cursor.getInt(i); 方法
i这里是id列的索引

 Cursor c = db.rawQuery("Select * From mSignUp", null); String mail = null; try { while (c.moveToNext()) { mail = c.getString(0); String pas = c.getString(1); Toast.makeText(getApplicationContext(), "Name = " + mail + " Pass = " + pas, Toast.LENGTH_SHORT).show(); } }catch (CursorIndexOutOfBoundsException e){ Log.e("OutOfBound", Log.getStackTraceString(e)); } finally { c.close(); } 

是工作。

 public static long getNextId(SQLiteDatabase db, String tableName) { Cursor c = null; long seq = 0; try { String sql = "select seq from sqlite_sequence where name=?"; c = db.rawQuery(sql, new String[] {tableName}); if (c.moveToFirst()) { seq = c.getLong(0); } } finally { if (c != null) { c.close(); } } return seq + 1; }