SQLiteDatabase

Begin —— 2015年6月22日20:15:53

void android.database.sqlite.SQLiteDatabase.execSQL(String sql, Object[] bindArgs) throws SQLException

 

Execute a single SQLstatement that is NOT a SELECT/INSERT/UPDATE/DELETE.

For INSERTstatements, use any of the following instead.

·        insert(String, String, ContentValues)

·        insertOrThrow(String, String, ContentValues)

·        insertWithOnConflict(String, String, ContentValues,int)

For UPDATEstatements, use any of the following instead.

·        update(String, ContentValues, String, String [])

·        updateWithOnConflict(String, ContentValues, String,String [], int)

For DELETEstatements, use any of the following instead.

·        delete(String, String, String [])

For example, thefollowing are good candidates for using this method:

·        ALTER TABLE

·        CREATE or DROP table/ trigger / view / index / virtual table

·        REINDEX

·        RELEASE

·        SAVEPOINT

·        PRAGMA that returnsno data

When using enableWriteAheadLogging(), journal_mode isautomatically managed by this class. So, do not set journal_mode using"PRAGMA journal_mode'" statement if your app is using enableWriteAheadLogging()

Parameters:

sql the SQL statement tobe executed. Multiple statements separated by semicolons are not supported.

bindArgs only byte[], String,Long and Double are supported in bindArgs.

Throws:

SQLException - if the SQL stringis invalid

流程

打开数据库(没有的话创建)

进行数据的操作

关闭数据库

①创建数据库文件

②在数据库文件中创建表

③向表中插入信息

o    insert(String, String, ContentValues)

 

long android.database.sqlite.SQLiteDatabase.insert(String table, String nullColumnHack, ContentValues values)

 

Convenience(方便)method for inserting a row into the database.

Parameters:

table the table to insertthe row into

nullColumnHack

optional; may be null.可选；允许设为空

SQL doesn't allowinserting a completely empty row without naming at least one column（列）name. If your provided values is empty, no columnnames 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 toexplicitly（明确地） insert a NULL into in the case whereyour values is empty.

values this map containsthe initial column values for the row. The keys should be the column names andthe values the column values ——一个map形式的数据集 map中 key 是表的列名，key对应的值是插入的这一行长，对应这个key的值

【这里面value和nullColumnHack有一定的关系：如果value为null的且nullColumnHack为null，系统会尝试插入一行空值，但这是不被允许的，，，nullColumnHack如果不为null（并且这个值对应的列是可空的）的话就可也避免value为空的情况报错—That all.】

【不管第三个参数是否包含数据，执行Insert()方法必然会添加一条记录，如果第三个参数为空，会添加一条除主键之外其他字段值为Null的记录。Insert()方法内部实际上通过构造insert SQL语句完成数据的添加，Insert()方法的第二个参数用于指定空值字段的名称，相信大家对该参数会感到疑惑，该参数的作用是什么？是这样的：如果第三个参数values 为Null或者元素个数为0，由于Insert()方法要求必须添加一条除了主键之外其它字段为Null值的记录，为了满足SQL语法的需要， insert语句必须给定一个字段名，如：insert into person(name) values(NULL)，倘若不给定字段名， insert语句就成了这样： insert into person() values()，显然这不满足标准SQL的语法】

Returns:

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

 

//=======================================================

   private void insertData()

   {

        // insert(String table, StringnullColumnHack, ContentValues values)

        // 由创建表的语句可以知道所有列都是允许为空的。

        ContentValuescontentValues = new ContentValues();

        contentValues.put("name", "电信");

        contentValues.put("telephone", "10000");

        sqlDB_1.insert("bussness_card", "id", contentValues);//报空指针错误

   }

//============================================================

o    insertOrThrow(String, String, ContentValues)

注释跟上一个函数相同

o    insertWithOnConflict(String, String, ContentValues,int)

 

Generalmethod for inserting a row into the database.

Parameters:

tablethe table to insert the row into

nullColumnHackoptional; may be null.SQL doesn't allow inserting a completely empty row without naming at least onecolumn name. If your provided initialValuesis empty, no column names are known and an empty row can't be inserted. If notset to null, the nullColumnHackparameter provides the name of nullable column name to explicitly insert a NULLinto in the case where your initialValuesis empty.

initialValuesthis map contains the initial column values for the row. The keys should be thecolumn names and the values the column values

conflictAlgorithmfor insert conflict resolver

Returns:

therow ID of the newly inserted row OR the primary key of the existing row if theinput param 'conflictAlgorithm' = CONFLICT_IGNOREOR -1 if any error

 

conflictAlgorithm的值 【除却这个参数的值基本与insert(……) 方法的注释相同】——但是没有明白这个参数的作用——望有心者留言

CONFLICT_NONE  Use the following when no conflict action isspecified.

CONFLICT_ROLLBACK  When a constraint violation occurs, animmediate ROLLBACK occurs, thus ending the current transaction, and the commandaborts with a return code of SQLITE_CONSTRAINT. If no transaction is active(other than the implied transaction that is created on every command) then thisalgorithm works the same as ABORT.

CONFLICT_ABORT  When a constraint violation occurs,noROLLBACK is executed so changes from prior commands within the same transactionare preserved. This is the default behavior.

CONFLICT_FAIL  When a constraint violation occurs, thecommand aborts with a return code SQLITE_CONSTRAINT. But any changes to thedatabase that the command made prior to encountering the constraint violationare preserved and are not backed out.

CONFLICT_IGNORE  When a constraint violation occurs, the onerow that contains the constraint violation is not inserted or changed. But thecommand continues executing normally. Other rows before and after the row thatcontained the constraint violation continue to be inserted or updated normally.No error is returned.

CONFLICT_REPLACE When a UNIQUEconstraint violation occurs, the pre-existing rows that are causing theconstraint violation are removed prior to inserting or updating the currentrow. Thus the insert or update always occurs. The command continues executingnormally. No error is returned. If a NOT NULL constraint violation occurs, theNULL value is replaced by the default value for that column. If the column hasno default value, then the ABORT algorithm is used. If a CHECK constraintviolation occurs then the IGNORE algorithm is used. When this conflictresolution strategy deletes rows in order to satisfy a constraint, it does notinvoke delete triggers on those rows. This behavior might change in a futurerelease.

 

 

o    execSQL(String sql, Object[] bindArgs)

 

很明显三个函数都是

public longinsertWithOnConflict(String table, String nullColumnHack, ContentValuesinitialValues, int conflictAlgorithm)

这一个函数

接下来分析一下它

 

 

public long insert(String table, String nullColumnHack, ContentValues values) {         try {             return insertWithOnConflict(table, nullColumnHack, values, CONFLICT_NONE);         } catch (SQLException e) {             Log.e(TAG, "Error inserting " + values, e);             return -1;         }     }       public long insertOrThrow(String table, String nullColumnHack, ContentValues values)             throws SQLException {         return insertWithOnConflict(table, nullColumnHack, values, CONFLICT_NONE);     }       public long insertWithOnConflict(String table, String nullColumnHack,             ContentValues initialValues, int conflictAlgorithm) {         acquireReference();         try {             StringBuilder sql = new StringBuilder();             sql.append("INSERT");             sql.append(CONFLICT_VALUES[conflictAlgorithm]);             sql.append(" INTO ");             sql.append(table);             sql.append('(');               Object[] bindArgs = null;             int size = (initialValues != null && initialValues.size() > 0)                     ? initialValues.size() : 0;             if (size > 0) {                 bindArgs = new Object[size];                 int i = 0;                 for (String colName : initialValues.keySet()) {                     sql.append((i > 0) ? "," : "");                     sql.append(colName);                     bindArgs[i++] = initialValues.get(colName);                 }                 sql.append(')');                 sql.append(" VALUES (");                 for (i = 0; i < size; i++) {                     sql.append((i > 0) ? ",?" : "?");                 }             } else {                 sql.append(nullColumnHack + ") VALUES (NULL");             }             sql.append(')');               SQLiteStatement statement = new SQLiteStatement(this, sql.toString(), bindArgs);             try {                 return statement.executeInsert();             } finally {                 statement.close();             }         } finally {             releaseReference();         }     }

                           

 

 

以上4个方法

先是：

insert(String, String, ContentValues)

insertOrThrow(String, String, ContentValues)

调用到

insertWithOnConflict(String, String, ContentValues,int)

 

后

insertWithOnConflict(String, String, ContentValues,int)

execSQL(String sql, Object[] bindArgs)

殊途同归调用到

public abstract classSQLiteProgram

此类中的 构造函数

 

———关于插入的方法先探寻到这里

 

④删除表中的信息

①  delete(String, String, String [])

 

Convenience(方便)method for deleting rows in the database.

Parameters:

table the table to deletefrom

whereClause the optional WHEREclause（字句） to apply when deleting. Passingnull will delete all rows.

whereArgs You may include ?sin the where clause, which will be replaced by the values from whereArgs. Thevalues will be bound as Strings.

Returns:

the number of rowsaffected if a whereClause is passed in, 0 otherwise.

如果where语句如果能执行返回受影响的行数，没有受影响的行，返回0.

To remove all rowsand get a count pass "1" as the whereClause.

试验中删除了一个共 41 行的表的所有行——返回41

 

 

看不懂没关系值结贴代码

【SQL 中delete是用来删除行的】

1.  private void delete(SQLiteDatabase db) {   2.      3.     //删除条件  4.     String whereClause = "_id=?";   5.      6.     //删除条件参数  7.     String[] whereArgs = {String.valueOf(2)};   8.      9.     //执行删除  10.    db.delete("stu_table",whereClause,whereArgs);    11. }   第二种方法的代码： 1.  private void delete(SQLiteDatabase db) {   2.      3.     //删除SQL语句  4.     String sql = "delete from stu_table where _id  = 6";   5.      6.     //执行SQL语句  7.     db.execSQL(sql);   8.  }

 

   private void deleteData()

   {

        sqlDB_1.delete("bussness_card", "id=?", new String[]{"5"});

        sqlDB_1.delete("bussness_card", "id=? AND telephone=?", new String[]{"1","10000"});

        //想要删除所有行却不删除表，，还会找到——SQLiteStatement——【突然有搞他一下的冲动】

        sqlDB_1.delete("bussness_card", whereClause, whereArgs);

      }

【让我们来分析下】

sqlDB_1.delete("bussness_card", "id=?", new String[]{"1"});

这句代码调用的方法：

public int delete(String table, String whereClause, String[] whereArgs) {         acquireReference();         try {             SQLiteStatement statement =  new SQLiteStatement(this, "DELETE FROM " + table +                     (!TextUtils.isEmpty(whereClause) ? " WHERE " + whereClause : ""), whereArgs); // new SQLiteStatement (this，”DELETE FROM bussness_card  WHERE id=?”, new String[]{"1"})直接到当前方法外分析这一方法             try {                 return statement.executeUpdateDelete();             } finally {                 statement.close();             }         } finally {             releaseReference();         } } SQLiteStatement(SQLiteDatabase db, String sql, Object[] bindArgs) {         super(db, sql, bindArgs, null);//在此方法外去找他的父类     } //大BOOS SQLiteProgram(SQLiteDatabase db, String sql, Object[] bindArgs, CancellationSignal cancellationSignalForPrepare) { //sql = “”DELETE FROM bussness_card  WHERE id=?”— bindArgs= new String[]{"1"}—cancellationSignalForPrepare =null        mDatabase = db;         mSql = sql.trim();           int n = DatabaseUtils.getSqlStatementType(mSql);         switch (n) {             case DatabaseUtils.STATEMENT_BEGIN:             case DatabaseUtils.STATEMENT_COMMIT:             case DatabaseUtils.STATEMENT_ABORT:                 mReadOnly = false;                 mColumnNames = EMPTY_STRING_ARRAY;                 mNumParameters = 0;                 break;               default:                 boolean assumeReadOnly = (n == DatabaseUtils.STATEMENT_SELECT);                 SQLiteStatementInfo info = new SQLiteStatementInfo();                 db.getThreadSession().prepare(mSql,                         db.getThreadDefaultConnectionFlags(assumeReadOnly),                         cancellationSignalForPrepare, info);                 mReadOnly = info.readOnly;                 mColumnNames = info.columnNames;                 mNumParameters = info.numParameters;                 break;         }           if (bindArgs != null && bindArgs.length > mNumParameters) {             throw new IllegalArgumentException("Too many bind arguments.  "                     + bindArgs.length + " arguments were provided but the statement needs "                     + mNumParameters + " arguments.");         }           if (mNumParameters != 0) {             mBindArgs = new Object[mNumParameters];             if (bindArgs != null) {                 System.arraycopy(bindArgs, 0, mBindArgs, 0, bindArgs.length);             }         } else {             mBindArgs = null;         } }

 

//不行啊，还搞不定

 

           // 删除所有行

           // 可以在不删除表的情况下删除所有的行。这意味着表的结构、属性和索引都是完整的：

           // DELETE FROM table_name

           // 或者：

// DELETE * FROM table_name

 

 

 

实例：

/**        * 删除表中数据：        * execSQL(String ， Object[]);应用失败        */       private void deleteData()       {            // 直接用这个            sqlDB_1.execSQL("DELETE FROM bussness_card WHERE id='46' ;");            //另一个怎么用【就是这么用  在SQLite 语句中允许使用？当做占位符并用 Object[] 数组中的值依次替换{顺序别乱}】            sqlDB_1.execSQL("DELETE FROM bussness_card WHERE id = ? AND telephone = ?;", new Object[]{48,"phone-2"});                                  int colum2 = sqlDB_1.delete("bussness_card", "id=?", new String[]{ "2" });            int colum1 = sqlDB_1.delete("bussness_card", "id=? AND telephone=?", new String[]{ "1", "10000" });            // 想要删除所有行却不删除表，，还会找到——SQLiteStatement——【突然有搞他一下的冲动)——并没有搞明白】            // 删除所有行            // 可以在不删除表的情况下删除所有的行。这意味着表的结构、属性和索引都是完整的：            // DELETE FROM table_name            // 或者：            // DELETE * FROM table_name            //int columAll = sqlDB_1.delete("bussness_card", null, null);// 删除所有行——可行       }

 

⑤修改表中的信息

• • update(String, ContentValues, String, String [])
  • updateWithOnConflict(String, ContentValues, String, String [], int)

 

【BOOS】

 

看完查询就知道了都一样，都是 SQLite 语句中的关键字们。

 

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

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. whereClause the optional WHERE clause to apply when updating. Passing null will update all rows. whereArgs You may include ?s in the where clause, which will be replaced by the values from whereArgs. The values will be bound as Strings. conflictAlgorithm for update conflict resolver Returns: the number of rows affected

 

 

 

 

 

 

 

 

 

 

 

 

 

 

⑥查找表中的信息

直接查找吧

1031 à 1154

1072 à 1154

1111 à 1154

1198 à 1031à1154

1236 à 1031 à1154

呵呵 1154 à 1309

 

1254 à 1309

1271 à 1309

1228 à 1309

关于 query 共有10 个函数，但是这 10 个函数其实是有 9 个都是间接调用 其中的大 BOOS

为什么要这么做？

——猜测，，每个间接调用的函数都有一些小特性，使得调用更简便，

——但肯定是双面的，在简便的同时极有可能失去了某些功能

 

同时参数颇多，已经到了不得不了解一下 SQLite 语法的时候了(其实就是一些关键字，没有什么)。

 

【先看BOOS】

 

Cursor android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(CursorFactory cursorFactory, String sql, String[] selectionArgs, String editTable, CancellationSignal cancellationSignal)   Runs the provided（提供的） SQL and returns a cursor over the result set. 【运行提供的SQL 并且 返回结果集的 cursor。】 Parameters: cursorFactory the cursor factory to use, or null for the default factory 【使用就使用，填 null 系统会使用默认的 factory 。】 sql the SQL query. The SQL string must not be ; terminated 【the SQL query. The SQL string 禁止用 分号 结尾。】 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. 【好像就是说条件语句中使用 ？  当做占位符的地方 都可以通过这个数组中的值进行替换 ， 但是这里面的值都是 String 类型的。—— 在删除语句中试试（在删除语句中成功）】 editTable the name of the first table, which is editable 【第一个可编辑的表的名称。——没有理解（难道是对于多表查询而言？）】 cancellationSignal A signal to cancel the operation in progress, or null if none. If the operation is canceled, then OperationCanceledException will be thrown when the query is executed（执行）.                    【一个取消正在进行的操作的信号，如果没有这么一个信号可以填 null 。……】 Returns: A Cursor object, which is positioned（位置） before the first entry. Note that Cursors are not synchronized（同步的）, see the documentation for more details. 【一个 Cursror 对象，位于首个记录之前。……】

 

 

想要得到查找到的信息这里涉及到一个返回值Curser类型的先去了解下

2015年6月22日22:31:22——今天到这里，规则是一切事物的本源，该睡觉了，这是我应该遵守的规则。——预祝：做个好梦。。。

 

 

【在来小BOOS】

 

public Cursor queryWithFactory(                      CursorFactory cursorFactory,                 boolean distinct,                               String table,                               String[] columns,                 String selection,                               String[] selectionArgs,                               String groupBy,                 String having,                               String orderBy,                               String limit,                               CancellationSignal cancellationSignal)

Query the given URL, returning a Cursor over the result set. Parameters: cursorFactory the cursor factory to use, or null for the default factory ——【还并不知道 cursor工厂是干什么的——先不用就是了】 distinct true if you want each row to be unique, false otherwise. 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. 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. 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. 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. cancellationSignal A signal to cancel the operation in progress, or null if none. If the operation is canceled, then OperationCanceledException will be thrown when the query is executed. Returns: A Cursor object, which is positioned before the first entry. Note that Cursors are not synchronized, see the documentation for more details. See Also: Cursor   【Selection】对返回哪一行的过滤声明，格式化成类似 SQL语句中 WHERE 字句的样式（但是不包括 WHERE 本身）.如果为 null 就返回得到 的 table 的 all rows. 【GROUP BY】【ORDER BY】【HAVING】【LINIT】都是SQL 关键字——【然而 HAVING 和 GROUP BY 并不是很理解啊】     原来这个函数是包含（近乎）所有的 SQL 关键字的函数，他出现的意义就在于，用户你可以没有需要，但是只要你有，用到这个关键字的查询语句，找到这个函数定然没有错； 【猜测其他的方法就是少了一些参数，也就是ＳＱＬ　语句中少了  几个关键字啊】

 

 

 

【小喽啰们】

 

                                                                                                    

一看之后果不其然——查询——告一段落