Tuesday, August 18, 2009

Setup MySQL Development in Eclipse

Using MySQL and Java? Check out an easier way: Yank

Setting up Eclipse as a MySQL development environment using the Database Development perspective is quite easy. Here you will find a step-by-step guide showing how to set up Eclipse for MySQL development. The tutorial shows how to get the right version of Eclipse, how to setup a MySQL database, how to get and use the Connector/J database driver, how to connect to a database within Eclipse, and how to create a table containing some sample data.

Step 1: Download and Install the Eclipse IDE for Java EE Developers.



Step 2: Download the Connector/J MySQL Database Driver.


Step 3: Download, Install, and start up MySQL. If you're developing on a Mac, see: Install MySQL on Mac OS X.

Step 4: Open up Eclipse and switch to the "Database Development" perspective (Window --> Open Perspective --> Other... --> Database Development perspective).




Step 5: Connect to your MySQL database.

In the "Data Source Explorer" view, right click on "Database Connections" and select "New...".



On the first page of the "New Connection Profile" Wizard, select the "MySQL" connection profile type and hit "Next >".

Click the circle with a plus sign next to the "Drivers" drop-down box.

On the "Name/Type" tab, select the MySQL JDBC Driver version 5.1 (or whatever version you downloaded). Just ignore the error in the wizard for now.

On the "Jar List" tab, add the driver .jar from the package that you downloaded and remove the default .jar that was added to the list. The error message in the wizard should now disappear. Click "OK".

On the second page of the "New Connection Profile" Wizard, edit the connection properties. In the "Database" and "URL" fields, change the word "database" to the actual name of the database you want to connect to. Change the URL, User name and Password appropriate to your circumstance. By default the MySQL database setup creates a root user with a blank password. Test the connection and hit "Finish".


Step 6: Create a table.

In the main Eclipse menu, select File --> New --> SQL File.



In the "New SQL File" wizard, select the folder where the SQL file should be stored. Create a new project if you don't have one yet. Give the file a name and hit "Finish".

In the SQL Editor view, write an SQL statement to add a table to the selected database. Highlight the text to be executed, right click, and choose "Execute Selected Text".


Step 7: Add data to the table.

In the "Data Source Explorer" view, dig down into the directory structure until you find your table in the "Tables" folder. Right click on it and choose Data --> Edit.



In the table editing view that appeared, add your data. Save to commit your changes.



Piece of cake!!

43 comments:

ElJuanfer said...

Hi. Excellent tutorial, however I have a problem. I can not see the Execution Plans in Eclipse while running MySQL queries. Do you know how to do it?

Tim Molter said...

ElJuanfer, sorry, I haven't figured that out yet!

baahar said...

Thanks a lot for this tutorial. It helped to connect with Eclipse to my database so easily. As a Netbeans user, I often find Eclipse complicated.

Robert said...

Hi there!
I followed your guide, but when I hit test connection I get "Ping failed"? What do u think I have done wrong, because I can connect to MySQL through the terminal.

//Robert

Tim Molter said...

Hi Robert, Everything in the New Connection Profile wizard must be entered without mistakes. It's most likely that something in there is not correct. The first thing you could verify is that you have defined the host and port correctly. Perhaps whoever set up the DB set the port to something non-standard. If you are able to access the DB via the terminal, I guess you have the username and password and DB name correct. After you double check all that, and there are still problems, it could be a firewall issue. That's all I can think of!

admin said...

Hi, this is very good and simple explanation for database connection in eclipse.

robezy said...

Hello,

I have a trouble with datatools? Could you help? Whenever I try to execute a group of statements together, I get error saying there is a syntax error. But if I execute statements one by one there is no problem? Any Idea what is wrong? I tried all options, Execute selected statement, execute all etc. But all gives same error.

Thanks,
Robert

Tim Molter said...

Hi Robert, I'm not sure, but make sure you have a semicolon (;) after each statement if you don't. Otherwise I don't know what to say. Good luck!

Wendy said...

Hi,

Do I need to place the connector/j in a certain place? Because right now Eclipse cannot see MySQL in the connection profile wizard.

Thanks!

Wendy said...

To clarify, in my "New Connection Profile" wizard, I only have 5 options (Derby Embedded Database, Generic JDBC Connection, HSQLDB Connection Profile, PostgreSQL JDBC Connection, and Sybases ASA) , none of which are MySQL. Could you help?

Tim Molter said...

Wendy, that's strange. I've never ran into that problem, but it seems to be a problem with your Eclipse installation. No, you don't have to put the Connector/J in a certain place... I hope you can figure it out!

Anonymous said...

This was a fantastic tutorial...I wish I could have found it earlier!

MrHyde said...

One example of Development in Postgres??

Tim Molter said...

MrHyde, sorry no time...

Pipistrello said...

Hi Robert,
Nice tutorial, but i get an exception:
Communications link failure, unable to connect to "database name"
Do you know what it could depend from?

Tim Molter said...

Pipistrello, no, sorry, I haven't seen that issue before. Good luck!

Bhupesh said...

Thanks a lot.dear..it was a great tutorial...

Nani said...

hi there such a great tutorial really can't stop appreciating you. But i cannot connect to the database it says ping failed whenever i try to connect. Is this a driver problem i am using java connector 5.1.17 driver. Please help me.

Thanks

Tim Molter said...

Nani, I doubt it's a driver problem. It sounds like either your MySQL server is not running or the port number is set to something other than 3306.

GeekMetalGig said...

@NANI: Actually what you've got problem i have also faced that same problem. actually u must do by these following steps:
1. first start the mysql server.
2. then type "show databases;"
3. then type "use ";
4. now in eclipse in database field change the name as your database name.
5. and also change the Url as "jdbc:mysql://localhost:3306/"


Thank You,

Gabi said...

Thank you very much! This tutorial was a real lifesaver for me in connecting to MySQL database using Eclipse.

cknoettg said...

Thank you for the tutorial. To confirm what GeekMetalGig said, if you right click the new profile that you have created for MySQL in Eclipse, and select Properties, you have to change:

1. Database (field) to the Name that MySQL assigned to you when you first installed MySQL - if you can't remember it, fire up the MySQL WorkBench and it will show up in the lower left-hand corner of the screen once you have established a connection.

2. URL (field) - erase "database" from the end of the URL listed in this tutorial.

Of course, you also want to make sure that MySQL was set up on Port 3306 and your password is correct.

Anonymous said...

am very new to java. MY eclipse download which is does not have the "database development " perspective. By following instruction, t get to the dead end where there are options like:cvc repository, debug, java(default),java browsing, java type hierchy,planning, resources,team sync., and XML. thanks alot.
DEV,

Tim Molter said...

Anonymous, what Eclipse package did you download and install? It could be that yours simply doesn't have that perspective bundled by default.

Anonymous said...

Thank you. needed the another version . Was using the classic 3.7 but needed the " Eclipse IDE for .Java EE Developers" . thank you

Christopher Marshall said...

Hi All,

I've seen a number of people on here discussing the "ping failed" problem. I encountered the same problem and here are two things you can check. They solved it for me:

---

1) Get the Jar and not a zip

When I downloaded Connector/J I got the file mysql-connector-java-5.1.17.zip. Because the button to add the driver says "Add Jar/Zip" I assumed that this was the file it needed.

However, if you unzip it you will find a file mysql-connector-java-5.1.17-bin.jar; this is the one you actually need.

2) The default connection string seems wrong.

Somewhere along the way my connection string became: "/jdbc:mysql:/192.168.56.101:3306/db" it actually should be "jdbc:mysql://192.168.56.101:3306/db" (note the extra '/'). I'm not sure if this came from the default or if I inadvertently deleted it.

---

Once I'd fixed these I got a ping success.

Frank said...

Excellent tutorial... things went well until Step 5... I also have the dreaded 'ping failed' problem. :( But for me, it sits there for several minutes before giving me the message.

Can someone clarify (screenshot?) please... what exactly are the Database and URL values? Does the URL have your database name on the end or not?

Thanks.

Tim Molter said...

Frank (and others having the Ping failure problem),

There seems to be a lot of people having the Ping failure problem so I thought I'd go through the tutorial myself and see if I ran into the problem too. I recently installed Eclipse Indigo so I was starting from a clean slate. (A while ago I started using Sequel Pro for database work and haven't touched a SQL table via Eclipse in a long time. I highly recommend Sequel Pro, BTW. It's free.)

What I found is that the tutorial as I have it here is still correct. The database URL is correct. I was able to go through it in a few minutes. However I did get the Ping failure error at first as well! My conclusion is that it's a bug in Eclipse's database connection wizard. After I got the ping failure, I copied and pasted my DB's password into the password field again, and got a Ping success. I tried it again from the beginning and that was the trick again on the second try. Seems like a weird bug to me.

I hope that helps although it's a pretty inconclusive result....

Frank said...

Thanks Tim... that did help.

After your comment, I gave up on the Database Connection thing altogether, and just imported my code (I'm moving from NetBeans to Eclipse) and it worked (once I'd figured out how to include the driver .jar in my project. :)

preeti said...

very helpful...thanks Tim

Anonymous said...

Thanks

cjr said...

Just what I was looking for. Thanks!

Prashanth said...

Thanks a lot Tim . This was of great help.

Anonymous said...

thanks for the tutorial and would like to thank @GeekMetalGig for his comments on how to solve 'ping failed'

achu said...

thanks.but "ping failed' error is showing.could u pls help me....

Kally said...

I got the Ping failed too. So, I unzipped the package and imported the actual JAR file instead of zip/tar and BAM it works!!!! You might need to manually remove the zip file you included before adding the Jar file.

Anonymous said...

Excellent! It was very hard thing just before I read this! Thank you for sharing...

Supreet said...

Awesome,,,,Simple and effective...Thanks a tonne...

Anonymous said...

Awesome ...great help..thanks a lot....

vishal said...

hi thanx a lot for ur help,but when iam creating a table it is showing no database selected,even though my details are exactly the same as urs.

Anonymous said...

Mr Varrier said
Its very useful.Thank you..!!

Anonymous said...

Some additional clarity for the ping failed issue: http://dev.mysql.com/downloads/connector/j/ only has an msi file, not a .jar file to download. Make sure you download the msi, then double click it to install. You should be able to find the jar file within the program files somewhere.

Thanks for the tutorial! Good stuff. :)

Karthikeyan Stalin said...

Hello Master :) thanks a lot for this tutorial. it helped me a lot to connect the eclipse to the database..