目前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语句。