Embedded Database for NetBeans Platform CRUD Tutorial

Below are some notes from Ashwin Rao about how to change the NetBeans Platform CRUD Tutorial to used an embedded database, instead of one that is external.

Got the embedded DB working. As you can see from the screen shot, the DBManager folder has an extra CustDB folder that contains the embedded database. Since I use a "Drop and Create" table strategy, at every build, the table is created afresh and not data exists. I need to implement the create part of the tutorial. I think I need to just use the "create" table strategy, but will figure that out soon, I guess.

Here is my attempt on capturing this. A bit of a disclaimer first. The steps are relatively simple if you know what to do exactly, but I must admit making some rookie mistakes and then trying to recover, which made my effort more laborious that it needed be. But I will admit that I probably learnt a bit more by straying of the beaten path and then trying to recover, than if I had somehow followed the required steps to the tee.

  • Creating the embedded database (New section prior to "Creating the Entity Classes")
    1. Capture the Table schema/structure from the sample database (alternatively point to a preprepared table schema/structure "grab" file that we can provide)
      1. Connect to the sample database
      2. Drill down to "customer" table node
      3. Select "Grab Structure" from context menu
      4. Specify file location to store the "grab" file
      5. Repeat the above steps for the "discount_code" table (since there is a foreign key relationship between the two tables. As a feature enhancement idea, it would be nice if this action were to recognise that there are dependent tables and automatically grabbed the structure for the related tables).
    2. Create new database connection
      1. Right click the top level "Databases" node to get the context menu and then select "New Connection..."
      2. In the "New Database Connection" dialog box, select "Java DB (Embedded)" for "Driver Name"
      3. In the "Database" field provide "" for ex: "CustDB"
      4. In the "User Name" field provide "app". To simplify things later on, it is important that the user name is the same as the default schema created in the database, which is "app". Basically JPA by default will look for a schema name with the same name as the user name if you don't explicitly indicate a schema to use
      5. In the "Password" filed provide "" for ex "app"
      6. In the "Additional Props" field enter "create=true"
      7. If you select the check box for "Show JDBC URL" it should now show something like this: "jdbc:derby:CustDB;create=true"
      8. Click "OK" to close the dialog box and create the new db connection.
      9. Right click the new connection and select "Properties" from the context menu
      10. The "Driver" field in the properties window should show "org.apache.derby.jdbc.EmbeddedDriver"
    3. Add tables into the database
      1. Drill down to the "Tables" node in the newly created database connection and select "Recreate Tables..." from the context menu
      2. Select the "" file and select "Choose" to create the customer table
      3. Repeat the above step for the "discount_code" table
    4. Create the relationship key between Customer table and Discount_Code table. (This is required since the grab of the tables did not capture the foreign key relationship info)
      1. Select the "Customer" table
      2. Right click and select "Execute Command" from context menu and paste the SQL code below and click execute to add the foreign key relationship ALTER TABLE APP.CUSTOMER ADD CONSTRAINT FOREIGNKEY_DISCOUNT_CODE FOREIGN KEY (DISCOUNT_CODE) REFERENCES APP.DISCOUNT_CODE(DISCOUNT_CODE);
  • Creating the Entity Classes
    1. Follow the tutorial as is choosing the table from the embedded db connection instead of the server connection.
  • Wrapping the Entity Class JAR in a module
  • Creating other related modules
    1. The EclipseLink wrapped library remains the same as before.
    2. For the wrapped library that woul choose the "derby.jar" found in the glassfish server install area within the NetBeans install (could also be in the JDK, but it was not the case on my Mac), instead of the "derbyclient.jar". The "EmbeddedDriver" class is found in this jar
  • Replace wrapped "derbyclient" module in DBManager with the newly wrapped "derby.jar" module
    1. Ensure that you do a clean and build all, before you replace.
  • Replace "CustomerLibrary" module with the new module containing entity classes created from the embedded database - for ex "CustomerLibraryEmb"
    1. Again ensure that you have done a clean all and build all, prior to replacing the modules
  • Reset the module dependencies
    1. The new "CustomerLibraryEmb" module should depend on the new wrapped "derby.jar" and the existing "EclipseLink" modules
    2. The "CustomerEditor" and "CustomerViewer" modules should depend on the new "CustomerLibraryEmb" module
  • Modify the persistence code
    1. "If necessary", ensure that the code where you retrieve the EntityManager uses the right Persistence Unit. Chances are that when you created the module for the new entity classes you would have given a different name like "CustomerLibraryEmb" which would result in a PU named "CustomerLibraryEmbPU", unless you ensured that the a persistence unit with the same name as in the original library was created.
    2. The code in question is in the "TopComponent" constructor of the CustomerViewer and in the Save Cookie implementation.

    Hopefully I have captured all the nuances. The main thing is really in recreating the table structure in the embedded database.

  • Comments:

    Hello, this was very helpful. But I am getting some errors, I think maybe because I am trying to load Oracle entity and jdbc classes in my NB RCP app at the same time I am trying to load the Embidded JavaDB entity and jdbc classes.

    It says "Note: NetBeans forbids ambiguous delegations. (Issue 118020) If a class could be loaded from two (or more) places, it will not be loaded at all." In this article... http://wiki.netbeans.org/DevFaqModuleCCE

    Is it possible to use both, Oracle and the Embedded JavaDB in a NB RCP at the same time?

    Thanks for any help!

    Posted by Tom on March 25, 2011 at 09:55 AM PDT #

    ps: one more thing I should mention, I am trying to make an Oracle and embedded JavaDB connection in the same module, not seperate modules in a NB RCP app.

    I don't think the errors would occur if I had Oracle and JavaBD connections in seperate modules, but I need both connections in the same module.

    Thanks again... the NetBeans Platform is awesome!

    Posted by Tom on March 26, 2011 at 11:45 AM PDT #

    Really impossible to know what the problem is based on your description. I don't know what you mean by using multiple databases at the same time. What does "at the same time" mean? What is the architecture of your application? Why are you using two databases? And let's not use this blog to discuss this -- join the mailing list dev @ platform netbeans org and ask your questions there.

    Posted by Geertjan Wielenga on March 26, 2011 at 08:23 PM PDT #

    Hi Geertjan, Thank you very much for your great tutorials. I am a big fan of your blog. However, I'd like to clarify one point in this Embedded JavaDB tutorial. We need to add the following line: <property name="eclipselink.ddl-generation" value="drop-and-create-tables"/> into the persistence.xml file after creating entity classes from database. Otherwise, we will get "Table/View does not exist" error message. Thanks.

    Posted by guest on May 21, 2011 at 05:46 PM PDT #

    Hi Geertjan, Thank you very much for your great tutorials. I am a big fan of your blog. However, I'd like to clarify one point in this Embedded JavaDB tutorial. We need to add the following line: <property name="eclipselink.ddl-generation" value="drop-and-create-tables"/> into the persistence.xml file after creating entity classes from database. Otherwise, we will get "Table/View does not exist" error message. Thanks.

    Posted by guest on May 21, 2011 at 05:46 PM PDT #

    hmh just cant get it to work, keep getting NUllpointerException on this line : result=WindowManager.getDefault().findTopComponent("CustomerTopComponent").getLookup().lookupResult(Employees.class);

    Posted by guest on June 01, 2011 at 05:53 PM PDT #

    Hi,

    I'm getting this error:

    Internal Exception: java.sql.SQLException: No suitable driver found for jdbc:derby:CustDB

    However, I have the derby.jar wrapped in a module and set the dependencies corresponding to the information in this (and the original) article.

    Any idea?

    Thank you for this article - rico

    Posted by Rico Leuthold on July 14, 2011 at 02:14 AM PDT #

    Hi Geertjan,

    Great post, it is working nicely.

    But now when I package the app to installer, it does not include the embedded database.

    When I use the installer it does not create the embedded database.

    Anything in particular I need to do to enable that?

    Thanks.

    Posted by Tom Van Schoor on August 22, 2011 at 04:16 AM PDT #

    Ok solved my package as installer problem.

    My database was named the same as my project and so was not created due to a name conflict.

    After renaming my DB, the installer creates the embedded DB without problems.

    Cheers!

    Posted by Tom Van Schoor on August 22, 2011 at 04:36 AM PDT #

    Hi,

    I tried the original CRUD tutorial, but my Netbeans (7.0, updated to 7.0.1) seems not to have the sample database.

    Is it possible to recover/download & re-install it?

    Many thanks,

    Matthew

    Posted by Matthew on November 09, 2011 at 12:27 AM PST #

    I must have clobbered it somehow. I tried it on another PC, and it all works fine.

    Still, it would be nice to be able to get it back somehow...if I do it again in my newby-ness.

    Cheers :)

    Posted by Matthew on November 10, 2011 at 07:39 PM PST #

    Hi Geertjan,
    I am trying to using derby EmbeddedDriver and getting the error:
    "Table/View 'TableName' does not exist".
    The steps taken by me to use derby EmbeddedDriver are:
    1. I had created a table with name "college" in the sample database provided in the netbeans.
    2. Then, grab its structure and save it at a location.
    3. Then, created a new database connection "EmbDatabase" with EmbeddedDriver and recreate table by using the grab structure of table "college".
    4. Created the Database Schema from the newly created database connection "EmbDatabase".
    5. Then, created the entity classes from the existing Database Schema.
    6. Then, I had edit the persistence.xml, by adding the line:
    <property name="eclipselink.ddl-generation" value="drop-and-create-tables"/>
    7. Then, run the project..
    Still, I am getting the error: "Table/View 'APP.college' does not exist".
    I am working on this from last 7 days..
    Please, help me....

    Posted by Sanjay Singh on November 19, 2011 at 02:26 AM PST #

    There is some discussions about the Table Generation Strategy where people have recommended it be set to "DROP AND CREATE". However I've had the problem where I save some data to the embedded database, close the app, and when I re-open it, the data is gone. It appears that on startup the tables are getting dropped and then re-created, thus deleting all my precious data! So my work-around is to set the Table Generation Strategy to CREATE(not Drop and Create):

    <property name="eclipselink.ddl-generation" value="create-tables"/>

    Posted by Crazy Guy on November 24, 2011 at 04:03 PM PST #

    I am trying to use MySQL in this example, but I dont know what are the replacement for derby.. my app wont work.. new to netbeans

    Posted by ha on December 08, 2011 at 12:15 PM PST #

    Hi Geertjan,

    I'm having a similar issue to Tom Van Schoor's post above however his solution does not work from me. My situation is that I can run my netbeans platfrom app out of the NB 7.2 IDE on Ubuntu 12 using an embedded derby database however when I create an installer, perform the install and run, the db folder is not created and consequently I don't get any database interaction or stack traces for troubleshooting.

    my top level project name is SimDriver, the java project that has the entities is RsprDbLib, the wrapper module is RsprDbMod and the jdbc url is: "jdbc:derby:rsprDB;create=true"

    i've created a post on stackoverflow for this:
    http://stackoverflow.com/questions/12220881/running-netbeans-platform-app-on-ubuntu-12-04-with-embedded-derby-db-and-jpa

    Any advice would be greatly appreciated.
    -James

    Posted by James M Chan on August 31, 2012 at 02:14 PM PDT #

    The best place for your question is dev@platform.netbeans.org which is where NetBeans Platform experts are found.

    Posted by Geertjan on September 01, 2012 at 12:54 AM PDT #

    Post a Comment:
    • HTML Syntax: NOT allowed
    About

    Geertjan Wielenga (@geertjanw) is a Principal Product Manager in the Oracle Developer Tools group living & working in Amsterdam. He is a Java technology enthusiast, evangelist, trainer, speaker, and writer. He blogs here daily.

    The focus of this blog is mostly on NetBeans (a development tool primarily for Java programmers), with an occasional reference to NetBeans, and sometimes diverging to topics relating to NetBeans. And then there are days when NetBeans is mentioned, just for a change.

    Search

    Archives
    « April 2014
    SunMonTueWedThuFriSat
      
    12
    13
    14
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
       
           
    Today