Java: Derby/JavaDB Database Basics

At work today I had to do a little database troubleshooting and had to learn a little bit about JavaDB which is really the Apache Derby database. So I'm creating this post to record some of that information for future reference.



Documentation

You can download the documentation from Apache Derby Documentation Page. There are manuals for all versions of the db. NetBeans 6.8 is using Derby 10.5. If you need quick access to the SQL reference materials, there are links to the online Derby Reference in HTML. The reference contains detailed information on SQL syntax and functions.



SQL Shell

Today I really needed access to an interactive SQL shell for Derby, just like the mysql command for MySQL. One actually exists for Derby. Its call IJ and if you are using Windows and NetBeans its located in:

C:\Program Files\sges-v3\javadb\bin\ij.bat.



If you navigate to that directory, you can start up the shell by typing ij. The only thing that is a little tricky and different, is that before you can access a database, you must issue a connect command. For example, something like this:

ij> connect 'jdbc:derby://localhost:1527/dbname;create=true;user=user;password=pass;';



Once the connection is established you can issue normal SQL statements. I was attempting to change the starting value for an auto generated ID field. To reset the counter to 5, I could issue a command like this:

ALTER TABLE table-name ALTER column-name RESTART WITH 5;



Of course in the end, this was not really the problem. lol. But, issuing the command eventually nudged me in the right direction.