Java Database application using JavaDB – Part 2

In the first part of this tutorial, I discussed  how to work with Java DB inside Netbeans and basically how we are going to manually perform database operations. In this section I will show you the way a real java application initiates a Java DB database connection and use for data manipulation within the application.
First you should remove the database connection  if it  already has a connection.
1. Expand the Database Explorer in the Services window and find your database.
2. Have a look at your database connection node. In my case it is jdbc:derby://localhost:1527/IDDB [ on APP]
3. Check whether the node icon is displayed as broken node. Then  it’s disconnected. Otherwise you need to right click on the node and select Disconnect. Obviously it is our application that should initiate this connection during its execution. (Actually it is not necessary to disconnect the connection if it’s already connected. But we can understand what happens behind the scene)
4. Next you should start the Java DB server if it is not started yet. This is because Server should be able to listen for a database connection request made by applications. To start the server, right click on Java DB in the Services window. From the menu that appears, click Start Server. If your firewall needs your permission for this, you should allow the connection.
5. Then you need to add a client driver so that driver manager can make a connection with our database. This driver is sprcific to each database type
eg. mysql,Java DB
Accordingly, you are going to add the driver that supports Java DB type database.
In Projects tab right click on  Libraries and select Add JAR/Folder. In a Windoes, you can find the driver at
C:\Program Files\Sun\JavaDB\lib\derbyclient.jar
Locate this jar file and click Open. It will appear in your Libraries folder.

5. Enter the following code into your main method.

 public static void main(String[] args)
 {
 try
 {
 String url = "jdbc:derby://localhost:1527/IDDB";
 String username = "";
 String password = "";
 Connection con = DriverManager.getConnection(url ,username, password);
 }
 catch(SQLException ex)
 {
 System.out.println(ex.getMessage());
 }
 }

In the above code, we have created a connection object for our database.
If everything is OK Run the project.

If you encounter following errors please go back and recheck above steps carefully.
“java.net.ConnectException : Error connecting to server localhost on port 1527 with message Connection refused: connect.” >>
There ‘s a problem with connecting to the server.

“No suitable driver found for jdbc:derby://localhost:1527/Employees” >>
There ‘s a problem with adding derby driver.

6. Now you have the database connection. Threfore you can write SQL queries for manipulating data in the database.
First  we try to pull all the data from the table. It will be easier in your side to have some records in your database table.
Enter the following code.

Statement stmt = con.createStatement();
 ResultSet rs = stmt.executeQuery("SELECT * FROM TBL_ACCOUNTS");

while(rs.next())
 {
 int id = rs.getInt(1);
 String uname = rs.getString(2);
 String pw = rs.getString(3);
 System.out.println(id + " " + uname + " " + pw);

}

You should have a Statement object to run SQL queries.
In Statement class, there ‘s a bunch of methods such as execute(), executeQuery(), executeUpdate()
In this example we need to pull  data from the table. We need to use a SELECT query. So the preferred method is the executeQuery() which returns a resultSet object.
Now you should extract whatever you need from the ResultSet.

Here comes the Cursor concept. Cursor is a pointer to record in a table. But the idea is to identify a particular row in your table. We can control the position of the Cursor with the help of some predefined methods.

Just think the ResultSet as a table and there ‘s the Cursor pointing to one of its record. At first, cursor points to just before the first record when the data is loaded. The first call to next() causes the Cursoe to move the first record.  The second call moves the Cursor to second row and so on.

ResultSet class provides methods to extract the data  from each row.

In this way you can build a CRUD application which accesses Java DB as its database server. I ‘ll complete this tutorial series with  my next post which includes a complete CRUD application.
Happy coding!

 

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