Mort Learns JDBC for Glassfish

 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.

The Problem: 

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.

The Solution: 

Switch to JDBC Realm Authentication

The Trail

 
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

  1. use the --dbhome path as the root of all the databases  Done!
  2. else look in the current directory if there is a file named "derby.log" then make the current directory the root.
  3. 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();

Comments:

Excellent stuff.

Posted by Kedar Mhaswade on October 03, 2007 at 08:04 AM PDT #

[Trackback] Byron has a detailed post on why and how to setup JDBC Realm Authentication . It covers the use of JavaDB

Posted by Mirror on October 03, 2007 at 09:07 AM PDT #

Hi, I followed your tutorial to setup a jdbc connection pool with a matching jdbc resource in the admin console (Sun Java System Application Server under netbeans 6.1 glassfish v2). I accepted the defaults. The connection pool links to a derby database jdbc:derby://localhost:1527/filerouter. I added a resource reference: jdbc/loginpassword to my web.xml file. As well i have the jdbc-resource entry for jdbc/loginpassword and jdbc-connectionpool entry for connection pool loginpassword in my server resources sun-resources.xml. in my sun-web.xml file i have a resource reference name jdbc/loginpassowrd with JNDI name jdbc/loginpassword. I created a POJO utility class that gets the connection to the database. I have tried your code:
InitialContext ctx = new InitialContext();
DataSource ds = (DataSource) ctx.lookup("jdbc/loginpassword");
Connection connection = ds.getConnection();

When i compile and deploy the webapp i get the following error in my logs when i try to logon as a user.

javax.naming.CommunicationException: Can't find SerialContextProvider [Root exception is org.omg.CORBA.COMM_FAILURE: vmcid: SUN minor code: 201 completed: No]

I have tried establishing hashtable values in the code as well and i still get this error.

Code vers 2:

Hashtable ht = new Hashtable();

ht.put(Context.INITIAL_CONTEXT_FACTORY, "com.sun.appserv.naming.S1ASCtxFactory");

ht.put(Context.PROVIDER_URL, "iiop://127.0.0.1:3700");

try {

ctx = new InitialContext(ht);
// InitialContext ctx = new InitialContext();

javax.sql.DataSource ds = (javax.sql.DataSource)ctx.lookup("jdbc/loginpassword");

System.err.println("+++++ Exception! no Connection");

return ds.getConnection();

} catch(Exception e) {

throw new Exception("DataObjectFactory.getConnection():\\n" + e);

}

}

}
IF you have any idea why this is happening please let me know.

Posted by Shravan on November 12, 2008 at 02:29 AM PST #

This stuff can get really really messy. I recommend you go back and make it very very simple and then add complexity back to it a bit at a time.

Follow the tutorial, EXACTLY -- use the same names etc. Don't put anything into web.xml, etc.
Simply call it from a very simple servlet as in the Tutorial.

Once you have that working -- redo it with the final names that you want for pool/jndi, test the new names, delete the tutorial pool/jndi

Now start adding the web.xml stuff.

good luck!

Posted by guest on November 12, 2008 at 04:14 AM PST #

I tried following this exactly from scratch and when I hit the ping button after I created the connection pool I get an error. No Password Credential found?

Posted by Shravan on November 14, 2008 at 05:02 AM PST #

Thanks a lot! Althogh I still don't understand it.

I want my java EE app to be able to accept new users upon signing up, then they're given access to their own user.jsp,

which displays all their settings and saved data, also giving them access to a few other user.jsp pages, and while their logged in, they can save their data to my database.

After they've signed up, their userName and password will be saved to a database table, which holds their info.

Is this possible?

Of course i'd like to have a few different levels of admin access too, who's data such as userName, password, preferences are also stored in the database.

Im using NetBeans 6.5, GlassfishV2, and the database doesn't matter, but I'd prefer MySQL.

I'm pulling my hair out because I seem to run into a problem like this every few weeks, and it takes forever to solve.

If you could give me some advise I would greatly appreciate it. Thanks in advance.

Posted by john on April 30, 2009 at 04:14 PM PDT #

Sorry but I have to diss' this a bit. You are only describing how to set up and connect to a database. Where is the actual code for having a JDBC realm? THAT would have been interesting to know. Since you state that if that is what I am interested in, read on. Yes, but there is nothing about it.

An also, would JPA not be an better option to connect?

Posted by theironcprogrammer on March 13, 2010 at 10:47 PM PST #

Even with JPA, you still need connection pooling in your persistence.xml, which still require you to set up things like the way he discuss above

Posted by Thang Pham on July 22, 2010 at 03:58 AM PDT #

Where is the JDBC realm authentication? You just describe how to create a JDBC connection but not authenticate on that. This is a waste of time!

Posted by Giovanni on July 11, 2011 at 05:10 AM PDT #

I made a comment before asking you where is the realm authetication. You should put a reference in the final or in the start of the tutorial to this part, because a just find it by casualty.

Posted by Giovanni on July 11, 2011 at 05:17 AM PDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

ByronNevins

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today