There were a lot of discussions lately about inclusion of Derby (aka JavaDB) in the Sun JDK. First of all, I personally think that it will not really change anything.
I’m sure that developers that wanted embedded database until now were savvy enough to download Derby and include it in the project. One other argument that has been often used is that this will put unnecessary burden on the size of the JDK itself. While this is certainly true, with current Internet speeds extra 2MB size is not something that would worry most of the developers, and of course there are always alternatives to Sun JDK.
But instead of heating up this discussion again, I would like to write down some of my experiences with Derby. The focus will be on Derby as an embedded database since I haven’t (and don’t plan to) use Derby as a standalone enterprise database server.
Schemas
First of all, I would like to address Derby schema and authentication mechanisms. As you probably know, if you access database without authentication, default schema that will be used is APP. This is a common situation in case that you use Derby as an embedded database, since there is no need for authentication. However, even in this case you will probably want to allow your customers (or yourself) to start the application with derby in network server mode so that data could be accessed (and manipulated) with third party tools. In this scenario you will need to use authentication and also allow your users to change user/password combination for database access.
The problem with this approach is that default schema that will be used is the same as the username. So with every change of the username driver will try to use different schema. Of course, if you don’t take care of this, you will get “no such schema” exception.
Hopefully, there are few solutions to this problem:
-
Always prepend schema name to table name (for example
APP.table1instead oftable1) - this approach is a solution in most cases. It could be a problem in case that you need to rewrite your application queries. -
Don’t allow users to change username and always use
APPschema - while this would work, your users might feel restricted. -
Use
SET SCHEMA APPcommand to explicitly change schema name when connection is created - this might work in some applications, but in case that you use some kind of connection pool you must check whether your pool allows you to define query which will be executed upon connection creation. -
Make your custom authentication class - it’s not as hard as it seems. All you need to do is to implement
org.apache.derby.authentication.UserAuthenticatorinterface and use some other connection properties to authenticate your users.
This is not a big issue and as you see there are some easy workarounds, but still it would be great that straightforward tasks like this are no-brainers. The fact that this issue is in the FAQ proves that it isn’t.
Tools
Again, it’s all about helping developers (and customers) to access and manage data stored in the database. Derby provides some tools for this purpose (such as ij), but in my opinion they are not as polished as they could be.
It’s small things that make this tool hard to use. For a starters, not until you look into shell scripts you can realize that in order to successfully start ij you need to put all jars in the CLASSPATH or set it to be empty and set DERBY_INSTALL environment variable to point to Derby installation. Also, I’m not sure why this second approach is not working even if I have some classpath set on the system level.
Next, some switches wouldn’t hurt, so that we can use this tool like tools that are found in similar products (PostgreSQL, MySQL, …). For example, it would be nice to allow users to use switches to define connection parameters, instead of JDBC URL.
Online backups
It seems like from version 10.2 on Derby will support online backups that does not block update operations during backup, which is a must-have feature for these kind of applications.
Conclusion
Derby is ready-to-use embedded database that you can use in your Java applications and with all current development that is going on, I’m sure that developer experience will just get better over time.
"I'm sure that developer experience will just get better over time."
Not sure, I mean there are a series of projects out there that have been around a while, instead of improving over time they failed to attract too much attention.
I think Derby needs an infusion of developer interest. Otherwise, we'll be stuck with using ij as a tool. (Note, you can always use a tool like DBVisualizer with Derby, I've done that, at least it approximates the level of tools available for other databases).
From my own experience Derby is reliable and fast, but I agree with you assessment that there are a lot of rough points around the project.
Putting it in the JDK? Yuck, no. Please, if anything I think they should remove things from the baseline Java distributions. Why does my server-side JVN need a sound API and Swing? I'd like to see these dudes move toward a buffet-style JVM. That way I think you've see more people bundling just the parts they need.
Thanks for the blog post
> I think Derby needs an infusion of developer interest
The only good thing that can arise from putting it in the JDK is that Derby will get more popular and so attract more developer interest. But I'm afraid that's just my hope.
As for thrid-party database tools, I tend to use them (Aqua Data Studio, for example) whenever I can, but when your application is behind firewall and you can't access database from your desktop I'm afraid that ij is the only solution left.
Thanks.