Android 中的SQLite数据库的使用

由于手机内存小、携便性等特点，要求手机软件对一些数据的处理要做到轻量，在这我就SQLite数据库简单侃侃。

  SQLite是一种轻量级的数据库，在android中程序员无法在用户手机上手动创建数据库，这就使得我们必须用代码自动创建数据库。下面我就从数据库的创建到增删改查一一说明，希望对大家有所帮助.

1. 创建SQLite数据库

   首先得创建一个类DBOpenHelper继承SQLiteOpenHelper，SQLiteOpenHelper是一个抽象类，必须用子类实现其中的方法。

public class DBOpenHelper extends SQLiteOpenHelper
{
  private static final String DB_NAME = "pcar.db";//数据库名
  private static final int DB_VERSION = 1;//版本号

  //构造函数
  public DBOpenHelper(Context context)
  {
   super(context, DB_NAME, null, DB_VERSION);
  }

  @Override
  public void onCreate(SQLiteDatabase db)
  {
    //在该方法中创建数据库
    String sql="CREATE TABLE car(areaID VARCHAR(100) PRIMARY KEY NOT NULL,AreaCode VARCHAR(100),AreaName VARCHAR(100),AreaPrice float,EntryInTotal int,EntryOutTotal int)";
   //执行sql语句
    db.execSQL(sql);
  }

  @Override
  public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
  {
   // TODO Auto-generated method stub
   //该函数是在DB_VERSION（版本号）发生改变时执行
  }

}

以下代码为单元测试代码：

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

  public void testDBCreate()
  {
    DBOpenHelper helper = new DBOpenHelper(getContext());
    helper.getWritableDatabase();// 创建数据库
  }
}

执行完testDBCreate（）后如果成功的话，就会带data/data/自己的包下有一个databases文件夹，该文件夹下就会有一个pcar.db文件。就表明数据库创建成功。

查看database方法可百度。这里不再详述。

2，执行数据库的增删改查

package com.example.park.db;

import java.util.ArrayList;
import java.util.List;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import com.example.park.domain.ParkCount;

public class ParkCountService
{
  private DBOpenHelper dbOpenHelper;

  public ParkCountService(Context context)
  {
    super();
    dbOpenHelper = new DBOpenHelper(context);
  }

  /**
   * 保存数据
   * 
   * @param parkCount
   */
  public void save(ParkCount parkCount)
  {
    // 得到数据库的增删改查类SQLiteDatabase,用于管理和操作SQLite数据库，几乎所有的数据库操作，最终都将由这个类完成。
    SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
    db.execSQL(
        "insert into car(AreaID,AreaCode,AreaName,AreaPrice,EntryInTotal,EntryOutTotal)values(?,?,?,?,?,?)",
        new Object[]
        { parkCount.getAreaID(), parkCount.getAreaCode(), parkCount.getAreaName(), parkCount.getAreaPrice(),
            parkCount.getEntryInTotal(), parkCount.getEntryOutTotal() });

  }

  /**
   * 删除数据
   * 
   * @param areaID根据此ID删除数据
   */
  public void delete(int areaID)
  {
    SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
    db.execSQL("delete from car where AreaID=?", new Object[]
    { areaID });
  }

  /**
   * 更新数据
   * 
   * @param parkCount
   *          数据的对象
   */
  public void update(ParkCount parkCount)
  {
    SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
    db.execSQL("update car set AreaCode=?,AreaName=?,AreaPrice=?,EntryInTotal=?,EntryOutTotal=? where AreaID=?",
        new Object[]
        { parkCount.getAreaCode(), parkCount.getAreaName(), parkCount.getAreaPrice(), parkCount.getEntryInTotal(),
            parkCount.getEntryOutTotal(), parkCount.getAreaID() });
  }

  /**
   * 查询数据
   * 
   * @param id根据此id查询数据
   */
  public ParkCount find(int id)
  {
    SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
    Cursor cursor = db.rawQuery("select * from car where AreaID=?", new String[]
    { String.valueOf(id) });
    if (cursor.moveToFirst())
    {
      int areaID = cursor.getInt(cursor.getColumnIndex("areaID"));
      String areaCode = cursor.getString(cursor.getColumnIndex("AreaCode"));
      String areaName = cursor.getString(cursor.getColumnIndex("AreaName"));
      float areaPrice = cursor.getFloat(cursor.getColumnIndex("AreaPrice"));
      int entryInTotal = cursor.getInt(cursor.getColumnIndex("EntryInTotal"));
      int entryOutTotal = cursor.getInt(cursor.getColumnIndex("EntryOutTotal"));
      ParkCount count = new ParkCount(areaID, areaCode, areaName, areaPrice, entryInTotal, entryOutTotal);
      return count;
    }
    return null;
  }

  /**
   * 分页显示
   * 
   * @param offset
   *          获取多少条数据
   * @param maxResult跳过前几条数据
   * @return
   */
  public List<ParkCount> getScrollData(int offset, int maxResult)
  {
    List<ParkCount> parkList = new ArrayList<ParkCount>();
    SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
    Cursor cursor = db.rawQuery("select * from car order by AreaID asc limit ?,?", new String[]
    { String.valueOf(offset), String.valueOf(maxResult) });
    while (cursor.moveToNext())
    {
      int areaID = cursor.getInt(cursor.getColumnIndex("areaID"));
      String areaCode = cursor.getString(cursor.getColumnIndex("AreaCode"));
      String areaName = cursor.getString(cursor.getColumnIndex("AreaName"));
      float areaPrice = cursor.getFloat(cursor.getColumnIndex("AreaPrice"));
      int entryInTotal = cursor.getInt(cursor.getColumnIndex("EntryInTotal"));
      int entryOutTotal = cursor.getInt(cursor.getColumnIndex("EntryOutTotal"));
      ParkCount count = new ParkCount(areaID, areaCode, areaName, areaPrice, entryInTotal, entryOutTotal);
      parkList.add(count);
    }
    cursor.close();
    return parkList;
  }

  public long getCount()
  {
    SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
    Cursor cursor = db.rawQuery("select count(*) from car", null);
    cursor.moveToFirst();
    long result = cursor.getLong(0);
    cursor.close();
    return result;
  }
}

单元测试类

package com.example.park.test;

import java.util.List;

import android.test.AndroidTestCase;
import android.util.Log;
import com.example.park.db.DBOpenHelper;
import com.example.park.db.ParkCountService;
import com.example.park.domain.ParkCount;

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

  public void testDBCreate()
  {
    DBOpenHelper helper = new DBOpenHelper(getContext());
    helper.getWritableDatabase();// 创建数据库
  }

  public void testSave()
  {
    ParkCountService service = new ParkCountService(getContext());
    for (int i = 5; i < 20; i++)
    {
      ParkCount parkCount = new ParkCount(i, "0908" + i, "苏州" + i, (float) 15.0, 1, 1);
      service.save(parkCount);
    }
  }

  public void testDelete()
  {
    ParkCountService service = new ParkCountService(getContext());
    service.delete(0);
  }

  public void testUpdate()
  {
    ParkCountService service = new ParkCountService(getContext());
    ParkCount parkCount = new ParkCount(3, "09089", "观前", (float) 18.0, 8, 9);
    service.update(parkCount);
  }

  public void testFind()
  {
    ParkCountService service = new ParkCountService(getContext());
    Log.i(TAG, service.find(3).toString());
  }

  public void testCount()
  {
    ParkCountService service = new ParkCountService(getContext());
    Log.i(TAG, service.getCount() + "");
  }

  public void testScrollData()
  {
    ParkCountService service = new ParkCountService(getContext());
    List<ParkCount> parkCount = service.getScrollData(10, 5);
    for (ParkCount paCount : parkCount)
    {
      Log.i(TAG, paCount.toString());
    }
  }
}