多数据库怎么使用

分享牛

已于 2022-06-20 14:48:38 修改

阅读量1k

点赞数 1

CC 4.0 BY-SA版权

分类专栏：系统架构基础 java基础 BPM 文章标签： big data oracle 数据库

于 2017-12-26 11:38:34 首次发布

本文为博主原创文章，未经博主允许不得转载。不允许将博客的内容（包含博客文章的文字、代码片段、图片、视频、评论、出现的名词、或者代码片段等）复制到gitee/github等网站形成GPL-3.0协议。如果上述项目中复制任何一段代码，该项目开源协议自动失效。不允许任何组织或者个人对文章进行拆解去申请软件著作权。违反该规定将追究法律责任。

本文链接：https://blog.youkuaiyun.com/qq_30739519/article/details/78900577

BPM 同时被 3 个专栏收录

32 篇文章

订阅专栏

java基础

27 篇文章

订阅专栏

系统架构基础

1 篇文章

订阅专栏

本文介绍了一种针对特定数据库表的操作封装方法，并详细展示了MySQL和Oracle数据库的具体实现。此外，还提供了一个用于生成流水号的解决方案，包括表结构设计、业务逻辑处理及其实现细节。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

在activiti中框架中。默认支持mysql/oracle/sqlserver等数据库，下面参考activiti的做法封装一套jdbc操作。

1、针对一张表的操作者，系统表的操作有以下几种

/**
 * <pre>
 * 针对一张表的操作者，系统表的操作有以下几种
 * 针对表本身的：建表，删表 字段操作 数据的crud
 * </pre>
 *
 *
 */
public abstract class DbTableOperator {
    protected Logger logger = LoggerFactory.getLogger(this.getClass());
    public static final String PK_COLUMN_NAME = "ID_";
    public static final String COLUMN_STATUS = "STATUS_";
    public static final String BUSINESS_KEY = "BUSINESS_KEY_";
    //具体的表
    FormBusinessEntity table;
    /**
     * jdbc
     */
    protected JdbcTemplate jdbcTemplate;
    /**
     * 如果我们在sql中使用了in，那么通过?占位符来传参是不能解决问题的，直接拼接sql又会有sql注入的风险。这种情况下我们可以使用NamedParameterJdbcTemplate 来解决问题。
     * NamedParameterJdbcTemplate支持具名参数
     * PS:具名参数: SQL 按名称(以冒号开头)而不是按位置进行指定. 具名参数更易于维护, 也提升了可读性. 具名参数由框架类在运行时用占位符取代
     */
    NamedParameterJdbcTemplate jdbcN;

    public DbTableOperator(FormBusinessEntity table, JdbcTemplate jdbcTemplate,NamedParameterJdbcTemplate jdbcN) {
        super();
        this.table = table;
        this.jdbcTemplate = jdbcTemplate;
        this.jdbcN = jdbcN;
    }
    /**
     * <pre>
     * 返回的数据库类型
     * 枚举：DbType
     * </pre>
     *
     * @return
     */
    public abstract String type();

    /**
     * <pre>
     * 创建表
     * </pre>
     */
    public  abstract void createTable() ;
    /**
     * <pre>
     * 表是否已存在数据库
     * </pre>
     *
     * @return
     */
    public boolean isTableCreated() {
        return false;
    }
    /**
     * <pre>
     * 删除表
     * </pre>
     */
    public void dropTable() {
        if (!isTableCreated()) {
            return;
        }
        String sql = "drop table " + table.getTableName() + "";
        jdbcTemplate.execute(sql);
    }
    /**
     * <pre>
     * 增加字段
     * </pre>
     *
     * @param column
     *            字段
     */
    public void addColumn(IBusinessColumn column) {

    }

    /**
     * <pre>
     * 更新字段
     * </pre>
     *
     * @param column
     *            字段
     */
    public void updateColumn(IBusinessColumn column) {

    }

    /**
     * <pre>
     * 删除字段
     * </pre>
     */
    public void dropColumn(String columnName) {

    }
    /**
     * 需要删除的表数据
     * @param needDeleteOperateId 删除的id
     * @param businessKey       外检约束
     */
    public  void deleteData( List<String> needDeleteOperateId, String businessKey){
        String tableName = table.getTableName();
        StringBuilder sql = new StringBuilder();
        sql.append("UPDATE " + tableName + " SET ");
        sql.append(COLUMN_STATUS + "=1");
        sql.append(" WHERE " + PK_COLUMN_NAME + " in ");
        sql.append("( ");
        sql.append(":ids");
        sql.append(")");
        MapSqlParameterSource parameters = new MapSqlParameterSource();
        parameters.addValue("ids", needDeleteOperateId);
        jdbcN.update(sql.toString(), parameters);
    }
    /**
     * 插入数据
     * @param formJsonNode
     * @param businessKey
     */
    public void insertData(JsonNode formJsonNode,String businessKey){

    }
    public abstract Map<String, String> getTableNames();

    /**
     * <pre>
     * 这方法完全是Map的getOrDefault一样的
     * </pre>
     *
     * @param map
     * @param key
     * @param defVal
     * @return
     */
    public Object getOrDefault(Map<?, ?> map, Object key, Object defVal) {
        if (!map.containsKey(key)) {
            return defVal;
        }
        Object val = map.get(key);
        if (val == null) {
            return defVal;
        }
        return val;
    }
    /**
     * 同步列的信息
     */
    public abstract void syncColumn() ;
    public abstract List<Map<String, Object>> queryData( String businessKey);
    public abstract void updateData(JsonNode formJsonNode, String businessKey);
}

1、

mysql数据库的实现类

public class MysqlTableOperator extends DbTableOperator {

    /**
     * @param table
     * @param jdbcTemplate
     */
    public MysqlTableOperator(FormBusinessEntity table, JdbcTemplate jdbcTemplate, NamedParameterJdbcTemplate jdbcN) {
        super(table, jdbcTemplate, jdbcN);
    }

    @Override
    public String type() {
        return DbType.MYSQL.getKey();
    }

    @Override
    public void createTable() {
        List<? extends IBusinessColumn> columns = table.getColumns();
        StringBuilder sql = new StringBuilder();
        // 未生成表，不处理
        if (isTableCreated()) {
            return;
        }
        sql.append("CREATE TABLE " + table.getTableName() + " (" + "\n");

        sql.append(generateMysqlPrimaryInnerKey());//内置主键
        sql.append(generateMysqlInnerKey());//内置
        for (int i = 0; i < columns.size(); i++) {
            IBusinessColumn column = columns.get(i);
            sql.append(columnToSql(column));
            if (i < columns.size() - 1) {
                sql.append(",\n");
            }
        }
        sql.append(",\n");
        sql.append(" PRIMARY KEY (ID_)" + "\n)");

        //默认设置字符集
        sql.append(" ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin ");
        if (StringUtils.isNotEmpty(table.getDescription())) {
            sql.append(" COMMENT='" + table.getDescription() + "'");
        }
        // 建表结束
        sql.append(";");
        jdbcTemplate.execute(sql.toString());
    }

    @Override
    public boolean isTableCreated() {
        String sql = "select count(1) from information_schema.TABLES t where table_name =?";
        boolean result=false;
        if (jdbcTemplate.queryForObject(sql, Integer.class, table.getTableName()) > 0){
            result=  true;
        }
        return  result;

    }

    private String alterTable(String tableName) {
        return "ALTER TABLE " + tableName + "";
    }

    @Override
    public void addColumn(IBusinessColumn column) {
        StringBuilder sql = new StringBuilder();
        sql.append(alterTable(table.getTableName()));
        sql.append(" ADD COLUMN " + columnToSql(column) + ";");
        jdbcTemplate.execute(sql.toString());
    }

    @Override
    public void dropColumn(String columnName) {
        StringBuilder sql = new StringBuilder();
        sql.append(alterTable(table.getTableName()));
        sql.append(" DROP COLUMN " + columnName + ";");
        jdbcTemplate.execute(sql.toString());
    }

    @Override
    public Map<String, String> getTableNames() {
        String sql = "select table_name,table_comment from information_schema.tables t where t.table_type='BASE TABLE' AND t.table_schema=DATABASE()";
        List<Map<String, Object>> list;
        if (StringUtils.isNotEmpty(table.getTableName())) {
            sql += " AND TABLE_NAME LIKE ?";
            list = jdbcTemplate.queryForList(sql, "%" + table.getTableName() + "%");
        } else {
            list = jdbcTemplate.queryForList(sql);
        }

        Map<String, String> map = new LinkedHashMap<>();
        for (Map<String, Object> m : list) {
            map.put(m.get("table_name").toString(), m.get("table_comment").toString());
        }

        return map;
    }

    @Override
    public void updateColumn(IBusinessColumn column) {
        StringBuilder sql = new StringBuilder();
        sql.append(alterTable(table.getTableName()));
        sql.append(" MODIFY COLUMN " + columnToSql(column) + ";");
        jdbcTemplate.execute(sql.toString());
    }

    @Override
    public void syncColumn() {
        List<? extends IBusinessColumn> columns = table.getColumns();
        if (columns == null) {
            return;//列为空 直接返回
        }

        // 未生成表，不处理
        if (!isTableCreated()) {
            return;
        }
        Set<String> dbColumnNames = new HashSet<>();// 数据库中存在的字段名
        dbColumnNames.add(PK_COLUMN_NAME);
        dbColumnNames.add(COLUMN_STATUS);
        dbColumnNames.add(BUSINESS_KEY);
        Table<Column> dbTable = getDbTable(table.getTableName());
        for (Column c : dbTable.getColumns()) {
            dbColumnNames.add(c.getName());
        }

        for (IBusinessColumn column : columns) {
            boolean exits = false;
            for (String columnName : dbColumnNames) {
                if (columnName.equalsIgnoreCase(column.getColumnKey())) {
                    exits = true;
                    break;
                }
            }
            if (!exits) {// 结构有，数据库表内没有，增加
                addColumn(column);
            } else if (!dbTable.getColumn(column.getColumnKey()).equals(column)) {
                updateColumn(column);// 更新一遍结构
            }
        }
    }

    @Override
    public List<Map<String, Object>> queryData(String businessKey) {
        String tableName = table.getTableName();
        List<? extends IBusinessColumn> columnName = table.getColumns();
        StringBuilder sql = new StringBuilder();
        if (CollectionUtil.isEmpty(columnName)) {
            sql.append("SELECT * FROM " + tableName);
        } else {
            sql.append("SELECT");
            sql.append(" ");
            sql.append(PK_COLUMN_NAME);
            sql.append(" ");
            for (IBusinessColumn cn : columnName) {
                if (!sql.toString().endsWith("SELECT")) {
                    sql.append(",");
                }
                sql.append(" " + cn.getColumnKey());
            }
            sql.append(" FROM " + tableName);
        }
        sql.append(" WHERE ");
        sql.append(" BUSINESS_KEY_=" + businessKey);
        sql.append(" and " + COLUMN_STATUS + "=0");

        return jdbcTemplate.queryForList(sql.toString());
    }

    public Table<Column> getDbTable(String tableName) {
        return this.getTable(tableName);
    }

    public Table<Column> getTable(String tableName) {
        Table<Column> table = new Table<>();
        Map<String, String> tableNames = getTableNames();
        if (tableNames.isEmpty()) {
            throw new BusinessException(String.format("根据表名[%s]获取不到表", tableName));
        }
        table.setName(tableName);
        table.setComment(tableNames.get(tableName));
        table.setColumns(getColumns(tableName));

        return table;
    }

    /**
     * <pre>
     * 根据name获取其字段信息
     * </pre>
     *
     * @param name （表名/视图名）
     * @return
     */
    private List<Column> getColumns(String name) {
        String sql = "SELECT * FROM  INFORMATION_SCHEMA.COLUMNS  WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=?";
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql, name);
        List<Column> columns = new ArrayList<>();
        for (Map<String, Object> map : list) {
            Column column = new Column();
            column.setComment(getOrDefault(map, "COLUMN_COMMENT", "").toString());
            column.setDefaultValue(map.get("COLUMN_DEFAULT") == null ? null : map.get("COLUMN_DEFAULT").toString());
            column.setName(getOrDefault(map, "COLUMN_NAME", "").toString());
            column.setPrimary("PRI".equals(getOrDefault(map, "COLUMN_KEY", "")));
            column.setRequired("NO".equals(getOrDefault(map, "IS_NULLABLE", "")));
            column.setType(
                    PanGuColumnType.getByDbDataType(map.get("DATA_TYPE").toString(), "字段[" + column.getComment() + "(" + column.getName() + ")]").getKey());
            if (PanGuColumnType.VARCHAR.equalsWithKey(column.getType())) {
                column.setLength(Integer.parseInt(getOrDefault(map, "CHARACTER_MAXIMUM_LENGTH", "0").toString()));
            }
            if (PanGuColumnType.NUMBER.equalsWithKey(column.getType())) {
                column.setLength(Integer.parseInt(getOrDefault(map, "NUMERIC_PRECISION", "0").toString()));
                column.setDecimal(Integer.parseInt(getOrDefault(map, "NUMERIC_SCALE", "0").toString()));
            }
            columns.add(column);
        }
        return columns;
    }

    /**
     * <pre>
     * 把column解析成Sql
     * </pre>
     *
     * @param column
     * @return
     */
    private String columnToSql(IBusinessColumn column) {
        StringBuilder sb = new StringBuilder();
        sb.append("" + column.getColumnKey() + "");
        if (PanGuColumnType.CLOB.equalsWithKey(column.getDataType())) {
            sb.append(" text");
        } else if (PanGuColumnType.DATE.equalsWithKey(column.getDataType())) {
            sb.append(" datetime");
        } else if (PanGuColumnType.NUMBER.equalsWithKey(column.getDataType())) {
            sb.append(" decimal(" + column.getAttrLength() + "," + column.getDecimalLength() + ")");
        } else if (PanGuColumnType.VARCHAR.equalsWithKey(column.getDataType())) {
            sb.append(" varchar(" + column.getAttrLength() + ")");
        }

        if (column.isRequired() || column.isPrimary()) {
            sb.append(" NOT NULL");
        } else {
            sb.append(" NULL");
        }
        sb.append(" COMMENT '" + column.getDescription() + "'");
        return sb.toString();
    }

    private String generateMysqlPrimaryInnerKey() {
        StringBuilder sb = new StringBuilder();
        sb.append(" ID_ varchar(64) not null,");
        sb.append(COLUMN_STATUS + "  boolean default 0,");
        return sb.toString();
    }

    private String generateMysqlInnerKey() {
        StringBuilder sb = new StringBuilder();
        sb.append(" BUSINESS_KEY_ varchar(255)  null,");
        return sb.toString();
    }

    @Override
    public void insertData(JsonNode formJsonNode, String businessKey) {
        StringBuilder sql = new StringBuilder();
        StringBuilder columnNameSql = new StringBuilder();// 字段名字sql
        StringBuilder paramNameSql = new StringBuilder();// 参数sql
        List<Object> param = new ArrayList<>();// 参数
        sql.append(" ");
        columnNameSql.append(PK_COLUMN_NAME + ",");
        columnNameSql.append(BUSINESS_KEY + ",");
        param.add(UUID.randomUUID().toString());
        param.add(businessKey + "");
        paramNameSql.append("?,?,");
        sql.append("INSERT INTO " + table.getTableName());
        List<? extends IBusinessColumn> columns = table.getColumns();
        Object val = null;
        for (int i = 0; i < columns.size(); i++) {
            IBusinessColumn column = columns.get(i);
            String dataType = column.getDataType();//类型
            String columnKey = column.getColumnKey();
            columnNameSql.append(columnKey);
            paramNameSql.append("? ");
            JsonNode jsonNode = formJsonNode.get(columnKey);//获取值
            val = getColumnVal(column, dataType, jsonNode);
            param.add(val);
            if (i < columns.size() - 1) {
                columnNameSql.append(", ");
                paramNameSql.append(", ");

            }

        }
        sql.append("(" + columnNameSql + ") VALUES(" + paramNameSql + ")");
        jdbcTemplate.update(sql.toString(), param.toArray());

    }

    public Object getColumnVal(IBusinessColumn column, String dataType, JsonNode jsonNode) {
        Object val = null;
        if (PanGuColumnType.CLOB.equalsWithKey(dataType)) {
            String clob = jsonNode.asText();
            if (StringUtils.isNotEmpty(clob)) {
                val = clob;
            }
        } else if (PanGuColumnType.DATE.equalsWithKey(dataType)) {
            String date = jsonNode.asText();
            if (StringUtils.isNotEmpty(date)) {
                val = DateUtils.timeStamp(date);
            }

        } else if (PanGuColumnType.NUMBER.equalsWithKey(dataType)) {
            int intVal = jsonNode.asInt();
            if (intVal > 0) {
                val = intVal;
            }

        } else if (PanGuColumnType.VARCHAR.equalsWithKey(column.getDataType())) {
            String varchar = jsonNode.asText();
            if (StringUtils.isNotEmpty(varchar)) {
                val = varchar;
            }
        }
        return val;
    }

    @Override
    public void updateData(JsonNode formJsonNode, String businessKey) {

        String tableName = table.getTableName();
        List<? extends IBusinessColumn> columns = table.getColumns();
        //主表以及存在的数据肯定有id 区域或者表单新增的数据可能没有id
        JsonNode idJsonNode = formJsonNode.get(PK_COLUMN_NAME);
        String idVal = null;
        if (idJsonNode != null) {
            idVal = idJsonNode.asText();
        }

        StringBuilder sql = new StringBuilder();
        sql.append("UPDATE " + tableName + " SET ");
        List<Object> param = new ArrayList<>();// 参数
        Object val = null;
        boolean isAllFieldUpData = true;
        for (int i = 0; i < columns.size(); i++) {
            IBusinessColumn column = columns.get(i);
            String dataType = column.getDataType();//类型
            String columnKey = column.getColumnKey();
            JsonNode jsonNode = formJsonNode.get(columnKey);//获取值
            if (jsonNode != null) {//表单没有的数据不处理
                val = getColumnVal(column, dataType, jsonNode);
                sql.append(" " + columnKey + " =?");
                param.add(val);
                if (i < columns.size() - 1) {
                    sql.append(",");
                }
            } else {
                isAllFieldUpData = false;
            }
        }
        if (!isAllFieldUpData) {
            //列不全，截取最后一个逗号,
            sql.deleteCharAt(sql.length() - 1);

        }
        sql.append(" WHERE " + PK_COLUMN_NAME + " = ?");
        param.add(idVal);
        jdbcTemplate.update(sql.toString(), param.toArray());
    }


}

2、oracle实现类

public class OracleTableOperator extends DbTableOperator {

    /**
     * @param table
     * @param jdbcTemplate
     */
    public OracleTableOperator(FormBusinessEntity table, JdbcTemplate jdbcTemplate, NamedParameterJdbcTemplate jdbcN) {
        super(table, jdbcTemplate, jdbcN);
    }

    @Override
    public String type() {
        return DbType.ORACLE.getKey();
    }

    @Override
    public void createTable() {

        if (isTableCreated()) {
            return;
        }

        // 建表语句
        StringBuilder sql = new StringBuilder();
        sql.append("CREATE TABLE \"" + table.getTableName().toUpperCase() + "\" (" + "\n");
        sql.append(generateMysqlPrimaryInnerKey());//内置主键
        sql.append(generateMysqlInnerKey());//内置
        for (IBusinessColumn column : table.getColumns()) {
            sql.append(columnToSql(column) + ",\n");
        }
        if (table.getPkColumn() == null) {
            sql.append(" PRIMARY KEY (ID_)" + "\n)");
        } else {
            sql.append("PRIMARY KEY (\"" + table.getPkColumn().getName().toUpperCase() + "\")" + "\n)");

        }
        // 建表结束
        jdbcTemplate.execute(sql.toString());

        // 字段注解
        for (int i = 0; i < table.getColumns().size(); i++) {
            IBusinessColumn column = table.getColumns().get(i);
            if (StringUtil.isEmpty(column.getDescription())) {
                continue;
            }
            String str = "COMMENT ON COLUMN \"" + table.getTableName().toUpperCase() + "\".\"" + column.getColumnKey().toUpperCase() + "\"  IS '" + column.getDescription() + "'";
            jdbcTemplate.execute(str);
        }

    }

    @Override

    public boolean isTableCreated() {
        String sql = "select count(1) from user_tables t where table_name =?";
        boolean result = false;
        if (jdbcTemplate.queryForObject(sql, Integer.class, table.getTableName().toUpperCase()) > 0) {
            result = true;
        }
        return result;
    }

    @Override
    public void addColumn(IBusinessColumn column) {
        StringBuilder sql = new StringBuilder();
        sql.append("ALTER TABLE \"" + table.getTableName().toUpperCase() + "\"");
        sql.append(" ADD ( " + columnToSql(column) + " )");
        jdbcTemplate.execute(sql.toString());

        // 注解
        if (StringUtil.isEmpty(column.getDescription())) {
            return;
        }
        String str = "COMMENT ON COLUMN \"" + table.getTableName().toUpperCase() + "\".\"" + column.getColumnKey().toUpperCase() + "\"  IS '" + column.getDescription() + "'";
        jdbcTemplate.execute(str);
    }

    @Override
    public void dropColumn(String columnName) {
        StringBuilder sql = new StringBuilder();
        sql.append("ALTER TABLE " + table.getTableName() + "");
        sql.append(" DROP COLUMN " + columnName + ";");
        jdbcTemplate.execute(sql.toString());
    }

    @Override
    public Map<String, String> getTableNames() {
        String sql = "select t.table_name,f.comments from user_tables t inner join user_tab_comments f on t.table_name = f.table_name";
        List<Map<String, Object>> list;
        if (StringUtils.isNotEmpty(table.getTableName())) {
            sql += " AND t.table_name LIKE ?";
            list = jdbcTemplate.queryForList(sql, "%" + table.getTableName().toUpperCase() + "%");
        } else {
            list = jdbcTemplate.queryForList(sql);
        }

        Map<String, String> map = new LinkedHashMap<>();
        for (Map<String, Object> m : list) {
            map.put(m.get("table_name").toString(), getOrDefault(m, "comments", "").toString());
        }

        return map;
    }

    @Override
    public void updateColumn(IBusinessColumn column) {
        StringBuilder sql = new StringBuilder();
        sql.append("ALTER TABLE \"" + table.getTableName().toUpperCase() + "\"");
        sql.append(" MODIFY( " + columnToSql(column) + " )");
        jdbcTemplate.execute(sql.toString());

    }

    @Override
    public void syncColumn() {
        List<? extends IBusinessColumn> columns = table.getColumns();
        if (columns == null) {
            return;//列为空 直接返回
        }

        // 未生成表，不处理
        if (!isTableCreated()) {
            return;
        }
        Set<String> dbColumnNames = new HashSet<>();// 数据库中存在的字段名
        dbColumnNames.add(PK_COLUMN_NAME);
        dbColumnNames.add(COLUMN_STATUS);
        dbColumnNames.add(BUSINESS_KEY);
        Table<Column> dbTable = getDbTable(table.getTableName());
        for (Column c : dbTable.getColumns()) {
            dbColumnNames.add(c.getName());
        }

        for (IBusinessColumn column : columns) {
            boolean exits = false;
            for (String columnName : dbColumnNames) {
                if (columnName.equalsIgnoreCase(column.getColumnKey())) {
                    exits = true;
                    break;
                }
            }
            if (!exits) {// 结构有，数据库表内没有，增加
                addColumn(column);
            } else if (!dbTable.getColumn(column.getColumnKey()).equals(column)) {
                //oracle数据库的表不需要更新结构
                //updateColumn(column);// 更新一遍结构
            }
        }
    }

    @Override
    public List<Map<String, Object>> queryData(String businessKey) {
        String tableName = table.getTableName();
        List<? extends IBusinessColumn> columnName = table.getColumns();
        StringBuilder sql = new StringBuilder();
        List<Object> param = new ArrayList<>();// 参数

        getSql(tableName, columnName, sql);
        param.add(businessKey);
        param.add(0);
        //Oracle 区分大小写，要再次转换一下

        List<Map<String, Object>> mapList = jdbcTemplate.queryForList(sql.toString(), param.toArray());
        List<Map<String, Object>> resultList = new ArrayList<>();
        List<? extends IBusinessColumn> columns = table.getColumns();
        for (Map<String, Object> map : mapList) {
            Map<String, Object> resultMap = new HashMap<>();
            Iterator<Map.Entry<String, Object>> entries = map.entrySet().iterator();
            while (entries.hasNext()) {
                Map.Entry<String, Object> entry = entries.next();
                String key = entry.getKey();
                Object value = entry.getValue();
                for (IBusinessColumn ibc : columns) {
                    if (ibc.getColumnKey().equalsIgnoreCase(key)) {
                        resultMap.put(ibc.getColumnKey(), value);

                        break;
                    }
                }


            }
            resultMap.put(PK_COLUMN_NAME, map.get(PK_COLUMN_NAME));
            resultList.add(resultMap);
        }

        return resultList;
    }

    private void getSql(String tableName, List<? extends IBusinessColumn> columnName, StringBuilder sql) {
        if (CollectionUtil.isEmpty(columnName)) {
            sql.append("SELECT * FROM " + tableName);
        } else {
            sql.append("SELECT");
            sql.append(" ");
            sql.append(PK_COLUMN_NAME);
            sql.append(" ,");
            sql.append(BUSINESS_KEY);
            for (IBusinessColumn cn : columnName) {
                if (!sql.toString().endsWith("SELECT")) {
                    sql.append(",");
                }
                sql.append(" " + cn.getColumnKey());
            }
            sql.append(" FROM " + tableName);
        }
        sql.append(" WHERE ");
        sql.append(" BUSINESS_KEY_=?");
        sql.append(" and " + COLUMN_STATUS + "=?");
    }

    public Table<Column> getDbTable(String tableName) {
        return this.getTable(tableName);
    }

    public Table<Column> getTable(String tableName) {
        Table<Column> table = new Table<>();
        Map<String, String> tableNames = getTableNames();
        if (tableNames.isEmpty()) {
            throw new BusinessException(String.format("根据表名[%s]获取不到表", tableName));
        }
        table.setName(tableName);
        table.setComment(tableNames.get(tableName));
        table.setColumns(getColumns(tableName));

        return table;
    }

    /**
     * <pre>
     * 根据name获取其字段信息
     * </pre>
     *
     * @param name （表名/视图名）
     * @return
     */
    private List<Column> getColumns(String name) {
        // 先找到主键
        String sqlT = "select col.column_name from user_constraints con,user_cons_columns col where con.constraint_name=col.constraint_name and con.constraint_type='P' and col.table_name= ?";
        List<Map<String, Object>> listT = jdbcTemplate.queryForList(sqlT, name.toUpperCase());
        Set<String> pkNames = new HashSet<>();// 主键
        for (Map<String, Object> map : listT) {
            pkNames.add(getOrDefault(map, "COLUMN_NAME", "").toString());
        }

        // 开始解析字段信息
        String sql = "select a.*,b.comments from user_tab_columns a inner join user_col_comments b on a.table_name = b.table_name and a.column_name = b.column_name and a.table_name = ? ";
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql, name.toUpperCase());
        List<Column> columns = new ArrayList<>();
        for (Map<String, Object> map : list) {
            Column column = new Column();
            column.setComment(getOrDefault(map, "COMMENTS", "").toString());
            Object defVal = map.get("DATA_DEFAULT");
            if (defVal != null && !defVal.toString().trim().equals("NULL")) {
                column.setDefaultValue(map.get("DATA_DEFAULT").toString());
            }
            column.setName(getOrDefault(map, "COLUMN_NAME", "").toString());
            column.setPrimary(pkNames.contains(column.getName().toUpperCase()));
            column.setRequired("N".equals(getOrDefault(map, "NULLABLE", "Y")));
            column.setType(PanGuColumnType.getByDbDataType(map.get("DATA_TYPE").toString(), "字段[" + column.getComment() + "(" + column.getName().toUpperCase() + ")]").getKey());

            if (PanGuColumnType.VARCHAR.equalsWithKey(column.getType())) {
                column.setLength(Integer.parseInt(getOrDefault(map, "DATA_LENGTH", "0").toString()));
            }
            if (PanGuColumnType.NUMBER.equalsWithKey(column.getType())) {
                column.setLength(Integer.parseInt(getOrDefault(map, "DATA_PRECISION", "0").toString()));
                column.setDecimal(Integer.parseInt(getOrDefault(map, "DATA_SCALE", "0").toString()));
            }
            columns.add(column);
        }
        return columns;
    }

    /**
     * <pre>
     * 把column解析成Sql
     * </pre>
     *
     * @param column
     * @return
     */
    private String columnToSql(IBusinessColumn column) {
        StringBuilder sb = new StringBuilder();
        sb.append("\"" + column.getColumnKey().toUpperCase() + "\"");
        if (PanGuColumnType.CLOB.equalsWithKey(column.getDataType())) {
            sb.append(" CLOB");
        } else if (PanGuColumnType.DATE.equalsWithKey(column.getDataType())) {
            sb.append(" TIMESTAMP");
        } else if (PanGuColumnType.NUMBER.equalsWithKey(column.getDataType())) {
            sb.append(" NUMBER(" + column.getAttrLength() + "," + column.getDecimalLength() + ")");
        } else if (PanGuColumnType.VARCHAR.equalsWithKey(column.getDataType())) {
            sb.append(" VARCHAR2(" + column.getAttrLength() + ")");
        }

        if (column.isRequired() || column.isPrimary()) {
            sb.append(" NOT NULL");
        } else {
            sb.append(" NULL");
        }
        return sb.toString();
    }

    private String generateMysqlPrimaryInnerKey() {
        StringBuilder sb = new StringBuilder();
        sb.append(" ID_ varchar(64) not null ,");
        sb.append(COLUMN_STATUS + "  smallint default 0 check(STATUS_ in (1,0)) ,");
        return sb.toString();
    }

    private String generateMysqlInnerKey() {
        StringBuilder sb = new StringBuilder();
        sb.append(" BUSINESS_KEY_ varchar(255)  null,");
        return sb.toString();
    }

    @Override
    public void insertData(JsonNode formJsonNode, String businessKey) {
        StringBuilder sql = new StringBuilder();
        StringBuilder columnNameSql = new StringBuilder();// 字段名字sql
        StringBuilder paramNameSql = new StringBuilder();// 参数sql
        List<Object> param = new ArrayList<>();// 参数
        sql.append(" ");
        columnNameSql.append(PK_COLUMN_NAME + ",");
        columnNameSql.append(BUSINESS_KEY + ",");
        param.add(UUID.randomUUID().toString());
        param.add(businessKey + "");
        paramNameSql.append("?,?,");
        sql.append("INSERT INTO " + table.getTableName());
        List<? extends IBusinessColumn> columns = table.getColumns();
        Object val = null;
        for (int i = 0; i < columns.size(); i++) {
            IBusinessColumn column = columns.get(i);
            String dataType = column.getDataType();//类型
            String columnKey = column.getColumnKey();
            columnNameSql.append(columnKey);
            paramNameSql.append("? ");
            JsonNode jsonNode = formJsonNode.get(columnKey);//获取值
            val = getColumnVal(column, dataType, jsonNode);
            param.add(val);
            if (i < columns.size() - 1) {
                columnNameSql.append(", ");
                paramNameSql.append(", ");

            }

        }
        sql.append("(" + columnNameSql + ") VALUES(" + paramNameSql + ")");
        jdbcTemplate.update(sql.toString(), param.toArray());

    }

    public Object getColumnVal(IBusinessColumn column, String dataType, JsonNode jsonNode) {
        Object val = null;
        if (PanGuColumnType.CLOB.equalsWithKey(dataType)) {
            String clob = jsonNode.asText();
            if (StringUtils.isNotEmpty(clob)) {
                val = clob;
            }
        } else if (PanGuColumnType.DATE.equalsWithKey(dataType)) {
            String date = jsonNode.asText();
            if (StringUtils.isNotEmpty(date)) {
                val = DateUtils.timeStamp(date);
            }

        } else if (PanGuColumnType.NUMBER.equalsWithKey(dataType)) {
            int intVal = jsonNode.asInt();
            if (intVal > 0) {
                val = intVal;
            }

        } else if (PanGuColumnType.VARCHAR.equalsWithKey(column.getDataType())) {
            String varchar = jsonNode.asText();
            val = varchar;

        }
        if (column.isRequired() && (val == null || StringUtils.isEmpty(val.toString()))) {
            throw new BusinessException("必填值为空");
        }
        return val;
    }

    @Override
    public void updateData(JsonNode formJsonNode, String businessKey) {

        String tableName = table.getTableName();
        List<? extends IBusinessColumn> columns = table.getColumns();
        //主表以及存在的数据肯定有id 区域或者表单新增的数据可能没有id
        JsonNode idJsonNode = formJsonNode.get(PK_COLUMN_NAME);
        String idVal = null;
        if (idJsonNode != null) {
            idVal = idJsonNode.asText();
        }

        StringBuilder sql = new StringBuilder();
        sql.append("UPDATE " + tableName.toUpperCase() + " SET ");
        List<Object> param = new ArrayList<>();// 参数
        Object val = null;
        for (int i = 0; i < columns.size(); i++) {
            IBusinessColumn column = columns.get(i);
            String dataType = column.getDataType();//类型
            String columnKey = column.getColumnKey();
            JsonNode jsonNode = formJsonNode.get(columnKey);//获取值
            if (jsonNode != null) {//表单没有的数据不处理
                val = getColumnVal(column, dataType, jsonNode);
                sql.append(" " + columnKey.toUpperCase() + " =?");
                param.add(val);
                if (i < columns.size() - 1) {
                    sql.append(",");
                }
            }
        }
        sql.append(" WHERE " + PK_COLUMN_NAME + " = ?");
        param.add(idVal);
        jdbcTemplate.update(sql.toString(), param.toArray());
    }

}

3、数据库可能会生成一系列的表主键操作。这里以activiti的主键生成策略为例手写一个自己的主键生成器。

3.1表建立

DROP TABLE IF EXISTS `sys_serialno`;
CREATE TABLE `sys_serialno` (
  `ID_` varchar(255) COLLATE utf8_bin NOT NULL COMMENT '主键',
  `NAME_` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '名称',
  `ALIAS_` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '别名',
  `regulation_` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '流水号规则',
  `gen_type_` decimal(10,0) DEFAULT NULL COMMENT '生成类型',
  `no_length_` decimal(10,0) DEFAULT NULL COMMENT '流水号长度',
  `cur_date_` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '执行实例id',
  `init_value_` decimal(10,0) DEFAULT NULL COMMENT '初始值',
  `cur_value_` decimal(10,0) DEFAULT NULL COMMENT '是否成功',
  `step_` decimal(10,0) DEFAULT NULL COMMENT '步长',
  `DELETED` tinyint(1) DEFAULT NULL COMMENT '是否直接删除',
  PRIMARY KEY (`ID_`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='流水号';

3.1初始化数据
-- ----------------------------
-- Records of sys_serialno
-- ----------------------------
BEGIN;
INSERT INTO `sys_serialno` VALUES ('1', '每天使用一组流水号', 'dayNo', '{yyyy}{MM}{DD}{NO}', 1, 5, NULL, 1, 0, 2, 0);
INSERT INTO `sys_serialno` VALUES ('ccc', 'c', 'c', 1, 1, NULL, 1, 0, 1, 1);
INSERT INTO `sys_serialno` VALUES ( '1', '1', '1', 1, 111, NULL, 111, 0, 1, 1);
INSERT INTO `sys_serialno` VALUES ( '12121', '12121', '12122', 1, 1, NULL, 1, 0, 1, 1);
INSERT INTO `sys_serialno` VALUES ( '88888', '121212', '12122', 2, 1, NULL, 1, 0, 1, 1);
INSERT INTO `sys_serialno` VALUES ('77', '77', '777', 2, 777, NULL, 777, 0, 777, 1);
COMMIT;

每天使用一组流水号的策略参考activiti框架。

1、

流水号生成管理说明

/**
 * 流水号生成管理
 */
@RestController
@RequestMapping("/sys/serialNo/")
public class SysSerialNoController extends AbstractController {

    @Resource
    SerialNoManager serialNoManager;
    @Value("${pangu.delete}")
    private Boolean panguDelete;

    @PostMapping(value = "/listJson")
    @SysLog("流水号生成列表(分页条件查询)数据")
    @ResponseBody
    public R listJson(@ApiIgnore @RequestParam Map<String, Object> params, SerialNoQuery serialNoQuery) {
        return serialNoManager.listJson(params, serialNoQuery);
    }

    @PostMapping(value = "/save")
    @ResponseBody
    @SysLog("保存流水号")
    public R save(@RequestBody SerialNo serialNo) {
        boolean exist = serialNoManager.isAliasExisted(serialNo.getId(), serialNo.getAlias());
        if (!exist) {
            if (StringUtils.isNotEmpty(serialNo.getId())) {
                serialNo.setCreateTime(DateUtils.getCurrentDate());
                serialNo.setUpdateTime(DateUtils.getCurrentDate());
                serialNoManager.update(serialNo);
            } else {
                serialNo.setUpdateTime(DateUtils.getCurrentDate());
                serialNo.setId(SnowflakeIdWorkerUtils.getNextId());
                serialNoManager.save(serialNo);
            }

        } else {
            return R.error("添加流水号失败，别名【" + serialNo.getAlias() + "】在系统中已存在，不能重复");
        }
        return R.ok();
    }

    @SysLog("删除流水号")
    @PostMapping("/remove")
    @ResponseBody
    public R delete(@RequestBody String[] ids) {
        if (org.springframework.util.StringUtils.isEmpty(ids)) {
            return R.error("没有选中数据");
        }
        if (panguDelete) {
            serialNoManager.batchRemove(Arrays.asList(ids));
            return R.ok();
        } else {
            return R.error("演示模式下无法执行该操作");
        }

    }

    /**
     * V2.6.2 版本新增
     *
     * @param jsonParam
     * @return
     */
    @PostMapping(value = "/v2/remove")
    @SysLog("批量删除")
    @ResponseBody
    public R removeByIds(@RequestBody JSONObject jsonParam) {
        List list = jsonParam.getJSONArray("ids");
        if (list.isEmpty()) {
            return R.error("没有选中数据");
        }
        if (panguDelete) {
            serialNoManager.batchRemove(list);
            return R.ok();
        } else {
            return R.error("演示模式下无法执行该操作");
        }

    }


    @GetMapping(value = "/get/{id}")
    @SysLog("查询流水号")
    @ResponseBody
    public R get(@PathVariable("id") String id) {
        return serialNoManager.getCurById(id);
    }

    @GetMapping(value = "/previewIden/{alias}")
    @SysLog("执行流水号")
    @ResponseBody
    public R previewIden(@PathVariable("alias") String alias) {
        List<SerialNo> previewIden = serialNoManager.getPreviewIden(alias);
        return R.ok().put("data", previewIden);
    }


}

2、流水号接口以及实现类

public interface SerialNoManager {

    /**
     * 判读流水号别名是否已经存在
     *
     * @param id
     *     id为null 表明是新增的流水号，否则为更新流水号
     * @param alias
     * @return
     */
    boolean isAliasExisted(String id, String alias);

    /**
     * 根据别名获取当前流水号
     *
     * @param alias
     * @return
     */
    public String getCurIdByAlias(String alias);

    /**
     * 根据别名获取下一个流水号
     *
     * @param alias
     * @return
     */
    public String nextId(String alias);

    /**
     * 根据别名预览前十条流水号
     *
     * @param alias
     * @return
     */
    public List<SerialNo> getPreviewIden(String alias);


    R listJson(Map<String, Object> params, SerialNoQuery serialNoQuery);

    void save(SerialNo serialNo);

    void batchRemove(List<String> list);

    R getCurById(String id);


    void update(SerialNo serialNo);
}

@Service("serialNoManager")
public class SerialNoManagerImpl implements SerialNoManager {

    @Autowired
    SerialNoDao serialNoDao;
    @Override
    public boolean isAliasExisted(String id, String alias) {
        return serialNoDao.isAliasExisted(id, alias)>0;
    }
    /**
     * 根据流程规则别名获取得当前流水号。
     *
     * @param alias 流水号规则别名。
     * @return
     */
    @Override
    public String getCurIdByAlias(String alias) {
        SerialNo serialNoDaoByAlias = this.serialNoDao.getByAlias(alias);
        Integer curValue = serialNoDaoByAlias.getCurValue();
        if (curValue == null) {
            curValue = serialNoDaoByAlias.getInitValue();
        }
        return getByRule(serialNoDaoByAlias.getRegulation(), serialNoDaoByAlias.getNoLength(), curValue);
    }

    /**
     * 根据规则返回需要显示的流水号。
     *
     * @param rule     流水号规则。
     * @param length   流水号的长度。
     * @param curValue 流水号的当前值。
     * @return
     */
    private String getByRule(String rule, int length, int curValue) {
        Calendar now = Calendar.getInstance();
        int month = now.get(Calendar.MONTH) + 1;
        int day = now .get(Calendar.DAY_OF_MONTH);

        StringBuilder serialNo = new StringBuilder();
        int fillLength = length - String.valueOf(curValue).length();
        for (int i = 0; i < fillLength; i++) {
            serialNo.append("0");
        }
        serialNo.append(curValue);

        return rule.replace("{yyyy}",String.valueOf(now.get(Calendar.YEAR)))
            .replace("{MM}", String.valueOf((month < 10) ? "0" + month : "" + month))
            .replace("{mm}", String.valueOf(month))
            .replace("{DD}", String.valueOf((day < 10) ? "0" + day : "" + day))
            .replace("{dd}", String.valueOf(day))
            .replace("{NO}", serialNo.toString())
            .replace("{no}", String.valueOf(curValue));


    }


    /**
     * 根据流程规则别名获取得下一个流水号。
     *
     * @param alias 流水号规则别名。
     * @return
     */
    @Override
    public synchronized String nextId(String alias) {
        SerialNo serialNoDaoByAlias = serialNoDao.getByAlias(alias);
        if (serialNoDaoByAlias == null) {
            throw new BusinessException("流水号【" + alias + "】缺失！请联系系统管理员！");
        }

        Result result = genResult(serialNoDaoByAlias);

        int tryTimes = 0;
        while (result.getRtn() == 0) {
            tryTimes++; // 防止在使用中修改步长，导致死循环
            if (tryTimes > 100) {
                throw new BusinessException("获取流水号失败！ " + serialNoDaoByAlias.getAlias());
            }


            serialNoDaoByAlias.setCurValue(result.getCurValue());
            result = genResult(serialNoDaoByAlias);
        }
        return result.getIdNo();
    }



    public Result genResult(SerialNo serialNo) {
        String rule = serialNo.getRegulation();
        int step = serialNo.getStep();
        int genEveryDay = serialNo.getGenType();

        //如果失败过一次、使用失败的当前值。没有失败
        Integer curValue = serialNo.getCurValue();

        if (curValue == 0) {
            curValue = serialNo.getInitValue();
        }


        // 每天都生成
        if (genEveryDay == 1) {
            String curDate = getCurDate();
            String oldDate = serialNo.getCurDate();
            if (!curDate.equals(oldDate)) {
                serialNo.setCurDate(curDate);
                curValue = serialNo.getInitValue();
            } else {
                curValue = curValue + step;
            }
        } else {
            curValue = curValue + step;
        }
        serialNo.setNewCurValue(curValue);
        int i = 0;
        i = serialNoDao.updByAlias(serialNo);
        Result result = new Result(0, "", curValue);
        if (i > 0) {
            String rtn = getByRule(rule, serialNo.getNoLength(), curValue);
            result.setIdNo(rtn);
            result.setRtn(1);
        }
        return result;
    }

    /**
     * 返回当前日期。格式为 年月日。
     *
     * @return
     */
    public String getCurDate() {
        Date date = new Date();
        return DateUtil.format(date, "yyyyMMdd");

    }

    /**
     * 预览时，获取前十个流水号
     *
     * @param alias
     * @return
     */
    @Override
    public List<SerialNo> getPreviewIden(String alias) {
        int genNum = 10;
        SerialNo byAlias = serialNoDao.getByAlias(alias);
        String rule = byAlias.getRegulation();
        int step = byAlias.getStep();
        Integer curValue = byAlias.getCurValue();
        if (curValue == null) {
            curValue = byAlias.getInitValue();
        }
        List<SerialNo> tempList = new ArrayList<>();
        for (int i = 0; i < genNum; i++) {
            SerialNo serialNo = new SerialNo();
            if (i > 0) {
                curValue += step;
            }
            String rtn = getByRule(rule, byAlias.getNoLength(), curValue);
            serialNo.setId(curValue.toString());
            serialNo.setCurIdenValue(rtn);
            tempList.add(serialNo);
        }
        return tempList;
    }
    @Override
    public R listJson(Map<String, Object> params, SerialNoQuery serialNoQuery) {
        IPage<SerialNo> page = new Query<SerialNo>().getPage(params);
        IPage<SerialNo> ucRolesList = serialNoDao.getAllPage(page,params,serialNoQuery);

        return R.ok().put(Constants.COUNT, ucRolesList.getTotal()).put(Constants.DATA, ucRolesList.getRecords());
    }
    @Override
    public void save(SerialNo serialNo) {
        serialNoDao.save(serialNo);
    }
    @Override
    public void batchRemove(List<String> list) {
        serialNoDao.batchRemove(list);
    }
    @Override
    public R getCurById(String id) {
        SerialNo serialNo=  serialNoDao.getById(id);
        return R.ok().put(Constants.DATA,serialNo);
    }
    @Override
    public void update(SerialNo serialNo) {
        serialNoDao.update(serialNo);
    }

    public class Result {

        private int rtn = 0;
        private String idNo = "";
        private int curValue = 0;

        public Result(int rtn, String idNo, int curValue) {
            this.rtn = rtn;
            this.idNo = idNo;
            this.setCurValue(curValue);
        }


        public int getRtn() {
            return rtn;
        }

        public void setRtn(int rtn) {
            this.rtn = rtn;
        }

        public String getIdNo() {
            return idNo;
        }

        public void setIdNo(String idNo) {
            this.idNo = idNo;
        }

        public int getCurValue() {
            return curValue;
        }

        public void setCurValue(int curValue) {
            this.curValue = curValue;
        }


    }


}

3、流水号DAO使用

public interface SerialNoDao {

    /**
     * 判读流水号别名是否已经存在
     *
     * @param id
     *     id为null 表明是新增的流水号，否则为更新流水号
     * @param alias
     * @return
     */
    Integer isAliasExisted(@Param("id") String id, @Param("alias") String alias);

    /**
     * 根据别名获取流水号数据（数据库锁定了对应的行数据）
     *
     * @param alias
     * @return
     */
    SerialNo getByAlias(String alias);

    /**
     * 根据流程别名 。
     *
     * @param SerialNo
     *     void
     */
    int updByAlias(SerialNo serialNo);

    /**
     *
     * @param page
     * @param params
     * @param serialNoQuery
     * @return
     */
    IPage<SerialNo> getAllPage(IPage<SerialNo> page, @Param("params") Map<String, Object> params, @Param("serialNoQuery")  SerialNoQuery serialNoQuery);

    void save(SerialNo serialNo);

    void batchRemove(@Param("list")List<String> list);

    SerialNo getById(String id);

    void update(SerialNo serialNo);
}

4、对应的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.pangubpm.modules.data.dao.SerialNoDao">
    <resultMap id="SerialNo" type="SerialNo">
        <id property="id" column="id_" jdbcType="VARCHAR"/>
        <result property="name" column="name_" jdbcType="VARCHAR"/>
        <result property="alias" column="alias_" jdbcType="VARCHAR"/>
        <result property="regulation" column="regulation_" jdbcType="VARCHAR"/>
        <result property="genType" column="gen_type_" jdbcType="NUMERIC"/>
        <result property="noLength" column="no_length_" jdbcType="NUMERIC"/>
        <result property="curDate" column="cur_date_" jdbcType="VARCHAR"/>
        <result property="initValue" column="init_value_" jdbcType="NUMERIC"/>
        <result property="curValue" column="cur_value_" jdbcType="NUMERIC"/>
        <result property="step" column="step_" jdbcType="NUMERIC"/>
        <result column="DELETED" jdbcType="BOOLEAN" property="deleted" />
    </resultMap>

    <sql id="columns">
      id_,name_,alias_,regulation_,gen_type_,no_length_,cur_date_,init_value_,cur_value_,step_
   </sql>



    <update id="batchRemove"  parameterType="java.util.List">


        update sys_serialno
        set
        deleted=1
        where id_ in
        <foreach collection="list" index="index" item="item"
                 separator="," open="(" close=")">
            #{item}
        </foreach>
    </update>



    <select id="getAllPage" parameterType="Map" resultMap="SerialNo">
      SELECT * FROM sys_serialno
      WHERE
      DELETED=0



        <if test="serialNoQuery.name!=null  and serialNoQuery.name!='' ">
            and  name_  like CONCAT('%', #{serialNoQuery.name,jdbcType=VARCHAR},'%')
        </if>
        <if test="serialNoQuery.alias!=null  and serialNoQuery.alias!='' ">
            and  alias_  like CONCAT('%', #{serialNoQuery.alias,jdbcType=VARCHAR},'%')
        </if>
        order by UPDATE_TIME desc

   </select>


    <update id="update" parameterType="com.pangubpm.modules.data.entity.SerialNo">
      UPDATE sys_serialno SET
      name_=#{name,jdbcType=VARCHAR},
      alias_=#{alias,jdbcType=VARCHAR},
      regulation_=#{regulation,jdbcType=VARCHAR},
      gen_type_=#{genType,jdbcType=NUMERIC},
      no_length_=#{noLength,jdbcType=NUMERIC},
      cur_date_=#{curDate,jdbcType=VARCHAR},
      init_value_=#{initValue,jdbcType=NUMERIC},
      cur_value_=#{curValue,jdbcType=NUMERIC},
      step_=#{step,jdbcType=NUMERIC},
      UPDATE_TIME=#{updateTime,jdbcType=TIMESTAMP}
      WHERE
      id_=#{id}
   </update>

    <update id="updByAlias" parameterType="com.pangubpm.modules.data.entity.SerialNo">
      UPDATE sys_serialno SET
      cur_date_=#{curDate,jdbcType=VARCHAR},
      cur_value_=#{newCurValue,jdbcType=NUMERIC},
      UPDATE_TIME=#{updateTime,jdbcType=TIMESTAMP}

      WHERE alias_=#{alias,jdbcType=VARCHAR}
      and cur_value_=#{curValue,jdbcType=NUMERIC}
   </update>



    <select id="isAliasExisted" resultType="java.lang.Integer">
        select count(*) from sys_serialno where alias_=#{alias}
        <if test="id!=null">AND id_ !=#{id}</if>
    </select>

    <select id="getByAlias" parameterType="String" resultMap="SerialNo">
        SELECT
        <include refid="columns"/>
        FROM sys_serialno
        WHERE
        alias_=#{alias}
    </select>
    <select id="getById" parameterType="String" resultMap="SerialNo">
        SELECT
        <include refid="columns"/>
        FROM sys_serialno
        WHERE
        id_=#{id}
    </select>

    <insert id="save" parameterType="com.pangubpm.modules.data.entity.SerialNo">
        insert into sys_serialno
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null">
                ID_,
            </if>
            <if test="name != null">
                NAME_,
            </if>
            <if test="alias != null">
                ALIAS_,
            </if>
            <if test="regulation != null">
                regulation_,
            </if>
            <if test="genType != null">
                gen_type_,
            </if>
            <if test="noLength != null">
                no_length_,
            </if>
            <if test="curDate != null">
                cur_date_,
            </if>
            <if test="initValue != null">
                init_value_,
            </if>
            <if test="curValue != null">
                cur_value_,
            </if>
            <if test="step != null">
                step_,
            </if>
            <if test="deleted != null">
                DELETED,
            </if>
            <if test="updateTime != null">
                UPDATE_TIME ,
            </if>
            <if test="createTime!= null">
                CREATE_TIME ,
            </if>

        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="id != null">
                #{id,jdbcType=VARCHAR},
            </if>
            <if test="name != null">
                #{name,jdbcType=VARCHAR},
            </if>
            <if test="alias != null">
                #{alias,jdbcType=VARCHAR},
            </if>
            <if test="regulation != null">
                #{regulation,jdbcType=VARCHAR},
            </if>
            <if test="genType != null">
                #{genType,jdbcType=DECIMAL},
            </if>
            <if test="noLength != null">
                #{noLength,jdbcType=NUMERIC},
            </if>
            <if test="curDate != null">
                #{curDate,jdbcType=VARCHAR},
            </if>
            <if test="initValue != null">
                #{initValue,jdbcType=NUMERIC},
            </if>
            <if test="curValue != null">
                #{curValue,jdbcType=NUMERIC},
            </if>
            <if test="step != null">
                #{step,jdbcType=NUMERIC},
            </if>
            <if test="deleted != null">
                #{deleted,jdbcType=BOOLEAN},
            </if>
            <if test="updateTime != null">
                #{updateTime,jdbcType=TIMESTAMP},
            </if>
            <if test="createTime!= null">
                #{createTime,jdbcType=TIMESTAMP},
            </if>

        </trim>


    </insert>

</mapper>

5、涉及到的枚举定义

public enum PanGuColumnType {

   /**
    * 大文本
    */
   CLOB("clob", "大文本", new String[] { "text", "clob", "blob", "mediumblob", "mediumtext", "longblob", "longtext" }),
   /**
    * 数字型
    */
   NUMBER("number", "数字型", new String[] { "bit","tinyint", "number", "smallint", "mediumint", "int", "integer", "bigint", "float", "double", "decimal", "numeric" }),
   /**
    * 字符串
    */
   VARCHAR("varchar", "字符串", new String[] { "varchar", "varchar2", "char", "tinyblob", "tinytext" }),
   /**
    * 日期型
    */
   DATE("date", "日期型", new String[] { "date", "time", "year", "datetime", "timestamp" });
   public static JSONArray toJson(){
      JSONArray jsonArray = new JSONArray();
      for (PanGuColumnType e : PanGuColumnType.values()) {
         JSONObject object = new JSONObject();
         object.put("key", e.getKey());
         object.put("desc", e.getDesc());
         object.put("supports", e.getSupports());
         jsonArray.add(object);
      }
      return jsonArray;
   }

   private String key;
   private String desc;
   private String[] supports;

   private PanGuColumnType(String key, String desc, String[] supports) {
      this.key = key;
      this.desc = desc;
      this.supports = supports;
   }

   public String getKey() {
      return key;
   }

   public String getDesc() {
      return desc;
   }

   public String[] getSupports() {
      return supports;
   }

   /**
    * <pre>
    * 根据key来判断是否跟当前一致
    * </pre>
    *
    * @param key
    * @return
    */
   public boolean equalsWithKey(String key) {
      return this.key.equals(key);
   }

   public static PanGuColumnType getByKey(String key) {
      for (PanGuColumnType type : PanGuColumnType.values()) {
         if (type.getKey().equals(key)) {
            return type;
         }
      }
      return null;
   }

   /**
    * <pre>
    * 根据数据库的字段类型获取type
    * 无视大小写
    * </pre>
    *
    * @param dbDataType
    *            数据库的字段类型
    * @return
    */
   public static PanGuColumnType getByDbDataType(String dbDataType, String errMsgApp) {
      for (PanGuColumnType type : PanGuColumnType.values()) {
         for (String support : Arrays.asList(type.supports)) {
            if (dbDataType.toLowerCase().contains(support.toLowerCase())) {
               return type;
            }
         }

      }
      throw  new BusinessException(errMsgApp);
   }

   public static PanGuColumnType getByDbDataType(String dbDataType) {
      return getByDbDataType(dbDataType, "");
   }
}