Android学习笔记九：SQLiteDatabase中query、insert、update、delete方法参数说明

1、SQLiteDataBase对象的query()接口：

public Cursor  query  (String table, String[] columns, String selection, String[] selectionArgs,

 　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　String groupBy, String having,String orderBy,String limit)

Query the given table, returning a Cursor over the result set.

Parameters

table	The table name to compile the query against.(要查询的表名.)
columns	A list of which columns to return. Passing null will return all columns, which is discouraged to prevent reading data from storage that isn't going to be used.(想要显示的列，若为空则返回所有列，不建议设置为空，如果不是返回所有列)
selection	A filter declaring which rows to return, formatted as an SQL WHERE clause (excluding the WHERE itself). Passing null will return all rows for the given table.(where子句，声明要返回的行的要求，如果为空则返回表的所有行。)
selectionArgs	You may include ?s in selection, which will be replaced by the values from selectionArgs, in order that they appear in the selection. The values will be bound as Strings.( where子句对应的条件值)
groupBy	A filter declaring how to group rows, formatted as an SQL GROUP BY clause (excluding the GROUP BY itself). Passing null will cause the rows to not be grouped.(分组方式，若为空则不分组.)
having	A filter declare which row groups to include in the cursor, if row grouping is being used, formatted as an SQL HAVING clause (excluding the HAVING itself). Passing null will cause all row groups to be included, and is required when row grouping is not being used.(having条件，若为空则返回全部（不建议）)
orderBy	How to order the rows, formatted as an SQL ORDER BY clause (excluding the ORDER BY itself). Passing null will use the default sort order, which may be unordered.(排序方式，为空则为默认排序方式)
limit	Limits the number of rows returned by the query, formatted as LIMIT clause. Passing null denotes no LIMIT clause.(限制返回的记录的条数，为空则不限制)

Returns

• A Cursor object, which is positioned before the first entry. Note that Cursors are not synchronized, see the documentation for more details.

示例：

ContentValues cv = new ContentValues();

String[] args = {String.valueOf("a")};

query("user", new String[] { "username","password" },"username=?", args, null,null, null, null);

2、SQLiteDataBase对象的insert()接口：

public long insert (String table, String nullColumnHack, ContentValues values)

Convenience method for inserting a row into the database.

Parameters

table	the table to insert the row into(要插入数据的表的名称)
nullColumnHack	optional; may be null. SQL doesn't allow inserting a completely empty row without naming at least one column name. If your provided valuesis empty, no column names are known and an empty row can't be inserted. If not set to null, the nullColumnHack parameter provides the name of nullable column name to explicitly insert a NULL into in the case where your values is empty.( 当values参数为空或者里面没有内容的时候，我们insert是会失败的（底层数据库不允许插入一个空行），为了防止这种情况，我们要在这里指定一个 列名，到时候如果发现将要插入的行为空行时，就会将你指定的这个列名的值设为null，然后再向数据库中插入。)
values	this map contains the initial column values for the row. The keys should be the column names and the values the column values(一个ContentValues对象，类似一个map.通过键值对的形式存储值。)

Returns

• the row ID of the newly inserted row, or -1 if an error occurred

示例：

ContentValues cv = new ContentValues();

cv.put( "username" ,  "a" );

cv.put( "password" ,  "b" );

insert("user",  null , cv);

 

 

3、SQLiteDataBase对象的update()接口：

public int update (String table, ContentValues values, String whereClause, String[] whereArgs)

Convenience method for updating rows in the database.

Parameters

table	the table to update in(要更新的表名)
values	a map from column names to new column values. null is a valid value that will be translated to NULL.(一个ContentValues对象，类似一个map.通过键值对的形式存储值。)
whereClause whereArgs	the optional WHERE clause to apply when updating. Passing null will update all rows.(可选的where语句) the group of args to deal with(whereClause语句中表达式的？占位参数列表)

Returns

• the number of rows affected

ContentValues cv = new ContentValues();

cv.put( "username" ,  "c" );

cv.put( "password" ,  "d" );

String[] args = {String.valueOf( "a" )};

update("user", cv,  "username=?" ,args)

 

 

4、SQLiteDataBase对象的delete()接口：

public int delete (String table, String whereClause, String[] whereArgs)

Convenience method for deleting rows in the database.

Parameters

table	the table to delete from
whereClause whereArgs	the optional WHERE clause to apply when deleting. Passing null will delete all rows.(可选的where语句) the optional WHERE clause to apply when updating. Passing null will update all rows.(whereClause语句中表达式的？占位参数列表)

Returns

• the number of rows affected if a whereClause is passed in, 0 otherwise. To remove all rows and get a count pass "1" as the whereClause.

示例：

ContentValues cv = new ContentValues();

String[] args = {String.valueOf( "c" )};

delete("user",  "username=?" , args);










用SQLiteDatabase类的execSQL和rawQuery方法操作数据库


1、 execSQL

public void my_SQL_ADD_Column_SlaveConfigTable(String   ip,long port){

    if(null == ip){
        return;
    }

    String    insert_table_slave_config_name_aql=
                    "insert into "+
                    table_slave_config_name+
                    " ( IP , PORT ) values ( "+
                    "'"+ip.trim()+"'" +
                    " , "+ port+" )";
    SQLiteDatabase db = null;
    db = this.getWritableDatabase();//此this是继承SQLiteOpenHelper类得到的
    if(db != null){
        db.execSQL(insert_table_slave_config_name_aql);
        db.close();
    }
}

2、 rawQuery()用于执行select语句

SQLiteDatabase的rawQuery()用于执行select语句，使用例子如下：

<a target=_blank href="http://www.iteedu.com/handset/android/sqlitediary/SQLiteDatabase.php" style="color: rgb(51, 102, 153); text-decoration: none;">SQLiteDatabase</a> db= ....;
<a target=_blank href="http://www.iteedu.com/handset/android/sqlitediary/Cursor.php" style="color: rgb(51, 102, 153); text-decoration: none;">Cursor</a> cursor = db.rawQuery("select * from person",null);
...
cursor.close();
db.close();

rawQuery()方法的第一个参数为select语句；第二个参数为select语句中占位符参数的值，如果select语句没有使用占位符，该参数可以设置为null。带占位符参数的select语句使用例子如下：

Cursor c = db.rawQuery("SELECT * FROM table WHERE android123=?", args); 执行本地SQL语句查询

返回查询结果例子：

1.     //从数据库中查询数据  
2.     public String queryData(MySQLiteHelper myHelper){  
3.         String result = "";  
4.         //获得数据库对象  
5.         SQLiteDatabase db = myHelper.getReadableDatabase();  
6.         //查询表中的数据  
7.         Cursor cursor = db.query("hero_info", null, null, null, null, null, "id asc");  
8.         //获取name列的索引  
9.         int nameIndex = cursor.getColumnIndex("name");  
10.         //获取level列的索引  
11.         int levelIndex = cursor.getColumnIndex("level");  
12.         for (cursor.moveToFirst();!(cursor.isAfterLast());cursor.moveToNext()) {  
13.             result = result + cursor.getString(nameIndex)+ "\t\t";  
14.             result = result + cursor.getInt(levelIndex)+"       \n";  
15.         }  
16.         cursor.close();//关闭结果集  
17.         db.close();//关闭数据库对象  
18.         return result;  
19.     }

查询表中的某条数据
在Android中查询数据是通过Cursor类来实现的，当我们使用SQLiteDatabase.query()方法时，会得到一个Cursor对象，Cursor指向的就是每一条数据。它提供了很多有关查询的方法，具体方法如下：

方法	说明
move	以当前的位置为参考，将Cursor移动到指定的位置，成功返回true, 失败返回false
moveToPosition	将Cursor移动到指定的位置，成功返回true,失败返回false
moveToNext	将Cursor向前移动一个位置，成功返回true,失败返回false
moveToLast	将Cursor向后移动一个位置，成功返回true,失败返回 false。
movetoFirst	将Cursor移动到第一行，成功返回true,失败返回false
isBeforeFirst	返回Cursor是否指向第一项数据之前
isAfterLast	返回Cursor是否指向最后一项数据之后
isClosed	返回Cursor是否关闭
isFirst	返回Cursor是否指向第一项数据

isLast	返回Cursor是否指向最后一项数据
isNull	返回指定位置的值是否为null
getCount	返回总的数据项数
getInt	返回当前行中指定的索引数据

下面我们就是用Cursor来查询数据库中的数据，具体代码如下：

1 2 3 4 5 6 7 8 9

Cursor cur = mSQLiteDatabase.rawQuery( "SELECT * FROM table" , null ); if ( cur != null ){     if ( cur.moveToFirst() ){         do {                    int numColumn = cur.getColumnIndex( "num" );                    int num            = cur.getInt(numColumn);              } while ( cur.moveToNext());        }   }

使用SQLiteDatabase数据库要及时关闭(close), 否则可能会抛出SQLiteException异常。