Database Development Using the Eclipse IDE, MySQL with Eclipse Data Tools Platform in Java

The article is written for/using J2SE 6, MySQL 5.1.37 GA, MySQL Connector/J 5.1.8 and Eclipse IDE for Java EE Developers [Ganymede].

Table of Content

  1. Introduction
  2. System Requirements
  3. Article Prerequisites
  4. Installing/Setting up MySQL JDBC Driver and Connection Profile
  5. Open Database Development Perspective
  6. Connecting to MySQL Database
  7. Create, Insert, Edit and Execute SQL Files
  8. Summary
  9. Resources
  10. Feedback

Introduction

The Eclipse Data Tools Platform (DTP) is a new top-level project at eclipse.org. Originally proposed by Sybase in February 2005, DTP has attracted strong community support and is currently managed by a committee comprised of Sybase, IBM and Actuate. It is an open source initiative designed to provide solutions in the data framework and tooling domains. 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, and more. This post covers connecting to MySQL and basic DTP features. Eclipse includes:

  • Connect to a database (or multiple databases)
  • View database object trees (tables, views, stored procedures, etc.)
  • Generate data definition language (DDL) from database objects
  • Execute DDL commands against a database
  • Execute standard query language (SQL) queries against a database
  • View query results in table format

System Requirements

Article Prerequisites

  • I’ll assume that you’re comfortable installing Eclipse and Eclipse plugins.
  • Installing and running MySQL Database
    • Double click mysql-5.1.37-win32.msi.
    • Click Next and select Complete Setup Type. Click Next and click Install.
    • After installing MySQL, check Configure the MySQL server now and click Finish.
    • In MySQL Server Instance Configuration Wizard, click Next. Select Detailed Configuration, click Next.
    • Select Developer Machine, click Next, select Multifunctional Database and click Next. Click Next again.
    • Select Decision Support (DSS)/OLAP and click Next. Check Enable TCP/IP Networking and leave default port 3306. You can change if you want to. Also select Enable Strict Mode and click Next.
    • Select Standard Character Set and click Next.
    • Select Install As Windows Service, select Service Name, check Launch the MySQL Server automatically. Select Include Bin Directory in Windows Path. Click Next.
    • Check Modify Security Settings and give Password. Click Next and click Execute. Click Finish.

Installing/Setting up MySQL JDBC Driver and Connection Profile

Select File > New > Other. Expand Connection Profiles and select Connection Profile, click Next >.

Alternatively, you can select Data Source Explorer TAB, right click Database Connections, and select New…

Select MySQL as Connection Profile Type. Name the Connection Profile for the host and database to which you’re connecting (i.e. localhost.database). We are giving name MyMySQLConnection. Click Next >.

Click New Driver Definition button which will open new dialog box. Select Name – MySQL JDBC Driver and System Version – 5.1

Eclipse will give error as “Unable to locate JAR/Zip in the file system.”.

Select Jar List TAB. Click Add JAR/Zip… button and select mysql-connector-java-5.1.8-bin.jar. Press OK.

On the General TAB, update following fields and select Save password checkbox.

  • Database: mysql
  • URL: jdbc:mysql://localhost:3306/mysql
  • User name: root
  • Password: password

Click Test Connection to check connection has been successfully establish. Click Finish

Open Database Development Perspective

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

Select Window > Open Perspective > Other…. Select Database Development, click OK.

Connecting to MySQL Database

Above steps will connect to database. In case Eclipse is not connected to database, then follow these steps:

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

Create, Insert, Edit 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.

Select File > New > SQL File (or File > New > Other > SQL Development > SQL File). Click Create Project… in Create SQL File dialog. Select General > Project. Click Next.

Name the project MyFirstSQLProject. Click Finish.

Give File name as MyFirstSQL.sql. Select database server type as MySql_5.1. Select connection profile name as MyMySQLConnection. Select database name as test. Click Finish.

NOTE: MySQL by default has three databases when you install it. information_schema, mysql, and test

Type in the SQL you’d like to run. Select all, or a portion, of the SQL to run. Right-click in the document tab and select either Execute All, Execute Selected Text, or Execute Selected Text As One Statement. Review the SQL Results at the bottom of the SQL Development Perspective.

Right-click on a table and select Data > Edit. An editor tab appears with the table’s columns, enter one or many records into the rows provided. Right-click anywhere in the editor panel and select Save.

If your SQL statement executes successfully, you should see the results of the statement in the Results view. If the statement was a query, the Results view will display the returned records. If it’s not a query, you’ll be notified of the statement’s success and how many rows were affected.

Summary

In this tutorial, we learned how to:

  • Define a driver template
  • Create a connection profile
  • Connect to a MySQL 5.1.37 database
  • Run SQL statements against a MySQL 5.1.37 database from within the Eclipse IDE

Resources

Feedback

I would like to hear from you! If you liked this tutorial, have some suggestions or even some corrections for me, please let me know. I track all user feedback in comments sections.

37 Comments

  1. grishma says:

    I liked the tutorial but i have a doubt.
    I am not able to view the tables neither in the data explorer nor in left hand side .
    I can view the tables in the cmd.

    Please let me know to your earliest.

    Thanks

  2. olu says:

    Thanks for the tutorial and keep the good work and God bless you. I could not use the table edit command because it gives error “Failed to create the part’s controls”, I would appreciate your help.

    Olu Ore

  3. pallavi says:

    I m unable to connect to NewSQL Server… On clicking Test Connection, its showing Ping failed..ITs showing creating connections has encountered a problem. Can u just please help me out.

    Thanks

    • AnishMohan says:

      check the db name localhost…../dbname

    • mia says:

      yeah..i too encountered the same prblm

  4. KIM says:

    I like this tutorial and It is very helpful for me.
    Thank you very much.

  5. Raj says:

    Thank you so much it is very much useful to me…

  6. Nimmy Sunny says:

    Very nice tutorial, ๐Ÿ™‚

  7. Siddiq says:

    Ping not successfully! help me bro..

    • gan says:

      same problem

      • Manish says:

        do check the driver version of MySQL application and Driver and connector.

        2. Try to manually create a database using “create database xyz;” command in MySQL itself;

        3. then after adding driver in general tab, do : Database:xyz
        4.verify the username and password again
        5. then test .

  8. tarun says:

    please provide me the link to download any real life project of java db

  9. Jwala says:

    On clicking Test Connection, its showing Ping failed.. when we click finish button it shows erroe message showing creating connections has encountered a problem. please help

    • Ajit Pratap Singh says:

      Is you MySql service running?Please check as this could be the issue

  10. Kp says:

    hey,thanku so much its very helpful for me to solve connection with mysql error.

  11. Hema says:

    very help full..thank you ๐Ÿ˜€
    Please also add how to use created tables in java application !!

  12. sirisha says:

    Hi!!When i follow the steps…it shows ping failed on clicking Test Connection!!Help me!!

    • tri says:

      You need to rename:
      Database: database replace to mysql
      URL: jdbc:mysql://localhost:3306/database replace to jdbc:mysql://localhost:3306/mysql

      hope it help !!!

      • pavi says:

        hii, though i have replaced my url with mysql i got the same error as ‘ping failed’

  13. Yogita Parab says:

    This is really helpful document to me.Thank you so much.I recommend this document to everyone who want to learn the basics of database connectivity

  14. Rajender says:

    Its really help me out in my project, many thanks and keep this website with updates i ll follow and advertise this site to all my known peoples

  15. zoey says:

    When installing mysql, in last step -> execute, it has error said that cannot create Windows service for MySQL. Can you help me?

    • Mahmoud Hammam says:

      When installing mysql, in last step -> execute, it has error said that cannot create Windows service for MySQL. Can you help me

  16. Sri says:

    I got error “ping failed” how to overcome this issue

  17. Sri says:

    Connecting to database was exactly what I was looking for. Worked like wonder. Thank you so much ๐Ÿ™‚

  18. Mahmoud Hammam says:

    When installing mysql, in last step -> execute, it has error said that cannot create Windows service for MySQL. Can you help me

  19. Mahmoud Hammam says:

    Hi!!When i follow the stepsโ€ฆit shows ping failed on clicking Test Connection!!Help me!!

  20. Syed says:

    How to create jar file with mysql database

  21. Syed says:

    How to create jar file with mysql database kindly reply me thank u

  22. Ram padavalkar says:

    I like this project

  23. Nagesh Kadam says:

    I like this project
    but execute, it has error said that cannot create Windows service for MySQL. Can you help me

  24. Vijay says:

    Hi,

    Ping is failed.

    Creating connections has been encountered problem..

  25. Ahalya says:

    when i am creating a sample registration form,eclipse is not able to connect to database.please help to know how to connect database?

  26. shweta says:

    helpful tutorial
    Thanks

  27. vaithee says:

    how to connect my sql with eclipse

  28. prem says:

    change the background . it’s irritating..

  29. Santhosh says:

    Thank you so much.. I have created my db but it contains many tables already. i dont know how they come. i think they are in built becoz when i create another db there also it comes and i couldn’t delete. pls someone guide me

Leave a Reply

Your email address will not be published. Required fields are marked *