android基本方式对数据库的操作

person表:

create table person(
_id integer primary key autoincrement,
name varchar(20),
age integer
);

<span style="font-size:14px;">package com.sqf.sql.db;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;


/**
 * 数据库帮助类，用于创建和管理数据库
 * */
public class PersonSQLiteOpenHelper extends SQLiteOpenHelper{

	private static final String TAG = "PersonSQLiteOpenHelper";

	/**
	 * 构造方法的四个参数
	 * 1.上下文
	 * 2.数据库的名称
	 * 3.游标工程
	 * 4.数据库的版本，从1开始
	 * */
	public PersonSQLiteOpenHelper(Context context, String name,
			CursorFactory factory, int version) {
		super(context, "sql.db", null, 2);
	}
	
	public PersonSQLiteOpenHelper(Context context) {
		super(context, "sql.db", null, 2);
	}

	/**
	 * 数据库第一次创建的时，回调此方法
	 * 初始化一些表
	 * */
	@Override
	public void onCreate(SQLiteDatabase db) {
		
		//操作数据库
		String sql = "create table person(_id integer primary key autoincrement,name varchar(20),age integer);";
		db.execSQL(sql);  //创建person表
	}
	
	
	/**
	 * 数据库版本号更新时回调此方法
	 * 更新数据库的内容(删除表，修改表，添加表)
	 * */
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		
		if(oldVersion == 1 && newVersion == 2){ //在person表中添加余额列balance
			Log.i(TAG, "数据库更新了");
			
			//在person表添加一列
			db.execSQL("alter table person add balance integer;");
		}
		
	}

}
</span>

<span style="font-size:14px;">package com.sqf.sql.dao;

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

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

import com.sqf.sql.db.PersonSQLiteOpenHelper;
import com.sqf.sql.entities.Person;

public class PersonDao {
	
	PersonSQLiteOpenHelper mOpenHelper;//数据库的帮助类
	
	public PersonDao(Context context){
		 mOpenHelper = new PersonSQLiteOpenHelper(context); 
	}
	
	/**
	 * 添加到person表一条数据
	 * */
	public void insert(Person person){
		
		SQLiteDatabase db = mOpenHelper.getWritableDatabase();
		if(db.isOpen()){  //如果数据库打开
			
			//db.execSQL("insert into person(name,age) values('lisi','1');");
			
			//执行添加到数据库的操作
			db.execSQL("insert into person(name,age) values(?,?)", new Object[]{person.getName(),person.getAge()});
			
			db.close(); //数据库关闭
		}
	}
	
	
	public void delete(int id){
		SQLiteDatabase db = mOpenHelper.getWritableDatabase();//获得可写入数据库对象
		if(db.isOpen()){
			db.execSQL("delete from person where _id = ?", new Integer[]{id});
		
			db.close();
		}
		
	}
	
	
	public void update(int id,String name){
		SQLiteDatabase db = mOpenHelper.getWritableDatabase();
		
		if(db.isOpen()){
			db.execSQL("update person set name = ? where id = ?", new Object[]{name,id});
			db.close();
		}
		
	}
	
	
	public List<Person>	 queryAll(){
		SQLiteDatabase db = mOpenHelper.getReadableDatabase();
		if(db.isOpen()){
			
			Cursor cursor = db.rawQuery("select _id,name,age from person", null);// 没有?  第二个参数为nul
			if(cursor != null && cursor.getCount() > 0){

				List<Person> personList = new ArrayList<Person>();
				
				int id;
				String name;
				int age;
				while(cursor.moveToNext()){
					 id = cursor.getInt(0); //取第0列的数据id
					 name = cursor.getString(1);
					 age = cursor.getInt(2);
					 personList.add(new Person(id,name,age));
				}
				db.close();
				return personList;
			}
			
			db.close();
		}
		
		return null;
	}
	
	
	public Person queryItem(int id){
		SQLiteDatabase db = mOpenHelper.getReadableDatabase();
		if(db.isOpen()){
			Cursor cursor = db.rawQuery("select _id,name,age from person where _id = ?", new String[]{id+""});
		
			if(cursor != null && cursor.moveToFirst()){
				int _id = cursor.getInt(0);
				String name = cursor.getString(1);
				int age = cursor.getInt(2);
			
				db.close();
				return new Person(_id,name,age);  
			}
			
			db.close();
		}
		
		return null;
	}
	
}
</span>

<span style="font-size:14px;">package com.sqf.sql.entities;

public class Person {
	
	private int id;
	private String name;
	private int age;
	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 int getAge() {
		return age;
	}
	public void setAge(int age) {
		this.age = age;
	}
	@Override
	public String toString() {
		return "Person [id=" + id + ", name=" + name + ", age=" + age + "]";
	}
	public Person(int id, String name, int age) {
		super();
		this.id = id;
		this.name = name;
		this.age = age;
	}
	public Person() {
		super();
	}
	
	
	
	
}
</span>

Junit测试:

package com.sqf.sql.test;

import java.util.List;

import com.sqf.sql.dao.PersonDao;
import com.sqf.sql.db.PersonSQLiteOpenHelper;
import com.sqf.sql.entities.Person;

import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.test.AndroidTestCase;
import android.util.Log;

public class TestCase extends AndroidTestCase {
	
	private static final String TAG = "TestCase";

	public void test(){
		
		//数据库什么时候创建
		PersonSQLiteOpenHelper openHelper = new PersonSQLiteOpenHelper(getContext());
		// 第一次连接数据库时创建数据库文件，并调用onCreate
		openHelper.getReadableDatabase();
	}

	
	public void testInsert(){
		PersonDao dao = new PersonDao(getContext());
		
		for(int i=0;i<20;i++){
			dao.insert(new Person(i,"大话"+i,20));
		}
	}
	
	public void testDelete(){
		PersonDao dao = new PersonDao(getContext());
		dao.delete(1);
	}
	
	public void testUpdate(){
		PersonDao dao = new PersonDao(getContext());
		dao.update(2, "哈哈");
	}

	public void testQueryAll(){
		PersonDao dao = new PersonDao(getContext());
		List<Person> queryAll = dao.queryAll();
		
		for(Person person:queryAll){
			Log.i(TAG, person.toString());
		}
	}
	
	public void testQuery(){
		PersonDao dao = new PersonDao(getContext());
		Person person = dao.queryItem(2);
		Log.i(TAG, person.toString());
	}
	
	
	
	public void testTransaction(){
		PersonSQLiteOpenHelper openHelper = new PersonSQLiteOpenHelper(getContext());
		SQLiteDatabase db = openHelper.getWritableDatabase();
		


		if(db.isOpen()){
			
			try {
				
				//开启事务
				db.beginTransaction();
				
				//从张三账户扣除1000
				db.execSQL("update person set balance =balance-1000 where name = 'zhangsan'");
				
				//ATM机挂了
//				 int a = 1/0; 
				
				//向李四账户加1000
				db.execSQL("update person set balance =balance+1000 where name = 'lisi'");
			
				//标记事务成功
				db.setTransactionSuccessful();//标记，进行提交
			
			} catch (SQLException e) {
				e.printStackTrace();
			} finally{
				//停止事务
				db.endTransaction();
			}
			
			db.close();
		}
		
	}
	
	
	public void testTransactionInsert(){
		PersonSQLiteOpenHelper openHelper = new PersonSQLiteOpenHelper(getContext());
		SQLiteDatabase db = openHelper.getWritableDatabase();
		//插入没有开启事务 消耗的毫秒37890
		//插入10000条 开启事务    消耗的毫秒6112

		if(db.isOpen()){
			//1.记录当前时间
			long start = System.currentTimeMillis();
			
			//2.开始添加数据
			try {
				db.beginTransaction();
				for(int i=0;i<10000;i++){
					db.execSQL("insert into person(name,age,balance) values('wang"+i+"',"+(10+i)+","+(10000+i)+")");
				}
				db.setTransactionSuccessful();
			} catch (SQLException e) {
				db.endTransaction();
				e.printStackTrace();
			}
			
			//3.记录结束时间,计算消耗
			long end = System.currentTimeMillis();
			
			long diff = end - start;
			
			Log.i(TAG, "消耗的毫秒" + diff);
			
			
			db.close();
		}
	}
	
}