数据库的管理SQLite

目前Android提供了两种SQLite的实现方法

1、一种是基于Android的封装接口来实现的，适合对数据库要求不高或者对SQLite不熟悉的开发者

SQLiteOpenHelper：通过继承这个类，开发者可以很容易的设计和操作数据库，注意封装会使Android的性能降低，在继承SQLiteOpenHelper时候必须实现onCreate(),onUpgrade()函数

public class DBHelper extends SQLiteOpenHelper{

 private static final String DB_NAME = "gps.db";
 private static final int VERSION = 1;
 
 /**
  * GPS表
  */
 public static final String GPS_TABLE = "GPSTBL";

 
 public DBHelper(Context context)
 {
  this(context, DB_NAME, null, VERSION);
 }
 
 public DBHelper(Context context, String name, CursorFactory factory,
   int version) {
  super(context, name, factory, version);
  // TODO Auto-generated constructor stub
 }

 
 @Override
 public void onCreate(SQLiteDatabase db) {
  // TODO Auto-generated method stub
  createGPS(db);
 }

 @Override
 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
  // TODO Auto-generated method stub
  
 }
 
 //创建gps表
 public void createGPS(SQLiteDatabase db){
  String sql1 = "create table "+GPS_TABLE+"(_id integer primary key autoincrement,longitude varchar(40),latitude varchar(40),distance varchar(40))";
  String sql2 = "create table "+GPS_TABLE+"(_id integer primary key autoincrement,longitude varchar(40),latitude varchar(40),distance varchar(40))";
  String sql3 = "create table "+GPS_TABLE+"(_id integer primary key autoincrement,longitude varchar(40),latitude varchar(40),distance varchar(40))";
  String sql4 = "create table "+GPS_TABLE+"(_id integer primary key autoincrement,longitude varchar(40),latitude varchar(40),distance varchar(40))";
  
  db.execSQL(sql1);
  db.execSQL(sql2);
  db.execSQL(sql3);
  db.execSQL(sql4);
 }
 
 //插入gps表
 public void insertGPS(String longitude,String latitude,String distance){
  ContentValues c = new ContentValues();
  c.put("longitude", longitude);
  c.put("latitude", latitude);
  c.put("distance", distance);
  DBHelper.this.getWritableDatabase().insert(GPS_TABLE, null, c);
 }
 
 //查找gps信息
 public List<String> listGPS(){
  List<String> list = new ArrayList<String>();
  
  SQLiteDatabase db = this.getWritableDatabase();
  Cursor c = null;

  try
  {
   c = db.query(GPS_TABLE, null, null, null,
     null, null, null, null);
   while (c.moveToNext())
   {
    String longitude = c.getString(c.getColumnIndex("longitude"));
    String latitude = c.getString(c.getColumnIndex("latitude"));
    String distance = c.getString(c.getColumnIndex("distance"));
    list.add(longitude+"-"+latitude+"-"+distance);
   }
  } catch (Exception e)
  {
   e.printStackTrace();
  } finally
  {
   c.close();
   db.close();
  }
  
  return list;
 }
 
 //删除gps信息
 public void deleteGPS(){
  SQLiteDatabase db = this.getWritableDatabase();
  db.delete(GPS_TABLE, null, null);
  db.close();
 }
}

当一次性需要修改多个数据的时候，建议使用SQLite的事务处理进行批量处理，这有利于提高执行效率

2、另外一种是基于原生的SQLite的方法来实现的，可以完全发挥出SQLite的能力，性能稍好

public class SqlStore {
 public static final int SDCARDSTORE = 2;
 private static SQLiteDatabase localSQLiteDatabase;
 File path = new File("/sdcard/gps_distance");// 创建目录
 File fdb = new File("/sdcard/gps_distance/distance.db");// 创建文件
 public static final ArrayList<String> table=new ArrayList<String>();
 public static final ArrayList<String> sqlStr=new ArrayList<String>();
 public static final String GPS_TABLE1 = "GPSTBL1";//表名
 public static final String GPS_TABLE2 = "GPSTBL2";//表名
 public static final String GPS_TABLE3 = "GPSTBL3";//表名
 public static final String GPS_TABLE4 = "GPSTBL4";//表名

 public SqlStore() {
  
  File path;
  File fdb;
  path = this.path;
  fdb = this.fdb;
  table.add(GPS_TABLE1);
  table.add(GPS_TABLE2);
  table.add(GPS_TABLE3);
  table.add(GPS_TABLE4);
  if (!path.exists()) {
   path.mkdir(); // 没有该文件夹则创建
  }
  if (!fdb.exists()) {
   try {
    fdb.createNewFile();
   } catch (IOException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
   } // 没有该文件则创建
  }

  // 将数据库的文件创建在SD卡中
  localSQLiteDatabase = SQLiteDatabase.openOrCreateDatabase(fdb, null);
  // 创建数据库中的表
  // ///

  // 将数据库的文件创建在SD卡中

//  String newTablestr = "CREATE TABLE GPS_TABLE (_id INTEGER PRIMARY KEY AUTOINCREMENT, arrayID TEXT, orderID INTEGER, dates TEXT, times TEXT, len BLOB, addUpLen BLOB);";
  String sql1 = "create table "+GPS_TABLE1+"(_id integer primary key autoincrement,longitude varchar(40),latitude varchar(40),distance varchar(40))";
  String sql2 = "create table "+GPS_TABLE2+"(_id integer primary key autoincrement,longitude varchar(40),latitude varchar(40),distance varchar(40))";
  String sql3 = "create table "+GPS_TABLE3+"(_id integer primary key autoincrement,longitude varchar(40),latitude varchar(40),distance varchar(40))";
  String sql4 = "create table "+GPS_TABLE4+"(_id integer primary key autoincrement,longitude varchar(40),latitude varchar(40),distance varchar(40))";
  sqlStr.add(sql1);
  sqlStr.add(sql2);
  sqlStr.add(sql3);
  sqlStr.add(sql4);
  for(int i=0;i<4;i++){
   if (!tabIsExist(table.get(i), SDCARDSTORE)) {
    try{
    localSQLiteDatabase.execSQL(sqlStr.get(i));
    }catch(Exception e){}
   }
  }

  // //

 }
 
 //插入gps表
  public void insertGPS(String longitude,String latitude,String distance,int num){
   localSQLiteDatabase = SQLiteDatabase.openOrCreateDatabase(fdb, null);
   ContentValues c = new ContentValues();
   c.put("longitude", longitude);
   c.put("latitude", latitude);
   c.put("distance", distance);
   localSQLiteDatabase.insert(table.get(num), null, c);
   
  }
  
  //查找gps信息
  public List<String> listGPS(){
   List<String> list = new ArrayList<String>();
   
   
   localSQLiteDatabase = SQLiteDatabase.openOrCreateDatabase(fdb, null);
   Cursor c = null;

   try
   {
    c = localSQLiteDatabase.query(table.get(0), null, null, null,
      null, null, null, null);
    while (c.moveToNext())
    {
     String longitude = c.getString(c.getColumnIndex("longitude"));
     String latitude = c.getString(c.getColumnIndex("latitude"));
     String distance = c.getString(c.getColumnIndex("distance"));
     list.add(longitude+"-"+latitude+"-"+distance);
    }
   } catch (Exception e)
   {
    e.printStackTrace();
   } finally
   {
    c.close();
    localSQLiteDatabase.close();
   }
   
   return list;
  }
  
  //删除gps信息
  public void deleteGPS(){
   localSQLiteDatabase = SQLiteDatabase.openOrCreateDatabase(fdb, null);
   localSQLiteDatabase.delete(table.get(0), null, null);
   localSQLiteDatabase.close();
  }
 // 判断数据库中的表是否存在
 public boolean tabIsExist(String tabName, int pathFlag) {
  boolean result = false;
  if (pathFlag == SDCARDSTORE) {

   if (!fdb.exists()) {
    return false;
   }
   // 将数据库的文件创建在SD卡中
   localSQLiteDatabase = SQLiteDatabase
     .openOrCreateDatabase(fdb, null);
  }
  if (tabName == null) {
   return false;
  }
  Cursor cursor = null;
  try {

   cursor = localSQLiteDatabase.query(tabName, null, null, null, null,
     null, null); // 模糊搜索
   if (cursor.moveToNext()) {
    int count = cursor.getInt(0);
    if (count > 0) {
     result = true;
    }
   }

  } catch (Exception e) {
   // TODO: handle exception
   e.printStackTrace();
  }
  return result;
 }
 

}

execSQL()和rawQuery()方法。 execSQL()方法可以执行insert、delete、update和CREATE TABLE之类有更改行为的SQL语句； rawQuery()方法用于执行select语句。