Android Development Series (ix): Create a database and complete a simple crud operation

This blog post mainly implements simple database creation and CRUD operations.

First, we build an Android Project named DB

first, complete the creation of the database operation:

Getwritabledatabase () and Getreadabledatabase () in the Sqliteopenhelper class can be used to obtain a sqlitedatabase instance of the operational database. where the Getwritabledatabase () method is called in the Getreadabledatabase () method.

Difference: where the Getwritabledatabase () method opens the database in read-write mode, once the database is full of disk space, the database can only read and not write, if the use of the Getwritabledatabase () method will be an error. The Getreadabledatabase () method is to open the database read-write first, if the database disk space is full, will open the failure, when the failure of Open will continue to try to open the database read-only, if the database space is free, The Sqlitedatabase object for the operational database is successfully obtained.

We first establish a class (inheriting Sqliteopenhelper) Dbopenhelper.java:

Package Cn.itcast.service;import Android.content.context;import Android.database.sqlite.sqlitedatabase;import Android.database.sqlite.sqlitedatabase.cursorfactory;import Android.database.sqlite.sqliteopenhelper;public Class Dbopenhelper extends Sqliteopenhelper {//constructor, calling the constructor of the parent class's Sqliteopenhelper public dbopenhelper (context context) { Super (context, "itcast.db", NULL, 1);//< package >/databases/} @Overridepublic void OnCreate (Sqlitedatabase db) {// is the Db.execsql ("Create TABLE person" (PersonID integer primary key autoincrement, name varchar) that is called when the database is created every time, the phone VARCHAR (+) NULL) "); @Overridepublic void Onupgrade (sqlitedatabase db, int oldversion, int newversion) {//is called when the version of the database changes, Db.execsql ("ALTER TABLE person ADD amount integer ");}}

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.

We then set up a test class to implement the method in call Dbopenhelper to create the database Dbtest.java:

Package Cn.itcast.test;public class DBTest extends Androidtestcase {public   void Testcreatedb () throws exception{ Dbopenhelper dbopenhelper = new Dbopenhelper (GetContext ());d bopenhelper.getwritabledatabase ();}}

do not forget that the test requires several statements to be configured in Androidmanifest.xml.

Running the test found a itcast.db file in the/data/data/cn.itcast.db/databases directory in the File Explorer view, which is the database we created.

second, complete the increase and deletion check crud operation:

We use the person to do the example

First, build a Javabean:Person.java

Package Cn.itcast.domain;public class Person {private Integer id;private string name;private string Phone;private Integer Amount;public person () {}public person (string name, string phone, Integer amount) {this.name = Name;this.phone = Phone;thi S.amount = amount;} Public person (integer ID, string name, string phone, integer amount) {this.id = Id;this.name = Name;this.phone = Phone;thi S.amount = amount;} Public Integer Getamount () {return amount;} public void Setamount (Integer amount) {this.amount = amount;} 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 String Getphone () {return phone;} public void Setphone (String phone) {this.phone = phone;}  @Overridepublic String toString () {return "person [id=" + ID + ", name=" + name + ", phone=" + phone+ ", amount=" + Amount + "]";}}

Then, we need to write the class of the business layer, Personservice.java

Package Cn.itcast.service;import Java.util.arraylist;import Java.util.list;import android.content.context;import Android.database.cursor;import Android.database.sqlite.sqlitedatabase;import Cn.itcast.domain.Person;public Class Personservice {private Dbopenhelper dbopenhelper;public Personservice (context context) {This.dbopenhelper = new Dbopenhelper (context);} /** * Add record * @param person */public void Save (person person) {Sqlitedatabase db = Dbopenhelper.getwritabledatabase ();d B.exe Csql ("INSERT into person (name, phone, amount) VALUES (?,?,?)", New Object[]{person.getname (), Person.getphone (), Person.getamount ()});} /** * Delete record * @param ID record id */public void delete (Integer id) {Sqlitedatabase db = Dbopenhelper.getwritabledatabase ();d B.exe Csql ("Delete from person where personid=?", New Object[]{id}); /** * Update record * @param person */public void Update (person person) {Sqlitedatabase db = Dbopenhelper.getwritabledatabase ();d B.E Xecsql ("Update person set name=?,phone=?,amount=?") Where personid=? ", New ObjeCt[]{person.getname (), Person.getphone (), Person.getamount (), Person.getid ()}); /** * Query record * @param ID record ID * @return */public person find (Integer ID) {Sqlitedatabase db = Dbopenhelper.getreadabledatabas E (); cursor cursor = db.rawquery ("select * from person where personid=?", New String[]{id.tostring ()}); if (Cursor.movetofirst ( ) {int PersonID = Cursor.getint (Cursor.getcolumnindex ("PersonID")); int amount = Cursor.getint (Cursor.getcolumnindex ( "Amount")); String name = cursor.getstring (Cursor.getcolumnindex ("name")); String phone = cursor.getstring (Cursor.getcolumnindex ("Phone")); return new person (PersonID, name, phone, amount);} Cursor.close (); return null;} /** * Paging Get record * @param offset skips the number of records in front * @param maxresult how many records per page * @return */public list<person> getscrolldata (in T offset, int maxresult) {list<person> persons = new arraylist<person> (); Sqlitedatabase db = Dbopenhelper.getreadabledatabase (); cursor cursor = db.rawquery ("SELECT * from Person ORDER by PersonID ASC limit?,?", New string[]{string.valueof (offset), string.valueof (Maxresult)}); while (Cursor.movetonext ()) {int PersonID = Cursor.getint (Cursor.getcolumnindex ("PersonID")); int amount = Cursor.getint (Cursor.getcolumnindex ("Amount")); String name = cursor.getstring (Cursor.getcolumnindex ("name")); String phone = cursor.getstring (Cursor.getcolumnindex ("Phone"));p Ersons.add (new person (PersonID, name, phone, amount) );} Cursor.close (); return persons;} /** * Paging Get record * @param offset skips how many records in front * @param maxresult how many records per page * @return */public Cursor getcursorscrolldata (int offs ET, int maxresult) {Sqlitedatabase db = Dbopenhelper.getreadabledatabase (); cursor cursor = db.rawquery ("Select PersonID as _id,name,phone,amount from the person order by PersonID ASC limit?,?", New STR Ing[]{string.valueof (offset), string.valueof (Maxresult)}); return cursor;} /** * Get Total records * @return */public long GetCount () {Sqlitedatabase db = Dbopenhelper.getreadabledatabase (); cursor cursor = db.rawquery ("SELECT count (*) from person", null); Cursor.movEtofirst (); Long result = Cursor.getlong (0); Cursor.close (); return result;}} 

Then we need to execute the test Personservicetest.java:

Package Cn.itcast.test;import Java.util.list;import Cn.itcast.domain.person;import cn.itcast.service.DBOpenHelper; Import Cn.itcast.service.personservice;import Android.test.androidtestcase;import Android.util.log;public class Personservicetest extends Androidtestcase {private static final String TAG = "Personservicetest";p ublic void Testcreatedb () throws Exception{dbopenhelper Dbopenhelper = new Dbopenhelper (GetContext ());d bopenhelper.getwritabledatabase (); public void Testsave () throws Exception{personservice service = new Personservice (This.getcontext ()), for (int i = 0; I &lt ; 20; i++) {Person person = new Person ("zhangxx" + I, "136765765" + I, [+]); Service.save (person);}} public void Testdelete () throws Exception{personservice service = new Personservice (This.getcontext ()); Service.delete ( 21);} public void Testupdate () throws Exception{personservice service = new Personservice (This.getcontext ()); Person person = service.find (1);p erson.setname ("Zhangxiaoxiao"), service.update (person); Public void Testfind () throws Exception{personservice service = new Personservice (This.getcontext ()); Person person = service.find (1); LOG.I (TAG, person.tostring ());} public void Testscrolldata () throws Exception{personservice service = new Personservice (This.getcontext ()); list<person> persons = Service.getscrolldata (0, 5); for (person person:persons) {log.i (TAG, person.tostring ());}} public void Testcount () throws Exception{personservice service = new Personservice (This.getcontext ()); Long result = Servi Ce.getcount (); LOG.I (TAG, result+ "");} public void Testupdateamount () throws Exception{personservice service = new Personservice (This.getcontext ()); Person Person1 = Service.find (1); Person Person2 = Service.find (2);p Erson1.setamount (+);p erson2.setamount (service.update); Service.update (Person2);}}

After finding itcast.db, you can open the view by downloading: SQLite Explorer

Android Development Series (ix): Create a database and complete a simple crud operation