1. Home
  2. Tutorials
  3. Databases
  4. SQL Workbench
Yolinux.com Tutorial

SQL Workbench/J: SQL GUI Database Client

SQL Workbench/J installation and use: SQL Workbench/J is a SQL database GUI interface tool for numerous SQL databases including PostgreSQL, Oracle, Apache Derby, hSQLdb (JBOSS), IBM DB2, Ingres, MySQL, Microsoft SQL server and Teradata. It is Java based and thus cross platform and universal to all computer systems which can run Java GUI programs. This tutorial will cover the use of SQL Workbench/J with a MySQL database.

Note that this is not the Oracle MySQL Workbench database tool.
For more on this tool see our Oracle MySQL Workbench tutorial.

Description:

SQL Workbench/J is a Java GUI admin client for SQL databases including MySQL. It allows easy point and click dicovery and investigation of any MySQL database.

SQL Workbench/J Installation for use with MySQL:

SQL Workbench/J installation on Linux and Microsoft Windows platforms.

  1. Install Java: SQL Workbench/J is a Java program and thus requires installation of the Java runtime and virtual machine. Java 1.6 is required.
    download Java
  2. Download SQL Workbench/J: download
  3. Download the MySQL JDBC driver: download
    Java JDBC library to connect to the MySQL database.


Linux:

  1. Install Java. See the YoLinux.com Java installation and configuration Tutorial
    Also set Java CLASSPATH environment variable (as per the instructions)
  2. Unzip or untar (tar -d file.tar.gz) each download.
    • unzip Workbench-Build110.zip

  3. Set Java CLASSPATH environment variable to include the JDBC driver.
  4. Start: ./sqlworkbench.sh


Microsoft Windows:

  1. Unzip each download.

  2. Set Java CLASSPATH environment variable:

    Select "Environment Variables"

    Edit the CLASSPATH

    C:\Program Files\Java\jre1.6.0_20\lib\rt.jar; C:\Program Files\Java\jdk1.6.0_20\lib\tools.jar;.\

  3. Start SQL Workbench/J:

    Double-click "SQLWorkbench.exe" (or if your system is configured properly, "sqlworkbench.jar")

SQL Workbench/J Configuration:

One must configure SQL Workbench/J to attach to the MySQL database.

  1. Configure SQL Workbench/J to use the Java JDBC MySQL database drivers:

    Select "File + Manage Drives ..."
    Be sure to change the default jdbc:mysql://hostname:port/name_of_database to your actual database configuration where the "hostname" is the network node name, its' IP address or "localhost" if the database is running locally.
    The port is probably the MySQL default "3306".
    Select Library by choosing the Library file and specifying the Java Classname.

  2. Configure the database connection:

    Select "File + Connect Window"
    Here the URL must be edited to reflect the hostname, port and database name of the MySQL database server.

The MySQL database can be configured for remote client access by both granting remote access to the database user (all hosts "%") explicity and the MySQL "bind-address" configuration in /etc/my.cnf.
For more see the YoLinux.com Linux and MySQL Tutorial.

Using SQL Workbench/J:

View Database Schema and Contents:
  • Select from the tool bar: Tools + Show Database Explorer
  • Select database from pull-down menu
  • Click a table "NAME" to view schema in next panel
  • Left click on table "NAME" and select "Pull SELECT into..." + New Tab
    This generates the SQL query to view table.
  • Select top left arrow icon
    Results panel will fill with query results

Insert / Delete a record in a table:
  • Adding a record to the database, perform the above to view current table contents.
  • In the results window, right click on the results table and select "Insert Row"
    This will generate a row which you can edit.
  • Edit fields to add data.
  • Right click on record to insert and select "Save changes to database".
    This will have added the new record.
  • To delete a record, right click on record and select "Delete row".
    You must follow up with Right click on table and select "Save changes to database".
    Only then is the record deleted.
    (this option will be dimmed and unavailable if there are no changes to "save".)

Links:

Books:

book cover image "MySQL, Second edition"
by Paul DuBois
ISBN # 0735712123, New Riders Publishing

Amazon.com
book cover image "Managing and Using MySQL"
by George Reese, Randy Jay Yarger, Tim King
ISBN # 0596002114, O'Reilly

Amazon.com
book cover image "MySQL Cookbook"
by Paul DuBois
ISBN # 0596001452, O'Reilly

Amazon.com
book cover image "High Performance MySQL"
by Jeremy D. Zawodny, Derek J. Balling
ISBN # 0596003064, O'Reilly

Amazon.com
book cover image "The Definitive Guide to MySQL, Second Edition"
by Michael Kofler
ISBN # 1590591445, APress

Amazon.com
book cover image "MySQL Certification Study Guide"
by Paul Dubois, Stefan Hinz, Carsten Pedersen
ISBN # 0672326329, Sams

Amazon.com