Embedded Database for NetBeans Platform CRUD Tutorial
By Geertjan on Feb 26, 2011
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.
- Capture the Table schema/structure from the sample database (alternatively point to a preprepared table schema/structure "grab" file that we can provide)
- Connect to the sample database
- Drill down to "customer" table node
- Select "Grab Structure" from context menu
- Specify file location to store the "grab" file
- 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).
- Create new database connection
- Right click the top level "Databases" node to get the context menu and then select "New Connection..."
- In the "New Database Connection" dialog box, select "Java DB (Embedded)" for "Driver Name"
- In the "Database" field provide "
" for ex: "CustDB"
- 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
- In the "Password" filed provide "
" for ex "app"
- In the "Additional Props" field enter "create=true"
- If you select the check box for "Show JDBC URL" it should now show something like this: "jdbc:derby:CustDB;create=true"
- Click "OK" to close the dialog box and create the new db connection.
- Right click the new connection and select "Properties" from the context menu
- The "Driver" field in the properties window should show "org.apache.derby.jdbc.EmbeddedDriver"
- Add tables into the database
- Drill down to the "Tables" node in the newly created database connection and select "Recreate Tables..." from the context menu
- Select the "
" file and select "Choose" to create the customer table
- Repeat the above step for the "discount_code" table
- 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)
- Select the "Customer" table
- 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);
- Follow the tutorial as is choosing the table from the embedded db connection instead of the server connection.
- The EclipseLink wrapped library remains the same as before.
- 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
- Ensure that you do a clean and build all, before you replace.
- Again ensure that you have done a clean all and build all, prior to replacing the modules
- The new "CustomerLibraryEmb" module should depend on the new wrapped "derby.jar" and the existing "EclipseLink" modules
- The "CustomerEditor" and "CustomerViewer" modules should depend on the new "CustomerLibraryEmb" module
- "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.
- 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.