IDEA中根据MySql表生成实体类, SQL文件

IDEA中根据MySql表生成实体类, SQL文件, 同时解决了groovy输出中文乱码的问题

1. 在IDEA中创建mysql连接 [参考]

2. 创建groovy脚本 [ 参考 ] 

　　(1) 选择要生成实例的表(支持多选)
　　(2) 右击已选中的表 > Scripted Extensions > Go To Scripts Directory
　　(3) 在左侧schema文件, 复制Generate POJOs.groovy并粘贴,改名为新脚本
　　(4) 编辑新脚本, 粘贴后面备注里面的groovy脚本, 保存
　　(5) 修改脚本以生成符合自己需求的文件格式

3. 使用新脚本, 生成类实例, sql文件  [ 参考 ] 

备注脚本:

import com.intellij.database.model.DasTable
import com.intellij.database.util.Case
import com.intellij.database.util.DasUtil

/*
 * Available context bindings:
 *   SELECTION   Iterable<DasObject>
 *   PROJECT     project
 *   FILES       files helper
 */

packageName = ""
typeMapping = [
        (~/(?i)bigint/)                   : "long",
        (~/(?i)int/)                      : "String",
        (~/(?i)float|double|decimal|real/): "String",
        (~/(?i)datetime|timestamp/)       : "String",
        (~/(?i)date/)                     : "String",
        (~/(?i)time/)                     : "String",
        (~/(?i)/)                         : "String"
]
tab = "    "
primaryKey = "id"

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

def generate(table, dir) {
    def className = javaName(table.getName(), true)
    def fields = calcFields(table)
    packageName = getPackageName(dir)
    primaryKey = findPrimaryKey(fields)

    printWriter(dir, className + ".java").withPrintWriter { out -> generateClass(out, className, table, fields) }
    printWriter(dir, "sqlMap" + className + ".xml").withPrintWriter { out -> generateSQL(out, className, table, fields) }
}

def printWriter(dir, fileName) {
    return new PrintWriter(new OutputStreamWriter(new FileOutputStream(new File(dir, fileName)), "utf-8"))
}

//系统默认一般都不是utf-8
def cn(text){
    return new String(text.getBytes(System.getProperty("file.encoding")),"utf-8");
}

//生成pojo类
def generateClass(out, className, table, fields) {
    out.println "/**"
    out.println " * ${cn("© xx有限公司")}"
    out.println " * ${cn("协议说明")}"
    out.println " */"
    out.println "package $packageName"
    out.println ""
    out.println ""
    out.println "/**"
    out.println " * ${table.getComment()}"
    out.println " * ${cn("业务详细说明")}"
    out.println " */"
    out.println "public class $className {"
    out.println ""
    fields.each() {
        if (it.annos != "") out.println "  ${it.annos}"
        // 输出注释
        if (it.commoent != null && it.commoent != "") {
            out.println "    /**"
            out.println "     * ${it.commoent.toString()}"
            out.println "     */"
        }
        out.println "    private ${it.type} ${it.name};"
    }
    //setter, getter
    out.println ""
    fields.each() {
        out.println ""
        out.println "${tab}public ${it.type} get${it.name.capitalize()}() {"
        out.println "${tab}${tab}return ${it.name};"
        out.println "${tab}}"
        out.println ""
        out.println "${tab}public void set${it.name.capitalize()}(${it.type} ${it.name}) {"
        out.println "${tab}${tab}this.${it.name} = ${it.name};"
        out.println "${tab}}"
        out.println ""
    }
    out.println ""
    out.println "}"
}

//生成sql
def generateSQL(out, className, table, fields) {
    out.println "<?xml version="1.0" encoding="UTF-8" ?>"
    out.println "<sqlMap namespace="${table.getName().toUpperCase()}">"
    out.println ""
    //INSERT
    out.println "${tab}<sql id="INSERT">"
    out.println "${tab}${tab}<![CDATA["
    out.println "${tab}${tab}${tab}INSERT INTO ${table.getName()}"
    out.println "${tab}${tab}${tab}("
    fields.each() {
        out.println "${tab}${tab}${tab}${tab}<#if ${it.javaName} ? exists > `${it.name}`${it == fields.last() ? "" : ","}</#if>"
    }
    out.println "${tab}${tab}${tab})"
    out.println "${tab}${tab}${tab}VALUES("
    fields.each() {
        out.println "${tab}${tab}${tab}${tab}<#if ${it.javaName} ? exists > :${it.javaName}</#if>"
    }
    out.println "${tab}${tab}${tab})"
    out.println "${tab}${tab}]]>"
    out.println "${tab}</sql>"
    out.println ""

    //UPDATE
    out.println "${tab}<sql id="UPDATE">"
    out.println "${tab}${tab}<![CDATA["
    out.println "${tab}${tab}${tab}UPDATE ${table.getName()}"
    out.println "${tab}${tab}${tab}SET"
    fields.each() {
        out.println "${tab}${tab}${tab}${tab}<#if ${it.javaName} ? exists > `${it.name}`=:${it.javaName}</#if>"
    }
    out.println "${tab}${tab}${tab}WHERE"
    out.println "${tab}${tab}${tab}${tab}${primaryKey}=:id"
    out.println "${tab}${tab}]]>"
    out.println "${tab}</sql>"
    out.println ""

    //QUERY
    out.println "${tab}<sql id="QUERY">"
    out.println "${tab}${tab}<![CDATA["
    out.println "${tab}${tab}${tab}SELECT"
    fields.each() {
        out.println "${tab}${tab}${tab}${tab}`${it.name}`${it == fields.last() ? "" : ","}"
    }
    out.println "${tab}${tab}${tab}FROM ${table.getName()}"
    out.println "${tab}${tab}${tab}WHERE 1=1"
    fields.each() {
        out.println "${tab}${tab}${tab}${tab}<#if ${it.javaName} ? exists > `${it.name}`=:${it.javaName}</#if>"
    }
    out.println "${tab}</sql>"
    out.println ""

    //
    out.println "</sqlMap>"
}

//用反射查看类信息
def printInfo(out, className, table, fields) {
    //DasUtil.isAutoGenerated(DasColumn)
    //DasUtil.isPrimary(DasColumn)
    //DasUtil.isIndexColumn(DasColumn)
    out.println ""
    out.println ""
    printMethods(out, table.class);
    out.println ""
    out.println ""

    out.println "primaryKey=${primaryKey}"
    out.println ""
    out.println ""
    printMethods(out, DasUtil.class);
    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
        fields += [[
                           name    : col.getName(),
                           javaName: javaName(col.getName(), false),
                           type    : typeStr,
                           commoent: col.getComment(),
                           annos   : "",
                           col     : col
                   ]]
    }
}

//生成javaName
def javaName(str, capitalize) {
    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 getPackageName(dir) {
    return dir.toString().replaceAll("\\", ".")
            .replaceAll("/", ".")
            .replaceAll("^.*src(\.main\.java\.)?", "") + ";"
}

//通过反射查看类方法, 也可以直接查看官网psi文件api
def printMethods(out, clazz) {
    clazz.declaredMethods.each() {
        out.println(it)
    }
}

//获取主键
def findPrimaryCol(fields) {
    def col
    fields.each() {
        if (DasUtil.isPrimary(it.col)) {
            col = it;
            return true;
        }
    }
    return col
}

//获取主键
def findPrimaryKey(fields) {
    def col = findPrimaryCol(fields)
    if (col != null) {
        return col.name
    }
    return "id"
}