Android Development Series (9): Create a database and complete simple CRUD operations

Android Development Series (9): Create a database and complete simple CRUD operations

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

First, create an Android Project named db

1. Complete database creation:

You can use getWritableDatabase () and getReadableDatabase () in the SQLiteOpenHelper class to obtain the SQLiteDatabase instance that operates the database. The getReadableDatabase () method calls the getWritableDatabase () method.

Difference: The getWritableDatabase () method opens the database in read/write mode. Once the disk space of the database is full, the database can only read but cannot write data. If getWritableDatabase () is used () an error occurs. The getReadableDatabase () method first opens the database in read/write mode. If the disk space of the database is full, the opening fails. When the opening fails, the database will continue to be opened in read-only mode; if the database space is free at this time, the SQLiteDatabase object for operating the database will be obtained successfully.

We first create a class (inheriting from 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 that calls the constructor public DBOpenHelper (Context context) {super (context, "itcast. db ", null, 1 );//
 <包>
  
/Databases/} @ Overridepublic void onCreate (SQLiteDatabase db) {// using cSQL ("create table person (personid integer primary key autoincrement, name varchar (20), phone VARCHAR (12) NULL) ") ;}@ Overridepublic void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion) {// db.exe cSQL (" alter table person ADD amount integer ") will be called when the volume of the data database changes ");}}
 

The onCreate () method is called only when the database is generated for the first time. In the onCreate () method, the database table structure can be generated and the initialization data used by some applications can be added.

The onUpgrade () method is called when the database version changes. Generally, the version number needs to be changed during software upgrade, and the database version is controlled by programmers.

Then, we create a test class to call the methods in DBOpenHelper to create database DBTest. java:

package cn.itcast.test;public class DBTest extends AndroidTestCase {   public void testCreateDB() throws Exception{DBOpenHelper dbOpenHelper = new DBOpenHelper(getContext());dbOpenHelper.getWritableDatabase();}}

Do not forget to configure several statements in AndroidManifest. xml for testing.

Run the test and find that there is an itcast. db File under the/data/cn. itcast. db/databases directory in the File Explorer view, which is the database we created.

2. Complete CRUD addition, deletion, modification, and query:

Let's use Person as an example.

First, create 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;this.amount = amount;}public Person(Integer id, String name, String phone, Integer amount) {this.id = id;this.name = name;this.phone = phone;this.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 at 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 voi D save (Person person) {SQLiteDatabase db = dbopenhelper.getwritabledatabase(mongomongodb.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(mongomongodb.exe cSQL ("delete from person where personid =? ", New Object [] {id});}/*** update record * @ param person */public void update (Person person) {SQLiteDatabase db = dbopenhelper.getwritabledatabase(mongomongodb.exe cSQL ("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. getReadableDatabase (); 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 ;} /*** retrieve records by PAGE ** @ param offset skips the previous number of records * @ param maxResult obtains the number of records per page * @ return */public List
 
  
GetScrollData (int offset, int maxResult) {List
  
   
Persons = new ArrayList
   
    
(); 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"); persons. add (new Person (personid, name, phone, amount);} c Ursor. close (); return persons ;} /*** retrieve records by PAGE ** @ param offset skips the previous number of records * @ param maxResult obtains the number of records per page * @ return */public Cursor getCursorScrollData (int offset, int maxResult) {SQLiteDatabase db = dbOpenHelper. getReadableDatabase (); Cursor cursor = db. rawQuery ("select personid as _ id, name, phone, amount from person order by personid asc limit ?,? ", New String [] {String. valueOf (offset), String. valueOf (maxResult)}); return cursor;}/*** obtain the total number of 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 test the 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";public void testCreateDB() throws Exception{DBOpenHelper dbOpenHelper = new DBOpenHelper(getContext());dbOpenHelper.getWritableDatabase();}public void testSave() throws Exception{PersonService service = new PersonService(this.getContext());for(int i = 0 ; i < 20 ; i++){Person person = new Person("zhangxx"+ i, "136765765"+ i, 100);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);person.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
 
   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 = service.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);person1.setAmount(100);person2.setAmount(50);service.update(person1);service.update(person2);}}
 

After you find itcast. db, you can open it by downloading: SQLite Explorer.