sqlserver 自带aes加密方法 sqlcipher加密

转载

mob64ca140530fb 2024-03-25 11:41:41

文章标签 数据库加密 android SQL sql 文章分类 架构后端开发

android本地数据库不支持加密，因此需要使用其他的工具，移动端最火的就是sqlcipher了！
下面我也使用sqlcipher进行了尝试，在此分享一下：

首先引入sqlcipher

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

构建数据库帮助类SQLiteOpenHelper

package com.ddv.www.sqlcipher.dbhelper;

import android.content.Context;

import net.sqlcipher.database.SQLiteDatabase;
import net.sqlcipher.database.SQLiteOpenHelper;

/**
 * Created by 黄家三少 on 2017/10/12.
 */

public class DBCipherHelper extends SQLiteOpenHelper {

    //数据库名字
    private static final String DB_NAME = "huangxiaoguo_db";
    //数据库密码
    public static final String DB_PASSWORD = "huangxiaoguo";
    //数据库版本
    private static final int DB_VERSION = 1;

    //数据库表名称
    public static final String TABLE_NAME = "huangxiaoguo";

    //列名（字段一）
    public static final String FIELD_ID = "id";
    //列名（字段二）
    public static final String FIELD_NAME = "name";
    //列名（字段三）
    public static final String FIELD_NUM = "num";

    public DBCipherHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
        //不可忽略的  进行so库加载
        SQLiteDatabase.loadLibs(context);
    }

    public DBCipherHelper(Context context) {
        this(context, DB_NAME, null, DB_VERSION);

    }

    /**
     * 创建数据库
     *
     * @param db
     */
    @Override
    public void onCreate(SQLiteDatabase db) {
        //创建表
        createTable(db);
    }

    private void createTable(SQLiteDatabase db) {
        //CREATE TABLE person (personid integer primary key autoincrement, name varchar(20))
        // db.execSQL("create table stu(_id integer primary key autoincrement,name varchar(20),num varchar(20))");
        String sql = "create table " + TABLE_NAME + " (" +
                FIELD_ID + " integer primary key autoincrement," +
                FIELD_NAME + " text not null, " + FIELD_NUM + " varchar(20)" + ");";
        db.execSQL(sql);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        //db.execSQL("alter table stu add sex varchar(20)");
        db.execSQL("alter table " + TABLE_NAME + " add phone varchar(12)"); //往表中增加一列
    }
}

实现增删改查方法

package com.ddv.www.sqlcipher.dbhelper;

import android.content.ContentValues;
import android.content.Context;
import android.util.Log;

import net.sqlcipher.Cursor;
import net.sqlcipher.SQLException;
import net.sqlcipher.database.SQLiteDatabase;

/**
 * Created by 黄家三少 on 2017/10/12.
 */

public class DBCipherManager {

    private DBCipherHelper dbCipherHelper;
    private volatile static DBCipherManager mInstance;

    private DBCipherManager(Context context) {
        dbCipherHelper = new DBCipherHelper(context.getApplicationContext());
    }

    public static DBCipherManager getInstance(Context context) {
        DBCipherManager instance = mInstance;
        if (instance == null) {
            synchronized (DBCipherManager.class) {
                instance = mInstance;
                if (instance == null) {
                    instance = new DBCipherManager(context);
                    mInstance = instance;
                }
            }
        }
        return instance;
    }

    /**
     * 插入数据
     *
     * @param name
     */
    public void insertData(String name, int num) {
        //获取写数据库
        SQLiteDatabase db = dbCipherHelper.getWritableDatabase(DBCipherHelper.DB_PASSWORD);
        //生成要修改或者插入的键值
        ContentValues values = new ContentValues();
        values.put(DBCipherHelper.FIELD_NAME, name);
        values.put(DBCipherHelper.FIELD_NUM, num);
        // insert 操作
        db.insert(DBCipherHelper.TABLE_NAME, null, values);
        //关闭数据库
        db.close();
    }

    /**
     * 未开启事务批量插入
     *
     * @param testCount
     */
    public void insertDatasByNomarl(int testCount) {
        //获取写数据库
        SQLiteDatabase db = dbCipherHelper.getWritableDatabase(DBCipherHelper.DB_PASSWORD);
        for (int i = 0; i < testCount; i++) {
            //生成要修改或者插入的键值
            ContentValues values = new ContentValues();
            values.put(DBCipherHelper.FIELD_NAME, String.valueOf(i));
            values.put(DBCipherHelper.FIELD_NUM, i);
            // insert 操作
            db.insert(DBCipherHelper.TABLE_NAME, null, values);
        }
        //关闭数据库
        db.close();
    }


    /**
     * 测试开启事务批量插入
     *
     * @param testCount
     */
    public void insertDatasByTransaction(int testCount) {
        //获取写数据库
        SQLiteDatabase db = dbCipherHelper.getWritableDatabase(DBCipherHelper.DB_PASSWORD);
        db.beginTransaction();  //手动设置开始事务
        try {
            //批量处理操作
            for (int i = 0; i < testCount; i++) {
                //生成要修改或者插入的键值
                ContentValues values = new ContentValues();
                values.put(DBCipherHelper.FIELD_NAME, String.valueOf(i));
                values.put(DBCipherHelper.FIELD_NUM, i);
                // insert 操作
                db.insert(DBCipherHelper.TABLE_NAME, null, values);
            }
            db.setTransactionSuccessful(); //设置事务处理成功，不设置会自动回滚不提交
        } catch (Exception e) {

        } finally {
            db.endTransaction(); //处理完成
            //关闭数据库
            db.close();
        }
    }

    /**
     * 删除数据
     */
    public void deleteData(String name) {
        //获取写数据库
        SQLiteDatabase db = dbCipherHelper.getWritableDatabase(DBCipherHelper.DB_PASSWORD);
        // delete 操作
        db.delete(DBCipherHelper.TABLE_NAME, DBCipherHelper.FIELD_NAME + "=?", new String[]{name});
        //关闭数据库
        db.close();
    }

    /**
     * 多个字段删除数据
     */
    public void deleteData(String name, int num) {
        //获取写数据库
        SQLiteDatabase db = dbCipherHelper.getWritableDatabase(DBCipherHelper.DB_PASSWORD);
        // delete 操作
        db.delete(DBCipherHelper.TABLE_NAME, DBCipherHelper.FIELD_NAME + "=?and " + DBCipherHelper.FIELD_NUM + "=?",
                new String[]{name, String.valueOf(num)});
        //关闭数据库
        db.close();
    }

    /**
     * 删除所有数据
     */
    public void deleteDatas() {
        //String sql = "delete from " + DBCipherHelper.TABLE_NAME;
        //execSQL(sql);
        SQLiteDatabase db = dbCipherHelper.getWritableDatabase(DBCipherHelper.DB_PASSWORD);
        db.delete(DBCipherHelper.TABLE_NAME, null, null);
        //关闭数据库
        db.close();
    }

    /**
     * 更新数据
     */
    public void updateData(String name, int num) {
        //生成要修改或者插入的键值
        ContentValues values = new ContentValues();
        values.put(DBCipherHelper.FIELD_NAME, name + name);
        values.put(DBCipherHelper.FIELD_NUM, num);
        //获取写数据库
        SQLiteDatabase db = dbCipherHelper.getWritableDatabase(DBCipherHelper.DB_PASSWORD);
        /**
         * 数据的更新
         * 参数一：要更新的数据所在的表名
         * 参数二：新的数据
         * 参数三：要更新数据的查找条件
         * 参数四：条件的参数
         */
        db.update(DBCipherHelper.TABLE_NAME, values, DBCipherHelper.FIELD_NUM + "=?", new String[]{String.valueOf(num)});
        //关闭数据库
        db.close();
    }

    /**
     * 指定条件查询数据
     */
    public void queryDatas(String name) {
        //获取可读数据库
        SQLiteDatabase db = dbCipherHelper.getReadableDatabase(DBCipherHelper.DB_PASSWORD);
        //查询数据库
        Cursor cursor = null;
        try {
            cursor = db.query(DBCipherHelper.TABLE_NAME, new String[]{DBCipherHelper.FIELD_NAME},
                    DBCipherHelper.FIELD_NAME + "=?", new String[]{name}, null, null, null);
            while (cursor.moveToNext()) {
                int count = cursor.getColumnCount();
                String columName = cursor.getColumnName(0);
                String tname = cursor.getString(0);
                Log.e("huangxiaoguo", "count = " + count + " columName = " + columName + "  name =  " + tname);
            }
            if (cursor != null) {
                cursor.close();
            }
        } catch (SQLException e) {
        }
        //关闭数据库
        db.close();
    }

    /**
     * 查询全部数据
     */
    public void queryDatas() {
        //获取可读数据库
        SQLiteDatabase db = dbCipherHelper.getReadableDatabase(DBCipherHelper.DB_PASSWORD);
        //查询数据库
        Cursor cursor = null;
        try {
            //获取数据游标
            cursor = db.query(DBCipherHelper.TABLE_NAME, new String[]{DBCipherHelper.FIELD_NAME, DBCipherHelper.FIELD_NUM}, null, null, null, null, null);
            while (cursor.moveToNext()) {
                int count = cursor.getColumnCount();
                String columeName = cursor.getColumnName(0);//获取表结构列名
                String name = cursor.getString(0);//获取表结构列数据
                int num = cursor.getInt(1);
                Log.e("huangxiaoguo", "count = " + count + " columName = " + columeName + "  name =  " + name + "  num =  " + num);
            }
            //关闭游标防止内存泄漏
            if (cursor != null) {
                cursor.close();
            }
        } catch (SQLException e) {
        }
        //关闭数据库
        db.close();
    }

    /**
     * 执行sql语句
     */
    private void execSQL(String sql) {
        //获取写数据库
        SQLiteDatabase db = dbCipherHelper.getWritableDatabase(DBCipherHelper.DB_PASSWORD);
        //直接执行sql语句
        db.execSQL(sql);//或者
        //关闭数据库
        db.close();
    }

}

执行方法页面-布局

<?xml version="1.0" encoding="utf-8"?>
<FrameLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    tools:context="com.ddv.www.sqlcipher.MainActivity">

    <ScrollView
        android:layout_width="match_parent"
        android:layout_height="match_parent">

        <LinearLayout
            android:layout_width="match_parent"
            android:layout_height="match_parent"
            android:orientation="vertical">


            <Button
                android:id="@+id/add"
                android:layout_width="match_parent"
                android:layout_height="wrap_content"
                android:layout_margin="20dp"
                android:text="增" />

            <Button
                android:id="@+id/add_no_be"
                android:layout_width="match_parent"
                android:layout_height="wrap_content"
                android:layout_margin="20dp"
                android:text="未开启事务批量插入" />

            <Button
                android:id="@+id/add_have_be"
                android:layout_width="match_parent"
                android:layout_height="wrap_content"
                android:layout_margin="20dp"
                android:text="测试开启事务批量插入" />

            <Button
                android:id="@+id/delete"
                android:layout_width="match_parent"
                android:layout_height="wrap_content"
                android:layout_margin="20dp"
                android:text="删" />

            <Button
                android:id="@+id/delete_some"
                android:layout_width="match_parent"
                android:layout_height="wrap_content"
                android:layout_margin="20dp"
                android:text="多个字段删除数据" />

            <Button
                android:id="@+id/updata"
                android:layout_width="match_parent"
                android:layout_height="wrap_content"
                android:layout_margin="20dp"
                android:text="改" />

            <Button
                android:id="@+id/query_for_condition"
                android:layout_width="match_parent"
                android:layout_height="wrap_content"
                android:layout_margin="20dp"
                android:text="指定条件查询数据" />

            <Button
                android:id="@+id/query_all"
                android:layout_width="match_parent"
                android:layout_height="wrap_content"
                android:layout_margin="20dp"
                android:text="查" />

            <Button
                android:id="@+id/delete_all"
                android:layout_width="match_parent"
                android:layout_height="wrap_content"
                android:layout_margin="20dp"
                android:text="清空" />
        </LinearLayout>
    </ScrollView>
</FrameLayout>

使用方法页面activity

package com.ddv.www.sqlcipher;

import android.os.Bundle;
import android.support.v7.app.AppCompatActivity;
import android.view.View;
import android.widget.Button;

import com.ddv.www.sqlcipher.dbhelper.DBCipherManager;

import butterknife.Bind;
import butterknife.ButterKnife;

public class MainActivity extends AppCompatActivity implements View.OnClickListener {


    @Bind(R.id.add)
    Button add;
    @Bind(R.id.add_no_be)
    Button addNoBe;
    @Bind(R.id.add_have_be)
    Button addHaveBe;
    @Bind(R.id.delete)
    Button delete;
    @Bind(R.id.delete_some)
    Button deleteSome;
    @Bind(R.id.updata)
    Button updata;
    @Bind(R.id.query_for_condition)
    Button queryForCondition;
    @Bind(R.id.query_all)
    Button queryAll;
    @Bind(R.id.delete_all)
    Button deleteAll;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        ButterKnife.bind(this);
        add.setOnClickListener(this);
        addNoBe.setOnClickListener(this);
        addHaveBe.setOnClickListener(this);
        delete.setOnClickListener(this);
        deleteSome.setOnClickListener(this);
        updata.setOnClickListener(this);
        queryForCondition.setOnClickListener(this);
        queryAll.setOnClickListener(this);
        deleteAll.setOnClickListener(this);
    }

    @Override
    public void onClick(View v) {
        switch (v.getId()) {
            case R.id.add:
                //插入数据
                for (int i = 0; i < 10; i++) {
                    DBCipherManager.getInstance(MainActivity.this).insertData(String.valueOf(i), i);
                }
                break;
            case R.id.add_no_be://未开启事务批量插入
                DBCipherManager.getInstance(MainActivity.this).insertDatasByNomarl(10);
                break;
            case R.id.add_have_be://测试开启事务批量插入
                DBCipherManager.getInstance(MainActivity.this).insertDatasByTransaction(10);
                break;
            case R.id.delete:
                //删除数据
                DBCipherManager.getInstance(MainActivity.this).deleteData(String.valueOf(5));
                break;
            case R.id.delete_some:
                //多个字段删除数据
                DBCipherManager.getInstance(MainActivity.this).deleteData(String.valueOf(6), 6);
                break;
            case R.id.updata:
                //更新数据
                DBCipherManager.getInstance(MainActivity.this).updateData(String.valueOf(3), 3);
                break;
            case R.id.query_for_condition://指定条件查询数据
                DBCipherManager.getInstance(MainActivity.this).queryDatas(String.valueOf(2));
                break;
            case R.id.query_all:
                //查询数据
                DBCipherManager.getInstance(MainActivity.this).queryDatas();
                break;
            case R.id.delete_all:
                //清空数据
                DBCipherManager.getInstance(MainActivity.this).deleteDatas();
                break;
        }
    }
}

到此为止基本完成………………

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。