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部分	描述
table	from  table_name	指定查询的表明
columns	selete column1,column2	指定查询的列名
selection	where column=value	指定where的约束条件
selectionArgs	------	为where中的占位符提供具体的位置
groupby	group by column	指定需要group by的列
having	having column=value	对group by后的结果进一步约束
orderBy	order 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);