MyBatis之通用化动态SQL

傷城~ 2022-04-11 11:53 541阅读 0赞

我们在上一篇整合SpringBoot的基础之上，再进行本篇的实验。

总体思路是将实体对象转成map后传给mybatis去执行，将mybatis的执行结果由map转成实体对象后输出。

首先CommDao.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="com.yj.dao.CommDao">
    	<insert id="insert" parameterType="java.util.Map">
    		insert into ${db.tableName}
    		<foreach collection="params.keys" item="key" open="(" close=")"
    			separator=",">
    			<if test="params.get(key) !=null and params.get(key) !=''">
    				${key}
    			</if>
    		</foreach>
    		values
    		<foreach collection="params.values" item="val" open="(" close=")"
    			separator=",">
    			<if test="val !=null and val !=''">
    				#{val}
    			</if>
    		</foreach>
    	</insert>
    
    	<delete id="deleteByPrimaryKey" parameterType="java.util.Map">
    		delete from ${db.tableName} where ${db.primaryKeyName}=#{params.${db.primaryKeyName}}
    	</delete>
    
    	<update id="updateByPrimaryKey" parameterType="java.util.Map">
    		update ${db.tableName} set
    		<foreach collection="params.keys" item="key" separator=",">
    			<if test="db.primaryKeyName!=key">
    				${key}= #{params.${key}}
    			</if>
    		</foreach>
    		where ${db.primaryKeyName} =#{params.${db.primaryKeyName}}
    	</update>
    
    	<select id="selectByPrimaryKey" parameterType="java.util.Map"
    		resultType="java.util.Map">
    		select * from ${db.tableName} where ${db.primaryKeyName}=#{params.${db.primaryKeyName}}
    	</select>
    </mapper>

CommDao

package com.yj.dao;
    
    import java.util.Map;
    import org.apache.ibatis.annotations.Param;
    import org.springframework.stereotype.Repository;
    import com.yj.comm.model.DbProperties;
    
    @Repository
    public interface CommDao {
        void insert(@Param("params") Map<String, Object> map, @Param("db") DbProperties dbProperties);
    
        void deleteByPrimaryKey(@Param("params") Map<String, Object> map, @Param("db") DbProperties dbProperties);
    
        void updateByPrimaryKey(@Param("params") Map<String, Object> map, @Param("db") DbProperties dbProperties);
    
        Map<String, Object> selectByPrimaryKey(@Param("params") Map<String, Object> map, @Param("db") DbProperties dbProperties);
    }

DbProperties

package com.yj.comm.model;
    
    import java.io.Serializable;
    
    /**
     * @param tableName 表名
     * @param primaryKeyName 库表的主键名称
     */
    public class DbProperties implements Serializable {
    	private static final long serialVersionUID = 2134017968462962008L;
    	private String tableName;
    	private String primaryKeyName;
    	public String getTableName() {
    		return tableName;
    	}
    	public void setTableName(String tableName) {
    		this.tableName = tableName;
    	}
    	public String getPrimaryKeyName() {
    		return primaryKeyName;
    	}
    	public void setPrimaryKeyName(String primaryKeyName) {
    		this.primaryKeyName = primaryKeyName;
    	}
    }

User

package com.yj.model;
    
    public class User{
        private Integer id;
    
        private String name;
        
        private Integer age;
    
        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 == null ? null : name.trim();
        }
    
    	public Integer getAge() {
    		return age;
    	}
    
    	public void setAge(Integer age) {
    		this.age = age;
    	}
    
    	@Override
    	public String toString() {
    		return "User [id=" + id + ", name=" + name + ", age=" + age + "]";
    	}
    }

CommService

package com.yj.service;
    
    import java.io.IOException;
    import java.util.Map;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    import com.yj.comm.model.DbProperties;
    import com.yj.comm.util.CommUtil;
    import com.yj.dao.CommDao;
    
    @Service
    public class CommService {
    
    	@Autowired
    	private CommDao commDao;
    
    	public void insert(Object object, DbProperties dbProperties) {
    		Map<String, Object> map = CommUtil.objectToMap(object);
    		commDao.insert(map, dbProperties);
    	}
    
    	public void deleteByPrimaryKey(Object object, DbProperties dbProperties) {
    		Map<String, Object> map = CommUtil.objectToMap(object);
    		commDao.deleteByPrimaryKey(map, dbProperties);
    	}
    
    	public void updateByPrimaryKey(Object object, DbProperties dbProperties) {
    		Map<String, Object> map = CommUtil.objectToMap(object);
    		commDao.updateByPrimaryKey(map, dbProperties);
    	}
    
    	public Object selectByPrimaryKey(Object object, DbProperties dbProperties) throws IOException {
    		Map<String, Object> map = CommUtil.objectToMap(object);
    		map=commDao.selectByPrimaryKey(map, dbProperties);
    		return CommUtil.mapToObject(map, object.getClass());
    	}
    }

CommUtil

package com.yj.comm.util;
    
    import java.beans.BeanInfo;
    import java.beans.Introspector;
    import java.beans.PropertyDescriptor;
    import java.lang.reflect.Method;
    import java.sql.Timestamp;
    import java.text.SimpleDateFormat;
    import java.util.HashMap;
    import java.util.Map;
    import java.util.regex.Matcher;
    import java.util.regex.Pattern;
    
    import com.alibaba.fastjson.JSON;
    
    public class CommUtil {
        private static Pattern pattern = Pattern.compile("_(\\w)");
        private static SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    
        public static String camelToUnderline(String str) {
            int len = str.length();
            StringBuilder sb = new StringBuilder(len);
            for (int i = 0; i < len; i++) {
                char c = str.charAt(i);
                if (Character.isUpperCase(c)) {
                    sb.append("_");
                    sb.append(Character.toLowerCase(c));
                } else {
                    sb.append(c);
                }
            }
            return sb.toString();
        }
    
        public static String underlineToCamel(String str) {
            str = str.toLowerCase();
            final StringBuffer sb = new StringBuffer();
            Matcher m = pattern.matcher(str);
            while (m.find()) {
                m.appendReplacement(sb, m.group(1).toUpperCase());
            }
            m.appendTail(sb);
            return sb.toString();
        }
    
    	public static Map<String, Object> objectToMap(Object bean) {
    		Map<String, Object> returnMap = null;
    		try {
    			Class type = bean.getClass();
    			returnMap = new HashMap<String, Object>();
    			BeanInfo beanInfo = Introspector.getBeanInfo(type);
    			PropertyDescriptor[] propertyDescriptors = beanInfo.getPropertyDescriptors();
    			for (int i = 0; i < propertyDescriptors.length; i++) {
    				PropertyDescriptor descriptor = propertyDescriptors[i];
    				String propertyName = descriptor.getName();
    				if (!propertyName.equals("class")) {
    					Method readMethod = descriptor.getReadMethod();
    					Object result = readMethod.invoke(bean, new Object[0]);
    					if (result != null) {
    						returnMap.put(CommUtil.camelToUnderline(propertyName), result);
    					} else {
    						returnMap.put(CommUtil.camelToUnderline(propertyName), "");
    					}
    				}
    			}
    		} catch (Exception e) {
    			e.printStackTrace();
    		}
    		return returnMap;
    	}
    
    	public static <T> T mapToObject(Map<String, Object> map, Class<T> clazz) {
    		T result = null;
    		Map<String, Object> dataMap = new HashMap<>();
    		for (Map.Entry<String, Object> entry : map.entrySet()) {
    			String key = CommUtil.underlineToCamel(entry.getKey());
    			Object value = entry.getValue();
    			if (value instanceof Timestamp) {
    				value = format.format(value);
    			}
    			dataMap.put(key, value);
    		}
    		String json = JSON.toJSONString(dataMap);
    		result = JSON.parseObject(json, clazz);
    
    		return result;
    	}
    }

准备完毕，我们进行单元测试

CommServiceTest

package com.yj.service;
    
    import java.io.IOException;
    import org.junit.Test;
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    import org.springframework.beans.factory.annotation.Autowired;
    import com.yj.ApplicationTest;
    import com.yj.comm.model.DbProperties;
    import com.yj.model.User;
    
    public class CommServiceTest extends ApplicationTest{
    
    	private Logger log = LoggerFactory.getLogger(this.getClass());
    	
    	@Autowired
    	private CommService commService;
    	
    	@Test
    	public void insert() {
    		User user =new User();
    		user.setName("test1");
    		user.setAge(18);
    		DbProperties dbProperties=new DbProperties();
    		dbProperties.setPrimaryKeyName("id");
    		dbProperties.setTableName("user");
    		commService.insert(user, dbProperties);
    	}
    	
    	@Test
    	public void deleteByPrimaryKey() {
    		User user =new User();
    		user.setId(14);
    		DbProperties dbProperties=new DbProperties();
    		dbProperties.setPrimaryKeyName("id");
    		dbProperties.setTableName("user");
    		commService.deleteByPrimaryKey(user, dbProperties);
    	}
    	
    	@Test
    	public void updateByPrimaryKey() {
    		User user =new User();
    		user.setId(13);
    		user.setName("aaa");
    		user.setAge(19);
    		DbProperties dbProperties=new DbProperties();
    		dbProperties.setPrimaryKeyName("id");
    		dbProperties.setTableName("user");
    		commService.updateByPrimaryKey(user, dbProperties);
    	}
    	
    	@Test
    	public void selectByPrimaryKey() throws IOException {
    		User user =new User();
    		user.setId(13);
    		DbProperties dbProperties=new DbProperties();
    		dbProperties.setPrimaryKeyName("id");
    		dbProperties.setTableName("user");
    		User result=(User) commService.selectByPrimaryKey(user, dbProperties);
    		log.info("result:"+result);
    	}
    }

基于动态SQL的最基本增删改查的操作成功