mysql数据库表映射实体类

 

分享知识 传递快乐

• JDBC连接数据库；
• 通过配置文件连接数据库；
• 反射指定表生成实体类；
• 反射当前数据库下所有表生成实体类；
• 类名、属性名、get/set方法名都采用驼峰命名
• 类名注释、属性注释、方法注释等

package com.xh.activiti.commons.utils;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.Map;
import java.util.Properties;

import org.apache.commons.lang3.StringUtils;

/**
 * <p>Title: 获取数据库基本信息的工具类</p>
 * <p>Description: </p>
 * 
 * @author H.Yang
 * @QQ 1033542070
 * @date 2018年3月12日
 */
public class GenEntityTable {

  private static final String PROPERTIES_NAME = "config/db.properties";
  private static final String PACKAGE_OUT_PATH = "com.xh.activiti.entity";// 指定实体生成所在包的路径
  private static final String AUTHOR_NAME = "H.Yang";// 作者名字

  private static String tableName = "";// 表名
  private static String remarkes = "";// 数据库表注释
  private static LinkedList<Map> resultCol = null;// 列信息
  private static boolean utilPackage = false; // 是否需要导入包java.util.*
  private static boolean sqlPackage = false; // 是否需要导入包java.sql.*
  private static Connection connection = null;
  private static ResultSet resultSet = null;

  public GenEntityTable() {
    super();
  }

  public GenEntityTable(final String driver, final String url, final String name, final String pass) {
    try {
      Properties props = new Properties();
      props.setProperty("user", name);
      props.setProperty("password", pass);
      props.setProperty("remarks", "true"); // 设置可以获取remarks信息
      props.setProperty("useInformationSchema", "true");// 设置可以获取tables remarks信息

      // 创建连接
      Class.forName(driver);
      // getConnection = DriverManager.getConnection(URL, NAME, PASS);
      connection = DriverManager.getConnection(url, props);
      System.out.println("数据库连接成功");
    } catch (ClassNotFoundException | SQLException e) {
      System.out.println("数据库驱动加载或连接异常！");
      e.printStackTrace();
    }
  }

  /**
   * <p>Title: 读取配置文件连接数据库</p>
   * <p>Description: </p>
   * 
   * @author H.Yang
   * @date 2018年3月12日
   * 
   */
  public static void init() {
    try {
      Properties properties = new Properties();
      InputStream in = GenEntityTable.class.getClassLoader().getResourceAsStream(PROPERTIES_NAME);
      properties.load(in);

      Properties props = new Properties();
      props.setProperty("user", properties.getProperty("db.master.user"));
      props.setProperty("password", properties.getProperty("db.master.password"));
      props.setProperty("remarks", "true"); // 设置可以获取remarks信息
      props.setProperty("useInformationSchema", "true");// 设置可以获取tables remarks信息

      // 创建连接
      Class.forName(properties.getProperty("db.master.driver"));
      connection = DriverManager.getConnection(properties.getProperty("db.master.url"), props);
      System.out.println("数据库连接成功");
      in.close();
    } catch (FileNotFoundException e) {
      e.printStackTrace();
    } catch (IOException e) {
      e.printStackTrace();
    } catch (SQLException e) {
      e.printStackTrace();
    } catch (ClassNotFoundException e) {
      e.printStackTrace();
    }

  }

  /**
   * <p>Title: 获取指定表的基本信息：字段名、字段类型、字段注释</p>
   * <p>Description: 自带关闭连接</p>
   * 
   * @author H.Yang
   * @date 2018年3月12日
   * 
   */
  private void startTable() {
    try {
      resultCol = new LinkedList<>();
      Map<String, Object> map = null;
      resultSet = connection.getMetaData().getTables(null, "%", tableName, new String[] { "TABLE" });
      while (resultSet.next()) {
        remarkes = resultSet.getString("REMARKS");

        ResultSet rs = connection.getMetaData().getColumns(null, "%", resultSet.getString("TABLE_NAME").toUpperCase(), "%");
        while (rs.next()) {
          map = new HashMap<>();
          map.put("columnName", rs.getString("COLUMN_NAME"));
          map.put("remarks", rs.getString("REMARKS"));
          map.put("dbType", rs.getString("TYPE_NAME"));
          map.put("valueType", sqlType2JavaType(rs.getString("TYPE_NAME")));

          if (rs.getString("TYPE_NAME").equalsIgnoreCase("datetime")) {
            utilPackage = true;
          }
          if (rs.getString("TYPE_NAME").equalsIgnoreCase("image") || rs.getString("TYPE_NAME").equalsIgnoreCase("text")) {
            sqlPackage = true;
          }
          resultCol.add(map);
        }
        if (rs != null) {
          rs.close();
        }
      }

      // 在内存中生成代码
      String content = parse(tableName);
      // 写入到文件中
      File directory = new File("");
      String outputPath = directory.getAbsolutePath() + "/src/main/java/" + PACKAGE_OUT_PATH.replace(".", "/") + "/"
          + initcap(tableName) + ".java";
      System.out.println("写出的路径:" + outputPath);
      // 创建文件
      File file = new File(outputPath);
      if (!file.exists()) {
        file.createNewFile();
      }
      // 写出到硬盘
      FileWriter fw = new FileWriter(file);
      PrintWriter pw = new PrintWriter(fw);
      pw.println(content);
      pw.flush();
      pw.close();
    } catch (SQLException | IOException e) {
      e.printStackTrace();
    } finally {
      this.close();
    }
  }

  /**
   * <p>Title: 获取当前是数据库下的所有表的基本信息：字段名、字段类型、字段注释</p>
   * <p>Description: </p>
   * 
   * @author H.Yang
   * @date 2018年3月13日
   * 
   */
  private void startMultitermTable() {
    try {
      Map<String, Object> map = null;
      resultSet = connection.getMetaData().getTables(null, "%", null, new String[] { "TABLE" });
      while (resultSet.next()) {
        resultCol = new LinkedList<>();
        tableName = resultSet.getString("TABLE_NAME");
        remarkes = resultSet.getString("REMARKS");
        ResultSet rs = connection.getMetaData().getColumns(null, "%", tableName.toUpperCase(), "%");
        while (rs.next()) {
          map = new HashMap<>();
          map.put("columnName", rs.getString("COLUMN_NAME"));
          map.put("remarks", rs.getString("REMARKS"));
          map.put("dbType", rs.getString("TYPE_NAME"));
          map.put("valueType", sqlType2JavaType(rs.getString("TYPE_NAME")));

          if (rs.getString("TYPE_NAME").equalsIgnoreCase("datetime")) {
            utilPackage = true;
          }
          if (rs.getString("TYPE_NAME").equalsIgnoreCase("image") || rs.getString("TYPE_NAME").equalsIgnoreCase("text")) {
            sqlPackage = true;
          }
          resultCol.add(map);
        }
        if (rs != null) {
          rs.close();
        }

        // 在内存中生成代码
        String content = parse(transVar(tableName));
        // 写入到文件中
        File directory = new File("");
        String outputPath = directory.getAbsolutePath() + "/src/main/java/" + PACKAGE_OUT_PATH.replace(".", "/") + "/"
            + transVar(tableName) + ".java";
        System.out.println("写出的路径:" + outputPath);
        // 创建文件
        File file = new File(outputPath);
        if (!file.exists()) {
          file.createNewFile();
        }
        // 写出到硬盘
        FileWriter fw = new FileWriter(file);
        PrintWriter pw = new PrintWriter(fw);
        pw.println(content);
        pw.flush();
        pw.close();
      }

    } catch (SQLException e) {
      e.printStackTrace();
    } catch (IOException e) {
      e.printStackTrace();
    } finally {
      this.close();
    }
  }

  /**
   * <p>Title: 关闭数据库连接</p>
   * <p>Description: </p>
   * 
   * @author H.Yang
   * @date 2018年3月12日
   * 
   */
  private static void close() {
    try {
      if (resultSet != null) {
        resultSet.close();
      }
      if (connection != null) {
        connection.close();
      }
      System.out.println("数据库连接关闭");
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

  /** 
   * 功能：生成实体类主体代码 
   * @param colNames 
   * @param colTypes 
   * @param colSizes 
   * @return 
   */
  private String parse(String tablename) {
    StringBuffer sb = new StringBuffer();
    sb.append("package " + PACKAGE_OUT_PATH + ";\r\n");
    sb.append("\r\n");
    // 判断是否导入工具包
    if (utilPackage) {
      sb.append("import java.util.Date;\r\n");
    }
    if (sqlPackage) {
      sb.append("import java.sql.*;\r\n");
    }
    // 注释部分
    sb.append("\r\n");
    sb.append("/**\r\n");
    sb.append(" * <p>Title: " + remarkes + "</p>\r\n");
    sb.append(" * <p>Description: </p>\r\n");
    sb.append(" * \r\n");
    sb.append(" * @author " + AUTHOR_NAME + "\r\n");
    sb.append(" * @QQ 1033542070\r\n");
    sb.append(" * \r\n");
    sb.append(" * @date " + new SimpleDateFormat("yyyy-MM-dd").format(new Date()) + "\r\n");
    sb.append(" */ \r\n");
    // 实体部分
    sb.append("public class " + tablename + "{\r\n");
    processAllAttrs(sb);// 属性
    processAllMethod(sb);// get set方法
    sb.append("}");

    return sb.toString();
  }

  /**
   * <p>Title: 生成所有属性 </p>
   * <p>Description: </p>
   * 
   * @author H.Yang
   * @date 2018年3月12日
   * 
   * @param sb
   */
  private void processAllAttrs(StringBuffer sb) {
    // 列名集合
    for (Map<String, String> mapAttr : resultCol) {
      if (StringUtils.isNotBlank(mapAttr.get("remarks"))) {
        sb.append("\t// " + mapAttr.get("remarks") + "\r\n");
      }
      sb.append("\tprivate " + mapAttr.get("valueType") + " " + defineVar(mapAttr.get("columnName")) + ";\r\n");
    }
  }

  /**
   * <p>Title: 生成所有方法 </p>
   * <p>Description: </p>
   * 
   * @author H.Yang
   * @date 2018年3月12日
   * 
   * @param sb
   */
  private void processAllMethod(StringBuffer sb) {

    for (Map<String, String> mapMethod : resultCol) {
      // SET
      sb.append("\r\n");
      if (StringUtils.isNotBlank(mapMethod.get("remarks"))) {
        sb.append("\t/**\r\n");
        sb.append("\t * " + mapMethod.get("remarks") + "\r\n");
        sb.append("\t */\r\n");
      }
      sb.append("\tpublic void set" + transVar(mapMethod.get("columnName")) + "(" + mapMethod.get("valueType") + " "
          + defineVar(mapMethod.get("columnName")) + ") {\r\n");
      sb.append("\t\tthis." + defineVar(mapMethod.get("columnName")) + " = " + defineVar(mapMethod.get("columnName")) + ";\r\n");
      sb.append("\t}\r\n");

      // GET
      sb.append("\r\n");
      if (StringUtils.isNotBlank(mapMethod.get("remarks"))) {
        sb.append("\t/**\r\n");
        sb.append("\t * " + mapMethod.get("remarks") + "\r\n");
        sb.append("\t */\r\n");
      }
      sb.append("\tpublic " + mapMethod.get("valueType") + " get" + initcap(transVar(mapMethod.get("columnName"))) + "() {\r\n");
      sb.append("\t\treturn " + defineVar(mapMethod.get("columnName")) + ";\r\n");
      sb.append("\t}\r\n");
    }
  }

  /**
   * <p>Title: 将输入字符串的首字母改成大写</p>
   * <p>Description: </p>
   * 
   * @author H.Yang
   * @date 2018年3月12日
   * 
   * @param str
   * @return
   */
  private String initcap(String str) {
    char[] ch = str.toCharArray();
    if (ch[0] >= 'a' && ch[0] <= 'z') {
      ch[0] = (char) (ch[0] - 32);
    }
    return new String(ch);
  }

  /**
   * <p>Title: 用于生成get/set方法时  </p>
   * <p>Description: 第一个字母大写，“_”后面一个字母大写，并去掉“_”</p>
   * 
   * @author H.Yang
   * @date 2018年3月12日
   * 
   * @param str
   * @return
   */
  private String transVar(String str) {
    StringBuffer sb = new StringBuffer(str.toLowerCase());
    String sign = "_";
    int index = 0;
    while ((index = sb.indexOf(sign, index)) != -1) {
      sb.replace(index, (index + sign.length()), "");

      char[] ch = new String(sb).toCharArray();
      if (ch[0] >= 'a' && ch[0] <= 'z') {
        ch[0] = (char) (ch[0] - 32);
      }
      if (index != 0 && index != ch.length) {
        ch[index] = (char) (ch[index] - 32);
      }
      sb = new StringBuffer(new String(ch));
      index = index + sign.length();
    }
    return sb.toString();
  }

  /**
   * <p>Title: 用于定义变量名  </p>
   * <p>Description: 首字母小写，“_”后面一个字母大写，并去掉“_”</p>
   * 
   * @author H.Yang
   * @date 2018年3月12日
   * 
   * @param str
   * @return
   */
  private String defineVar(String str) {
    StringBuffer sb = new StringBuffer(str.toLowerCase());
    String sign = "_";
    int index = 0;
    while ((index = sb.indexOf(sign, index)) != -1) {
      sb.replace(index, (index + sign.length()), "");
      char[] ch = new String(sb).toCharArray();
      if (ch[0] >= 'a' && ch[0] <= 'z' && index == 0) {
        ch[0] = (char) (ch[0] - 32);
      }
      if (index != 0 && index != ch.length) {
        ch[index] = (char) (ch[index] - 32);
      }
      sb = new StringBuffer(new String(ch));
      index = index + sign.length();
    }
    return sb.toString();
  }

  /**
   * <p>Title: 获得列的数据类型 </p>
   * <p>Description: </p>
   * 
   * @author H.Yang
   * @date 2018年3月12日
   * 
   * @param sqlType
   * @return
   */
  private String sqlType2JavaType(String sqlType) {
    if (sqlType.equalsIgnoreCase("bit")) {
      return "boolean";
    } else if (sqlType.equalsIgnoreCase("tinyint")) {
      return "byte";
    } else if (sqlType.equalsIgnoreCase("smallint")) {
      return "short";
    } else if (sqlType.equalsIgnoreCase("int")) {
      return "int";
    } else if (sqlType.equalsIgnoreCase("bigint unsigned")) {
      return "long";
    } else if (sqlType.equalsIgnoreCase("float")) {
      return "float";
    } else if (sqlType.equalsIgnoreCase("decimal") || sqlType.equalsIgnoreCase("numeric") || sqlType.equalsIgnoreCase("real")
        || sqlType.equalsIgnoreCase("money") || sqlType.equalsIgnoreCase("smallmoney")) {
      return "double";
    } else if (sqlType.equalsIgnoreCase("varchar") || sqlType.equalsIgnoreCase("char") || sqlType.equalsIgnoreCase("nvarchar")
        || sqlType.equalsIgnoreCase("nchar") || sqlType.equalsIgnoreCase("text")) {
      return "String";
    } else if (sqlType.equalsIgnoreCase("datetime")) {
      return "Date";
    } else if (sqlType.equalsIgnoreCase("image")) {
      return "Blod";
    }

    return null;
  }

  public static void main(String[] args) {

    // GenEntityTable entity = new GenEntityTable("com.mysql.jdbc.Driver",
    // "jdbc:mysql://localhost:3306/springwind?useUnicode=true&characterEncoding=utf-8", "root", "admini");
    GenEntityTable entity = new GenEntityTable();
    entity.init();
    entity.startMultitermTable();
    // entity.startTable();

  }
}

 

关于​​DatabaseMetaData的用法​​