数据库连接配置加密-数据库加密方式

1.项目结构

开发工具：IDEA+Maven

项目结构

2.配置2.1.Maven配置

Maven配置文件pom.xml数据库连接配置加密，增加Mysql8数据库连接类库、Mybatis类库、Log4j2类库、Base64加解密依赖类库

<?xml version="1.0" encoding="UTF-8"?><project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <parent> <artifactId>mybatis-configuration-properties</artifactId> <groupId>org.poiuy</groupId> <version>1.0-SNAPSHOT</version> </parent> <modelVersion>4.0.0</modelVersion> <artifactId>mybatis-configuration-properties-code</artifactId> <dependencies> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.22</version> </dependency> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version>3.5.5</version> </dependency> <dependency> <groupId>org.apache.logging.log4j</groupId> <artifactId>log4j-core</artifactId> <version>2.12.1</version> </dependency> <dependency> <groupId>org.apache.logging.log4j</groupId> <artifactId>log4j-api</artifactId> <version>2.12.1</version> </dependency> <dependency> <groupId>commons-codec</groupId> <artifactId>commons-codec</artifactId> <version>1.13</version> </dependency> </dependencies></project>2.2.Mybatis配置文件

Mybatis配置文件mybatis_config.xml数据库连接配置加密，此处主要配置properties、dataSource以及Mapper文件

<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN""http://mybatis.org/dtd/mybatis-3-config.dtd"><configuration> <!-- 另外可以通过properties配置文件数据库连接配置加密的方式将properties属性提取出来数据库连接配置加密，在另外一个文件中进行配置，当需要修改时，只需要提取出来的配置文件即可 --> <!-- <properties resource="db/db.properties"/> --> <properties> <property name="db.driver" value="com.mysql.cj.jdbc.Driver"/> <property name="db.url" value="jdbc:mysql://192.168.245.128:9906/poiuy_study"/> <property name="db.username" value="root"/> <property name="db.password" value="xxx"/> </properties> <environments default="development"> <environment id="development"> <transactionManager type="JDBC"/> <dataSource type="POOLED"> <property name="driver" value="${db.driver}" /> <property name="url" value="${db.url}" /> <property name="username" value="${db.username}" /> <property name="password" value="${db.password}" /> </dataSource> </environment> </environments> <mappers> <mapper resource="mapper/MybatisUserMapper.xml"/> </mappers></configuration>2.3..数据库配置

配置文件db.properties，要注意key值与Mybatis配置文件中保持相同

db.driver = com.mysql.cj.jdbc.Driverdb.url = jdbc:mysql://192.168.245.128:9906/poiuy_studydb.username = rootdb.password = xxx3.DAO层开发

Mapper接口，添加简单的增删改查功能

package org.poiuy.mybatis.configuration.properties.code.dao;import org.apache.ibatis.annotations.Param;import org.poiuy.mybatis.configuration.properties.code.domain.MybatisUser;/** * @author Poiuy * @Description */public interface MybatisUserMapper { public int insert(MybatisUser user); public int update(MybatisUser user); public int delete(@Param("id") int id); public MybatisUser selectByName(@Param("username") String username);}

对应的xml配置文件

<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mapper namespace="org.poiuy.mybatis.configuration.properties.code.dao.MybatisUserMapper"> <!-- //添加 public int insert(TestUser user); --> <insert id="insert" parameterType="org.poiuy.mybatis.configuration.properties.code.domain.MybatisUser"> INSERT INTO mybatis_user (username,secure,age,brith,email) VALUE (#{username},#{secure},#{age},#{brith},#{email}); </insert> <!-- //删除 public int delete(int id); --> <delete id="delete" parameterType="java.lang.Integer"> DELETE FROM mybatis_user WHERE id = #{id}; </delete> <!-- //查找 public TestUser select(int id); --> <select id="selectByName" parameterType="java.lang.String" resultType="org.poiuy.mybatis.configuration.properties.code.domain.MybatisUser"> SELECT * FROM mybatis_user WHERE username = #{username} </select> <!-- //更新 public int update (TestUser user); --> <update id="update" parameterType="org.poiuy.mybatis.configuration.properties.code.domain.MybatisUser"> UPDATE mybatis_user SET username = #{username} , secure = #{secure} , age = #{age} , brith = #{brith} , email = #{email} WHERE id = #{id} </update></mapper>4.Domain类

MybatisUser对应的数据库中的表

package org.poiuy.mybatis.configuration.properties.code.domain;import java.time.LocalDate;/** * @author Poiuy * @Description * CREATE TABLE `mybatis_user` ( * `id` int NOT NULL AUTO_INCREMENT, * `username` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, * `secure` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, * `age` int NOT NULL, * `brith` date NOT NULL, * `email` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, * PRIMARY KEY (`id`) * ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 */public class MybatisUser { private int id; private String username; private String secure; private int age; private LocalDate brith; private String email; ... 省略get set *** ...}5.启动类

读取配置文件通过mybatis对数据库进行操作

package org.poiuy.mybatis.configuration.properties.value;import org.apache.ibatis.io.Resources;import org.apache.ibatis.session.SqlSession;import org.apache.ibatis.session.SqlSessionFactory;import org.apache.ibatis.session.SqlSessionFactoryBuilder;import org.apache.logging.log4j.LogManager;import org.apache.logging.log4j.Logger;import org.poiuy.mybatis.configuration.properties.value.dao.MybatisUserMapper;import org.poiuy.mybatis.configuration.properties.value.domain.MybatisUser;import java.io.IOException;import java.time.LocalDate;/** * @author Poiuy * @Description */public class Application { private static final Logger logger = LogManager.getLogger(Application.class); public static void main(String[] args) { try { SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsReader("mybatis/mybatis_config.xml")); SqlSession session = factory.openSession(); MybatisUserMapper mybatisUserMapper = session.getMapper(MybatisUserMapper.class); MybatisUser user = new MybatisUser(); user.setUsername("xxx"); user.setSecure("xxx"); user.setAge(x); user.setEmail("1100@qq.com"); user.setBrith(LocalDate.of(1991,03,22)); int count = mybatisUserMapper.insert(user); logger.trace("插入数量：" + count); user.setEmail("1100@163.com"); count = mybatisUserMapper.update(user); logger.trace("更新数量：" + count); MybatisUser poi = mybatisUserMapper.selectByName("xxx"); logger.trace(poi.getUsername() + " -- " + poi.getEmail()); count = mybatisUserMapper.delete(poi.getId()); logger.trace("删除数量：" + count); } catch (IOException e) { e.printStackTrace(); } }}6.加密方式配置6.1.增加加密解密类

如果需要对配置文件中的数据库连接进行加密，则可以通过以下方式

添加Base64辅助类，包含加密解密 ***

package org.poiuy.mybatis.configuration.properties.code.util.secure;import org.apache.commons.codec.binary.Base64;import java.io.UnsupportedEncodingException;public class Base64Util { /** * 加密 */ public static String getBase64(String str) { byte[] b = null; String s = null; try { b = str.getBytes("utf-8"); } catch (UnsupportedEncodingException e) { e.printStackTrace(); } if (b != null) { s = new Base64().encodeAsString(b); } return s; } /** * 解密 */ public static String getFromBase64(String s) { byte[] b = null; String result = null; if (s != null) { try { b = new Base64().decodeBase64(s); result = new String(b, "utf-8"); } catch (Exception e) { e.printStackTrace(); } } return result; } public static void main(String[] args) { System.out.println(Base64Util.getBase64("root")); System.out.println(Base64Util.getFromBase64("bGxpYW4yMDc=")); }}6.2.数据库配置中使用加密后的字符串

然后通过Base64Util辅助类将数据库账号密码进行加密，然后填写到数据库配置文件中

db.driver = com.mysql.cj.jdbc.Driverdb.url = jdbc:mysql://192.168.245.128:9906/poiuy_studydb.username = cm9vdA==db.password = bGxpYW4yMDc=6.3.使用Properties参数传递的方式获得SqlSession

最后读取配置文件时对账号密码进行解密后生成SqlSessionFactory

package org.poiuy.mybatis.configuration.properties.code.util.mybatis;import org.apache.ibatis.io.Resources;import org.apache.ibatis.session.SqlSessionFactory;import org.apache.ibatis.session.SqlSessionFactoryBuilder;import org.poiuy.mybatis.configuration.properties.code.util.secure.Base64Util;import java.io.IOException;import java.io.InputStreamReader;import java.util.Properties;/** * 对配置进行解密 * 三种配置的优先级： * 1.在properties元素体内指定的属性首先被加载 * 2.根据properties元素中的resource属性读取类路径下的属性文件，并覆盖已读取的同名属性 * 3.读取作为 *** 参数传递的属性，并覆盖已读取的同名属性 */public class MybatisPropertiesUtil { private static SqlSessionFactory factory = null; /** * 使用XML文件创建SqlSessionFactory * @Description: TODO */ public static SqlSessionFactory getFactory() throws IOException { if(factory == null) { String source = "mybatis/mybatis_config.xml"; String db = "db/db.properties"; InputStreamReader sourceReader = new InputStreamReader(Resources.getResourceAsStream(source)); InputStreamReader dbReader = new InputStreamReader(Resources.getResourceAsStream(db)); Properties prop = new Properties(); prop.load(dbReader); prop.setProperty("db.username", Base64Util.getFromBase64(prop.getProperty("db.username"))); prop.setProperty("db.password", Base64Util.getFromBase64(prop.getProperty("db.password"))); //使用程序参数传递 factory = new SqlSessionFactoryBuilder().build(sourceReader,prop); return factory; } return factory; }}

启动类中使用MybatisPropertiesUtil获得SqlSession，在继续后续的逻辑

public class Application { public static void main(String[] args) { try { // SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsReader("mybatis/mybatis_config.xml")); // SqlSession session = factory.openSession(); // MybatisUserMapper mybatisUserMapper = session.getMapper(MybatisUserMapper.class); SqlSession session = MybatisPropertiesUtil.getFactory().openSession(); MybatisUserMapper mybatisUserMapper = session.getMapper(MybatisUserMapper.class); ... } catch (IOException e) { e.printStackTrace(); } }}