(Android Review) Sqlite

In fact, this part, in general, mainly write Dbopenhelper class and Persondao class can ....

The dbopenhelper mainly encapsulates the creation of the database, the creation and modification of the table. While Persondao mainly encapsulates DAO operations on tables ....

1, Dbopenhelper

Package Com.example.sqlitetest;import Android.content.context;import android.database.sqlite.SQLiteDatabase; Import Android.database.sqlite.sqliteopenhelper;public class Dbopenhelper extends Sqliteopenhelper {public Dbopenhelper (Context context) {//The parent class does not have a parameterless constructor and must be shown to call the parameter constructor./* * Because the parent class does not have a parameterless constructor, you must explicitly call the parameter constructor * Parameter 1: Context, which is used to determine the directory where the database file is stored. The database that was created exists in the/data/data/application/databases/xxx.db * Parameter 2: Database file name * Parameter 3: The factory that generated the cursor, NULL is to use the default * Parameter 4: Version of the database, starting from 1 */super (Contex T, "njupt.db", null,2);} /** * Generally, the code that creates the table is placed in onCreate ()/@Overridepublic void OnCreate (Sqlitedatabase db) {System.out.println ("----------- >oncreate ");d b.execsql (" CREATE TABLE person (ID INTEGER PRIMARY KEY autoincrement, name VARCHAR (20)) ");//Execute SQL statement, CREATE TABLE }/** * Modify the table's code in OnUpdate () ... */@Overridepublic void Onupgrade (sqlitedatabase db, int oldversion, int newversion) {System . OUT.PRINTLN ("---------->onupdate");d b.execsql ("ALTER TABLE person ADD balance INTEGER");}}

2, Persondao

Package Com.example.sqlitetest;import Java.util.arraylist;import Java.util.list;import android.content.Context; Import Android.database.cursor;import Android.database.sqlite.sqlitedatabase;public class PersonDao {private Context Context;private dbopenhelper helper;public Persondao (context context) {This.context = Context;helper = new DBOpenHelper ( context);} public void Insert (person p) {Sqlitedatabase db = Helper.getwritabledatabase ();d b.execsql ("Insert to person (name, Balance) VALUES (?,?) ", new object[] {p.getname (), P.getbalance ()});//Execute SQL statement, insert Db.close ();} public void Delete (int id) {Sqlitedatabase db = Helper.getwritabledatabase ();d b.execsql ("Delete from person WHERE id=?", N EW object[]{id});d b.close ();} public void Update (person p) {Sqlitedatabase db = Helper.getwritabledatabase ();d b.execsql ("Update person SET name=?"). Balance=? WHERE id=? ", New Object[]{p.getname (), P.getbalance (), P.getid ()});d b.close (); Public person query (int id) {Sqlitedatabase db = Helper.getreadabledatabase ();/Get database connection, readable cursor c = db.rawquery ("Select name, balance from person WHERE id=?", New String[]{id + ""}); Person p = null;if (C.movetonext ()) {//Determines whether the cursor contains the next record, and if so, moves the cursor back one string name = c.getstring (0);//Gets the data on index NO. 0, Convert to String type//string name = c.getstring (C.getcolumnindex ("name"));//This method is also excellent int balance = C.getint (1);p = new Person (ID , name, balance);} C.close ();d b.close (); return p;} Public list<person> Queryall () {Sqlitedatabase db = Helper.getreadabledatabase (); Cursor C = Db.rawquery ("Select ID, name, balance from person", NULL); list<person> persons = new Arraylist<person> (), while (C.movetonext ()) {person p = new Person (c.getint (0), C.getstring (1), C.getint (2));p Ersons.add (p);} C.close ();d b.close (); return persons;} public int Querycount () {Sqlitedatabase db = Helper.getreadabledatabase (); Cursor C = db.rawquery ("Select COUNT (*) from person", null); C.movetonext (); int COUNT = c.getint (0); C.close ();d b.close (); return count;} Public list<person> querypage (int pagenum,int capacity){String offset = (pageNum-1) *capacity + ""; String len = capacity + ""; Sqlitedatabase db = Helper.getreadabledatabase (); Cursor C = Db.rawquery ("Select ID, name, balance from person LIMIT?,?", New String[]{offset,len}); list<person> persons = new Arraylist<person> (), while (C.movetonext ()) {person p = new Person (c.getint (0), C.getstring (1), C.getint (2));p Ersons.add (p);} C.close ();d b.close (); return persons;} /** * Database Transactions * * from this account remittance to the account, remit amount This more money * @param from * @param to * @param amount */public void remit (int from,i NT To,int amount) {Sqlitedatabase db = Helper.getwritabledatabase (); try{db.begintransaction ();//Start Transaction Db.execsql (" UPDATE person SET balance=balance-? WHERE id=? ", new object[] {amount, from});d the B.execsql (" UPDATE person SET balance=balance+? "). WHERE id=? ", new object[] {amount, to});d b.settransactionsuccessful ();//SET Transaction success point ... The SQL statement}finally{db.endtransaction () before the execution of the successful point at the end of the transaction;//End Transaction Db.close ();}}

3. Person

Package Com.example.sqlitetest;public class Person {private integer id;private String name;private integer balance; Public person () {super ();} Public person (integer ID, String name, integer balance) {super (); this.id = Id;this.name = Name;this.balance = balance;} @Overridepublic String toString () {return "person [id=" + ID + ", name=" + name + ", balance=" + balance+ "]";} Public Integer GetId () {return ID;} public void SetId (Integer id) {this.id = ID;} Public String GetName () {return name;} public void SetName (String name) {this.name = name;} Public Integer GetBalance () {return balance;} public void Setbalance (Integer balance) {this.balance = balance;}}

4, DBTest

Package Com.example.sqlitetest;import Java.util.list;import Android.test.androidtestcase;public class DBTest extends androidtestcase {public void Testcreatedb () {Dbopenhelper helper = new Dbopenhelper (GetContext ()); Helper.getwritabledatabase ();}  public void Testinsert () {Persondao dao = new Persondao (GetContext ()), for (int i = 1; I < 100; ++i) {Dao.insert (new person (I, "hjd" + I, 45000+i));} Dao.insert (New person (2, "Hjd", 40000)); public void Testdelete () {Persondao dao = new Persondao (GetContext ());d ao.delete (1);} public void Testupdate () {Persondao dao = new Persondao (GetContext ()); Person p = new person (2, "Zzt", 10000);d ao.update (p);} public void Testquery () {Persondao dao = new Persondao (GetContext ()); System.out.println (Dao.query (2));} public void Testqueryall () {Persondao dao = new Persondao (GetContext ()); list<person> persons = Dao.queryall (); for (person p:persons) {System.out.println (P);}} public void Testquerycount () {Persondao dao = new Persondao (GetContext ()); System.out.printlN ("--------->" + dao.querycount ());} public void Testquerypage () {Persondao dao = new Persondao (GetContext ()); list<person> persons = Dao.querypage (5, ten); for (person p:persons) {System.out.println (P);}}}

5, Mainactivity

Package Com.example.sqlitetest;import Android.os.bundle;import Android.app.activity;import android.view.Menu; public class Mainactivity extends Activity {@Overrideprotected void onCreate (Bundle savedinstancestate) {super.oncreate (savedinstancestate); Setcontentview (R.layout.activity_main);} @Overridepublic boolean Oncreateoptionsmenu (Menu menu) {//Inflate the menu; This adds items to the action bar if it is PR Esent.getmenuinflater (). Inflate (R.menu.main, menu); return true;}}

The above Persondao used in the addition and deletion of the implementation method is required to directly write SQL, in fact, there is a way to implement additions and deletions to change.

In fact, its bottom-level implementation is also to wear which parameters are spelled into SQL statements.

The complete Persondao and dbtest are posted below.

Persondao

Package Com.example.sqlitetest;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;public class Persondao {private Context context;private dbopenhelper helper; Public Persondao (Context context) {This.context = Context;helper = new Dbopenhelper (context);} public void Insert (person p) {Sqlitedatabase db = Helper.getwritabledatabase ();d b.execsql ("Insert to person (name, Balance) VALUES (?,?) ", new object[] {p.getname (), P.getbalance ()}); Execute SQL statement, insert Db.close ();} /** * In some cases the program will accept a contentvalues, it is convenient to use this storage method ... * @param p */public void Insert1 (person p) {Sqlitedatabase db = Helper . Getwritabledatabase ();/** * Contentvalues: Similar to map, key to column name, value to insert content ... * Why contentvalues is similar to map, actually look at his member variables and put method to know Adds a value to the set.            * * @param key * The name of the value to put * @param value * The data for the value to put * * Public void put (string key, String value) {* Mvalues.put (key, value);} */contentvalues values = new Contentval UEs (); Values.put ("Name", P.getname ()), Values.put ("Balance", p.getname ());/** * The second parameter writes a column name casually to handle the case where the values are empty. Because the column name cannot be null */db.insert ("person", "name", "values");//In fact, it is the bottom layer of the use of the way to make a SQL statement. Returns the number of Db.close () of the inserted ID. public void Delete (int id) {Sqlitedatabase db = Helper.getwritabledatabase ();d b.execsql ("Delete from person WHERE id=?", N EW object[] {ID});d b.close ();} public void Delete1 (Int. ID) {Sqlitedatabase db = Helper.getwritabledatabase ();d b.delete ("person", "id=?", New string[]{ ID + ""});d B.close ();} public void Update (person p) {Sqlitedatabase db = Helper.getwritabledatabase ();d b.execsql ("Update person SET name=?, Bala Nce=? WHERE id=? ", new object[] {p.getname (), P.getbalance (), P.getid ()});d b.close ();} public void update1 (person p) {Sqlitedatabase db = Helper.getwritabledatabase (); Contentvalues values = new Contentvalues (), Values.put ("name", P.getname ()), Values.put ("BalancE ", P.getbalance ());d b.update (" person ", Values," id= ", new String[]{p.getid () +" "}); Db.close ();} Public person query (int id) {Sqlitedatabase db = Helper.getreadabledatabase ();//Get database connection, readable cursor c = db.rawquery ("Selec T name, balance from person WHERE id=? ", new string[] {id +" "}); Person p = null;if (C.movetonext ()) {//Determines whether the cursor contains the next record, and if so, moves the cursor back one string name = c.getstring (0);//Gets the data on index NO. 0, Convert to String type//String name = C.getstring (C.getcolumnindex ("name"));//This method is also excellent int balance = C.getint (1);p = new Person (ID , name, balance);} C.close ();d b.close (); return p;} Public person Query1 (int id) {Sqlitedatabase db = Helper.getreadabledatabase ();//Get database connection, readable//cursor c = db.rawquery (" Select name, balance from person WHERE id=? ",//new string[] {id +" "});/** * db.query (table name, column name to query, query criteria, parameters for query criteria, group By,h Aving,order by); */cursor C = db.query ("Person", new string[]{"name", "Balance"}, "Id=?", New String[]{id + ""}, NULL, NULL, NULL); Person p = null;if (C.movetonext ()) {//determines if the cursor contains the next record, and if it contains, the cursorMove backward one string name = c.getstring (0);//Get Data on index NO. 0, convert to String type//String name = C.getstring (C.getcolumnindex ("name"));// This method is also excellent for int balance = C.getint (1);p = new person (ID, name, balance);} C.close ();d b.close (); return p;} Public list<person> Queryall () {Sqlitedatabase db = Helper.getreadabledatabase (); Cursor C = Db.rawquery ("Select ID, name, balance from person", NULL); list<person> persons = new arraylist<person> (); while (C.movetonext ()) {person p = new Person (c.getint (0), C.G Etstring (1), C.getint (2));p Ersons.add (p);} C.close ();d b.close (); return persons;} Public list<person> queryAll1 () {Sqlitedatabase db = Helper.getreadabledatabase ();//cursor C = Db.rawquery (" Select ID, name, balance from person ", NULL); Cursor C = db.query ("person", NULL, NULL, NULL, NULL, NULL, "ID DESC"); list<person> persons = new arraylist<person> (); while (C.movetonext ()) {person p = new Person (c.getint (0), C.G Etstring (1), C.getint (2));p Ersons.add (p);} C.close ();d b.close (); Return persons;} public int Querycount () {Sqlitedatabase db = Helper.getreadabledatabase (),//cursor C = db.rawquery ("Select COUNT (*) from P Erson ", NULL); Cursor C = db.query ("Person", New string[]{"COUNT (*)"}, NULL, NULL, NULL, NULL, NULL); C.movetonext (); int COUNT = C.getint ( 0); C.close ();d b.close (); return count;} public int queryCount1 () {Sqlitedatabase db = Helper.getreadabledatabase (); Cursor C = db.rawquery ("Select COUNT (*) from person", null); C.movetonext (); int COUNT = c.getint (0); C.close ();d b.close (); return count;} Public list<person> querypage (int pagenum, int capacity) {String offset = (pageNum-1) * capacity + ""; String len = capacity + ""; Sqlitedatabase db = Helper.getreadabledatabase (); Cursor C = Db.rawquery ("Select ID, name, balance from person LIMIT?,?", new string[] {offset, len}); list<person> persons = new arraylist<person> (); while (C.movetonext ()) {person p = new Person (c.getint (0), C.G Etstring (1), C.getint (2));p Ersons.add (p);} C.close ();d b.close (); return persons;} Public list<person> queryPage1 (int pagenum,int capacity) {String offset = (pageNum-1) * capacity + ""; String len = capacity + ""; Sqlitedatabase db = Helper.getreadabledatabase ()//cursor c = db.rawquery (//"SELECT ID, name, balance from person LIMIT?, ? ", new string[] {//offset, len}); Cursor C = db.query ("person", NULL, NULL, NULL, NULL, NULL, Null,offset + "," + len); list<person> persons = new arraylist<person> (); while (C.movetonext ()) {person p = new Person (c.getint (0), C.G Etstring (1), C.getint (2));p Ersons.add (p);} C.close ();d b.close (); return persons;} /** * Database Transactions * * from this account remittance to the account, remit amount this more money * * @param from * @param to * @param amount */public void remit (int from , int to, int amount) {Sqlitedatabase db = Helper.getwritabledatabase (); try {db.begintransaction ();//Start Transaction Db.execsql (" UPDATE person SET balance=balance-? WHERE id=? ", new object[] {amount, from});d the B.execsql (" UPDATE person SET balance=balance+? "). WHERE id=? ", new object[] {amount, to});d B.SETTRANSACTIONSUCCEssful ();//SET Transaction success point ... The SQL statement before executing the success point at the end of the transaction} finally {db.endtransaction ();//End Transaction Db.close ();}}

DBTest

Package Com.example.sqlitetest;import Java.util.list;import Android.test.androidtestcase;public class DBTest extends androidtestcase {public void Testcreatedb () {Dbopenhelper helper = new Dbopenhelper (GetContext ()); Helper.getwritabledatabase ();} /** * It is important to note that in this example, the ID in the person table is self-increasing ....  Do not accept ID in person This member variable affects ... */public void Testinsert () {Persondao dao = new Persondao (GetContext ()); for (int i = 1; I < 100; ++i) {Dao.insert (new person (I, "hjd" + I, 45000+i));} Dao.insert (New person (2, "Hjd", 40000)); public void TestInsert1 () {Persondao dao = new Persondao (GetContext ()), for (int i = 0; i <; ++i) {Dao.insert1 (new Pe Rson (i, "hjd" + I, 30000));} Dao.insert1 (New person (3, "DZDP", 10000)); public void Testdelete () {Persondao dao = new Persondao (GetContext ());d ao.delete (1);} public void TestDelete1 () {Persondao dao = new Persondao (GetContext ());d ao.delete1 (1);} public void Testupdate () {Persondao dao = new Persondao (GetContext ()); Person p = new person (2, "Zzt", 10000);d ao.update (p);} Public VOID testUPdate1 () {Persondao dao = new Persondao (GetContext ()); Person p = new person (2, "Hjd", 40000);d ao.update1 (p);} public void Testquery () {Persondao dao = new Persondao (GetContext ()); System.out.println (Dao.query (2));} public void TestQuery1 () {Persondao dao = new Persondao (GetContext ()); System.out.println ("------------>" + dao.query1 (2));} public void Testqueryall () {Persondao dao = new Persondao (GetContext ()); list<person> persons = Dao.queryall (); for (person p:persons) {System.out.println (P);}} public void TestQueryAll1 () {Persondao dao = new Persondao (GetContext ()); list<person> persons = Dao.queryall1 (); for (person p:persons) {System.out.println ("----------->" + P);}} public void Testquerycount () {Persondao dao = new Persondao (GetContext ()); System.out.println ("--------->" + dao.querycount ());} public void TestQueryCount1 () {Persondao dao = new Persondao (GetContext ()); System.out.println ("------->querycount1:" + dao.querycount1 ());} public void Testquerypage () {PersondaoDAO = new Persondao (GetContext ()); list<person> persons = Dao.querypage (5, ten); for (person p:persons) {System.out.println (P);}} public void TestQueryPage1 () {Persondao dao = new Persondao (GetContext ()); list<person> persons = Dao.querypage1 (2), for (person p:persons) {System.out.println ("--------->" + P);}}}

SOURCE Download: http://download.csdn.net/detail/caihongshijie6/7623211