从0开学习shardingjdbc之加密存储

法学小鑫
272 阅读4分钟

笔者的上一篇文章介绍了shardingjdbc的分库分表逻辑，本篇文章将要介绍加密存储的逻辑。

现实中有两个情况，第一种是密码列和原文列同时存在的情况，第二种是只有密码列同时存在的情况。本文对两种情况均做了代码示例。

本项目基于springboot + mybatis plus + druid + shardingjdbc实现了上述逻辑，代码已经上传到github ：github.com/faxuexiaoxi…

其中passwordDemo是本次项目的代码示例

image.png

项目代码结构也是非常的清晰明了，如下所示

image.png

老规矩，先介绍用到的技术栈。

框架版本
springboot3.2.2
mybatis3.0.3
mybatis plus3.5.5
druid1.2.20
shardingjdbc5.2.1
  1. 引入依赖
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter</artifactId>
</dependency>

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
    <scope>test</scope>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter</artifactId>
</dependency>

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
    <scope>test</scope>
</dependency>
<!--    Mybatis    -->
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
</dependency>
<!--     Mybatis Plus    -->
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-spring-boot3-starter</artifactId>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!-- Druid 数据库连接池 -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.2.20</version>
</dependency>

<!--        shardingsphere-->
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
    <version>5.2.1</version>
</dependency>

<!--    shardingsphere和2.2不兼容，需要使用1.33，-->
<dependency>
    <groupId>org.yaml</groupId>
    <artifactId>snakeyaml</artifactId>
    <version>1.33</version>
</dependency>

2.mysql建表语句,建表语句位于init.sql中。各位在实现代码的时候，可能需要先创建一个数据库，笔者创建的是sharding的数据库。

image.png

image.png 其中user_0是同时有加密列和原文列的表，user_1只有加密列的表。

DROP TABLE IF EXISTS `user_0`;
CREATE TABLE if not exists `user_0`
(
    `userId`        BIGINT         NOT NULL AUTO_INCREMENT,
    `userName`      VARCHAR(45) NOT NULL,
    `userPassword`  VARCHAR(45) NOT NULL,
    `userPassword_` VARCHAR(45) NOT NULL,
    PRIMARY KEY (`userId`)
);

DROP TABLE IF EXISTS `user_1`;
CREATE TABLE if not exists `user_1`
(
    `userId`        BIGINT         NOT NULL AUTO_INCREMENT,
    `userName`      VARCHAR(45) NOT NULL,
    `userPassword_` VARCHAR(45) NOT NULL,
    PRIMARY KEY (`userId`)
)
  1. entity类 user类同时存在加密列和原文列。UserNoPassword只有加密列
package org.faxuexiaoxin.passworddemo.entity;

import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableName;

@TableName("user")
public class User {

    /**
     * 玩家id
     */
    @TableField("userId")
    private Long userid;

    /**
     * 玩家名称
     */
    @TableField("userName")
    private String userName;

    /**
     * 玩家密码
     */
    @TableField("userPassword")
    private String userPassword;

    /**
     * 玩家加密后的密码
     */
    @TableField("userPassword_")
    private String userPassword_;

    public Long getUserid() {
        return userid;
    }

    public void setUserid(Long userid) {
        this.userid = userid;
    }

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public String getUserPassword() {
        return userPassword;
    }

    public void setUserPassword(String userPassword) {
        this.userPassword = userPassword;
    }

    public String getUserPassword_() {
        return userPassword_;
    }

    public void setUserPassword_(String userPassword_) {
        this.userPassword_ = userPassword_;
    }

    @Override
    public String toString() {
        return "User{" +
                "userid=" + userid +
                ", userName='" + userName + ''' +
                ", userPassword='" + userPassword + ''' +
                ", userPassword_='" + userPassword_ + ''' +
                '}';
    }
}

package org.faxuexiaoxin.passworddemo.entity;

import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableName;

/**
 * 没有加密字段的User
 */
@TableName("user_no_password")
public class UserNoPassword {

    /**
     * 玩家id
     */
    @TableField("userId")
    private Long userid;

    /**
     * 玩家名称
     */
    @TableField("userName")
    private String userName;

    /**
     * 玩家密码 原文列
     */
    @TableField("userPassword")
    private String userPassword;

    public Long getUserid() {
        return userid;
    }

    public void setUserid(Long userid) {
        this.userid = userid;
    }

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public String getUserPassword() {
        return userPassword;
    }

    public void setUserPassword(String userPassword) {
        this.userPassword = userPassword;
    }

    @Override
    public String toString() {
        return "UserNoPassword{" +
                "userid=" + userid +
                ", userName='" + userName + ''' +
                ", userPassword='" + userPassword + ''' +
                '}';
    }
}
  1. mapper层，使用mybatis plus提供的baseMapper即可
package org.faxuexiaoxin.passworddemo.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.faxuexiaoxin.passworddemo.entity.User;

public interface UserMapper extends BaseMapper<User> {

}

package org.faxuexiaoxin.passworddemo.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.faxuexiaoxin.passworddemo.entity.UserNoPassword;

public interface UserNoPasswordMapper extends BaseMapper<UserNoPassword> {
}

5.入口类，开启mapper即可。

package org.faxuexiaoxin.passworddemo;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
@MapperScan(basePackages = "org.faxuexiaoxin.passworddemo.mapper")
public class PasswordDemoApplication {

    public static void main(String[] args) {
        SpringApplication.run(PasswordDemoApplication.class, args);
    }

}

6.测试类：

package org.faxuexiaoxin.passworddemo.mapper;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import org.faxuexiaoxin.passworddemo.entity.User;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.List;

import static org.junit.jupiter.api.Assertions.*;

@SpringBootTest
class UserMapperTest {

    @Autowired
    private UserMapper userMapper;

    @Test
    void testInsertData() {
        User user = new User();
        user.setUserPassword("123123");
        user.setUserName("插入有原文列");

        userMapper.insert(user);
    }


    @Test
    void testQuery() {
        QueryWrapper<User> userQueryWrapper = new QueryWrapper<>();
        userQueryWrapper.eq("userPassword","123123");

        List<User> users = userMapper.selectList(userQueryWrapper);
        System.out.println(users);
    }
}

package org.faxuexiaoxin.passworddemo.mapper;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import org.faxuexiaoxin.passworddemo.entity.UserNoPassword;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.List;

import static org.junit.jupiter.api.Assertions.*;

@SpringBootTest
class UserNoPasswordMapperTest {

    @Autowired
    private UserNoPasswordMapper mapper;

    @Test
    void testInsert() {
        UserNoPassword userNoPassword = new UserNoPassword();
        userNoPassword.setUserName("只有原文列的数据");
        userNoPassword.setUserPassword("加密数据");
        mapper.insert(userNoPassword);
    }

    @Test
    void testQuery() {
        QueryWrapper<UserNoPassword> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("userPassword","加密数据");
        List<UserNoPassword> userNoPasswords = mapper.selectList(queryWrapper);
        System.out.println(userNoPasswords);
    }
}

7.核心配置：application.properties

spring.application.name=passwordDemo

#打印sql
spring.shardingsphere.props.sql-show=true

#配置sharding的数据源
spring.shardingsphere.datasource.names=m0

spring.shardingsphere.datasource.m0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m0.dirver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m0.url=jdbc:mysql://localhost:3306/sharding
spring.shardingsphere.datasource.m0.username=root
spring.shardingsphere.datasource.m0.password=root

######################################### 加密列和原文列同时存在的表 ####################################
#加密列和原文列同时存在的表
spring.shardingsphere.rules.sharding.tables.user.actual-data-nodes=m0.user_0

#配置分布式id的算法
spring.shardingsphere.rules.sharding.tables.user.key-generate-strategy.column=userid
spring.shardingsphere.rules.sharding.tables.user.key-generate-strategy.key-generator-name=alg_snow
spring.shardingsphere.rules.sharding.key-generators.alg_snow.type=SNOWFLAKE
spring.shardingsphere.rules.sharding.key-generators.alg_snow.props.worker-id=1

#加密策略（下面的配置是同时存在加密列和原文列的配置）
#查询加密列：true表示 查询sql是使用加密数据。否则使用原文列查询。
spring.shardingsphere.rules.encrypt.tables.user.query-with-cipher-column=true
#原文列
spring.shardingsphere.rules.encrypt.tables.user.columns.userPassword.plain-column=userPassword
#加密列
spring.shardingsphere.rules.encrypt.tables.user.columns.userPassword.cipher-column=userPassword_
#加密算法
spring.shardingsphere.rules.encrypt.tables.user.columns.userPassword.encryptor-name=user_password_encrypted
#加密算法的配置：MD5，AES、SM3、SM4等。MD5是hash算法，不需要配置秘钥
spring.shardingsphere.rules.encrypt.encryptors.user_password_encrypted.type=MD5
######################################### 加密列和原文列同时存在的表 ####################################

######################################### 只有加密列存在的表 ####################################
#只有加密列的表
spring.shardingsphere.rules.sharding.tables.user_no_password.actual-data-nodes=m0.user_1

#配置分布式id的算法
spring.shardingsphere.rules.sharding.tables.user_no_password.key-generate-strategy.column=userid
spring.shardingsphere.rules.sharding.tables.user_no_password.key-generate-strategy.key-generator-name=password_alg_snow
spring.shardingsphere.rules.sharding.key-generators.password_alg_snow.type=SNOWFLAKE
spring.shardingsphere.rules.sharding.key-generators.password_alg_snow.props.worker-id=1

#加密策略（下面的配置是同时存在加密列和原文列的配置）
#查询加密列：true表示 查询sql是使用加密数据。否则使用原文列查询。
spring.shardingsphere.rules.encrypt.tables.user_no_password.query-with-cipher-column=true
#加密列
spring.shardingsphere.rules.encrypt.tables.user_no_password.columns.userPassword.cipher-column=userPassword_
#加密算法
spring.shardingsphere.rules.encrypt.tables.user_no_password.columns.userPassword.encryptor-name=user_without_password_encrypted
#加密算法的配置：MD5，AES、SM3、SM4等。MD5是hash算法，不需要配置秘钥
spring.shardingsphere.rules.encrypt.encryptors.user_without_password_encrypted.type=AES
#部分算法配置加密秘钥，例如：aes-key-value。
spring.shardingsphere.rules.encrypt.encryptors.user_without_password_encrypted.props.aes-key-value=abcdef
######################################### 只有加密列存在的表 ####################################

8.运行测试UserMapperTtest

运行testInserData如图所示，数据插入成功，路由正确

image.png

image.png 运行testQuery，如图所示，使用了加密列进行查询（同时我们也发现了，返回的数据格式不正确，userPassword字段原本是放原文列的，但是放入了加密列） image.png 9.运行测试UserNoPasswordMapperTest 插入数据测试如下：

image.png

image.png

查询测试如下：

image.png 10.总结

  • shardingjdbc使用加密列查询的逻辑比较简单，只需要配置application.properties即可。
  • 同时存在密文列和原文列的时候查询出来的数据似乎映射有问题（ps：如果后续研究出来解决方案将更新在文章底部）
avatar
文章
阅读
粉丝