Android sqlite数据转存到SQL

一 流程图

二 详解

android 自带的sqlite可以方便的存储数据,但是不够安全.使用sqlcipher可以对数据库加密.sqlcipher github地址 , sqlicipher 官方地址

66785E13-A2E4-46F1-82D3-77A825A1490A.jpeg
418C3058-5B71-4523-85B4-A07F401F3725.jpeg

1. 引入sqlcipher库

compile 'net.zetetic:android-database-sqlcipher:3.5.4@aar'

2. 初始化库

最好在application调用 一定要在使用sqlite之前调用
SQLiteDatabase.loadLibs(this);

• 在gradle中还要配置ndk 不然不能兼容部分手机

 defaultConfig {
        ndk {
            abiFilters "armeabi", "armeabi-v7a", "x86","mips"
        }
    }

3. sqlcipher写入数据

import net.sqlcipher.database.SQLiteDatabase;
import net.sqlcipher.database.SQLiteOpenHelper;
public class DBCipherHelper extends SQLiteOpenHelper {

   private static final String DATABASE_NAME = "sqlcipher_database";

   private static int DATABASE_VERSION = 1;

   public DBCipherHelper(Context context) {
       super(context, DATABASE_NAME, null, DATABASE_VERSION);
   }

   @Override
   public void onCreate(SQLiteDatabase db) {
       db.execSQL(SQL.CREATE_TABLE_SQL);
   }

   @Override
   public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {

   }
}

public interface SQL {
    String MY_TABLE = "my_table";

    String CREATE_TABLE_SQL = "CREATE TABLE IF NOT EXISTS "+MY_TABLE+" ("
            + "id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "
            + "userID VARCHAR, "
            + "value VARCHAR"
            + ")";

    //用户ID
    String USERID = "userID";
    //值
    String VALUE = "value";
}

 /**
     * sqlcipher写入
     */
    private void sqlcipherWriteIn() {
        String userid = mETwritein_cipher.getText().toString().trim();
        String value = mEtwritenin_ciphervalue.getText().toString().trim();
        if (TextUtils.isEmpty(userid) || TextUtils.isEmpty(value)) {
            Toast.makeText(this, "填数据啦!!!", Toast.LENGTH_LONG).show();
            return;
        }
        DBCipherHelper mCipherHelper = new DBCipherHelper(this);
        net.sqlcipher.database.SQLiteDatabase db = mCipherHelper.getWritableDatabase("secret_key");
        ContentValues cv = new ContentValues();
        cv.put(SQL.USERID, userid);
        cv.put(SQL.VALUE, value);
        db.insert(SQL.MY_TABLE, null, cv);
        db.close();
    }

4. sqlcipher读出数据

    /**
     * sqlcipher写出
     */
    private void sqlcipherWriteOut() {
        DBCipherHelper mCipherHelper = new DBCipherHelper(this);
        net.sqlcipher.database.SQLiteDatabase db = mCipherHelper.getWritableDatabase("secret_key");
        net.sqlcipher.Cursor cursor = null;
        String result = "";
        try {
            cursor = db.rawQuery("select * from " + SQL.MY_TABLE, null);
            while (cursor.moveToNext()) {
                String userid = cursor.getString(cursor.getColumnIndex(SQL.USERID));
                String value = cursor.getString(cursor.getColumnIndex(SQL.VALUE));
                result = result + userid + "," + value + ";";
            }
            mTvwriteout_cipher.setText(result);
        } catch (Exception e) {

        } finally {
            if (cursor != null) {
                cursor.close();
            }
            db.close();
        }
    }

5.将数据从sqlite转移到sqlcipher

/**
 * Created by callxkj on 16/10/11.
 * 基本的sqlite数据库
 */

public class NormalDBHelper extends SQLiteOpenHelper {

    private static final String DATABASE_NAME = "normal_database";

    private static int DATABASE_VERSION = 1;

    public NormalDBHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
         db.execSQL(SQL.CREATE_TABLE_SQL);
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {

    }
}

/**
     * 将数据从sqlite转移到sqlcipher
     */
    private void sqliteTOsqlcipher() {
        NormalDBHelper mNormalDBHelper = new NormalDBHelper(this);
        mNormaldb = mNormalDBHelper.getWritableDatabase();
        //开启事务 数据转移成功了才能删除旧sqlite中的数据
        mNormaldb.beginTransaction();
        Cursor cursor = null;
        try {
            cursor = mNormaldb.rawQuery("select * from " + SQL.MY_TABLE, null);
            if (cursor.moveToNext()) {
                Log.d(TAG, "还没转移");
                cursor.close();
                toMove(mNormaldb);
            } else {
                cursor.close();
                Log.d(TAG, "已经转移了");
            }
        } catch (Exception e) {
            Log.d(TAG, e.toString());
        } finally {
            mNormaldb.endTransaction();
            if (mNormaldb != null) {
                mNormaldb.close();
            }
        }
    }

    /**
     * 转移sqlite的数据到chipher数据库
     *
     * @param db sqlite
     */
    private void toMove(SQLiteDatabase db) {
        Cursor cursor = null;
        Cursor cipherCursor = null;
        net.sqlcipher.database.SQLiteDatabase cipherDB = null;
        try {
            ArrayList<SQLbean> sqLbeanlist = new ArrayList<>();
            cursor = db.rawQuery("select * from " + SQL.MY_TABLE, null);
            while (cursor.moveToNext()) {
                String userid = cursor.getString(cursor.getColumnIndex(SQL.USERID));
                String value = cursor.getString(cursor.getColumnIndex(SQL.VALUE));
                SQLbean bean = new SQLbean();
                bean.userid = userid;
                bean.value = value;
                sqLbeanlist.add(bean);
            }
            db.delete(SQL.MY_TABLE, null, null);
            DBCipherHelper dbHelper = new DBCipherHelper(this);
            cipherDB = dbHelper.getWritableDatabase("secret_key");
            for (int x = 0; x < sqLbeanlist.size(); x++) {
                SQLbean sqLbean = sqLbeanlist.get(x);
                //判断数据是否有重复同步
                cipherCursor = cipherDB.rawQuery("select * from " + SQL.MY_TABLE
                                + " where " + SQL.USERID + " = ? and " + SQL.VALUE + " = ?"
                        , new String[]{sqLbean.userid, sqLbean.value});
                if (cipherCursor.moveToNext()) {
                    //说明数据已经转移过来了 不做处理
                } else {
                    ContentValues cv = new ContentValues();
                    cv.put(SQL.USERID, sqLbean.userid);
                    cv.put(SQL.VALUE, sqLbean.value);
                    cipherDB.insert(SQL.MY_TABLE, null, cv);
                }
            }
           // db.execSQL("drop table "+SQL.MY_TABLE);//删除表
            db.setTransactionSuccessful();
        } catch (Exception e) {

        } finally {
            if (cursor != null) {
                cursor.close();
            }
            if (cipherDB != null) {
                cipherDB.close();
            }

        }
    }

三 验证结果

Paste_Image.png
Paste_Image.png

最后的福利