Android教程網 >> Android技術 >> 關於Android編程 >> (Android review)SQLite

(Android review)SQLite

編輯：關於Android編程

其實，這一部分，總的來說，主要寫DBOpenHelper類和PersonDao類即可。。。。

其中DBOpenHelper主要封裝了數據庫的創建、表的創建與修改。。而PersonDao主要封裝對表的DAO操作。。。。

1、DBOpenHelper

package com.example.sqlitetest;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DBOpenHelper extends SQLiteOpenHelper {

	
	
	public DBOpenHelper(Context context){//父類沒有無參構造函數,必須顯示調用有參構造函數.
		/*
		 * 由於父類沒有無參構造函數, 必須顯式調用有參的構造函數
		 * 參數1: 上下文環境, 用來確定數據庫文件存儲的目錄..創建的數據庫存在於/data/data/應用的報名/databases/xxx.db
		 * 參數2: 數據庫文件的名字
		 * 參數3: 生成游標的工廠, 填null就是使用默認的
		 * 參數4: 數據庫的版本, 從1開始
		 */
		super(context,njupt.db,null,2);
	}
	
	
	/**
	 * 一般來說,創建表的代碼放在onCreate()中
	 */
	@Override
	public void onCreate(SQLiteDatabase db) {
		System.out.println(----------->onCreate);
		db.execSQL(CREATE TABLE person(id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(20)));		// 執行SQL語句, 創建表
	}

	/**
	 * 修改表的代碼放在onUpdate()中...
	 */
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		System.out.println(---------->onUpdate);
		
		db.execSQL(ALTER TABLE person ADD balance INTEGER);
	}

}

2、PersonDao

package com.example.sqlitetest;

import java.util.ArrayList;
import java.util.List;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public class PersonDao {

	private Context context;
	private DBOpenHelper helper;
	
	public PersonDao(Context context) {
		this.context = context;
		helper = new DBOpenHelper(context);
	}
	
	public void insert(Person p){
		SQLiteDatabase db = helper.getWritableDatabase();
		db.execSQL(INSERT INTO person(name, balance) VALUES(?, ?), new Object[] { p.getName(), p.getBalance() });		// 執行SQL語句, 插入
		db.close();
	}
	
	public void delete(int id){
		SQLiteDatabase db = helper.getWritableDatabase();
		db.execSQL(DELETE FROM person WHERE id=?, new Object[]{id});
		db.close();
	}
	
	public void update(Person p){
		SQLiteDatabase db = helper.getWritableDatabase();
		db.execSQL(UPDATE person SET name=?, balance=? WHERE id=?, new Object[]{p.getName(),p.getBalance(),p.getId()});
		db.close();		
	}
	
	public Person query(int id){
		SQLiteDatabase db = helper.getReadableDatabase();//獲取數據庫連接,可讀的
		Cursor c = db.rawQuery(SELECT name, balance FROM person WHERE id=?, new String[]{id + });
		
		Person p = null;
		if(c.moveToNext()){//判斷游標是否包含下一條記錄,如果包含,將游標向後移一位
			
			String name = c.getString(0);//獲取0號索引上的數據,轉換為String類型
//			String name = c.getString(c.getColumnIndex(name));//這種方法也是極好的
			int balance = c.getInt(1);
			
			p = new Person(id, name, balance);
		}
		c.close();
		db.close();
		return p;
	}
	
	public List queryAll(){
		SQLiteDatabase db = helper.getReadableDatabase();
		Cursor c = db.rawQuery(SELECT id, name, balance FROM person, null);
		List persons = new ArrayList();
		while(c.moveToNext()){
			Person p = new Person(c.getInt(0), c.getString(1), c.getInt(2));
			persons.add(p);
		}
		c.close();
		db.close();
		
		return persons;
	}
	
	public int queryCount(){
		SQLiteDatabase db = helper.getReadableDatabase();
		Cursor c = db.rawQuery(SELECT COUNT(*) FROM person, null);
		
		c.moveToNext();
		int count = c.getInt(0);
		
		c.close();
		db.close();
		
		return count;
	}
	
	public List queryPage(int pageNum,int capacity){
		String offset = (pageNum - 1)*capacity + ;
		String len = capacity + ;
		SQLiteDatabase db = helper.getReadableDatabase();
		Cursor c = db.rawQuery(SELECT id, name, balance FROM person LIMIT ?,?, new String[]{offset,len});
		List persons = new ArrayList();
		
		while(c.moveToNext()){
			Person p = new Person(c.getInt(0), c.getString(1), c.getInt(2));
			persons.add(p);
		}
		
		c.close();
		db.close();
		
		return persons;
	}
	
	/**
	 * 數據庫的事務
	 * 
	 * 從from這個賬戶匯款到to這個賬戶,匯amount這個多錢
	 * @param from 
	 * @param to
	 * @param amount
	 */
	public void remit(int from,int to,int amount){
		SQLiteDatabase db = helper.getWritableDatabase();
		try{
			db.beginTransaction();//開始事務
			db.execSQL(UPDATE person SET balance=balance-? WHERE id=?, new Object[] { amount, from });
			db.execSQL(UPDATE person SET balance=balance+? WHERE id=?, new Object[] { amount, to });
			db.setTransactionSuccessful();//設置事務成功點...結束事務的時候執行成功點之前的sql語句
		}finally{
			db.endTransaction();//結束事務
			db.close();
		}
	}
}

3、Person

package com.example.sqlitetest;

public class Person {

	private Integer id;
	private String name;
	private Integer balance;
	
	public Person() {
		super();
	}

	public Person(Integer id, String name, Integer balance) {
		super();
		this.id = id;
		this.name = name;
		this.balance = balance;
	}

	@Override
	public String toString() {
		return Person [id= + id + , name= + name + , balance= + balance
				+ ];
	}

	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 getBalance() {
		return balance;
	}

	public void setBalance(Integer balance) {
		this.balance = balance;
	}
	
	
	
}

4、DBTest

package com.example.sqlitetest;

import java.util.List;

import android.test.AndroidTestCase;

public class DBTest extends AndroidTestCase {

	public void testCreateDB(){
		DBOpenHelper helper = new DBOpenHelper(getContext());
		helper.getWritableDatabase();
	}
	
	
	public void testInsert(){
		PersonDao dao = new PersonDao(getContext());
		
		for(int i = 1 ;  i < 100 ; ++i){
			dao.insert(new Person(i,hjd + i , 45000+i));
		}
//		dao.insert(new Person(2, hjd, 40000));
	}
	
	public void testDelete(){
		PersonDao dao = new PersonDao(getContext());
		dao.delete(1);
	}
	
	public void testUpdate(){
		PersonDao dao = new PersonDao(getContext());
		
		Person p = new Person(2, zzt, 10000);
		dao.update(p);
	}
	
	public void testQuery(){
		PersonDao dao = new PersonDao(getContext());
		System.out.println(dao.query(2));
	}
	
	public void testQueryAll(){
		PersonDao dao = new PersonDao(getContext());
		List persons = dao.queryAll();

		for(Person p : persons){
			System.out.println(p);
		}
	}
	
	public void testQueryCount(){
		PersonDao dao = new PersonDao(getContext());
		
		System.out.println(---------> + dao.queryCount());
		
	}
	
	public void testQueryPage(){
		PersonDao dao = new PersonDao(getContext());
		
		List persons = dao.queryPage(5, 10);
		
		for(Person p : persons){
			System.out.println(p);
		}
	}
	
}

5、MainActivity

package com.example.sqlitetest;

import android.os.Bundle;
import android.app.Activity;
import android.view.Menu;

public class MainActivity extends Activity {

	@Override
	protected void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.activity_main);
	}

	@Override
	public boolean onCreateOptionsMenu(Menu menu) {
		// Inflate the menu; this adds items to the action bar if it is present.
		getMenuInflater().inflate(R.menu.main, menu);
		return true;
	}

}

以上的PersonDao中所使用到的增刪改查的實現方法都是需要直接寫sql，其實還有一種方式來實現增刪改查。

其實其底層實現也是將穿的哪些參數拼成sql語句的。。

以下將完整的PersonDao和DBTest貼出來

PersonDao

package com.example.sqlitetest;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public class PersonDao {

	private Context context;
	private DBOpenHelper helper;

	public PersonDao(Context context) {
		this.context = context;
		helper = new DBOpenHelper(context);
	}

	public void insert(Person p) {
		SQLiteDatabase db = helper.getWritableDatabase();
		db.execSQL(INSERT INTO person(name, balance) VALUES(?, ?),
				new Object[] { p.getName(), p.getBalance() }); // 執行SQL語句, 插入
		db.close();
	}

	/**
	 * 某些情況之下程序會接受一個ContentValues,這時候采用這種存儲方式比較方便...
	 * @param p
	 */
	public void insert1(Person p) {
		SQLiteDatabase db = helper.getWritableDatabase();

		/**
		 * ContentValues:類似於Map,鍵放列名,值放要插入的內容...
		 * 為什麼說ContentValues類似於Map,其實看他的成員變量及put方法就知道了 Adds a value to the set.
		 * 
		 * @param key
		 *            the name of the value to put
		 * @param value
		 *            the data for the value to put
		 * 
		 *            public void put(String key, String value) {
		 *            mValues.put(key, value); }
		 */
		ContentValues values = new ContentValues();
		values.put(name, p.getName());
		values.put(balance, p.getName());

		/**
		 * 第二個參數隨便寫一個列名,用於處理values值為空的情況..因為列名不能為null
		 */
		db.insert(person, name, values);//其實它底層也是采用拼SQL語句的方式的.返回插入的id是多少
		db.close();
	}

	public void delete(int id) {
		SQLiteDatabase db = helper.getWritableDatabase();
		db.execSQL(DELETE FROM person WHERE id=?, new Object[] { id });
		db.close();
	}

	public void delete1(int id){
		SQLiteDatabase db = helper.getWritableDatabase();
		db.delete(person, id=?, new String[]{id + });
		db.close();
	}
	
	public void update(Person p) {
		SQLiteDatabase db = helper.getWritableDatabase();
		db.execSQL(UPDATE person SET name=?, balance=? WHERE id=?,
				new Object[] { p.getName(), p.getBalance(), p.getId() });
		db.close();
	}

	public void update1(Person p){
		SQLiteDatabase db = helper.getWritableDatabase();
		
		ContentValues values = new ContentValues();
		values.put(name, p.getName());
		values.put(balance, p.getBalance());
		
		db.update(person, values, id=?, new String[]{p.getId() + });
	    db.close();
	}
	public Person query(int id) {
		SQLiteDatabase db = helper.getReadableDatabase();// 獲取數據庫連接,可讀的
		Cursor c = db.rawQuery(SELECT name, balance FROM person WHERE id=?,
				new String[] { id +  });

		Person p = null;
		if (c.moveToNext()) {// 判斷游標是否包含下一條記錄,如果包含,將游標向後移一位

			String name = c.getString(0);// 獲取0號索引上的數據,轉換為String類型
			// String name = c.getString(c.getColumnIndex(name));//這種方法也是極好的
			int balance = c.getInt(1);

			p = new Person(id, name, balance);
		}
		c.close();
		db.close();
		return p;
	}

	public Person query1(int id){
		SQLiteDatabase db = helper.getReadableDatabase();// 獲取數據庫連接,可讀的
//		Cursor c = db.rawQuery(SELECT name, balance FROM person WHERE id=?,
//				new String[] { id +  });
		
		/**
		 * db.query(表名,需要查詢的列名,查詢條件,查詢條件的參數,group by,having,order by);
		 */
		Cursor c = db.query(person, new String[]{name,balance}, id=?, new String[]{id + }, null, null, null);
		
		Person p = null;
		if (c.moveToNext()) {// 判斷游標是否包含下一條記錄,如果包含,將游標向後移一位

			String name = c.getString(0);// 獲取0號索引上的數據,轉換為String類型
			// String name = c.getString(c.getColumnIndex(name));//這種方法也是極好的
			int balance = c.getInt(1);

			p = new Person(id, name, balance);
		}
		c.close();
		db.close();
		return p;
	}
	
	public List queryAll() {
		SQLiteDatabase db = helper.getReadableDatabase();
		Cursor c = db.rawQuery(SELECT id, name, balance FROM person, null);
		List persons = new ArrayList();
		while (c.moveToNext()) {
			Person p = new Person(c.getInt(0), c.getString(1), c.getInt(2));
			persons.add(p);
		}
		c.close();
		db.close();

		return persons;
	}

	public List queryAll1(){
		SQLiteDatabase db = helper.getReadableDatabase();
//		Cursor c = db.rawQuery(SELECT id, name, balance FROM person, null);
		
		Cursor c = db.query(person, null, null, null, null, null, id DESC);
		
		List persons = new ArrayList();
		while (c.moveToNext()) {
			Person p = new Person(c.getInt(0), c.getString(1), c.getInt(2));
			persons.add(p);
		}
		c.close();
		db.close();

		return persons;
	}
	
	public int queryCount() {
		SQLiteDatabase db = helper.getReadableDatabase();
//		Cursor c = db.rawQuery(SELECT COUNT(*) FROM person, null);
		Cursor c = db.query(person, new String[]{COUNT(*)}, null, null, null, null, null);
		c.moveToNext();
		int count = c.getInt(0);

		c.close();
		db.close();

		return count;
	}

	public int queryCount1(){
		SQLiteDatabase db = helper.getReadableDatabase();
		Cursor c = db.rawQuery(SELECT COUNT(*) FROM person, null);

		c.moveToNext();
		int count = c.getInt(0);

		c.close();
		db.close();

		return count;
	}
	
	public List queryPage(int pageNum, int capacity) {
		String offset = (pageNum - 1) * capacity + ;
		String len = capacity + ;
		SQLiteDatabase db = helper.getReadableDatabase();
		Cursor c = db.rawQuery(
				SELECT id, name, balance FROM person LIMIT ?,?, new String[] {
						offset, len });
		List persons = new ArrayList();

		while (c.moveToNext()) {
			Person p = new Person(c.getInt(0), c.getString(1), c.getInt(2));
			persons.add(p);
		}

		c.close();
		db.close();

		return persons;
	}

	
	public List queryPage1(int pageNum,int capacity){
		String offset = (pageNum - 1) * capacity + ;
		String len = capacity + ;
		SQLiteDatabase db = helper.getReadableDatabase();
//		Cursor c = db.rawQuery(
//				SELECT id, name, balance FROM person LIMIT ?,?, new String[] {
//						offset, len });
		
		Cursor c = db.query(person,null, null, null, null, null, null,offset + , + len);
		List persons = new ArrayList();

		while (c.moveToNext()) {
			Person p = new Person(c.getInt(0), c.getString(1), c.getInt(2));
			persons.add(p);
		}

		c.close();
		db.close();

		return persons;
	}
	/**
	 * 數據庫的事務
	 * 
	 * 從from這個賬戶匯款到to這個賬戶,匯amount這個多錢
	 * 
	 * @param from
	 * @param to
	 * @param amount
	 */
	public void remit(int from, int to, int amount) {
		SQLiteDatabase db = helper.getWritableDatabase();
		try {
			db.beginTransaction();// 開始事務
			db.execSQL(UPDATE person SET balance=balance-? WHERE id=?,
					new Object[] { amount, from });
			db.execSQL(UPDATE person SET balance=balance+? WHERE id=?,
					new Object[] { amount, to });
			db.setTransactionSuccessful();// 設置事務成功點...結束事務的時候執行成功點之前的sql語句
		} finally {
			db.endTransaction();// 結束事務
			db.close();
		}
	}
}

DBTest

package com.example.sqlitetest;

import java.util.List;

import android.test.AndroidTestCase;

public class DBTest extends AndroidTestCase {

	public void testCreateDB(){
		DBOpenHelper helper = new DBOpenHelper(getContext());
		helper.getWritableDatabase();
	}
	
	/**
	 * 需要注意的是，在這個例子中,person表中的id是自增的....不收Person中的id這個成員變量影響...
	 */
	
	public void testInsert(){
		PersonDao dao = new PersonDao(getContext());
		
		for(int i = 1 ;  i < 100 ; ++i){
			dao.insert(new Person(i,hjd + i , 45000+i));
		}
//		dao.insert(new Person(2, hjd, 40000));
	}
	
	public void testInsert1(){
		PersonDao dao = new PersonDao(getContext());
		
		for(int i = 0 ; i < 100 ; ++i){
			dao.insert1(new Person(i, hjd + i, 30000));
		}
//		dao.insert1(new Person(3, dzdp, 10000));
	}
	
	public void testDelete(){
		PersonDao dao = new PersonDao(getContext());
		dao.delete(1);
	}
	
	public void testDelete1(){
		PersonDao dao = new PersonDao(getContext());
		dao.delete1(1);
	}
	
	public void testUpdate(){
		PersonDao dao = new PersonDao(getContext());
		
		Person p = new Person(2, zzt, 10000);
		dao.update(p);
	}
	
	public void testUPdate1(){
		PersonDao dao = new PersonDao(getContext());
		
		Person p = new Person(2, hjd, 40000);
		dao.update1(p);
	}
	public void testQuery(){
		PersonDao dao = new PersonDao(getContext());
		System.out.println(dao.query(2));
	}
	
	public void testQuery1(){
		PersonDao dao = new PersonDao(getContext());
		System.out.println(------------> + dao.query1(2));
	}
	
	public void testQueryAll(){
		PersonDao dao = new PersonDao(getContext());
		List persons = dao.queryAll();

		for(Person p : persons){
			System.out.println(p);
		}
	}
	
	public void testQueryAll1(){
		PersonDao dao = new PersonDao(getContext());
		List persons = dao.queryAll1();

		for(Person p : persons){
			System.out.println( -----------> + p);
		}
	}
	
	public void testQueryCount(){
		PersonDao dao = new PersonDao(getContext());
		
		System.out.println(---------> + dao.queryCount());
		
	}
	
	public void testQueryCount1(){
		PersonDao dao = new PersonDao(getContext());
		
		System.out.println(------->queryCount1: + dao.queryCount1());
	}
	
	public void testQueryPage(){
		PersonDao dao = new PersonDao(getContext());
		
		List persons = dao.queryPage(5, 10);
		
		for(Person p : persons){
			System.out.println(p);
		}
	}
	
	public void testQueryPage1(){
		PersonDao dao = new PersonDao(getContext());
		List persons = dao.queryPage1(2, 10);
		
		for(Person p : persons){
			System.out.println(---------> + p);
		}
		
	}
	
}

上一頁:Android JNI用於驅動測試
下一頁:實例介紹 Java(android) 回調函數使用方法

關於Android編程

Android編程入門 Android技術基礎 Android資訊關於Android編程 Android開發 Android開發環境 Android開發實例

相關文章