android 数据库操作

From : http://daixj110.javaeye.com/blog/890532

另一篇：http://database.51cto.com/art/200903/113334.htm

Databasehelper代码

1. publicclassDatabaseHelperextendsSQLiteOpenHelper{
3. privatefinalstaticStringNAME="sharp.db" ;
4. //Factory:代表记录集游标工厂，是专门用来生成记录集游标，记录集游标是对查询结果进行迭代的，后面我们会继续介绍。
5. publicDatabaseHelper(Contextcontext,Stringname,CursorFactoryfactory,
6. intversion){
7. super(context,name,factory,version);
8. //TODOAuto-generatedconstructorstub
9. }
10. publicDatabaseHelper(Contextcontext){
11. super(context,NAME,null,1 );
12. }
14. /**
15. *用户第一次使用软件时调用，实现数据库的操作crud
16. */
17. @Override
18. publicvoidonCreate(SQLiteDatabasedb){
19. //TODOAuto-generatedmethodstub
20. Log.i("save" , "create" );
21. db.execSQL("createtableperson(personidintegerprimarykeyautoincrement,namevarcahr(20),ageinteger)" );
22. }
24. @Override
25. publicvoidonUpgrade(SQLiteDatabasedb,intoldVersion,intnewVersion){
26. //TODOAuto-generatedmethodstub
27. db.execSQL("droptableifexistsperson" );
28. onCreate(db);
29. }
30. /**
31. *根据版本号进行更新
32. *@paramdb
33. *@parammNewVersion
34. */
35. publicvoidcheckVersionCreate(SQLiteDatabasedb,intmNewVersion){
36. intversion=db.getVersion();
37. if(version!=mNewVersion){
38. db.beginTransaction();
39. try{
40. if(version==0 ){
41. onCreate(db);
42. }else{
43. onUpgrade(db,version,mNewVersion);
44. }
45. db.setVersion(mNewVersion);//设置为新的版本号
46. db.setTransactionSuccessful();
47. }
48. finally{
49. db.endTransaction();
50. }
51. }
52. }
53. /**
54. *数据库名不空，获得数据库实例
55. *@parammDatabase
56. *@parammContext
57. *@parammName
58. *@parammFactory
59. *@return
60. */
61. publicSQLiteDatabasegetDatabase(SQLiteDatabasemDatabase,ContextmContext,StringmName,CursorFactorymFactory){
62. if(mDatabase!=null&&mDatabase.isOpen()&&!mDatabase.isReadOnly()){
63. returnmDatabase;//Thedatabaseisalreadyopenforbusiness
64. }
65. if(mName==null){
66. mDatabase=SQLiteDatabase.create(null);
67. }else{
68. mDatabase=mContext.openOrCreateDatabase(mName,0 ,mFactory);
69. }
70. returnmDatabase;
71. }
72. }

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：

Java代码

1. public class DbService{
2. private DatabaseHelperdatabaseHelper;
3. private Contextcontext;
4. public DbService(Contextcontext){
5. this .context=context;
6. databaseHelper=new DatabaseHelper( this .context);
7. }
8. //保存
9. public void save(Personperson){
10. SQLiteDatabasedb=databaseHelper.getWritableDatabase();
11. db.execSQL("insertintoperson(name,age)values(?,?)" , new Object[]{person.getName(),person.getAge()});
12. }
13. //更新
14. public void update(Personperson){
15. SQLiteDatabasedb=databaseHelper.getWritableDatabase();
16. db.execSQL("updatepersonsetname=?,age=?wherepersonid=?" ,
17. new Object[]{person.getName(),person.getAge(),
18. person.getId()});
19. }
20. //根据id查找
21. public Personfind(Integerid){
22. SQLiteDatabasedb=databaseHelper.getReadableDatabase();
23. Cursorcursor=db.rawQuery("selectpersonid,name,agefrompersonwherepersonid=?" , new String[]{String.valueOf(id)});
24. if (cursor.moveToNext()){ //迭代记录集
25. Personperson=new Person(); //实例化person
26. person.setId(cursor.getInt(cursor.getColumnIndex("personid" )));
27. person.setName(cursor.getString(1 ));
28. person.setAge(cursor.getInt(2 )); //将查到的字段，放入person，
29. return person;
30. }
31. cursor.close();//游标关闭
32. return null ;
33. }
34. //删除
35. public void delete(Integerid){
36. SQLiteDatabasedb=databaseHelper.getWritableDatabase();
37. db.execSQL("deletefrompersonwhereid=?" , new Object[]{id});
38. }
39. //
40. public List<Person>getScrollData( int firstResult, int maxResult){
41. List<Person>persons=new ArrayList<Person>();
42. SQLiteDatabasedb=databaseHelper.getReadableDatabase();
43. Cursorcursor=db.rawQuery("selectpersonid,name,agefrompersonlimit?,?" ,
44. new String[]{String.valueOf(firstResult),String.valueOf(maxResult)}); //firstResult开始索引
45. while (cursor.moveToNext()){ //maxResult每页获取的记录数
46. Personperson=new Person();
47. person.setId(cursor.getInt(cursor.getColumnIndex("personid" )));
48. person.setName(cursor.getString(1 ));
49. person.setAge(cursor.getInt(2 ));
50. persons.add(person);
51. }
52. cursor.close();
53. return persons;
54. }
55. //
56. public long getCount(){
57. SQLiteDatabasedb=databaseHelper.getReadableDatabase();
58. Cursorcursor=db.rawQuery("selectcount(*)fromperson" , null );
59. //没有占位符参数的话，直接用null
60. cursor.moveToFirst();
61. long count=cursor.getLong( 0 );
62. cursor.close();
63. return count;
64. }
65. //
66. public void close(){
67. databaseHelper.close();
68. }
69. }

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：

Java代码

1. public class Person{
2. private Integerid;
3. private Stringname;
4. private Integerage;
5. public Person(){}
6. public Person(Stringname,Integerage){
7. this .name=name;
8. this .age=age;
9. }
10. public IntegergetId(){
11. return id;
12. }
13. public void setId(Integerid){
14. this .id=id;
15. }
16. public StringgetName(){
17. return name;
18. }
19. public void setName(Stringname){
20. this .name=name;
21. }
22. public IntegergetAge(){
23. return age;
24. }
25. public void setAge(Integerage){
26. this .age=age;
27. }
28. @Override
29. public StringtoString(){
30. return "Person[age=" +age+ ",id=" +id+ ",name=" +name+ "]" ;
31. }
32. }

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：

Java代码

1. public class PersonServiceTest extends AndroidTestCase{
2. public void testSave() throws Throwable{ //测试保存方法
3. DbServicepersonService=new DbService( this .getContext());
4. //传入上下文
5. Personperson=new Person( "Tom" , 21 );
6. personService.save(person);
7. }
8. }

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：

Java代码

1. public class SqlLiteActivity extends Activity{
2. /**Calledwhentheactivityisfirstcreated.*/
3. private final static StringTAG= "PersonActivity" ;
4. private ListViewlistView;
5. private DbServicepersonService;
6. @Override
7. public void onCreate(BundlesavedInstanceState){
8. super .onCreate(savedInstanceState);
9. setContentView(R.layout.main);
10. listView=(ListView)findViewById(R.id.personList);
11. personService=new DbService( this );
12. save(personService);
13. List<Person>persons=personService.getScrollData(0 , 3 ); //前十条数据
14. List<HashMap<String,String>>data=new
15. ArrayList<HashMap<String,String>>();
16. HashMap<String,String>title=new HashMap<String,String>();
17. title.put("personid" , "编号" );
18. title.put("name" , "姓名" );
19. title.put("age" , "年龄" );
20. data.add(title);//标题
21. for (Personperson:persons){
22. HashMap<String,String>map=new HashMap<String,String>();
23. map.put("personid" ,String.valueOf(person.getId()));
24. map.put("name" ,person.getName());
25. map.put("age" ,String.valueOf(person.getAge()));
26. data.add(map);//显示各个数据
27. }
28. SimpleAdapteradapter=new SimpleAdapter(SqlLiteActivity. this ,
29. data,R.layout.personitem,new String[]{ "personid" , "name" , "age" },
30. new int []{R.id.personid,R.id.name,R.id.age});
31. listView.setAdapter(adapter);
32. }
34. //
35. public void save(DbServiceservice){
36. for ( int i= 0 ;i< 5 ;i++){
37. Personperson=new Person();
38. person.setName("d" );
39. person.setAge(12 );
40. service.save(person);
41. }
42. }
43. }

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"?>

Java代码

1. <LinearLayout
2. xmlns:android="http://schemas.android.com/apk/res/android"
3. android:orientation="vertical"
4. android:layout_width="fill_parent"
5. android:layout_height="fill_parent"
6. >
7. <ListView
8. android:id="@+id/personList"
9. android:layout_width="fill_parent"
10. android:layout_height="wrap_content"
11. ></ListView>
12. </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"?>

Java代码

1. <RelativeLayout
2. xmlns:android="http://schemas.android.com/apk/res/android"
3. android:layout_width="fill_parent"
4. android:layout_height="wrap_content" >
5. <TextView
6. android:layout_width="60px"
7. android:layout_height="wrap_content"
8. android:id="@+id/personid"
9. />
11. <TextView
12. android:layout_width="160px"
13. android:layout_height="wrap_content"
14. android:layout_toRightOf="@id/personid"
15. android:layout_alignTop="@id/personid"
16. android:gravity="center_horizontal"
17. android:id="@+id/name"
18. />
20. <TextView
21. android:layout_width="wrap_content"
22. android:layout_height="wrap_content"
23. android:layout_toRightOf="@id/name"
24. android:layout_alignTop="@id/name"
25. android:id="@+id/age"
26. />
28. </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>