Database Development Using the Eclipse IDE, MySQL with Eclipse Data Tools Platform in Java
Table of Content
- Introduction
- System Requirements
- Article Prerequisites
- Installing/Setting up MySQL JDBC Driver and Connection Profile
- Open Database Development Perspective
- Connecting to MySQL Database
- Create, Insert, Edit and Execute SQL Files
- Summary
- Resources
- 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
- Java SE Development Kit (JDK) 6
- Eclipse IDE for Java EE Developers [Ganymede]
- MySQL 5.1.37 GA Community Server Edition
- MySQL Connector/J 5.1.8, JDBC driver for MySQL
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.
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
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
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
check the db name localhost…../dbname
yeah..i too encountered the same prblm
I like this tutorial and It is very helpful for me.
Thank you very much.
Thank you so much it is very much useful to me…
Very nice tutorial, 🙂
Ping not successfully! help me bro..
same problem
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 .
please provide me the link to download any real life project of java db
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
Is you MySql service running?Please check as this could be the issue
hey,thanku so much its very helpful for me to solve connection with mysql error.
very help full..thank you 😀
Please also add how to use created tables in java application !!
Hi!!When i follow the steps…it shows ping failed on clicking Test Connection!!Help me!!
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 !!!
hii, though i have replaced my url with mysql i got the same error as ‘ping failed’
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
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
When installing mysql, in last step -> execute, it has error said that cannot create Windows service for MySQL. Can you help me?
When installing mysql, in last step -> execute, it has error said that cannot create Windows service for MySQL. Can you help me
I got error “ping failed” how to overcome this issue
Connecting to database was exactly what I was looking for. Worked like wonder. Thank you so much 🙂
When installing mysql, in last step -> execute, it has error said that cannot create Windows service for MySQL. Can you help me
Hi!!When i follow the steps…it shows ping failed on clicking Test Connection!!Help me!!
How to create jar file with mysql database
How to create jar file with mysql database kindly reply me thank u
I like this project
I like this project
but execute, it has error said that cannot create Windows service for MySQL. Can you help me
Hi,
Ping is failed.
Creating connections has been encountered problem..
when i am creating a sample registration form,eclipse is not able to connect to database.please help to know how to connect database?
helpful tutorial
Thanks
how to connect my sql with eclipse
change the background . it’s irritating..
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
Hi
Today I face a problem in this perspective.
I run USE Test databases and they try to run query. But I show error that I did not select database.
ping failed
ping failed
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:403)
at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:990)
at com.mysql.jdbc.MysqlIO.(MysqlIO.java:335)
at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2187)
at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2220)
at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2015)
at com.mysql.jdbc.ConnectionImpl.(ConnectionImpl.java:768)
at com.mysql.jdbc.JDBC4Connection.(JDBC4Connection.java:47)
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:403)
at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:385)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:323)
at org.eclipse.datatools.connectivity.drivers.jdbc.JDBCConnection.createConnection(JDBCConnection.java:328)
at org.eclipse.datatools.connectivity.DriverConnectionBase.internalCreateConnection(DriverConnectionBase.java:105)
at org.eclipse.datatools.connectivity.DriverConnectionBase.open(DriverConnectionBase.java:54)
at org.eclipse.datatools.connectivity.drivers.jdbc.JDBCConnection.open(JDBCConnection.java:96)
at org.eclipse.datatools.enablement.internal.mysql.connection.JDBCMySQLConnectionFactory.createConnection(JDBCMySQLConnectionFactory.java:28)
at org.eclipse.datatools.connectivity.internal.ConnectionFactoryProvider.createConnection(ConnectionFactoryProvider.java:83)
at org.eclipse.datatools.connectivity.internal.ConnectionProfile.createConnection(ConnectionProfile.java:359)
at org.eclipse.datatools.connectivity.ui.PingJob.createTestConnection(PingJob.java:76)
at org.eclipse.datatools.connectivity.ui.PingJob.run(PingJob.java:59)
at org.eclipse.core.internal.jobs.Worker.run(Worker.java:55)
Caused by: java.net.ConnectException: Connection refused: connect
at java.net.DualStackPlainSocketImpl.connect0(Native Method)
at java.net.DualStackPlainSocketImpl.socketConnect(Unknown Source)
at java.net.AbstractPlainSocketImpl.doConnect(Unknown Source)
at java.net.AbstractPlainSocketImpl.connectToAddress(Unknown Source)
at java.net.AbstractPlainSocketImpl.connect(Unknown Source)
at java.net.PlainSocketImpl.connect(Unknown Source)
at java.net.SocksSocketImpl.connect(Unknown Source)
at java.net.Socket.connect(Unknown Source)
at com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:211)
at com.mysql.jdbc.MysqlIO.(MysqlIO.java:299)
… 22 more