idea生成mysql脚本_Java IDEA根据database以及脚本代码自动生成DO,DAO,SqlMapper文件（一）...

最新推荐文章于 2025-05-13 15:57:07 发布

原创最新推荐文章于 2025-05-13 15:57:07 发布 · 1k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#idea生成mysql脚本

本文介绍了如何在Java IDEA中利用Groovy脚本自动生成DO, DAO, SqlMapper文件。首先，配置数据库连接，然后创建Groovy脚本文件分别用于生成DO, DAO, SqlMapper。脚本支持自定义表名前缀，获取表和字段注释，以及主键识别。脚本生成的DO文件需先于DAO和SqlMapper文件生成，以确保依赖关系正确。文章还提醒注意项目路径不能包含中文。" 111276277,10294562,MyBatis多表操作与动态SQL详解,"['MyBatis', 'Java开发', '数据库操作', '事务管理', '缓存技术']

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

根据数据库代码自动生成的插件挺多的，这里主要分享两种：

1.根据database以及脚本代码自动生成

2.根据mybatis-generator-core自动生成(下一章节进行分享，包含sqlserver的注释获取)

这篇主要介绍的是database以及groovy脚本代码自动生成，可以自定义去除数据库表的前缀生成相应的类名，支持获取表的备注，字段备注，主键获取。

database以及脚本代码自动生成相对于mybatis-generator-core自动生成，其优点是不需要将生成的文件的代码嵌入到项目中，而是相当于IDEA的脚本插件使用，但是不好同时生成DO,DAO,SqlMapper文件,因为同时生成的话，反而使用mybatis-generator-core更加方便。

注：使用database以及脚本代码自动生成的时候得先生成DO文件，再DAO文件，最后生成SqlMapper文件，具体原因将在流程里面做介绍

废话不多，直接进入操作过程：

1.配置Database，链接数据库

这一步网上教程很多，也相对简单，这里不做详细介绍了，先选择数据源，这里使用MySql做介绍

接下来就是配置链接信息，需要填写的如下图，点击测试通过之后直接APPLY,OK即可

2.接下来创建脚本文件

跳转到脚本目录下新建三个文件，分别为生成DO,DAO,SQLMAPPER文件，代码如下：

Generate ModePOJOs.groovy

importcom.intellij.database.model.DasTableimportcom.intellij.database.model.ObjectKindimportcom.intellij.database.util.Caseimportcom.intellij.database.util.DasUtilimport java.io.*

importjava.text.SimpleDateFormat/*** @Description 生成DO文件

* @zsy

* @Date 2020-07-13*/packageName= ""tableComment= ""typeMapping=[

(~/(?i)tinyint|smallint|mediumint|int/) : "Integer",

(~/(?i)bigint/) : "Long",

(~/(?i)bool|bit/) : "Boolean",

(~/(?i)float|double|decimal|real/) : "Double",

(~/(?i)datetime|timestamp|date|time/) : "Date",

(~/(?i)/) : "String"]//自定义需要去除的表头,根据自己的数据库表头自定义。自定义作者名称//只有后面紧接着的是"_"下滑线的表头会被处理，比如：t_estcolumn会被处理，testcolumn不会被处理

tableHeaderRemoved = ["t","t_sys"]

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

}

def generate(table, dir) {

def className= javaClassName(table.getName(), true)

def fields=calcFields(table)

packageName=getPackageName(dir)

tableComment=table.getComment()if(tableComment==null){

tableComment= ""}

tableComment= tableComment.replaceAll("\n"," ")

className= className +"DO"PrintWriter printWriter= new PrintWriter(new OutputStreamWriter(new FileOutputStream(new File(dir, className + ".java")), "UTF-8"))

printWriter.withPrintWriter {out->generate(out, className, fields,table)}

}//获取包所在文件夹路径

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 java.io.Serializable;"out.println"import io.swagger.annotations.*;"out.println"import lombok.*;"Set types= newHashSet()

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 ${tableComment} \n" +

" * @Author ${authorName} \n" +

" * @Date "+ new SimpleDateFormat("yyyy-MM-dd").format(new Date()) + " \n" +

" */"out.println""out.println"@Data"out.println"@ApiModel(description= \"${tableComment}\")"out.println"public class $className implements Serializable {"out.println""out.println genSerialID()

i= 0fields.each() {

out.println""

if(it.commoent==null){

it.commoent= ""}

it.commoent= it.commoent.replaceAll("\n","").replaceAll("\r","")//输出注释

if(isNotEmpty(it.commoent)) {

out.println"\t/**"out.println"\t * ${it.commoent.toString()}"out.println"\t */"}

out.println"\t@ApiModelProperty(value = \"${it.commoent}\", position = ${i})"

//输出成员变量

out.println "\tprivate ${it.type} ${it.name};"i++}

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

def calcFields(table) {//加载当前数据库主键

def primaryKey = ""def prKey=DasUtil.getPrimaryKey(table);if (prKey != null) {

def keyRef=prKey.getColumnsRef();if (keyRef != null) {

def it=keyRef.iterate();while(it.hasNext()) {//默认只处理单主键

primaryKey =it.next();

}

primaryKey= javaName(primaryKey, false);

}

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 colName=col.getName()

def nameTemp= javaName(colName, false)//当前列是否为主键

def isPrimaryKey =nameTemp.equals(primaryKey)

def comm=[

colName : colName,

name : nameTemp,

type : typeStr,

commoent: col.getComment(),

isKey: isPrimaryKey,

annos:"@Column(name = \""+col.getName()+"\" )"]

fields+=[comm]

}

}//去掉自定义的字符串，下面的javaName为原始的方法，不去除表头

def javaClassName(str, capitalize) {

def tempStr=str//去除开头的指定字符串

tableHeaderRemoved.each(){

it=it.toLowerCase()if(isNotEmpty(it)){if (it.size()

def headTemp= tempStr[0..it.size() - 1]

def underLineFlag=tempStr[it.size()..it.size()]if (it == headTemp.toLowerCase() && underLineFlag == "_") {

tempStr= tempStr[it.size()..tempStr.size() - 1]return true}

}

def s=com.intellij.psi.codeStyle.NameUtil.splitNameIntoWords(tempStr)

.collect { Case.LOWER.apply(it).capitalize() }

.join("")

.replaceAll(/[^\p{javaJavaIdentifierPart}[_]]/, "_")

capitalize|| s.length() == 1? s : Case.LOWER.apply(s[0]) + s[1..-1]

}

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 isNotEmpty(content) {return content != null && content.toString().trim().length() > 0}static String changeStyle(String str, booleantoCamel){if(!str || str.size() <= 1)returnstrif(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('')

}

}staticString genSerialID()

{return "\tprivate static final long serialVersionUID = "+Math.abs(new Random().nextLong())+"L;"}

Generate DaoPOJOs.groovy

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.io.*

import java.text.SimpleDateFormat

/**

* @Description 生成Dao文件，生成此文件请先生成DO文件

* @Author zsy

* @Date 2020-07-13

packageName = ""

tableComment = ""

modelNameSpace = ""

typeMapping = [

(~/(?i)tinyint|smallint|mediumint|int/) : "Integer",

(~/(?i)bigint/) : "Long",

(~/(?i)bool|bit/) : "Boolean",

(~/(?i)float|double|decimal|real/) : "Double",

(~/(?i)datetime|timestamp|date|time/) : "Date",

(~/(?i)/) : "String"

]

//自定义需要去除的表头,根据自己的数据库表头自定义。自定义作者名称

// 只有后面紧接着的是"_"下滑线的表头会被处理，比如：t_estcolumn会被处理，testcolumn不会被处理

tableHeaderRemoved = ["t","t_sys"]

authorName = "ZhengSiyuan"

//项目路径不能包含中文

modelCreateDir = "mybatis-generator-test\\code-generato\\src\\main\\java\\com\\zsy\\codegenerato\\models"

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 = javaClassName(table.getName(), true)

def fields = calcFields(table)

packageName = getPackageName(dir)

tableComment = table.getComment()

def modelDirPath = getSqlMapperModelNameSpace(dir)

getModelNameSpace(modelDirPath, className)

PrintWriter printWriter = new PrintWriter(new OutputStreamWriter(new FileOutputStream(new File(dir, className + "DAO.java")), "UTF-8"))

printWriter.withPrintWriter {out -> generateSqlMapper(out, className, fields,table)}

}

// 获取包所在文件夹路径

def getPackageName(dir) {

return dir.toString().replaceAll("\\\\", ".").replaceAll("/", ".").replaceAll("^.*src(\\.main\\.java\\.)?", "") + ";"

}

//获取Model的项目保存地址

def getSqlMapperModelNameSpace(dir){

def nameTemp = modelCreateDir.toString().replaceAll("\\\\", ".").replaceAll("/", ".")

def nameList = nameTemp.split('\\.')

def projectName = nameList[0]

def allDirPath = dir.toString().replaceAll("\\\\", ".").replaceAll("/", ".")

def index = allDirPath.toString().indexOf(projectName)

def commonPath = allDirPath[0..index-1]

return "${commonPath}${nameTemp}".replaceAll("\\.", "\\\\")

}

def getModelNameSpace(modelDir, className){

def modelPathTemp = new File(modelDir)

modelPathTemp.eachFileRecurse{directory->createModelNameSpace(directory, className)}

}

def createModelNameSpace(directory, className){

def judgeName = className+"DO.java"

if(directory.toString().indexOf(judgeName.toString())>0){

modelNameSpace = directory.toString().replaceAll("\\\\", ".")

.replaceAll("/", ".")

.replaceAll("^.*src(\\.main\\.java\\.)?", "")

modelNameSpace = modelNameSpace[0..modelNameSpace.size()-6]

}

def generateSqlMapper(out, className, fields,table) {

out.println "package ${packageName}"

out.println ""

out.println "import ${modelNameSpace};"

out.println "import com.huitu.service.enums.DbEnum;"

out.println "import java.util.List;"

out.println "import org.springframework.stereotype.Component;"

out.println ""

def componentName = firstCharToLow(className)+"DAO"

out.println "@Component(\"${componentName}\")"

out.println "public interface ${className}DAO {"

out.println ""

out.println "}"

}

def calcFields(table) {

// 加载当前数据库主键

def primaryKey = ""

def prKey = DasUtil.getPrimaryKey(table);

if (prKey != null) {

def keyRef = prKey.getColumnsRef();

if (keyRef != null) {

def it = keyRef.iterate();

while (it.hasNext()) {

// 默认只处理单主键

primaryKey = it.next();

}

primaryKey = javaName(primaryKey, false);

}

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 colName = col.getName()

def nameTemp = javaName(colName, false)

// 当前列是否为主键

def isPrimaryKey = nameTemp.equals(primaryKey)

def comm =[

colName : colName,

name : nameTemp,

type : typeStr,

commoent: col.getComment(),

isKey: isPrimaryKey,

annos: "@Column(name = \""+col.getName()+"\" )"]

fields += [comm]

}

// 去掉自定义的字符串，下面的javaName为原始的方法，不去除表头

def javaClassName(str, capitalize) {

def tempStr = str

// 去除开头的指定字符串

tableHeaderRemoved.each(){

it = it.toLowerCase()

if (isNotEmpty(it)){

if (it.size() < tempStr.size()){

def headTemp = tempStr[0..it.size() - 1]

def underLineFlag = tempStr[it.size()..it.size()]

if (it == headTemp.toLowerCase() && underLineFlag == "_") {

tempStr = tempStr[it.size()..tempStr.size() - 1]

return true

}

def s = com.intellij.psi.codeStyle.NameUtil.splitNameIntoWords(tempStr)

.collect { Case.LOWER.apply(it).capitalize() }

.join("")

.replaceAll(/[^\p{javaJavaIdentifierPart}[_]]/, "_")

capitalize || s.length() == 1? s : Case.LOWER.apply(s[0]) + s[1..-1]

}

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 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 firstCharToUp(String str){

String r = str[0..0]

if(((char)r).isLowerCase()){

return "${r.toUpperCase()}${str[1..str.length()-1]}"

}

return str

}

//首字母转为小写

static String firstCharToLow(String str){

String r = str[0..0]

if(((char)r).isUpperCase()){

return "${r.toLowerCase()}${str[1..str.length()-1]}"

}

return str

}

static String genSerialID()

{

return "\tprivate static final long serialVersionUID = "+Math.abs(new Random().nextLong())+"L;"

}

Generate MapperPOJOs.groovy

importcom.intellij.database.model.DasTableimportcom.intellij.database.model.ObjectKindimportcom.intellij.database.util.Caseimportcom.intellij.database.util.DasUtilimport java.io.*

importjava.text.SimpleDateFormat/*** @Description 生成SqlMapper文件，生成此文件请先生成DO、DAO文件

* @Author zsy

* @Date 2020-07-13*/packageName= ""tableComment= ""modelNameSpace= ""daoNameSpace= ""tableName= ""typeMapping=[

(~/(?i)tinyint|smallint|mediumint|int/) : "Integer",

(~/(?i)bigint/) : "Long",

(~/(?i)bool|bit/) : "Boolean",

(~/(?i)float|double|decimal|real/) : "Double",

(~/(?i)datetime|timestamp|date|time/) : "Date",

tableHeaderRemoved = ["t","t_sys"]

authorName= "ZhengSiyuan"

//项目路径不能包含中文

modelCreateDir = "mybatis-generator-test\\code-generato\\src\\main\\java\\com\\zsy\\codegenerato\\models"daoCreateDir= "mybatis-generator-test\\code-generato\\src\\main\\java\\com\\zsy\\codegenerato\\mapper"FILES.chooseDirectoryAndSave("Choose directory", "Choose where to store generated files") { dir ->SELECTION.filter { itinstanceof DasTable && it.getKind() ==ObjectKind.TABLE }.each { generate(it, dir) }

}

def generate(table, dir) {

tableName=table.getName()

def className= javaClassName(tableName, true)

def fields=calcFields(table)

packageName=getPackageName(dir)

tableComment=table.getComment()

def modelDirPath=getModelAndDaoSavePath(dir, modelCreateDir)

def daoDirPath=getModelAndDaoSavePath(dir, daoCreateDir)

getNameSpace(modelDirPath, className,"DO.java")

getNameSpace(daoDirPath, className,"DAO.java")

PrintWriter printWriter= new PrintWriter(new OutputStreamWriter(new FileOutputStream(new File(dir, className + "Mapper.xml")), "UTF-8"))

printWriter.withPrintWriter {out->generateSqlMapper(out, className, fields,table)}

}//获取包所在文件夹路径

def getPackageName(dir) {return dir.toString().replaceAll("\\\\", ".").replaceAll("/", ".").replaceAll("^.*src(\\.main\\.java\\.)?", "") + ";"}//获取Model的项目保存地址

def getModelAndDaoSavePath(dir, savePath){

def nameTemp= savePath.toString().replaceAll("\\\\", ".").replaceAll("/", ".")

def nameList= nameTemp.split('\\.')

def projectName= nameList[0]

def allDirPath= dir.toString().replaceAll("\\\\", ".").replaceAll("/", ".")

def index=allDirPath.toString().indexOf(projectName)

def commonPath= allDirPath[0..index-1]return "${commonPath}${nameTemp}".replaceAll("\\.", "\\\\")

}

def getNameSpace(modelDir, className, suffix){

def modelPathTemp= newFile(modelDir)

modelPathTemp.eachFileRecurse{directory->createModelNameSpace(directory, className, suffix)}

}

def createModelNameSpace(directory, className, suffix){

def judgeName= className+suffixif(directory.toString().indexOf(judgeName.toString())>0){

def tempNameSpace= directory.toString().replaceAll("\\\\", ".")

.replaceAll("/", ".")

.replaceAll("^.*src(\\.main\\.java\\.)?", "")if(suffix=="DO.java"){

modelNameSpace= tempNameSpace[0..tempNameSpace.size()-6]

}else if(suffix=="DAO.java"){

daoNameSpace= tempNameSpace[0..tempNameSpace.size()-6]

}

def generateSqlMapper(out, className, fields,table) {

out.println"<?xml version=\"1.0\" encoding=\"UTF-8\"?>"out.println""-//mybatis.org//DTD Mapper 3.0//EN\""out.println"\t\t\"http://mybatis.org/dtd/mybatis-3-mapper.dtd\">"out.println""out.println"\t"fields.each() {if(it.isKey){

out.println"\t\t"}else{

out.println"\t\t"}

}

out.println"\t"def filesId= firstCharToLow(className)+"Fields";

out.println"\t"def filesIdStr= ""def count= 1def keyColumnName= ""def keyName= ""def keyType= ""fields.each(){if(filesIdStr==""){

filesIdStr=it.colName

}else{

filesIdStr= filesIdStr+",${it.colName}"

if(count==10 || count == 20 || count == 30 || count == 40){

filesIdStr= filesIdStr+"\n\t\t"}

}if(it.isKey){

keyColumnName=it.colName

keyName=it.name

keyType=it.type

}

count= count+1}

out.println"\t\t${filesIdStr}"out.println"\t"

if(keyColumnName != ""){

out.println"\t"out.println"\t\tSELECT"out.println"\t\t"out.println"\t\tFROM ${tableName} WITH(NOLOCK)"out.println"\t\tWHERE ${keyColumnName} = #{${keyName}}"out.println"\t"}

out.println""}

def calcFields(table) {//加载当前数据库主键

def primaryKey = ""def prKey=DasUtil.getPrimaryKey(table);if (prKey != null) {

def keyRef=prKey.getColumnsRef();if (keyRef != null) {

def it=keyRef.iterate();while(it.hasNext()) {//默认只处理单主键

primaryKey =it.next();

}

primaryKey= javaName(primaryKey, false);

}

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

def typeStrOrg= spec.toString().replaceAll(/$(.*?)$/, "")

def typeStr= typeMapping.find { p, t ->p.matcher(spec).find() }.value

def colName=col.getName()

def nameTemp= javaName(colName, false)//当前列是否为主键

def isPrimaryKey =nameTemp.equals(primaryKey)

def comm=[

colName : colName,

name : nameTemp,

type : typeStr,

typeOrg : typeStrOrg,

commoent: col.getComment(),

isKey: isPrimaryKey,

annos:"@Column(name = \""+col.getName()+"\" )"]

fields+=[comm]

}

}//去掉自定义的字符串，下面的javaName为原始的方法，不去除表头

def javaClassName(str, capitalize) {

def tempStr=str//去除开头的指定字符串

tableHeaderRemoved.each(){

it=it.toLowerCase()if(isNotEmpty(it)){if (it.size()

def headTemp= tempStr[0..it.size() - 1]

def underLineFlag=tempStr[it.size()..it.size()]if (it == headTemp.toLowerCase() && underLineFlag == "_") {

tempStr= tempStr[it.size()..tempStr.size() - 1]return true}

}

def s=com.intellij.psi.codeStyle.NameUtil.splitNameIntoWords(tempStr)

.collect { Case.LOWER.apply(it).capitalize() }

.join("")

.replaceAll(/[^\p{javaJavaIdentifierPart}[_]]/, "_")

capitalize|| s.length() == 1? s : Case.LOWER.apply(s[0]) + s[1..-1]

}

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 isNotEmpty(content) {return content != null && content.toString().trim().length() > 0}static String changeStyle(String str, booleantoCamel){if(!str || str.size() <= 1)returnstrif(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('')

}

}//首字母转成大写

staticString firstCharToUp(String str){

String r= str[0..0]if(((char)r).isLowerCase()){return "${r.toUpperCase()}${str[1..str.length()-1]}"}returnstr

}//首字母转为小写

staticString firstCharToLow(String str){

String r= str[0..0]if(((char)r).isUpperCase()){return "${r.toLowerCase()}${str[1..str.length()-1]}"}returnstr

}staticString genSerialID()

{return "\tprivate static final long serialVersionUID = "+Math.abs(new Random().nextLong())+"L;"}

其中项目配置路径需要注意，项目路径不能包含中文，..\\models下可以包含子文件夹，但不需要把文件名写在配置中。

modelCreateDir = "mybatis-generator-test\\code-generato\\src\\main\\java\\com\\zsy\\codegenerato\\models"

3.使用脚本生成代码，生成顺序为DO=>DAO=>SqlMapper，因为DAO文件需要用到DO的地址，SqlMapper需要用到DO以及DAO文件地址。

右键选中表，选择相应的脚本文件：

选择需要存放文件的文件夹，点击OK即可，生成其他文件步骤也是一样的。

小结：使用此方法最大的好处就是可以按需生成，而且不需要将生成代码整合在项目当中。一般使用此方法生成DO文件，而DAO跟SqlMapper根据业务需要自己编写。

参考资料：