Data Tools Platform (DTP) 1.6 Working with Derby and MySQL Part 1

Description

DTP provides extensible frameworks and tools for creating, managing and using, various data sources, like databases, flat files, xml files etc).

It only takes a few clicks before you're connecting to and drilling into the Data Source Explorer to view your databases.

DTP environment is very easy to configure and use.

With SQL Query Builder, a graphical SQL query editor in DTP 1.6, you can create complex SQL Statements with a minimum of hand-coding SQL. One of it's great features is the creation in of "joins" in SQL statements, in an easy manner and a visual interactive way.

Author : Kaesar ALNIJRES

Introduction

DTP provides extensible frameworks and tools for creating, managing and using, various data sources, like databases, flat files, xml files etc).
It only takes a few clicks before you're connecting to and drilling into the Data Source Explorer to view your databases.
DTP environment is very easy to configure and use.
With SQL Query Builder, a graphical SQL query editor in DTP 1.6, you can create complex SQL Statements with a minimum of hand-coding SQL. One of it's great features is the creation in of "joins" in SQL statements, in an easy manner and a visual interactive way.
DTP two means features (in my opinion) are interactivity and central connection model, should also provide a consistent, highly usable environment that works well with associated technologies. The idea is simple, one definition and multiple using of the same data source. In the case of databases one connection profile to a database is defined (only once) let many Eclipse projects use this connection profile without further connection operations
DTP uses SQL to create, query, update databases
Eclipse WTP, JEE and BIRT for instance include and use DTP
This tutorial tries to give a hint about using DTP for connecting to databases.
Two databases servers are described here : Derby and MySQL

Requirement :

• Database server with JDBC driver, like Derby or MySQL
• An Eclipse distribution (or package) that includes DTP 1.6 like Eclipse JEE
• Or the following :
• Eclipse 3.4
• EMF & SDO 2.4
• GEF 3.4
• DTP 1.6

Installation

Very easy to install. If you grab the JEE package, you'll get DTP and its dependencies installed automatically. gunzip, or Unzip the file and you're done

Derby Database

Derby or "Apache Derby" is an open source relational database implemented entirely in Java. Available under the Apache license and it's available as part of Java 6
Steps to Connecting to Derby Using DTP

1. Download and install Derby on your hard disk or find and use derby.jar file included with Java 6
2. Define a Driver Template
3. Create a Connection Profile for Derby Embedded
4. Connecting to Derby
5. Using Derby Database

Find derby.jar on your hard driver

If you have Java 6 installed on your system, you'll find "derby.jar" in JAVA_HOME/db/lib (JAVA_HOME is the installation directory of Java 6 aka jdk1.6.0_xx)
You can also go to http://db.apache.org/derby and follow the instructions there to download and install Derby

Defining a Driver Template :

Run Eclipse

Windows -> Preferences -> Data Management -> Connectivity -> Driver Definition 

This is the place where you will define a driver and it's configurations. This driver will be used later by Database Development

Click on "Add" to open Driver Definition's dialog 

New Driver Definition :

In this dialog you will specify a driver ; find it's location on hard drive.You can give a name to this definition :
In this dialog you have three tabs :

1. Type/Name
2. JAR List
3. Properties

When all is finished with these tabs Click OK twice to quit Preferences.

1-Type/Name :

Type is the type of driver to use. This will be selected among several available templates. Just select the most appropriate template for your JDBC driver.
"Derby Embedded JDBC" 10.2 is selected here in this example Please note that Derby Embedded will create a database directly on your hard drive in the folder that you will specify later in the Properties Tab or Connection Profile. No need to configure a Derby server

2-JAR List

Here you will enter the location of the derby.jar file mentioned earlier. Click first on JAR List tab, you will notice an error message, because no derby.jar have been found (yet)

Click on derby.jar -> Click on Edit JAR/Zip 

From here you can browse your file system, find, and add derby.jar When you click on OK you will see that everything is fine now (no more errors)

Note : It is also possible to clear derby.jar -> Click on Add JAR/Zip

3-Properties

In this tab you can change URL to your database.
On my linux system, I want my Database directory to be called "TestDerby" instead of the default "MyDB". I clicked on the URL field and modify the path to my database and it's name
Please note : MyDB is the database name (and the database directory name)

Open Database Development Perspective

This perspective has some views and Data Source Explorer. These are particularly suitable for working with databases. Here we can create connection profiles, connecting to databases, populate databases, edit content and more.
Please note that DTP is not only for working with databases, but the objective of this tutorial is to show you how to connect and use DTP with Derby and MySQL

Go to Window menu -> Open perspective -> Other -> Database Development 

Create a New Connection Profile

In Data Source Explorer" -> right click on Databases -> click on new. 

Select Properties of the New Connection Profile

In the New Connection Profile dialog -> select Derby -> enter a name for this profile and a little description if you would like to -> click on Next 

Specify a Driver and Connection Details

In the general tab the only required fields for creating a database are :

1. Database location
2. Create database
3. URL

you can modify database location, (this location was entered when defining a driver for Derby). URL will change automatically to have the new location. Create database is selected because I don't have a database with this name and location. It's possible to test the connection with "Test Connection"
You can even enter a New Driver Definition as done before or to Edit this configuration

URL will change to match Database Location 

Click on Finish

Note :

-If you use Browse to change database location, you will notice that you can select only directories and you MUST enter a name for your database

-You can connect automatically when the wizard completes or with every launch of the Eclipse platform

Export and Import the Connection Profile

You can export any connection profile as an encrypted or not flat file. You can use this file later on another Eclipse platform. To import a connection profile from a flat file use Import

Connecting to the database

Now that the profile connection has been created all we have to do is to right click on "derby connection profile" that we have created and to click on "connect"

Rright click on "derby connection profile" -> "connect"

You can rename, delete, duplicate your profile, change it's properties and ping to test the connection

JDBC Connection Properties

This dialog will appear as soon as you click on "Connect" in the last step
As you can see nothing new here. We have seen all these properties earlier when defining our JDBC Driver or upon creating the new connection profile.

Click on "OK" and you are done. 

Your are connected

Now that you have your connection to the database, you can create, edit, drop tables, columns, rows or what else you want in your database.

Let us do it the soft way

DTP let you "thanks to the DTP team" do almost everything with databases without entering any code. And even when you have to hand code things in SQL, Assistants and SQL Query Builder are really of great help.

We are going to verify this.
We are going first to create our first table without using any SQL queries at all

Our database is called TestDerby in this example

In Data Source Explorer Click on TestDerby -> Schemas -> APP

Right clic on Tables -> New Table

Define Options for your table

The minimum to select is "Create statement"

Click on Next 

Columns

In this dialog, you will choose a name for your table, add some columns in it and specify properties for every column
For creating an "employees" table, where every employee has and id, last name and first name do the following :

Enter "employees" in "Table name"

Click on "Add column" button first, "new_column_0" is add.

Change the column name to "id" select "Data Type" from the combo box "INTEGER" 

Enter last name and first name

Do the same thing as "ID" to enter lastname and firstname columns

Enter length for lastname and firstname or keep the default value

Click on Next

Note:

You can precise if you want your field to be Nullable or not and their default value 

Select members of table's primary key

Click on Finish

Note :

You can change the Name of the Primary Key if you want

When you click on Finish you will get an SQL Statement ready to use

All you have to do to create your table is to :

Right click -> "Execute All" or CTRL+ALT+X 

SQL Result

As you can see in the "SQL Result" everything is OK. SQL statement was successfully executed.

Where is the table ?

Right click on tables -> select Refresh 

"EMPLOYEES" table is created under tables

Populating the Table

Right click on "EMPLOYEES" table -> Data -> Edit

Note:

It's also possible to populate the table from a flat file -> Using "Load"

EMPLOYEES is now opened in a visual editor with columns headers.

All what we have to do to is to click on "new row" first. On columns next and enter data to populate the table

A new row will be added every time automatically.
Try it, it's really very easy to use. All is interactive here : click and enter,

select and delete

Right click and Save or CTRL+S

Note:

Other items in the Pop-up are obvious 

DTP for SQL Guru

If you are SQL champion and want to enter all in SQL, no problems "SQL Scrapbook" and "SQL File" are for you with an assistant at your service
And what about the promising :

"Create complex SQL statements and queries in an interactive way, with a minimum SQL code" ? 

The answer is SQL Query Builder

SQL Scrapbook

Click on "SQL Scrapbook" icon in the tool bar
Enter SQL query

Right click and -> Execute All

See at once the result in "SQL Result" pane

What is SQL File

You can create a SQL File in an Eclipse project, like a Java project and use the centralized Database connection provided by DTP in the Database Development Perspective
This file can be saved and used later, in the same or other Eclipse project
Imagine the possibility of testing your SQL Statements before embedding these statements into your code.

In a Java or a Resource project :

New -> Other -> SQL Development -> SQL File -> Next 

Select a parent folder -> Enter file name.

Select as we have done in Connection Profile:
Database Server Type
Connection Profile Name
Database Name


Click on Finish 

The SQL File is ready to use

The SQL File is opened in a special SQL Editor Using it, is straightforward :

Enter your SQL Statement

Right click -> Execute All

Download