Using idea to connect database to generate entity class and customize groovy script file

Time:2020-1-17

Introduction: now 2019, there are many plug-ins or editors that support automatic generation of data entity classes based on data tables, such as idea, various mybatis code generation tools, etc. This article introduces how to use idea’s groovy script file to generate entity classes with JPA annotations

Reference link: IntelliJ idea detailed steps to generate annotated entity class through database table – sad autumn wind

1、 Using idea to connect to database

  • Attention should be paid to:

    1. Only the official version of idea has this function. The community version has
    2. The version of idea used in the demo image is 2018.3, and the interface of different versions may be slightly different
  1. Create a new data connection

    • ① If not, you can open the menu bar above: View – tool window – Database
    • The same is true for creating a new Oracle Database

Using idea to connect database to generate entity class and customize groovy script file

  1. Configure data connection

    1. Fill in a connection name, whatever you want
    2. No choice, default
    3. Fill in the IP address of the database connection, for example, the local database can be filled in: localhost or 127.0.0.1
    4. Fill in the open port number of the database. If it is not set, the default is 3306
    5. Fill in the name of the database you need to connect to
    6. Fill in the user name of the database
    7. Fill in database password
    8. There will be a driver that needs to be downloaded. In the figure, it has been downloaded
    9. Fill in your own database connection URL, and then click the 9 button to test the connection. If the local connection fails, check whether the MySQL service is enabled

Using idea to connect database to generate entity class and customize groovy script file

2、 Two methods of creating entity classes in idea

(1) , if you just create a simple entity class with only properties, getters and setters, you don’t need JPA / Hibernate entity class annotation

  1. Click to open the data connection you just created
  2. Open the schemas and find the table that you need to create entity class (you can use Ctrl or shift to select multiple)
  3. Right click: scripted extensions – generate pojos.groovy
  4. Select the generation path and it will be OK
  • Attention should be paid to

    • There may be a problem with the generated package name. The default ispackage com.sample, need to modify manually

Using idea to connect database to generate entity class and customize groovy script file

(2) The hibernate / JPA framework is used, the annotated entity class is needed, and the corresponding method is provided by idea

  • Add JPA module

    1. Open project structure
    2. Open the module and click + to add the module
    3. Select JPA
    4. Click OK in the lower right corner to close the project structure

Using idea to connect database to generate entity class and customize groovy script file

  • Generate annotated entity class using persistence tool-1

    1. If the previous step is OK, the persistence toolbar will appear in the sidebar at the bottom left corner of idea
    2. Open the persistence toolbar, right-click the project: generate persistence mapping – by database schema

Using idea to connect database to generate entity class and customize groovy script file

  • Generate annotated entity class using persistence tool-2
  1. Select database connection
  2. Select the package of the generated class
  3. Select tables to build
  4. Select the fields to generate (all fields except foreign keys are selected by default after selecting the table)
  5. Modify the property name to be generated
  6. Modify the property type to be generated
  7. Check to generate annotated entity class

Using idea to connect database to generate entity class and customize groovy script file

The generated class instance is as follows

Using idea to connect database to generate entity class and customize groovy script file

3、 Further, use your own groovy to generate annotated entity classes

Two methods of generating entity classes using idea are introduced above, each with its own advantages and disadvantages
It is simple and efficient to generate entity classes without annotations, but the disadvantage is that it is not suitable if you use the JPA / Hibernate framework
Generating annotated entity class can generate annotated entity class, but the disadvantage is that the generated annotation of entity class may not conform to our annotation. It can only be generated under the current project package. Faced with some complex databases such as Oracle, many system tables will be displayed and table space switching is not convenient
We know that idea generates entity classes without annotation through groovy script files, and we can find this file. Can we modify this file to generate entity classes with annotation?

(1) , the benefits of using your own groovy to generate annotated entity classes

  1. In the database toolbar, the operation is simple and convenient
  2. You can edit the script yourself to make the generated entity class meet your customization requirements
  3. When generating entity classes for a database such as Oracle, you can avoid the impact of system tables and select your own table space for operation
  4. Can be generated anywhere on the computer, unlimited

(2) , using your own groovy to generate annotated entity classes

  1. @Author author name needs to modify the script by yourself
  2. @The table space name of table (schema =) cannot be read. You need to modify the script yourself and set the table space name (MySQL can directly delete the definition of this attribute) (idea’s persistence tool can read (schema =) attribute and generate it, but we don’t know how to get it)
  3. @The primary key generation method of ID is unknown and needs to be determined by yourself after generation (MySQL users can modify the script directly to set the primary key generation method, but Oracle users need to generate entity classes and then confirm one by one)

(3) , where to use your own groovy to generate annotated entity classes can continue to improve

1. If you need the hashcode () and equals () methods, modify the script to generate it automatically

(4) , create a new groovy script file

  1. In the database toolbar, right-click: scripted extensions – go to scripts directory
  2. Create a new generate mypojos.groovy in the directory

Using idea to connect database to generate entity class and customize groovy script file

  1. Add content inside

    1. //1. Change idea to your nameWhere you can change the author’s name to your own
    2. //2. Schema = \ "add your own tablespace name after it (MySQL can not be added, and it does not need this schema attribute)Here you can modify it according to the prompts
import com.intellij.database.model.DasTable
import com.intellij.database.model.ObjectKind
import com.intellij.database.util.Case
import com.intellij.database.util.DasUtil

import java.text.SimpleDateFormat

/*
 * Available context bindings:
 *   SELECTION   Iterable<DasObject>
 *   PROJECT     project
 *   FILES       files helper
 */
packageName = ""
typeMapping = [
        (~/(?i)tinyint|smallint|mediumint/)      : "Integer",
        (~/(?i)int/)                             : "Long",
        (~/(?i)bool|bit/)                        : "Boolean",
        (~/(?i)float|double|decimal|real/)       : "Double",
        (~/(?i)datetime|timestamp|date|time/)    : "Date",
        (~/(?i)blob|binary|bfile|clob|raw|image/): "InputStream",
        (~/(?i)/)                                : "String"
]


FILES.chooseDirectoryAndSave("Choose directory", "Choose where to store generated files") { dir ->
    SELECTION.filter { it instanceof DasTable && it.getKind() == ObjectKind.TABLE }.each { generate(it, dir) }
}

def generate(table, dir) {
    def className = javaName(table.getName(), true)
    def fields = calcFields(table)
    packageName = getPackageName(dir)
    PrintWriter printWriter = new PrintWriter(new OutputStreamWriter(new FileOutputStream(new File(dir, className + ".java")), "UTF-8"))
    printWriter.withPrintWriter { out -> generate(out, className, fields, table) }

//    new File(dir, className + ".java").withPrintWriter { out -> generate(out, className, fields,table) }
}

//Get package folder path
def getPackageName(dir) {
    return dir.toString().replaceAll("\\", ".").replaceAll("/", ".").replaceAll("^.*src(\.main\.java\.)?", "") + ";"
}

def generate(out, className, fields, table) {
    out.println "package $packageName"
    out.println ""
    out.println "import javax.persistence.Column;"
    out.println "import javax.persistence.Entity;"
    out.println "import javax.persistence.Table;"
    out.println "import javax.persistence.Id;"
    out.println "import javax.persistence.GeneratedValue;"
    out.println "import java.io.Serializable;"
    Set types = new HashSet()

    fields.each() {
        types.add(it.type)
    }

    if (types.contains("Date")) {
        out.println "import java.util.Date;"
    }

    if (types.contains("InputStream")) {
        out.println "import java.io.InputStream;"
    }
    out.println ""
    out.println "/**\n" +
            " * @Description  \n" +
            "* @ author idea \ n" + // 1. Change idea to your name
            " * @Date " + new SimpleDateFormat("yyyy-MM-dd").format(new Date()) + " \n" +
            " */"
    out.println ""
    out.println "@Entity"
    Out. Println "@ table (name = \" "+ table. Getname() +" \ ", schema =" \ ")" // 2. Add your own tablespace name after the schema = \ "(MySQL can not be added, and it does not need this schema attribute)
    out.println "public class $className  implements Serializable {"
    out.println ""
    out.println genSerialID()
    fields.each() {
        out.println ""
        //Output comments
        if (isNotEmpty(it.commoent)) {
            out.println "\t/**"
            out.println "\t * ${it.commoent.toString()}"
            out.println "\t */"
        }

        if ((it.annos+"").indexOf("[@Id]") >= 0) out.println "\[email protected]"

        if (it.annos != "") out.println "   ${it.annos.replace("[@Id]", "")}"


        //Output member variable
        out.println "\tprivate ${it.type} ${it.name};"
    }

    //Output get / set method
    fields.each() {
        out.println ""
        out.println "\tpublic ${it.type} get${it.name.capitalize()}() {"
        out.println "\t\treturn this.${it.name};"
        out.println "\t}"
        out.println ""

        out.println "\tpublic void set${it.name.capitalize()}(${it.type} ${it.name}) {"
        out.println "\t\tthis.${it.name} = ${it.name};"
        out.println "\t}"
    }

    //Output toString method
    out.println ""
    out.println "\[email protected]"
    out.println "\tpublic String toString() {"
    out.println "\t\treturn \"TpApiConfig{\" +"
    fields.each() {
        out.println "\t\t\t\t\"${it.name}='\" + ${it.name} + '\'' +"
    }
    out.println "\t\t\t\t'}';"
    out.println "\t}"

    out.println ""
    out.println "}"
}

def calcFields(table) {
    DasUtil.getColumns(table).reduce([]) { fields, col ->
        def spec = Case.LOWER.apply(col.getDataType().getSpecification())

        def typeStr = typeMapping.find { p, t -> p.matcher(spec).find() }.value
        def comm = [
                colName : col.getName(),
                name    : javaName(col.getName(), false),
                type    : typeStr,
                commoent: col.getComment(),
                annos   : "\[email protected](name = \"" + col.getName() + "\" )"]
        if ("id".equals(Case.LOWER.apply(col.getName())))
            comm.annos += ["@Id"]
        fields += [comm]
    }
}

//It has been modified to use javaname. If necessary, it can be modified to javaclassname in def classname = javaname (table. Getname(), true)
//Process the class name (because my tables are all named with T, so we need to remove the t at the beginning of generating the class name,
//If you don't need it, please find the place where javaclassname is used and change it to javaname.)
def javaClassName(str, capitalize) {
    def s = com.intellij.psi.codeStyle.NameUtil.splitNameIntoWords(str)
            .collect { Case.LOWER.apply(it).capitalize() }
            .join("")
            .replaceAll(/[^\p{javaJavaIdentifierPart}[_]]/, "_")
    //Remove the beginning t http://developer.51cto.com/art/200906/129168.htm
    s = s[1..s.size() - 1]
    capitalize || s.length() == 1 ? s : Case.LOWER.apply(s[0]) + s[1..-1]
}

def javaName(str, capitalize) {
//    def s = str.split(/(?<=[^\p{IsLetter}])/).collect { Case.LOWER.apply(it).capitalize() }
//            .join("").replaceAll(/[^\p{javaJavaIdentifierPart}]/, "_")
//    capitalize || s.length() == 1? s : Case.LOWER.apply(s[0]) + s[1..-1]
    def s = com.intellij.psi.codeStyle.NameUtil.splitNameIntoWords(str)
            .collect { Case.LOWER.apply(it).capitalize() }
            .join("")
            .replaceAll(/[^\p{javaJavaIdentifierPart}[_]]/, "_")
    capitalize || s.length() == 1 ? s : Case.LOWER.apply(s[0]) + s[1..-1]
}

def isNotEmpty(content) {
    return content != null && content.toString().trim().length() > 0
}

static String changeStyle(String str, boolean toCamel) {
    if (!str || str.size() <= 1)
        return str

    if (toCamel) {
        String r = str.toLowerCase().split('_').collect { cc -> Case.LOWER.apply(cc).capitalize() }.join('')
        return r[0].toLowerCase() + r[1..-1]
    } else {
        str = str[0].toLowerCase() + str[1..-1]
        return str.collect { cc -> ((char) cc).isUpperCase() ? '_' + cc.toLowerCase() : cc }.join('')
    }
}

static String genSerialID() {
    return "\tprivate static final long serialVersionUID =  " + Math.abs(new Random().nextLong()) + "L;"
}
  1. Right click on the table and select the script you write to generate entity class

The effect is as follows:

Using idea to connect database to generate entity class and customize groovy script file