connecting to a file-based derby database

Question

I want to work with a file-based database using apache derby. I was wondering if anyone can carify how to connect & create this database using netbeans as an IDE. I passed through derby manuals trying to figure this one out, but all i got was "Embedded Derby JDBC Database Connection", which i was told is not a file-based approach, and either way, the connection didn't seem to work. any help would be much appreciated

Answer 1

To create one, your jdbc url will be: jdbc:derby:foo;create=true, and it will create a database called foo in the derby system directory. If you want to create one in an absolute location on your hard drive, specify an absolute path. jdbc:derby:/home/me/foo;create=true.

Once the database is created, you can connect to it with the same url, or you can drop the ;create=true part off.

You can set the derby system directory via system properties, e.g. System.setProperty("derby.system.homeSystem.setProp", "/home/bar/whatever");. I think you would need to do this before starting a derby database has been started, but I've never tried to do it afterwards. I have found that setting the derby system home for the app and specifying relative database url to work better, but that's a personal preference.

Answer 2

Most certainly the driver that you want to use is the embedded driver in derby.jar. Rather than getting into the "coding side of things" as there are enough examples running about, I kind of like to use netbeans itself to do everything up prior to starting the coding.

Since I use the embedded approach to convert wireshark / tcpdump / nmap files into database entries, I kind of use a slightly different approach.

Since I prefer to us the latest stable versions of Derby instead of the installed version I have a slightly different approach which allows me to use whatever version I desire as well as putting the database in a r/w data directory where ever I want in the file system.

1. Create: mkdir -p $HOME/opt/derby and cd ~/opt/derby.
2. Download latest version from db.apache.org and unbundle is in $HOME/opt/derby/
3. Create the symbolic link: ln -s pwd/version pwd/latest.
4. Create the symbolic link: ln -s pwd/latest pwd
5. Startup netbeans.

From here on you can do everything in netbeans:

1. Create an Ant Library and call it "ASF-Derby-Emb.": Tools->Ant Libraries->New Library.
2. In the settings panel set the classpath to /home/[loginid]/opt/derby/default/lib/derby.jar and, optionally, add in the derbytools.jar.
3. Click in on the source tab and added in the path to the source code if you have downloaded it.
4. Click on the javadoc tab and and in /home/[loginid]/opt/derby/default/javadocs

When you get into coding, just add in the ASF-Derby-Emb Library and it will be automatically copied into your 'dist.'

Now set up the JavaDB.

1. Set up the Services tab: Window->Services and select that tab.
2. Select JavaDB and right-click and stop the JavaDB that may be running.
3. Select JavaDB and right-click and click on Properties menu entry which will bring up a properties dialog.
4. Browse to the JavaDB installation directory: in $HOME/opt/derby/default.
5. Either leave the Database installation directory location as is or browse to the directory where you want to put it, but make sure you have read/write access.
6. click 'OK' and now you have a different JavaDB installation and database location.

Just by changing the database location you can put a database where ever you want it. Or you can accomplish the same thing in code as the url is: jdbc:derby://database location directory.

Any way, now I can setup my database.

1. select JavaDB and right-click and select 'Create Database' and just fillin the dialog and the database will be initialized in the directory which was set up in the properties file.
2. Create a connection: select JavaDB and then select the name of the database that has been created.

From this point there are three choices:

1. Do nothing and create the code needed to create the tables, etc.
2. Create a project and create a top-level project directory called sql and place all your sql scripts defining tables, etc. in that directory.
3. Use the 'builtin facility: Service->jdbc:derby://??? and create the tables using the facility that appears and allows you to enter the necessary sql.

Personally, (2) I find to be the best approach, initially. If you have pre-existing sql scripts with the the .sql extension the simply by opening them under the project, they will be loaded into the sql editor and can be executed there.

Alternatively (3) can be used and the resulting script saved.

Any way, much can be created before the coding begins. There are a lot of ways to do it. I would much rather have all created before coding -- it makes developing test cases much easier and using the preceding I don't have to jump in and out of netbeans to modify things.

Answer 3

When you download NetBeans 7.1.2, the "All" package you get the glassfish app server with it. After installing w/ glassfish, in the IDE you should be able to select the Services tab > Expand Databases and you should see Java DB. R. Click on Java DB and select Start Server. Then R. Click again and select Create Database. Enter the db name, user and a password. BTW i normally use APP for both the user and password because this way it also becomes the default schema and I don't have to go changing around anything for production environment.

Now in the Java DB group you should see the new database you created. R. Click on that and select connect. You should see a connection item appear under the Databases group. Expand this item and you should see the APP schema in bold indicating it is the default schema. Expand that and R. Click on the Tables select Create Table and you'll get a ui that helps you populate a table. Repeat until all your tables are created. Other ways to create tables using the ide is right click on the Tables and select Execute Command where you can run DDL to define the tables schema. This is the way i do db creation, by saving my script as an .sql file so i can delete the db and rerun it again as needed.

Here is an example of the my dbinit.sql script i use to create my tables in derby.

create table usertable (
    username varchar(128) NOT NULL CONSTRAINT USER_PK PRIMARY KEY ,
    password varchar(128) NOT NULL,
    email varchar(128) NOT NULL,
    firstname varchar(128) NOT NULL,
    lastname varchar(128) NOT NULL
);

create table grouptable(
    username varchar(128) NOT NULL,
    groupid  varchar(128) NOT NULL,
    CONSTRAINT GROUP_PK PRIMARY KEY(username, groupid),
    CONSTRAINT USER_FK FOREIGN KEY(username) REFERENCES usertable(username)
        ON DELETE CASCADE ON UPDATE RESTRICT
);

insert into usertable(username,password,firstname,lastname) 
    values ('admin', '21232f297a57a5a743894a0e4a801fc3','','');
insert into grouptable(username,groupid) values ('admin', 'USER');
insert into grouptable(username,groupid) values ('admin', 'ADMIN');

You can easily delete the database you've created by right clicking on the database, R. click on the database you wish to remove and select delete. and reuse your script to regenerate it.

Hope this helps! :)