Case study of adding, deleting, modifying, and querying an Android SQLite Database

Person entity class

Copy codeThe Code is as follows: package com. ljq. domain;

Public class Person {
Private Integer id;
Private String name;
Private String phone;

Public Person (){
Super ();
}

Public Person (String name, String phone ){
Super ();
This. name = name;
This. phone = phone;
}

Public Person (Integer id, String name, String phone ){
Super ();
This. id = id;
This. name = name;
This. phone = phone;
}

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;
}

}

DBOpenHelper database Association class
Copy codeThe Code is as follows: package com. ljq. db;

Import android. content. Context;
Import android. database. sqlite. SQLiteDatabase;
Import android. database. sqlite. SQLiteOpenHelper;

Public class DBOpenHelper extends SQLiteOpenHelper {
// The class is not instantiated. It cannot be used as a parameter of the parent class constructor and must be declared as static.
Private static final String DBNAME = "ljq. db ";
Private static final int VERSION = 1;

// The third parameter CursorFactory specifies the factory class for obtaining a cursor instance during query execution,
// If this parameter is set to null, the default factory class is used.
Public DBOpenHelper (Context context ){
Super (context, DBNAME, null, VERSION );
}

@ Override
Public void onCreate (SQLiteDatabase db ){
Db.exe cSQL ("create table person (id integer primary key autoincrement, name varchar (20), phone varchar (20 ))");
}

@ Override
Public void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion ){
// Note: the deletion operation cannot be performed in the production environment.
Db.exe cSQL ("drop table if exists person ");
OnCreate (db );
}
}

PersonService Service
Copy codeThe Code is as follows: package com. ljq. db;

Import java. util. ArrayList;
Import java. util. List;

Import android. content. Context;
Import android. database. Cursor;

Import com. ljq. domain. Person;

Public class PersonService {
Private DBOpenHelper dbOpenHelper = null;

/**
* Constructor
*
* After the getWritableDatabase () or getReadableDatabase () method is called, The SQLiteDatabase instance is cached;
* This is a mobile app. Generally, only one user accesses the database. Therefore, we recommend that you do not close the database to maintain the connection status.
* GetWritableDatabase (): The difference between getReadableDatabase and getReadableDatabase is that when the database is full, an error is returned when the former is called, but not when the latter is called,
* If the database is not updated, it is best to call the latter to obtain the database connection.
*
* It is better for programmers familiar with SQL statements to use exeSQL () and rawQuery (), because they are more intuitive and clear.
*
* @ Param context
*/
Public PersonService (Context context ){
DbOpenHelper = new DBOpenHelper (context );
}

Public void save (Person person ){
Dbopenhelper.getwritabledatabase(cmd.exe cSQL ("insert into person (name, phone) values (?, ?) ",
New Object [] {person. getName (), person. getPhone ()});
}

Public void update (Person person ){
Dbopenhelper.getwritabledatabase(cmd.exe cSQL ("update person set name = ?, Phone =? Where id =? ",
New Object [] {person. getName (), person. getPhone (), person. getId ()});
}

Public void delete (Integer... ids ){
If (ids. length> 0 ){
StringBuffer sb = new StringBuffer ();
For (Integer id: ids ){
Sb. append ("? "). Append (",");
}
Sb. deleteCharAt (sb. length ()-1 );
Dbopenhelper.getwritabledatabase(cmd.exe cSQL ("delete from person where id in (" + sb + ")", (Object []) ids );
}
}

Public Person find (Integer id ){
Cursor cursor = dbOpenHelper. getReadableDatabase (). rawQuery ("select id, name, phone from person where id =? ",
New String [] {String. valueOf (id )});
If (cursor. moveToNext ()){
Int personid = cursor. getInt (0 );
String name = cursor. getString (1 );
String phone = cursor. getString (2 );
Return new Person (personid, name, phone );
}
Return null;
}

Public long getCount (){
Cursor cursor = dbOpenHelper. getReadableDatabase (). query ("person ",
New String [] {"count (*)"}, null );
If (cursor. moveToNext ()){
Return cursor. getLong (0 );
}
Return 0;
}

/**
* Paging
*
* @ Param startResult offset, starting from 0 by default
* @ Param maxResult the number of entries displayed per page
* @ Return
*/
Public List <Person> getScrollData (int startResult, int maxResult ){
List <Person> persons = new ArrayList <Person> ();
// Cursor cursor = dbOpenHelper. getReadableDatabase (). query ("person", new String [] {"id, name, phone "},
// "Name like? ", New String [] {" % ljq % "}, null, null," id desc "," 1, 2 ");
Cursor cursor = dbOpenHelper. getReadableDatabase (). rawQuery ("select * from person limit ?,? ",
New String [] {String. valueOf (startResult), String. valueOf (maxResult )});
While (cursor. moveToNext ()){
Int personid = cursor. getInt (0 );
String name = cursor. getString (1 );
String phone = cursor. getString (2 );
Persons. add (new Person (personid, name, phone ));
}
Return persons;
}

}

PersonServiceTest test class
Copy codeThe Code is as follows: package com. ljq. test;

Import java. util. List;

Import com. ljq. db. PersonService;
Import com. ljq. domain. Person;

Import android. test. AndroidTestCase;
Import android. util. Log;

Public class PersonServiceTest extends AndroidTestCase {
Private final String TAG = "PersonServiceTest ";

Public void testSave () throws Exception {
PersonService personService = new PersonService (this. getContext ());
PersonService. save (new Person ("zhangsan1", "059188893343 "));
PersonService. save (new Person ("zhangsan2", "059188893343 "));
PersonService. save (new Person ("zhangsan3", "059188893343 "));
PersonService. save (new Person ("zhangsan4", "059188893343 "));
PersonService. save (new Person ("zhangsan5", "059188893343 "));
}

Public void testUpdate () throws Exception {
PersonService personService = new PersonService (this. getContext ());
Person person = personService. find (1 );
Person. setName ("linjiqin ");
PersonService. update (person );
}

Public void testFind () throws Exception {
PersonService personService = new PersonService (this. getContext ());
Person person = personService. find (1 );
Log. I (TAG, person. getName ());
}

Public void testList () throws Exception {
PersonService personService = new PersonService (this. getContext ());
List <Person> persons = personService. getScrollData (0, 10 );
For (Person person: persons ){
Log. I (TAG, person. getId () + ":" + person. getName ());
}
}

Public void testCount () throws Exception {
PersonService personService = new PersonService (this. getContext ());
Log. I (TAG, String. valueOf (personService. getCount ()));
}

Public void testDelete () throws Exception {
PersonService personService = new PersonService (this. getContext ());
PersonService. delete (1 );
}

Public void testDeleteMore () throws Exception {
PersonService personService = new PersonService (this. getContext ());
PersonService. delete (new Integer [] {2, 5, 6 });
}
}

Running result