Click here to Skip to main content
14,093,423 members
Click here to Skip to main content
Add your own
alternative version

Stats

6.6K views
Posted 29 Mar 2016
Licenced CPOL

Building ORM Objects and Store Procedures from an ERD Using Java and Eclipse

, 29 Mar 2016
Rate this:
Please Sign up or sign in to vote.
ORM objects and basic CRUD operation procedures are first phase tasks that come after establishing a first version of the database. This java-based code generation tool builds those classes and stored procedures.

Introduction

The building out of ORM classes and stored procedures is a good excercise for beginning programmers, but the novelty of it wears off, and then its time to find a better way. Whenever I find myself doing the same things over and over against across different projects, I start looking for shortcuts to the process. This is my attempt at building a code generating tool that builds ORM classes and stored procedures you would be creating from your ERD. I wanted to build a simple tool that could be used across a variety of databases and build source files for a variety of languages. There are plenty of commercial products which are more complicated, more expensive, and more features, but this serves my purposes in many cases.

Background

This first version builds stored procedures (SELECT ONE, SELECT ALL, INSERT, UPDATE, DELETE) for Sqlserver and can build out your ORM objects in Java, C#, or VB.NET depending on how you set the up the configuration. It could be extended to work with other databases easily. There are plenty of tools that do the same thing and go alot further with this type of functionality, but I wanted something simple for my purposes. I like to be able to see the source code to things and change/extend them or simplify them as needed. My daily work is C# and Sqlserver was my focus here. I choose to write this in Java for no particular reason. I used Eclipse to write this code. I have included all source here.
 

Using the code

There are 4 packages with aboout 14 classes. The entry point to the program is the Main.java listed below. Make sure you set the configuration as you need it. I have several enums defined to help make good choices. Only the Microsoft Sqlserver database procedure builder is currently implemented. All three - Java, C#, and VB.NET are building on the class generation side, but you must define the language in the Main.java as seen below. Create and have folders ready with writable permissions for accepting the created files. I tested this on AdventureWorks2012 database which is quite large and did not have any issues. There is no way currently to skip certain tables so you will probably have some throw away classes and procedures for those tables you don't really need an ORM class for. This program is not going to do any  complicated SQL logic or class relationship building for you. It writes one class for every table it sees and writes five stored procedures for every table it sees.

 

package builder;

import common.DatabaseServerType;
import common.DomainObjectLanguage;
import common.ProcedureBuildType;
import domainclassbuilder.CSharpClassWriter;
import domainclassbuilder.JavaClassWriter;
import domainclassbuilder.VisualBasicClassWriter;
import sqlprocedurebuilder.MSSQLProcBuilder;
import sqlprocedurebuilder.MSSQLProcWriter;
import sqlprocedurebuilder.ProcBuilder;


public class Main {
    
      
    public static void main(String[] args) {
        
        DatabaseServerType lDatabaseServerType = DatabaseServerType.MICROSOFTSQLSERVER;
        ProcedureBuildType lProcedureBuildType = ProcedureBuildType.ONEFILEALLTABLES;
        DomainObjectLanguage lDomainObjLang = DomainObjectLanguage.VISUALBASIC;
                
        String lAbsoluteProcedureDirectoryLocation = "C:/Build/Procedures";
        String lAbsoluteClassDirectoryLocation = "C:/Build/Classes";
        String lDbName = "NOP360DEV";        
        String lDbConnection = "jdbc:sqlserver://RHODES-PC\\RHODES_HOME_PC;database=NOP360DEV";        
        String lDbUser = "sa";        
        String lDbPassword = "password";
        String lAuthor = "Giancarlo Rhodes";
        String lOneFileName = "AllProcedures.SQL";
        
        ProcBuilder lProcBuilder = null; 
                
        switch (lDatabaseServerType){
        
        case MICROSOFTSQLSERVER:
                                    
            ProcBuilder._dbname = lDbName;
            ProcBuilder._dbconnection = lDbConnection;
            ProcBuilder._dbusername = lDbUser;
            ProcBuilder._dbuserpassword = lDbPassword;
            
            //MSSQLProcBuilder lMSSQLProcBuilder = new MSSQLProcBuilder(ProcBuilder._dbname, ProcBuilder._dbconnection, ProcBuilder._dbusername, ProcBuilder._dbuserpassword);                            
            lProcBuilder =  new MSSQLProcBuilder(ProcBuilder._dbname, ProcBuilder._dbconnection, ProcBuilder._dbusername, ProcBuilder._dbuserpassword);
            
            // lMSSQLProcBuilder.PrintTables();   // TESTING
            // lMSSQLProcBuilder.PrintAllMetaRows();  //  TESTING
            MSSQLProcWriter lMSSQLProcWriter = new MSSQLProcWriter(lAbsoluteProcedureDirectoryLocation, lProcBuilder);
            lMSSQLProcWriter.Write(lProcedureBuildType, lAuthor, lOneFileName);
            break;
            
        case ORACLE:                        
            // TODO - IMPLEMENT
            break;
                        
        case MYSQL:
            
            // TODO - IMPLEMENT
            break;
            
        default:
            break;
                
        }
         
        
        switch (lDomainObjLang){
                        
        case VISUALBASIC:
                
            //if (lDatabaseServerType == DatabaseServerType.MICROSOFTSQLSERVER){
            //    lProcBuilder = new MSSQLProcBuilder(ProcBuilder._dbname, ProcBuilder._dbconnection, ProcBuilder._dbusername, ProcBuilder._dbuserpassword);    
            //}else if(lDatabaseServerType == DatabaseServerType.ORACLE){
            //    // TODO - IMPLEMENT
            //}else if(lDatabaseServerType == DatabaseServerType.MYSQL){
            //    // TODO - IMPLEMENT
            //}
            VisualBasicClassWriter lVBClassWriter = new VisualBasicClassWriter(lAbsoluteClassDirectoryLocation, lProcBuilder, lAuthor, lDatabaseServerType);                
            lVBClassWriter.BuildAllClasses();
            break;
                    
        case CSHARP:
            
            if (lDatabaseServerType == DatabaseServerType.MICROSOFTSQLSERVER){
                lProcBuilder = new MSSQLProcBuilder(ProcBuilder._dbname, ProcBuilder._dbconnection, ProcBuilder._dbusername, ProcBuilder._dbuserpassword);    
            }else if(lDatabaseServerType == DatabaseServerType.ORACLE){
                // TODO - IMPLEMENT
            }else if(lDatabaseServerType == DatabaseServerType.MYSQL){
                // TODO - IMPLEMENT
            }
                        
             CSharpClassWriter lCSharpClassWriter = new CSharpClassWriter(lAbsoluteClassDirectoryLocation, lProcBuilder, lAuthor, lDatabaseServerType);    
             lCSharpClassWriter.BuildAllClasses();
            break;
                        
        case JAVA:
            
            if (lDatabaseServerType == DatabaseServerType.MICROSOFTSQLSERVER){
                lProcBuilder = new MSSQLProcBuilder(ProcBuilder._dbname, ProcBuilder._dbconnection, ProcBuilder._dbusername, ProcBuilder._dbuserpassword);    
            }else if(lDatabaseServerType == DatabaseServerType.ORACLE){
                // TODO - IMPLEMENT
            }else if(lDatabaseServerType == DatabaseServerType.MYSQL){
                // TODO - IMPLEMENT
            }
            
             JavaClassWriter lJavaClassWriter = new JavaClassWriter(lAbsoluteClassDirectoryLocation, lProcBuilder, lAuthor, lDatabaseServerType);    
             lJavaClassWriter.BuildAllClasses();
            break;
                        
        default:
            break;
            
        }
                              
    }
                   
}

 

 

Points of Interest

Get code from zip or latest at:

https://github.com/grhodes29/ProcDomBuilder

History

Version 1 - 3/27/2016

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Giancarlo Rhodes
Software Developer (Senior)
United States United States
Started engineering as a 5 year old by completely dissembled my father's .38 special replica pistol. I was trying to figure out GOTOs and GOSUBs on the Commodore Vic-20 at 10. Graduated from the University of Missouri-Columbia with a degree in Computer Science in 1998 and have been programming since. My area of focus has been web programming for the last 10 years.

You may also be interested in...

Pro
Pro

Comments and Discussions

 
-- There are no messages in this forum --
Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web01 | 2.8.190518.1 | Last Updated 30 Mar 2016
Article Copyright 2016 by Giancarlo Rhodes
Everything else Copyright © CodeProject, 1999-2019
Layout: fixed | fluid