Android数据存储SQLite-使用sql操作数据库

SQLiteDatabase db=new SQLiteDatabase();public static final String CREATE_BOOK = "create table Book ("            + "id integer primary key autoincrement, "            + "author text, "            + "price real, "            + "pages integer, "            + "name text)";public static final String CREATE_CATEGORY = "create table Category ("            + "id integer primary key autoincrement, "            + "category_name text, "            + "category_code integer)";db.execSQL(CREATE_BOOK);db.execSQL(CREATE_CATEGORY);

上面两种是建表用的，下面介绍增删改查的SQL语句用法。

db.execSQL("insert into Book(name,author,pages,price) values(?,?,?,?)",new String [] {"VinciCode","Dan Brown","454","16.96"});db.execSQL("update Book set price=? where name=? ",new String [] {"10.99","VinciCode"});db.execSQL("delete from Book where pages>?",new String [] {"500"});

</pre>查询的时候不能使用execSQL，看源码。<p></p><p><span style="font-size:14px;"></span></p><pre code_snippet_id="1861573" snippet_file_name="blog_20160901_4_9277962" name="code" class="java">/**     * Execute a single SQL statement that is NOT a SELECT     * or any other SQL statement that returns data.     * <p>     * It has no means to return any data (such as the number of affected rows).     * Instead, you're encouraged to use {@link #insert(String, String, ContentValues)},     * {@link #update(String, ContentValues, String, String[])}, et al, when possible.     * </p>     * <p>     * When using {@link #enableWriteAheadLogging()}, journal_mode is     * automatically managed by this class. So, do not set journal_mode     * using "PRAGMA journal_mode'<value>" statement if your app is using     * {@link #enableWriteAheadLogging()}     * </p>     *     * @param sql the SQL statement to be executed. Multiple statements separated by semicolons are     * not supported.     * @throws SQLException if the SQL string is invalid     */    public void execSQL(String sql) throws SQLException {        executeSql(sql, null);    }

这个函数返回值是void，所以它很清楚的说明了不允许使用查询的SQL语句，也不能使用任何有返回数据的语句。

那我们可以采用两种方法来进行查询。

第一种：

使用query，这个函数因为参数比较多，比较麻烦，对于SQL不太熟悉的安卓开发人员可以使用这个。对于SQL只是有了解的不推荐使用。

/**     * Query the given table, returning a {@link Cursor} over the result set.     *     * @param table The table name to compile the query against.     * @param 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.     * @param 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.     * @param 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.     * @param 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.     * @param 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.     * @param 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.     * @return A {@link Cursor} object, which is positioned before the first entry. Note that     * {@link Cursor}s are not synchronized, see the documentation for more details.     * @see Cursor     */    public Cursor query(String table, String[] columns, String selection,            String[] selectionArgs, String groupBy, String having,            String orderBy) {        return query(false, table, columns, selection, selectionArgs, groupBy,                having, orderBy, null /* limit */);    }

query()方法参数对应的SQL部分描述tablefrom  table_name指定查询的表明columnsselete column1,column2指定查询的列名selectionwhere column=value指定where的约束条件selectionArgs------为where中的占位符提供具体的位置groupbygroup by column指定需要group by的列havinghaving column=value对group by后的结果进一步约束orderByorder by conlumn1,conlumn2指定查询结果的排序方式第二种是使用rawQuery()

/**     * Runs the provided SQL and returns a {@link Cursor} over the result set.     *     * @param sql the SQL query. The SQL string must not be ; terminated     * @param selectionArgs You may include ?s in where clause in the query,     *     which will be replaced by the values from selectionArgs. The     *     values will be bound as Strings.     * @return A {@link Cursor} object, which is positioned before the first entry. Note that     * {@link Cursor}s are not synchronized, see the documentation for more details.     */    public Cursor rawQuery(String sql, String[] selectionArgs) {        return rawQueryWithFactory(null, sql, selectionArgs, null, null);    }

这里面允许我们使用SQL语句进行查询。

例如：

db.rawQuery("selete * from Book",null);