SqLite封转工具类

hyzqb

package com.cz.hello.utils;
import java.util.ArrayList;
import java.util.List;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import com.cz.hello.model.CityInfo;
public class Db {
private static final StringDB_NAME = "city.db";
private static final int DB_VERSION = 1;
private static Context mContext = null;
private SQLiteDatabase mSQLiteDatabase= null;
private DatabaseHelper mDatabaseHelper= null;
private static final StringTABLE_CITY        = "table_city";   //城市表
public static final StringKEY_ID           = "_id";         //数据id
public static final StringKEY_CITY_NAME     = "city_name";    //城市名称
public static final StringKEY_ADD_TIME     = "add_time";    //插入时间
private static final StringTABLE_CITY_CREATE = "CREATE TABLE " + TABLE_CITY
+ " (" + KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
+ KEY_ADD_TIME  + " datetime default (datetime('now', 'localtime')),"
+ KEY_CITY_NAME + " TEXT)" ;

private static class DatabaseHelper extends SQLiteOpenHelper
{
DatabaseHelper(Context context)
{
super(context, DB_NAME, null, DB_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db)
{
db.execSQL(TABLE_CITY_CREATE);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
{
db.execSQL("DROP TABLE IF EXISTS notes");
onCreate(db);
}
}
public Db(Context context){
mContext = context;
}
public void open() throws SQLException{
mDatabaseHelper = new DatabaseHelper(mContext);
mSQLiteDatabase = mDatabaseHelper.getWritableDatabase();
}
public void close(){
mDatabaseHelper.close();
}
/**
* 插入一条城市数据
*/
public void insertCityData(String cityName)
{
if(Utils.isEmpty(cityName)){
return;
}
open();
ContentValues initialValues = new ContentValues();
initialValues.put(KEY_CITY_NAME, cityName);
Cursor cursor = mSQLiteDatabase.query(TABLE_CITY, null, KEY_CITY_NAME+"=?", new String[]{cityName}, null, null, null);
if (cursor.moveToNext()) {
mSQLiteDatabase.update(TABLE_CITY, initialValues, KEY_CITY_NAME+"=?", new String[]{cityName});
}else{
mSQLiteDatabase.insert(TABLE_CITY, null, initialValues);
}
cursor.close();
close();
}
/**
* 获取所有城市列表数据
*/
public List<CityInfo> fetchAllCityData(){
List<CityInfo> list = new ArrayList<CityInfo>();
open();
Cursor cursor = mSQLiteDatabase.query(TABLE_CITY, new String[] {
KEY_ID,
KEY_CITY_NAME}, null, null, null, null, KEY_ADD_TIME+" desc");
while (cursor.moveToNext()) {
CityInfo city = new CityInfo();
city.cityname = cursor.getString(cursor.getColumnIndex(KEY_CITY_NAME));
city.id = cursor.getString(cursor.getColumnIndex(KEY_ID));
list.add(city);
}
cursor.close();
close();
return list;
}
/**
* @author chenzheng
* @since 2014-7-26
* @Description: 根据id删除数据
* @throws
* @param id
* @return
* boolean
*/
public void deleteCityData(String id){
open();
mSQLiteDatabase.delete(TABLE_CITY, KEY_ID + "=" + id, null);  
close();  
}
}