Home :: Blogs :: venkat's blog

Grails - How to create groovy domain classes from mysql database

By venkat - Posted on April 18th, 2008
Tagged:  
Average: 2.5 (2 votes)

Recently I started working with Grails and I am very much impressed with its capabilities and kind of digging deep now.

Being used MyEclipse extensively I started searching for tool to create groovy domain objects from mysql database. But no luck I couldn't able to find any tools or scripts. So I created one which suits my needs presently. Feel free to use it any way you like.

Features

- Converts Plural table names to Singular form
- Removes underscores (_) and capitalizes first letter of every word.

Dependencies

- MySQL Connector/J
- Apache Commons Lang

Features for next Version

- Validation for user input and Error Handling
- Connects to any database ( MySQL / SQL Server / Oracle )
- Processes Relations and create GORM entries in groovy domain classes

  1. /*
  2.  *
  3.  * Licensed under the Apache License, Version 2.0 (the "License");
  4.  * you may not use this file except in compliance with the License.
  5.  * You may obtain a copy of the License at
  6.  *
  7.  *      <a href="http://www.apache.org/licenses/LICENSE-2.0<br />
  8. " title="http://www.apache.org/licenses/LICENSE-2.0<br />
  9. ">http://www.apache.org/licenses/LICENSE-2.0<br />
  10. </a> *
  11.  * Unless required by applicable law or agreed to in writing, software
  12.  * distributed under the License is distributed on an "AS IS" BASIS,
  13.  * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  14.  * See the License for the specific language governing permissions and
  15.  * limitations under the License.
  16.  *
  17.  * Venkat Addanki - <a href="http://www.techgrasp.com/blogs/venkat<br />
  18. " title="http://www.techgrasp.com/blogs/venkat<br />
  19. ">http://www.techgrasp.com/blogs/venkat<br />
  20. </a> *
  21.  */
  22. package com.techgrasp.getschema;
  23.  
  24. import java.io.BufferedWriter;
  25. import java.io.FileWriter;
  26. import java.io.IOException;
  27. import java.sql.Connection;
  28. import java.sql.DatabaseMetaData;
  29. import java.sql.DriverManager;
  30. import java.sql.ResultSet;
  31. import java.sql.SQLException;
  32. import java.util.HashMap;
  33.  
  34. import org.apache.commons.lang.WordUtils;
  35.  
  36. public class Main {
  37.        
  38.         public static final HashMap<String,String> dataTypes = new HashMap<String,String>();
  39.         public static String hostName = null;
  40.         public static String dbPort = null;
  41.         public static String dbName = null;
  42.         public static String dbUser = null;
  43.         public static String dbPass = null;
  44.         public static String location = null;
  45.  
  46.     public static void main(String[] args) {
  47.        
  48.        
  49.         try {
  50.                
  51.                 if(args.length == 0) {
  52.                         System.err.println("Invalid number of parameters");
  53.                         System.err.println("Usage: GenerateDomain <dbhost> <port> <dbname> <user> <password> <generatedLocation>");
  54.                         System.exit(0);
  55.                 }
  56.                
  57.                 hostName = args[0];
  58.                 dbPort = args[1];
  59.                 dbName = args[2];
  60.                 dbUser = args[3];
  61.                 dbPass = args[4];
  62.                 location = args[5];
  63.                
  64.                 initializeHashMap();
  65.             Class.forName("com.mysql.jdbc.Driver");
  66.             String url = "jdbc:mysql://" + hostName + ":" + dbPort + "/" + dbName;
  67.             Connection con = DriverManager.getConnection(url, dbUser,dbPass);
  68.  
  69.             final DatabaseMetaData dmd = con.getMetaData();
  70.            
  71.             String[] types = { "TABLE" };
  72.             ResultSet tables = dmd.getTables(dbName, null,null,types);
  73.            
  74.             while (tables.next()) {
  75.                 final String tableName = tables.getString(3);
  76.                 generateDomain(dbName,tableName,dmd);
  77.             }
  78.             tables.close();
  79.             con.close();
  80.            
  81.         } catch (Exception e) {
  82.             e.printStackTrace();
  83.         }
  84.     }
  85.  
  86.  
  87.         private static void generateDomain(String catalog, String tableName, DatabaseMetaData dmd) throws IOException, SQLException {
  88.                
  89.                 String groovyDomainName = getDomainNameFromTableName(tableName);
  90.                 if(groovyDomainName.equals("Config")) {
  91.                         groovyDomainName = "AppConfig";
  92.                 }
  93.                
  94.                 System.out.println("Generating class " + groovyDomainName);
  95.                 BufferedWriter bw = new BufferedWriter( new FileWriter(location + groovyDomainName + ".groovy"));
  96.                 bw.write("class " + groovyDomainName + " { \n\n");
  97.                
  98.                 ResultSet columns = dmd.getColumns(catalog, null, tableName, null);
  99.                
  100.                 while(columns.next()) {
  101.                         bw.write("    " + dataTypes.get(columns.getString(6)) + " " + columns.getString(4) + "\n");
  102.                 }
  103.                
  104.         columns.close();
  105.         bw.write("\n");    
  106.                 bw.write("    static mapping = {\n");
  107.                 bw.write("      table '" + tableName + "'\n");
  108.                 bw.write("      version false\n");
  109.                 bw.write("    }\n");
  110.                 bw.write("}\n");
  111.                 bw.close();
  112.                
  113.         }
  114.  
  115.         private static String getDomainNameFromTableName(final String tableName) {
  116.                 String temp1 = tableName.replace('_', ' ') + " ";
  117.                 String temp2 = temp1.replaceAll("s ", " ");
  118.                 String temp5 = temp2.replaceAll("ie ","y ");
  119.                 String temp3 = WordUtils.capitalize(temp5);
  120.                 return temp3.replaceAll(" ", "");
  121.         }
  122.  
  123.         private static void initializeHashMap() {
  124.                 dataTypes.put("CHAR","String");
  125.                 dataTypes.put("VARCHAR","String");
  126.                 dataTypes.put("LONGVARCHAR","String");
  127.                 dataTypes.put("TEXT","String");
  128.                 dataTypes.put("LONGTEXT","String");
  129.                 dataTypes.put("NUMERIC","BigDecimal");
  130.                 dataTypes.put("DECIMAL","BigDecimal");
  131.                 dataTypes.put("BIT","Boolean");
  132.                 dataTypes.put("TINYINT","Byte");
  133.                 dataTypes.put("SMALLINT","Short");
  134.                 dataTypes.put("INTEGER","Integer");
  135.                 dataTypes.put("INT","Integer");
  136.                 dataTypes.put("INT UNSIGNED","Integer");
  137.                 dataTypes.put("BIGINT","Long");
  138.                 dataTypes.put("REAL","Float");
  139.                 dataTypes.put("FLOAT","Double");
  140.                 dataTypes.put("DOUBLE","Double");
  141.                 dataTypes.put("BINARY","Byte[]");
  142.                 dataTypes.put("BLOB","Byte[]");
  143.                 dataTypes.put("VARBINARY","Byte[]");
  144.                 dataTypes.put("LONGVARBINARY","Byte[]");
  145.                 dataTypes.put("DATE","Date");
  146.                 dataTypes.put("TIME","Date");
  147.                 dataTypes.put("DATETIME","Date");
  148.                 dataTypes.put("TIMESTAMP","Date");
  149.         }
  150.  
  151. }

Bookmark/Search this post with:
  • Delicious
  • Digg
  • StumbleUpon
  • Propeller
  • Reddit
  • Magnoliacom
  • Newsvine
  • Furl
  • Facebook
  • Google
  • Yahoo
  • Technorati
  • Icerocket
Stop Being Lazy
Submitted by Anonymous on Tue, 06/03/2008 - 22:05.

You should contribute this to the project rather than putting it here. Sure would be nice if this was part of the grails commands???

Post new comment

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
j
M
T
E
s
4
Enter the code without spaces and pay attention to upper/lower case.