 | |
Return to: The
Learning Curve Journals»
In the last journal I explored working with the Sun Java Studio Creator bundled database. I found that the Java DB was included with the Java Studio Creator IDE and available for building my applications. The Java DB is Sun's supported distribution of the open source Apache Derby database.
Because I'm building Java applications, I might wish to install the Java DB into my production environment. I could then maintain all the great features that Java has to offer – security being one of them – throughout my deployment. Plus the Java DB is available at no cost under the Apache project.
But because I run a number of projects under various databases, I also need to consider using existing databases with my new Java applications – at least for now. Plus it's cool to know that I can quite easily connect those applications I build with Java Studio Creator – with minimal effort – to my database infrastructure.
In this journal, I explore connecting to and working with Microsoft SQL Server 2000 and MySQL in the Java Studio Creator IDE. I also explore working with the Java DB by creating new databases and tables and populating them with data.
Contents
Supported Databases
Java Studio Creator includes drivers - or connectors - to a variety of databases. These are:
Note that the
DataDirect driver bundled with Java Studio Creator that allows for these connections is free to use with a local installation of the IDE. To connect to third-party databases in a production environment (with the exception of MySQL), I need to purchase a license from DataDirect.
Connecting to the databases listed above is intuitive. I demonstrate how easy it is to connect to Microsoft SQL Server 2000 below. I can then easily work with tables and build queries with drag-and-drop functionality.
Don't see your favorite database in the list? Worry not! It's not difficult to manually import a driver, as I demonstrate in the next section for the MySQL database.
Connecting to Microsoft SQL Server 2000
Connecting to Microsoft SQL Server 2000 is about as easy as it gets. What's great is that I can easily work with my database tables visually in Java Studio Creator once I add a data source to the IDE.
Adding a Data Source to the IDE
First I right-click the Data Sources node in the Servers window and select Add Data Source from the pop-up window. I can also choose Add Data Source from the Data drop-down menu. A new dialog box then opens that lets me add a new data source, as shown in Figure 1.
In this Data Source dialog box, I can define the following:
To connect to my Microsoft SQL Server database, I complete the dialog as shown above in Figure 1.
The IDE gives me the option to test the connection to the database, which is a good way to make sure that I can access a new database both within the IDE and outside of my application.
Once I confirm that the connection to the database works, I choose a Validation Table by clicking the Select button. This allows the application server to use a connection pool and validate the database connection by querying the database. This process does not write to the table, and no rows in the table are needed. It is best not to use a table with a large number of rows or a table that is accessed often.
I click Add to complete the process, close the window, and return to the IDE. Note how the database now shows up in my Servers window as shown in Figure 2. I can expand its node to view its tables. I can also right-click a table name and choose View Data to view the content in a table, or double-click a table name. From here I can build my applications with drag-and-drop functionality.
Next, I connect to a MySQL database.
Connecting to MySQL
MySQL is not one of the preconfigured data source options within Java Studio Creator. To connect to this database, I need to obtain the JDBC driver — or connector as it is also named — and add it. This procedure is outlined in this SDN article: Creating Database Server Types and Data Sources.
First I download the MySQL JDBC driver from the www.mysql.com website.
Note: The article I noted above references an older edition of the driver, version 3.1. At the time of this writing, version 5.0 is the latest, and operates fine within Java Studio Creator. To view the latest driver editions, go to the mySQL.com download page: http://dev.mysql.com/downloads/
Then I extract the file on my computer and return to the Add Data Source window, shown in Figure 3. From the Server Type drop-down menu, I choose Add Server Type.
Next I click New and choose the JDBC driver file that I downloaded and uncompressed from mySQL.com:
mysql-connector-java-5.0.3-bin.jar Under Display Name, I type mySQL. I click the Suggest button to have Java Studio Creator fill in the driver class name and the URL template. The defaults that the IDE picks work just fine. Figure 4 displays the information I just added.
So now I can add a MySQL database to the Servers window for drag-and-drop functionality, just as I did for the Microsoft SQL Server database. I go back and right-click my Data Sources node in the Server window and choose Add Data Source. Figure 5 shows a MySQL database that I connected to.
Working With the Bundled Database
In my last journal, I provided an overview of the bundled database included with Java Studio Creator. This database is named The Java DB and is also known as Derby by the Apache project. Now I want to explore more fully how I can create databases and manage tables with the Java DB.
Because the Java DB doesn't include a graphical user interface that would allow me to create new databases and users, I use features that Java Studio Creator provided to do this. I can also work with most databases in this way, because Java Studio Creator allows me to execute SQL commands directly from the IDE. I could create tables, as an example, in both the Microsoft SQL Server and MySQL databases that I attached to above.
Databases, Data Sources, and Schemas
Java Studio Creator includes a number of data sources, including VIR (Vehicle Incident Reporter), JumpStartCycles, and TravelDB. These are actually database schemas – they all sit within one physical database called
sample. This is apparent if I right-click one of the data sources in the Servers window, such as VIR, and choose Modify Data Source. As shown in the left dialog box of Figure 6, the Database URL string is: jdbc:derby://localhost:21527/sample This URL string tells me that the data source VIR belongs to the
sample database. There is another way I can tell too: If I click the Schemas tab in the Modify Data Source dialog box, and then click the Get Schemas button, I see all schemas in the sample database as shown in Figure 6. A number of system schemas are included as well as Travel and JSC (JSC is named JumpStartCycles in the Servers window). Note: Make sure the database server is started by right clicking Bundled Database Server in the Servers window and choosing Start Bundled Database.
Creating a New Database
I would now like to create a new database named
location that contains a list of all the zip codes in the U.S., along with their central longitude and latitude values. I would like to create a new database, rather than create a new schema in the sample database. Because the Java DB doesn't offer a stand-alone program that would allow me to create a new database, I use an option in the JDBC URL that the driver understands: by sending create=true as a command to the database.I do so by first right-clicking the Data Sources node in the Servers window and choosing Add Data Source.
In the Add Data Source window that opens, I populate it with data as shown in Figure 7 (I can enter any password in the Password field). I first enter the name that I would like to reference the database by in Java Studio Creator in the Data Source Name field. Note that I can't, however, enter a new Database Name as this field is dimmed. But I'll get around this shortly. I also choose Derby as the Server Type and enter any value for the User ID and Password. The user ID and password values I enter will get created in the next step along with the database.
I now change the connection string by typing the following into the Database URL field and adding the database name
location and create=true string: jdbc:derby://localhost:21527/location;create=true This is the trick to creating a new database! So how do I execute this URL string? It's easy. Just click the Test Connection button and the URL string is passed to the Java DB and the new database — and user — is created.
Note that after a few moments, the window shown in Figure 8 is displayed. The database was created and a connection was made, however I have not yet established a validation table, so I also get the error shown. I'll correct that shortly.
I click OK in the Location Connection Status window, and then revise the connection string by removing
create=true as shown in Figure 9. When I click the Add button, my location database appears in my Servers window, however with a red "x" next to it because there is no validation table defined yet.
As a side note, I can look at my directory structure to see where the new database physically sits. In a default Windows installation it would sit here
C:\Program Files\Sun\Creator2_1\SunAppServer8\derby\databases I can browse the sub-directories for both the
sample and location databases. This is where data sits, along with binary log files and more. It is good to see that by executing the URL string above, a whole directory structure is created for my location database. I can then manually back up files and copy the database into production if needed.Building Tables
Now that I have created my
location database, I wish to create zip code and validation tables, and also populate them with data. To do this, I will use another feature in Java Studio Creator that allows me to execute SQL commands directly from the IDE.First, I start the Run Query feature by right-clicking my location database in the Servers window under Data Sources, and choosing View Data as shown in Figure 10.
The new window that opens allows me to manually type SQL commands or execute a file with a list of SQL commands in it. I wish to manually create the zip codes table by typing the following into the Query window (it's best to break it into several lines as shown):
create table location.ZipCodes ( zipcode varchar(10) NOT NULL, latitude DOUBLE, longitude DOUBLE, city varchar(50), state varchar(50), county varchar(50), zipclass varchar(50) ); I then click the Run Query button which causes this SQL command to execute. The
ZipCodes table is created with the various fields that I specified. The results are shown in Figure 11. Note that the database returns 0 row(s) affected, which is displayed in the results window.
Next I create a validation table by entering the following in the Query window and clicking the Run Query button again:
create table location.validationtable (testcol varchar(1)); Fixing the Validation Table Error
Now that I have a validation table established, I need to fix the validation table error that is causing a red "x" next to my table name. I right-click the
location table and choose Modify Data Source. From here, I click the Select button to open the Select Validation Table window as shown in Figure 12.
I choose the
validationTable that I created above and close the dialog box by clicking Select. Then I click Test Connection to see my validation table found. Finally, I click Modify to close the remaining dialog box. My location database sits as a node under Data Sources with no error, and I can expand on the node to view the zipcodes and validationtable tables.Populating the Zip Code Table
Now I populate the
zipcodes table with data from a source file by importing the file with a SQL command. I use an internal Derby procedure that reads in a textual comma-delimited set of data.To populate the
zipcodes table:
Executing More Complex SQL Commands in a Script File
The Query window (shown in Figure 13) also allows me to execute more complex scripts that contain a sequence of SQL commands.
If I click the
 file icon (in the Query window), I can load a command file, as I demonstrate below. These steps drop the tables I just created and then re-create them with just a couple of mouse-clicks.To load a command file:
You should now have the location tables created and fully populated.
Code Sample 1
-- drop tables so database is empty drop table location.ZipCodes; drop table location.validationtable; -- Create new tables create table location.ZipCodes ( zipcode varchar(10) NOT NULL, latitude DOUBLE, longitude DOUBLE, city VARCHAR(50), state varchar(50), county varchar(50), zipclass varchar(50) ) ; create table location.validationtable (testcol varchar(1)); -- Load the tables (be sure to replace [directory] with location of ZIP_CODES.txt on your machine. CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE(‘LOCATION’,‘ZIPCODES’, ‘[directory]ZIP_CODES.txt’, ‘,’, ‘"’, null,0);
Note that when you initially installed Java Studio Creator, a series of SQL command files were executed that created all of the database schemas in the
sample database. You can view these SQL command files in this directory under a typical Windows XP installation:C:\Program Files\Sun\Creator2_1\rave2.0\startup\samples It is useful to see how these databases were created and populated through SQL commands because you can customize and execute any of these commands yourself to manage your databases.
More Information About Derby
I demonstrated one of the internal procedures above that allowed me to import a comma-delimited file into my
ZipCodes table. There are many other procedures available for use, and they can be viewed in the Apache Derby documentation. I had success in running some procedures but not others in the Java DB. Derby documentation in general is available at this link: http://db.apache.org/derby/manuals/index.html
Next Time
In the next series of articles, I'll build further on this code to develop an exciting application that pulls together a lot of the concepts discussed here to plot locations on a map.
More Developer Resources
For more tech tips, articles, and expert advice for developers, visit the Java Studio Creator developer resources on the Sun Developer Network (SDN) at
/jscreator/.
|
| ||||||||||||||||||||||||||||||||||||||||||||||||
| |
| ||||||||||||
Print-friendly Version
icon as shown in Figure 14. Confirm that "semicolon at end of line" is selected and then click "Run Query." This is the correct option because the create-location-derby.sql file terminates each SQL command with a semicolon
