One of the tools in this series that I have used the most is DBVisualizer. It is a database tool for developers and those that need to browse their data. Although the uses are plentiful, we will keep this DBVisuualizer how-to focused on the standard features and usage. A tool like this is too much to cover in an introduction so your best approach will be to use it.
A Free Version
There are two versions available, free and paid. The rough differences are that the free version is a data browser while the paid version adds a lot of administration and manipulation tools. However, you can see for yourself on their comparison page. Note that the free version does allow for a twenty-one day trial of the paid version. This is an excellent way to help you decide whether that price is worthwhile.
It is important to note that DBVisualizer is a Java application. When you install, you have the choice of installing a JDK or using one on the system. If you have installed Java on your system, then it is best just to use that. On the other hand, it is not hard to perform either option. Just accept the defaults provided.
Getting Started
The first step is to connect to a database. You can click on the connections tab and then right click on the Connections item to see a menu that includes the option to create a database connection. When you select that menu item, you will be asked if you want to use the wizard. That option is much easier. At this point, you should be ok entering the data for each step of the wizard if you understand database connection strings. Your driver is likely supported by default, and the application will build the proper connection string.
Once you have a connection created, you can test it. The output will either show a successful session creation or provide a detailed error message from the driver manager. Once you connect you can review the connection tabs to tweak it out. However, you are not likely to need to do this. You can jump right into your database.
Browsing a Database
The terminology varies by the vendor but roughly speaking there is a database that holds your tables. You can view this by expanding the children of the connection (left panel) and then opening the Databases row. A database can be double-clicked to see details about it. Alternatively, the child list can be expanded to view tables, triggers, stored procedures, views, or more. Each of these items can be expanded to see a list of that type of objects available in the DB.
Each object type is a little different, but typically a double-click will open a view into the data while expanding the child list will show some details. For example, double-click on a table name to view details about it or display the child list to see the columns.
You can quickly browse a table structure this way. However, more details are provided in the Columns tab when you double-click on the table name. There are some such tabs available in the table detail. Nevertheless, one is all I usually need. That is the Data tab. Select this tab to see the data in the table. You can filter, sort, and even edit the data as well as export a selection. If you want to quickly manipulate data, then this is the easiest way outside of using SQL.
Scripting and Administration
There can be a lot of tedium in database development and administration. One seems to continuously have to type out the column names and template text for queries. DB Visualizer makes this easy. You can right-click on a table to see a menu of options. One of these is “Script Table.” This feature makes it easy to create a starting point for a query on a table whether it is an insert, update, or delete. There are also options to send the default query text to a file, the clipboard, or your working session. This feature alone can save a lot of time throughout your tasks.
The next step from scripting queries is imports and exports. You can import or export a table or an entire database. Although the import is not going to compete with a high-end data integration or ETL tool, it is perfect for those one-off tasks we always seem to be assigned. The import can read a CSV or XLS file and scans for likely column headers then provides a wizard approach to handle the mappings.
Documentation and Design
This tool extends from general administration to a full-featured IDE as well. Whether you want help in design or the documentation of it, you can use tools for each table. The detail view of a table includes tabs for the DDL from the DB and native DDL (source) that was used to create the table. There is also a tab for references that can quickly provide a graphical view of the table within the schema and other tables it references.
The row count, grants, index, and primary key tabs are all provided for a quick view of the table properties. You are not allowed to edit these values here. Instead, you can easily adjust index and key values via the alter table dialog. On the other hand, you can dog deep into your data via the Navigator tab.
The Navigator
This feature is excellent for when you need to track down related data for a record. It allows you to start with a record and then view the related data in another table. The top portion displays a graphical representation of the relationships while the bottom provides the pertinent data in the selected table. You can take advantage of this tool while researching data issues without having to create a bunch of queries or a single, possibly highly complex, one.
Our Experience
This is a favorite application for working with databases. With the free version so easy to try out, it is hard to think of a reason not to avoid this. We think you will be happy you did so.
