|
|
1.
Introduction
Welcome to the Database Tools tutorial. In this tutorial we are
going to cover some of the basic and advanced database-specific
functionality contained in MyEclipse. Most of this functionality
is contained in what is known as the
Database Explorer, but the
Database Explorer also serves as a starting point for
other advanced database features like Hibernate mapping
generation, Spring-Hibernation support and others.
Other MyEclipse tutorials are available that cover those
additional functionalities, but for this tutorial we are going to
focus on the specific database functionalities that any developer
or DBA will find useful during development.
|
|
2.
Suggested Audience
This tutorial is intended for developers who are somewhat
familiar with MyEclipse so you'll recognize navigation within the
IDE, and understand some of the more common concepts like
"Views". This tutorial also assumes the reader has
knowledge of databases and some basic knowledge of working with
databases in Java (e.g. is familiar with JDBC drivers).
In this example we will be using MySQL as our DBMS, but the
reader is free to use any other database that they feel
comfortable with, as long as they are able to follow the
instructions in the tutorial. The tutorial also assumes that you
already have a DBMS configured for use and will not go into
detail on how to do that since it varies from database to
database.
If either MyEclipse or databases makes you feel uncomfortable, we
certainly welcome you to follow through the tutorial anyway, even
just skipping to the demonstrations (movies) to get a feel for
the types of tools you have at your disposal in MyEclipse.
If you wish to learn more about either MyEclipse or MyEclipse's
database features please have a look at either our product
Documentation for more
material or our
Resources section
respectively.
|
3.
System Requirements
This tutorial was created with MyEclipse, a local install of
MySQL 5.0 and the 5.0 version of the Connector/J MySQL JDBC
driver. If you are using different version of these applications
most of these screens and instructions should still be very
similar.
If you notice portions
of this tutorial looking different than the screens you are
seeing, please
let us know and we will make sure to
resolve any inconsistencies.
|
4.
Getting Started
To begin using the database functionality in MyEclipse you'll
need a connection to a database. In order to get a connection to
a database you first need to configure the JDBC driver that will
be used to establish that connection. MyEclipse ships over 30
pre-configured templates for JDBC drivers so in most cases
creating a new JDBC driver configuration shouldn't be harder than
simply finding your template, using the drop down menu, and then
adding the JAR to the classpath that contains the driver. If you
cannot find a template for the driver you want to use, the driver
templates are completely free-form so you can directly configure
all the important details as necessary.
After configuring your JDBC driver, you will be able to create a
connection to your database using the new connection wizard as
shown below:
After connecting to the database we can immediately start
exploring the elements it contains. For the purpose of this
tutorial we are going to stick to
tables, but MyEclipse also has functionality to view and
edit other database elements such as views, triggers, and stored
procedures.
Within this database are 2 sample tables (we create the 3rd later
on) and some simple records. If you'd like to create the same
database for local use, the create-table scripts and sample data
used in this tutorial can all be found in the
Resources section
below.
Now that we are connected to the database, we'll drill down to
our
user table and query it, to see what in it:
You now have an idea of how easy MyEclipse makes navigating your
information, and it even generates your
select statements for you. But how can we update the
information in the database? Normally you would write
update statements to change the values in your database,
but that can be quite time consuming. Using the
Edit Data feature in MyEclipse we can quickly change the
values in our tables using a GUI. For example, let's change
Ryan's
COUNTRYCODE value from Japan (JP) to United States (US):
So far so good. We've learned how to connect to our database,
query our tables for information and even update our table's
information. The basics are taken care of so let's see how
MyEclipse handles some of the more advanced database features
that developers and DBAs will be familiar with; things like
indices, creating tables and foreign keys.
|
5.
Working with the Database
As we mentioned in the section above we have taken a look at the
database basics so far in MyEclipse, but stopping at the basics
isn't enough. Developers and DBAs need more control over their
DBMSs such as managing indices on their tables, creating and
dropping tables, and manipulating foreign key constraints.
MyEclipse handles all of this and we'll have a look at indices
first.
Creating an index can be an important performance tool for
developers. DBMSs will typically create indices on keys in a
table. However, there may be other fields that are used regularly
in ORDER BY or WHERE clauses that require an index as a
performance enhancement.
In this tutorial let's hypothetically say we do a lot of ordering
on the
FIRSTNAME of our users, so let's create an index on that
field:
Creating a Table
During design, a developer of a database-enabled application will
be modeling the domain of his application and creating
corresponding tables within which he can persist the
application's data. Let's have a look at how MyEclipse handles
table creation by adding a new table called
user_relationship that represents a pair of user IDs
that are related in some way.
Now that we've created the
user_relationship table we should make both the new
fields foreign key references to the
ID field of the
user table. This is necessary because in order for two
users to have a valid relationship, they both have to be valid
users and that dictates that they need to be represented by
records in the
user table.
So far so good. We have created our new table and even enforced
our key constraints appropriately for it. Let's keep going and
see what else MyEclipse can do.
|
6.
Generation and Visualization
Now that we have our new
user_relationship table along with the appropriate
constraints, let's say that we want to get an idea of how our
domain model is looking for our application. That's going to be
easy since MyEclipse has a very cool and easy to use ER Diagram
generation wizard that will visualize our table and their
relationships for us.
Naturally, as the table structure and relationships are changed
you can refresh the diagram view to see those changes reflected
in it. The diagram view can also be exported or an image file and
included in a design document which will make documenting an
early design, or understanding an existing complex system, much
easier.
Generating DDL
Lastly, we want to cover the DDL generation functionality in
MyEclipse as this feature is important during the development
stages where a team may need to replicate or backup a database
they are designing.
To generate the DDL (e.g. "create-table statements")
for your specific table you can simply right click on it in the
table node and select
Generate > DDL:
With the DDL generated into a SQL editor for you, you can easily
run it, modify and run it or just save it to a .sql file for
backup purposes.
|
7.
Conclusion
We hope you have found the introduction to the MyEclipse Database
Tools presented in this tutorial helpful. If you have a favorite
database feature that we didn't cover in this tutorial and you
think we should have,
let us know.
Or, if you have comments about this tutorial or
suggestions/questions for us, please
let us know. We always value our user's
feedback especially on educational materials such as these.
|
8.
FAQ
-
Does MyEclipse support Oracle, MS SQL Server,
PostgreSQL, HypersonicSQL and other DBMSs?
-
Yes, MyEclipse's database functionality is built on top of the
JDBC standard. That means any database that has a high quality
JDBC driver should function just fine with MyEclipse.
-
I am having a hard time using the Database
Explorer, it is acting erratic.
-
Almost every time we have gotten reports of database
functionality problems in the
Database Explorer it was due to an old or buggy JDBC
driver (for example the older
classes12.zip driver for Oracle). Normally upgrading
your driver or switching to another high quality driver solves
the problem.
-
I cannot see or create foreign keys in MySQL
like you did in the tutorial, why?
-
You need to make sure you are using the
InnoDB database type or other database type that
supports foreign keys in MySQL. The older default
MyISAM table type does not support foreign keys.
-
How can I fine-tune the limits of my result
sets or the look of my ER Diagrams?
-
All these settings can be found under
Window > Preferences > MyEclipse > Database
Explorer
-
MyEclipse FAQ
|
9.
Resources
Below are links to resources that we hope will help answer most
of the questions you could have while working your way through
this tutorial. You will notice that a lot of the material linked
is geared towards MySQL; this is intentional. A lot of the
database basics are common across all major DBMSs and since our
tutorial uses MySQL we decided to try and keep everything
applicable and easy to follow. Have a look:
Resources
MySQL
Database Basics
Other JDBC Drivers for other DBMSs
|
10.
Feedback
We would like to hear from you! If you liked this tutorial, has
some suggestions or even some corrections for us please let us
know. We track all user feedback about our learning material in
our
Documentation Forum.
Please be sure to let us know which piece of MyEclipse material
you are commenting on so we can quickly pinpoint any issues that
arise.
|
