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.
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
table1) - 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.
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.
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.
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.
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.