jnpf-common-v6x/jnpf-boot-common/jnpf-common-database/target/classes/jnpf/database/model/dbtable/JdbcTableModel.java

package jnpf.database.model.dbtable;

import jnpf.constant.MsgCode;
import jnpf.constant.TableFieldsNameConst;
import jnpf.database.constant.RsColumnKeyConst;
import jnpf.database.constant.RsTableKeyConst;
import jnpf.database.model.dbfield.DbFieldModel;
import jnpf.database.model.dbfield.JdbcColumnModel;
import jnpf.database.model.entity.DbLinkEntity;
import jnpf.database.model.interfaces.DbSourceOrDbLink;
import jnpf.database.util.ConnUtil;
import jnpf.exception.DataException;
import lombok.Cleanup;
import io.swagger.v3.oas.annotations.media.Schema;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * JDBC元数据字段模型类型
 *
 * @author JNPF开发平台组
 * @version V3.2.0
 * @copyright 引迈信息技术有限公司
 * @date 2021/10/21
 */
@Data
@NoArgsConstructor
public class JdbcTableModel {

    /**
     * 数据库类型
     */
    private String dbEncode;

    /**
     * 表名
     */
    private String table;

    /**
     * 表类型
     */
    private String tableType;

    /**
     * 表注释
     */
    private String comment;

    /**
     * 主键字段
     */
    private String primaryField;

    /**
     * 主键字段列表
     */
    private List<String> primaryFields;

    /**
     * jdbc字段集合
     */
    private List<JdbcColumnModel> jdbcColumnModelList;

    /* ================== 构造方法 ================= */

    public JdbcTableModel(DbLinkEntity dbLinkEntity, String table) throws Exception {
//        @Cleanup Connection conn = PrepSqlDTO.getConn(dbLinkEntity);
        @Cleanup Connection conn = ConnUtil.ConnCommon.getConnRemarks(dbLinkEntity);
        @Cleanup ResultSet rs = getTableMetaDateRs(conn, table);
        List<String> primaryKeys = getPrimaryKeys(conn, table).stream().sorted((o1, o2) -> {
                        return (TableFieldsNameConst.F_ID.equalsIgnoreCase(o2) || TableFieldsNameConst.ID.equalsIgnoreCase(o2)) ? 1 : 0;
        }).collect(Collectors.toList());
        if (rs.next()) {
            this.dbEncode = dbLinkEntity.getDbType();
            this.table = rs.getString(RsTableKeyConst.TABLE_NAME);
            this.tableType = rs.getString(RsTableKeyConst.TABLE_TYPE);
            this.comment = rs.getString(RsTableKeyConst.REMARKS);
            this.primaryField = primaryKeys.isEmpty() ? null : primaryKeys.get(0);
            this.primaryFields = primaryKeys;
            this.jdbcColumnModelList = JdbcColumnModel.getList(conn, table, primaryFields);
        }else {
            throw new DataException(MsgCode.DB009.get(table));
        }
    }

    /* ================== 内部方法 ================= */

    public DbTableFieldModel convertDbTableFieldModel() throws Exception {
        // 转换表
        DbTableFieldModel dbTableFieldModel = new DbTableFieldModel();
        dbTableFieldModel.setTable(this.table);
        dbTableFieldModel.setComment(this.comment);
        // 转换字段集合
        List<DbFieldModel> dbFieldModelList = new ArrayList<>();
        for (JdbcColumnModel jdbcColumnModel : this.jdbcColumnModelList) {
            dbFieldModelList.add(jdbcColumnModel.convertDbFieldModel(this.dbEncode));
        }
        dbTableFieldModel.setDbFieldModelList(dbFieldModelList);
        return dbTableFieldModel;
    }

    /* ================== 静态方法 ================= */

    /**
     * 获取表元数据对象(所有表)
     * @param conn 数据连接
     * @return ignore
     * @throws SQLException ignore
     */
    public static List<JdbcTableModel> getList(Connection conn) throws Exception {
        @Cleanup ResultSet rs = getTableMetaDateRs(conn);
        List<JdbcTableModel> list = new ArrayList<>();
        while (rs.next()) {
            JdbcTableModel jdbcTableModel = new JdbcTableModel();
            jdbcTableModel.setTable(rs.getString(RsTableKeyConst.TABLE_NAME));
            jdbcTableModel.setTableType(rs.getString(RsTableKeyConst.TABLE_TYPE));
            jdbcTableModel.setComment(rs.getString(RsTableKeyConst.REMARKS));
            jdbcTableModel.setJdbcColumnModelList(JdbcColumnModel.getList(conn, jdbcTableModel.getTable(), jdbcTableModel.getPrimaryFields()));
            list.add(jdbcTableModel);
        }
        return list;
    }


    public static String getPrimary(DbSourceOrDbLink dbSourceOrDbLink, String table) throws SQLException {
        @Cleanup Connection conn = ConnUtil.getConnOrDefault(dbSourceOrDbLink);
        return getPrimary(conn, table);
    }

    /**
     * 获取第一个主键， 排除某些主键
     */
    public static String getPrimaryExculde(Connection conn, String table, String... excludeField) throws SQLException {
        List<String> primaryKeys = getPrimaryKeys(conn, table);
        if(!primaryKeys.isEmpty()){
            for (String primaryKey : primaryKeys) {
                boolean exlude = false;
                for (String exclude : excludeField) {
                    if(primaryKey.equalsIgnoreCase(exclude)){
                        exlude = true;
                        break;
                    }
                }
                if(!exlude){
                    return primaryKey;
                }
            }
        }
        return "";
    }


    /**
     * 获取第一个主键， 排除某些主键
     */
    public static String getPrimaryExculde(DbSourceOrDbLink dbSourceOrDbLink, String table, String... excludeField) throws SQLException {
        @Cleanup Connection conn = ConnUtil.getConnOrDefault(dbSourceOrDbLink);
        return getPrimaryExculde(conn, table, excludeField);
    }

    /**
     * 如有多个主键返回第一个主键
     */
    public static String getPrimary(Connection conn, String table) throws SQLException {
        List<String> primaryKeys = getPrimaryKeys(conn, table);
        return primaryKeys.isEmpty()? "" : primaryKeys.get(0);
    }


    public static String getPrimaryIdFirst(DbSourceOrDbLink dbSourceOrDbLink, String table) throws SQLException {
        @Cleanup Connection conn = ConnUtil.getConnOrDefault(dbSourceOrDbLink);
        return getPrimaryIdFirst(conn, table);
    }

    /**
     *  如有多个主键返回第一个主键, id优先返回
     */
    public static String getPrimaryIdFirst(Connection conn, String table) throws SQLException {
        List<String> primaryKeys = getPrimaryKeys(conn, table);
        String findedId = "";
        for (String primaryKey : primaryKeys) {
            findedId = primaryKey;
            // 联合主键优先返回f_id, id字段
            if(TableFieldsNameConst.F_ID.equalsIgnoreCase(findedId) || TableFieldsNameConst.ID.equalsIgnoreCase(findedId)){
                break;
            }
        }
        return findedId;
    }


    /**
     * 返回全部主键
     */
    public static List<String> getPrimaryKeys(DbSourceOrDbLink dbSourceOrDbLink, String table) throws SQLException {
        @Cleanup Connection conn = ConnUtil.getConnOrDefault(dbSourceOrDbLink);
        return getPrimaryKeys(conn, table);
    }

    /**
     * 返回全部主键
     */
    public static List<String> getPrimaryKeys(Connection conn, String table) throws SQLException {
        //获取表主键
        @Cleanup ResultSet rs = conn.getMetaData().getPrimaryKeys(conn.getCatalog(), null, table);
        List<String> primaryKeys = new ArrayList<>();
        while(rs.next()){
            primaryKeys.add(rs.getString(RsColumnKeyConst.COLUMN_NAME));
        }
        return primaryKeys;
    }

    /**
     * 返回全部表的主键信息
     * @return [{表名:主键名}]
     */
    public static List<Map<String, String>> getPrimaryMapList(Connection conn, String schema) throws SQLException {
        //获取表主键
        @Cleanup ResultSet rs = conn.getMetaData().getPrimaryKeys(conn.getCatalog(), schema, null);
        List<Map<String, String>> list = new ArrayList<>();
        while (rs.next()){
            Map<String, String> map = new HashMap<>();
            map.put(rs.getString(RsColumnKeyConst.TABLE_NAME), rs.getString(RsColumnKeyConst.COLUMN_NAME));
            list.add(map);
        }
        return list;
    }

    /* ================================== 结果集 ================================== */

    /**
     * 从conn中获取数据库的表元数据
     * @param conn 数据连接
     * @return 返回表元数据
     * @throws SQLException ignore
     */
    public static ResultSet getTableMetaDateRs(Connection conn, String table) throws SQLException {
        return conn.getMetaData().getTables(conn.getCatalog(), null, table, new String[]{"TABLE"});
    }

    private static ResultSet getTableMetaDateRs(Connection conn) throws SQLException {
        return getTableMetaDateRs(conn, null);
    }

}