mysql 动态连接数据源创建表

1、

@Controller
@RequestMapping("api/table")
@Api(tags = "创建表")
public class CreateTableControlelr {

    //private static final Logger LOGGER	= LoggerFactory.getLogger(CreateTableControlelr.class);

    @Autowired
    private SysMysqlCreateTableManager sysMysqlCreateTableManager;

    @Autowired
    private JobDatasourceService datasourceService;


    /**
     * 创建表
     */
    @PostMapping("/create")
    @ResponseBody
    public ReturnT<String> createTable(@RequestBody TableEntity table){
        JobDatasource datasource = datasourceService.getById(table.getId());
        if(null!=table && StringUtils.isNotBlank(table.getTableName()) && table.getColumns().size()>0){
            try {
                //检查表是否存在，若不存在，则创建，若存在，则给出提示
                if(!sysMysqlCreateTableManager.TableIsExist(table.getTableName(),datasource)){
                    List<CreateTableParam> list  = table.getColumns();
                    Map<String,List<CreateTableParam>> map = new HashMap<>();
                    List<CreateTableParam> columnList = new ArrayList<>();
                    columnList.addAll(list);
                    map.put(table.getTableName(), columnList);
                    ReturnT<String> tableByMap = sysMysqlCreateTableManager.createTableByMap(map, datasource);
                    return tableByMap;
                }else{
                    return new ReturnT<>(ReturnT.FAIL_CODE, (I18nUtil.getString("table_already_exists")));
                }
            } catch (Exception e) {
                return new ReturnT<>(ReturnT.FAIL_CODE, (I18nUtil.getString("crate_table_exception"))+e.getMessage());
            }
        }else{
            return new ReturnT<>(ReturnT.FAIL_CODE, (I18nUtil.getString("table_or_column_is_null")));
        }
    }

import com.wugui.datatx.core.biz.model.ReturnT;
import com.wugui.datax.admin.entity.JobDatasource;
import com.wugui.datax.admin.entity.tables.CreateTableParam;
import com.wugui.datax.admin.mapper.BookTypeMapper;
import com.wugui.datax.admin.util.AESUtil;
import com.wugui.datax.admin.util.JdbcUtils;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.*;
import java.util.Map.Entry;

@Component
public class SysMysqlCreateTableManager {
    private static final Logger LOGGER = LoggerFactory.getLogger(SysMysqlCreateTableManager.class);

    @Autowired
    private BookTypeMapper bookTypeDAO;

    /**
     * 根据map结构创建表
     *
     * @param newTableMap
     */
    public ReturnT<String> createTableByMap(Map<String, List<CreateTableParam>> newTableMap,JobDatasource datasource) {
        // 做创建表操作
        int i = 0;
        if (newTableMap.size() > 0) {
            for (Entry<String, List<CreateTableParam>> entry : newTableMap.entrySet()) {
                i++;
                Map<String, List<CreateTableParam>> map = new HashMap<String, List<CreateTableParam>>();
                map.put(entry.getKey(), entry.getValue());
                LOGGER.info("开始创建表：" + entry.getKey() + " 字段：" + entry.getValue());
                String sql = createSql(map);
                return createConnect(datasource, sql,"create");
            }
        }
        return   new ReturnT<>(ReturnT.FAIL_CODE, "创建表字段为空");
    }

    /**
     * 查看表是否存在
     *
     * @param tableName
     */
    public boolean TableIsExist(String tableName,JobDatasource datasource) {
        boolean flag = false;
        if (StringUtils.isNotBlank(tableName)) {
            String sql = "select count(1) from information_schema.tables where table_name = '"+tableName+"'";
            ReturnT<String> select = createConnect(datasource, sql, "select");
            if (select.getStatus()==500) {
                flag = true;
            }
        }
        return flag;
    }

    public ReturnT<String> createConnect(JobDatasource jobDatasource, String sql,String type) {
        String jdbcUrl = jobDatasource.getJdbcUrl();
        Connection con = null;
        PreparedStatement stmt = null;
        ResultSet resultSet = null;
        try {
            Class.forName(jobDatasource.getJdbcDriverClass());
            con = DriverManager.getConnection(jdbcUrl,
                    AESUtil.decrypt(jobDatasource.getJdbcUsername()),
                    AESUtil.decrypt(jobDatasource.getJdbcPassword()));
            stmt = con.prepareStatement(sql);

            //返回false代表更新或者插入操作，返回true代表查询
            if (type.equals("create")){
                stmt.execute();
            }else {
                 resultSet = stmt.executeQuery();
                int anInt =0;
                while (resultSet.next()){
                     anInt = resultSet.getInt(1);
                }
                if (anInt==1){
                    return  new ReturnT<>(ReturnT.FAIL_CODE, "表已经存在，创建失败");
                }
            }
            return  new ReturnT<>(ReturnT.SUCCESS_CODE, "创建成功");
        } catch (Exception e) {
            LOGGER.error("create table found error :[{}]",e.getMessage());
            return  new ReturnT<>(ReturnT.FAIL_CODE, "create table fail"+e.getMessage());
        } finally {
            if (resultSet!=null){
                JdbcUtils.close(resultSet);
            }
            JdbcUtils.close(stmt);
            JdbcUtils.close(con);
        }
    }

    /**
     * @param map 拼接创建表的sql
     * @return
     */
    public String createSql(Map<String, List<CreateTableParam>> map) {
        Set<String> set = map.keySet();
        List<String> keyList = new ArrayList<>();
        keyList.addAll(set);
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append("create table `").append(keyList.get(0)).append("` (");
        List<CreateTableParam> list = map.get(keyList.get(0));
        for (CreateTableParam param : list) {
            if (param.getFileTypeLength() == 0) {
                stringBuilder.append("`").append(param.getFieldName()).append("`").append(" ");
                stringBuilder.append(param.getFieldType()).append(" ");
            }
            if (param.getFieldDecimalLength() == 1) {
                stringBuilder.append("`").append(param.getFieldName()).append("`").append(" ");
                stringBuilder.append(param.getFieldType());
                stringBuilder.append("(").append(param.getFieldLength()).append(")").append(" ");
            }
            if (param.getFieldDecimalLength() == 2) {
                stringBuilder.append("`").append(param.getFieldName()).append("`").append(" ");
                stringBuilder.append(param.getFieldType());
                stringBuilder.append("(").append(param.getFieldLength()).append(",");
                stringBuilder.append(param.getFieldDecimalLength()).append(")").append(" ");
            }
            if (!param.isFieldIsNull()) {
                stringBuilder.append("NOT NULL").append(" ");
            }
            if (param.isFieldIsAutoIncrement()) {
                stringBuilder.append("AUTO_INCREMENT").append(" ");
            }
            if (!param.isFieldIsAutoIncrement() && param.getFieldDefaultValue() != null) {
                stringBuilder.append("DEFAULT ");
                stringBuilder.append(param.getFieldDefaultValue()).append(" ");
            }
            if (param.isFieldIsKey()) {
                stringBuilder.append(",PRIMARY KEY (`");
                stringBuilder.append(param.getFieldName());
                stringBuilder.append("`)").append(" ");
            }
            if (param.isFieldIsUnique()) {
                stringBuilder.append(",UNIQUE KEY (`");
                stringBuilder.append(param.getFieldName());
                stringBuilder.append("`)").append(" ");
            }
        }
        stringBuilder.append(");");
        return stringBuilder.toString();
    }

@Data
public class CreateTableParam {

		/**
		 * 字段名
		 */
		private String	fieldName;
		


		/**
		 * 字段类型
		 */
		private String	fieldType;

		/**
		 * 类型长度
		 */
		private int		fieldLength;

		/**
		 * 类型小数长度
		 */
		private int		fieldDecimalLength;

		/**
		 * 字段是否非空
		 */
		private boolean	fieldIsNull;

		/**
		 * 字段是否是主键
		 */
		private boolean	fieldIsKey;

		/**
		 * 主键是否自增
		 */
		private boolean	fieldIsAutoIncrement;

		/**
		 * 字段默认值
		 */
		private String	fieldDefaultValue;

		/**
		 * 该类型需要几个长度（例如，需要小数位数的，那么总长度和小数长度就是2个长度）一版只有0、1、2三个可选值，自动从配置的类型中获取的
		 */
		private int		fileTypeLength;

		/**
		 * 值是否唯一
		 */
		private boolean	fieldIsUnique;

		
		


		@Override
		public String toString() {
			return "CreateTableParam [fieldName=" + fieldName + ", fieldType=" + fieldType + ", fieldLength="
					+ fieldLength + ", fieldDecimalLength=" + fieldDecimalLength + ", fieldIsNull=" + fieldIsNull
					+ ", fieldIsKey=" + fieldIsKey + ", fieldIsAutoIncrement=" + fieldIsAutoIncrement
					+ ", fieldDefaultValue=" + fieldDefaultValue + ", fileTypeLength=" + fileTypeLength
					+ ", fieldIsUnique=" + fieldIsUnique + "]";
		}

@Data
public class TableEntity implements Serializable {
	
	public TableEntity() {
		// TODO Auto-generated constructor stub
	}

	/**
	 * 
	 */
	private static final long serialVersionUID = -3271257418286413963L;

	private String id;
	
	/*表名*/
	private String tableName;
	/*表字段集合*/
	private List<CreateTableParam> columns;



	@Override
	public String toString() {
		return "TableEntity [tableName=" + tableName + ", columns=" + columns + "]";
	}