How to migrate HSQLDB based tutorials to MySQL and Derby on NetBeans.

First of tutorials,we had better to learn "How to create JDBC Connection pool and JDBC resource".here

We already know that If you create some Web Application using NetBeans "Visual JSF Application" Framework,then "JDBC Connection pool and JDBC resource" is created automatically.
You can see tutorials from here: Java Web Application Learning Trail

You can create a sample application using this tutorial: Using Databound Components to Access a Database
You need not complete this tutorial,only one JSF component(using database) is sufficient.
I think this is most quick way to create "JDBC Connection pool and JDBC resource".

Here,we create "JDBC Connection pool and JDBC resource" manually.
This "Know_How" is from the tutorial: LAB-4923: Spring Framework and JPA

Using this "JDBC Connection pool and JDBC resource",we can migrate ZK applications
from HSQLDB based application to MySQL based or Derby.
With this base environment we proceed to ZKtodo2.

1. ZKtodo2

Because NetBeans include Javadb,it is very useful to know how to migrate database "HSQLDB" of existing samples to "Derby"

This sample is from the next tutorial: Step by Step Tutorial - A Real-World Application with Database
But this is tutorial is old versioned .
So I downloaded from ZK Community
Here we select ZK Forge on SF.net
From this SourceForge.net Repository,we download zktodo2.tar.gz

2. ZKPetshop2

With this tutorial,we try to change database "HSQLDB" of existing samples to "MySQL"

This sample is from the Book: "ZK™: Ajax without JavaScript™ Framework" chapter8.
But this is tutorial is old versioned because fisheye component in index.zul is old.
So I downloaded from ZK Community
Here we select ZK Forge on SF.net
From this SourceForge.net Repository,we download zkPetShop2.tar.gz

Tuorial:
How to create JDBC Connection pool and JDBC resource

1. Create database.

Select Database from Service Tab and select Java DB -> Create Database.

pet4

Observe that the Create Java DB Database dialog appears.
For the Database Name field, enter ZKDERBY.
For User Name field, enter zkderby.
For Password field, enter zkderby.
Click OK.

pet5

2. Create a Database connection.
Caution!! This is old!! Database connection is created automatically.

Select Services tab window.
Right click Database and select New Connection.

pet2

Observe that the New Database Connection dialog box appears.
For the Database Host field, enter localhost.
For the Port field ,enter 1527
For User Name field, enter zkderby.
For Password field, enter zkderby.
Click OK.

pet3

3. Populate database tables.

Right click jdbc:derby://localhost:1527/ZKDERBY and select Execute Command.

pet6

Cut ane paste the contents from below to the editor area. This SQL is also provided as <zktodo2_UNZIPPED_DIRECTORY>/creaetable.sql.

CREATE TABLE "ZKDERBY"."REMINDER"
(
REMINDER_ID varchar(50) PRIMARY KEY NOT NULL,
NAME varchar(50),
PRIORITY int,
DATE date
)
;
CREATE UNIQUE INDEX SQL090324110418140 ON REMINDER(REMINDER_ID)
;

Click Run SQL button.

pet8

Expand Tables.
Observe that the tables are created.

pet9

4. Open Admin Console of the GlassFish V2 application server.

Select Services tab window.
Expand Servers.
Right click GlassFish V2.1 and select View Admin Console.

Enter values to User Name and Password fields - the default is admin for User Name and adminadmin for the Password.

5. Ceate a connection pool.

Expand Resources->JDBC.
Click Connection Pools.
Click New on the right of the page.

For the Name field, enter derby_net_ZKDerby_zkderbyPool.
For Resource Type, select javax.sql.DataSource from the drop-down menu.
For Database Vendor field, select JavaDB from the drop-down menu.
Click Next.

Scroll down to the end of the page to see the Additional Properties section.
For Connection Attributes field, enter ;create=true.
For DatabaseName field, enter ZKDERBY.
For Password field, enter zkderby.
For User field, enter zkderby.
Click Finish.

p3c

Observe that a new connection pool called derby_net_ZKDerby_zkderbyPool is created.

6. Remove SecurityMechanism property from derby_net_ZKDerby_zkderbyPool.

Selectderby_net_ZKDerby_zkderbyPool under Connection Pools node on the left.
Select Addtional Properties tab on the right and observe that the Edit Connection Pool Properties are displayed.
Check Secutiry Mechanism and click Delete Properties.

p3d

Click Save.

p3d2

Edit this as look like below and Save.

p3d2

7. Start Java DB server.

Select Database from Service Tab and select Java DB ->Start Server ..

pet1

8. Make sure derby_net_ZKDerby_zkderbyPool is operational.

Start Java DB server (if it is not started already)
Select General tab window.
Click Ping button.

p3d4

Make sure that the Ping Succeeded message is displayed on the top.

p3d4

9. Create JDBC resource.

Select JDBC Resources under JDBC on the left.
Click New button on the right.

For JNDI Name field, enter jdbc/ZKDERBY_ApacheDerby.
For Pool Name field, select derby_net_ZKDerby_zkderbyPool from the drop-down menu.
Click OK.

Observe that the jdbc/ZKDERBY_ApacheDerby JDBC resource is now added.