Java Database application using JavaDB - Part 1

Java supports varoius RDBMS in the market. If you need to connect your application with database for CRUD operations, the preferred way is to use a Database Management System. File based storage mechanism is recommended for simple data management tasks.

With regard to Java, there are many RDBMS solutions such as MySQL,Oracle,PostgreSQL,Java DB,SQLite...etc. Each solution is ideal for specific scenarios. This article demonstrates how to connect your application to Java DB(previously known as Derby).

We are going to build the application with Netbeans IDE and Java DB is a in-built database within Netbeans installation package.

In Java platform, it uses an API called JDBC for database connectivity. Then, who are going to connect your application to database?.  It's JDBC Driver Manager. This is common for most of the database connection scenarios with Java.

Create new Java Application with Netbeans and  go to Services tab.
It displays all the database engines, drivers and database connections available.


Java DB is a virtual server. We need to start the server.
Right click Java DB under databases select Start Server.
To create a database, right click Java DB and select Create Database.
You can change the database location with properties.



Newly created database will be added to the list.


Now you can connect with the database and create tables.
Right click on your database and select Connect. It will display hierarchical view of Tables,Views and Procedures for your database. Perhaps this hierarchy may appear in APP category under your database.


To create a table, right click on Tables and select Create Table. This will prompt Create Table dialog. You can enter a table name, add/remove column and define indexes in this dialog.



Unfortunately, Java DB does not support to define the auto increment feature using AUTO_INCREMENT keyword for a column. We can not set this property using the IDE interface. But still possible with writing SQL query to create your table. For that we need to drop the table and write the suitable query to create a table with an auto generated field.
To minimize the troubles , let's try to reuse what we have now. Hopefully, we can save the current table structure before dropping the table. Then you open the saved binary data file and edit the structure so that our requirement will get reflected.
(BTW, Dropping  a table causes to remove all the data within the table and we  have no other solution than this as we need to change the table structure.)
Right click on your table and select Grab Structure. This will prompt you to save it in your disk.
Next simply drop the table.
Right click on Tables and select Recreate Table to open saved structure earlier.


Click on Edit table script in the dialog appeared. Now you will be able to edit your table structure to include auto generated property. So you need to add following property for the desired column to be auto generated.

GENERATED ALWAYS AS IDENTITY(START WITH 1, INCREMENT BY 1)



Ok. Now you have achived it!

Now you can play with the table by adding some data. You can also write SQL queries for data manipulation tasks. Let's move to next section of out tutorial. That 's data management task within the application. We write some code to programmatically connect with the database and handle some database operations.

Part 2