Netbeans + Visual Paradigm = EJB Tutorial (Part 3)

In part 1 of this tutorial we created an EJB using Netbeans and Visual Paradigm, in part 2 a little ZK application to read data using the EJB. In part 3 we will move away from the Derby DB to PostgreSQL and Oracle DB and challenge ourselves with identifier more than 30 characters, which is an issue for Oracle (yes, it is 2011). We will add columns with more than 30 characters and play with a few different column types (the ones showing up in a normal DB layout).

  • Add new fields to the ERD
    this_is_a_very_long_remark_field
    floatcol
    numbercol

    Updated ERD

  • Synchronize to class diagram

    Class diagram

  • Update Netbeans Project
    Since we only added columns, that does not create a problem.
    Try:
    Lets change the name of one column above and go through the same procedure again, no problem too.
    Delete a column and same procedure again, no problem.
    (Seems the VP team fixed this, in earlier version deleted columns would not disappear from the code.)
    Created sourcecode
  • Setup the required DB’s in VP
    You need to tick the DB’s otherwise you cant create a script.
    If we dont connect directly to the DB instance we dont need ID, driver, etc

    Database Configuration

     

  • Create scripts to create the DB
    (I prefer this over the direct connection to a DB as VP feature)
    Go to your ERD diagram, dont select anything, right-click, Utilities, Generate SQL.

    Create SQL

    Oracle Script

    CREATE TABLE "customer_name" (
      "last_name"                            varchar2(255),
      "first_name"                           varchar2(255),
      "entrydate"                            date,
      "last_contact"                         timestamp,
      "customer_uuid"                        varchar2(36) NOT NULL,
      "this_is_a_very_long_remark_field_too" varchar2(255),
      "floatcol"                             float(10),
      "numbercol"                            number(19),
      CONSTRAINT "pk_customer_name"
        PRIMARY KEY ("customer_uuid"));
    CREATE TABLE "customer_address" (
      "customer_city"                   char(255),
      "customer_street"                 char(255),
      "customeraddress_uuid"            char(36) NOT NULL,
      "fk_customername_customeraddress" varchar2(36) NOT NULL,
      CONSTRAINT "pl_customer_address"
        PRIMARY KEY ("customeraddress_uuid"));
    ALTER TABLE "customer_address" ADD CONSTRAINT "rel_customername_customeraddress" FOREIGN KEY ("fk_customername_customeraddress") REFERENCES "customer_name" ("customer_uuid");
    
    

    PostgreSQL Script

    CREATE TABLE "customer_name" (
      "last_name"                            varchar(255),
      "first_name"                           varchar(255),
      "entrydate"                            date,
      "last_contact"                         timestamp,
      "customer_uuid"                        varchar(36) NOT NULL,
      "this_is_a_very_long_remark_field_too" varchar(255),
      "floatcol"                             float4,
      "numbercol"                            int8,
      CONSTRAINT "pk_customer_name"
        PRIMARY KEY ("customer_uuid"));
    CREATE TABLE "customer_address" (
      "customer_city"                   char(255),
      "customer_street"                 char(255),
      "customeraddress_uuid"            char(36) NOT NULL,
      "fk_customername_customeraddress" varchar(36) NOT NULL,
      CONSTRAINT "pl_customer_address"
        PRIMARY KEY ("customeraddress_uuid"));
    ALTER TABLE "customer_address" ADD CONSTRAINT "rel_customername_customeraddress" FOREIGN KEY ("fk_customername_customeraddress") REFERENCES "customer_name" ("customer_uuid");
    
    

    On the first sight only the number and float columns differ in the script.

  • Add a schema
    The schema is not relevant to the classdiagramm or the created sourcecode, but it is important for the setup of our DB

    Schema

    The resulting scripts are slightly changed to:
    CREATE TABLE “myschema”.”customer_name” (
      “last_name”                            varchar2(255),

  • Run the SQL scripts
    (Assuming you have PostgreSQL and Oracle DB at hand, I use a Virtualbox image for this purpose, one running POstgreSQL 9.1 the other Oracle Express 10g.

    PostgreSQL with the pgAdmin III tool
    (We need to create the schema ‘myschema’ in the DB of our choice)

    Create tables

    Oracle using the APEX web admin tool
    We need to create the user ‘myschema’ !

    Create user

  • Create the tables

    Create tables

    Now we hit 2 problems:

    • ORA-00972: identifier is too long
      Due to our too long identifier
    • ORA-01918: user ‘myschema’ does not exist
      Due to Oracle creates the user in capital

    Remember, we want to serve multiple DB’s with the same ERD without doing any modifications (other than selecting the DB type when creating the script)

  • Address the ORA-00972 problem
    Oracle, even the latest version 11g, cannot handle identifier longer than 30 characters. VP helps on this when you select Oracle as DB and warns you if your name is getting too long. Since we started with PostgreSQL we didnt have to limit ourselves from creating very descriptive names though.
    Our sample column and troublemaker  ‘this_is_a_very_long_remark_field_too’shows up in

    1. ERD diagram
    2. Class diagram
    3. Created sourcecode class
    4. Meta class
    5. Facade class
      This is the class where you would implement your business logic

    While we can change easily the ERD to ‘this_is_a_short_remark_field’ and automatically get up to the meta class, our variable might be all over the place downstream the facade class.

    • The code creation creates a cosmetic hickup. The getter and setter is OK, but the internal variable remains the old one, makes the code less consistent.

      Solution: Delete the old classes (demo.Customer_name) before rerun the sync between VP and NB.
    • Our facade class is screwed
      (In a real sized application you have maybe dozens of references)

      Solution: None
      We cannot refactor because it is an unresolved variable.
      No choice, but to manually do it, or replace in the whole project, which only works if you use unique names and you are sure it is not used in any other context.

       

  • Address the ORA-01918 problem
    Just change the schema name to uppercase.

Remarks:

  • Careful naming conventions help to avoid this additional work.
  • In the next and last part we will configure Glassfish for the 2 different datasources.
Advertisements

One thought on “Netbeans + Visual Paradigm = EJB Tutorial (Part 3)

  1. Pingback: Netbeans + Visual Paradigm = EJB Tutorial (Part 4) « The JavaDude Weblog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s