Generating modeled database layout from JDeveloper

JDeveloper is a fantastic development tool that allows one to model and develop all layers of an application from the data all the way to the user interface and beyond. One of these awesome features is exposed via the database modeler. Here you can lay out everything pertaining to the data structure of your application prior to creating that structure in a database before actually generating it saving you the headache of repeatedly creating new DDL to make tweaks as you get it just right. Once you've done that you can then go on to generate the default data services that will fulfill the object side of the object-relational bridge of your application right from there as well!

In the database modeler you have options for everything you could think of: tables, views (materialized or not), constraints, keys, custom types, procedures, sequences, you name it. What was funny was I had one of those moments the other day when generating a data structure for a test application I have been working on that prompted my posting.

So anyway, I had modeled out my data structure, had my database connection set up and was ready to generate the modeled structure so I could begin working on creating my business components.

So here you can see the context menu used. I select what I want to create (in this case Ctrl-A for everything), and then right-click on the diagram selecting in the context menu: Synchronize with Database->Generate To->[database connection name].

I then jump midway into the generation wizard since I've already selected my source, target, and objects. I selected to create my objects (option of create, replace, and alter), opted not to create a script, and just told the wizard to finish. And, voila! Once I opened up the database navigator, there were my tables, all set up and ready to go for me. But here was the catch.

When you follow the steps I've just shown you, it creates the physical structure you designed but nothing else.

So for example, if you take a look here at one of the tables I created you will note that the primary key of the table, ID, is a sequence generated primary key that is populated by a BEFORE INSERT trigger. When I went to test data input with my newly created ADF business components I began getting errors because these weren't present. Luckily this was a simple problem to resolve.

All I had to do was to slide back over to my application navigator, highlight the sequences and triggers that I wanted (Ctrl-clicking my way down the list), and then right-click on them and select Generate To->[database connection name].

This then created the necessary sequences and triggers for me and life was grand. A simple thing, but something that was obviously easy to overlook even after doing it so many times. Luckily since I was using JDev I was able to identify, resolve, and verify my fix in less time than it took me to write this post!