[Java] [VBA] I tried to make a tool that converts the primitive type of the Entity class generated by Hibernate Tools into the corresponding reference type

4 minute read

Hibernate Tools that can instantly create the corresponding Entity class based on table definition and VIEW definition. It’s very convenient, but by default numeric types are created as primitive types. If the DB value can be null, this is NG. I can’t find any settings that can be converted to the corresponding reference type (nulls are allowed, unlike primitive types). In this case it is faster to make it! So, I made a tool to achieve the above.

Environment

  • OS: Windows10
  • VBA: 7.1
  • IDE: Eclipse2020-03
  • Java: 8
  • MySQL: 5.7
  • Hibernate ORM: 5.4
  • Spring Boot: 2.3.1

Creating Entity class

How to create Entity class with Hibernate Tools

  • [Generate JPA Entity class using Hibernate Tools Qiita](https://qiita.com/[email protected]/items/cb7c40cea2ff3225d5e3)

Since it is described in detail in, it is omitted in this article.

Data type correspondence table

The data types of MySQL (hereinafter, MySQL), Java data types output by Hibernate Tools (hereinafter, Hibernate Tools), and corresponding reference types are as follows. (The output is the result of actually running Hibernate Tools.)

MySQL HibernateTools(Java) Corresponding reference type(Java)
INTEGER int Integer
INT int Integer
TINYINT byte Byte
SMALLINT short Short
MEDIUMINT int Integer
BIGINT BigInteger -
DECIMAL BigDecimal -
NUMERIC BigDecimal -
FLOAT float Float
DOUBLE double Double
BIT byte Byte

Since BigInteger and BigDecimal are reference types, there is no need to convert them, so the corresponding reference types are not written. Even so, INTEGER is not output as Integer and BIGINT is output as long.

code

Based on the above, I created the VBA code. (There are some conversions other than numeric type.)

``


Sub macro_for_entity_class_java()

    Dim filePath As String'for getting file path
    Dim javaFile As String'For getting Java file
    
    Dim FSO As Object'for file processing
    
    Dim replaceContent As String'for string conversion
    
    Dim reg As Object'for regular expression objects (used to replace @JoinColumn)
    Set reg = CreateObject("VBScript.RegExp")'Set for regular expression object
    
    'Get the Excel file path
    filePath = ThisWorkbook.Path
    'Get the Java file in the same file as the Excel file
    javaFile = Dir(filePath & "\*.java")
    
    'Open Java files in sequence and execute processing
    Do While javaFile <> ""
    
        'Open the Java file and read the contents
        Set FSO = CreateObject("Scripting.FileSystemObject")
        With FSO.GetFile(filePath & "\" & javaFile).OpenAsTextStream
            replaceContent = .ReadAll
            .Close
        End With
        
        'Boolean → Boolean
        replaceContent = replace(replaceContent, "private boolean", "private Boolean")
        replaceContent = replace(replaceContent, "public boolean get", "public Boolean get")
        replaceContent = replace(replaceContent, "(boolean", "(Boolean")
        
        'byte → Byte
        replaceContent = replace(replaceContent, "private byte", "private Byte")
        replaceContent = replace(replaceContent, "public byte get", "public Byte get")
        replaceContent = replace(replaceContent, "(byte", "(Byte")
        
        'short → Short
        replaceContent = replace(replaceContent, "private short", "private Short")
        replaceContent = replace(replaceContent, "public short get", "public Short get")
        replaceContent = replace(replaceContent, "(short", "(Short")
        
        'int → Integer
        replaceContent = replace(replaceContent, "private int", "private Integer")
        replaceContent = replace(replaceContent, "public int get", "public Integer get")
        replaceContent = replace(replaceContent, "(int", "(Integer")
        
        'long → Long
        replaceContent = replace(replaceContent, "private long", "private Long")
        replaceContent = replace(replaceContent, "public long get", "public Long get")
        replaceContent = replace(replaceContent, "(long", "(Long")
        
        'float → Float
        replaceContent = replace(replaceContent, "private float", "private Float")
        replaceContent = replace(replaceContent, "public float get", "public Float get")
        replaceContent = replace(replaceContent, "(float", "(Float")
        
        'double → Double
        replaceContent = replace(replaceContent, "private double", "private Double")
        replaceContent = replace(replaceContent, "public double get", "public Double get")
        replaceContent = replace(replaceContent, "(double", "(Double")
        
        'Object → String (against JSON type; excluding PK classes)
        If InStr(javaFile, "PK.java") = 0 Then
            replaceContent = replace(replaceContent, "private Object", "private String")
            replaceContent = replace(replaceContent, "public Object get", "public String get")
            replaceContent = replace(replaceContent, "(Object", "(String")
        End If
        
        'Add "insertable = false, updatable = false" to @JoinColumn
        reg.Pattern = "(.+?)@JoinColumn\((.+?)\)"
        reg.IgnoreCase = False
        reg.Global = True
        replaceContent = reg.replace(replaceContent, "[email protected]($2, insertable = false, updatable = false)")
        
        'Delete the original file and output a file that describes the contents after replacement
        FSO.GetFile(filePath & "\" & javaFile).Delete
        FSO.CreateTextFile (filePath & "\" & javaFile)
        With FSO.GetFile(filePath & "\" & javaFile).OpenAsTextStream(8)
            .Write replaceContent
            .Close
        End With
        
        'Get the next Java file
        javaFile = Dir
        
    Loop
    
End Sub

Supplement

  • Place the Java file to be converted in the same layer as the Excel file that describes the macro.-In the conversion process, you may think that it is only necessary to use only int → Integer, long → Long, but it will be converted when variable names such as introduction and longitude appear, so to prevent it It is written as above.
  • It is necessary to obtain JSON type as String type and then convert it to Java object. Convert using one of the following libraries!
    ① Jackson: [Reference] Notes on how to use Jackson | Qiita
    ②JSONIC: [Reference] [Java] JSON basics and how to convert JSON using JSONIC | TASK NOTES
  • This tool has been released on GitHub. Please use it if you like!

Change log

  • 2020/06/17: Added data type correspondence table, modified some contents
  • 2020/06/19: JSON → Added conversion method to Java object, deleted future issues
  • 2020/07/05: Added processing for JPA ``Repeated column in mapping for entity’’ error, added GitHub repository

Reference

■VBA

  • [Get workbook path Excel VBA @Workshop](http://excel.style-mods.net/tips_vba/tips_vba_4_09.htm)
  • [Unknown feature of Dir function moug mog](https://www.moug.net/tech/exvba/0100048.html)
  • [A collection of sample programs related to the processing of multiple files in a specific folder using the VBA:Dir function SE Life Log-Other IT memorandum centering on VBA -](https://selifelog.com/blog-entry-293.html)
  • “Do While FileName <> “” About Macros ExcelSchool](http://www.excel.studio-kazu.jp/kw/20171207164726.html)
  • [Read and write text files with VBA Make Excel work more efficient with VBA](https://vbabeginner.net/vba%E3%81%A7%E3%83%86%E3%82%AD%E3%82%B9%E3%83%88%E3%83%95%E3%82%A1%E3%82%A4%E3%83%AB%E3%81%AE%E8%AA%AD%E3%81%BF%E6%9B%B8%E3%81%8D%E3%82%92%E8%A1%8C%E3%81%86/)
  • [Operate (Open) text file Office TANAKA](http://officetanaka.net/excel/vba/file/file08.htm)
  • [Text file replacement method VB Beginner’s Meeting-Q&A bulletin board past log](http://www.gizcollabo.jp/vbtomo/log/archive/vbqanda_40587_0.html)
  • [Use regular expressions in VBA (RegExp object) ExcelWork.info](https://excelwork.info/excel/vbaregexp/)
  • [Use regular expression in VBA (RegExp object property) ExcelWork.info](https://excelwork.info/excel/vbaregexpproperty/)
  • [I want to change only part of the string Regular expression sample collection that can be used immediately](https://hodade.com/seiki/page.php?r_ichibu)

■MySQL

  • [11.1 Numeric Data Types(version5.7) MySQL](https://dev.mysql.com/doc/refman/5.7/en/numeric-types.html)

■ Java

  • [Common sense of how numbers are handled in programs atmarkIT](https://www.atmarkit.co.jp/ait/articles/0702/27/news100.html)
  • [JPA “Repeated column in mapping for entity” error Qiita](https://qiita.com/pilot/items/2bb3b4457ac4ea307605)