快速搭建数据库持久层架构-自动建表,自动装配

最新推荐文章于 2022-09-20 14:19:38 发布

置顶逆流的河水

最新推荐文章于 2022-09-20 14:19:38 发布

阅读量1.8k

点赞数 11

分类专栏：数据库框架文章标签：持久层 mybaits springboot jpa 数据库

本文链接：https://blog.csdn.net/sg7067576/article/details/90752709

版权

数据库框架专栏收录该内容

1 篇文章 0 订阅

订阅专栏

本文使用springboot+mybatis，利用注解和反射技术，帮助正在搭建数据库相关架构的人员快速明白市面上主流类似jpa，hibernate等框架实现原理，使用mybatis来完成框架的自动建表，自动注入，自动映射功能。

1.定义好table注解，将使用数据库驱动编码自动生成表格

@Target(ElementType.TYPE)
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface Table {
    String value();
}

2.实体类使用注解，用于数据库table生成,例如:

package com.basic.framework.pojo;

import com.fasterxml.jackson.annotation.JsonIgnore;
import com.smart.mybatis.annotation.Column;
import com.smart.mybatis.annotation.Table;
import lombok.Getter;
import lombok.Setter;

@Setter
@Getter
@Table("tb_admin")
public class Admin extends BasePojo {

    /**
     * 用户名
     */
    @Column(value = "username", columnDefinition = "VARCHAR(20)")
    private String username;

    /**
     * 密码
     */
    @Column(value = "password", columnDefinition = "VARCHAR(60)")
    @JsonIgnore
    private String password;

    /**
     * 类型
     */
    @Column(value = "type", columnDefinition = "VARCHAR(20)")
    private String type;


    public enum AdminType {

        superAdmin("超级管理员"), admin("系统管理员"),examAdmin("考试管理员");

        private String label;

        AdminType(String label) {
            this.label = label;
        }

        public String getLabel() {
            return label;
        }

        public void setLabel(String label) {
            this.label = label;
        }
    }
}

3.通过反射将实体类中的元素注入到sql中，从而达到自动生成表的目的

public void init(String url, String packageName, String username, String password) {
    Date begin = new Date();
    //获取包下所有class类，获取到后，扫描注解，完成数据库表生成
    List<String> classList = getClazzName(packageName, false);
    for (String className : classList) {
        String valueName;
        StringBuilder stringBuilder = new StringBuilder();
        try {
            Object object = Class.forName(className).newInstance();
            if (object.getClass().getAnnotation(Table.class) == null)
                continue;
            String tableName = object.getClass().getAnnotation(Table.class).value();

            if (isTableExist(url, username, password, "SELECT * FROM information_schema.TABLES WHERE TABLE_SCHEMA=(SELECT DATABASE()) AND `table_name` ='" + tableName + "'")) {
                String excuteAddSql = "";
                String excuteModifySql = "";
                //表已经存在
                List<TableField> map = findTableFields(url, username, password, "SELECT column_name,column_type,column_default FROM information_schema.columns WHERE `table_name` ='" + tableName + "'");
                //遍历属性，如果在结果集中不存在，则需要添加字段
                Field[] superFields = object.getClass().getSuperclass().getDeclaredFields();
                excuteAddSql += addFiledSql(superFields, map);
                excuteModifySql += addModifyFiledSql(superFields, map);
                Field[] fields = object.getClass().getDeclaredFields();
                excuteAddSql += addFiledSql(fields, map);
                excuteModifySql += addModifyFiledSql(fields, map);
                if (excuteAddSql.length() != 0) {
                    excuteAddSql = excuteAddSql.substring(0, excuteAddSql.length() - 1);
                    excuteAddSql = "ALTER TABLE " + tableName + " ADD " + excuteAddSql;
                    executeSql(excuteAddSql, url, username, password);
                }
                if (excuteModifySql.length() != 0) {
                    excuteModifySql = "ALTER TABLE " + tableName + excuteModifySql.substring(0, excuteModifySql.length() - 1);
                    executeSql(excuteModifySql, url, username, password);
                }
                continue;
            }

            //父类，此时扫描出父类的注解
            Field[] superFields = object.getClass().getSuperclass().getDeclaredFields();
            for (Field superField : superFields) {
                if (superField.getAnnotation(GeneratedValue.class) != null)
                    stringBuilder.append(superField.getAnnotation(Id.class).value()).append(" ").append(superField.getAnnotation(Id.class).columnDefinition()).append(" ").append("AUTO_INCREMENT PRIMARY KEY,");

                if (superField.getAnnotation(Column.class) != null)
                    stringBuilder.append(superField.getAnnotation(Column.class).value()).append(" ").append(superField.getAnnotation(Column.class).columnDefinition()).append(isNull(superField.getAnnotation(Column.class).isNull())).append(",");
            }
            Field[] fields = object.getClass().getDeclaredFields();
            for (Field field : fields) {
                if (field.getAnnotation(Column.class) != null)
                    stringBuilder.append(field.getAnnotation(Column.class).value()).append(" ").append(field.getAnnotation(Column.class).columnDefinition()).append(isNull(field.getAnnotation(Column.class).isNull())).append(",");
            }
            valueName = stringBuilder.toString().substring(0, stringBuilder.toString().length() - 1);
            String executeSql = "CREATE TABLE " + tableName + "(" + valueName + ")";
            //表不存在，直接生成
            executeSql(executeSql, url, username, password);
        } catch (ClassNotFoundException | InstantiationException | IllegalAccessException e) {
            e.printStackTrace();
        }
    }
    Date end = new Date();
    System.out.println("====>>init smart table time:" + (end.getTime() - begin.getTime()) + "ms");
}

3.crud框架搭建,本文是在mybatis框架上进行的二次搭建，感兴趣的同学可以省去mybatis，同样使用反射和注解技术，实现底层mysql的crud功能

DAO层:

import org.apache.ibatis.annotations.Mapper;

import java.util.HashMap;
import java.util.List;
import java.util.Map;
@Mapper
public interface BaseMapper {

    int insertBatch(Map<String, Object> params);

    int insert(Map<String, Object> params);

    int update(Map<String, Object> params);

    int delete(Map<String, Object> params);

    HashMap<String,Object> findById(Map<String, Object> params);

    List<Map<String,Object>> list(Map<String, Object> params);

    HashMap<String,Object> find(Map<String, Object> params);

    /**count*/
    Integer count(Map<String, Object> params);

    /**批量更新*/
    int updateBatch(Map<String, Object> params);
}

Service层

import com.github.pagehelper.PageInfo;
import com.smart.mybatis.page.Pageable;
import com.smart.mybatis.pojo.*;

import java.util.List;

public interface BaseService<T> {

    int insert(T entity);

    int insert(List<T> list);

    int update(T entity);

    int updateBatch(List<T> entities);

    int delete(T entity);

    Object findById(Long id, Class<T> cls);

    List<T> list(T entity, Class<T> cls);

    List<T> list(T entity, Class<T> cls, List<Query> queryList);

    List<T> list(T entity, Class<T> cls, List<Query> queryList, List<GroupBy> groupByList);

    List<T> list(T entity, Class<T> cls, List<OrderBy> orderList, List<GroupBy> groupByList, List<Like> likes);

    List<T> list(T entity, Class<T> cls, List<Query> queryList, List<OrderBy> orderList, List<GroupBy> groupByList, List<Like> likes);

    List<T> list(T entity, Class<T> cls, List<Query> queryList, List<OrderBy> orderList, List<GroupBy> groupByList, List<Like> likes, List<Compare> compareList);

    Object find(T entity);

    Object find(T entity, List<Query> queryList);

    PageInfo<T> page(Pageable pageable, T entity, Class<T> cls);

    PageInfo<T> page(Pageable pageable, T entity, Class<T> cls, List<OrderBy> orderList, List<GroupBy> groupByList, List<Like> likes);


    Integer count(T entity, Class<T> cls, CountField countField);


    Integer count(T entity, Class<T> cls, CountField countField, List<Compare> compareList);

    Integer count(T entity, Class<T> cls, CountField countField, List<Compare> compareList, List<Query> queryList);
}

BaseServiceImpl层:

@Override
public int insert(T entity) {
    Map<String, Object> param = transformObj(entity, TableConstants.INSERT, null, null, null, null, null,null);
    if (null == param)
        return 0;
    int num = baseMapper.insert(param);
    if (num > 0) {
        Long keyId = (Long) param.get("id");
        addKeyId(entity, keyId);
    }
    return num;
}

@Override
public int update(T entity) {
    return baseMapper.update(transformObj(entity, TableConstants.UPDATE, null, null, null, null, null,null));
}

后面的就不一一列举，实现原理都是一致的，下面我来说说transformObj这个方法:

//获取表名
if (null == t.getClass().getAnnotation(Table.class))
    return null;
Date a = new Date();
Map<String, Object> re = new LinkedHashMap<>();
re.put(TableConstants.TABLE_NAME, t.getClass().getAnnotation(Table.class).value());
// 拿到该类
Class<?> clz = t.getClass();
// 获取实体类的所有属性，返回Field数组
Field[] fields = clz.getDeclaredFields();
//获取父类id属性
Field[] superFields = clz.getSuperclass().getDeclaredFields();
for (Field field : superFields) {
    if (null != field.getAnnotation(Id.class) && getFieldValue(t, field) != null) {
        re.put(TableConstants.KEY_ID, field.getAnnotation(Id.class).value());
        re.put(TableConstants.KEY_VALUE, getFieldValue(t, field));
        continue;
    }
    if (null != field.getAnnotation(Sql.class) && getFieldValue(t, field) != null)
        re.put("SQL", getFieldValue(t, field));
}
if (TableConstants.INSERT.equals(type)) {
    List<Object> keys = new ArrayList<>();//存放列名
    List<Object> values = new ArrayList<>();//存放列值
    addParm(superFields, keys, values, t, fields);
    re.put(TableConstants.COLUMNS, keys);
    re.put(TableConstants.VALUES, values);
}

/**
 * insert添加参数
 */
private void addParm(Field[] superFields, List<Object> keys, List<Object> values, Object t, Field[] fields) {
    for (Field field : superFields) {
        if (null != field.getAnnotation(Column.class) && getFieldValue(t, field) != null) {
            if (null != keys)
                keys.add(field.getAnnotation(Column.class).value());
            if (null != values)
                values.add(getFieldValue(t, field));
        }
    }
    for (Field field : fields) {
        //判断是否存在标签
        if (field.getAnnotation(Column.class) != null) {
            //列不为空
            if (null != keys)
                keys.add(field.getAnnotation(Column.class).value());
            if (null != values)
                values.add(getFieldValue(t, field));
        }
    }
}

该方法通过反射获取到实体类的真实值，将真实值保存在map中，通过mapper映射到mapper文件中.

<insert id="insert" parameterType="java.util.HashMap" useGeneratedKeys="true" keyProperty="id" keyColumn="id">
    <selectKey resultType="long" order="AFTER" keyProperty="id">
        SELECT LAST_INSERT_ID() as id
    </selectKey>
    INSERT INTO ${TABLE_NAME} (
    <foreach collection="COLUMNS" item="item" index="index" separator=",">
        ${item}
    </foreach>
    ) VALUES (
    <foreach collection="VALUES" item="item" index="index" separator=",">
        #{item}
    </foreach>
    )
</insert>

如果你想查询，你只需要配置好OneToOne标签将会自动生成链表查询，返回数据通过反射重新注入到实体类。

此处不再一一列举，该框架中还包含了更新，关联查询等等一系列功能，动态注入这些值意味着你不再需要编写费时的xml，自动装配将会帮你生成这些语句，极大的提高了编码效率,具体代码已上传至github，在TestController可以进行体验,后续会继续讲述数据库分布式分库分表，并升级该架构适应分库分表。

github地址:https://github.com/sgfh/smart_mybatis