File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Looking for a database to Java code generator Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Looking for a database to Java code generator" Watch "Looking for a database to Java code generator" New topic
Author

Looking for a database to Java code generator

paul wheaton
Trailboss

Joined: Dec 14, 1998
Posts: 20635
    ∞

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?


permaculture Wood Burning Stoves 2.0 - 4-DVD set
Gregg Bolinger
GenRocket Founder
Ranch Hand

Joined: Jul 11, 2001
Posts: 15299
    
    6

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 ]

GenRocket - Experts at Building Test Data
paul wheaton
Trailboss

Joined: Dec 14, 1998
Posts: 20635
    ∞

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.
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17257
    
    6

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 ]

Perfect World Programming, LLC - Two Laptop Bag - Tube Organizer
How to Ask Questions the Smart Way FAQ
Thomas Paul
mister krabs
Ranch Hand

Joined: May 05, 2000
Posts: 13974
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.


Associate Instructor - Hofstra University
Amazon Top 750 reviewer - Blog - Unresolved References - Book Review Blog
paul wheaton
Trailboss

Joined: Dec 14, 1998
Posts: 20635
    ∞

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.
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

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
paul wheaton
Trailboss

Joined: Dec 14, 1998
Posts: 20635
    ∞

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?
Gregg Bolinger
GenRocket Founder
Ranch Hand

Joined: Jul 11, 2001
Posts: 15299
    
    6

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?
paul wheaton
Trailboss

Joined: Dec 14, 1998
Posts: 20635
    ∞

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.
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

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.
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

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
paul wheaton
Trailboss

Joined: Dec 14, 1998
Posts: 20635
    ∞

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.
paul wheaton
Trailboss

Joined: Dec 14, 1998
Posts: 20635
    ∞

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

Joined: Dec 14, 1998
Posts: 20635
    ∞

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.
Andy Bowes
Ranch Hand

Joined: Jan 14, 2003
Posts: 171
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 Bowes<br />SCJP, SCWCD<br />I like deadlines, I love the whoosing noise they make as they go flying past - Douglas Adams
paul wheaton
Trailboss

Joined: Dec 14, 1998
Posts: 20635
    ∞

Andy,
I don't know XSLT - are those steps something I could put into an automated build?
Thomas Paul
mister krabs
Ranch Hand

Joined: May 05, 2000
Posts: 13974
This would be a very cool Ant task.
paul wheaton
Trailboss

Joined: Dec 14, 1998
Posts: 20635
    ∞

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

Doing it as XSLT or writing a little java program?
Andy Bowes
Ranch Hand

Joined: Jan 14, 2003
Posts: 171
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 ]
Peter den Haan
author
Ranch Hand

Joined: Apr 20, 2000
Posts: 3252
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
Andy Bowes
Ranch Hand

Joined: Jan 14, 2003
Posts: 171
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.
Christopher La May
Greenhorn

Joined: Apr 14, 2003
Posts: 1
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.
paul wheaton
Trailboss

Joined: Dec 14, 1998
Posts: 20635
    ∞

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.
Patrick O'Hara
Greenhorn

Joined: May 08, 2001
Posts: 9
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.


Pat O<br />pdohara@practicalprogrammer.org
Joel McNary
Bartender

Joined: Aug 20, 2001
Posts: 1821

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.


Piscis Babelis est parvus, flavus, et hiridicus, et est probabiliter insolitissima raritas in toto mundo.
Mike Curwen
Ranch Hand

Joined: Feb 20, 2001
Posts: 3695

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.
Andy Bowes
Ranch Hand

Joined: Jan 14, 2003
Posts: 171
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
paul wheaton
Trailboss

Joined: Dec 14, 1998
Posts: 20635
    ∞

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?
paul wheaton
Trailboss

Joined: Dec 14, 1998
Posts: 20635
    ∞

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

Joined: May 12, 2003
Posts: 4
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 ]
Kalpesh Soni
Ranch Hand

Joined: Jan 02, 2001
Posts: 312
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

and this is GENERATED sql




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


Test 094, IBM WID 6.0 cert
SCJP 1.2
SCBCD 1.3 Beta
SCWCD 1.4 Beta
SCMAD Beta
SCDJWS Beta

KS
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Looking for a database to Java code generator