android 数据库操作

另一篇：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();
		}
}		

 

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 + "]";
	}
}

 

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);
		}
}

 

一个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);
		}
	}
}

 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>

 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>