Saturday, 25 March 2017

JDeveloper Feature: Database Navigator

Now we are accustomed with the JDeveloper and we know about one of the most used features of JDeveloper, Application Navigator. Now, we'll check another useful feature of JDeveloper, Database Navigator. Database navigator is the window, where you'll find all the available database connections. From this window, you can create a database connection, view existing database elements, delete connections and manage database objects in a visual manner, also you  will be able to import/export data from/to multiple formats. If you are not familiar with SQL also, you can complete your trivial database tasks using this feature.

To open the database navigator, you need to navigate from the menu bar - Window -> Database -> Databases.

The database navigator shows different type of connections, IDE Connections, application specific connections and Cloud connections. Let me describe the difference between them.

IDE Connection

This type of connections are available as part of the IDE itself. So, you can use them as and when required in the development stage but while deploying the application, these connections don't get exported. These connections are good for development  usage.

Application Connection

This type of connections are available in each application. When an application is open, you can see the corresponding connection in the application. If you thoroughly check the above screenshot, you will find that there are two application names present from our earlier post. We'll check this in our next section.

Cloud Connection

This type of connections are required when you have a cloud hosted Oracle Instance or DBaaS instance. This type of connection is not in the scope of this series as this is itself a major part of Cloud Computing.

Now, in this section, we'll check different operations we can perform on connections. For this, we need to first create some connections. We can use any database of our choice. For demonstration purpose, I will use the integrated JavaDB. So, you don't have to go to other applications for this purpose. To start the JavaDB Instance, you need to run the integrated WebLogic Server. To do this, click on the Start Server Instance option from Run option in the menu bar as shown in the picture,

If this is the first time you are starting server, you need to provide an administrator user id and password. This is the username, you will use to perform administrative task in WebLogic console.

For the first time it will take some time to prepare and configure the server domain. Depending on your system specification, it may take several minutes to complete. Also, you may observe lag in your system if you are running on low resources.  So, please be patient until the configuration completes.

Once the server is started, you can find the following in the log window,

Now, we are ready to create one database connection to the integrated JavaDB database. To do that, we will go to the Databases window and click on the Add button in the top left corner of Databases window, It will open a pop up to create a database connection as follows,

From here, you need to provide different configurations of this connection. I will use the Integrated Java Derby Database. For other database, you can configure this accordingly with the connection details as provided by the database administrator -

The previous screen is the default database provided with the Integrated Server. So, we can readily use it for our development purpose. If you cannot find Library class, simply click on the magnifier icon and it will show all the available Libraries, you can use the Java DB JDBC Driver for this connection. Once all the configuration is complete, simply click on Ok and it will create a connection in the Databases window for future uses. I've created the connection as IDE connection. Si, it is available through out all the applications present in the IDE, similarly you can creatte connections for a particular application as well. We'll create application connections as and when required.

Next step is to explore the connection.

If you click the expand icon the newly created connection, you can find all the available Schemas in it -

Similarly expanding any schema, you can view its related database objects

Now, if we right click on the tables, we can view different operations you can perform on it -
If we click on New Table, it will show another dialog to get the details of the table. Let's create a new table in App Schema -
Explore this window a little and you will be accustomed to it. Once you are satisfied with exploring this dialog, click on OK and it will create the table for you.
Now, if we double click on the table, it will show the database editor in the main editor pane. We can use this editor to do different database operations. Now, let's add some record in it and commit the data. Right click on the connection and click on Open SQL Worksheet

It will open a SQL Editor in the main Editor Pane with a Script Output Window, just below it.

Now, put the following SQL Command, in the editor -
insert into demo values (1, 'O');
insert into demo values (2, 'r');
insert into demo values (3, 'a');
insert into demo values (4, 'c');
insert into demo values (5, 'l');
insert into demo values (6, 'e');
commit;

Now in the top left corner of the SQL Worksheet, you can find a Play button, use the same to run the whole script at once. This will create all the data in the table.

Now, let's look at another feature where you can export all the data of a table in a long lists of formats (like html, insert query, html, pdf, csv, excel file etc.). Just right click on the table and select Export. It will open a dialog for preferences -


So, we can use this feature to share a very long table or we can use this feature to create a backup of data.

Also you might have figured out that, if you have knowledge on Standard ANSI SQL, you can work with any database using the SQL WorkSheet window. Apart from trivial tasks, you can perform administrative tasks as well.

Well, well. I know, you guys are thinking that, we have specialised database tools to do all these stuffs, why to use Oracle JDeveloper for that.

Yeah, you are right, there are lot of specialised tools to do all database stuffs in a single go. But do not forget, you are here to build Oracle ADF/SOA Applications. So,all the administrative tasks had already been performed before even you start. This is just a convenient to look or manipulate your data in a single tool  and trust me, when working with very long team, you will 90% of time ask for a mini DB to unit test your code. Once all problems are solved, you will look for integration. So, once you start working on it, you will love this feature of pre-installed database server.

Another point to mention, due to some bug in the tool, we are not able to use the declarative CRUD Operation functionality in the DB Object Viewer. You can only edit the table definitions using this Editor. But for DB Objects of Oracle, it supports full features of the tool. We'll go with another post on this part on how to effectively use the Database Navigator feature of Oracle JDeveloper with some standard Database Server Like Oracle DB.

Now, that's a lot of stuffs and requires lot of prctice to get acquainted with. So, roll your sleeves up and start practicing till I come up with the next part of it !!!