Looking for a database to Java code generator

I'm looking for something that would mash some code into my build.
Suppose I had a table called "Employee" and it had three columns: EmployeeID (int not null), EmployeeName (varchar(80) null) and SupervisorEmployeeID (int null).
I would like to run a program that would generate a class called EmployeeRecord that would have the attributes
int employeeID ;
String employeeName ;
Integer SupervisorEmployeeID ;
with getters and setters for each (both int and Integer for the last one).
The class would also have the methods
addNewRecord()
updateRecord()
deleteRecord()
each with the innards to take care of its database manipulation of the object.
And there would be a constructor EmployeeRecord( int id ) that would read the information from the database.
And the class would have public static constants like TABLE_NAME, EMPLOYEE_ID_COLUMN_NAME, etc.
I'm guessing that something like this already exists?

So basically you are looking for a Java Bean creator based on a DB query of METADATA from the database?
I usually just manually code one, but I am currently working on a generic bean creator that will create my java file based on the varables and data types I give it in a GUI. I suppose it wouldn't be that hard to make it also query a database to get the information it needs to create the bean. You would also have to have data type mapping static constents set up so that your JAVA data types meshed well with your DB data types.
doesn't JDO do some of this kind of already?
[ April 02, 2003: Message edited by: Gregg Bolinger ]

I don't want a bean cuz I might want to use this without an EJB server.
I don't want JDO cuz there are things about that I'm not currently comfortable with.
I would go for something kinda like JDO.
I was thinking of rolling my own, but thought there is probably something that does this already ...
The big perk that I want is if I change my database, anything that uses the database will get a compiler error. Plus, I have my type checking taken care of.

Sounds like the SCJD assignment.

Well, one way to do this is to get XML as the results from the database and use JAXB to do Data Binding, which creates Java classes based on XML, and can convert from XML to Java class and back to XML. JAXB is really easy to implement.
Not sure if this is the cleanest way to do what you want, but it does keep EJBs out of the equation. Let's you use whatever means to access the batadase that you want. (Yes I was lyxdesic on purpose there).

I don't want a bean cuz I might want to use this without an EJB server.

Um, Gregg's suggestion actually wasn't and EJB, but a JavaBean. JavaBeans are just a spec for building classes and doesn't require an EJB server.

Mark
[ April 02, 2003: Message edited by: Mark Spritzler ]

I wrote a class like this once when I was at Avis. A generic version would be a nice class to add to the javaRanch utilities.

It would be nice if it didn't already exist

If lots of folks are doing this sort of thing all the time right now then it would probably be wise to use what they are using.
I already have some spiffy stuff that works great, but you feed in table names and column names. It is very simple and quick. The problem is that if I change the database, I end up having to remember the changes and hunt for where I might have used that stuff. With what I'm suggesting here, it would cause the compiler to barf at every point I need to change. Plus, something like this could also force type casting.

If you are looking for a Persistence Framework that provides code generation, you might want to look at Jaxor ( http://jaxor.sourceforge.net )
If you don't want code generation, I like Castor ( http://castor.exolab.org )
Other possibilities are Jakarta OJB and Hibernate, and of course the JDO implementations (haven't looked at them yet...)
Dave

If you are looking for a Persistence Framework that provides code generation, you might want to look at Jaxor ( http://jaxor.sourceforge.net )
I followed the link and read a bit. It sounds like they have a whole environment. I think I'm looking for something a bit simpler than that.
If you don't want code generation, I like Castor ( http://castor.exolab.org )
If it doesn't generate code, then my guess is that my idea of database changes resulting in code breakage at compile time wouldn't work. Do you think this is an accurate assessment?
Other possibilities are Jakarta OJB and Hibernate, and of course the JDO implementations (haven't looked at them yet...)

Went and looked at OJB - sounds like JDO in the making.
Got a link for hibernate?

Originally posted by Paul Wheaton:
I don't want a bean cuz I might want to use this without an EJB server.
I don't want JDO cuz there are things about that I'm not currently comfortable with.
I would go for something kinda like JDO.
I was thinking of rolling my own, but thought there is probably something that does this already ...
The big perk that I want is if I change my database, anything that uses the database will get a compiler error. Plus, I have my type checking taken care of.

Well, from when I read the definition of a Java Bean, not an EJB mind you, it is a class with an empty constructor, private variable definitions and public getter and setter methods. So I would say you want something similar just with the added DB functionallity of insert, delete, and update. My question would be how to keep it persistant with the DB. What would check for DB changes ALL THE TIME to insure this?

Well, from when I read the definition of a Java Bean, not an EJB mind you, it is a class with an empty constructor, private variable definitions and public getter and setter methods. So I would say you want something similar just with the added DB functionallity of insert, delete, and update.
You're on the right track!
My question would be how to keep it persistant with the DB. What would check for DB changes ALL THE TIME to insure this?

Exactly why I would want it to be a program that generates the classes as part of the build. It would open the database, find out what all the tables are and generate the class files based on what it discovers about the tables.

I heard of something like that, I'll go check my notes.
In the mean time, here are some more:
http://hibernate.bluemars.net
http://www.ofbiz.org/core/docs/entity.html
http://jakarta.apache.org/ojb/index.html
http://www.simpleorm.org
http://www.objectweb.org/jorm/
I haven't found what you were looking for. I was thinking Castor Doclet, but this works the other way around. It generates the db mapping from the Java code.

I think Toplink (now owned by Oracle) does this, but it is a commercial product.
A list of Object-Relation mappng tools is available here:
http://www.uq.net.au/~zzabergl/simpleorm/ORMTools.html#reviews

Hibernate: doesn't do the code generation.
OFBEE: Defined in an XML file instead of reading from the current database. This could be okay if the XML were rich enough to cover everything you could do with all databases. Cuz then, you would define stuff in the XML and interact with the database in Java. But since that ain't so, instead you define the database in SQL, define the mapping in XML (and maybe a few other things) and access the data in Java. What I'm looking for is that you do less, not more. The database is already defined in what is best for defining a database: SQL. What I want is to have all of my java access stuff to be automatically generated so I can eliminate/reduce my human errors. Having an XML layer just makes things worse (IMO).
SimpleORM: Sounds much closer to what I'm looking for, but does not support SQL Server.
JORM: It mentioned object binding making me think that it is a bit more than what I'm looking for.

David,
I'm starting to read your page, but that seems to be the sort of thing I'm looking for!

I'm leaning towards Thomas's suggestion of making my own.
In a nutshell, a program opens a database, sees a table called "Employee" and creates a java source file called EmployeeTable.java. And that class has getRow(), getRows(), addRow(), deleteRow(), updateRow() methods. It also has a bunch of public constants to hold the table name and all of the column names. Oh, and a getter and setter for each value of the row.
Not much to it.

Hi Paul,
Couldn't you just do a quick query against the database table, get the ResultSet Meta Data and export the details of the columns in XML format and then use XSLT to generate a class file based upon the column definitions.
Shouldn't be too taxing.

Andy,
I don't know XSLT - are those steps something I could put into an automated build?

This would be a very cool Ant task.

Originally posted by Thomas Paul:
This would be a very cool Ant task.

Doing it as XSLT or writing a little java program?

I have been giving this a bit more thought. I don't think it should be too difficult to create this either as an Ant task or as a stand-alone application. We would need a way of specifying the table(s) that we are interested in.
The details of the table(s) could be retreived by issueing a SQL statement like:
"SELECT * FROM <TABLE_NAME> WHERE 1=2"
which would bring back the coulmn definitions in the ResultSetMetaData but would have no rows in the ResultSet.
An XML fragment could then be built that looks something like:
<table name="table_name">
<column name="id" type="Integer" autoincrement="true"/>
<column name="employee_name" type="String" length="30"/>
<column name="address" type="String" length="400"/>
.....
</table>
This XML could then be transformed using XSLT to generate the Data Access Code that we are after.
The only issue I can see at the moment is how to identify the Primary Key of the table as this would be required to generate the updateRow() and deleteRow() methods. Does anyone out there have any bright ideas.
If I get a chance I will attempt to generate a bit of code and the associated XSLT this evening. (My wife has an evening class, so I can do this without too much hassle

)
[ April 04, 2003: Message edited by: Andy Bowes ]

You may find dbForms interesting. Most of it you won't be interested in -- at its core is a tag library for thin, database-driven applications. But it's got a little GUI tool which will connect to a database, turn the metadata into an XML document and apply XSL Transformations to them to generate JSPs and the like. You can add your own transformations and it should be a doddle to write an XSLT file that generates JavaBeans for you.
- Peter

Hi Peter,
dbForms look pretty interesting but I haven't seen it create any Java classes, it seems to only create JSP files that link to the underlying database. I'll give it a bit more of a look tomorrow.
I have spent a couple of hours working on a similar solution that reads the database structure, creates an XML schema and then applies XSLT to create 2 classes for each table :-
i) A Value Object class that represents a row in the database table.
ii) A Data Access Object that interacts with the database to select, create, update and delete these rows.
I am still missing a way to automatically identify the primary key columns in a DB table without introducing database vendor specific code. It needs a bit more polish to create a final solution that can be added to the Code Barn but I am already pretty convinced that it would have saved significant time on previous projects.

Well, if you really want the simpliest way to do this and are not adverse to learning a new platform/API, you can always try to use WebObjects. It has a built in database modeler that can build your database for you and generate all the code. It treats all your tables as objects, and knows how to do updates, inserts and deletes on your data without you having to write any code yourself.

Well, Marilyn and I were going to write a little something this weekend that would do the ant thing, but the MD stuff took more time than we thought.
It seems like there really wouldn't be much to it.

Mr Wheaton,
My company is also looking at code generation options. Here are the products that I have looked at:
Chrysalis: An SQL database code generator<http://chrysalis.tigris.org/>
DbGen - An Object Relational Mapping Tool <http://dbgen.sourceforge.net/>
sql2java <http://www.bitmechanic.com/projects/s2j/>
XDoclet <http://xdoclet.sourceforge.net>
karapan-sapi <http://www.javanovic.com/>
Jet Gen <http://www.jetools.com/products/jetgen>
You can also look at the list <http://www.javaskyline.com/database.html#ormap>
I already sent this to you, but as I got my JavaRanch login sorted out, I thought others might be interested.

Originally posted by Andy Bowes:
I have been giving this a bit more thought. I don't think it should be too difficult to create this either as an Ant task or as a stand-alone application. We would need a way of specifying the table(s) that we are interested in.
The details of the table(s) could be retreived by issueing a SQL statement like:
"SELECT * FROM <TABLE_NAME> WHERE 1=2"
which would bring back the coulmn definitions in the ResultSetMetaData but would have no rows in the ResultSet.
An XML fragment could then be built that looks something like:
<table name="table_name">
<column name="id" type="Integer" autoincrement="true"/>
<column name="employee_name" type="String" length="30"/>
<column name="address" type="String" length="400"/>
.....
</table>
This XML could then be transformed using XSLT to generate the Data Access Code that we are after.
The only issue I can see at the moment is how to identify the Primary Key of the table as this would be required to generate the updateRow() and deleteRow() methods. Does anyone out there have any bright ideas.

Using the select statement is not the way to go. Instead, you should get the Database metadata from the JDBC Driver. (I did this once, but forget the exact methods used; also, the JDBC-ODBC Bridge does not support this when connecting to an Access ODBC Data Source. Not much of a drawback....) This will give you primary key information.
I have successfully written a code-from-data ganerator, but I don't think that it would suit Paul's needs as it uses my persistance framework.

See my post here... I came to this forum to ask a slightly related question.. and thought I'd link to yours.

The one thing Torque won't do is use metadata, you have to specify your metadata in a *-schema.xml file.

Thanks Joel,
I'll revisit my code, I haven't really used the Database MetaData before so it will be a voyage of discovery.
Andy

Well, I'm about 80% done with making my own.
I have it generating the class files now. And they seem to be working pretty good.
Now I'm trying to define how get the variant info from the geek running my program. First I want to do the command line stuff. Later, I'll support ant.
There are two parameters that I will always need. The driver and URL so that Tammy (that's what I named my program) can get her info from the database. Optionally a name and password to go with it.
Next, I need to feed in the way that the connection is handled within the code. Possibly through a Driver, or possibly through JNDI (like when in an EJB environment).
I'm thinking of a command line like this:
Tammy Driver=com.mysql.Driver ConURL=jdbc:mysql://localhost/soup FJNDI=java:comp/env/jdbc/DataSource
What do you guys think?

I went with a properties file.
Now adding the mock table classes for each object.
Anybody wanna review an early release?

For simple code generation you might want to
use a template similiar to those used here
sutternow

For more of a whole web application, keep and eye
on
StrutsGenerator
In addition to code enhancements, we should have a swing based gui due out soon.
Matt
[ May 12, 2003: Message edited by: Matthew Payne ]
[ May 12, 2003: Message edited by: Matthew Payne ]

I think hibernate is great
If you already have db schema you can use something like middlegen and hibernate to generate java code
I prefer the other way
I write java code, and some xdoclet hibernate tags in my java file
then build it, generate xml mappings that hibernate needs
The xml has more info then just table.column mapping to java class.field
e.g. how to map relations and how to map inheritence
you do not have to modify class files and all
once xml is generated you can use hibernate schema export to make sql scripts !!!
This is java file

This is GENERATED xml file

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
    "-//Hibernate/Hibernate Mapping DTD 2.0//EN" 
    "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping>
    <class
        name="org.aol.harmony.persistence.User"
        table="t_user"
        dynamic-update="true"
        dynamic-insert="false"
    >
        <id
            name="id"
            column="id"
            type="java.lang.Long"
            unsaved-value="null"
        >
            <generator class="hilo">
                <param name="table">t_all_id</param>
                <param name="column">next</param>
            </generator>
        </id>
        <version
            name="version"
            type="int"
            column="version"
        />
        <property
            name="name"
            type="java.lang.String"
            update="true"
            insert="true"
            column="NAME"
            not-null="false"
            unique="false"
        />
        <property
            name="status"
            type="java.lang.Character"
            update="true"
            insert="true"
            column="status"
            not-null="true"
            unique="false"
        />
        <property
            name="created"
            type="timestamp"
            update="false"
            insert="true"
            column="created_date"
        />
        <property
            name="modified"
            type="timestamp"
            update="true"
            insert="true"
            column="modified_date"
        />
        
    </class>
</hibernate-mapping>

and this is GENERATED sql

What else would one want ??
soni_kalpesh@hotmail.com