数据库敏感字段加解密便捷方式
因为项目临时新加需求对数据库的敏感字段加解密，需要对项目的整体代码进行修改，可愁怀我了
在网上发现有注解的方式可以进行针对字段加解密，感兴趣的可以去看原作者的文章https://blog.csdn.net/u012954706/article/details/105437768#comments
我这里只是留下备档防止文章不见了，你们看起来会很难受，所以看大佬的文章吧
1、数据库连接加解密
1.1、数据库连接配置
####################################
### DB
####################################
#durid
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.druid.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.druid.initialSize=5
spring.datasource.druid.minIdle=5
spring.datasource.druid.maxActive=20
spring.datasource.druid.maxWait=60000
spring.datasource.druid.timeBetweenEvictionRunsMillis=60000
spring.datasource.druid.minEvictableIdleTimeMillis=300000
spring.datasource.druid.validationQuery=SELECT 1 FROM DUAL
spring.datasource.druid.testWhileIdle=true
spring.datasource.druid.testOnBorrow=false
spring.datasource.druid.testOnReturn=false
#####################################
#### DB
####################################
spring.datasource.druid.url=jdbc:mysql://127.0.0.1:3306/hlj_demo?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true
spring.datasource.druid.username=GCBeAUOZNANpmXfIUPO42qx/dQP80Lae3BI7ABxQN2AzWhgQAG+S6Dhe
spring.datasource.druid.password=GCAfE1p20be+BX5TZsVlFe1/T1bQ+f2IhnjqOQKe7CJT7xgQ8YOQrf7U
####################################
#是否需要数据连接加密
spring.datasource.encrypt=true
1.2、Java数据库连接
package com.fintech.confin.web.config;
import com.alibaba.druid.pool.DruidDataSource;
import com.fintech.confin.sensitivity.KeycenterUtils;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import javax.sql.DataSource;
/**
* @author HealerJean
* @ClassName DateSourceConfig
* @date 2020/4/9 10:43.
* @Description
*/
@Configuration
public class DateSourceConfig {
@Value("${spring.datasource.druid.driver-class-name}")
private String driverClassName;
@Value("${spring.datasource.druid.url}")
private String dbUrl;
@Value("${spring.datasource.druid.username}")
private String username;
@Value("${spring.datasource.druid.password}")
private String password;
@Value("${spring.datasource.druid.initialSize}")
private int initialSize;
@Value("${spring.datasource.druid.minIdle}")
private int minIdle;
@Value("${spring.datasource.druid.maxActive}")
private int maxActive;
@Value("${spring.datasource.druid.maxWait}")
private int maxWait;
@Value("${spring.datasource.druid.timeBetweenEvictionRunsMillis}")
private int timeBetweenEvictionRunsMillis;
@Value("${spring.datasource.druid.minEvictableIdleTimeMillis}")
private int minEvictableIdleTimeMillis;
@Value("${spring.datasource.druid.validationQuery}")
private String validationQuery;
@Value("${spring.datasource.druid.testWhileIdle}")
private boolean testWhileIdle;
@Value("${spring.datasource.druid.testOnBorrow}")
private boolean testOnBorrow;
@Value("${spring.datasource.druid.testOnReturn}")
private boolean testOnReturn;
@Value("${spring.datasource.encrypt}")
private boolean encrypt;
@Bean(name = "dataSource")
public DataSource dataSource(KeycenterUtils keycenterUtils) {
DruidDataSource datasource = new DruidDataSource();
datasource.setDriverClassName(driverClassName);
datasource.setUrl(dbUrl);
if (encrypt) {
datasource.setUsername(keycenterUtils.decrypt(username));
datasource.setPassword(keycenterUtils.decrypt(password));
} else {
datasource.setUsername(username);
datasource.setPassword(password);
}
datasource.setInitialSize(initialSize);
datasource.setMinIdle(minIdle);
datasource.setMaxActive(maxActive);
datasource.setMaxWait(maxWait);
datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
datasource.setValidationQuery(validationQuery);
datasource.setTestWhileIdle(testWhileIdle);
datasource.setTestOnBorrow(testOnBorrow);
datasource.setTestOnReturn(testOnReturn);
return datasource;
}
}
2、数据库字段加解密
2.1、KeyCenterUtils：加解密工具类
package com.healerjean.proj.config.keycenter.one;
import org.springframework.stereotype.Service;
import java.util.Base64;
/**
* @author HealerJean
* @ClassName AES
* @date 2020/4/9 14:28.
* @Description
*/
@Service
public class KeyCenterUtils {
/**
* 自己写加密逻辑
*/
public String encrypt(String src) {
try {
String result = Base64.getEncoder().encodeToString(src.getBytes("UTF-8"));
return result;
} catch (Exception e) {
throw new RuntimeException("encrypt fail!", e);
}
}
/**
* 自己写解密逻辑
*/
public String decrypt(String src) {
try {
byte[] asBytes = Base64.getDecoder().decode(src);
String result = new String(asBytes, "UTF-8");
return result;
} catch (Exception e) {
throw new RuntimeException("decrypt fail!", e);
}
}
}
2.2、CustomTypeHandler数据库字段加解密控制器
package com.healerjean.proj.config.keycenter.one;
/**
* @author HealerJean
* @ClassName AESTypeHandler
* @date 2020/4/9 14:27.
* @Description
*/
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
@Service
public class CustomTypeHandler<T> extends BaseTypeHandler<T> {
@Autowired
private KeyCenterUtils keyCenterUtils;
public CustomTypeHandler() {
}
@Override
public void setNonNullParameter(PreparedStatement ps, int i, Object parameter, JdbcType jdbcType) throws SQLException {
ps.setString(i, this.keyCenterUtils.encrypt((String)parameter));
}
@Override
public T getNullableResult(ResultSet rs, String columnName) throws SQLException {
String columnValue = rs.getString(columnName);
//有一些可能是空字符
return StringUtils.isBlank(columnValue) ? (T)columnValue : (T)this.keyCenterUtils.decrypt(columnValue);
}
@Override
public T getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
String columnValue = rs.getString(columnIndex);
return StringUtils.isBlank(columnValue) ? (T)columnValue : (T)this.keyCenterUtils.decrypt(columnValue);
}
@Override
public T getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
String columnValue = cs.getString(columnIndex);
return StringUtils.isBlank(columnValue) ? (T)columnValue : (T)this.keyCenterUtils.decrypt(columnValue);
}
}
2.3、Handle的使用
2.3.1、数据层实体类注解
package com.healerjean.proj.data.entity;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableName;
import com.healerjean.proj.config.keycenter.one.CustomTypeHandler;
import lombok.Data;
import java.util.Date;
@Data
@TableName(autoResultMap = true) //有了这个BaseMapper查询的结果才能解密
public class User {
private Long id;
private String name;
private Integer age;
//有了这个数据库BaseMapper插入的时候才能加密
@TableField(typeHandler = CustomTypeHandler.class)
private String telPhone;
@TableField(typeHandler = CustomTypeHandler.class)
private String email;
private Date createDate;
private Date createTime;
}
2.3.2、自定义sql查询的配置
如果不是mybatisPlus的 BaseMapper内部的方法，则需要我们自己放入我们自定义的typeHandler
@Results({
@Result(column = "email", property = "email", typeHandler = CustomTypeHandler.class),
@Result(column = "tel_phone", property = "telPhone", typeHandler = CustomTypeHandler.class)})
@Select("select * from user where id = #{id}")
List<User> selectDncryptList(Long id);
2.3.3、测试
User中的数据都是正常的 。不是密文。因为我们只讲入库的数据设置了密文。并不会改变User对象本身
@Test
public void encrypt(){
List<User> users = null ;
//插入数据
User user = new User();
user.setName("name");
user.setAge(12);
user.setEmail("healerjean@gmail.com");
user.setTelPhone("18841256");
userMapper.insert(user);
//更新
user.setEmail("12456@gmail.com");
userMapper.updateById(user);
//查询 ：列表查询
users = userMapper.selectList(null);
System.out.println(users);
//查询 ：根据Id查询
User user1 = userMapper.selectById(user.getId());
System.out.println(user1);
//自定义sql查询
users = userMapper.selectDncryptList(user.getId());
System.out.println(users);
}
2.4、敏感字段查询：（需要精确查询）
1、数据库连接加解密1.1、数据库连接配置####################################### DB#####################################duridspring.datasource.type=com.alibaba.druid.pool.DruidDataSourcespring.datasource.druid.driver-class-name=com.mysql.jdbc.Driverspring.datasource.druid.initialSize=5spring.datasource.druid.minIdle=5spring.datasource.druid.maxActive=20spring.datasource.druid.maxWait=60000spring.datasource.druid.timeBetweenEvictionRunsMillis=60000spring.datasource.druid.minEvictableIdleTimeMillis=300000spring.datasource.druid.validationQuery=SELECT 1 FROM DUALspring.datasource.druid.testWhileIdle=truespring.datasource.druid.testOnBorrow=falsespring.datasource.druid.testOnReturn=false因为数据库中是密文，所以查询的时候，需要我们先加密后才能查
// 根据敏感字段查询
Wrapper<User> userWrapper = new QueryWrapper<User>().lambda()
.select(User::getEmail)
.eq(User::getEmail, keyCenterUtils.encrypt("healerjean@gmail.com"));
users = userMapper.selectList(userWrapper);
System.out.println(users);
######################################### DB####################################spring.datasource.druid.url=jdbc:mysql://127.0.0.1:3306/hlj_demo?useUnicode=true&characterEncoding=UTF-8&autoReconnect=truespring.datasource.druid.username=GCBeAUOZNANpmXfIUPO42qx/dQP80Lae3BI7ABxQN2AzWhgQAG+S6Dhespring.datasource.druid.password=GCAfE1p20be+BX5TZsVlFe1/T1bQ+f2IhnjqOQKe7CJT7xgQ8YOQrf7U####################################
#是否需要数据连接加密spring.datasource.encrypt=true123456789101112131415161718192021222324252627282930311.2、Java数据库连接package com.fintech.confin.web.config;
import com.alibaba.druid.pool.DruidDataSource;import com.fintech.confin.sensitivity.KeycenterUtils;import org.springframework.beans.factory.annotation.Value;import org.springframework.context.annotation.Bean;import org.springframework.context.annotation.Configuration;
import javax.sql.DataSource;
/** * @author HealerJean * @ClassName DateSourceConfig * @date 2020/4/9 10:43. * @Description */@Configurationpublic class DateSourceConfig {@Value("${spring.datasource.druid.driver-class-name}")private String driverClassName;@Value("${spring.datasource.druid.url}")private String dbUrl;@Value("${spring.datasource.druid.username}")private String username;@Value("${spring.datasource.druid.password}")private String password;@Value("${spring.datasource.druid.initialSize}")private int initialSize;@Value("${spring.datasource.druid.minIdle}")private int minIdle;@Value("${spring.datasource.druid.maxActive}")private int maxActive;@Value("${spring.datasource.druid.maxWait}")private int maxWait;@Value("${spring.datasource.druid.timeBetweenEvictionRunsMillis}")private int timeBetweenEvictionRunsMillis;@Value("${spring.datasource.druid.minEvictableIdleTimeMillis}")private int minEvictableIdleTimeMillis;@Value("${spring.datasource.druid.validationQuery}")private String validationQuery;@Value("${spring.datasource.druid.testWhileIdle}")private boolean testWhileIdle;@Value("${spring.datasource.druid.testOnBorrow}")private boolean testOnBorrow;@Value("${spring.datasource.druid.testOnReturn}")private boolean testOnReturn;@Value("${spring.datasource.encrypt}")private boolean encrypt;@Bean(name = "dataSource")public DataSource dataSource(KeycenterUtils keycenterUtils) {DruidDataSource datasource = new DruidDataSource();datasource.setDriverClassName(driverClassName);datasource.setUrl(dbUrl);if (encrypt) {datasource.setUsername(keycenterUtils.decrypt(username));datasource.setPassword(keycenterUtils.decrypt(password));} else {datasource.setUsername(username);datasource.setPassword(password);}datasource.setInitialSize(initialSize);datasource.setMinIdle(minIdle);datasource.setMaxActive(maxActive);datasource.setMaxWait(maxWait);datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);datasource.setValidationQuery(validationQuery);datasource.setTestWhileIdle(testWhileIdle);datasource.setTestOnBorrow(testOnBorrow);datasource.setTestOnReturn(testOnReturn);return datasource;}}
1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677782、数据库字段加解密2.1、KeyCenterUtils：加解密工具类package com.healerjean.proj.config.keycenter.one;
import org.springframework.stereotype.Service;
import java.util.Base64;
/** * @author HealerJean * @ClassName AES * @date 2020/4/9 14:28. * @Description */@Servicepublic class KeyCenterUtils {
/** * 自己写加密逻辑 */ public String encrypt(String src) { try { String result = Base64.getEncoder().encodeToString(src.getBytes("UTF-8")); return result; } catch (Exception e) { throw new RuntimeException("encrypt fail!", e); } }
/** * 自己写解密逻辑 */ public String decrypt(String src) { try { byte[] asBytes = Base64.getDecoder().decode(src); String result = new String(asBytes, "UTF-8"); return result; } catch (Exception e) { throw new RuntimeException("decrypt fail!", e); } }
}
1234567891011121314151617181920212223242526272829303132333435363738394041422.2、CustomTypeHandler数据库字段加解密控制器package com.healerjean.proj.config.keycenter.one;
/** * @author HealerJean * @ClassName AESTypeHandler * @date 2020/4/9 14:27. * @Description */
import java.sql.CallableStatement;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;
import org.apache.commons.lang3.StringUtils;import org.apache.ibatis.type.BaseTypeHandler;import org.apache.ibatis.type.JdbcType;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Service;
@Servicepublic class CustomTypeHandler<T> extends BaseTypeHandler<T> {
@Autowired private KeyCenterUtils keyCenterUtils;
public CustomTypeHandler() { }
@Override public void setNonNullParameter(PreparedStatement ps, int i, Object parameter, JdbcType jdbcType) throws SQLException { ps.setString(i, this.keyCenterUtils.encrypt((String)parameter)); } @Override public T getNullableResult(ResultSet rs, String columnName) throws SQLException { String columnValue = rs.getString(columnName); //有一些可能是空字符 return StringUtils.isBlank(columnValue) ? (T)columnValue : (T)this.keyCenterUtils.decrypt(columnValue); }
@Override public T getNullableResult(ResultSet rs, int columnIndex) throws SQLException { String columnValue = rs.getString(columnIndex); return StringUtils.isBlank(columnValue) ? (T)columnValue : (T)this.keyCenterUtils.decrypt(columnValue); }
@Override public T getNullableResult(CallableStatement cs, int columnIndex) throws SQLException { String columnValue = cs.getString(columnIndex); return StringUtils.isBlank(columnValue) ? (T)columnValue : (T)this.keyCenterUtils.decrypt(columnValue); }}
1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253542.3、Handle的使用2.3.1、数据层实体类注解package com.healerjean.proj.data.entity;
import com.baomidou.mybatisplus.annotation.TableField;import com.baomidou.mybatisplus.annotation.TableName;import com.healerjean.proj.config.keycenter.one.CustomTypeHandler;import lombok.Data;
import java.util.Date;
@Data@TableName(autoResultMap = true) //有了这个BaseMapper查询的结果才能解密public class User { private Long id; private String name; private Integer age;
//有了这个数据库BaseMapper插入的时候才能加密 @TableField(typeHandler = CustomTypeHandler.class) private String telPhone;
@TableField(typeHandler = CustomTypeHandler.class) private String email;
private Date createDate; private Date createTime;}
1234567891011121314151617181920212223242526272.3.2、自定义sql查询的配置如果不是mybatisPlus的 BaseMapper内部的方法，则需要我们自己放入我们自定义的typeHandler
@Results({ @Result(column = "email", property = "email", typeHandler = CustomTypeHandler.class), @Result(column = "tel_phone", property = "telPhone", typeHandler = CustomTypeHandler.class)})@Select("select * from user where id = #{id}")List<User> selectDncryptList(Long id);123452.3.3、测试User中的数据都是正常的 。不是密文。因为我们只讲入库的数据设置了密文。并不会改变User对象本身
@Testpublic void encrypt(){ List<User> users = null ;
//插入数据 User user = new User(); user.setName("name"); user.setAge(12); user.setEmail("healerjean@gmail.com"); user.setTelPhone("18841256"); userMapper.insert(user);
//更新 user.setEmail("12456@gmail.com"); userMapper.updateById(user);
//查询 ：列表查询 users = userMapper.selectList(null); System.out.println(users);
//查询 ：根据Id查询 User user1 = userMapper.selectById(user.getId()); System.out.println(user1);
//自定义sql查询 users = userMapper.selectDncryptList(user.getId()); System.out.println(users);
}
12345678910111213141516171819202122232425262728293031323334352.4、敏感字段查询：（需要精确查询）因为数据库中是密文，所以查询的时候，需要我们先加密后才能查
// 根据敏感字段查询Wrapper<User> userWrapper = new QueryWrapper<User>().lambda() .select(User::getEmail) .eq(User::getEmail, keyCenterUtils.encrypt("healerjean@gmail.com"));
users = userMapper.selectList(userWrapper);System.out.println(users);————————————————版权声明：本文为CSDN博主「HealerJean.」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。原文链接：https://blog.csdn.net/u012954706/article/details/105437768
