springBoot生成SQL文件-使用Hibernate5的SchemaExport實現01

  • 2020 年 1 月 21 日
  • 筆記

上一篇我們說了使用jpa配置屬性直接生成SQL全量腳本文件的方式，想重新了解可以看：springBoot+jpa根據實體類註解生成SQL文件。 這一篇是根據Hibernate的SchemaExport實現程式建表，具體的方案可以是寫在main函數中直接執行，也可以注入在springBoot中，在項目啟動時自動完成。這裡首先介紹第一種。

本系列環境基於 springBoot1.5.8.RELEASE+jpa+Hibernate5.0+java8

最開始就在想既然可以通過配置spring.jpa.hibernate.ddl-auto=update實現自動創建和更新資料庫的表結構，就應該有辦法通過程式創建全量SQL和增量SQL吧，通過搜索，找到了蛛絲馬跡:

  • 在Hibernate4.x中可直接使用：
Configuration cfg = new Configuration().configure();  SchemaExport se = new SchemaExport(cfg);  se.create(true,true);
  • 在Hibernate5.x中SchemExport(Configuration)已經失效，就需要使用：
ServiceRegistry serviceRegistry = new StandardServiceRegistryBuilder().configure().build();   MetadataImplementor metadata = (MetadataImplementor) new MetadataSources( serviceRegistry ).buildMetadata();  SchemaExport schemaExport =  new SchemaExport(metadata);  schemaExport.create(true, true);

但是這些默認方式都需要hibernate.cfg.xml文件，對於本系列中本身使用註解的項目而言則無法直接使用。

漸進

在之後的尋找中，發現可以手動配置的這些屬性：

Map<String, Object> settings = new HashMap<>();  settings.put("hibernate.dialect", DIALECT_CLASS);  settings.put("hibernate.physical_naming_strategy",  "org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy");  settings.put("hibernate.implicit_naming_strategy", "org.springframework.boot.orm.jpa.hibernate.SpringImplicitNamingStrategy");  settings.put("hibernate.id.new_generator_mappings", false);    StandardServiceRegistry standardServiceRegistry = new StandardServiceRegistryBuilder().applySettings(settings)          .build();  MetadataSources metadata = new MetadataSources(standardServiceRegistry);  MetadataImplementor metadataImplementor = (MetadataImplementor) metadata.getMetadataBuilder().build();  SchemaExport schemaExport = new SchemaExport(metadataImplementor);  String outputFile = getOutputFilename(args);  schemaExport.setOutputFile(outputFile);  schemaExport.setDelimiter(";");  schemaExport.create(true, false);

但是這個目前尚無法根據entity中註解文件生成腳本，畢竟還沒設置掃描的路徑。現在通過反射過濾獲取所需要的class列表：

String pattern = getPattern(args);  List<Class<?>> classes = getClassesByAnnotation(Entity.class, pattern);  classes.forEach(metadata::addAnnotatedClass);

涉及到的相關方法如下：

/**  * 根據運行mian函數時的輸入路徑參數獲取掃描路徑，  * 無輸出時使用默認路徑PATTERN  * @param args  */  private static String getPattern(String[] args) {      String pattern = PATTERN;      if(args != null && args.length >= 3              && StringUtils.hasText(args[2])) {          pattern = args[2];      }      return pattern;  }  /**  * 使用流過濾獲取所需類的list  */  private static List<Class<?>> getClassesByAnnotation(Class<? extends Annotation> annotation, String pattern) {      return getResources(pattern).stream()              .map(r -> metadataReader(r))              .filter(Objects::nonNull)              .filter(mr -> mr.getAnnotationMetadata().hasAnnotation(annotation.getName()))              .map(mr -> entityClass(mr))              .filter(Objects::nonNull)              .collect(Collectors.toList());  }    /**      * 獲取與模式對應的資源資訊。      * @param pattern      * @return      */  private static List<Resource> getResources(String pattern) {      PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();      Resource[] resources;      try {          resources = resolver.getResources(pattern);      } catch (IOException e) {          throw new RuntimeException(e);      }      return Arrays.asList(resources);  }      private static Class<?> entityClass(MetadataReader mr) {      String className = mr.getClassMetadata().getClassName();      Class<?> clazz;      try {          clazz = Class.forName(className);      } catch (ClassNotFoundException e) {          System.err.printf("%s Class not found", className);          return null;      }      return clazz;  }    private static MetadataReader metadataReader(Resource r) {      MetadataReader mr;      try {          mr = new SimpleMetadataReaderFactory().getMetadataReader(r);      } catch (IOException e) {          System.err.printf(e.getMessage());          return null;      }      return mr;  }  /**  *根據輸入生成輸出路徑，無輸入時使用默認路徑SCHEMA_SQL  */  private static String getOutputFilename(String[] args) {      SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd_HHmmss");      String currentDate = sdf.format(Calendar.getInstance().getTime());      if(args != null && args.length > 0              && StringUtils.hasText(args[0])) {          String customSchemaName = args[0];          if(customSchemaName.contains("%s")) {              return String.format(customSchemaName, currentDate);          }          return customSchemaName;      }      return String.format(SCHEMA_SQL, currentDate);  }

該方法運行期間根據掃描的路徑可能會報一些類未找到等錯誤，但不會影響腳本的生成，如：

java.lang.ClassNotFoundException: org.springframework.hateoas.config.EnableHypermediaSupport$HypermediaType     [main] DEBUG org.springframework.core.type.classreading.AnnotationAttributesReadingVisitor - Failed to classload enum type while reading annotation metadata  java.lang.ClassNotFoundException: org.jboss.logging.annotations.Message$Format    DEBUG org.springframework.core.type.classreading.AnnotationAttributesReadingVisitor - Failed to classload enum type while reading annotation metadata  java.lang.ClassNotFoundException: org.jboss.logging.annotations.Message$Format

以上錯誤是在使用默認掃描路徑PATTERN = "classpath*:**/*.class";的情況下可能出現的，至於原因是在getClassesByAnnotation過程中掃描了項目中的jar及其類後未能匹配到一些類等。這也反映出了掃描範圍的大小在於掃描路徑的選取。

完整程式碼

package com.windcoder.qycms.core.basis.test.Hibernate.ddl;    import org.hibernate.boot.MetadataSources;  import org.hibernate.boot.registry.StandardServiceRegistry;  import org.hibernate.boot.registry.StandardServiceRegistryBuilder;  import org.hibernate.boot.spi.MetadataImplementor;  import org.hibernate.dialect.Dialect;  import org.hibernate.dialect.MySQL5InnoDBDialect;  import org.hibernate.tool.hbm2ddl.SchemaExport;  import org.springframework.core.io.Resource;  import org.springframework.core.io.support.PathMatchingResourcePatternResolver;  import org.springframework.core.type.classreading.MetadataReader;  import org.springframework.core.type.classreading.SimpleMetadataReaderFactory;  import org.springframework.util.FileCopyUtils;  import org.springframework.util.StreamUtils;  import org.springframework.util.StringUtils;      import javax.persistence.Entity;  import java.io.File;  import java.io.FileInputStream;  import java.io.IOException;  import java.lang.annotation.Annotation;  import java.nio.charset.Charset;  import java.text.SimpleDateFormat;  import java.util.*;  import java.util.stream.Collectors;    /**   * 可以使用JPA Entity類生成DDL查詢的類   *  windcoder.com   * 生成成功，但DIALECT_CLASS獲取不友好。   * 參考：https://gist.github.com/sbcoba/e4264f4b4217746767e682c61f9dc3a6   */  public class JpaEntityDdlExport {      /**       * 要創建的文件名       */      private static final String SCHEMA_SQL = "schema_%s.sql";        /**       * 域類路徑位置（如果範圍很寬，則只能找到帶有@Entity的類）       */      private final static String PATTERN = "classpath*:**/*.class";            /**       * 定義DB的DDL       * org.hibernate.dialect.* 包參考*       *       * - Oracle  Oracle10gDialect.class       * - H2 H2Dialect.class       * ...       *       */      private final static Class<? extends Dialect> DIALECT_CLASS = MySQL5InnoDBDialect.class;        public static void main(String[] args) {          createData(args);      }          /**       * 生成全量SQL腳本       * @param args       */      public static void createData(String[] args){          Map<String, Object> settings = new HashMap<>();          settings.put("hibernate.dialect", DIALECT_CLASS);          settings.put("hibernate.physical_naming_strategy","org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy");          settings.put("hibernate.implicit_naming_strategy","org.springframework.boot.orm.jpa.hibernate.SpringImplicitNamingStrategy");          settings.put("hibernate.id.new_generator_mappings", false);            StandardServiceRegistry standardServiceRegistry = new StandardServiceRegistryBuilder()                  .applySettings(settings)                  .build();            MetadataSources metadata = new MetadataSources(standardServiceRegistry);          String pattern = getPattern(args);          List<Class<?>> classes = getClassesByAnnotation(Entity.class, pattern);          classes.forEach(metadata::addAnnotatedClass);          MetadataImplementor metadataImplementor = (MetadataImplementor) metadata.getMetadataBuilder().build();          SchemaExport schemaExport = new SchemaExport(metadataImplementor);          String outputFile = getOutputFilename(args);          schemaExport.setOutputFile(outputFile);          schemaExport.setDelimiter(";");          schemaExport.create(true, false);      }          private static String getPattern(String[] args) {          String pattern = PATTERN;          if(args != null && args.length >= 3                  && StringUtils.hasText(args[2])) {              pattern = args[2];          }          return pattern;      }        private static void appendMetaData(String outputFile, Map<String, Object> settings) {          String charsetName = "UTF-8";          File ddlFile = new File(outputFile);          try {              StringBuilder sb = new StringBuilder();              sb.append("/* Generate Environmentn");              for (Map.Entry<String, Object> entry : settings.entrySet()) {                  sb.append(entry.getKey().toString() + ": " + entry.getValue() + "n");              }              sb.append("*/n");              String ddlFileContents = StreamUtils.copyToString(new FileInputStream(ddlFile), Charset.forName(charsetName));              sb.append(ddlFileContents);              FileCopyUtils.copy(sb.toString().getBytes(charsetName), ddlFile);          } catch (IOException e) {              e.printStackTrace();          }      }          private static List<Class<?>> getClassesByAnnotation(Class<? extends Annotation> annotation, String pattern) {          return getResources(pattern).stream()                  .map(r -> metadataReader(r))                  .filter(Objects::nonNull)                  .filter(mr -> mr.getAnnotationMetadata().hasAnnotation(annotation.getName()))                  .map(mr -> entityClass(mr))                  .filter(Objects::nonNull)                  .collect(Collectors.toList());      }        /**       * 獲取與模式對應的資源資訊。       * @param pattern       * @return       */      private static List<Resource> getResources(String pattern) {          PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();          Resource[] resources;          try {              resources = resolver.getResources(pattern);          } catch (IOException e) {              throw new RuntimeException(e);          }          return Arrays.asList(resources);      }          private static Class<?> entityClass(MetadataReader mr) {          String className = mr.getClassMetadata().getClassName();          Class<?> clazz;          try {              clazz = Class.forName(className);          } catch (ClassNotFoundException e) {              System.err.printf("%s Class not found", className);              return null;          }          return clazz;      }        private static MetadataReader metadataReader(Resource r) {          MetadataReader mr;          try {              mr = new SimpleMetadataReaderFactory().getMetadataReader(r);          } catch (IOException e) {              System.err.printf(e.getMessage());              return null;          }          return mr;      }        private static String getOutputFilename(String[] args) {          SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd_HHmmss");          String currentDate = sdf.format(Calendar.getInstance().getTime());          if(args != null && args.length > 0                  && StringUtils.hasText(args[0])) {              String customSchemaName = args[0];              if(customSchemaName.contains("%s")) {                  return String.format(customSchemaName, currentDate);              }              return customSchemaName;          }          return String.format(SCHEMA_SQL, currentDate);      }  }

VirMach 便宜 VPS

QNews

QNews