sqlite 密码 springboot

一、效果图如下：

二、具体实现功能

1、创建windows窗体应用程序：

2、编写控制脚本：

①sqlite数据库帮助类：SqliteHelper.cs

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data.SQLite;



namespace TestEncryptSqLite

{

    /// <summary>

    /// SQLite 操作类

    /// </summary>

    public class SqliteHelper
    {

        /// <summary>

        /// 数据库连接定义

        /// </summary>



        public SQLiteConnection dbConnection;



        /// <summary>

        /// SQL命令定义

        /// </summary>

        private SQLiteCommand dbCommand;



        /// <summary>

        /// 数据读取定义

        /// </summary>

        private SQLiteDataReader dataReader;



        /// <summary>

        /// 数据库连接字符串定义

        /// </summary>

        private SQLiteConnectionStringBuilder dbConnectionstr;



        /// <summary>

        /// 构造函数

        /// </summary>

        /// <param name="connectionString">连接SQLite库字符串</param>

        public SqliteHelper(string connectionString,string sqliteDatabasePwd)
        {

            try
            {

                dbConnection = new SQLiteConnection();



                dbConnectionstr = new SQLiteConnectionStringBuilder();

                dbConnectionstr.DataSource = connectionString;
                if (!string.IsNullOrEmpty(sqliteDatabasePwd))
                {
                    dbConnectionstr.Password = sqliteDatabasePwd;      //设置密码，SQLite ADO.NET实现了数据库密码保护
                    dbConnection.ConnectionString = dbConnectionstr.ToString();

                    dbConnection.Open();
                }
                else
                {
                    return;
                }
            }

            catch (Exception e)

            {

                Log(e.ToString());

            }

        }



        /// <summary>

        /// 执行SQL命令

        /// </summary>

        /// <returns>The query.</returns>

        /// <param name="queryString">SQL命令字符串</param>

        public SQLiteDataReader ExecuteQuery(string queryString)
        {

            try

            {

                dbCommand = dbConnection.CreateCommand();

                dbCommand.CommandText = queryString;       //设置SQL语句

                dataReader = dbCommand.ExecuteReader();

            }

            catch (Exception e)

            {

                Log(e.Message);

            }



            return dataReader;

        }



        /// <summary>

        /// 关闭数据库连接

        /// </summary>

        public void CloseConnection()

        {

            //销毁Command

            if (dbCommand != null)

            {

                dbCommand.Cancel();

            }

            dbCommand = null;

            //销毁Reader

            if (dataReader != null)

            {

                dataReader.Close();

            }

            dataReader = null;

            //销毁Connection

            if (dbConnection != null)

            {

                dbConnection.Close();

            }

            dbConnection = null;



        }



        /// <summary>

        /// 读取整张数据表

        /// </summary>

        /// <returns>The full table.</returns>

        /// <param name="tableName">数据表名称</param>

        public SQLiteDataReader ReadFullTable(string tableName)

        {

            string queryString = "SELECT * FROM " + tableName;  //获取所有可用的字段

            return ExecuteQuery(queryString);

        }



        /// <summary>

        /// 向指定数据表中插入数据

        /// </summary>

        /// <returns>The values.</returns>

        /// <param name="tableName">数据表名称</param>

        /// <param name="values">插入的数值</param>

        public SQLiteDataReader InsertValues(string tableName, string[] values)

        {

            //获取数据表中字段数目

            int fieldCount = ReadFullTable(tableName).FieldCount;

            //当插入的数据长度不等于字段数目时引发异常

            if (values.Length != fieldCount)

            {

                throw new SQLiteException("values.Length!=fieldCount");

            }

            string queryString = "INSERT INTO " + tableName + " VALUES (" + "'" + values[0] + "'";

            for (int i = 1; i < values.Length; i++)

            {

                queryString += ", " + "'" + values[i] + "'";

            }

            queryString += " )";

            return ExecuteQuery(queryString);

        }



        /// <summary>

        /// 更新指定数据表内的数据

        /// </summary>

        /// <returns>The values.</returns>

        /// <param name="tableName">数据表名称</param>

        /// <param name="colNames">字段名</param>

        /// <param name="colValues">字段名对应的数据</param>

        /// <param name="key">关键字</param>

        /// <param name="value">关键字对应的值</param>

        /// <param name="operation">运算符：=,<,>,...，默认“=”</param>

        public SQLiteDataReader UpdateValues(string tableName, string[] colNames, string[] colValues, string key, string value, string operation)

        {

            // operation="=";  //默认

            //当字段名称和字段数值不对应时引发异常

            if (colNames.Length != colValues.Length)

            {

                throw new SQLiteException("colNames.Length!=colValues.Length");

            }

            string queryString = "UPDATE " + tableName + " SET " + colNames[0] + "=" + "'" + colValues[0] + "'";



            for (int i = 1; i < colValues.Length; i++)

            {

                queryString += ", " + colNames[i] + "=" + "'" + colValues[i] + "'";

            }

            queryString += " WHERE " + key + operation + "'" + value + "'";



            return ExecuteQuery(queryString);

        }

        /// <summary>

        /// 更新指定数据表内的数据

        /// </summary>

        /// <returns>The values.</returns>

        /// <param name="tableName">数据表名称</param>

        /// <param name="colNames">字段名</param>

        /// <param name="colValues">字段名对应的数据</param>

        /// <param name="key">关键字</param>

        /// <param name="value">关键字对应的值</param>

        /// <param name="operation">运算符：=,<,>,...，默认“=”</param>

        public SQLiteDataReader UpdateValues(string tableName, string[] colNames, string[] colValues, string key1, string value1, string operation, string key2, string value2)

        {

            // operation="=";  //默认

            //当字段名称和字段数值不对应时引发异常

            if (colNames.Length != colValues.Length)

            {

                throw new SQLiteException("colNames.Length!=colValues.Length");

            }

            string queryString = "UPDATE " + tableName + " SET " + colNames[0] + "=" + "'" + colValues[0] + "'";



            for (int i = 1; i < colValues.Length; i++)

            {

                queryString += ", " + colNames[i] + "=" + "'" + colValues[i] + "'";

            }

            //表中已经设置成int类型的不需要再次添加‘单引号’，而字符串类型的数据需要进行添加‘单引号’

            queryString += " WHERE " + key1 + operation + "'" + value1 + "'" + "OR " + key2 + operation + "'" + value2 + "'";



            return ExecuteQuery(queryString);

        }





        /// <summary>

        /// 删除指定数据表内的数据

        /// </summary>

        /// <returns>The values.</returns>

        /// <param name="tableName">数据表名称</param>

        /// <param name="colNames">字段名</param>

        /// <param name="colValues">字段名对应的数据</param>

        public SQLiteDataReader DeleteValuesOR(string tableName, string[] colNames, string[] colValues, string[] operations)

        {

            //当字段名称和字段数值不对应时引发异常

            if (colNames.Length != colValues.Length || operations.Length != colNames.Length || operations.Length != colValues.Length)

            {

                throw new SQLiteException("colNames.Length!=colValues.Length || operations.Length!=colNames.Length || operations.Length!=colValues.Length");

            }



            string queryString = "DELETE FROM " + tableName + " WHERE " + colNames[0] + operations[0] + "'" + colValues[0] + "'";

            for (int i = 1; i < colValues.Length; i++)

            {

                queryString += "OR " + colNames[i] + operations[0] + "'" + colValues[i] + "'";

            }

            return ExecuteQuery(queryString);

        }



        /// <summary>

        /// 删除指定数据表内的数据

        /// </summary>

        /// <returns>The values.</returns>

        /// <param name="tableName">数据表名称</param>

        /// <param name="colNames">字段名</param>

        /// <param name="colValues">字段名对应的数据</param>

        public SQLiteDataReader DeleteValuesAND(string tableName, string[] colNames, string[] colValues, string[] operations)

        {

            //当字段名称和字段数值不对应时引发异常

            if (colNames.Length != colValues.Length || operations.Length != colNames.Length || operations.Length != colValues.Length)

            {

                throw new SQLiteException("colNames.Length!=colValues.Length || operations.Length!=colNames.Length || operations.Length!=colValues.Length");

            }



            string queryString = "DELETE FROM " + tableName + " WHERE " + colNames[0] + operations[0] + "'" + colValues[0] + "'";



            for (int i = 1; i < colValues.Length; i++)

            {

                queryString += " AND " + colNames[i] + operations[i] + "'" + colValues[i] + "'";

            }

            return ExecuteQuery(queryString);

        }





        /// <summary>

        /// 创建数据表

        /// </summary> +

        /// <returns>The table.</returns>

        /// <param name="tableName">数据表名</param>

        /// <param name="colNames">字段名</param>

        /// <param name="colTypes">字段名类型</param>

        public SQLiteDataReader CreateTable(string tableName, string[] colNames, string[] colTypes)

        {

            string queryString = "CREATE TABLE IF NOT EXISTS " + tableName + "( " + colNames[0] + " " + colTypes[0];

            for (int i = 1; i < colNames.Length; i++)

            {

                queryString += ", " + colNames[i] + " " + colTypes[i];

            }

            queryString += "  ) ";

            return ExecuteQuery(queryString);

        }



        /// <summary>

        /// Reads the table.

        /// </summary>

        /// <returns>The table.</returns>

        /// <param name="tableName">Table name.</param>

        /// <param name="items">Items.</param>

        /// <param name="colNames">Col names.</param>

        /// <param name="operations">Operations.</param>

        /// <param name="colValues">Col values.</param>

        public SQLiteDataReader ReadTable(string tableName, string[] items, string[] colNames, string[] operations, string[] colValues)

        {

            string queryString = "SELECT " + items[0];

            for (int i = 1; i < items.Length; i++)

            {

                queryString += ", " + items[i];

            }

            queryString += " FROM " + tableName + " WHERE " + colNames[0] + " " + operations[0] + " " + colValues[0];

            for (int i = 0; i < colNames.Length; i++)

            {

                queryString += " AND " + colNames[i] + " " + operations[i] + " " + colValues[0] + " ";

            }

            return ExecuteQuery(queryString);

        }



        /// <summary>

        /// 本类log

        /// </summary>

        /// <param name="s"></param>

       public static void Log(string s)
        {

            Console.WriteLine("class SqLiteHelper:::" + s);

        }

    }









}

②给窗体应用程序编写对应的控制功能：

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SQLite;

namespace TestEncryptSqLite
{
    public partial class Form1 : Form
    {
        //文件及其对应路径
        private string _fileAndPath = null;

        private SQLiteConnection _con;
        bool isCorrect = false;         //原密码是否正确


        private SqliteHelper sql;

        private string sqliteDatabasePWD = null;


        /// <summary>
        /// 文件路径
        /// </summary>
        public string DbFilePath { get =>@"E:\成品\EquipmentMonitoring1.db"; }

        /// <summary>
        /// 旧密码
        /// </summary>
        public string OriginalPassword { get; set; }

        public string FileAndPath { get => _fileAndPath; set => _fileAndPath = value; }

        /// <summary>
        /// 修改密码
        /// </summary>
        /// <param name="newPassword">新密码</param>
        public void ChangePassword(string newPassword,string originalPassword)
        {
           
            _con = new SQLiteConnection("Data Source=" + this.FileAndPath);
            if (!string.IsNullOrEmpty(originalPassword))
            {
                try
                {
                    _con.SetPassword(originalPassword);
                    _con.Open();
                    isCorrect = true;
                    if (isCorrect)
                    {
                        if (!string.IsNullOrEmpty(newPassword))
                        {
                            _con.ChangePassword(newPassword);
                            _con.Close();
                        }
                        try
                        {
                            _con.Open();
                        }
                        catch (Exception ex)
                        {
                            throw new Exception("无法连接到数据库!" + ex.Message);
                        }
                    }
                }
                catch (Exception e)
                {
                    isCorrect = false;
                    throw new Exception("无法连接到数据库!" + e.Message); ;
                }

            }
            else
            {
                MessageBox.Show("请点击“选择文件”按钮,选择对应的数据库,选择对应的数据库", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
           

          
           
        }

        public Form1()
        {
            InitializeComponent();
        }

        private void textBox2_TextChanged(object sender, EventArgs e)
        {

        }

        private void button1_Click(object sender, EventArgs e)
        {
            string pwd = textBox1.Text.Trim();
            string pwd2 = textBox2.Text.Trim();

            if (!string.IsNullOrEmpty(pwd))
            {
                if (!string.IsNullOrEmpty(pwd2))
                {
                    ChangePassword(pwd2, pwd);

                    MessageBox.Show("给 " + this.FileAndPath + " 修改密码成功！！！", "给Sqlite数据库修改密码成功提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    textBox1.Text = "";
                    textBox2.Text = "";
                }
                else
                {
                    MessageBox.Show("新密码不能为空，请检查后重新输入！！！", "Sqlite数据库修改提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                    textBox2.Text = "";
                }
            }
            else
            {
                MessageBox.Show("请输入正确的数据库原密码！", "Sqlite数据库原密码提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                textBox1.Text = "";
            }
           
        }

        private void button2_Click(object sender, EventArgs e)
        {
            _con = new SQLiteConnection("Data Source=" + this.FileAndPath);
            if (!string.IsNullOrEmpty(textBox1.Text.Trim()))
            {
                if (textBox1.Text.Trim() == sqliteDatabasePWD)
                {
                    _con.SetPassword(textBox1.Text.Trim());
                }
                else
                {
                    MessageBox.Show("请在“打开数据库”按钮左侧输入正确的数据库密码！", "Sqlite数据库密码输入错误提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
               

            }
            else
            {
                MessageBox.Show("请点击“选择文件”按钮,选择对应的数据库,选择对应的数据库", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                MessageBox.Show("请在“打开数据库”按钮左侧输入正确的数据库密码！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
               
            }
            try
            {
                _con.Open();
            }
            catch (Exception ex)
            {
                throw new Exception("无法连接到数据库!" + ex.Message);
            }
        }

        private void button3_Click(object sender, EventArgs e)
        {
            if (!string.IsNullOrEmpty(textBox_filePath.Text))
            {
                if (!string.IsNullOrEmpty(textBox1.Text))
                {
                    sqliteDatabasePWD = textBox1.Text;
                    Query();
                    MessageBox.Show("查询数据成功，请看“查询成功”按钮左侧的标签内容", "查询成功提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                else
                {
                    MessageBox.Show("请在“打开数据库”按钮左侧的输入框中输入正确的密码", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                }
               
            }
            else
            {
                MessageBox.Show("请点击“选择文件”按钮,选择对应的数据库", "提示",MessageBoxButtons.OK,MessageBoxIcon.Warning);
            }
            
          
        }


        //插入数据
        private void insertData()
        {
            try

            {



                // sql = new SqLiteHelper("data source=mydb.db");

                sql = new SqliteHelper(this.FileAndPath,sqliteDatabasePWD);

                //创建名为table1的数据表

                sql.CreateTable("table1", new string[] { "ID", "Name", "Age", "Email" }, new string[] { "INTEGER", "TEXT", "INTEGER", "TEXT" });

                //插入两条数据

                sql.InsertValues("table1", new string[] { "1", "张三", "16", "[e-mail]" });

                //sql.InsertValues("table1", new string[] { "2", "李四", "17", "[e-mail]" });



                //更新数据，将Name="张三"的记录中的Name改为"小三"

                sql.UpdateValues("table1", new string[] { "Name" }, new string[] { "sunlei" }, "Name", "小三", "=");

                //删除Name="小三"且Age=16的记录,DeleteValuesOR方法类似



                sql.DeleteValuesAND("table1", new string[] { "Name", "Age" }, new string[] { "小三", "16" }, new string[] { "=", "=" });



                //读取整张表

                SQLiteDataReader reader = sql.ReadFullTable("table1");

                while (reader.Read())
                {

                    //读取ID

                    label3.Text = reader.GetInt32(reader.GetOrdinal("ID")).ToString();

                    //读取Name

                    label4.Text = reader.GetString(reader.GetOrdinal("Name")).ToString();

                    //读取Age

                    label5.Text = reader.GetInt32(reader.GetOrdinal("Age")).ToString();

                    //读取Email

                    label6.Text = reader.GetString(reader.GetOrdinal("Email")).ToString();

                }



            }

            catch (Exception ex)

            {



                MessageBox.Show(ex.Message);

            }
        }

        //查看数据
        private void Query()
        {
            try
            {
                if (!string.IsNullOrEmpty(sqliteDatabasePWD))
                {
                    sql = new SqliteHelper(this.FileAndPath, sqliteDatabasePWD);

                    if (textBox1.Text.Trim() == sqliteDatabasePWD)
                    {
                        SQLiteDataReader reader = sql.ReadFullTable("PeoplePower");
                        while (reader.Read())
                        {

                            //读取ID

                            label3.Text = reader.GetInt32(reader.GetOrdinal("ID")).ToString();

                            //读取Name

                            label4.Text = reader.GetString(reader.GetOrdinal("PowerType")).ToString();

                        }
                    }
                    else
                    {
                        MessageBox.Show("请输入正确的Sqlite数据库密码", "Sqlite数据库密码输入错误提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        textBox1.Text = "";
                        return;
                    }

                   
                }
                else
                {
                    return;
                }
               
            }
            catch (Exception e)
            {

                throw new Exception(e.Message);
            }
        }

        /// <summary>
        /// 选择文件及其对应的路径
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button4_Click(object sender, EventArgs e)
        {
            //选择文件及其对应路径
            string path= SelectFileAndPath(openFileDialog1);
            textBox_filePath.Text = path;

            if (!string.IsNullOrEmpty(path))
            {
                _fileAndPath = path;
            }
        }

        #region    选择文件夹、文件路径
        /// <summary>
        /// 选择文件目录
        /// </summary>
        /// <param name="folderBrowserDialog"></param>
        /// <returns></returns>
        private string  SelectFileContents(FolderBrowserDialog folderBrowserDialog)
        {
            string fileContent = null;
            if (folderBrowserDialog !=null  &&  folderBrowserDialog.ShowDialog() == DialogResult.OK)
            {
                fileContent = folderBrowserDialog.SelectedPath;
            }
            else
            {
                fileContent = null;
            }
            return fileContent;
        }

        /// <summary>
        /// 选择文件和对应路径
        /// </summary>
        /// <param name="openFileDialog"></param>
        /// <returns></returns>
        private string SelectFileAndPath(OpenFileDialog openFileDialog)
        {
            string fileAndPath = null;
            if (openFileDialog != null)
            {

                openFileDialog = new OpenFileDialog();
                openFileDialog.Filter = "All Files(*.*)|*.*|word Files(*.doc)|*.doc";
                if (openFileDialog.ShowDialog() == DialogResult.OK)
                {
                    fileAndPath = openFileDialog.FileName;
                }
                else
                {
                    fileAndPath = "";
                }
            }
            else
            {
                fileAndPath = null;
            }
            return fileAndPath;
        }


#endregion

        private void Form1_Load(object sender, EventArgs e)
        {

        }

        private void label1_Click(object sender, EventArgs e)
        {

        }

        /// <summary>
        /// 给没有加密的数据库设置密码
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void Btn_SettingsPWD_Click(object sender, EventArgs e)
        {
            if (!string.IsNullOrEmpty(textBox_filePath.Text))
            {
                //给数据库设置密码
                string sqliteOirginalPWD = TextBox_sqlitePwd.Text.Trim();
                if (!string.IsNullOrEmpty(sqliteOirginalPWD))
                {
                    _con = new SQLiteConnection("Data Source=" + this.FileAndPath);
                    _con.Open();
                    _con.ChangePassword(sqliteOirginalPWD);
                    _con.Close();
                    textBox1.Text = sqliteOirginalPWD;
                    sqliteDatabasePWD = sqliteOirginalPWD;

                    //禁用设置密码这一行的组件
                    TextBox_sqlitePwd.Enabled = false;
                    Btn_SettingsPWD.Enabled = false;

                    MessageBox.Show("给 " +FileAndPath +" Sqlite数据库设置密码成功！！！", "给Sqlite数据库设置密码成功提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                else
                {
                    MessageBox.Show("请点击“选择文件”按钮,选择对应的数据库，然后给“设置密码”按钮左侧的输入框，输入需要设置的数据库初始密码，最后点击“设置密码”按钮即可", "给Sqlite数据库设置初始密码提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
            }
            else
            {
                MessageBox.Show("请点击“选择文件”按钮,选择对应的数据库，然后给“设置密码”按钮左侧的输入框，输入需要设置的数据库初始密码，最后点击“设置密码”按钮即可", "给新的Sqlite数据库设置初始密码提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
        }
    }
}

三、使用方法： 

1、给没有加密的Sqlite数据库进行初始加密：

①首先点击“选择文件”按钮，选择对应的sqlite数据库，获取到该数据库

②输入初始密码后，点击“设置密码”按钮即可给Sqlite数据库进行加密；

2、给加密后的Sqlite数据库修改密码：

①首先点击“选择文件”按钮，选择对应的sqlite数据库，获取到该数据库

②在原密码输入框那里输入原密码

③在新密码那里输入新密码

④点击“修改密码”即可给Sqlite数据后修改密码成功。

3、检测是否修改成功：

①首先点击“选择文件”按钮，选择对应的sqlite数据库，获取到该数据库

②在原密码那里输入正确的Sqlite数据库密码

③点击“查看数据”按钮即可看到数据库的内容

注意：该工具的“查看数据”按钮功能只适用于我这里的数据库（下载地址为：），如果要查看自己的数据库需要在程序里面自己定义修改。