org.myframework.codeutil.TableMetaInfoReader Maven / Gradle / Ivy

Go to download
Show more of this group Show more artifacts with this name
Show all versions of coder-maven-plugin Show documentation
Assist in generating CRUD code, without limitation on the specific language, as long as you can write velocity templates.
There is a newer version: 1.0.8
Show newest version
package org.myframework.codeutil;

import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.myframework.type.JdbcType;
import org.myframework.util.FileUtil;
import org.myframework.util.StringUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

/**
 * @className: TableMetaInfoReader
 * @description:
 * @author: WANGHUI
 * @createDate: 2018/6/7 14:35
 * @version: 1.0
 */
public class TableMetaInfoReader {

    private final static Logger logger = LoggerFactory.getLogger(TableMetaInfoReader.class);

    Properties properties;

    private String basedir;

    private String tableInfoPropFile;

    //待读取数据库
    private String catalog;

    //待读取EXCEL，存放表结构信息
    private String excelConfig;

    //是从数据库读取，还是从excel读取
    private boolean isDbClass;

    public TableMetaInfoReader(String basedir , String tableInfoPropFile ) throws IOException {
        this.tableInfoPropFile = tableInfoPropFile;
        this.basedir = basedir;

        properties = FileUtil.loadProperties(basedir + "/" + tableInfoPropFile);
        System.out.println(" loadProperties : " +  basedir + "/" + tableInfoPropFile);
        catalog = properties.getProperty("dbname", "");
        excelConfig = properties.getProperty("excelConfig", "tableConfig.xlsx");
        isDbClass = "True".equalsIgnoreCase(properties.getProperty("isDbClass", "true"));
    }

    private Connection getConnection() throws Exception {
        Properties localProperties = new Properties();
        localProperties.put("remarksReporting", "true");//注意这里
        localProperties.put("user", properties.getProperty("jdbc.username"));
        localProperties.put("password", properties.getProperty("jdbc.password"));

        //	   orcl为数据库的SID
        Class.forName(properties.getProperty("jdbc.driver")).newInstance();
        Connection conn = DriverManager.getConnection(properties.getProperty("jdbc.url"), localProperties);
        logger.info("读取数据库配置信息：" + properties);
        return conn;
    }


    public List getColumns(String tableName) throws Exception {
        if (isDbClass) {
            //数据库读取配置信息
            return getLsColumns(catalog, tableName.toUpperCase());
        } else {
            return getColumnFromFile(basedir + "/" + excelConfig, tableName);
        }
    }

    /**
     * 从csv文件中读取配置信息
     *
     * @param fileName
     * @return
     * @throws Exception
     */
    private List getColumnFromFile(String fileName, String sheetName) throws Exception {
        List columnList = new ArrayList();
        Workbook workBook = new XSSFWorkbook(FileUtil.getInputStream(fileName));
        Sheet sheet = workBook.getSheet(sheetName);
        if(sheet == null ) throw new RuntimeException(sheetName + " 对应的Sheet不存在" + fileName);
        //总行数
        int totalRows = sheet.getPhysicalNumberOfRows();

        for (int i = 1; i < totalRows; i++) {
            Row row = sheet.getRow(i);
            Column column = new Column();
            column.setColumnName(row.getCell(0).getStringCellValue());//字段名
            column.setComments(row.getCell(1).getStringCellValue());//备注
            column.setDataType(row.getCell(2).getStringCellValue());//数据类型
            column.setJavaName(row.getCell(3).getStringCellValue());//java字段名
            if (row.getCell(4) != null)
                column.setColumnKey(row.getCell(4).toString());//是否主键字段
            columnList.add(column);
        }
        workBook.close();
        return columnList;
    }


    private List getLsColumns(String catalog, String tableName) throws Exception {
        Connection conn = getConnection();
        List lsColumns = new ArrayList(10);
        PreparedStatement stmt = conn.prepareStatement("select *  from " + tableName + " where 1=0 ");
        ResultSet resultSet = stmt.executeQuery();
        ResultSetMetaData rsmd = resultSet.getMetaData();
        int n = rsmd.getColumnCount();
        logger.debug("============读取表相关的字段信息 START ================");
        for (int i = 1; i <= n; i++) {
            String colName = rsmd.getColumnName(i);
            String fieldName = StringUtil.toBeanPatternStr(colName);
            String dataType = rsmd.getColumnClassName(i);
            String jdbcType = JdbcType.forCode(rsmd.getColumnType(i)).toString();
            if ("java.math.BigDecimal".equals(dataType) && rsmd.getScale(i) == 0)
                dataType = "Long";
            if ("oracle.sql.CLOB".equals(dataType))
                dataType = "String";
            Column column = new Column();
            column.setColumnName(colName);
            column.setJavaName(fieldName);
            column.setJdbcType(jdbcType);
            column.setDataType(dataType.endsWith("Timestamp") ? "java.util.Date" : dataType);
            column.setPrecision(String.valueOf(rsmd.getPrecision(i)));
            column.setScale(String.valueOf(rsmd.getScale(i)));
            column.setLength(String.valueOf(rsmd.getColumnDisplaySize(i)));
            column.setNullable(String.valueOf(1 == rsmd.isNullable(i)));

//			获取列注释
            DatabaseMetaData dbmd = conn.getMetaData();
            ResultSet rs = dbmd.getColumns(null, null, tableName, null);
            while (rs.next()) {
                if (colName.equals(rs.getString("COLUMN_NAME"))) {
                    String comments = rs.getString("REMARKS");
                    column.setComments(StringUtil.asString(comments));
                }

            }
            //				获取主键列

            ResultSet rs2 = dbmd.getPrimaryKeys(catalog, null, tableName);
            while (rs2.next()) {
                if (colName.equals(rs2.getString("COLUMN_NAME")))
                    column.setColumnKey("TRUE");
            }
            logger.debug("TABLE COLUMN INFO>>>>>" + column + "---------------------");
            lsColumns.add(column);
        }
        logger.debug("============读取表相关的字段信息 END ================");
        return lsColumns;
    }


}