Operate SQLite databases using specific methods

If the developer is not familiar with SQL syntax, Android sqlitedatabase provides addition, deletion, modification, and query statements to operate the database. Although Android provides these so-called "convenient" methods for operating the database, however, I believe that these methods are purely "chicken ribs". After all, SQL syntax is a basic skill for programmers. However, since Android provides these methods, we will briefly introduce them here.

1. Insert: Method Signature: Long insert (string table, string nullcolumnhack, contentvalues values)

Table: Table name.

Nullcolumnhack: name of the column that forcibly inserts a null value. When values is null or it contains 0 Key-value pairs, it takes effect. It should not be the column name of the primary key column or the column name of the non-empty column.

Values: indicates the data record by a row. Similar to a map set, contentvalues stores key-value pairs and keys are the column names of Data columns.

2. Update: Method Signature: int Update (string table, contentvalues values, string whereclause, string [] whereargs)

Table: Table name.

Values: the data to be updated.

Whereclause: condition.

Whereargs: input parameters for the whereclause clause. That is, it is used to replace the placeholder content.

The returned integer is the number of records affected by the update statement.

3. Delete: Method Signature: int Delete (string table, string whereclause, string [] whereargs)

Table: Table Name

Whereclause: condition. Records that meet this condition will be deleted.

Whereargs: Used to input parameters for the whereclause clause. Replace placeholders.

The returned integer is the number of records affected by the delete statement.

4. Query: Method Signature: cursor query (string table, string [] columns, string selection, string [] selectionargs, string groupby, string having, string orderby)

Table: Table Name

Columns: name of the column to be queried

Selection: the query Condition Clause is equivalent to the part after the where keyword.

Selectionargs: input parameter for selection, replacing the placeholder.

Groupby: controls the group, which is equivalent to the part after the SELECT statement group.

Having: used to filter groups, which is equivalent to the part after the SELECT statement having.

Orderby: sort. This is equivalent to the part after the SELECT statement 'ORDER '.

The following code uses a simple example to demonstrate their usage:

Activity:

Package COM. lovo. activity; import android. app. activity; import android. content. contentvalues; import android. database. cursor; import android. database. SQLite. sqlitedatabase; import android. OS. bundle; import android. view. view; import android. widget. textview; import COM. lovo. dao. dbutil; import COM. lovo. databasetest. r; public class databasetestactivity extends activity {private textview show; private sqlitedataba Se dB; @ overrideprotected void oncreate (bundle savedinstancestate) {super. oncreate (savedinstancestate); setcontentview (R. layout. main); show = (textview) findviewbyid (R. id. main_ TV _show); DB = dbutil. getinstance (this);} public void click (view v) {Switch (v. GETID () {case R. id. main_btn_insert: // Add the specified data contentvalues insertvalues = new contentvalues (); insertvalues. put ("s_name", "Li Si"); insertvalues. put ("s_age ", 23); insertvalues. put ("s_sex", "male"); dB. insert ("t_stu", null, insertvalues); break; case R. id. main_btn_delete: // Delete the data dB according to the specified conditions. delete ("t_stu", "s_name like? ", New string [] {" Li _ "}); break; case R. id. main_btn_update: // modify the data contentvalues updatevalues = new contentvalues (); updatevalues. put ("s_name", "new name"); dB. update ("t_stu", updatevalues, "_ id =? ", New string [] {" 2 "}); break; case R. id. main_btn_find: // query all data cursor = dB. query ("t_stu", null, null); // query by specified conditions // cursor = dB. query ("t_stu", null, "s_name like? ", New // string [] {" Zhang % "}, null); stringbuffer sb = new stringbuffer (); While (cursor. movetonext () {int id = cursor. getint (cursor. getcolumnindex ("_ id"); string name = cursor. getstring (cursor. getcolumnindex ("s_name"); string sex = cursor. getstring (cursor. getcolumnindex ("s_sex"); int age = cursor. getint (cursor. getcolumnindex ("s_age"); sb. append (ID + "" + name + "" + sex + "" + age + "\ n");} Show. settext (sb. tostring (); break ;}}}

Sqliteopenhelper subclass (dbutil ):

Package COM. lovo. dao; import android. content. context; import android. database. SQLite. sqlitedatabase; import android. database. SQLite. sqlitedatabase. cursorfactory; import android. database. SQLite. sqliteopenhelper; public class dbutil extends sqliteopenhelper {Private Static dbutil; private dbutil (context, string name, cursorfactory factory, int version) {super (context, name, factory, version);} public static sqlitedatabase getinstance (context) {If (dbutil = NULL) {// specify the database name as student, which must be modified here. The default factory is used here; the specified version is 1 dbutil = new dbutil (context, "student", null, 1);} return dbutil. getreadabledatabase () ;}@ overridepublic void oncreate (sqlitedatabase dB) {try mongodb.exe csql ("create table t_stu (_ id integer primary key, s_name text, s_age integer, s_sex text) ");} catch (exception e) {e. printstacktrace () ;}@ overridepublic void onupgrade (sqlitedatabase dB, int oldversion, int newversion) {system. out. println ("----- onupgrade called -----" + oldversion + "--->" + newversion );}}

Layout XML:

<Linearlayout xmlns: Android = "http://schemas.android.com/apk/res/android" Android: layout_width = "match_parent" Android: layout_height = "match_parent" Android: Orientation = "vertical"> <button Android: id = "@ + ID/main_btn_insert" Android: layout_width = "wrap_content" Android: layout_height = "wrap_content" Android: onclick = "click" Android: TEXT = "add"/> <button Android: Id = "@ + ID/main_btn_delete" Android: layout_width = "wrap_content" Android: layout_height = "wrap_content" Android: onclick = "click" Android: text = "delete"/> <button Android: Id = "@ + ID/main_btn_update" Android: layout_width = "wrap_content" Android: layout_height = "wrap_content" Android: onclick = "click" Android: text = "modify"/> <button Android: Id = "@ + ID/main_btn_find" Android: layout_width = "wrap_content" Android: layout_height = "wrap_content" Android: onclick = "click" Android: text = ""/> <textview Android: id = "@ + ID/main_ TV _show" Android: layout_width = "wrap_content" Android: layout_height = "wrap_content"/> </linearlayout>