Database Modeling in JDeveloper: A Quick Tour

I'm finally getting around to implementing a New Year's resolution to start blogging about the Database features in Oracle's JDeveloper product. I know it's April already, but better late than never!

If you've been following blogs from other JDev staffers, you'll know that we're all currently working in teams using our own product to build various applications. This is helping us learn more about the product outside our own areas of expertise, as well as identify bugs and enhancements to make the product better.

I'm working on a team that's building a genealogy app to record your family tree. It has the catchy name of "Roots". As part of our regular work, all of the Roots team members work on the Database features in JDev, so the early part of the project involved heavy use of our own features to design the database schema.

What spurred me to start this blog was the discovery, from a number of conversations and observations, that quite a surprising proportion of Oracle Java/Database developers didn’t actually realize JDeveloper has database modeling, so I'll start with a quick introduction, with drill-down in future posts.

The two main start points for a database model are reverse-engineering from the database or built ‘top-down’ from the analysis of requirements. Top-down modeling often starts with an abstract, or logical data model in ERD or UML or some other modeling syntax before transforming to a database design for refinement and the addition of physical aspects of the model (such as indexing etc) . JDeveloper supports both, including logical modeling, but that’s a subject for another day. Let’s look at building a simple model from scratch.

To create a new database model in JDev, you use the New Gallery (I already created myself a new application & generic project):


There’s a clue in the newly create diagram as to what you do next! I’ll just build a classic 4-table order entry model. I click on the table icon in the component palette & drag it onto the diagram.

For the first object I create, I need to choose whether I want my model to be immediately implemented in the database as I build it, or whether my model should be stored as metadata to be generated to the database later. I’m going to choose the latter option because that way I can maintain versions of my model as it evolves in a repository. I’ll also choose the database I’m going to implement in the next page of the dialog:


I create the other tables in the same drag-and-drop way. Double click on a table to get the dialog to add columns, PKs, indexes etc. Some designers add the columns first then hook up the foreign keys between them in the table dialog:


JDeveloper also lets me drag and drop a foreign key from the component palette; click on the ‘many’ end first then the ‘one’ end. The mini-dialog will suggest a new column or columns to use at the ‘many’ end to represent the primary key of the ‘one’ end. You can accept that to create the columns for you or pick an existing column.


And that’s pretty much it, but notice I can choose UML or ERD notation for the FKs. As an option in the Property Inspector (View > Property Inspector if it isn’t already showing), I switched to ERD here:


I’m now ready to generate and run the DDL. All I need for that is a connection to the target database, which I can create in the Database Navigator:


You can create connections to many different database types (e.g. MySQL, DB2, Microsoft SQL Server). I’m sticking with the Oracle XE installed on my laptop. As I go through the Generate Wizard I can override the defaults. Make sure the schema is what you want it to be (it defaults from the package that contains your modeled elements). Here's my database implemented:


You can have the Generator just run the DDL or you can save it to run later. I just generated and ran it, but here’s a fragment of the generated DDL just for completeness:


I could have used a template table, for example to add the same audit columns to every table or to add a surrogate key (with a sequence & trigger to populate it automatically created). There is also reporting and querying so you can do quality checking up front (e.g. list all tables with no PK), and all the colours and fonts are configurable. In addition, there’s a range of tools for adding notes & comments & links to your diagram, and tools for auto-layout, alignment etc. Athough I’ve only built tables here I can model views procedures, triggers, and more.

I said you can also reverse engineer, so here’s an example of that. I just create a new diagram, select all the tables I want from a connection (in this example to the shipped HR demo schema), and drag them on. I get asked if I want to draw a diagram the database or to create a model, which is great if you just want to visualize a schema in situ. Most tools don’t let you do that, and instead insist you make a model outside the database, which is a pain if you just want to understand the implemented database and you’re not planning to make any significant additions to it. However, let’s say in this case I do intend to implement an extension to the database, so I take the "Copy to Project" option and I get my diagram, all laid out for me:


But I’m a bit obsessive about straight lines and about the many ends always being to the left and using bigger boxes for tables with more columns, keys and constraints to show, so let’s tidy this up.

I resize all the tables so that will display all their columns, keys and constraints (select a table, RMB, select all this type, RMB, "Optimize Shape Size > Height and Width") and re-lay them out – I prefer the left to right option, but you can play with all the options in the auto-layout drop down in the diagram toolbar, including laying out part of a diagram (by selecting the objects to be laid out). If you select none you get the whole diagram laid out:


Then I stretch a few tables so they can all connect up with straight lines. Then I select all the FKs. The easiest way to do that is to select any one FK & there’s a RMB menu item to "Select All of this Type". There’s also an option to "Straighten Lines" (select a line, RMB, "Straighten Lines"). Here is the result (with ERD notation again, just for consistency):


I realize I’ve left a few loose threads here (e.g. logical modeling, templates, reporting, repository, modeling the database itself and more realistic models – not just a few tables). I’ll be coming back to those and others in future posts.