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<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),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> 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<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()) //类似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;
}
}