Android SQLite工作中使用到的简单方法记录

1.不同db文件数据表内容拷贝

 /**
     * 通过attach附加外部数据库方式批量插入
     * function: 不同db文件，相同结构的表，内容的完全拷贝，字段完全相同
     * @param db 新的数据库
     * @param tempDb 旧的数据库
     * @param oldDbAsName  旧的数据库别名
     * @param tableName  要拷贝的所有表名
     */
    public static boolean dbTableCopy(SQLiteDatabase db ,SQLiteDatabase tempDb, String oldDbAsName,String ...tableName){
        String oldDbFilePath = tempDb.getPath();
        boolean isSuccess = false;
        if(db==null || TextUtils.isEmpty(oldDbFilePath) || TextUtils.isEmpty(oldDbAsName) || tableName==null || tableName.length<1){
            return isSuccess;
        }
        // 检查要复制的表是否存在
        List<String> tables = Arrays.asList(tableName);
        ArrayList<String> list = new ArrayList<>();
        Cursor cursor = db.rawQuery("select name from sqlite_master where type='table' order by name", null);
        while(cursor.moveToNext()){
             String name = cursor.getString(0);
             LogUtil.i(TAG,"新数据库表："+name);
             list.add(name);
        }
        if(!list.containsAll(tables)){
            LogUtil.w(TAG,"存在新旧表不一致！");
            return isSuccess;
        }
        try {
            LogUtil.e("TAG","=========");
            db.execSQL(String.format("ATTACH DATABASE \'%s\' AS %s", oldDbFilePath, oldDbAsName));
            LogUtil.e("TAG","+++++++++");
//            db.beginTransaction();
            // INSERT INTO X.TABLE(fieldname1, fieldname2) SELECT fieldname1, fieldname2 FROM Y.TABLE;
            for (int i = 0; i < tableName.length; i++) {
                if(isEmptyTable(tempDb,tableName[i])){// 旧表为空跳过
                    continue;
                }
                if(!isEmptyTable(db,tableName[i])){// 新表有数据，先清空再插入
                    LogUtil.e("TAG","表"+tableName[i]+"已经有数据！");
                    deleteTable(db,tableName[i]);
                }
                SQLiteStatement sqLiteStatement = db.compileStatement(String.format("INSERT INTO %s SELECT * FROM  %s", tableName[i], oldDbAsName + "." + tableName[i]));
                long id = sqLiteStatement.executeInsert();
                LogUtil.i(TAG, "dbTableCopy: table " + tableName[i] + " insert last line ID:" + id+",插入记录数："+fetchCount(db,tableName[i]));

            }
//            db.setTransactionSuccessful();
//            db.endTransaction();
            db.execSQL(String.format("DETACH DATABASE %s", oldDbAsName));
            LogUtil.e("======","当前时间："+TimeFormatUtil.format(System.currentTimeMillis())+",线程："+Thread.currentThread().getName());
            isSuccess = true;
        } catch (SQLException e) {
            e.printStackTrace();
            isSuccess = false;
        }
        return isSuccess;
    }

2.判断数据表是否为空

    // 判断数据表是否为空
    private static boolean isEmptyTable(SQLiteDatabase db,String tableName){
        int number = 0;
        Cursor c = db.rawQuery("select * from "+tableName, null);
        number=c.getCount();
        return number == 0;
    }

3.清空某个数据表

// 清空某一个表
    public static void deleteTable(SQLiteDatabase db,String tableName){
        db.execSQL("delete from "+tableName);
        LogUtil.e("TAG","清空了数据表:"+tableName);
    }

4.获取某个数据表记录总条数

// 获取表记录数量
    private static long fetchCount(SQLiteDatabase db,String tableName) {
        String sql = "SELECT COUNT(*) FROM " + tableName;
        SQLiteStatement statement = db.compileStatement(sql);
        long count = statement.simpleQueryForLong();
        return count;
    }

5.删除某个表

	        //删除某一个表
	    public void dropTable(SQLiteDatabase db,String tableName){
		     db.execSQL("drop table "+tableName);
	    }