Android -- SQLite 数据库创建，增删改查，事务处理

1. 概述

在Android平台上，集成了一个嵌入式关系型数据库—SQLite，SQLite3支持 NULL、INTEGER、REAL（浮点数字）、TEXT(字符串文本)和BLOB(二进制对象)数据类型，虽然它支持的类型只有五种，但实际上sqlite3也接受varchar(n)、char(n)、decimal(p,s) 等数据类型，只不过在运算或保存时会转成对应的五种数据类型。 SQLite最大的特点是你可以把各种类型的数据保存到任何字段中，而不用关心字段声明的数据类型是什么。例如：可以在Integer类型的字段中存放字符串，或者在布尔型字段中存放浮点数，或者在字符型字段中存放日期型值。但有一种情况例外：定义为INTEGER PRIMARY KEY的字段只能存储64位整数， 当向这种字段保存除整数以外的数据时，将会产生错误。 另外， SQLite 在解析CREATE TABLE 语句时，会忽略 CREATE TABLE 语句中跟在字段名后面的数据类型信息，如下面语句会忽略 name字段的类型信息：

CREATE TABLE person (personid integer primary key autoincrement, name varchar(20))

SQLite可以解析大部分标准SQL语句，如：

查询语句：select * from 表名 where 条件子句 group by 分组字句 having ... order by 排序子句

如：select * from person

        select * from person order by id desc

        select name from person group by name having count(*)>1

分页SQL与mysql类似，下面SQL语句获取5条记录，跳过前面3条记录

select * from Account limit 5 offset 3 或者 select * from Account limit 3,5

插入语句：insert into 表名(字段列表) values(值列表)。如： insert into person(name, age) values(‘传智’,3)

更新语句：update 表名 set 字段名=值 where 条件子句。如：update person set name=‘传智‘ where id=10

删除语句：delete from 表名 where 条件子句。如：delete from person  where id=10

 

为了实现对数据库版本进行管理，SQLiteOpenHelper类提供了两个重要的方法，分别是onCreate(SQLiteDatabase db)和onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)，前者用于初次使用软件时生成数据库表，后者用于升级软件时更新数据库表结构。当调用SQLiteOpenHelper的getWritableDatabase()或者getReadableDatabase()方法获取用于操作数据库的SQLiteDatabase实例的时候，如果数据库不存在，Android系统会自动生成一个数据库，接着调用onCreate()方法，onCreate()方法在初次生成数据库时才会被调用，在onCreate()方法里可以生成数据库表结构及添加一些应用使用到的初始化数据。onUpgrade()方法在数据库的版本发生变化时会被调用，一般在软件升级时才需改变版本号，而数据库的版本是由程序员控制的，假设数据库现在的版本是1，由于业务的变更，修改了数据库表结构，这时候就需要升级软件，升级软件时希望更新用户手机里的数据库表结构，为了实现这一目的，可以把原来的数据库版本设置为2(有同学问设置为3行不行？当然可以，如果你愿意，设置为100也行)，并且在onUpgrade()方法里面实现表结构的更新。当软件的版本升级次数比较多，这时在onUpgrade()方法里面可以根据原版号和目标版本号进行判断，然后作出相应的表结构及数据更新。

public class DatabaseHelper extends SQLiteOpenHelper {

    //类没有实例化,是不能用作父类构造器的参数,必须声明为静态

         private static final String name = "itcast";//数据库名称

         private static final int version = 1; //数据库版本

         public DatabaseHelper(Context context) {

//第三个参数CursorFactory指定在执行查询时获得一个游标实例的工厂类,设置为null,代表使用系统默认的工厂类

                super(context, name, null, version);

         }

        @Override public void onCreate(SQLiteDatabase db) {

              db.execSQL("CREATE TABLE IF NOT EXISTS person (personid integer primary key autoincrement, name varchar(20), age INTEGER)");  

         }

        @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

               db.execSQL(" ALTER TABLE person ADD phone VARCHAR(12) NULL ");//往表中增加一列

  // DROP TABLE IF EXISTS person 删除表

       }

}

在实际项目开发中，当数据库表结构发生更新时，应该避免用户存放于数据库中的数据丢失。

 

Android sqlite3工具的使用

1 cmd à adb shell 首先挂载到linux

2 cd data/data/com.android.contacts.provider

3 cd database

4 sqlite3 contacts 打开数据库 eg: sqlite3 contacts.db

5 .tables 查看所有的表  eg: .table

6 .schema 查看所有的创建表、视图的语句 eg: .schema

7 .help 查看帮助  eg: .help

8 .header(s) NO |OFF是否显示列头信息 eg: headers ON

9 .mode MODE  ?table? 指定数据显示风格 eg: .mode column

10 .nullValue NULL空值数据显示问题 eg: .nullValue NULL

 

2. 示例代码

NoteSQLiteOpenHelper.java， 继承实现抽象类SQLiteOpenHelper

public class NoteSQLiteOpenHelper extends SQLiteOpenHelper {

	private static final String TAG = "NoteSQLiteOpenHelper";
	/**
	 * context 上下文 name 数据库的名称 cursorfactory 游标工厂 一般设置null 默认游标工厂 version 数据库的版本
	 * 版本号从1开始的
	 * 
	 * @param context
	 */
	public NoteSQLiteOpenHelper(Context context) {
		super(context, "note.db", null, 3);
	}

	/**
	 * oncreate 方法 会在数据库第一创建的时候的是被调用 适合做数据库表结构的初始化
	 */
	@Override
	public void onCreate(SQLiteDatabase db) {
		Log.i(TAG, "oncreate 方法被调用了...");
		db.execSQL("create table account (id integer primary key autoincrement , name  varchar(20), money varchar(20) )");
	}
	
	/*
	* 当数据库的版本号发生变化的时候，会调用这个方法
	*/
	@Override
	public void onUpgrade(SQLiteDatabase db, int arg1, int arg2) {
		Log.i(TAG,"onupdate 方法被调用了 ,在这个方法里面做更新数据库表结构的操作");
		//db.execSQL(sql);  //alter table account add ... 添加修改表结构语句
	}

}

NoteBean.java， javabean

public class NoteBean {
	private int id;
	private float money;
	private String name;
	
	@Override
	public String toString() {
		return "NoteBean [id=" + id + ", money=" + money + ", name=" + name
				+ "]";
	}
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public float getMoney() {
		return money;
	}
	public void setMoney(float money) {
		this.money = money;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public NoteBean(int id, float money, String name) {
		this.id = id;
		this.money = money;
		this.name = name;
	}
	public NoteBean(){
		
	}
	
}

NoteDao.java, dao接口实现， 方式一：直接用SQL语句

/**
 * 记账本的dao
 * 
 * @author Administrator
 * 
 */
public class NoteDao {
	// 因为 任何一个操作都是需要 得到 NoteSQLiteOpenHelper helper
	// 把他放在构造方法里面初始化
	private NoteSQLiteOpenHelper helper;

	public NoteDao(Context context) {
		helper = new NoteSQLiteOpenHelper(context);
	}

	/**
	 * 添加一条账目信息 到数据库
	 * 
	 * @param name
	 *            花销的名称
	 * @param money
	 *            金额
	 */
	public void add(String name, float money) {
		SQLiteDatabase db = helper.getWritableDatabase();
		db.execSQL("insert into account (name,money) values (?,?)",
				new Object[] { name, money });
		// 记住 关闭.
		db.close();
	}

	public void delete(int id) {
		SQLiteDatabase db = helper.getWritableDatabase();
		db.execSQL("delete from account where id=?", new Object[] { id });
		db.close();
	}

	public void update(int id, float newmoney) {
		SQLiteDatabase db = helper.getWritableDatabase();
		db.execSQL("update account set money =? where id=?", new Object[] {
				newmoney, id });
		db.close();
	}

	/**
	 * 返回数据库所有的条目
	 * 
	 * @return
	 */
	public List<NoteBean> findAll() {
		// 得到可读的数据库
		SQLiteDatabase db = helper.getReadableDatabase();
		List<NoteBean> noteBeans = new ArrayList<NoteBean>();
		// 获取到数据库查询的结果游标
		Cursor cursor = db.rawQuery("select id,money,name from account ", null);
		while (cursor.moveToNext()) {
			int id = cursor.getInt(cursor.getColumnIndex("id"));
			String name = cursor.getString(cursor.getColumnIndex("name"));
			float money = cursor.getFloat(cursor.getColumnIndex("money"));
			NoteBean bean = new NoteBean(id, money, name);
			noteBeans.add(bean);
			bean = null;
		}

		db.close();
		return noteBeans;
	}

	/**
	 * 模拟一个转账的操作. 使用数据库的事务
	 * 
	 * @throws Exception
	 */
	public void testTransaction() throws Exception {
		// 得到可写的数据库
		SQLiteDatabase db = helper.getWritableDatabase();
		db.beginTransaction(); // 开始事务
		try {
			db.execSQL("update account set money = money - 5 where id=? ",
					new String[] { "2" });
			db.execSQL("update account set money = money + 5 where id=? ",
					new String[] { "3" });
			//标记数据库事务执行成功
			db.setTransactionSuccessful();
		} catch (Exception e) {
			// TODO: handle exception
		} finally {
			db.endTransaction();//关闭事务.
			db.close();
		}

	}

}

NoteDao2.java， dao方式二： 用android提供的API

public class NoteDao2 {
	private NoteSQLiteOpenHelper helper;

	public NoteDao2(Context context) {
		helper = new NoteSQLiteOpenHelper(context);
	}

	/**
	 * 添加一条账目信息 到数据库
	 * 
	 * @param name
	 *            花销的名称
	 * @param money
	 *            金额
	 * 
	 * @return true 插入成功 false 失败
	 */
	public boolean add(String name, float money) {
		SQLiteDatabase db = helper.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put("name", name);
		values.put("money", money);
		long rawid = db.insert("account", null, values);
		db.close();
		if (rawid > 0) {
			return true;
		} else {
			return false;
		}
	}

	public boolean delete(int id) {
		SQLiteDatabase db = helper.getWritableDatabase();
		int result = db.delete("account", "id=?", new String[] { id + "" });
		db.close();
		if (result > 0) {
			return true;
		} else {
			return false;
		}
	}

	public boolean update(int id, float newmoney) {
		SQLiteDatabase db = helper.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put("id", id);
		values.put("money", newmoney);
		int result = db.update("account", values, "id=?", new String[] { id
				+ "" });
		db.close();
		if (result > 0) {
			return true;
		} else {
			return false;
		}
	}

	/**
	 * 返回数据库所有的条目
	 * 
	 * @return
	 */
	public List<NoteBean> findAll() {
		// 得到可读的数据库
		SQLiteDatabase db = helper.getReadableDatabase();
		List<NoteBean> noteBeans = new ArrayList<NoteBean>();
		Cursor cursor = db.query("account", new String[] { "id", "name",
				"money" }, null, null, null, null, null);
		while (cursor.moveToNext()) {
			int id = cursor.getInt(cursor.getColumnIndex("id"));
			String name = cursor.getString(cursor.getColumnIndex("name"));
			float money = cursor.getFloat(cursor.getColumnIndex("money"));
			NoteBean bean = new NoteBean(id, money, name);
			noteBeans.add(bean);
			bean = null;
		}
		db.close();
		return noteBeans;

	}
}

TestNoteDao.java 测试类

public class TestNoteDao extends AndroidTestCase {
	NoteDao dao	; 
	
	
	/**
	 * 测试框架初始化完毕后  初始化数据的操作
	 */
	
	@Override
	protected void setUp() throws Exception {
		super.setUp();
		dao = new NoteDao(getContext());
	}
	/**
	 * 测试框架执行完毕后  擦屁股的操作
	 */
	@Override
	protected void tearDown() throws Exception {
		super.tearDown();
	}

	public void testAdd() throws Exception {
	
		for (int i = 0; i < 20; i++) {
			dao.add("3月"+i+"号打酱油", 2.58f+i);
		}
	}

	public void testupdate() throws Exception{
		//NoteDao dao = new NoteDao(getContext());
		dao.update(2, 9.88f);
	}
	
	public void testDelete() throws Exception{
		//NoteDao dao = new NoteDao(getContext());
		dao.delete(1);
	}
	
	
	public void testFindAll() throws Exception{
		//NoteDao dao = new NoteDao(getContext());
		List<NoteBean> beans = dao.findAll();
		for(NoteBean bean:beans){
			System.out.println(bean.toString());
		}
	}
	
	public void testTrans() throws Exception{
		dao.testTransaction();
	}
}