View RSS Feed


Working with databases in Eclipse using QuantumDB

Rate this Entry
by , 02-05-2012 at 01:32 PM (8884 Views)
Database is an important part of a software program. There are a number of tools/editors for popular RMDBS such as MySQL Query Browser, SQL Server Management Studio, TOAD… for database administrators and/or developers. For those who are working with Java and database using Eclipse IDE, a plug-in for database integrated in an IDE would be a necessary tool, and QuantumDB is such a plug-in. It is easy to setup and helps developers working with the most popular database systems smoothly.

In summary, QuantumDB can work with various database systems: DB2, HSQLDB, MySQL, Oracle, Microsoft SQL Server … using standard JDBC drivers. It allows developer to review database structures like schemas, tables, views, sequences, constraints…; execute SQL statements against the database like inserts, updates, deletes…

Install QuantumDB for Eclipse

QuantumDB can be downloaded separately or installed as an Eclipse’s plug-in. The home page is
Select Help -> Install New Software from Eclipse’s main menu. In the Install dialog, click Add button on the right of the drop-down list Work with, in the dialog Add Repository, type the following information:

As in the following screen:

Name:  install - add repository.PNG
Views: 2971
Size:  36.9 KB
Figure: Install QuantumDB via Eclipse's Software Update feature

Click OK, Eclipse takes a while to load some information about the plug-in and display it in the list:

Name:  install 2.PNG
Views: 2815
Size:  32.5 KB
Figure: Select features to be installed

Here we see three items, click Select All to install all features of the plug-in. Then click Next. It may take a while for Eclipse to calculate the required disk space to install the plug-in. The following screen will be displayed afterward:

Name:  install 3.PNG
Views: 2797
Size:  29.3 KB
Figure: Review all items before installing

Click Next. On the Review Licenses screen, select I accept the terms of the license agreements:

Name:  install - review license.PNG
Views: 2810
Size:  29.8 KB
Figure: Accept the terms and license agreement

Then click Finish. The download and installation may take a few minutes to complete, depending on your network speed:

Name:  installing software.PNG
Views: 2832
Size:  11.3 KB
Figure: Installation progress

During the installation, Eclipse will pop up a warning:

Name:  security warning.PNG
Views: 2790
Size:  9.7 KB
Figure: Security warning

Click OK to continue. After the installation completed, Eclipse will ask to restart the IDE:

Name:  software update.PNG
Views: 2800
Size:  9.9 KB
Figure: A restart required after installation

Click Restart Now to restart the IDE.

Working in QuantumDB Perspective

The plug-in provides a perspective – the Quantum DB perspective which can be opened by selecting Window -> Open Perspective -> Other… Select Quantum DB from the Open Perspective dialog:

Name:  open perspective.PNG
Views: 2798
Size:  17.7 KB
Figure: A new perspective available for QuantumDB in Eclipse

The Quantum DB perspective displays the following views by default:
  • Database Bookmarks: manages database connections as bookmarks.
  • Quantum SQL Queries View: edit and execute SQL queries.
  • Quantum Table View: displays result sets made by the queries.
  • Quantum SQL Log: shows SQL queries history and other logging information.

The following screenshot shows the Quantum DB perspective in Eclipse:

Name:  Quantum DB perspective.PNG
Views: 2934
Size:  61.5 KB
Figure: The QuantumDB perspective in Eclipse IDE

Connect to a database

Making connection to a database is the first and important task. Quantum DB makes this easy by the Database Bookmarks view, as you can define the connections once and re-use them later, just like bookmarks. Another important thing is, QuantumDB uses standard JDBC drivers to work with database, so you must have JAR files for those drivers ready on your computer, such as mysql-connector-java.jar for MySQL, or ojdbc.jar for Oracle. In this article we will make connection to a MySQL database.

In the Database Bookmarks view, click on New Bookmark icon:

Name:  new bookmark.PNG
Views: 2782
Size:  4.6 KB
Figure: Add new database bookmark

In the New Bookmark dialog, click Add Driver… The New JDBC Driver dialog appears, do the followings:

  • Click Add External Jar… to select the JDBC driver’s JAR file, such as mysql-connector-java-5.1.7-bin.jar for MySQL database.
  • Select MySQL in the dropdown Type.
  • Click Browse to select driver’s class name, com.mysql.jdbc.Driver, for MySQL.

Name:  select a driver.PNG
Views: 2772
Size:  8.2 KB
Figure: Select a driver class name

The New JDBC Driver dialog should look like the following screenshot:

Name:  new JDBC Driver.PNG
Views: 2841
Size:  14.6 KB
Figure: Setup a JDBC driver

Click Finish. The driver name com.mysql.jdbc.Driver is added to the New Bookmark dialog:

Name:  new bookmark 2.PNG
Views: 2799
Size:  24.9 KB
Figure: A new driver is added

Select the com.mysql.jdbc.Driver name, click Next. In the next screen, you need to provide detail information to connect to the database, such as userid, password, host name, port, and database name:

Name:  connection details.PNG
Views: 2808
Size:  24.7 KB
Figure: Specify database connection details

After typing the required information, click Next. In the next screen, you will provide a name for the bookmark:

Name:  bookmark name.PNG
Views: 2799
Size:  21.0 KB
Figure: Name for the database bookmark

Click Next. In the last screen, you are asked to select which schemas should be displayed, select Show all schemas:

Name:  schemas.PNG
Views: 2776
Size:  22.2 KB
Figure: Select which schema should be displayed

Click Finish. The new bookmark, MySQL, is added to the Database Bookmarks view. Right click (or double left click) on the bookmark to connect to the database:

Name:  connect.PNG
Views: 2770
Size:  7.9 KB
Figure: Connect to the database

The plug-in connects to the database and displays the structures in tree format, like the following screenshot:

Name:  tree structure.PNG
Views: 2906
Size:  16.1 KB
Figure: The database tree structure

From this well-organized tree, you can perform various operations against database objects such as tables, views, procedures… by the context menu displayed when clicking on the object name. The output will be displayed as a tab in the Quantum Table View, and the logging information will be displayed in the Quantum SQL Log view.

Working with database tables

Most of the time you will be working with database tables, so the following list describes the most common and useful operations against tables:

  • Query all data of a table: Right click on the table name, select View Table (or left double click on the table name), the result set is displayed in the Quantum Table View:

    Name:  quantum table view.PNG
Views: 2775
Size:  13.9 KB
    Figure: Query all table data
  • View detailed structure of a table: Right click on the table name, select View Table Details. The structure is displayed like this:

    Name:  table details.PNG
Views: 2750
Size:  10.3 KB
    Figure: View table structure in details
  • Quick delete all rows in a table: select SQL Statements -> Delete All Rows from the table’s context menu.
  • Quick drop a table: select SQL Statements -> Drop Table/View/Sequence… from the table’s context menu.
  • Export table structure: select Copy from the context menu, the XML structure data will be copied into clipboard. This feature is useful if you are working with DbUnit testing framework.
  • Export table data to XML catalog: Select Export from the context menu, in the Export dialog, select XML -> XML Catalog. This feature is useful if you are working with DbUnit testing framework.

Working with Quantum SQL Queries View

This view allows developers to type in queries in SQL language and execute them against the database. There are two main ways to execute queries:

  • Execute all statements in the editor: Do not select any statement, click on the Execute button
  • Execute a specific statement: Select the entire statement, then click Execute button.

You can also execute the statements from the context menu displayed when right clicking either on the selected statement or in the editor.

Name:  execute statement.PNG
Views: 2791
Size:  19.2 KB
Figure: The SQL Queries view

Working with Quantum Table View

This view is not only to display data. It also allows developers to do some common tasks for a result set: Insert, Update, Delete, Export… All these commands can be found from the context menu:

Name:  working with result set.PNG
Views: 2802
Size:  15.7 KB
Figure: Context commands for a result set

However, the plug-in does not allow you to alter table structure, just view.

Other views

The plug-in comes with some other views, to see the complete list, select Window -> Show View -> Other… and expand the QuantumDB branch:

Name:  quantum views.PNG
Views: 2772
Size:  13.6 KB
Figure: All views of QuantumDB perspective

Disconnect from database

After finish working with the database, right click on the database bookmark name, select Disconnect.

Submit "Working with databases in Eclipse using QuantumDB" to Facebook Submit "Working with databases in Eclipse using QuantumDB" to Digg Submit "Working with databases in Eclipse using QuantumDB" to Submit "Working with databases in Eclipse using QuantumDB" to StumbleUpon Submit "Working with databases in Eclipse using QuantumDB" to Google