Sqlitedatabase Database Operations
Sqlitedatabase Open Administrative Tools Sqliteexpertsetup
Create a database
Automatically create DATABASE features
Sqliteopenhelper. Getreadabledatabase () or. getwriteabledatabase
Create Dbopenhelper extends Sqliteopenhelper
{
Public Dbopenhelper (Context context)
{
Super (context, "itcast.db", null,1); Database name, version number default save Directory < package >/databases/
}
public void OnCreate (Sqlitedatabase db)//is called when the database is created every time
{
Manipulating SQL statements through instances of class Sqlitedatabase
Db.execsql ("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, as if the version changed from 1 to 2
Db.execsql ("ALTER TABLE person ADD phone VARCHAR () 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.execsql ("INSERT into person (Name,phone) VALUES (?,?)", New Object[]{person.getname (), +person.getphone ()});
Db.close ();
}
public void Delete (person person)
{
Sqlitedatabase db=dbopenhelper.getwriteabledatabase ();
Db.execsql ("Delete from person where personid=?", New Object[]{id});
}
public void update (person person)
{
Sqlitedatabase db=dbopenhelper.getwriteabledatabase ();
Db.execsql ("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;
}
/**
* Paging to get records
* @param offset skips the previous number of records
* @param maxresult how many records each page gets
* @return
**/
Public list<person> getscrolldata (int 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), St Ring.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> Person=service.getscrolldata (0,5);
for (person person:p ersons)
{
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.execsql ("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.execsql ("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.execsql ("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,null,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;
}
/**
* Paging to get records
* @param offset skips the previous number of records
* @param maxresult how many records each page gets
* @return
**/
Public list<person> getscrolldata (int offset,int maxresult)
{
list<person> persons = new arraylist<person> ();
Sqlitedatabase db=dbopenhelper.getreadabledatabase ();
Cursor cursor=db.query ("person", Null,null,null,null,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,null,null,null);
Cursor.movetofirst ();
Long Result=cursor.getlong (0);
Cursor.close ();
return result;
}
}
