android 数据库操作

Android的数据库是用Sqlite3，关于增删改查的语句，其实与别的数据是区别并的大，网上也有很多，所以就不说这个问题。
这次主要是写写我在项目中用到数据库编码习惯。

以增删改查Book为例，先是结构：


跟着看Book类：

package com.lxb.dbdemo.model;

public class Book {

	private int id; // ID
	private String name; // 名称
	private float price; // 价格
	private boolean canSale; // 是否可售

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public float getPrice() {
		return price;
	}

	public void setPrice(float price) {
		this.price = price;
	}

	public boolean isCanSale() {
		return canSale;
	}

	public void setCanSale(boolean canSale) {
		this.canSale = canSale;
	}

	@Override
	public String toString() {
		return "Book [id=" + id + ", name=" + name + ", price=" + price
				+ ", canSale=" + canSale + "]";
	}

}

然后是比较重要的继承自SQLiteOpenHelper的类，这里主要是建表及升级数据库操作（升级数据库只要把数据库版本号往上提就行）：

package com.lxb.dbdemo.db;

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

public class BookDBHelper extends SQLiteOpenHelper {
	
	private static final String DATABASE_NAME = "book_db"; // 数据库名称
	private static final int DATABASE_VERSION = 1; // 数据库版本
	
	public static final String BOOK_TABLE = "book_table"; // 书表名
	public static final String BOOK_ID = "book_id"; // 书ID
	public static final String BOOK_NAME = "book_name"; // 书名
	public static final String BOOK_PRICE = "book_price"; // 书价格
	public static final String BOOK_CAN_SALE = "book_cansale"; // 是否可售
	
	public BookDBHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// 建表操作
		StringBuilder sb = new StringBuilder();
		sb.append("CREATE TABLE IF NOT EXISTS " + BOOK_TABLE + "(");
		sb.append(BOOK_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,");
		sb.append(BOOK_NAME + " VARCHAR(100),");
		sb.append(BOOK_PRICE + " VARCHAR(100),");
		sb.append(BOOK_CAN_SALE + " INTEGER)");
		db.execSQL(sb.toString());
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("DROP TABLE IF EXISTS " + BOOK_TABLE);
		onCreate(db);
	}

}

接下来是一个Dao类，实现了增删改查的方法等：

package com.lxb.dbdemo.db.dao;

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;

import com.lxb.dbdemo.db.BookDBHelper;
import com.lxb.dbdemo.model.Book;

public class BookDao {
	
	private BookDBHelper helper;
	private SQLiteDatabase db; // 数据库对象
	
	public BookDao(Context context) {
		helper = new BookDBHelper(context);
	}
	
	/**
	 * 打开数据库
	 */
	public void open() {
		if(db == null || !db.isOpen()) {
			db = helper.getWritableDatabase();
		}
	}
	
	/**
	 * 关闭数据库
	 */
	public void close() {
		db.close();
		db = null;
	}
	
	/**
	 * 检查数据库是否可用
	 * @return
	 */
	private boolean checkDb() {
		open();
		if(db != null && db.isOpen()) {
			return true;
		}
		return false;
	}
	
	/**
	 * 保存
	 * @param book
	 * @return
	 */
	public int saveBook(Book book) {
		ContentValues values = new ContentValues();
		values.put(BookDBHelper.BOOK_NAME, book.getName());
		values.put(BookDBHelper.BOOK_PRICE, book.getPrice());
		values.put(BookDBHelper.BOOK_CAN_SALE, book.isCanSale());
		if(checkDb()) {
			long id = db.insert(BookDBHelper.BOOK_TABLE, BookDBHelper.BOOK_ID, values);
			return Integer.parseInt(id + "");
		}
		return -1;
	}
	
	/**
	 * 根据ID删除
	 * @param id
	 * @return
	 */
	public int deleteBook(int id) {
		if(checkDb()) {
			int result = db.delete(BookDBHelper.BOOK_TABLE, BookDBHelper.BOOK_ID + "=?", new String[]{id + ""});
			return result;
		}
		return -1;
	}
	
	/**
	 * 删除
	 * @param book
	 * @return
	 */
	public int deleteBook(Book book) {
		if(book != null) {
			deleteBook(book.getId());
		}
		return -1;
	}
	
	/**
	 * 更新
	 * @param book
	 * @return
	 */
	public int updateBook(Book book) {
		if(book != null) {
			if(checkDb()) {
				ContentValues values = new ContentValues();
				values.put(BookDBHelper.BOOK_NAME, book.getName());
				values.put(BookDBHelper.BOOK_PRICE, book.getPrice());
				values.put(BookDBHelper.BOOK_CAN_SALE, book.isCanSale());
				return db.update(BookDBHelper.BOOK_TABLE, values, 
						BookDBHelper.BOOK_ID + "=?", new String[]{book.getId() + ""});
			}
		}
		return -1;
	}
	
	/**
	 * 根据ID查找
	 * @param id
	 * @return
	 */
	public Book getBook(int id) {
		if(checkDb()) {
			Cursor cursor = db.query(BookDBHelper.BOOK_TABLE, 
					null, BookDBHelper.BOOK_ID + "=?", new String[]{id + ""}, null, null, null);
			Book book = null;
			if(cursor != null && cursor.moveToFirst()) {
				book = new Book();
				book.setId(cursor.getInt(cursor.getColumnIndex(BookDBHelper.BOOK_ID)));
				book.setName(cursor.getString(cursor.getColumnIndex(BookDBHelper.BOOK_NAME)));
				book.setPrice(cursor.getFloat(cursor.getColumnIndex(BookDBHelper.BOOK_PRICE)));
				book.setCanSale(cursor.getInt(cursor.getColumnIndex(BookDBHelper.BOOK_CAN_SALE))
						== 1 ? true : false);
			}
			cursor.close();
			return book;
		}
		return null;
	}

	/**
	 * 查找全部
	 * @return
	 */
	public List<Book> getAllBooks() {
		if(checkDb()) {
			List<Book> books = new ArrayList<Book>();
			Cursor cursor = db.query(BookDBHelper.BOOK_TABLE, null, null, null, null, null, null);
			if(cursor != null) {
				while(cursor.moveToNext()) {
					Book book = new Book();
					book.setId(cursor.getInt(cursor.getColumnIndex(BookDBHelper.BOOK_ID)));
					book.setName(cursor.getString(cursor.getColumnIndex(BookDBHelper.BOOK_NAME)));
					book.setPrice(cursor.getFloat(cursor.getColumnIndex(BookDBHelper.BOOK_PRICE)));
					book.setCanSale(cursor.getInt(cursor.getColumnIndex(BookDBHelper.BOOK_CAN_SALE))
							== 1 ? true : false);
					books.add(book);
				}
			}
			cursor.close();
			return books;
		}
		return null;
	}

}

最后是使用的代码，以查为例：

		BookDao bookDao = new BookDao(activity);
		bookDao.open();
		Book book = bookDao.getBook(1);
		bookDao.close();

下面是Demo运行的截图：




最后是Demo的源码：
http://download.csdn.net/detail/shinay/4533586