Android資料庫操作
SQLiteDatabase資料庫操作
SQLiteDatabase 開啟管理工具 SQLiteExpertSetup
建立資料庫
自動建立資料庫功能
SQLiteOpenHelper .getReadableDatabase() 或.getWriteableDatabase
建立DBOpenHelper extends SQLiteOpenHelper
{
public DBOpenHelper(Context context)
{
super(context,"itcast.db",null,1); //資料庫名稱，版本號碼 預設儲存目錄 <包>/databases/
}
public void onCreate(SQLiteDatabase db) //是在資料庫每一次被建立時調用的
{
//通過類SQLiteDatabase的執行個體來操作SQL語句
db.execSQL("CREATE TABLE person(personid integer primary key autoincrement,name varchar(20))");
}
public void onUpgrade(SQLiteDatabase db,int oldVersion,int newVersion)
{
//檔案版本號碼發生變更時調用,如版本由1變為2
db.execSQL("ALTER TABLE person ADD phone VARCHAR(12) NULL");
}
}
Person表類
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+"]";
}
}
業務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用於對查詢結果集進行隨機訪問
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;
}
/**
*分頁擷取記錄
*@param offset 跳過前面多少條記錄
*@param maxResult 每頁擷取多少條記錄
*@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()) //類似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;
}
}
測試類別：
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+"");
}
}
其他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值欄位
//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;
}
/**
*分頁擷取記錄
*@param offset 跳過前面多少條記錄
*@param maxResult 每頁擷取多少條記錄
*@return
**/
public List getScrollData(int offset,int maxResult)
{
List persons = new ArrayList();
SQLiteDatabase db=dbOpenHelper.getReadableDatabase();
Cursor cursor=db.query("person",null,null,null,null,null,"personid asc", offset+","+maxResult);
while(cursor.moveToNext()) //類似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;
}
}
