Case study on Android SQLite database additions and deletions _android

Person entity class

Copy Code code 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 Code code 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 {
Class is not instantiated, is a parameter that cannot be used as a parent class constructor, and must be declared as a static
private static final String dbname = "ljq.db";
private static final int VERSION = 1;

The third parameter cursorfactory specifies the factory class that obtains a cursor instance when executing the query.
Set to NULL to represent the factory class using the system default
Public Dbopenhelper {
Super (context, dbname, NULL, VERSION);
}

@Override
public void OnCreate (Sqlitedatabase db) {
Db.execsql ("CREATE TABLE person" (ID INTEGER PRIMARY KEY autoincrement, NAME VARCHAR (), PHONE VARCHAR (20));
}

@Override
public void Onupgrade (sqlitedatabase db, int oldversion, int newversion) {
Note: No delete operation can be done on the production environment
Db.execsql ("DROP TABLE IF EXISTS person");
OnCreate (DB);
}
}

Personservice Business Class

Copy Code code 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
*
* When the getwritabledatabase () or Getreadabledatabase () method is invoked, the Sqlitedatabase instance is cached;
* Because this is a mobile application, generally only one user access to the database, so it is recommended not to close the database, stay connected state.
* Getwritabledatabase (), getreadabledatabase the difference is that when the database is full, the former will be the error, call the latter does not,
* So if you're not updating the database, it's best to call the latter to get the database connection.
*
* For programmers familiar with SQL statements, it is best to use Exesql (), Rawquery (), because it is more intuitive and straightforward
*
* @param context
*/
Public Personservice {
Dbopenhelper = new Dbopenhelper (context);
}

public void Save (person person) {
Dbopenhelper.getwritabledatabase (). Execsql ("INSERT into person (name, phone) values (?,?)",
New Object[]{person.getname (), Person.getphone ()});
}

public void update (person person) {
Dbopenhelper.getwritabledatabase (). Execsql ("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 (). Execsql ("Delete from person where id" ("+sb+") ", (object[]) IDs);
}
}

Public person find (Integer ID) {
Cursor Cursor = Dbopenhelper.getreadabledatabase (). Rawquery ("Select ID, name, phone from 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 to new person (PersonID, name, phone);
}
return null;
}

Public long GetCount () {
Cursor Cursor = dbopenhelper.getreadabledatabase (). Query ("Person",
New string[]{"Count (*)"}, Null,null,null,null,null);
if (Cursor.movetonext ()) {
Return Cursor.getlong (0);
}
return 0;
}

/**
* Page pagination
*
* @param startresult Offset, default starting from 0
* @param maxresult The number of bars 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 Code code 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});
}
}

Run Results