Database Development in Eclipse with the Data Tools Platform (DTP)

Eclipse logo

I'm getting lazier. I mean lazy like looking for 10 minutes for a lost TV remote rather than getting off the couch to change the channel. Sure, it's not a big deal to switch back and forth between Eclipse and the MySQL Query Browser or phpMyAdmin, but those seconds add up.

In this post I'll show you how to take advantage of another part of the "I" in the Eclipse "IDE".

The Eclipse Data Tools Platform (DTP) is a standard Eclipse plugin that you can use to perform most database development and query functions. The DTP plugin supports several database platforms via JDBC, including Derby, Oracle, MS SQL, Postgres, Sybase ASA, Flat Files, XML Data, Web Services, and more. This post covers connecting to MySQL and basic DTP features.

Requirements

I'll assume that you're comfortable installing Eclipse and Eclipse plugins . I'm also assuming that you've used Eclipse for a bit and have at least one project defined. Here's what you'll need before we get started.

  • Eclipse with the DTP plugin, I'm running Eclipse 3.3 which came bundled with DTP version 1.5.0.
  • MySQL JDBC Driver, I'm using Connect/J 5.1.
  • MySQL Server version 4 or 5 running on localhost or a remote host that allows remote connections.
  • MySQL user account with a database or privileges to create databases.

Install a JDBC Driver

Installing Eclipse and DTP does not install JDBC drivers. You must install the appropriate driver for the database server you're using. You can usually get drivers from the vendor. I downloaded the MySQL Connector/J.

  1. Visit http://dev.mysql.com/downloads/connector/
  2. Select 'Connector/J' in the left menu, then select a version. I used version 5.1.
  3. Download the source and binaries and save somewhere that makes sense for your OS. I dropped mine in /Library/Java/Extensions on my Mac.

Create a Driver Template and Connection Profile

Next, create a driver template and a connection profile. You'll be able to reuse this template when creating future connection profiles.

  1. Select File > New > Other
  2. Expand Connection Profiles and select Connection Profile, Next >
  3. Select Generic JDBC Connection, Next >
    New connection profile wizard
  4. Name the Connection Profile for the host and database to which you're connecting (i.e. localhost.database), Next >
  5. Click on the ... button to add your JDBC driver to the Select a driver dropdown menu.
    1. Select the appropriate folder for your database JDBC driver version, I selected MySQL > 5.1, Click Add...
    2. Expand the Available Driver Templates folders, select "MySQL JDBC Driver"
    3. Add the driver version to the Driver Name, I changed mine to "MySQL JDBC Driver 5.1"

      Note: Don't be confused by differences in server and driver versions. You can use the 5.1 driver to connect to MySQL 4.x.

    4. Check "Edit New Driver Definition Immediately", click OK
  6. Eclipse will display an "Unable to locate jar/zip..." message. Tell Eclipse where the JDBC driver is.
    1. Select the driver file in the list, click the "Edit Jar/Zip" button
    2. Browse to and open your JDBC driver. I placed mine at /Library/Java/Extensions/mysql-connector-java-5.1.5/mysql-connector-java-5.1.5-bin.jar
    3. Set your Connection URL, Database Name, Password, and User ID in the Properties pane, click OK

      DTP Driver Profile Details

  7. Your Driver template now appears in the Available Driver Definitions list, select it, click OK
  8. Add Optional Properties to those you just set or check the "Save Password" box if you'd like.
  9. Click the "Test Connection" button, hopefully your Ping succeeded! If not, double check your connection properties.
  10. Click Finish

Open DTP's Database Development Perspective

With a connection template and profile defined, open up the Database Development Perspective, if it isn't already open.

  1. Select Window > Open Perspective > Other...
  2. Select Database Development, click OK
    Database Development perspective

The Database Development Perspective

Establish a Database Connection

  1. In the Data Source Explorer pane, expand the Database folder.
  2. Right-click on the Connection Profile and select Connect
  3. You should now see folders for table, stored procedures, views, and other objects supported by your database platform.
  4. If you have tables in your database, expand to view columns.

Create and Execute SQL Files

You can create and modify tables, triggers, stored procedures directly via DTP. Before proceeding, open an existing or create a new Eclipse project. You'll save SQL files to an existing project which makes it easy to place them under version control with the rest of your application files.

  1. Select File > New > SQL File (or File > New > Other > SQL Development > SQL File).
  2. Choose the project and folder where you'll save the file.
  3. Select the Database server type, Connection profile, and Database name. You can skip specifying the database if you're going to create one with the file.Create a new SQL file
  4. Click Finish
  5. Type in the SQL you'd like to run.
  6. Select all, or a portion, of the SQL to run.
  7. Right-click in the document tab and select either Execute All or Execute Selected Text.
    Execute SQL
  8. Review the SQL Results at the bottom of the SQL Development Perspective.

The buttons in the SQL Results tab allow you to track the status for all SQL executed and provide a handy filter for sifting through results.

Insert and Edit Data

  1. Expand Databases > Host > YourDatabase > Schemas in the Data Source Explorer
    Data Source Explorer
  2. Right-click on a table and select Data > Edit
  3. An editor tab appears with the table's columns, enter one or many records into the rows provided
    Table editor
  4. Right-click anywhere in the editor panel and select save

Easy, isn't it? You probably noticed other nifty features under the Data menu, including Load and Extract. These options allow you to import and export delimitied data from files.

Conclusion

Overall I'm very happy with DTP. It's been a bit of an adjustment getting used to how things are done, but I no longer have to get off the Eclipse "couch" to take care of SQL development tasks. I've covered most of the basics here but I think you'll find several other useful features. Happy data developing!

Comments

"Data Tools" is a vast

"Data Tools" is a vast domain, yet there are a fairly small number of foundational testking mcts requirements when developing with or managing data-centric systems. A developer is interested in an environment that is easy to configure testking mcitp, one in which the challenges of application development are due to the problem domain, not the complexity of the tools employed. Data management testking mcdba, whether by a developer working on an application, or an administrator maintaining or monitoring a production system, should also provide a consistent, highly usable environment that works well with associated testking mcdst technologies.

Database Development in Eclipse with the Data Tools Platform

Blogs are always a main source of getting accurate information and provide you the handy results.you can get instant and reliable information which surely helps you in any field of your concern.your blogs fulfill these requirement and I really appreciate it.keep it up doing good work. i will come back to see more in future as well.best regards

dumps | oracle 10g certification dumps | oracle 9i sql dumps | aca certification

hello every body

Other additions include a YUI client-side component loader, an image loader, a color picker, and a YUI unit test utility. Designers will appreciate YUI 2.3's formalized skinning abilities which promise to make it much easier to apply a consistent look and feel to YUI components.

Clark
mcsa
USA

I've made a connection to a

I've made a connection to a odbc driver and it work fine, I can write my sql statement and execute it whit no problem. Anyway I don't see any tables or stored procedure under Data Source Explorer for any of the connection I've create whit ping successfully at affiliate marketing network. Neither whit jdbc:odbc and other jdbc drive such as Sql Jdbc that work fine tto.

thank you!

i've been struggling with getting my ping to succeed for hours untill i found this tutorial and it showed that the server name has to be prefixed with jdbc:mysql

thank you so much for getting my connection to work

regards,
a .net addict who's trying out java and eclipse

Also a thank you from me...

I just thought I should comment and say thanks too. Very helpful, I'm just learning to use Eclipse (loving it!), it's so cool, I haven't done any hours worth of coding since I owned a DOS machine, for the last few years I thought just having a desktop environment was cool (little scripts & short programs) but this stuff makes me feel alive! I'll be making more complex apps than I ever thought I could do, thanks for helping me set up the DB portion. BTW (to others needing this info mainly) The commenter who said there where a few diffs was right, but anyone who gets this far should be able to stumble through the subtle differences anyhow...I did (also trial and error), the key for me was to 'browse' for the driver class and choose it that way rather than manual entry...works!

Thank you!!

Hi, thanks for these instructions! I'm glad you took the time to write it up.

The most current version of Eclipse has changed a bit though so these instructions are a little bit outdated. After using your instructions and a bit of trial and error I figured out how to set up MySQL dev on the latest version of Eclipse. I wrote up an article on it with hopes that someone will find it useful: http://obscuredclarity.blogspot.com/2009/08/setup-mysql-development-in-e...

Rock on!

Nicely done Tim. Great

Nicely done Tim. Great tutorial.

Not sure why it wouldn't

Not sure why it wouldn't work other than the something like a lack of user privileges on your database.

Reply

I've tried using all the jdbc drivers from 3.1-5.1 and using a generic odbc connection and a specific MySQL connection (version 4 and 5) yet I get the same results either way.

Can't see data on MySQL version 4.1 server

Hello,

Great article! I've done the same and it works just fine with MySQL v5 databases but when I tried to view the data on a MySQL v4 database it can connect but it doesn't list any tables. I've tried using all the jdbc drivers from 3.1-5.1 and using a generic odbc connection and a specific MySQL connection (version 4 and 5) yet I get the same results either way.

So anyone know how to view your MySQL v4 layout with Eclipse's DTP (Data Tools Project)?

Thanks!
- Jake

Originally tested against 4.1

Not sure why it wouldn't work other than the something like a lack of user privileges on your database.

What about for Windows

How do you set up similar for a Windows Machine?

Find a MySQL JDBC tutorial

Everything should be the same if you can install the MySQL JDBC driver for Windows. Try this link or Google "windows install jdbc mysql".

OK button disabled?

The OK button on the "New Driver Definition" window is disabled for me. Any tips?

The OK button should be

The OK button should be enabled as soon as you've selected a valid JDBC Driver file from the list.

Timely Help

Your step-by-step instructions ensures a smooth configuration...Thanks for sharing...I'm pretty sure this will help everyone stuck with this specific problem.

Fantastic post

Thanks for sharing this, I've been wanting to be able to see my MySQL Schemas in Eclipse for some time :)

Overall I'm very happy with

Overall I'm very happy with DTP. It's been a bit of an adjustment getting used to how things are done, but I no longer have to get off the Eclipse "couch" to take care of SQL development tasks. I've covered most of the basics here but I think you'll find several other useful features.

copyright © 2011, 2 tablespoons | Privacy Policy