Mybatis-BaseTypeHandler实现数据层数据加解密

我会带着你远行 2022-10-13 05:27 17阅读 0赞

需求：数据库保存的数据需要加密，然后查询的时候解密  
解决方案：继承BaseTypeHandler，然后重写具体的set、get方法。本文加解密使用了Base64算法。

### BaseTypeHandler实现数据层数据加解密 ###

*  一、创建实体类
 *  二、加密和解密工具类
 *  三、DAO层和Service层代码
 *  四、配置文件
 *  五、执行结果
 *   *  1.利用postman测试如下
 *  六、代码地址及项目结构

# 一、创建实体类 #

student

package org.spring.springboot.entity;
    
    public class Student { 
    	private Integer id;
    	private String name;
    	private String course;
    	private String score;
    
    	@Override
    	public String toString() { 
    		return "Student [id=" + id + ", name=" + name + ", course=" + course + ", score=" + score + "]";
    	}
    
    	public Integer getId() { 
    		return id;
    	}
    
    	public void setId(Integer id) { 
    		this.id = id;
    	}
    
    	public String getName() { 
    		return name;
    	}
    
    	public void setName(String name) { 
    		this.name = name;
    	}
    
    	public String getCourse() { 
    		return course;
    	}
    
    	public void setCourse(String course) { 
    		this.course = course;
    	}
    
    	public String getScore() { 
    		return score;
    	}
    
    	public void setScore(String score) { 
    		this.score = score;
    	}
    }

# 二、加密和解密工具类 #

AES、AESTypeHandler

package org.spring.springboot.controller;
    
    import java.util.Base64;
    
    public class AES { 
    	public static 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 static 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);
    		}
    	}
    }

package org.spring.springboot.controller;
    
    import java.sql.CallableStatement;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import org.apache.ibatis.type.BaseTypeHandler;
    import org.apache.ibatis.type.JdbcType;
    
    public class AESTypeHandler extends BaseTypeHandler<Object> { 
    
    	@Override
    	public void setNonNullParameter(PreparedStatement ps, int i, Object parameter, JdbcType jdbcType)
    			throws SQLException { 
    		ps.setString(i, AES.encrypt((String)parameter));
    	}
    
    	@Override
    	public Object getNullableResult(ResultSet rs, String columnName) throws SQLException { 
    		String columnValue = rs.getString(columnName);
    		return AES.decrypt(columnValue);
    	}
    
    	@Override
    	public Object getNullableResult(ResultSet rs, int columnIndex) throws SQLException { 
    		String columnValue = rs.getString(columnIndex);
    		return AES.decrypt(columnValue);
    	}
    	
    	@Override
    	public String getNullableResult(CallableStatement cs, int columnIndex)
    			throws SQLException { 
    		String columnValue = cs.getString(columnIndex);
    		return AES.decrypt(columnValue);
    	}
    }

# 三、DAO层和Service层代码 #

要**指明一个类是 Mapper 有如下两种方式**：

1:是在具体的 Mapper 上添加 @Mapper 注解，表明该接口是一个 MyBatis 中的 Mapper。这种方式就是需要在每一个 Mapper 上都添加注解。  
2:在主启动类上添加 @MapperScan(“com.example.demo.mapper”) 注解，表示扫描 com.example.demo.mapper 包下的所有接口作为 Mapper。这样就不需要在每个接口上配置 @Mapper 注解了。

StudentMapper

package org.spring.springboot.mapper;
    
    import java.util.List;
    import org.apache.ibatis.annotations.Mapper;
    import org.spring.springboot.entity.Student;
    import org.springframework.stereotype.Repository;
    
    @Mapper
    @Repository
    public interface StudentMapper { 
    	//查询
        Student selectStudentById(int id);
        //新增
        boolean insertStudent(List<Student> student);
    }

StudentService

package org.spring.springboot.service;
    
    import java.util.ArrayList;
    import java.util.List;
    import org.spring.springboot.entity.Student;
    import org.spring.springboot.entity.User;
    import org.spring.springboot.mapper.StudentMapper;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    
    @Service
    public class StudentService { 
    	
    	@Autowired
    	private StudentMapper studentMapper;
    	
    	//查询
    	public Student selectStudentById(int id) { 
    		return studentMapper.selectStudentById(id);
    	}
    	
    	//批量新增
        public boolean insertStudent() { 
        	List<Student> list = new ArrayList<>();
        	for (int i = 0; i <2; i++) { 
        		Student student = new Student();
        		student.setCourse(String.valueOf(i*10));
        		student.setName("刘"+i);
        		student.setScore(String.valueOf(i*10));
        		list.add(student);
            }
        	studentMapper.insertStudent(list);
            return true;
        }   
    }

# 四、配置文件 #

StudentMapper.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.spring.springboot.mapper.StudentMapper">
       <resultMap id="StudentMapperResultMap" type="org.spring.springboot.entity.Student">
          <result column="id" jdbcType="INTEGER" property="id"></result>
          <result column="name" jdbcType="VARCHAR" property="name"></result>
          <result column="course" jdbcType="VARCHAR" property="course"></result>
          
          <result column="score" jdbcType="VARCHAR" property="score" typeHandler="org.spring.springboot.controller.AESTypeHandler"></result>
       </resultMap>
       
       <select id="selectStudentById" resultMap="StudentMapperResultMap">
          select * from student where id = #{id}
       </select>
       
       <insert id="insertStudent" parameterType="org.spring.springboot.entity.Student"> 
          insert into student (id,name,course,score)  values
          <foreach collection="list" item="student" separator=",">
             
             (#{student.id},#{student.name},#{student.course},#{student.score,typeHandler=org.spring.springboot.controller.AESTypeHandler}) 
          </foreach>
       </insert>
       
    </mapper>

application.yml

server:
      port: 8086
     
    spring:
      datasource:
        username: root
        password: root
        url: jdbc:mysql://10.*.*.*:3306/springboot?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC
        driver-class-name: com.mysql.cj.jdbc.Driver
     
    mybatis:
      mapper-locations: classpath:mapping/*Mapper.xml
      type-aliases-package: org.spring.springboot.entity
    
    #showSql
    logging:
      level:
        com:
          example:
            mapper : debug

# 五、执行结果 #

## 1.利用postman测试如下 ##

1.1新增http://localhost:8086/StudentController/insertStudent  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppa2UxMTIzMQ_size_16_color_FFFFFF_t_70]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppa2UxMTIzMQ_size_16_color_FFFFFF_t_70 1]  
1.2查询http://localhost:8086/StudentController/selectById/5  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppa2UxMTIzMQ_size_16_color_FFFFFF_t_70 2]

# 六、代码地址及项目结构 #

https://gitee.com/jike11231/springboot-typehandler.git  
整体项目结构  
![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5oeS6Jmr6Jmrfg_size_11_color_FFFFFF_t_70_g_se_x_16]  
**参考文章**  
https://blog.csdn.net/qq\_31289187/article/details/104761910  
https://www.cnblogs.com/wangjuns8/p/8688815.html  
https://blog.csdn.net/qq\_37433657/article/details/105725020  
https://blog.csdn.net/weixin\_43430525/article/details/85783053

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppa2UxMTIzMQ_size_16_color_FFFFFF_t_70]: /images/20221005/9e691575af5a414ea914f18e9c18a9ed.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppa2UxMTIzMQ_size_16_color_FFFFFF_t_70 1]: /images/20221005/f292d6ed5833478c8a1a9c53922f8fb1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppa2UxMTIzMQ_size_16_color_FFFFFF_t_70 2]: /images/20221005/571994aa602b465dbbc839ac2189bd99.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5oeS6Jmr6Jmrfg_size_11_color_FFFFFF_t_70_g_se_x_16]: /images/20221005/478fd536f07247948b712c4e86685c87.png