一:帮助类:
/**
* Created by jiangxiangfei on 2016/1/2.
*/
public class MySQLiteOpenHelper extends SQLiteOpenHelper{
private static final String DATABASE_NAME = "list.db";
private static final int DATABASE_VERSION = 2;
public MySQLiteOpenHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
//注意:openhelper的调用时创建或者打开一个数据库:如果是第一次才会执行oncreat方法创建表 其他的时候不会执行 只有当版本号不一致的时候才会调用 onupgrade方法
//注意 oncreat或者onupgrade的方法执行会传进一个默认的给你创建或者修改表结构
// 将现有表重命名为临时表;
//
// 创建新表;
//
// 将临时表的数据导入新表(注意处理修改的列);
//
// 删除临时表。
@Override
public void onCreate(SQLiteDatabase db) {
Log.e("jxf","执行oncreat方法");
db.execSQL("CREATE TABLE 'schedule' ('id1' integer PRIMARY KEY AUTOINCREMENT ,'id' bigint(11),'title' varchar(100),'teacher_name' varchar(100),'teacher_img_small' varchar(256),'teacher_img_big' varchar(256),'start_time' datetime,'end_time' datetime,'province_name' varchar(100),'city_name' varchar(100),'address' varchar(256),'price' bigint(11),'again_price' bigint(11),'sitin_price' bigint(11),'ask_price' bigint(11),'url' varchar(256),'color' varchar(256),'price_type' varchar(256))");
db.execSQL("CREATE TABLE 'home' ('id1' integer PRIMARY KEY AUTOINCREMENT ,'id' bigint(11),'title' varchar(100),'icon' varchar(256),'creat_time' datetime,'url' varchar(256),'update_time' datetime)");
db.execSQL("CREATE TABLE 'contents' ('id1' integer PRIMARY KEY AUTOINCREMENT ,'teacher_id' bigint(11),'portraiturl_big' varchar(256),'portraiturl_small' varchar(256),'nickname' varchar(256),'introduce' varchar(256),'catgory_name' varchar(256),'catgory_id' varchar(256),'remark' text,'pricerange' text,'sort' bigint(11),'is_thumbs' bigint(11),'score' bigint(11),'thumbs_count' bigint(11))");
db.execSQL("CREATE TABLE 'channel' ('id1' integer PRIMARY KEY AUTOINCREMENT ,'id' bigint(11),'channelname' text,'price' bigint(11),'categoryname' varchar(256),'iconurl' varchar(256),'des' text,'teacher_id' bigint(11))");
db.execSQL("CREATE TABLE 'channel2' ('id1' integer PRIMARY KEY AUTOINCREMENT ,'id' bigint(11),'channelname' text,'price' bigint(11),'categoryname' varchar(256),'iconurl' varchar(256),'des' text,'teacher_id' bigint(11),'categoryid' bigint(11))");
db.execSQL("CREATE TABLE 'customer' ('id' integer PRIMARY KEY AUTOINCREMENT,'name' varchar(256),'mobile' varchar(100),'identitycard' varchar(100),'agent' varchar(256))");
}
//注意小版本的时候可以做数据库的历史存储 创建原来的表:键临时表中的数据存储进新建的原来的空数据库 做表 迁移 在删除临时的数据库 大版本直接删除数据库
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
Log.e("jxf","执行onUpgrade方法");
//版本1的数据库
// db.execSQL("CREATE TABLE 'schedule' ('id1' integer PRIMARY KEY AUTOINCREMENT ,'id' bigint(11),'title' varchar(100),'teacher_name' varchar(100),'teacher_img_small' varchar(256),'teacher_img_big' varchar(256),'start_time' datetime,'end_time' datetime,'province_name' varchar(100),'city_name' varchar(100),'address' varchar(256))");
// db.execSQL("CREATE TABLE 'home' ('id1' integer PRIMARY KEY AUTOINCREMENT ,'id' bigint(11),'title' varchar(100),'icon' varchar(256),'creat_time' datetime,'url' varchar(256),'update_time' datetime)");
// db.execSQL("CREATE TABLE 'contents' ('id1' integer PRIMARY KEY AUTOINCREMENT ,'teacher_id' bigint(11),'portraiturl_big' varchar(256),'portraiturl_small' varchar(256),'nickname' varchar(256),'introduce' varchar(256),'catgory_name' varchar(256),'catgory_id' varchar(256),'remark' text,'pricerange' text,'sort' bigint(11))");
// db.execSQL("CREATE TABLE 'channel' ('id1' integer PRIMARY KEY AUTOINCREMENT ,'id' bigint(11),'channelname' text,'price' bigint(11),'categoryname' varchar(256),'iconurl' varchar(256),'des' text,'teacher_id' bigint(11))");
if (oldVersion==1){
db.execSQL("Drop TABLE 'schedule'");
db.execSQL("Drop TABLE 'home'");
db.execSQL("Drop TABLE 'contents'");
db.execSQL("Drop TABLE 'channel'");
//版本升级要做的操作 需要创建的新表 版本2的数据库
db.execSQL("CREATE TABLE 'schedule' ('id1' integer PRIMARY KEY AUTOINCREMENT ,'id' bigint(11),'title' varchar(100),'teacher_name' varchar(100),'teacher_img_small' varchar(256),'teacher_img_big' varchar(256),'start_time' datetime,'end_time' datetime,'province_name' varchar(100),'city_name' varchar(100),'address' varchar(256),'price' bigint(11),'again_price' bigint(11),'sitin_price' bigint(11),'ask_price' bigint(11),'url' varchar(256),'color' varchar(256),'price_type' varchar(256))");
db.execSQL("CREATE TABLE 'home' ('id1' integer PRIMARY KEY AUTOINCREMENT ,'id' bigint(11),'title' varchar(100),'icon' varchar(256),'creat_time' datetime,'url' varchar(256),'update_time' datetime)");
db.execSQL("CREATE TABLE 'contents' ('id1' integer PRIMARY KEY AUTOINCREMENT ,'teacher_id' bigint(11),'portraiturl_big' varchar(256),'portraiturl_small' varchar(256),'nickname' varchar(256),'introduce' varchar(256),'catgory_name' varchar(256),'catgory_id' varchar(256),'remark' text,'pricerange' text,'sort' bigint(11),'is_thumbs' bigint(11),'score' bigint(11),'thumbs_count' bigint(11))");
db.execSQL("CREATE TABLE 'channel' ('id1' integer PRIMARY KEY AUTOINCREMENT ,'id' bigint(11),'channelname' text,'price' bigint(11),'categoryname' varchar(256),'iconurl' varchar(256),'des' text,'teacher_id' bigint(11))");
db.execSQL("CREATE TABLE 'channel2' ('id1' integer PRIMARY KEY AUTOINCREMENT ,'id' bigint(11),'channelname' text,'price' bigint(11),'categoryname' varchar(256),'iconurl' varchar(256),'des' text,'teacher_id' bigint(11),'categoryid' bigint(11))");
db.execSQL("CREATE TABLE 'customer' ('id' integer PRIMARY KEY AUTOINCREMENT,'name' varchar(256),'mobile' varchar(100),'identitycard' varchar(100),'agent' varchar(256))");
}
}
}
二:管理类
/**
* Created by jiangxiangfei on 2016/1/2.
*/
public class SQLHelperManager {
private static SQLHelperManager instance;
private Context mContext;
private MySQLiteOpenHelper mySQLiteOpenHelper;
private SQLiteDatabase db;
//单例模式
private SQLHelperManager(Context context){
mContext=context;
mySQLiteOpenHelper=new MySQLiteOpenHelper(mContext);
db=mySQLiteOpenHelper.getWritableDatabase();
}
//使用单例,不加锁
public static SQLHelperManager getInstance() {
if(instance == null){
Log.e("jxf","数据库管理类单例不存在:open时创建");
instance = new SQLHelperManager(App.getContext());
}else{
Log.e("jxf","数据库管理类单例已经存在:open时不创建:直接拿来用");
}
return instance;
}
//打开数据库
private void open(){
if (db==null||!db.isOpen()){
db=mySQLiteOpenHelper.getWritableDatabase();
Log.e("jxf","db之前没有或者关闭掉了:getWritableDatabase()打开数据库");
}
else{
Log.e("jxf","db之间就已经存在:没有被关闭");
return;
}
}
//关闭数据库
public void close(){
if (db.isOpen()){
db.close();
Log.e("jxf", "db存在才关闭db对象");
}
Log.e("jxf","db已经存在了");
}
//添加数据库的方法:抽出表名
public void insert(String tableName, ContentValues values) {
open();
db.insert(tableName, null, values);
Log.e("jxf", "表:" + tableName + "添加数据");
}
//删除全部数据
public void deleteAll(String tableName){
open();
db.delete(tableName, null, null);
Log.e("jxf", "表:" + tableName + "删除了全部数据");
}
//获取单条的
public Cursor seleteOne(String tableName,String[] columns, String orderBy,String limit){
open();
Cursor cursor=db.query(tableName,columns,null,null,null,null,orderBy,limit);
Log.e("jxf", "表:" + tableName + "获取全部数据:得到唯一的cursor");
return cursor;
}
//获取集合并利用SQL排序排序
public Cursor seleteAll(String tableName,String[] columns, String orderBy){
open();
Cursor cursor=db.query(tableName,columns,null,null,null,null,orderBy);
Log.e("jxf", "表:" + tableName + "获取全部数据:得到cursor");
return cursor;
}
//维持20条的删除单部分
public void deleteOne(String tableName,String where,String[] wheres){
open();
db.delete(tableName, where, wheres);
Log.e("jxf", "表:" + tableName + "删除了超过20条的数据");
}
//更新一条数据
public void updateOne(String tableName,ContentValues values,String where,String[] wheres){
open();
int i=db.update(tableName, values, where, wheres);
Log.e("jxf", "表:" + tableName + "更新单条数据结果"+i);
Log.e("jxf", "表:" + tableName + "更新单条数据");
}
public Cursor seleteAllWhere(String tableName, String[] columns,String where, String[] wheres,String orderBy) {
open();
Cursor cursor=db.query(tableName,columns,where,wheres,null,null,orderBy);
Log.e("jxf", "表:" + tableName + "获取"+where+"全部数据:得到cursor");
return cursor;
}
public void deleteAllWhere(String tableName,String where,String[] wheres) {
open();
db.delete(tableName, where, wheres);
Log.e("jxf", "表:" + tableName + "删除了"+where+"全部数据");
}
}
以上的操作是维持数据库中20条操作的。
实际使用:
在App类中,也就是application中 得到管理类单例后:会进行版本管理,马上关闭。
在代码中直接使用app 类中的管理类单例,就行操作
但是注意:单一线程或者操作数据不做的主线程,使用完要关闭:也就是封装的close方法。
但是多个线程的操作。如果同时在进行:也就是在切换着进行数据库的操作。如果一个关闭了,切换到位执行完的,会报错:数据库对象为空。所以一般多线程的操作:我一般是在activity或者fragment的生命周期方法ondestory中关掉。
还要注意:数据库的版本迭代问题:尤其是:app开发中会有许多的版本迭代。数据库可能也要有变动所以也要进行变动操作