From : http://daixj110.javaeye.com/blog/890532
另一篇:http://database.51cto.com/art/200903/113334.htm
- public class DatabaseHelper extends SQLiteOpenHelper {
- private final static String NAME="sharp.db" ;
- //Factory:代表记录集游标工厂,是专门用来生成记录集游标,记录集游标是对查询结果进行迭代的,后面我们会继续介绍。
- public DatabaseHelper(Context context, String name, CursorFactory factory,
- int version) {
- super(context, name, factory, version);
- // TODO Auto-generated constructor stub
- }
- public DatabaseHelper(Context context){
- super(context, NAME, null, 1 );
- }
- /**
- * 用户第一次使用软件时调用,实现数据库的操作crud
- */
- @Override
- public void onCreate(SQLiteDatabase db) {
- // TODO Auto-generated method stub
- Log.i("save" , "create" );
- db.execSQL("create table person(personid integer primary key autoincrement,name varcahr(20),age integer)" );
- }
- @Override
- public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
- // TODO Auto-generated method stub
- db.execSQL("drop table if exists person" );
- onCreate(db);
- }
- /**
- * 根据版本号进行更新
- * @param db
- * @param mNewVersion
- */
- public void checkVersionCreate(SQLiteDatabase db,int mNewVersion){
- int version = db.getVersion();
- if (version != mNewVersion) {
- db.beginTransaction();
- try {
- if (version == 0 ) {
- onCreate(db);
- }else{
- onUpgrade(db, version,mNewVersion);
- }
- db.setVersion(mNewVersion); //设置为新的版本号
- db.setTransactionSuccessful();
- }
- finally{
- db.endTransaction();
- }
- }
- }
- /**
- * 数据库名不空,获得数据库实例
- * @param mDatabase
- * @param mContext
- * @param mName
- * @param mFactory
- * @return
- */
- public SQLiteDatabase getDatabase(SQLiteDatabase mDatabase,Context mContext,String mName,CursorFactory mFactory){
- if (mDatabase != null && mDatabase.isOpen() && !mDatabase.isReadOnly()) {
- return mDatabase; // The database is already open for business
- }
- if (mName == null) {
- mDatabase = SQLiteDatabase.create(null);
- } else {
- mDatabase = mContext.openOrCreateDatabase(mName, 0 , mFactory);
- }
- return mDatabase;
- }
- }
DbService:
- public class DbService {
- private DatabaseHelper databaseHelper;
- private Context context;
- public DbService(Context context){
- this .context=context;
- databaseHelper=new DatabaseHelper( this .context);
- }
- //保存
- public void save(Person person){
- SQLiteDatabase db = databaseHelper.getWritableDatabase();
- db.execSQL("insert into person(name, age) values(?,?)" , new Object[]{person.getName(), person.getAge()});
- }
- //更新
- public void update(Person person){
- SQLiteDatabase db = databaseHelper.getWritableDatabase();
- db.execSQL("update person set name=?,age=? where personid=?" ,
- new Object[]{person.getName(), person.getAge(),
- person.getId()});
- }
- //根据id查找
- public Person find(Integer id){
- SQLiteDatabase db = databaseHelper.getReadableDatabase();
- Cursor cursor = db.rawQuery("select personid,name,age from person where personid=?" , new String[]{String.valueOf(id)});
- if (cursor.moveToNext()){ //迭代记录集
- Person person = new Person(); //实例化person
- person.setId(cursor.getInt(cursor.getColumnIndex("personid" )));
- person.setName(cursor.getString(1 ));
- person.setAge(cursor.getInt(2 )); //将查到的字段,放入person,
- return person;
- }
- cursor.close();//游标关闭
- return null ;
- }
- //删除
- public void delete(Integer id){
- SQLiteDatabase db=databaseHelper.getWritableDatabase();
- db.execSQL("delete from person where id=?" , new Object[]{id});
- }
- //
- public List<Person> getScrollData( int firstResult, int maxResult){
- List<Person> persons = new ArrayList<Person>();
- SQLiteDatabase db = databaseHelper.getReadableDatabase();
- Cursor cursor = db.rawQuery("select personid,name,age from person limit ?,?" ,
- new String[]{String.valueOf(firstResult),String.valueOf(maxResult)}); //firstResult开始索引
- while (cursor.moveToNext()){ //maxResult每页获取的记录数
- Person person = new Person();
- person.setId(cursor.getInt(cursor.getColumnIndex("personid" )));
- person.setName(cursor.getString(1 ));
- person.setAge(cursor.getInt(2 ));
- persons.add(person);
- }
- cursor.close();
- return persons;
- }
- //
- public long getCount(){
- SQLiteDatabase db = databaseHelper.getReadableDatabase();
- Cursor cursor = db.rawQuery("select count(*) from person" , null );
- //没有占位符参数的话,直接用null
- cursor.moveToFirst();
- long count = cursor.getLong( 0 );
- cursor.close();
- return count;
- }
- //
- public void close(){
- databaseHelper.close();
- }
- }
public class DbService {
private DatabaseHelper databaseHelper;
private Context context;
public DbService(Context context){
this.context=context;
databaseHelper=new DatabaseHelper(this.context);
}
//保存
public void save(Person person){
SQLiteDatabase db = databaseHelper.getWritableDatabase();
db.execSQL("insert into person(name, age) values(?,?)",new Object[]{person.getName(), person.getAge()});
}
//更新
public void update(Person person){
SQLiteDatabase db = databaseHelper.getWritableDatabase();
db.execSQL("update person set name=?,age=? where personid=?",
new Object[]{person.getName(), person.getAge(),
person.getId()});
}
//根据id查找
public Person find(Integer id){
SQLiteDatabase db = databaseHelper.getReadableDatabase();
Cursor cursor = db.rawQuery("select personid,name,age from person where personid=?", new String[]{String.valueOf(id)});
if(cursor.moveToNext()){ //迭代记录集
Person person = new Person();//实例化person
person.setId(cursor.getInt(cursor.getColumnIndex("personid")));
person.setName(cursor.getString(1));
person.setAge(cursor.getInt(2)); //将查到的字段,放入person,
return person;
}
cursor.close();//游标关闭
return null;
}
//删除
public void delete(Integer id){
SQLiteDatabase db=databaseHelper.getWritableDatabase();
db.execSQL("delete from person where id=?",new Object[]{id});
}
//
public List<Person> getScrollData(int firstResult, int maxResult){
List<Person> persons = new ArrayList<Person>();
SQLiteDatabase db = databaseHelper.getReadableDatabase();
Cursor cursor = db.rawQuery("select personid,name,age from person limit ?,?",
new String[]{String.valueOf(firstResult),String.valueOf(maxResult)}); //firstResult开始索引
while(cursor.moveToNext()){ //maxResult每页获取的记录数
Person person = new Person();
person.setId(cursor.getInt(cursor.getColumnIndex("personid")));
person.setName(cursor.getString(1));
person.setAge(cursor.getInt(2));
persons.add(person);
}
cursor.close();
return persons;
}
//
public long getCount(){
SQLiteDatabase db = databaseHelper.getReadableDatabase();
Cursor cursor = db.rawQuery("select count(*) from person", null);
//没有占位符参数的话,直接用null
cursor.moveToFirst();
long count = cursor.getLong(0);
cursor.close();
return count;
}
//
public void close(){
databaseHelper.close();
}
}
Person:
- public class Person {
- private Integer id;
- private String name;
- private Integer age;
- public Person(){}
- public Person(String name,Integer age){
- this .name=name;
- this .age=age;
- }
- 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 Integer getAge() {
- return age;
- }
- public void setAge(Integer age) {
- this .age = age;
- }
- @Override
- public String toString() {
- return "Person [age=" + age + ", id=" + id + ", name=" + name + "]" ;
- }
- }
public class Person {
private Integer id;
private String name;
private Integer age;
public Person(){}
public Person(String name,Integer age){
this.name=name;
this.age=age;
}
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 Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
@Override
public String toString() {
return "Person [age=" + age + ", id=" + id + ", name=" + name + "]";
}
}
PersonServiceTest:
- public class PersonServiceTest extends AndroidTestCase{
- public void testSave() throws Throwable{ //测试保存方法
- DbService personService = new DbService( this .getContext());
- //传入上下文
- Person person = new Person( "Tom" , 21 );
- personService.save(person);
- }
- }
public class PersonServiceTest extends AndroidTestCase{
public void testSave() throws Throwable{ //测试保存方法
DbService personService = new DbService(this.getContext());
//传入上下文
Person person = new Person("Tom", 21);
personService.save(person);
}
}
一个activity:
- public class SqlLiteActivity extends Activity {
- /** Called when the activity is first created. */
- private final static String TAG= "PersonActivity" ;
- private ListView listView;
- private DbService personService;
- @Override
- public void onCreate(Bundle savedInstanceState) {
- super .onCreate(savedInstanceState);
- setContentView(R.layout.main);
- listView = (ListView)findViewById(R.id.personList);
- personService = new DbService( this );
- save(personService);
- List<Person> persons = personService.getScrollData(0 , 3 ); //前十条数据
- List<HashMap<String, String>> data = new
- ArrayList<HashMap<String,String>>();
- HashMap<String, String>title = new HashMap<String, String>();
- title.put("personid" , "编号" );
- title.put("name" , "姓名" );
- title.put("age" , "年龄" );
- data.add(title); //标题
- for (Person person : persons){
- HashMap<String, String> map = new HashMap<String, String>();
- map.put("personid" , String.valueOf(person.getId()));
- map.put("name" , person.getName());
- map.put("age" , String.valueOf(person.getAge()));
- data.add(map); //显示各个数据
- }
- SimpleAdapter adapter = new SimpleAdapter(SqlLiteActivity. this ,
- data, R.layout.personitem, new String[]{ "personid" , "name" , "age" },
- new int []{R.id.personid, R.id.name, R.id.age});
- listView.setAdapter(adapter);
- }
- //
- public void save(DbService service){
- for ( int i= 0 ;i< 5 ;i++){
- Person person=new Person();
- person.setName("d" );
- person.setAge(12 );
- service.save(person);
- }
- }
- }
public class SqlLiteActivity extends Activity {
/** Called when the activity is first created. */
private final static String TAG="PersonActivity";
private ListView listView;
private DbService personService;
@Override
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.main);
listView = (ListView)findViewById(R.id.personList);
personService = new DbService(this);
save(personService);
List<Person> persons = personService.getScrollData(0, 3);//前十条数据
List<HashMap<String, String>> data = new
ArrayList<HashMap<String,String>>();
HashMap<String, String>title = new HashMap<String, String>();
title.put("personid","编号");
title.put("name", "姓名");
title.put("age", "年龄");
data.add(title); //标题
for(Person person : persons){
HashMap<String, String> map = new HashMap<String, String>();
map.put("personid", String.valueOf(person.getId()));
map.put("name", person.getName());
map.put("age", String.valueOf(person.getAge()));
data.add(map); //显示各个数据
}
SimpleAdapter adapter = new SimpleAdapter(SqlLiteActivity.this,
data, R.layout.personitem, new String[]{"personid", "name","age"},
new int[]{R.id.personid, R.id.name, R.id.age});
listView.setAdapter(adapter);
}
//
public void save(DbService service){
for(int i=0;i<5;i++){
Person person=new Person();
person.setName("d");
person.setAge(12);
service.save(person);
}
}
}
main.xml<?xml version="1.0" encoding="utf-8"?>
- <LinearLayout
- xmlns:android="http://schemas.android.com/apk/res/android"
- android:orientation="vertical"
- android:layout_width="fill_parent"
- android:layout_height="fill_parent"
- >
- <ListView
- android:id="@+id/personList"
- android:layout_width="fill_parent"
- android:layout_height="wrap_content"
- ></ListView>
- </LinearLayout>
<LinearLayout
xmlns:android="http://schemas.android.com/apk/res/android"
android:orientation="vertical"
android:layout_width="fill_parent"
android:layout_height="fill_parent"
>
<ListView
android:id="@+id/personList"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
></ListView>
</LinearLayout>
personitem.xml<?xml version="1.0" encoding="utf-8"?>
- <RelativeLayout
- xmlns:android="http://schemas.android.com/apk/res/android"
- android:layout_width="fill_parent"
- android:layout_height="wrap_content" >
- <TextView
- android:layout_width="60px"
- android:layout_height="wrap_content"
- android:id="@+id/personid"
- />
- <TextView
- android:layout_width="160px"
- android:layout_height="wrap_content"
- android:layout_toRightOf="@id/personid"
- android:layout_alignTop="@id/personid"
- android:gravity="center_horizontal"
- android:id="@+id/name"
- />
- <TextView
- android:layout_width="wrap_content"
- android:layout_height="wrap_content"
- android:layout_toRightOf="@id/name"
- android:layout_alignTop="@id/name"
- android:id="@+id/age"
- />
- </RelativeLayout>
<RelativeLayout
xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="fill_parent"
android:layout_height="wrap_content">
<TextView
android:layout_width="60px"
android:layout_height="wrap_content"
android:id="@+id/personid"
/>
<TextView
android:layout_width="160px"
android:layout_height="wrap_content"
android:layout_toRightOf="@id/personid"
android:layout_alignTop="@id/personid"
android:gravity="center_horizontal"
android:id="@+id/name"
/>
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_toRightOf="@id/name"
android:layout_alignTop="@id/name"
android:id="@+id/age"
/>
</RelativeLayout>