develop sql databases with eclipse, sqlexplorer, … · develop sql databases with eclipse,...

31
Develop SQL databases with Eclipse, SQLExplorer, and Clay Yes, you can use Eclipse to develop database-enable applications Skill Level: Intermediate Robi Sen ([email protected]) Freelance Writer Department13 29 Nov 2005 Learn how to use Eclipse and the SQLExplorer plug-in to connect to any database that supports a JDBC driver. These tools allow you to view database schemas, view table data, add and edit table data, and write, edit, and execute SQL. You will also learn how to use Azzurri Clay to create Entity Relationship Diagrams (ERDs), reverse-engineer databases, add tables, edit tables, delete tables, edit relationships, add indexes, and change your underlying data model into different SQL dialects. Section 1. Before you start One of the more frustrating things for any developer is the number of tools that must be used to develop applications -- from text editors, version control software, and deployment tools to database design and management tools. This can become annoying and cumbersome when working on projects, requiring you to switch back and forth between different tools. It can also be very expensive, not only in terms of software licenses but also in terms of developer time because developers have to learn new software applications. The Eclipse IDE helps solve this problem by offering plug-ins that allow you to use the IDE as a single point to interact with all of your applications. Eclipse plug-ins offer specific functionality while maintaining a consistent user interface. This tutorial will look at how to connect and interact with databases directly from Eclipse, allowing developers a single place to develop software, design, and manage databases. Develop SQL databases with Eclipse, SQLExplorer, and Clay © Copyright IBM Corporation 1994, 2008. All rights reserved. Page 1 of 31

Upload: doanquynh

Post on 05-Sep-2018

242 views

Category:

Documents


0 download

TRANSCRIPT

Develop SQL databases with Eclipse,SQLExplorer, and ClayYes, you can use Eclipse to develop database-enableapplications

Skill Level: Intermediate

Robi Sen ([email protected])Freelance WriterDepartment13

29 Nov 2005

Learn how to use Eclipse and the SQLExplorer plug-in to connect to any databasethat supports a JDBC driver. These tools allow you to view database schemas, viewtable data, add and edit table data, and write, edit, and execute SQL. You will alsolearn how to use Azzurri Clay to create Entity Relationship Diagrams (ERDs),reverse-engineer databases, add tables, edit tables, delete tables, edit relationships,add indexes, and change your underlying data model into different SQL dialects.

Section 1. Before you start

One of the more frustrating things for any developer is the number of tools that mustbe used to develop applications -- from text editors, version control software, anddeployment tools to database design and management tools. This can becomeannoying and cumbersome when working on projects, requiring you to switch backand forth between different tools. It can also be very expensive, not only in terms ofsoftware licenses but also in terms of developer time because developers have tolearn new software applications.

The Eclipse IDE helps solve this problem by offering plug-ins that allow you to usethe IDE as a single point to interact with all of your applications. Eclipse plug-insoffer specific functionality while maintaining a consistent user interface. This tutorialwill look at how to connect and interact with databases directly from Eclipse, allowingdevelopers a single place to develop software, design, and manage databases.

Develop SQL databases with Eclipse, SQLExplorer, and Clay© Copyright IBM Corporation 1994, 2008. All rights reserved. Page 1 of 31

To follow along with the examples, you will need Eclipse V3.1, SQLExplorer, AzzurriClay, and Apache Derby, which are all no-cost open source tools. You should befamiliar with basic concepts in Eclipse, as well as SQL and database administration.

About this tutorial

SQLExplorer allows the Eclipse IDE to access any database that supports a JDBCdriver -- practically every major database, providing a simple, convenient, and freemethod for working with databases without having to use expensive and unfamiliartools. Since SQLExplorer is also a Eclipse plug-in, you can work within the sameIDE without having to switch between tools and products. Another Eclipse plug-in,Azzurri Clay, complements SQLExplorer by allowing you to visualize your databaseas an Entity Relationship Diagram (ERD) or convert your database from one type toanother.

Prerequisites

You will need the following tools to follow along:

Eclipse V3.1Download the latest version of Eclipse from Eclipse.org. It's possible to useV3.0 with some changes, but this article will not cover V3.0.

SQLExplorer V2.2.4Download SQLExplorer, the Eclipse IDE plug-in for database query/schemaviewer for nearly any kind of JDBC database.

Azzurri Clay V1.2.0 (freely available Core edition)Download Azzurri Clay, a database design tool that runs as a plug-in inEclipse. It offers a user interface for designing database models graphically.

Graphical Editor Framework V3.1.1Download the Graphical Editor Framework V3.1.1 Runtime.

Apache Derby 10.1Download Apache Derby V10.1. Alternatively, download Cloudscape V10.1from IBM. Cloudscape is an IBM-supported version of Derby.

This tutorial will be done using Windows® XP, but with minor changes, everythingshould work on Linux®, Mac OS X, or Solaris.

System requirements

You need to set up Eclipse V3.1, SQLExplorer, Azzurri Clay, and Apache DerbyV10.1 before we begin.

developerWorks® ibm.com/developerWorks

Develop SQL databases with Eclipse, SQLExplorer, and ClayPage 2 of 31 © Copyright IBM Corporation 1994, 2008. All rights reserved.

1. Download and install Eclipse V3.1. We use <eclipse> in place of Eclipse'shome directory, which, for this tutorial, is C:\software\eclipse. You caninstall Eclipse V3.1 anywhere you like, but make sure to substitute yourEclipse installation home directory when you see <eclipse>.

2. Download the Graphical Editor Framework (GEF) V3.1.1. The GEF isneeded for SQLExplorer and Azzurri Clay to work correctly.

3. Copy the GEF to your Eclipse installation directory, <eclipse>, andunpack it. This should install its files in <eclipse>/eclipse/plugins and<eclipse>/eclipse/Features.

4. Download SQLExplorer V2.4.4.

5. Copy the SQLExplorer.zip to your <eclipse>/eclipse directory and unpackit.

6. Download the Azzurri Clay V1.2.0 Core edition files. Copy the zip file intoyour <eclipse>/eclipse directory and unpack it.

7. Download Apache Derby V10.1.

8. On Windows, you can use the Windows Installer. For this tutorial, you willinstall the Apache Derby V10.1 database to C:\derby. Use <derby> torefer to the Derby home directory.

9. On Windows, you can simply install Derby by double-clicking the installer.Make sure to read the installation instructions and the read-me file.Depending on your system, you may need to set class path variablesmanually.

10. The Derby installer should also install an example database you will usefor this tutorial in <derby>\demo\databases\toursDB.

You should now be ready to use SQLExplorer and Azzurri Clay from Eclipse V3.1 towork with Derby.

Section 2. Setting up SQLExplorer and Derby

Let's start by launching SQLExplorer and hooking it into Derby. SQLExplorer doesnot, by default, support Derby, so we must create a new JDBC driver type forSQLExplorer. This will be useful if you ever plan to use other databases with JDBCdrivers that SQLExplorer does not support by default.

ibm.com/developerWorks developerWorks®

Develop SQL databases with Eclipse, SQLExplorer, and Clay© Copyright IBM Corporation 1994, 2008. All rights reserved. Page 3 of 31

Launching SQLExplorer

Now that we've installed the software, it's time to start working with SQLExplorer:

1. Select Eclipse and launch it.

2. After Eclipse launches, navigate to Window > Open Perspective >Other, as shown in Figure 1.Figure 1. Eclipse V3.1 menu for switching perspectives

3. Select the SQLExplorer perspective. You should see something likeFigure 2.Figure 2. Basic SQLExplorer view

developerWorks® ibm.com/developerWorks

Develop SQL databases with Eclipse, SQLExplorer, and ClayPage 4 of 31 © Copyright IBM Corporation 1994, 2008. All rights reserved.

If for some reason you do not see the SQLExplorer view, it is most likely becausethe GEF plug-in or SQLExplorer has not been installed correctly. If this is the case,uninstall these plug-ins and reinstall them. Typically, all you have to do to uninstall aEclipse plug-in is delete all the files in <eclipse>\eclipse\plugins and<eclipse>\eclipse\features related to that plug-in.

Creating a Derby driver

Before using SQLExplorer with a database, you must first set up a driver so you cancreate a JDBC connection to your database:

1. For Derby, the first thing you need to do is mouse-over to the Drivers

ibm.com/developerWorks developerWorks®

Develop SQL databases with Eclipse, SQLExplorer, and Clay© Copyright IBM Corporation 1994, 2008. All rights reserved. Page 5 of 31

Panel and select the Create new Driver icon or simply right-click, as inFigure 3.Figure 3. Creating a new driver in SQLExplorer

2. You should see the New Driver screen, as in Figure 4.Figure 4. The New Driver screen

developerWorks® ibm.com/developerWorks

Develop SQL databases with Eclipse, SQLExplorer, and ClayPage 6 of 31 © Copyright IBM Corporation 1994, 2008. All rights reserved.

3. In the Name field, enter something like Derby.

4. In the field Example URL, enter something likejdbc:derby:c:/derby/demo/databases/toursDB. This example URL will beused to prefill Derby database connection URL strings, and it is notimportant if it exactly matches where you have set up your Derbydatabase.

5. Next, select the Extra Class Path Panel. Here, you need to add threeDerby .jar files to the class path: dbjcc.jar, dbjcc_license_c.jar, derby.jar.To do this, select Add and browse to the Derby lib directory. If youinstalled Derby in C:\Derby, this should just be C:\Derby\lib. You shouldsee something like Figure 5.Figure 5. Browsing to the Derby lib directory

ibm.com/developerWorks developerWorks®

Develop SQL databases with Eclipse, SQLExplorer, and Clay© Copyright IBM Corporation 1994, 2008. All rights reserved. Page 7 of 31

6. Select the first .jar file, which should be dbjcc.jar. Click OK. Now repeatthese steps for dbjcc_license_c.jar and derby.jar.

7. Finally, you need to enter the Driver Class Name, which isorg.apache.derby.jdbc.EmbeddedDriver. Once entered, click OK.

Your Derby driver should now be set up, and you should see it in the Drivers panelwith a little green check mark near it. If you see a red circle with an "X" in it, selectthe driver and edit it to make sure it is configured properly. The most commonproblems are missing .jar files or mistyping something.

Also, you have to do this step since, by default, SQLExplorer does not have Derbyas a driver type. If you look at the Drivers panel, you will see there are a number ofdrivers for other databases, and to create a connection to one of those, you need tofollow similar steps.

Creating a connection to Derby

Now that you have created a driver, you need to create an alias, which is essentiallya database connection instance. You can have many aliases, each connecting to adifferent database with the same driver type. Follow these instructions to set up analias:

1. In the same panel where you saw drivers, you should see a tab calledAliases. Select this tab.

developerWorks® ibm.com/developerWorks

Develop SQL databases with Eclipse, SQLExplorer, and ClayPage 8 of 31 © Copyright IBM Corporation 1994, 2008. All rights reserved.

2. Select the Create new alias icon or simply right-click and select Createnew alias.

3. You should see the Create new alias screen, which should look likeFigure 6.Figure 6. Create new Alias screen

4. First, you need to name your alias. For this example, you will use theTours Database supplied with the Derby installation. You can name youralias anything you like, but for this example, use toursDB.

5. Now, select the Derby Driver from the drop-down menu.

6. Next, you need to add the database connection URL for your Derbydatabase. If you installed Derby in a folder called Derby of your C: drive,then your connection URL should look like this:jdbc:derby:<derby>/demo/databases/toursDB, where the start is the typeof connection, the database type, and the full directory path to your Derbydatabase. A literal might look like this:jdbc:derby:c:/derby/demo/databases/toursDB.

7. You can leave the User Name and Password fields blank.

8. Select OK.

9. You should now see your new database alias in the Alias panel.

10. Double-click on the toursDB alias.

11. A connection dialog should pop up, prompting you to provide a user nameand password. For the toursDB database, you do not need one, so leavethese blank.

ibm.com/developerWorks developerWorks®

Develop SQL databases with Eclipse, SQLExplorer, and Clay© Copyright IBM Corporation 1994, 2008. All rights reserved. Page 9 of 31

12. Also note that there is a checkbox called Auto Commit, which you canuncheck if you do not want operations committed, unless you specificallydirect SQLExplorer otherwise.

13. Now select OK.

You should now see progress bars indicating that SQLExplorer is connecting to thetoursDB. When SQLExplorer has made its connection, you should be returned to themain view and now see the Database Structure panel, which should contain thetoursDB.

If you receive an error, the most common reasons are mistyping the connectioninformation or having incorrect connection information.

Once you create an alias to the toursDB, you can start working against it. If youcreate new Derby databases, all you have to do is create new aliases because youhave already defined the driver for Derby. Next, let's start using SQLExplorer to workwith the database.

Section 3. Working with SQLExplorer with Derby

Now that you are connected to toursDB, let's start exploring SQLExplorer. Thissection will look at how to use SQLExplorer to view your database schema, andcreate, edit, and execute SQL.

The toursDB database example

First, let's look at the toursDB schema. Being able to view a schema, table, tablecolumns, and attributes is very useful when creating SQL. The most common way todo this in SQLExplorer is to use the Database Structure view:

1. Navigate to the Database Structure view and click on APP to expand it.

2. Click on TABLE to expand the tables in the database. You should seesomething like Figure 7.Figure 7. Database Structure View showing the toursDB Schema,including system tables

developerWorks® ibm.com/developerWorks

Develop SQL databases with Eclipse, SQLExplorer, and ClayPage 10 of 31 © Copyright IBM Corporation 1994, 2008. All rights reserved.

From here, you can select a table and view its structure, such as columns, indexes,primary keys, and foreign keys. You can also view data in the table, as well as get arow count.

ibm.com/developerWorks developerWorks®

Develop SQL databases with Eclipse, SQLExplorer, and Clay© Copyright IBM Corporation 1994, 2008. All rights reserved. Page 11 of 31

Using the SQL editor

Let's assume you want to do a simple select query on the CITIES table. To open anew SQL editor, navigate to the Connections Panel where you can click on NewSQL Editor or right-click on the database name, as shown in Figure 8.

Figure 8. Opening a SQL editor in SQLExplorer

This will open a new panel called SQL Editor. For this example, you can use thissimple query:

Listing 1. Simple query

SELECTCITY_NAME, COUNTRY, AIRPORTFROM CITIESORDER BY CITY_NAME DESC

Then select the icon that looks like a little running man, and you should see theoutput of your query in the SQL Results pane. Your results should look like Figure 9.

developerWorks® ibm.com/developerWorks

Develop SQL databases with Eclipse, SQLExplorer, and ClayPage 12 of 31 © Copyright IBM Corporation 1994, 2008. All rights reserved.

Figure 9. Query results from a simple SELECT in SQLExplorer

In SQL Results, you should see the query instance number, as well as four icons.The red icon closes the pane, the icon that looks like two pages copies the queryresult to your clipboard, and the other two return all rows or more rows, respectively.

Database Structure View

You can accomplish much the same thing by going to the Database Structure Viewand right-clicking on the CITIES table and selecting Generate Select in SQL Editor(see Figure 10).

Figure 10. SQLExplorer supports auto-generation SQL for SELECT andCREATE SQL commands

ibm.com/developerWorks developerWorks®

Develop SQL databases with Eclipse, SQLExplorer, and Clay© Copyright IBM Corporation 1994, 2008. All rights reserved. Page 13 of 31

By selecting Generate Select in SQL Editor, SQLExplorer will create a new SQLEditor panel with a SELECT statement, including all of the fields in your table.

SQLExplorer is a simple tool with nice time-saving features. One is SQL History.When you are writing SQL, you often make queries, execute them, change yourquery, and execute again and again. Sometimes it is useful to compare a previousquery or to roll back changes you made to your code. SQLExplorer creates a historyof all queries executed, allowing you to easily roll back to previous versions orcompare queries. To use SQL History, right-click on a table, such as CITIES, andselect Generate Select in SQL Editor, which should create a new SQL Selectquery, as shown in Listing 2.

Listing 2. Creating a new SQL Select query

SELECT CITY_ID, CITY_NAME,COUNTRY, AIRPORT, LANGUAGE,COUNTRY_ISO_CODEFROM CITIES

Go ahead and run it by selecting the Run icon. You should see the output of this

developerWorks® ibm.com/developerWorks

Develop SQL databases with Eclipse, SQLExplorer, and ClayPage 14 of 31 © Copyright IBM Corporation 1994, 2008. All rights reserved.

query in SQL Results. Now change the Select query to add an Order By clause, likethat shown in Listing 3.

Listing 3. Adding an Order By clause

SELECT CITY_ID, CITY_NAME,COUNTRY, AIRPORT, LANGUAGE,COUNTRY_ISO_CODEFROM CITIESOrder BY CITY_NAME ASC

Now select Run again. You should see the output ordered by CITY_NAME inascending alphabetical order. But what if you had a complex query you had madechanges to, and you wanted to go back to your first working query? Undo is ofteninsufficient. With SQLExplorer, you can simply recall any query that ran from theSQL History, which you can find in the far-right pane, by selecting SQL History. Youcan also go to Eclipse Window > Show View > SQL History. You should seeresults like those shown in Figure 11.

Figure 11. SQL History panel shows every query run and lets you recall themto the SQL Editor anytime

To recall a query, all you have to do is right-click on it and select Open in Editor.

SQLExplorer and other databases

ibm.com/developerWorks developerWorks®

Develop SQL databases with Eclipse, SQLExplorer, and Clay© Copyright IBM Corporation 1994, 2008. All rights reserved. Page 15 of 31

At the beginning of this tutorial, we mentioned that SQLExplorer can be used on anydatabase that has a JDBC driver. As long as you can provide SQLExplorer the .jarfiles for your database along with the JDBC driver, you should be able to work withyour favorite database. To do this, all you need to do is create a driver and an alias,just like you did for Derby. For example, Listing 4 contains some databases and theirconnection information.

Listing 4. Databases and connection information

DB2Driver Class: COM.ibm.db2.jdbc.app.DB2DriverConnection URL: jdbc:db2:<database>Driver .jar/.zip: db2java.zip

SybaseDriver Class: com.sybase.jdbc2.jdbc.SybDriverConnection URL: jdbc:sybase:Tds:<host>:<port>/<database>Driver .jar/.zip: jconn2.jar

OracleDriver Class: oracle.jdbc.driver.OracleDriverConnection URL: jdbc:oracle:thin:@ <host>:<port>:<sid>Driver .jar/.zip: classes12.zip

Microsoft SQLServerDriver Class: com.microsoft.jdbc.sqlserver.SQLServerDriverConnection URL: jdbc:microsoft:sqlserver://localhost:1433Driver .jar/.zip: mssqlserver.jar, msbase.jar, msutil.jar

PostgreSQLDriver Class: org.postgresql.DriverConnection URL: jdbc:postgresql://<server>:<port>/<database>Driver .jar/.zip: postgresql.jar

To connect to another database, just use a driver and create an alias, like you didwith Derby.

In the next section, you will look at how to use Azzurri Clay to visualize a databaseas an ERD, as well as how to manage a database from Eclipse.

Section 4. Working with Azzurri Clay

So far, you have learned to use SQLExplorer to perform queries and view dataagainst a database with SQLExplorer, but you often need to do more than just runqueries. As a developer, you need to add, edit, or delete the underlying schema ofthe database. In order to understand a database, you need to visualize it as an ERDor convert it from one database type to another. There are a number of excellenttools for doing this, but most of them are very expensive, proprietary, and onlysupport a few specific databases.

Azzurri Clay is a plug-in that is free to use and allows you to work with any databasewith a JDBC driver. In this section, we use Clay to reverse-engineer the toursDB

developerWorks® ibm.com/developerWorks

Develop SQL databases with Eclipse, SQLExplorer, and ClayPage 16 of 31 © Copyright IBM Corporation 1994, 2008. All rights reserved.

database, create an ERD to visualize it, add new relationships to the model, edittable column names, and look at how to change your data model from Derby toanother database type.

Getting started with Clay

To get started, you need to create a data model file. First, you should switch to adifferent perspective, such as the default Java perspective. Next, create a newproject in that perspective. For this tutorial, you can create one called tours and pointit to a directory at c:\tours.

1. Now go to File > New > Other. You should see the Database Modelingfolder. Expand it and select the Azzurri Clay Database Design Diagramwizard, then select Next, as shown in Figure 12.Figure 12. The Azzurri Clay Database Design Diagram wizard

2. You should now see a New Database Model form, like in Figure 13. Makesure to select the tours project (call the file tourDB.clay), then select ANSI

ibm.com/developerWorks developerWorks®

Develop SQL databases with Eclipse, SQLExplorer, and Clay© Copyright IBM Corporation 1994, 2008. All rights reserved. Page 17 of 31

SQL-92 from the SQL Dialect, as shown in Figure 13. You will notice thatthere are a number of SQL dialects supported by Clay, but Derby is not inthis list. So for the moment, just select ANSI SQL-92. We will introduceClay to the Derby JDBC driver later.Figure 13. The New Database Model wizard form

3. Now select Finish.

You should now see a panel called toursDB.clay, as well as the toursDB.clay file inyour project, like in Figure 14.

Figure 14. The Database Model Graphical Editor

developerWorks® ibm.com/developerWorks

Develop SQL databases with Eclipse, SQLExplorer, and ClayPage 18 of 31 © Copyright IBM Corporation 1994, 2008. All rights reserved.

Reverse-engineering the toursDB example

Now let's create an ERD of the toursDB in the Derby directory. There are a couple ofways to do this, but the easiest is to just reverse-engineer the database by clickinganywhere in the Database Model Graphical Editor and selecting Reverse EngineerDatabase. You should now see a panel for selecting or creating connections todatabases, like in Figure 15.

Figure 15. The Connection wizard to reverse-engineer a database

ibm.com/developerWorks developerWorks®

Develop SQL databases with Eclipse, SQLExplorer, and Clay© Copyright IBM Corporation 1994, 2008. All rights reserved. Page 19 of 31

Now select Add New. You should see the Edit Connection Panel. Figure 16 showsthe panel with the correct information to connect to the Derby toursDB informationalready filled in. For reference, the settings for creating a connection are:

• Connection Type -- This should be your Database Type. For Derby, itwill be Unknown.

• Alias -- This is the name you want to use for your database. For thisexample, it's toursDB.

• URL -- Your database connection URL, which will bejdbc:derby:</derby>/demo/databases/toursDB or the literal if your Derbyhome is C:\derby, then use jdbc:derby:c:/derby/demo/databases/toursDB.

• User -- Your user name, which is blank for toursDB.

• Password -- Your password, which is blank for the toursDB.

• JDBC Driver -- The class path and name for your JDBC driver, which isorg.apache.derby.JDBC.EmbeddedDriver.

• Driver Location -- This is the path or paths to the .jar files needed foryour driver. For this example, we need to add three .jar files: dbjcc.jar,dbjcc_license_c.jar, and derby.jar, which are found in the <derby>\lib orC:derby\lib directory.

Figure 16. Connection setting to create a connection to the Derby toursDB

developerWorks® ibm.com/developerWorks

Develop SQL databases with Eclipse, SQLExplorer, and ClayPage 20 of 31 © Copyright IBM Corporation 1994, 2008. All rights reserved.

Now select OK and you should be taken back to the Reverse Engineer a Databasewizard and the Connect panel. Select Next and you will be prompted to select aschema. Select App and click Next.

Now you should see the Select Database tables to import into model screen. Selectthe >> button to select all tables, like in Figure 17, then select Finish.

Figure 17. The tables you can select to import into your diagram

ibm.com/developerWorks developerWorks®

Develop SQL databases with Eclipse, SQLExplorer, and Clay© Copyright IBM Corporation 1994, 2008. All rights reserved. Page 21 of 31

Working with ERD

You should now see an ERD of the database, including all the tables, their columns,primary keys, foreign keys, and relationships (see Figure 18). NOTE: The tableswere rearranged to fit, and you may have to scroll to see all your tables.

Figure 18. The resulting ERD of the toursDB

developerWorks® ibm.com/developerWorks

Develop SQL databases with Eclipse, SQLExplorer, and ClayPage 22 of 31 © Copyright IBM Corporation 1994, 2008. All rights reserved.

Clay allows you to do almost anything you can do in a commercial product. Forexample, to add a relationship between the FLIGHTS table and theFLIGHTS_HISTORY table, all you need to do is go to the left-hand corner of thegraphical editor and select the Foreign Key Reference icon. Now select theFLIGHTS table, then the FLIGHTS_HISTORY table. You should now see arelationship appear between the tables, like in Figure 19.

Figure 19. Creating a relationship between two tables with Clay

ibm.com/developerWorks developerWorks®

Develop SQL databases with Eclipse, SQLExplorer, and Clay© Copyright IBM Corporation 1994, 2008. All rights reserved. Page 23 of 31

You can edit a relationship just by double-clicking it. If you select the relationship youjust created and double-click it, you should see the Foreign Key Reference pop up. Itshould look something like Figure 20.

Figure 20. Foreign Key Reference panel shows relationships between keys

developerWorks® ibm.com/developerWorks

Develop SQL databases with Eclipse, SQLExplorer, and ClayPage 24 of 31 © Copyright IBM Corporation 1994, 2008. All rights reserved.

Go ahead and just click Cancel since the relationship is fine. If you wish to edit arelationship to point to another field, you can easily do this by clicking on the key andselecting another key.

When you manage or edit relationships in a database you change the underlyingrules that govern that database and sometimes create problems that will result indata corruption or other unintended consequences. Azzurri Clay helps you with thisby validating your database to make sure it does not violate your SQL dialect,relationships, or other rules. We look at how Clay's database validation works in thenext section.

Section 5. Using Clay to validate your database

ibm.com/developerWorks developerWorks®

Develop SQL databases with Eclipse, SQLExplorer, and Clay© Copyright IBM Corporation 1994, 2008. All rights reserved. Page 25 of 31

Relational databases are sophisticated systems that allow you to not only store databut define things like what type of data can be stored, as well as rules such asconstraints. The more sophisticated your database rules and logic and the more youchange them the more likely you will accidentally make a change that will causeproblems.

To help you deal with this, Clay provides a mechanism to validate your database.

Validating your database

Another nice feature of Clay is that every time you save your model, Clay validatesyour data model. This means that if your data model violates any of the rules foryour database, it will report them to you and help you resolve them.

To see this in action, right-click in the Graphical Editor and select Save. You shouldsee the Tasks panel in Eclipse show several warnings, like those shown in Figure21.

Figure 21. Data model validation in Clay

If you select the first problem, "Invalid column name: LANGUAGE (reserved word),"and right-click on it, you should see a option to Go To. Select that, and you shouldbe taken to the Clay Edit Table panel for CITIES and the field in question,LANGUAGE, should be highlighted (see Figure 22). For this example, let's changeLANGUAGE to LANGUAGES and select OK.

Figure 22. Clay Edit Table panel for CITIES

developerWorks® ibm.com/developerWorks

Develop SQL databases with Eclipse, SQLExplorer, and ClayPage 26 of 31 © Copyright IBM Corporation 1994, 2008. All rights reserved.

Now go back to the graphical editor and right-click and select Save again. Youshould see in the Problems panel that there are now only two problems.

Clay's data model validation is a powerful feature that can save you a lot of time andeffort. Another useful feature is Clay's ability to move your data model from oneformat to another. While SQL is a standardized language, most databaseimplementations have slightly different implementations of SQL, which makesmigrating SQL from one database system to another problematic. Clay can help youwith this by allowing you to easily change your database SQL dialect from, forexample, Derby to MySQL V4.0. To do this, right-click in the graphical editor andselect Edit Database Model. This will show you the pop-up panel shown in Figure23. Then select SQL Dialect and choose what database SQL dialect you which tomigrate to. Then select OK.

Figure 23. Editing the database model SQL dialect

ibm.com/developerWorks developerWorks®

Develop SQL databases with Eclipse, SQLExplorer, and Clay© Copyright IBM Corporation 1994, 2008. All rights reserved. Page 27 of 31

When you select OK, you should see a pop-up that lets you know that there may besome data types you need to change and to look at your task list for problems (seeFigure 24).

Figure 24. Clay lets you know what problems to address

developerWorks® ibm.com/developerWorks

Develop SQL databases with Eclipse, SQLExplorer, and ClayPage 28 of 31 © Copyright IBM Corporation 1994, 2008. All rights reserved.

From here, if you have problems, go to the task list, select a problem, and select GoTo to address the specific problem, like you did earlier. Nothing could be easier, andClay guides you through the process of making a model compliant with the newdatabases' SQL format. Because Clay also generates a model file, it is possible tocreate various iterations of a schema, as well as SQL Dialects, and store them inversion control, allowing you to easily roll back or migrate to different databaseversions during development.

Clay provides a majority of the features needed for a developer or databaseadministrator to design, build, port, and manage the schema of their databasessuccessfully.

Section 6. Summary

In this tutorial, you looked at how you can use SQLExplorer and Azzurri Clay toconnect to an Apache Derby database. You also learned how to work withSQLExplorer's SQL Editor, as well as how to visualize a database, edit it, create

ibm.com/developerWorks developerWorks®

Develop SQL databases with Eclipse, SQLExplorer, and Clay© Copyright IBM Corporation 1994, 2008. All rights reserved. Page 29 of 31

relationships, and validate a data model in Clay. You also learned how to connect toother databases besides Derby (such as MS SQL and Oracle) by creating a driverand an alias to that database.

Eclipse with SQLExplorer and Clay plug-ins allow you to create a seamlessdevelopment experience. With Eclipse, you can focus on developing your applicationwithout the annoyance of switching among programs or the cost of expensive andproprietary software.

developerWorks® ibm.com/developerWorks

Develop SQL databases with Eclipse, SQLExplorer, and ClayPage 30 of 31 © Copyright IBM Corporation 1994, 2008. All rights reserved.

Resources

Learn

• Read the developerWorks article "Getting started with the Eclipse Platform" toget an overview of the Eclipse Platform, including its origin and architecture,and details on how to install Eclipse and plug-ins.

• To learn more about Eclipse, visit developerWorks' Eclipse project resources.

• Visit the developerWorks Open source zone for extensive how-to information,tools, and project updates to help you develop with open source technologiesand use them with IBM's products.

Get products and technologies

• Download the latest version of Eclipse from Eclipse.org.

• Download SQLExplorer, the Eclipse IDE plug-in for database query/schemaviewer for nearly any kind of JDBC database.

• Download Azzurri Clay, a database design tool that runs as a plug-in in Eclipse.Clay offers a user interface for designing database models graphically. It canalso create a database model by reverse engineering an existing database.

• Download the Graphical Editor Framework V3.1.1 Runtime.

• Download the latest version of Apache 2 from Apache.org.

• Download Apache Derby V10.1.

• Innovate your next open source development project with IBM trial software,available for download or on DVD.

Discuss

• Get involved in the developerWorks community by participating indeveloperWorks blogs.

About the author

Robi SenRobi Sen is the vice president of Department13 LLC, which is a boutique informationand technology services company focusing on Fortune 1000 companies andgovernment organizations. He spends most of his time on developing largeenterprise-scale applications and has spoken extensively on the topics of enterpriseapplication integration and service-oriented architectures.

ibm.com/developerWorks developerWorks®

Develop SQL databases with Eclipse, SQLExplorer, and Clay© Copyright IBM Corporation 1994, 2008. All rights reserved. Page 31 of 31