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

The article is written for/using J2SE 6, Derby 10.5.1.1, Eclipse plugins for Derby and Eclipse IDE for Java EE Developers [Ganymede].

Table of Content

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

Introduction

This post covers connecting to Derby and basic DTP features. Apache Derby, an Apache DB subproject, is an open source relational database implemented entirely in Java and available under the Apache License, Version 2.0. Some key advantages include:

  • Derby has a small footprint — about 2 megabytes for the base engine and embedded JDBC driver.
  • Derby is based on the Java, JDBC, and SQL standards.
  • Derby provides an embedded JDBC driver that lets you embed Derby in any Java-based solution.
  • Derby also supports the more familiar client/server mode with the Derby Network Client JDBC driver and Derby Network Server.
  • Derby is easy to install, deploy, and use.

System Requirements

Article Prerequisites

  • I’ll assume that you’re comfortable installing Eclipse and Eclipse plugins.

Installing/Setting up Derby JDBC Driver and Connection Profile

Extract derby_core_plugin_[version].zip and derby_ui_doc_plugin_[version].zip. Copy folders available in plugins folder to eclipse > plugins directory.


Now start Eclipse. 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 Derby 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 MyDerbyConnection. Click Next >.

Specify a Driver and Connection Details as follows

  • In Drivers drop down, select Derby Embedded JDBC Driver 10.2 Default.
  • Under General TAB, Database Location: C:\eclipse\workspace\MyDerbyDB
  • User name: admin
  • Password: password

Click Test Connection. This will create Database Folder and ping success. 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 Derby 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 Derby_10.x. Select connection profile name as MyDerbyConnection. Select database name as MyDerbyDB. Click Finish.

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 Derby 10.5.1.1 database
  • Run SQL statements against a Derby 10.5.1.1 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.

21 Comments

  1. NOAM says:

    Thanks this was very helpful ! a great tutorial

  2. omar says:

    thanks a lot it was very simple to follow it

  3. ronan says:

    cheers for the tutorial!! needed a db connection asap and this is the only one that was simple to read and learn. Thanks again

  4. lawrence says:

    very detailed step by step procedure! no stack overflow

  5. ravi says:

    from where i can get those plugin of derby

  6. karl says:

    thanks a ton… by far the easiest tutorial i’ve found to get me up and running.

  7. arti jain says:

    it was really helpful……….simple and clear…….

    thnx!!!

  8. tarun says:

    what is different between mysql and sqlite database?

  9. Anuj Kumar says:

    how can i create a connection profile for oracle and create connection using that profile.I want java code for creating profile along with creating connection. please help me …..

  10. Yesh says:

    Thank you 🙂 nice tuorial

  11. Vivek Soni says:

    hey,thanx for explaining it so nicely.
    I saw all the sites,but i was unable to connect my eclipse with the database derby. You paved me the way
    thanx a ton

  12. Jonas says:

    Thank you so much. All the other tutorials on the Internet are so confusing but this one… thank you!

  13. gauraV says:

    THANKS MATE IT WAS REALLY HELPFULLLLLLLL

  14. Sriram says:

    Good Job. It was helpful

  15. Ameya kunjir says:

    thanks a lot very good article

  16. Dinesh Prajapati says:

    Thanks.
    I tried so many time but failed to create connection with derby. but after following given your steps i am able to connect with derby.

    Thank you very much!!!

  17. Yuvaraj says:

    Really nice article.

  18. Patrick Franklyn says:

    Need help, please. My Eclipse workspace is C:\Users\PATF\workspace
    The plugins where the jars, etc are, are in C:\Users\PATF\workspace\plugins
    org.apache.derby.core_10.5.1
    org.apache.derby.plugin.doc_1.1.2
    org.apache.derby.ui_1.1.2
    did the following in eclipse
    file -> new -> other -> Connection Profiles -> Connection Profile
    Clicked Next
    Selected Derby
    Entered Name as MyDerbyConnection
    Clicked Next
    Drivers list was empty so I clicked on the + button and got the drop down
    Selected Derby Embedded JDBC Derby 10.2 but got this error
    Unable to locate JAR/zip in file system as specified by the driver definition:
    derby.jar

    I checked c:\Users\PATF\workspace\plugins\org.apache.derby.core_10.5.1 and derby.jar is there. What am I doing wrong?

    • Lincoln says:

      Same issue here. Which version of eclipse do you use?

  19. Lekhashree Pandit says:

    Great tutorial.. thumbs up.. 🙂

Leave a Reply to Yesh Cancel reply

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