Android database operations
SQLiteDatabase operations
SQLiteDatabase open the management tool SQLiteExpertSetup
Create a database
Automatic database creation
SQLiteOpenHelper. getReadableDatabase () or. getWriteableDatabase
Create DBOpenHelper extends SQLiteOpenHelper
{
Public DBOpenHelper (Context context)
{
Super (context, "itcast. db", null, 1); // database name. The default version number is saved in the directory. <包> /Databases/
}
Public void onCreate (SQLiteDatabase db) // It is called every time the database is created
{
// Operate SQL statements through an instance similar to SQLiteDatabase
Db.exe cSQL ("create table person (personid integer primary key autoincrement, name varchar (20 ))");
}
Public void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion)
{
// Called when the file version number is changed. For example, the version number is changed from 1 to 2.
Db.exe cSQL ("alter table person ADD phone VARCHAR (12) NULL ");
}
}
Person table class
Public class Person
{
Private Integer id;
Private String name;
Private String 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;
}
Public String toString ()
{
Return "Person [id =" + id + ", name =" + name + ", phone =" + phone + "]";
}
}
Business PersonService
Private DBOpenHelper dbOpenHelper;
Public PersonService (Context context ){
This. dbOpenHelper = new DBOpenHelper (context );
}
Public class PersonService {
Public void save (Person person)
{
SQLiteDatabase db = dbOpenHelper. getWriteableDatabase ();
Db.exe cSQL ("insert into person (name, phone) values (?,?) ", New Object [] {person. getName (), + person. getPhone ()});
// Db. close ();
}
Public void delete (Person person)
{
SQLiteDatabase db = dbOpenHelper. getWriteableDatabase ();
Db.exe cSQL ("delete from person where personid =? ", New Object [] {id });
}
Public void update (Person person)
{
SQLiteDatabase db = dbOpenHelper. getWriteableDatabase ();
Db.exe cSQL ("update person set name = ?, Phone =? Where personid =? ", New Object [] {person. getName (), person. getPhone (), person. getId ()});
}
Public void find (Person person)
{
SQLiteDatabase db = dbOpenHelper. getReadableDatabase ();
Cursor cursor = db. rawQuery ("select * from person where personid =? ", New String [] {id. toString ()}); // cursor is used to randomly access the query result set.
If (cursor. moveToFirst ())
{
Int personid = cursor. getInt (cursor. getColumnIndex ("personid "));
String name = cursor. getString (cursor. getColumnIndex ("name "));
String phone = cursor. getString (cursor. getColumnIndex ("phone "));
Return new Person (personid, name, phone );
}
Cursor. close ();
Return null;
}
/**
* Retrieve records by PAGE
* @ Param offset skips the previous number of records
* @ Param maxResult how many records are obtained 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 () // similar to ResultSet. next ()
{
Int personid = cursor. getInt (cursor. getColumnIndex ("personid "));
String name = cursor. getString (cursor. getColumnIndex ("name "));
String phone = cursor. getString (cursor. getColumnIndex ("phone "));
Person. add (new Person (personid, name, phone ));
}
Cursor. close ();
Return persons;
}
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;
}
}
Test class:
Public class PersonServiceTest extends AndroidTestCase
{
Private static final String TAG = "PersonServiceTest ";
Public void testCreateDB () throws Exception
{
DBOpenHelper dbOpenHelper = new DBOpenHelper (getContext ());
DbOpenHelper. getWriteableDatabase ();
}
Public void testSave () throws Exception
{
PersonService service = new PersonService (this. getContext ());
For (int I = 0; I <20; I ++)
{
Person person = new Person ("zhangxx" + I, "132234324" + 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 );
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 Person = 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 + "");
}
}
Other PersonService
Private DBOpenHelper dbOpenHelper;
Public PersonService (Context context ){
This. dbOpenHelper = new DBOpenHelper (context );
}
Public class PersonService {
Public void save (Person person)
{
SQLiteDatabase db = dbOpenHelper. getWriteableDatabase ();
ContentValues values = new ContentValues ();
Values. put ("name", person. getName ());
Values. put ("phone", person. getPhone ());
Db. insert ("person", null, values); // NULL Value Field
// Db.exe cSQL ("insert into person (name, phone) values (?,?) ", New Object [] {person. getName (), + person. getPhone ()});
// Db. close ();
}
Public void delete (Person person)
{
SQLiteDatabase db = dbOpenHelper. getWriteableDatabase ();
Db. delete ("person", "personid =? ", New String [] {id. toString ()});
// Db.exe cSQL ("delete from person where personid =? ", New Object [] {id });
}
Public void update (Person person)
{
SQLiteDatabase db = dbOpenHelper. getWriteableDatabase ();
ContentValues values = new ContentValues ();
Values. put ("name", person. getName ());
Values. put ("phone", person. getPhone ());
Db. update ("person", values, "personid =? ", New String [] {person. getId (). toString ()});
// Db.exe cSQL ("update person set name = ?, Phone =? Where personid =? ", New Object [] {person. getName (), person. getPhone (), person. getId ()});
}
Public void find (Person person)
{
SQLiteDatabase db = dbOpenHelper. getReadableDatabase ();
Cursor cursor = db. query ("person", null, "personid =? ", New String [] {id. toString ()}, null });
If (cursor. moveToFirst ())
{
Int personid = cursor. getInt (cursor. getColumnIndex ("personid "));
String name = cursor. getString (cursor. getColumnIndex ("name "));
String phone = cursor. getString (cursor. getColumnIndex ("phone "));
Return new Person (personid, name, phone );
}
Cursor. close ();
Return null;
}
/**
* Retrieve records by PAGE
* @ Param offset skips the previous number of records
* @ Param maxResult how many records are obtained per page
* @ Return
**/
Public List GetScrollData (int offset, int maxResult)
{
List Persons = new ArrayList ();
SQLiteDatabase db = dbOpenHelper. getReadableDatabase ();
Cursor cursor = db. query ("person", null, "personid asc", offset + "," + maxResult );
While (cursor. moveToNext () // similar to ResultSet. next ()
{
Int personid = cursor. getInt (cursor. getColumnIndex ("personid "));
String name = cursor. getString (cursor. getColumnIndex ("name "));
String phone = cursor. getString (cursor. getColumnIndex ("phone "));
Person. add (new Person (personid, name, phone ));
}
Cursor. close ();
Return persons;
}
Public long getCount ()
{
SQLiteDatabase db = dbOpenHelper. getReadableDatabase ();
Cursor cursor = db. query ("person", new String [] {"count (*)"}, null, null );
Cursor. moveToFirst ();
Long result = cursor. getLong (0 );
Cursor. close ();
Return result;
}
}
