[VBA] I tried to make a tool to convert the primitive type of Entity class generated by Hibernate Tools to the corresponding reference type.

Hibernate Tools that can instantly create the corresponding Entity class based on the table definition and VIEW definition. It's very convenient, but by default numeric types are created as primitive types. If null can be entered in the DB value, this is NG. I searched for a setting that can be converted to the corresponding reference type (null is acceptable unlike the primitive type), but it does not come out, If this is the case, it's faster to make it! So, I made a tool to realize 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 in Hibernate Tools

-Generate JPA Entity class using Hibernate Tools | Qiita

Since it is described in detail in, I will omit it in this article.

Data type correspondence table

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

Since BigInteger and BigDecimal are reference types, no conversion is required, so the corresponding reference types are not written. Even so, INTEGER is output as Integer and BIGINT is output as long, isn't it?

code

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

python

Sub macro_for_entity_class_java()

    Dim filePath As String 'For getting the file path
    Dim javaFile As String 'For Java file acquisition
    
    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")  'Settings for regular expression objects
    
    'Get the path of an Excel file
    filePath = ThisWorkbook.Path
    'Get the Java file in the same file as the Excel file
    javaFile = Dir(filePath & "\*.java")
    
    'Open Java files in order and execute processing
    Do While javaFile <> ""
    
        'Open a 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 classes for PK)
        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
        
        ' @"Insertable" in Join Column= false, updatable =Added "false"
        reg.Pattern = "(.+?)@JoinColumn\((.+?)\)"
        reg.IgnoreCase = False
        reg.Global = True
        replaceContent = reg.replace(replaceContent, "$1@JoinColumn($2, insertable = false, updatable = false)")
        
        'Delete the original file and output the 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 following Java file
        javaFile = Dir
        
    Loop
    
End Sub

Supplementary information

--Place the Java file to be converted in the same layer as the Excel file in which the macro is described. --In the conversion process, you may be wondering if only int → Integer, long → Long should be used, but in order to prevent it, it will be converted when a variable name such as introduction or longitude appears. It is written as above. --JSON type needs to be converted to Java object after getting as String type. You can convert using one of the following libraries!
① Jackson: [Reference] Jackson usage memo | 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 and corrected some contents --2020/06/19: Added JSON → conversion method to Java object, deleted future issues --2020/07/05: Added the process corresponding to JPA's "Repeated column in mapping for entity" error, added GitHub repository

reference

■VBA -Get the path of the workbook | Excel VBA @Workshop -Unknown Dir Function Features | moug Moog -VBA: Sample program collection for processing multiple files in a specific folder using the Dir function | SE Life Log --VBA and other IT memorandums- -293.html) -"Do While FileName <>" About Macros "(hiro) | Excel School -[Reading and writing text files with VBA | Streamline Excel work 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 /) -Manipulate (open) text file | Office TANAKA -How to replace character strings in text files | VB Beginners' Association --Q & A Bulletin Board Past Log -Use regular expressions in VBA (RegExp object) | ExcelWork.info -Use regular expressions in VBA (RegExp object properties) | ExcelWork.info -I want to change only a part of the character string | Ready-to-use regular expression sample collection

■MySQL

• 11.1 Numeric Data Types(version5.7) | MySQL

■Java -Common sense of how to handle numbers in programs | atmarkIT -JPA "Repeated column in mapping for entity" error | Qiita