*android SQLite Database Usage

Sqliteopenhelper class

Use the SQLiteOpenHelper and methods in the class getWritableDatabase() getReadableDatabase() to get a reference to the database.

To enable the management of database versions, the SQLiteOpenHelper class provides two important methods, namely onCreate() onUpgrade() , the first to generate a database table when using the software, and the database table structure to be updated when the software is upgraded.

When calling Sqliteopenhelper's Getwritabledatabase () or Getreadabledatabase () method to get the Sqlitedatabase instance used to manipulate the database, if the database does not exist, The Android system automatically generates a database and then calls the OnCreate () method.

The OnCreate () method is called only when the database is first generated, and in the OnCreate () method, the database table structure can be generated and the initialization data used by some applications to be added.

The Onupgrade () method is called when the version of the database changes, and the version number is usually changed when the software is upgraded, and the version of the database is controlled by the programmer.

Assuming that the database version is 1, due to business changes, modify the database table structure, it is necessary to upgrade the software, upgrade the software to update the database table structure of the user's phone, in order to achieve this goal, the original database version can be set to 2, and in Onupgrade () method to implement the table structure update.

When the version of the software upgrade more than the number, then in the Onupgrade () method can be based on the original number and the target version number to judge, and then make the corresponding table structure and data update.

Sqlitedatabase class

Android provides a SQLiteDatabase class named ( SQLiteOpenHelper and methods in the class that getWritableDatabase() getReadableDatabase() return objects of this class).

　　SQLiteDatabaseThe class encapsulates some APIs that manipulate the database, which you can use to complete the Add (Create), query (Retrieve), update, and delete operations of the data (these operations are referred to as CRUD).

Sqlitedatabase's study should focus on mastering the Execsql () and Rawquery () methods.

The Execsql () method can perform SQL statements with change behavior such as INSERT, delete, update, and create table;

The Rawquery () method is used to execute a SELECT statement.

Program examples

The first is a Databasehelper class, which inherits the Sqliteopenhelper and implements the OnCreate and Onupgrade methods.

Although the name and version of the database are passed in to the constructor of the class, the database is actually created when the getwritabledatabase () or Getreadabledatabase () method of the class is first invoked.

After the first creation, the OnCreate method is called (we created the data table here), and then the OnCreate is no longer called.

Databasehelper class:

Package Com.example.hellodatabases;import Android.content.context;import Android.database.DatabaseErrorHandler; Import Android.database.sqlite.sqlitedatabase;import Android.database.sqlite.sqlitedatabase.cursorfactory;import Android.database.sqlite.sqliteopenhelper;import android.util.log;//Reference: http://blog.csdn.net/liuhe688/article/ Details/6715983public class Databasehelper extends sqliteopenhelper//inherit Sqliteopenhelper class {//database version number private static    final int database_version = 1;    Database name private static final String database_name = "TESTDB.DB";    Data table name, a database can have more than one table (although only one table is established in this example) public static final String table_name = "persontable";            constructor, call the constructor of the parent class Sqliteopenhelper public databasehelper (context context, String name, Cursorfactory factory,    int version, Databaseerrorhandler ErrorHandler) {Super (context, name, Factory, version, ErrorHandler); } public Databasehelper (context context, String name, Cursorfactory factory, int version)   {Super (context, name, Factory, version); Sqliteopenhelper constructor Parameters://Context: Contextual environment//Name: Database name//factory: Cursor factory (optional)//version: Database module        Type version number} public Databasehelper (context context) {Super (context, database_name, NULL, database_version); The database is actually created when the getwritabledatabase () or Getreadabledatabase () method is called LOG.D (Appconstants.log_tag, "Databasehelper Cons        Tructor ");    Cursorfactory is set to NULL, using the system default factory class}//Inheriting the Sqliteopenhelper class, there are three methods that must be covered: onCreate (), Onupgrade (), OnOpen () @Override public void OnCreate (Sqlitedatabase db) {//Call time: The first time the database is created OnCreate () method is called//OnCreate method has a Sqlitedat Abase the object as a parameter, populate the table and initialize the data as needed//This method mainly completes the database operation after creating the database Log.d (Appconstants.log_tag, "Databasehelper oncreat        E ");        Build the SQL statement that created the table (you can paste it from the SQLite expert tool's DDL and add it to StringBuffer) stringbuffer sbuffer = new StringBuffer ();        Sbuffer.append ("CREATE TABLE [" + table_name + "] ("); SbuFfer.append ("[_id] INTEGER not NULL PRIMARY KEY autoincrement,");        Sbuffer.append ("[Name] TEXT,");        Sbuffer.append ("[age] INTEGER,");        Sbuffer.append ("[INFO] TEXT)");        Executes the SQL statement that created the table Db.execsql (sbuffer.tostring ()); Even if the program is modified to run again, as long as the database has been created, it will no longer enter this OnCreate method} @Override public void Onupgrade (sqlitedatabase db, int oldversion, int newversion) {//Call time: If the database_version value is changed to another number, the system discovers that the existing database version is different, the three parameters of the Onupgrade//Onupgrade method are called, a SQ Litedatabase object, an old version number and a new version number//so that a database can be transformed from the old model to the new model//This method mainly completes the operation of changing the database version log.d (APPCONSTANTS.L        Og_tag, "Databasehelper onupgrade");        Db.execsql ("DROP TABLE IF EXISTS" + table_name);        OnCreate (DB);    This is simply to say, by checking the constant value to determine how to delete the old table when upgrading, and then call OnCreate to create a new table//generally in the actual project is not to do so, the correct way is to update the data table structure, but also to consider the user data stored in the database is not lost}        @Override public void OnOpen (Sqlitedatabase db) {Super.onopen (db); Each time the database is opened, the LOG.D is executed first (AppConstants.LOG_tag, "Databasehelper onOpen"); }}

Define a person class as test data:

Package Com.example.hellodatabases;public class person{public    int _id;    public String name;    public int age;    public String info;    Public person ()    {    } public person    (string name, int age, string info)    {        this.name = name;        This.age = age;        This.info = info;    }}

Then there is a management class that holds the database objects and encapsulates the various operations:

Package Com.example.hellodatabases;import Java.util.arraylist;import Java.util.list;import Android.content.contentvalues;import Android.content.context;import Android.database.cursor;import Android.database.sqlite.sqlitedatabase;import android.util.log;//Reference: http://blog.csdn.net/liuhe688/article/    Details/6715983public class dbmanager{Private Databasehelper helper;    Private Sqlitedatabase DB;        Public DBManager (Context context) {LOG.D (Appconstants.log_tag, "DBManager-to-Constructor");        Helper = new Databasehelper (context);        Because Getwritabledatabase internally called Mcontext.openorcreatedatabase (mname, 0,//mfactory);    So to ensure that the context is initialized, we can place the instantiation Dbmanager step in the activity's oncreate db = Helper.getwritabledatabase (); }/** * Add persons * * @param persons */public void Add (list<person> persons) {L        OG.D (Appconstants.log_tag, "DBManager--and add"); Use transaction processing to ensure data integrity Db.begintransaction (); Start a transaction        try {for (person person:persons) {db.execsql ("INSERT into" + Data                        Basehelper.table_name + "VALUES (null,?,?,?)", new object[] {person.name,                Person.age, person.info}); Execsql () method with two parameters, with placeholder parameters?            , the parameter value is placed in the back, the order corresponds to//a parameter of the Execsql () method, the user to enter special characters need to escape//use placeholders to effectively distinguish this situation} Db.settransactionsuccessful (); Set transaction completed successfully} finally {db.endtransaction ();//End Transaction}}/** * Update per Son's age * * @param person */public void updateage (person person) {LOG.D (Appconstants.log_tag        , "DBManager--updateage");        Contentvalues CV = new Contentvalues ();        Cv.put ("Age", person.age);    Db.update (Databasehelper.table_name, CV, "NAME =?", new string[] {person.name});   }/** * Delete old person * * @param person  */public void Deleteoldperson (person person) {LOG.D (Appconstants.log_tag, "DBManager--deleteoldpers        On ");    Db.delete (Databasehelper.table_name, "Age >=?", new string[] {string.valueof (person.age)}); }/** * Query All persons, return list * * @return list<person> */Public list<person>        Query () {LOG.D (Appconstants.log_tag, "DBManager-to-query");        arraylist<person> persons = new arraylist<person> ();        Cursor C = querythecursor ();            while (C.movetonext ()) {Person person = new person ();            person._id = C.getint (C.getcolumnindex ("_id"));            Person.name = c.getstring (C.getcolumnindex ("name"));            Person.age = C.getint (C.getcolumnindex ("Age"));            Person.info = c.getstring (C.getcolumnindex ("info"));        Persons.add (person);        } c.close ();    return persons; }/** * Query All persons, return CURSOR * * @return CURSOR * * Public cursor querythecursor () {LOG.D (Appconstants.log_tag        , "DBManager--querythecursor");        Cursor C = Db.rawquery ("SELECT * from" + databasehelper.table_name, NULL);    return C; }/** * Close database */public void Closedb () {LOG.D (Appconstants.log_tag, "DBManager--Cl        Osedb ");    Release database resource Db.close (); }}

The main activity:

Package Com.example.hellodatabases;import Java.util.arraylist;import Java.util.hashmap;import java.util.List; Import Java.util.map;import Android.app.activity;import Android.database.cursor;import Android.database.cursorwrapper;import Android.os.bundle;import Android.view.menu;import Android.view.View;import Android.widget.listview;import android.widget.simpleadapter;import android.widget.simplecursoradapter;//Reference: http ://blog.csdn.net/liuhe688/article/details/6715983public class Hellodbactivity extends activity{private DBManager    DbManager;    Private ListView ListView;        @Override protected void OnCreate (Bundle savedinstancestate) {super.oncreate (savedinstancestate);        Setcontentview (r.layout.activity_hello_db);        ListView = (ListView) Findviewbyid (R.id.listview);    Initialize DbManager DbManager = new DbManager (this); } @Override Public boolean Oncreateoptionsmenu (Menu menu) {//Inflate the menu, this adds items to the AC tion Bar IF it is present.        Getmenuinflater (). Inflate (r.menu.hello_db, menu);    return true;        } @Override protected void OnDestroy () {Super.ondestroy (); Dbmanager.closedb ();//Release database resource} public void Add (view view) {arraylist<person> persons = new Arrayl        Ist<person> ();        Person Person1 = new Person ("Ella", "lively Girl");        Person Person2 = new Person ("Jenny", "Beautiful Girl");        Person Person3 = new Person ("Jessica", "Sexy Girl");        Person person4 = new Person ("Kelly", and "Hot Baby");        Person person5 = new Person ("Jane", "a Pretty Woman");        Persons.add (Person1);        Persons.add (Person2);        Persons.add (Person3);        Persons.add (PERSON4);        Persons.add (PERSON5);    Dbmanager.add (persons); public void Update (view view) {//change the age of Jane to 30 (note that the value in the database is changed to be queried to refresh the results displayed in the listview) person person =        New Person ();        Person.name = "Jane"; PersoN.age = 30;    Dbmanager.updateage (person); public void Delete (view view) {//delete all people over 30 years of age (this is done after the update, Jane will be deleted (because her age is changed to 30))//the same query to view the change knot        person person = new person ();        Person.age = 30;    Dbmanager.deleteoldperson (person);        public void query (view view) {list<person> persons = Dbmanager.query ();        arraylist<map<string, string>> list = new arraylist<map<string, string>> ();             for (person person:persons) {hashmap<string, string> map = new hashmap<string, string> ();            Map.put ("name", Person.name);            Map.put ("info", Person.age + "years old," + person.info);        List.add (map); } simpleadapter adapter = new Simpleadapter (this, list, Android. R.layout.simple_list_item_2, new string[] {"Name", "info"}, new int[] {Android. R.id.text1, Android. R.id.text2});    Listview.setadapter (adapter); } @SuppressWarnings ("deprecation") public void querythecursor (view view) {Cursor c = Dbmanager.querythecu        Rsor (); Startmanagingcursor (c); Entrusted to activity to manage the life cycle of the cursor based on its life cycle cursorwrapper Cursorwrapper = new Cursorwrapper (c) {@Overr IDE public String getString (int columnindex) {//will be preceded by the introduction of age if (Getcolu                    Mnname (columnindex). Equals ("info")) {int age = GetInt (Getcolumnindex ("Age"));                Return age + ' years old, ' + super.getstring (columnindex);            } return Super.getstring (ColumnIndex);        }        }; Make sure that there are "_id" columns in the query results simplecursoradapter adapter = new Simplecursoradapter (this, Android.                        R.layout.simple_list_item_2, Cursorwrapper, new string[] {"Name", "info"}, new int[] { Android. R.id.text1, andRoid.        R.ID.TEXT2});        ListView ListView = (ListView) Findviewbyid (R.id.listview);    Listview.setadapter (adapter); }}

Another, attached layout and other:

<?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 "&G    T <button android:layout_width= "fill_parent" android:layout_height= "Wrap_content" android:onClick= "a DD "android:text=" add "/> <button android:layout_width=" fill_parent "android:layout_height=" Wrap_content "android:onclick=" Update "android:text=" Update "/> <button android:layout_width = "Fill_parent" android:layout_height= "wrap_content" android:onclick= "delete" android:text= "delete"/&    Gt <button android:layout_width= "fill_parent" android:layout_height= "wrap_content" android:onClick= "q Uery "android:text=" Query "/> <button android:layout_width=" Fill_parent "Android:layout_heig     ht= "Wrap_content"   android:onclick= "Querythecursor" android:text= "Querythecursor"/> <listview android:id= "@+id/lis TView "android:layout_width=" fill_parent "android:layout_height=" Wrap_content "/></linearlayout>

Package Com.example.hellodatabases;public class appconstants{public    static final String log_tag= "Hello DB";}

Resources

Official website training:saving Data in SQL Databases

Http://developer.android.com/training/basics/data-storage/databases.html

A detailed description of the SQLite app in Android:

http://blog.csdn.net/liuhe688/article/details/6715983

Introduction to the Cursor class:

Http://www.cnblogs.com/TerryBlog/archive/2010/07/05/1771459.html

Android Small Project--sqlite using the Dharma (source):

Http://www.cnblogs.com/TerryBlog/archive/2010/06/12/1757166.html

*android SQLite Database Usage