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();
    }
  }
  
}