mysql自动建表实体类注解_快速搭建数据库持久层架构-自动建表,自动装配

本文使用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 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 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;

@Mapper

public interface BaseMapper {

int insertBatch(Map params);

int insert(Map params);

int update(Map params);

int delete(Map params);

HashMap findById(Map params);

List> list(Map params);

HashMap find(Map params);

/**count*/

Integer count(Map params);

/**批量更新*/

int updateBatch(Map 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 {

int insert(T entity);

int insert(List list);

int update(T entity);

int updateBatch(List entities);

int delete(T entity);

Object findById(Long id, Class cls);

List list(T entity, Class cls);

List list(T entity, Class cls, List queryList);

List list(T entity, Class cls, List queryList, List groupByList);

List list(T entity, Class cls, List orderList, List groupByList, List likes);

List list(T entity, Class cls, List queryList, List orderList, List groupByList, List likes);

List list(T entity, Class cls, List queryList, List orderList, List groupByList, List likes, List compareList);

Object find(T entity);

Object find(T entity, List queryList);

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

PageInfo page(Pageable pageable, T entity, Class cls, List orderList, List groupByList, List likes);

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

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

Integer count(T entity, Class cls, CountField countField, List compareList, List queryList);

}

BaseServiceImpl层:

@Override

public int insert(T entity) {

Map 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 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 keys = new ArrayList<>();//存放列名

List 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 keys, List 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文件中.

SELECT LAST_INSERT_ID() as id

INSERT INTO ${TABLE_NAME} (

${item}

) VALUES (

#{item}

)

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

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