Generate Java Model Class from MySQL table


Many time we need to generate Java model class for RDBMS tables. For this i have written small MySQL stored procedure to generate JAVA model class for a MySQL table. Any one can generate class by calling this stored procedure with TABLE_NAME parameter.

One can easily modify this script according to his need.

Here is stored procedure script to generate Java Model code

-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `GenJavaModel`(in pTableName VARCHAR(255) )
BEGIN
DECLARE vClassName varchar(255);
declare vClassGetSet mediumtext;
declare vClassPrivate mediumtext;
declare v_codeChunk_pri_var varchar(1024);
declare v_codeChunk_pub_get varchar(1024);
declare v_codeChunk_pub_set varchar(1024);

DECLARE v_finished INTEGER DEFAULT 0;
DEClARE code_cursor CURSOR FOR 
    SELECT pri_var,pub_get, pub_set FROM temp1; 

DECLARE CONTINUE HANDLER 
        FOR NOT FOUND SET v_finished = 1;

set vClassGetSet =\'\';
/* Make class name*/
    SELECT (CASE WHEN col1 = col2 THEN col1 ELSE concat(col1,col2)  END) into vClassName
    FROM(
    SELECT CONCAT(UCASE(MID(ColumnName1,1,1)),LCASE(MID(ColumnName1,2))) as col1,
    CONCAT(UCASE(MID(ColumnName2,1,1)),LCASE(MID(ColumnName2,2))) as col2
    FROM
    (SELECT SUBSTRING_INDEX(pTableName, \'_\', -1) as ColumnName2,
        SUBSTRING_INDEX(pTableName, \'_\', 1) as ColumnName1) A) B;

    /*store all properties into temp table*/
    CREATE TEMPORARY TABLE IF NOT EXISTS  temp1 ENGINE=MyISAM  
    as (
    select 
    concat(\'tprivate \', ColumnType,\' _\', FieldName,\';\') pri_var,
    concat( \'public \', ColumnType , \' get\' , FieldName,\'(){rntt return _\', FieldName,\';rnt}\') pub_get,
    concat( \'public void \', \' set\' , FieldName,\'( \',ColumnType,\' value){rntt _\', FieldName,\' = value;rnt}\') pub_set
    FROM(
    SELECT (CASE WHEN col1 = col2 THEN col1 ELSE concat(col1,col2)  END) AS FieldName, 
    case DATA_TYPE 
            when \'bigint\' then \'long\'
            when \'binary\' then \'byte[]\'
            when \'bit\' then \'bool\'
            when \'char\' then \'String\'
            when \'date\' then \'Date\'
            when \'datetime\' then \'DateTime\'
            when \'datetime2\' then \'DateTime\'
            when \'decimal\' then \'decimal\'
            when \'float\' then \'float\'
            when \'image\' then \'byte[]\'
            when \'int\' then \'int\'
            when \'money\' then \'decimal\'
            when \'nchar\' then \'String\'
            when \'ntext\' then \'String\'
            when \'numeric\' then \'decimal\'
            when \'nvarchar\' then \'String\'
            when \'real\' then \'double\'
            when \'smalldatetime\' then \'Date\'
            when \'smallint\' then \'short\'
            when \'mediumint\' then \'int\'
            when \'smallmoney\' then \'decimal\'
            when \'text\' then \'String\'
            when \'time\' then \'Date\'
            when \'timestamp\' then \'Date\'
            when \'tinyint\' then \'byte\'
            when \'uniqueidentifier\' then \'String\'
            when \'varbinary\' then \'byte[]\'
            when \'varchar\' then \'String\'
            when \'year\' THEN \'int\'
            else \'UNKNOWN_\' + DATA_TYPE
        end ColumnType
    FROM(
    select CONCAT(UCASE(MID(ColumnName1,1,1)),LCASE(MID(ColumnName1,2))) as col1,
    CONCAT(UCASE(MID(ColumnName2,1,1)),LCASE(MID(ColumnName2,2))) as col2, DATA_TYPE
    from
    (SELECT SUBSTRING_INDEX(COLUMN_NAME, \'_\', -1) as ColumnName2,
    SUBSTRING_INDEX(COLUMN_NAME, \'_\', 1) as ColumnName1,
    DATA_TYPE, COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS  WHERE table_name = pTableName) A) B)C);

    set vClassGetSet = \'\';
    set vClassPrivate = \'\';
    /* concat all properties*/
    OPEN code_cursor;
 
            get_code: LOOP
         
                FETCH code_cursor INTO v_codeChunk_pri_var, v_codeChunk_pub_get,  v_codeChunk_pub_set;
         
                IF v_finished = 1 THEN 
                    LEAVE get_code;
                END IF;
                
                -- build code
                select  CONCAT(\'t\',vClassPrivate,\'rn\', v_codeChunk_pri_var) into vClassPrivate;
                select  CONCAT(\'t\',vClassGetSet,\'rnt\', v_codeChunk_pub_get,\'rnt\', v_codeChunk_pub_set) into  vClassGetSet ;
         
            END LOOP get_code;
     
        CLOSE code_cursor;

drop table temp1;
/*make class*/
select concat(\'public class \',vClassName,\'rn{\',vClassPrivate,\'rn\', vClassGetSet,\'rn}\');
END

Execute above script to create stored procedure. You can add addition data types or modify existing according to your need.

Call this stored to get Java model code, using table as parameter

CALL `dietplandb`.`GenJavaModel`(\'unit_lookup\');

genrate-java-model-1

Here is final output

public class UnitLookup
{                
    private int _Id;
    private String _Name;
    private Date _DateModified;
    private byte _IsDeleted;
                
    public int getId(){
         return _Id;
    }
    public void  setId( int value){
         _Id = value;
    }
    public String getName(){
         return _Name;
    }
    public void  setName( String value){
         _Name = value;
    }
    public Date getDateModified(){
         return _DateModified;
    }
    public void  setDateModified( Date value){
         _DateModified = value;
    }
    public byte getIsDeleted(){
         return _IsDeleted;
    }
    public void  setIsDeleted( byte value){
         _IsDeleted = value;
    }
}

You can modify stored procedure script to modify code generation according to you requirement.