Working with databases in Eclipse using QuantumDB
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 http://quantum.sourceforge.net/
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:
- QuantumDB for Name field.
- http://quantum.sourceforge.net/update-site for Location field.
As in the following screen:
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:
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:
Figure: Review all items before installing
Click Next. On the Review Licenses screen, select I accept the terms of the license agreements:
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:
Figure: Installation progress
During the installation, Eclipse will pop up a warning:
Figure: Security warning
Click OK to continue. After the installation completed, Eclipse will ask to restart the IDE:
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:
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:
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:
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.
Figure: Select a driver class name
The New JDBC Driver dialog should look like the following screenshot:
Figure: Setup a JDBC driver
Click Finish. The driver name com.mysql.jdbc.Driver is added to the New Bookmark dialog:
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:
Figure: Specify database connection details
After typing the required information, click Next. In the next screen, you will provide a name for the bookmark:
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:
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:
Figure: Connect to the database
The plug-in connects to the database and displays the structures in tree format, like the following screenshot:
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:
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:
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.
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:
Figure: Context commands for a result set
However, the plug-in does not allow you to alter table structure, just view.
The plug-in comes with some other views, to see the complete list, select Window -> Show View -> Other… and expand the QuantumDB branch:
Figure: All views of QuantumDB perspective
Disconnect from database
After finish working with the database, right click on the database bookmark name, select Disconnect.