[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

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.)

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, 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

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

Recommended Posts

[VBA] I tried to make a tool to convert the primitive type of Entity class generated by Hibernate Tools to the corresponding reference type.
[Java] I tried to make a maze by the digging method ♪
I tried to make a parent class of a value object in Ruby
I tried to make a product price comparison tool of Amazon around the world with Java, Amazon Product Advertising API, Currency API (2017/01/29)
I tried to make the sample application into a microservice according to the idea of the book "Microservice Architecture".
I tried to convert a string to a LocalDate type in Java
I tried to make a client of RESAS-API in Java
I tried to make it possible to set the delay for the UDP client of Android by myself
I tried to express the result of before and after of Date class with a number line
I made a tool to output the difference of CSV file
I tried to make a sample program using the problem of database specialist in Domain Driven Design
I tried to make full use of the CPU core in Ruby
From Java9, the constructor of the class corresponding to primitive types is deprecated.
How to make the schema of the URL generated by Rails URL helper https
A story when I tried to make a video by linking Processing and Resolume
What I tried when I wanted to get all the fields of a bean
I tried to automatically generate a class to convert from a data class to a Bundle with APT
I was swallowed by the darkness of the romaji, trying to convert my name to romaji
[Small story] I tried to make the java ArrayList a little more convenient
I want to recursively get the superclass and interface of a certain class
Even if I want to convert the contents of a data object to JSON in Java, there is a circular reference ...
Make a margin to the left of the TextField
I tried to develop a man-hour management tool
I tried to create a log reproduction script at the time of apt install
I tried to make a message function of Rails Tutorial extension (Part 1): Create a model
I tried to investigate the mechanism of Emscripten by using it with the Sudoku solver
I tried to operate home appliances by holding a smartphone over the NFC tag
I want to call a method of another class
I tried to decorate the simple calendar a little
05. I tried to stub the source of Spring Boot
I tried to reduce the capacity of Spring Boot
I tried to make a login function in Java
I want to add a reference type column later
I tried to make a reply function of Rails Tutorial extension (Part 3): Corrected a misunderstanding of specifications
[Java] I tried to make a rock-paper-scissors game that beginners can run on the console.
I tried to make a message function of Rails Tutorial extension (Part 2): Create a screen to display
I tried a puzzle that can only be solved by the bottom 10% of bad engineers