Example of adding, deleting, modifying, and querying an Android-SQLite Database

If you don't talk much about it, directly add the code, and the comments in the Code are clear.

Package mars.com; 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. view. view. onclicklistener; import android. widget. button; import android. widget. edittext; import android. widget. textview; public class demosqlactivity extends activity {private editt EXT number; // student ID private edittext name; // name private edittext Major; // professional private button insert; private button Delete; private button modify; private button seek; private textview show; private sqlitedatabase mydb = NULL; // database Private Static string database_name = "studentdatabase. DB "; // database name Private Static string table_name =" student "; // table name Private Static string name =" name "; // name Private Static string Major = "Major"; // professional Private Static string number = "Number "; // student ID Private Static string create_table = "create table" + table_name + "(" + number + "integer primary key," + name + "text," + major + "text) "; @ overridepublic void oncreate (bundle savedinstancestate) {super. oncreate (savedinstancestate); setcontentview (R. layout. main); this. initwidget (); mydb = openorcreatedatabase (database_name, mode_pri Vate, null); // create the data database mydb.exe csql (create_table); // create a table // put some false data contentvalues CV = new contentvalues (); cv. put (name, "Zhang San"); cv. put (number, "111111"); cv. put (Major, "aaaaaa"); mydb. insert (table_name, null, CV); // insert data cv. put (name, "Li Si"); cv. put (number, "222222"); cv. put (Major, "bbbbbb"); mydb. insert (table_name, null, CV); // insert data cv. put (name, "Wang Wu"); cv. put (number, "333333"); cv. put (Major, "cccccc"); mydb. insert (Table_name, null, CV); // insert data this. showdata (); mydb. close ();} private void showdata () {Show. settext ("database content:"); show. append ("\ n name \ t student ID \ t Major"); cursor = mydb. query (table_name, new string [] {number, name, Major}, null, null); // and the obtained 0, 1, 2 For int COUNT = cursor. getcolumncount (); // get the number if (cursor = NULL | count <0) return; If (cursor. movetofirst () {do {// The obtained strin is related to the cursor. G numberstr = cursor. getstring (0); // obtain the student ID string namestr = cursor. getstring (1); // obtain the name string majorstr = cursor. getstring (2); // get professional show. append ("\ n" + namestr + "\ t" + numberstr + "\ t" + majorstr);} while (cursor. movetonext (); }}// initialize the private void initwidget () {Show = (textview) findviewbyid (R. id. textview2); number = (edittext) findviewbyid (R. id. edittext02); name = (edittext) findviewbyid (R. id. editt Ext01); major = (edittext) findviewbyid (R. id. edittext1); insert = (button) findviewbyid (R. id. button1); Delete = (button) findviewbyid (R. id. button2); Modify = (button) findviewbyid (R. id. button3); Seek = (button) findviewbyid (R. id. button4); insert. setonclicklistener (New buttonlistener (); Delete. setonclicklistener (New buttonlistener (); Modify. setonclicklistener (New buttonlistener (); Seek. setonclicklistener (New buttonlistener ();} class buttonlistener implements onclicklistener {public void onclick (view v) {Switch (v. GETID () {case R. id. button1: Try {mydb = openorcreatedatabase (database_name, mode_private, null); // open the database contentvalues CV = new contentvalues (); // contentvalues object cv. put (name, name. gettext (). tostring (); // use a key-value pair to put it into the CV object. put (number, number. gettext (). tostring (); cv. put (major, major. gettext (). t Ostring (); mydb. insert (table_name, null, CV); // insert data showdata (); mydb. close ();} catch (exception e) {e. printstacktrace ();} break; case R. id. button2: Try {mydb = openorcreatedatabase (database_name, mode_private, null); // open the database string whereclause = "number =? "; String [] whereargs = {number. gettext (). tostring ()}; mydb. delete (table_name, whereclause, whereargs); showdata (); mydb. close ();} catch (exception e) {e. printstacktrace ();} break; case R. id. button3: Try {mydb = openorcreatedatabase (database_name, mode_private, null); // open the database contentvalues CV = new contentvalues (); cv. put (name, name. gettext (). tostring (); cv. put (number, number. gettext (). tostring (); cv. put (M Ajor, Major. gettext (). tostring (); string whereclause = "number =? "; String [] whereargs = {number. gettext (). tostring ()}; mydb. update (table_name, CV, whereclause, whereargs); // modify the database showdata (); mydb. close ();} catch (exception e) {e. printstacktrace ();} break; case R. id. button4: Try {mydb = openorcreatedatabase (database_name, mode_private, null); // open the database string selection = "number =? "; String [] selectionargs = {number. gettext (). tostring ()}; cursor = mydb. query (table_name, new string [] {number, name, Major}, selection, selectionargs, null, null); int COUNT = cursor. getcolumncount (); // get the number if (cursor = NULL | count <0) return; If (cursor. movetofirst () {string numberstr = cursor. getstring (0); string namestr = cursor. getstring (1); string majorstr = cursor. getstring (2); number. settext (numberstr); Name. settext (namestr); major. settext (majorstr);} showdata (); mydb. close ();} catch (exception e) {e. printstacktrace () ;}break; default: Break ;}}}}

The main. xml file is as follows:

<? XML version = "1.0" encoding = "UTF-8"?> <Linearlayout xmlns: Android = "http://schemas.android.com/apk/res/android" Android: layout_width = "fill_parent" Android: layout_height = "fill_parent" Android: Orientation = "vertical"> <linearlayout Android: layout_width = "match_parent" Android: layout_height = "wrap_content"> <textview Android: Id = "@ + ID/textview02" Android: layout_width = "wrap_content" Android: layout_height = "wrap_content" Android: text = "student ID"/> <edittext Android: Id = "@ + ID/edittext02" Android: layout_width = "wrap_content" Android: layout_height = "wrap_content" Android: layout_weight = "1" Android: EMS = "10" Android: inputtype = "Number"/> </linearlayout> <linearlayout Android: layout_width = "match_parent" Android: layout_height = "wrap_content"> <textview Android: Id = "@ + ID/textview01" Android: layout_width = "wrap_content" Android: layout_height = "wrap_content" Android: text = "name"/> <edittext Android: Id = "@ + ID/edittext01" Android: layout_width = "wrap_content" Android: layout_height = "wrap_content" Android: layout_weight = "1" Android: EMS = "10"/> </linearlayout> <linearlayout Android: layout_width = "match_parent" Android: layout_height = "wrap_content"> <textview Android: Id = "@ + ID/textview1" Android: layout_width = "wrap_content" Android: layout_height = "wrap_content" Android: TEXT = "professional"/> <edittext Android: Id = "@ + ID/edittext1" Android: layout_width = "wrap_content" Android: layout_height = "wrap_content" Android: layout_weight = "1" Android: EMS = "10"> <requestfocus/> </edittext> </linearlayout> <linearlayout Android: layout_width = "match_parent" Android: layout_height = "wrap_content"> <button Android: Id = "@ + ID/button1" Android: layout_width = "wrap_content" Android: layout_height = "wrap_content" Android: TEXT = "add"/> <button Android: Id = "@ + ID/button2" Android: layout_width = "wrap_content" Android: layout_height = "wrap_content" Android: TEXT = "delete"/> <button Android: Id = "@ + ID/button3" Android: layout_width = "wrap_content" Android: layout_height = "wrap_content" Android: TEXT = "modify"/> <button Android: Id = "@ + ID/button4" Android: layout_width = "wrap_content" Android: layout_height = "wrap_content" Android: TEXT = "View"/> </linearlayout> <textview Android: Id = "@ + ID/textview2" Android: layout_width = "wrap_content" Android: layout_height = "wrap_content" Android: TEXT = "textview"/> </linearlayout>

The image is as follows:

Note: In this program, the primary key is number. Therefore, the primary key cannot be repeated. If it is repeated, an error will be reported. If it is run once on a real machine, it must be deleted once, because a new database version is required for each operation. If the previous database version is not deleted, an error is returned. The solution is to set a version variable. if the version is the same, delete the database and create a new one. The above errors are all about databases. I will not list them one by one. This section describes how to use the SQLite database on Android. Instead of database development.

In my opinion, the convenient database method provided by Android is purely a weakness, because the execsql method in sqlitedatabase can execute arbitrary SQL statements, including statements with placeholders. Because no return value is returned, you must use the rawquery () method to execute a query statement. Write examples as follows:

DB = sqlitedatabase. openorcreatedatabase (this. getfilesdir () + "/My. db3 ", null); // create the table db.exe csql (" create table user (user_id integer primary key, user_name varchar (255), user_pass varchar (255 ))"); // insert data. Of course, you can add or modify db.exe csql ("insert into table name values (null ,?,?) ", New string [] {corresponds to the first item, corresponds to the second item}); // query data cursor = dB. rawquery ("select * from table name", null); int COUNT = cursor. getcolumncount (); // get the number if (cursor = NULL | count <0) return; If (cursor. movetofirst () {string numberstr = cursor. getstring (0); string namestr = cursor. getstring (1); string majorstr = cursor. getstring (2 );}