Mort Learns JDBC for Glassfish
By ByronNevins on Oct 02, 2007
I was intrigued by a problem and I set about solving it. I finally did solve it. It was a long difficult trail, but now that I know how to do it it seems fairly easy.
If you are interested in Glassfish, JDBC, authentication and/or realms then read on.
I now have 3 home made web modules doing useful things. They all have authorization and authentication using file-based authentication. That works fine but what if I want to add a new user? It means I have to get the information and then create the right command to get the information into the key-file. Or I figure out how to call Glassfish and have it put the username and password in for me. Also -- what if I want the user's email address? I can't put it in the key-file. What about deleting a user? Uh-oh. Sounds complicated. Plus I'm going to have to have some other data storage for the other user info. I might as well use a real database -- the database gives me an easy way to add/edit/delete users at runtime (allowing users to create their own account unattended). The database solves the problem of where to put the extra user info as well.
Switch to JDBC Realm Authentication
Using JDBC and JavaDB in GlassFish is easy. You can go from no DB at all to a formed DB ready for your first SQL commands in 10 minutes or less.
There are a few traps waiting along for the unwary and I'll reveal them along with some tips.
JavaDB Essential Information
JavaDB is based on Derby. A JavaDB database appears as a directory. The directory's name is the database name. When JavaDB opens up a database, that's it -- no other JavaDB runtime is now allowed to connect to that database.
Tip: If you have connection problems look in the directory where the database is supposed to be. Is it there?
If you use an embedded JavaDB database then what happens is that GlassFish itself is the JavaDB runtime that has that database locked. As a result you can NOT look at the database anywhere else like, e.g. a SQL command processor. If you want to issue SQL commands that way, you have to take down GlassFish to be able to access the database. That's a disadvantage. The advantages are
- it is super-secure since there are no ports open for accessing the database. GlassFish is in charge of security for accessing the database
- It is simpler to setup than the networked client
The other choice is using the Derby Network Server. This is just the classic client server database setup. Derby runs one network server instance which is the "owner" of the databases embedded into it. You can work on this database via SQL tools while GlassFish is connected to it. It's not as simple. You have to separately start and stop the JavaDB server. GlassFish makes this easy for you with an asadmin command. asadmin takes care of all the details -- environmental variables, classpath, etc.
I recommend the Network client mode. It's no big deal, it's just another command that you use before starting GlassFish. I figured out a way to make it a service in Windows as well (perhaps a future blog?)
Note: You can easily change an embedded client or a network client back and forth.
asadmin start-database -- dbhome <path-to-database-parent-dir>
Here's the first trap. You don't have to specify the --dbhome option but I highly recommend always using it.
Short Primer on Where the Heck is My Database on Disk?!?
If you run a stand-alone java program that uses embedded JavaDB, and you tell it to open a database named "foo", it will look for a directory named "foo" in the directory you ran the program from. If you start a networked server -- it's the same deal, it will look in the directory that you started the server in. GlassFish determines the all-important location of your databases on disk as follows and in this order
- use the --dbhome path as the root of all the databases Done!
- else look in the current directory if there is a file named "derby.log" then make the current directory the root.
- else use a standard location: <gf>/databases
If you don't use the --dbhome option then you need to be aware of whether or not derby.log is in your current directory.
Let's Get This JDBC Party Started Already
You need to create two items in Glassfish in order to access a database.
The first is a JDBC Connection Pool.
In the Admin GUI navigate to Resources/JDBC/Connection Pool
Set the easy stuff. Note that "name" is the name of the pool. This pool is for a network client. The configuration is a bit different for embedded databases. I have pictures at the end of this blog for the embedded case.
Here is the first page of mysterious properties. If you are an experienced DB person you'll know what to do. If you're like me you'll happily take the defaults.
This is where all the action is. You don't want to make any mistakes here. Here is the default layout:
And here is what it should look like.
Note that DataSource has no value.
Tip: set connectionAttributes to ";create=true". Now you can make GlassFish create the database for you!
And here is the new Connection Pool. Click on it and then press the ping button and the (empty) database will be created.
The next step is to create a JDBC Resource. Armed with this jndi name you can get a connection to the database at runtime.
Navigate to Resources/JDBC/JDBC Resources and press the "new" button.
That's all. Glassfish is ready to serve up database connections. From a servlet this code will do the job:
InitialContext ctx = new InitialContext();
DataSource ds = (DataSource) ctx.lookup("jdbc/userauth");
Connection connection = ds.getConnection();