Derby + Eclipse + Tomcat for ISYS 403

OK, I've had some serious problems!  But I've managed to figure most of them out and recorded some advice here for those going through the same problem.  I know it probably doesn't even make a whole lot of sense but if you're stuck maybe this can provide some clue for how to get it working:

The problem for me (and there could be many possible ways to do this) was because there are two different ways to run a derby server, and, subsequently, connect to a derby database.  Each method requires the use of different drivers.  You can either:

• Use an embedded driver, which causes derby to run in the same virtual machine as your application -or- 
• Use a client/server (network server) model, where the derby network server is running on your local machine on a certain port (1527, I think).  Eclipse and your Java code both connect to it.

For a web application I recommend the network server method. Technically you can use the embedded server on Eclipse and the Client server for your code if you point them at the same place for the database.

If you look in his code Dr. Albrecht's file uses the network server method of connecting.  You can tell this by two ways:

1. The driver he's loading with Class.forName is
   "org.apache.derby.jdbc.ClientDriver".  ClientDriver is the driver that lets you connect to a server on the network or on your local machine running on a different port.  The embedded Derby would say "org.apache.derby.jdbc.EmbeddedDriver".
2. The connection string is "'jdbc:derby://localhost:1527/' + DB_NAME".  In his file that comes out as "jdbc:derby://localhost:1527/ManagersMiner".  Notice how it is connecting to localhost (your computer) on port 1527.  The embedded Derby connection string would be  "'jdbc:derby:' + DB_NAME", or with this project, "jdbc:derby:ManagersMiner".

However, in the instructions when we set up the server in Eclipse you'll notice that we loaded the "Embedded" driver, not the Client driver.  When you set up the embedded driver in Eclipse you pick a file location on your computer for the database and Eclipse connects to that location.  When you start the derby network server on your machine the database location defaults to the location you started the script from, i.e. the derby folder you downloaded + "bin". 

So you see a problem here!  The embedded derby server in Eclipse is looking for a database where you told it to (probably in your home folder where it defaults to)…and the network server is serving off a database in the "bin" directory of your derby installation!

So while you can use the embedded server with Eclipse and the network server with your code, technically, I'll describe two different ways to get this working using either the network client driver for both or the embedded driver for both:

1.  Using the network client driver

To use the network driver you'll want both Eclipse and your program code to use the network driver.  Follow these steps to use the network driver.

1. Start the Derby server.  You'll need to do this anytime that you want to use your database, and if you were really running a full web server you'd want this to be on all the time.
1. You can follow these instructions.  Essentially, you start the script from the derby folder you downloaded (e.g. "db-derby-10.0.1.0-bin") + "bin".  Execute the startNetworkServer script.  That means that you find the startNetworkServer script in the derby folder you downloaded.  There are two versions of it - one for Windows, one for other operating systems.  If you're on Windows, double click on the batch file version.  Otherwise execute the other version from your terminal.  Don't close the window - leave the program running.  If you ever get a "Connection refused" error, it is probably because you forgot to start the derby network server.
2. Configure eclipse.
1. Under the database development perspective, right click on "Database Connections" and click "New".  Select Derby and give the connection a name.  I called mine "ClientDerby".  Click "Next".
2. Look up at the top, where it says, "Drivers". You'll need to add a new driver
1. To add a new driver, click the plus button to the right of the drop-down.  Choose "Derby Client JDBC Driver", the latest one (10.2).
2. Under the "Jar List" tab, remove the jar that's there and add a new one.  Find your "db-derby-10.9.1.0-bin" folder and go to the "lib" folder inside it.  Pick "derbyclient.jar".  "derbyclient.jar" is the Java code that gives Eclipse the know-how to connect to a derby database server as a client.
3. Ignore the properties tab for now
3. Change your database name.  I called mine "ManagersMiner". 
1. For your information, the Derby server that you started in step one will serve databases from where it is started.  That means that it will create the database in the "bin" folder of your "db-derby-10.9.1.0-bin". If you don't want it there you can see this link for more details.  For this project it should be fine if it's there.
4. Don't touch the username/password.  Use the defaults that are already in there.  For some reason it works.
5. Make sure to check "Save password"!
6. Check "Create database (if required)".
7. Copy the URL to your clipboard.  That's the connection string that you can pop right into your code (or Dr. Albrecht's code).
8. Click test connection.  It should say ping succeeded.  If it didn't, make sure that your derby server is started (see step 1).
9. Click "finish"
3. Configure your code.
1. In any code that you are planning on connecting to the database with, make sure you use the client driver.
1. Class.forName("org.apache.derby.jdbc.ClientDriver");
2. After you load the client driver, use the right connection string.
1. Paste the URL from before into your DriverManager.getConnection call.
1. DriverManager.getConnection("jdbc:derby://localhost:1527/ManagersMiner;create=true")
2. For the CreateDB.java file, you can use DB_NAME to tell it where to connect instead of hard-coding it in.  Optional
1. DriverManager.getConnection("jdbc:derby://localhost:1527/" + DB_NAME + ";create=true")
3. You'll notice that this has a ";create=true" on the end.  This means it will create the database if necessary.  If you get an error like "Cannot find database" it means you don't have this on the end.
4. Connect your project.
1. Make sure your project is created in Eclipse.  Go back to your "Java EE" perspective.
2. Find your "db-derby-10.9.1.0-bin" folder and go into the "lib" directory inside.  Drag the "derbyclient.jar" file from explorer/finder directly into your Eclipse project.  It will ask you if you want to copy it to your project.  You do.  Then, right click on "derbyclient.jar" in your project (at the bottom) and choose "Build Path" -> "Add to Build Path".  Now your project should be able to run!  You can use the same code in your project as Dr. Albrecht used in his to connect to the database.
3. If you ever get a ClassNotFoundException from your code on that "Class.forName" statement, you probably didn't do this step right.
5. Connect tomcat.  Now that eclipse can connect, we need tomcat to connect to the derby server!
1. Find your "db-derby-10.9.1.0-bin" folder and go into the "lib" directory inside.  Copy "derbyclient.jar" and now find your tomcat folder ("apache-tomcat-7.0.32").  Go into the "lib" directory with that as well.  Paste the derbyclient.jar from the derby folder's lib to tomcat's lib.
2. If you ever get a ClassNotFoundException from your servlet, you probably didn't do this step right.
6. In order to run CreateDB.java you might have to disconnect Eclipse from your database.  Under the Database Development, right-click on your connection ("ClientDerby") and say "disconnect".  You can connect it again the same way later.
7. Run CreateDB.java if you want right now.  It should work.
1. Verify that the data is there by going back to to the "Database Development" tab, right-clicking on your database connection "ClientDerby", clicking "Connect" if it isn't already, and then checking under "Schemas/APP/tables" to see if all the tables are old.
8. That's it!  You should now be able to connect in both your code and through eclipse.
9. See your tables from the Database Development perspective.  The top line ("ClientDerby") is your connection, the one below it ("ManagersMiner") is your database.  Your tables are under "Schemas/APP/tables".  Run SQL from the SQL scrapbook by right-clicking on your SQL statements and saying, "Execute All".  When you run SQL, you need to include "app." on the front of the table names.  So "select * from app.employee" should run properly.

For our project I strongly recommend using the client driver as described above. The following section describes using the embedded driver for reference, but you shouldn't need to follow these steps if you've followed the previous ones.

2.  Using the embedded driver

To use the embedded driver you'll want both Eclipse and your program code to use the embedded driver.  Follow these steps to use the embedded driver.

1. Configure eclipse.
1. Under the database development perspective, right click on "Database Connections" and click "New".  Select Derby and give the connection a name.  I called mine "EmbeddedDerby".  Click "Next".
2. Look up at the top, where it says, "Drivers".  If you followed Dr. Albrecht's tutorial you should have one that says "Embedded" in it.  Use that.  If you haven't followed Dr. Albrecht's tutorial yet, you need to add a new driver.
1. To add a new driver, click the plus button to the right of the drop-down.  Choose "Derby Embedded JDBC Driver", the latest one (10.2).
2. Under the "Jar List" tab, remove the jar that's there and add a new one.  Find your "db-derby-10.9.1.0-bin" folder and go to the "lib" folder inside it.  Pick "derby.jar".  "derby.jar" is the embedded derby server that Eclipse will run when it connects to your database.
3. Ignore the properties tab for now
3. Select your database location.  This is an important part.  If you're going to run the embedded Derby server with your java application, then I recommend you put it in your project's home folder.  For example, my project is names "ExecSys" in Eclipse and so my database location is: "/Users/sean/prog/workspace/ExecSys/ManagersMiner".
4. Don't put a username/password in
5. Make sure "create a database (if required)" is checked
6. Copy the URL to your clipboard.  That's the connection string that you can pop right into your code (or Dr. Albrecht's code).
7. Click test connection.  It should say ping succeeded.  If it didn't, make sure that the "ManagersMiner" folder doesn't already exist!
8. Click "finish"
2. Configure your code.
1. In any code that you are planning on connecting to the database with, make sure you use the embedded driver.
1. Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
2. After you load the embedded driver, use the right connection string.
1. Paste the URL from before into your DriverManager.getConnection call.
1. DriverManager.getConnection("jdbc:derby:/Users/sean/prog/workspace/ExecSys/ManagersMiner;create=true")
2. For the CreateDB.java file, you can use DB_NAME to tell it where to connect instead of hard-coding it in.  Optional
1. DriverManager.getConnection("jdbc:derby:/Users/sean/prog/workspace/ExecSys/" + DB_NAME + ";create=true")
3. You'll notice that this has a ";create=true" on the end.  This means it will create the database if necessary.
3. Connect your project.
1. Make sure your project is created in Eclipse.Go back to your "Java EE" perspective.
2. Find your "db-derby-10.9.1.0-bin" folder and go into the "lib" directory inside.  Drag the "derby.jar" file from explorer/finder directly into your Eclipse project.  It will ask you if you want to copy it to your project.  You do.  Then, right click on "derby.jar" in your project (at the bottom) and choose "Build Path" -> "Add to Build Path".  Now your project should be able to run!  You can use the same code in your project as Dr. Albrecht used in his to connect to the database.
3. If you ever get a ClassNotFoundException from your code on that "Class.forName" statement, you probably didn't do this step right.
4. Connect tomcat.  Now that eclipse can connect, we need tomcat to connect to it!
1. Find your "db-derby-10.9.1.0-bin" folder and go into the "lib" directory inside.  Copy "derby.jar" and now find your tomcat folder ("apache-tomcat-7.0.32").  Go into the "lib" directory with that as well.  Paste the derby.jar from the derby folder's lib to tomcat's lib.
2. If you ever get a ClassNotFoundException from your servlet, you probably didn't do this step right.
5. In order to run CreateDB.java you'll have to disconnect Eclipse from your database.  Under the Database Development, right-click on your connection ("EmbeddedDerby") and say "disconnect".  You can connect it again the same way later.
6. Run CreateDB.java right now if you want.  It should work.
1. Verify that the data is there by going back to to the "Database Development" tab, right-clicking on your database connection "EmbeddedDerby", clicking "Connect", and then checking under "Schemas/APP/tables" to see if all the tables are old.
7. That's it!  You should now be able to connect in both your code and through eclipse.
8. See your tables from the Database Development perspective.  The top line ("EmbeddedDerby") is your connection, the one below it ("ManagersMiner") is your database.  Your tables are under "Schemas/APP/tables".  Run SQL from the SQL scrapbook by right-clicking on your SQL statements and saying, "Execute All".  When you run SQL, you need to include "app." on the front of the table names.  So "select * from app.employee" should run properly.

Hopefully this helped!

Sean

P.S. Here is what I'd still love to see (and if somebody out there knows…): A way to start and stop the derby network server through Eclipse!