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);}/** * 数据库第一次创建的时，回调此方法 * 初始化一些表 * */@Overridepublic void onCreate(SQLiteDatabase db) {//操作数据库String sql = "create table person(_id integer primary key autoincrement,name varchar(20),age integer);";db.execSQL(sql);  //创建person表}/** * 数据库版本号更新时回调此方法 * 更新数据库的内容(删除表，修改表，添加表) * */@Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {if(oldVersion == 1 && newVersion == 2){ //在person表中添加余额列balanceLog.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);// 没有?  第二个参数为nulif(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;}@Overridepublic 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());// 第一次连接数据库时创建数据库文件，并调用onCreateopenHelper.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();//从张三账户扣除1000db.execSQL("update person set balance =balance-1000 where name = 'zhangsan'");//ATM机挂了// int a = 1/0; //向李四账户加1000db.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条 开启事务    消耗的毫秒6112if(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();}}}