ColdFusion 9 debugging hibernate schema exports

I have really been diving deep into the new ColdFusion 9 hibernate integration and I love it. I was working with a many-to-many relationship last night and I was having some issues. To enable hibernate there is 1 setting that is required and a few for this example that we are going to use for this example. <cfscript> this.ormenabled = true; this.datasource = "gannon"; this.ormsettings = { cfclocation = "model", dbcreate = "update", logsql = "true", eventHandling = true }; </cfscript>

Back in my application I have a product entity and a category entity. The product can be associated with multiple categories and categories can obviously have multiple products. This would allow us to get all the categories a product is a part of as well us list any products that belong to a category. A many-to-may relationship is setup in the database using a link table that holds a foreign key to each record. Below is my product entity as it was and my category entity as it was. <cfscript> component extends="gannon.model.abstract.AbstractEntity" entityname="Product" persistent="true" output="false" { property name="productId" fieldtype="id" generator="increment" sqltype="integer"; property name="name"; property name="price"; property name="qty" sqltype="integer"; property name="description"; property name="categories" fieldtype="many-to-many" cfc="gannon.model.category.Category" linktable="ProductCategory" fkcolumn="productId" inversejoincolumn="categoryId" lazy="true" cascade="all" orderby="name"; } </cfscript>

<cfscript> component extends="gannon.model.abstract.AbstractEntity" entityname="Category" persistent="true" output="false" { property name="categoryId" fieldtype="id" generator="increment" sqltype="integer"; property name="name"; property name="parent" type="numeric"; property name="products" fieldtype="many-to-many" cfc="gannon.model.product.Product" linktable="ProductCategory" fkcolumn="categoryId" inversejoincolumn="productId" lazy="true" cascade="all" orderby="productId"; } </cfscript>

I am not going to get into all the details of setting up a many-to-many relationship because thats not the focus on this post. Now that I thought I was all ready to go I reloaded my application hoping my 3 new tables would be written (Product/Category/ProductCategory). When I ran the application I got the following error.

Error during DDL export

Well I am no hibernate expert but after asking a few people and doing some reading I realized this was an issue with the schema export. Basically something was not right with the way I setup my relationship here so the resulting schema was incorrect. How do you debug something like this is what I asked myself? I asked a friend and he said you need to enable ddl debugging in the hibernate settings file. I run my server right from ColdFusion Builder so I am already seeing a bunch of errors in the console view but I am not seeing what I need.

If you go to the cf_root\lib\log4j.properties file we can make a quick change so that we can see some debugging information. You should be able to find the following line:

### log schema export/update ### #log4j.logger.org.hibernate.tool.hbm2ddl=ERROR

What we want to do is log more just errors, we want to get some debugging information back, so change ERROR to DEBUG. Next I reloaded my application again and got some more information back.

Column 'Category.categoryId' is not the same data type as referencing column 'ProductCategory.categoryId' in foreign key 'FKD05546ED5424A3B7'.

I checked the database and sure enough my categoryId field was getting created as a varchar in the Category table and a integer in the ProductCategory table. I went back to my category entity and realized that I didn't set its type to numeric. I reloaded the application and everything worked out just how I wanted it to.

The new hibernate integration is simply awesome. It is so easy to use yet powerful enough for us to get under the hood in situations like this one. If I were you I would start your local servers within ColdFusion Builder (or a console) so you can start to see the different things going on behind the scenes. It really gives you some insight on to how things are working, and how they are not!