SQL Learning Notes database Special (ii): Android under SQL database development

In Android development, the database is indispensable. In Android development, a small embedded database is used, sqllite. Today will write two articles about the development of the database in the Android development environment, the first one introduces the use of Android's own API to achieve database use and management, as well as additions and deletions, database upgrades related operations.

First, the database Generation 1, javabean file preparationHere is an example of a person class that describes

Package Com.example.freedomsql.bean;import java.io.serializable;/** * @ClassName: Person * @author Victor_freedom ([ Email protected]) * @createddate 2015-1-10 PM 4:22:09 * @Description: TODO */public class Person implements Serializable {p rivate int id;private string Name;private string number;//private string nickname;public person (int ID, string name, Stri ng number) {super (); this.id = Id;this.name = Name;this.number = number;//this.nickname = nickname;}}

2, the writing of database classesTo implement the use of a database, you need to create a new class and inherit the Sqliteopenhelper class. Then implement a construction method, see the code for details

Package Com.example.freedomsql.db;import Android.content.context;import android.database.sqlite.SQLiteDatabase; Import android.database.sqlite.sqliteopenhelper;/** * @ClassName: Freedomdb * @author victor_freedom ([email protected] * @createddate 2015-1-10 PM 2:49:15 * @Description: TODO */public class Freedomdb extends Sqliteopenhelper {public Freedo MDB (Context context) {///Parameter: Context, database name, cursor Factory is generally null, database version number, upgrade need to change super (context, "freedom.db", NULL, 1);} /** * This method is triggered the first time the database is created */@Overridepublic void OnCreate (Sqlitedatabase db) {//sql statement for details, refer to the first article in this series Db.execsql ("Create Table person (ID integer PRIMARY key autoincrement,name varchar (), number varchar (20));} /** * This method is triggered when the database version number changes */@Overridepublic void Onupgrade (sqlitedatabase db, int oldversion, int newversion) {//if (oldvers Ion = = 1 && newversion = = 2) {//Db.execsql ("ALTER TABLE person add nickname varchar (20)");/}}}

3, database operation class DAO class writingOnce the database has been created, an action class is required to implement the operations on the database, generally named the DAO class. Please refer to the code for details:

Package Com.example.freedomsql.db.dao;import Java.util.arraylist;import Java.util.list;import Android.content.context;import Android.database.cursor;import Android.database.sqlite.sqlitedatabase;import Com.example.freedomsql.bean.person;import com.example.freedomsql.db.freedomdb;/** * @ClassName: PersonDao * @author Victor_freedom ([email protected]) * @createddate 2015-1-10 PM 2:30:24 * @Description: TODO */public class Persondao { Private Freedomdb Dbhelper;public Persondao (context context) {DBHelper = new Freedomdb (context);} /** * @Title: Add * @Description: Add * @param name * @param number * @throws */public void Add (string name, string number) {Sqlitedatabase db = Dbhelper.getwritabledatabase ();d b.execsql ("INSERT into person (name,number) VALUES (?,?)", New Object[] {name, number});d b.close ();//System api//Contentvalues = values = new Contentvalues ();//Values.put ("name", name); /Values.put ("number", number),//Long id = db.insert (table name), null,values (data));//return ID;} ///**// * @title:add//* @Description: Add method After upgrade//* @param name//* @param number//* @param nickname//* @throws//*///public void Add (string name, string number, string nickname) {//sqlitedatabase db = Dbhelper.getwritabledatabase ();//db.execsql (" Insert into person (name,number,nickname) VALUES (?,?,?) ",//new object[] {name, number, nickname});//db.close ();////System A pi////contentvalues = values = new Contentvalues ()////values.put ("name", name),////values.put ("number", number);////  Long id = db.insert (table name), null,values (data))////return id;////}/** * @Title: Delete * @Description: Delete * @param name * @throws */public void Delete (String name) {Sqlitedatabase db = Dbhelper.getwritabledatabase ();d b.execsql ("Delete from PE Rson where Name=? ", new object[] {name});//System api//int number=db.delete (" person "," Name=? ", New String[]{name});d B.close ();} /** * @Title: Updata * @Description: UPDATE * @param name * @param newnumber * @throws */public void Updata (String name, Strin G newnumber) {Sqlitedatabase db = Dbhelper.getwritabledatabase ();d b.execsql ("Update person set number=?") Where Name=? ", new object[] {newnumber, name});//System api//contentvalues values = new Contentvalues ();//Values.put (" Numbe R ", newnumber);//int num = db.update (" Person ", values," Name=? ", new string[] {name//});d b.close ();  /** * @Title: Find * @Description: Query * @param name * @return * @throws */public boolean find (String name) {Sqlitedatabase db = Dbhelper.getreadabledatabase (); cursor cursor = db.rawquery ("select * from person where name =?", new string[] {name});//system api//CURSOR cursor = Db.quer Y (table name), NULL, "Name=", new//string[] (name), null,null,null); Boolean result = Cursor.movetonext (); Cursor.close () ;d B.close (); return result;} /** * @Title: FindAll * @Description: Enquiry * @return * @throws */public list<person> findAll () {list<person> Pers ONS = new arraylist<person> (); Sqlitedatabase db = Dbhelper.getreadabledatabase (); cursor cursor = db.rawquery ("SELECT * from person", null);//System api//CuRsor cursor= db.query ("person", new//string[]{"name", "id", "number"},null,null,null,null,null); Cursor.movetonext ()) {int id = cursor.getint (cursor.getcolumnindex ("id")); String name = cursor.getstring (Cursor.getcolumnindex ("name")); String number = Cursor.getstring (Cursor.getcolumnindex ("number"));//String nickname = Cursor.getstring (cursor//. Getcolumnindex ("nickname")); Person p = new person (ID, name, number);p Ersons.add (p);} Cursor.close ();d b.close (); Return persons;}}

4, the main activity inside the codeWithin the activity we use the DAO class to manipulate the database.

Package Com.example.freedomsql;import Com.example.freedomsql.db.dao.persondao;import Android.app.Activity;import Android.app.actionbar;import Android.app.fragment;import Android.os.bundle;import Android.view.LayoutInflater; Import Android.view.menu;import android.view.menuitem;import android.view.view;import android.view.ViewGroup; Import Android.os.build;public class Mainactivity extends Activity {@Overrideprotected void OnCreate (Bundle Savedinstancestate) {super.oncreate (savedinstancestate); Setcontentview (R.layout.activity_main); Persondao dao = new Persondao (Getbasecontext ());d Ao.add ("Tom Boss", "");d Ao.add ("Learned", "119");//Dao.add ("Tom boss 1", "110", " Soup boss ")//Dao.add (" Learned 1 "," 119 "," Guo Nu ");}

The only way to do this is to insert data, and interested students can test their own other operations and write other methods in the DAO class for more complex operations. Database content display after code execution
Second, the database upgrade update in the actual development, the database will certainly have upgrade-related operations, but the database upgrade naturally can not let the project before the data cleared, must be retained, so this requires a certain control and processing of the database version number. So we're going to upgrade the database just now. 1, the Person class upgrade, add a nickname field

Package Com.example.freedomsql.bean;import java.io.serializable;/** * @ClassName: Person * @author Victor_freedom ([ Email protected]) * @createddate 2015-1-10 PM 4:22:09 * @Description: TODO */public class Person implements Serializable {p rivate int id;private String name;private string number;private string nickname;public person (int ID, string name, string Number, String nickname) {super (); this.id = Id;this.name = Name;this.number = Number;this.nickname = nickname;}}

2. Changes to the database generation class1. Change the version number to 2

Public Freedomdb (Context context) {/////Parameters are: contexts, database names, cursor factories are generally null, database version numbers, upgrades need to change super (context, "freedom.db", NULL, 2);}

2, in the Onupgrade method of processing, in the actual development, in particular, the version number should be processed logically.

/** * This method is triggered when the database version number changes */@Overridepublic void Onupgrade (sqlitedatabase db, int oldversion, int newversion) {if (oldversion = = 1 && newversion = = 2) {db.execsql ("ALTER TABLE person add nickname varchar (20)");}}

3. Modification of DAO class1, increase the method changes

/** * @Title: Add * @Description: Add method After Upgrade * @param name * @param number * @param nickname * @throws */public void Add (Str ing name, string number, string nickname) {Sqlitedatabase db = Dbhelper.getwritabledatabase ();d b.execsql ("insert into per Son (Name,number,nickname) VALUES (?,?,?) ", new object[] {name, number, nickname});d B.close ();//System api//Contentvalues = V Alues = new Contentvalues ();//Values.put ("name", name),//Values.put ("number", number),//Long id = db.insert (table name), Null,values (data));//return ID;}

2. Changes to the Query method

/** * @Title: FindAll * @Description: Enquiry * @return * @throws */public list<person> findAll () {list<person> Pers ONS = new arraylist<person> (); Sqlitedatabase db = Dbhelper.getreadabledatabase (); cursor cursor = db.rawquery ("SELECT * from person", null);//system api//cursor cursor= db.query ("person", new//string[]{"name "," id "," number "},null,null,null,null,null); while (Cursor.movetonext ()) {int id = cursor.getint ( Cursor.getcolumnindex ("id")); String name = cursor.getstring (Cursor.getcolumnindex ("name")); String number = cursor.getstring (Cursor.getcolumnindex ("number")); String nickname = cursor.getstring (Cursor.getcolumnindex ("nickname")); Person p = new person (ID, name, number,nickname);p Ersons.add (p);} Cursor.close ();d b.close (); return persons;}

4, changes to the main activity

Package Com.example.freedomsql;import Com.example.freedomsql.db.dao.persondao;import Android.app.Activity;import Android.app.actionbar;import Android.app.fragment;import Android.os.bundle;import Android.view.LayoutInflater; Import Android.view.menu;import android.view.menuitem;import android.view.view;import android.view.ViewGroup; Import Android.os.build;public class Mainactivity extends Activity {@Overrideprotected void OnCreate (Bundle Savedinstancestate) {super.oncreate (savedinstancestate); Setcontentview (R.layout.activity_main); Persondao dao = new Persondao (Getbasecontext ());d Ao.add ("Tom boss 1", "110", "Tom Boss");d Ao.add ("Learned 1", "119", "Guo Nu");}

This completes the operation after the database upgrade, we look at the upgrade of the database content from the graph we can see that the previous data is still there, not cleared away, the newly added fields are added, the newly created objects are also created successfully.
In the Android development of the basic operation of the SQLite database, of course, in the actual development, we will not do so, there are many packaged framework for us to use, and the performance of a great optimization, easy to operate. Learning these basic development is to let us know more clearly the operation principle of database bottom. All database operating frameworks are basically based on this set of theories. Understanding these after we go to learn the use of the framework, it will be easy to get started.

SQL Learning Notes database Special (ii): Android under SQL database development