Since writing this, I’ve realised JavaDB is pretty crap. Do yourself a favour and try the decent PostgreSQL installer I found.
Glassfish v3 ships with JavaDB (aka Apache Derby aka Cloudscape).
I’ll be using this for trying out Rails and JRuby, but it’s also handy
for things like authentication via JDBC Realms.
A JavaDB database is essentially a directory that only one process can access at a time (a bit like sqlite). This can be Glassfish itself (an embedded database) or a standalone database process (that serves SQL clients over TCP/IP).
Both have pros and cons. I’ll take you through creating both.
option 1: standalone database server
You sure you want JavaDB? There are better options (sorry, can’t help it).
The main benefit to running a network server process is that it’s the only way for multiple clients to access the database simultaneously
(it’s also the only option that makes sense if you were clustering Glassfish).
If you need to create a schema before you deploy a webapp (with NetBeans or ‘rake migrate’)
you’ll have to stop Glassfish first unless you go down this route.
hypnotoad:databases $ asadmin start-database --dbhost 127.0.0.1
Database started in Network Server mode on host 127.0.0.1 and port 1527.
Could not connect to Derby Network Server on host 127.0.0.1 port 1527.
Starting database in the background.
Log redirected to /Users/dick/Applications/glassfishv3-tp2/glassfish/databases/derby.log.
Command start-database executed successfully.( –dbhost defaults to ‘0.0.0.0’ but this causes problems if you change IP . Stick to 127.0.0.1).
Next, create the connection pool (and associated database - see later).
It’s simplest to do this on the command line (partly due to bug 4889 ):
hypnotoad:databases $ asadmin create-jdbc-connection-pool \
--datasourceclassname=org.apache.derby.jdbc.ClientConnectionPoolDataSource\
--isconnectvalidatereq=true --validationmethod=meta-data \
--property user=GFv3:password=GFv3:databaseName=railsdb:\
connectionAttributes=\;create\\=true \
railspool
Command create-jdbc-connection-pool executed successfully.- The ’;create=true’ option tells JavaDB to create the ‘railsdb’ database on demand
- host:port defaults to localhost:1527
- username and password can be anything, but are required
We now ‘ping’ the pool. This checks our network connection is good, and has the side-effect
of creating the ‘railsdb’ database:
hypnotoad:databases $ asadmin ping-connection-pool railspool
Command ping-connection-pool executed successfully.
hypnotoad:databases $ ls
derby.log railsdboption 2. embed Derby in Glassfish
This is my preferred option for several reasons:
- it saves having to run 2 JVMs
- in development, I don’t mind stopping Glassfish
- for production, I want webapps to create their own schema anyway
- connection validation and authentication is no longer an issue
- sorry to bang on about it, but if you want a standalone database there are much better options
One side effect is that Glassfish is going to use more memory (especially if you have several connection pools configured). You might want to tweak your JVM .
There’s no need to ‘start-database’ in this case – just go ahead and make the pool:
hypnotoad:glassfishv3-tp2 $ asadmin create-jdbc-connection-pool \
--datasourceclassname org.apache.derby.jdbc.EmbeddedDataSource \
--property databaseName=\$\{com.sun.aas.instanceRoot\}/databases/railsdb:\
connectionAttributes=\;create\\=true \
railspool
Command create-jdbc-connection-pool executed successfully.- the different DataSource class is what makes it embedded
- provide a full path in the databaseName attribute to avoid current working directory hell
- since Glassfish is the database server, we can skip username,password and connection validation options
If we ping the pool, we can see Glassfish creates derby.log and the database dir
hypnotoad:glassfishv3-tp2 $ asadmin ping-connection-pool railspool
Command ping-connection-pool executed successfully.
hypnotoad:glassfishv3-tp2 $ tail derby.log
2008-05-15 11:12:48.770 GMT:
Booting Derby version The Apache Software Foundation – Apache Derby – 10.2.2.1 – (538595): instance c013800d-0119-ec48-424c-000001a39158
on database directory /Users/dick/Applications/glassfishv3-tp2/glassfish/domains/domain1/databases/railsdb
Database Class Loader started – derby.database.classpath=’‘
hypnotoad:glassfishv3-tp2 $Butler Lampsons mamma didn’t raise no fools
Whichever option you choose, the command to give the pool a JNDI name is the same:
hypnotoad:glassfishv3-tp2 $ asadmin create-jdbc-resource --connectionpoolid=railspool jdbc/railspool
Command create-jdbc-resource executed successfully.And we’re done. Now simply write a webapp to use the damn thing.
Originally I like your blog entry which was very informative and to the point and then I noticed that you added a mention of Java DB being pretty CRAP. Not sure why you added this without developing about it.
In your blog entry you mention that only 1 process can access a Java DB database - well this statement can be very confusing as to people thinking it is a single-user database, where it is NOT. Java DB is a multi-threaded engine which can run many user connections concurrently and in a multi-processor enviroment as well (since the JVM supports this).
It is not a multi-process database architecture which means only 1 process will server lots of concurrent user connections (like GlassFish does for instance).
Java DB has always been multi-threaded since its inception 10 years ago at Cloudscape.
Several glassfish instances can for instance access a Java DB instance running as a standalone server and in parallel. Java DB / Apache Derby is a robust and mature database with an active users and developers community.
It is not an Enterprise DB and never meant to be. On the other hands it can sustain lots of concurrent users in an embedded (server or not) and as a plain standalone server (as other multi-user databases). Thanks.
@Francois having used JavaDB I can’t recommend it, and I updated the post to make that very clear. I don’t set out to offend and of course, my opinion is worth exactly what you paid for it
JavaDB bundles with Java6, but filing bugs and googling fixes for issues it had with NetBeans and Glassfish wasted more of my time than it would have taken to drop in a JDBC jar for a ‘real’ database.
I expected both^W most of my readers to know the difference between a process and a thread (if not, then thanks for clarifying). Processes ‘locking’ JavaDBs doesn’t hurt scalability, but it does impact servicability - I want to be able to open a CLI session to a running DB.
Yes, for standalone databases that’s not an issue, but I’d use PostgreSQL for that (or ideally Oracle, run by someone else!)u.
I’m a big fan of embedded databases, but JavaDBs CLI is a bit clunky and cryptic, it’s Java only, and the ‘1 process at a time’ option is a *big* irritation. SQLite is a better fit for me.
Hope that clears things up a bit.
@Dick, Java DB (Apache Derby) is a 100% Java database that can run embedded or standalone and I’m not why you’re making Java a big case or issue when it is on the contrary a big advantage in the embedded case. Java DB is not bundled with Java 6, it is bundled with ‘Sun’ JDK 6 (not the JRE).
Java DB is a ‘real’ Database and not a toy one. Like I said it is not an Enterprise DB but it is robust and can stand a lot of concurrent users, running embedded or in server mode. SQLite is a great database but it does not scale as good as Java DB when running embedded and was not developed to serve that purpose either.
Java DB client interface is JDBC which is a standard Java interface used by other databases as well. Not sure why you’re saying it is clunky and cryptic. It is not perfect and has its issues like other CLIs found in other databases.
Again, I don’t understand your issue with 1 process at a time. You can have one embedded Java DB instance / engine serving several databases in one JVM - you can connect to this embedded DB using the embedded driver or the client one (TCP stack) by enabling remote access as Java DB is running embedded.
There is a protection lock file to prevent 2 Java DB instances from accessing the same database and causing corruptions (are you talking about this lock file?). If a database could be accessed by 2 different DB ‘engine’ instances, it would have to be a cluster topology and Java DB does not support a shared-disk clustering topology (e.g. like Oracle RAC), again it is not meant to be enterprise.
You can still access a Java DB database through different applications / connections via the client driver, even when Java DB is running embedded.
@Francois think we still have our wires crossed.
I never said JavaDB was a toy; I’m sure it’s robust, I just personally found it awkward to use for development. I don’t think I’m your target audience
The CLI I’m talking about is ij . I often need a way to run SQL by hand against the database, and ij is really basic compared to psql, mysql or ’sqlite file.db’.
‘Java only’ is an issue for me. Learning a DB is a big time investment and I’d like to be able to apply that knowledge to all the languages I code in.
Bundling a DB with Java is a great idea, but I found a lot of apps I wanted to use had quirks with it (usually because most of their testers use mysql, I get the same trouble with postgres).
The only JavaDB feature that I see as a flaw (as opposed to just a design decision that doesn’t fit how I work) is the process locking issue. Can you explain your last point about locking a bit further? You’re probably the best person to confirm this
With SQLite, I *can* have 2 db ‘engines’ (the sqlite CLI and my Rails or Merb webapp) using the same DBfile. I don’t think that’s true for ij and a Glassfish webapp, and that’s my problem with it.