Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Looking for a database to Java code generator

 
paul wheaton
Trailboss
Pie
Posts: 21346
Firefox Browser IntelliJ IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
Gregg Bolinger
GenRocket Founder
Ranch Hand
Posts: 15302
6
Chrome IntelliJ IDE Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
paul wheaton
Trailboss
Pie
Posts: 21346
Firefox Browser IntelliJ IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
Thomas Paul
mister krabs
Ranch Hand
Posts: 13974
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
paul wheaton
Trailboss
Pie
Posts: 21346
Firefox Browser IntelliJ IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Pie
Posts: 21346
Firefox Browser IntelliJ IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 15302
6
Chrome IntelliJ IDE Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Pie
Posts: 21346
Firefox Browser IntelliJ IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Pie
Posts: 21346
Firefox Browser IntelliJ IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Pie
Posts: 21346
Firefox Browser IntelliJ IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
David,
I'm starting to read your page, but that seems to be the sort of thing I'm looking for!
 
paul wheaton
Trailboss
Pie
Posts: 21346
Firefox Browser IntelliJ IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 171
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
paul wheaton
Trailboss
Pie
Posts: 21346
Firefox Browser IntelliJ IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Andy,
I don't know XSLT - are those steps something I could put into an automated build?
 
Thomas Paul
mister krabs
Ranch Hand
Posts: 13974
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This would be a very cool Ant task.
 
paul wheaton
Trailboss
Pie
Posts: 21346
Firefox Browser IntelliJ IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 171
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 3252
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 171
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Pie
Posts: 21346
Firefox Browser IntelliJ IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Joel McNary
Bartender
Posts: 1840
Eclipse IDE Java Ruby
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Mike Curwen
Ranch Hand
Posts: 3695
IntelliJ IDE Java Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 171
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Pie
Posts: 21346
Firefox Browser IntelliJ IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Pie
Posts: 21346
Firefox Browser IntelliJ IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I went with a properties file.
Now adding the mock table classes for each object.
Anybody wanna review an early release?
 
Matthew Payne
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 312
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic