Binding to MySQL in Java Studio Creator

Originally, I was planning to use this article to cover use of Java Studio Creator for simple Web apps that could be used on a mobile phone, but I realized that this and other articles I was planning to write about would require data in something a little more complete than the pointbase Java database that comes with Java Studio Creator by default.

Creating database-driven apps requires a database, and that requires data. This makes it necessary to create schemas and populate them with some sample data. This is of course possible with pointbase, but without any admin tools it means writing a bunch of SQL and just takes longer than if you have access to some higher-level tools.

Also, for many people working on a project, at least right now pointbase is probably not a consideration. Serious contenders for backend databases for J2EE enterprise Web apps of course include Oracle, DB2, SQL Server, and a host of other commercial options. On top of that, many people wanting something a bit higher in the food chain but maybe without a lot of cost may consider Postgres, Firebird, or MySQL. It is the latter I have chosen for my future examples, since it is quick and easy to download and install on any supported platform (which is pretty much all of them), is simple to use, and has some very nice higher-level support tools. More importantly, it also has a JDBC driver (essential for use in Java Studio Creator).

That said, most of the points in this article about using Java Studio Creator with MySQL above are transferable to other databases. I will not attempt to cover the installation and administration of those other databases, because if you want to use them you probably know that already. However, I will point out the points where JDBC setup, database URL template setup, and other places that are likely to be customized for different databases.

MySQL is chosen because future articles I write will include scripts to set up and populate MySQL databases with data that will be used for the examples. If you want to try and adapt these to other databases to follow the articles, I wish you luck, but politely decline to offer technical assistance in converting the data across to the database of your choice.

Gathering and Installing the Software

The first step will be obtaining MySQL, the JDBC drivers, and (optional) some tools that make working with MySQL easier.

Depending on your platform, you may already find that you have MySQL installed on your machine. Most Linux distributions in particular come with MySQL either installed by default, or in the accompanying software packages with your distribution. You can use the software installation application included with your version of Linux to search for and install MySQL if it is provided. Failing that, you can download binaries for Linux from the same place as binaries for Mac and Windows.

For all platforms, if you need to install MySQL (and on Windows and MacOSX you probably will), the downloads page is:

http://dev.mysql.com/downloads/mysql/4.1.html

There are quite a lot of different versions, so you may need to scroll down to find the platform binary you need.

After download, installation will be what you are expecting on your platform—probably RPM for Linux, msi for Windows, and dmg for MacOSX. Install as you would any other app; you should hit no problems.

If you are prompted for a root or admin username and password, remember what they are because you will need them later.

You also need to grab the JDBC driver for MySQL; the download page for this is:

http://dev.mysql.com/downloads/connector/j/3.1.html

The binaries are pure Java jars; hence, they are the same across all platforms. Just grab the zip or tar.gz based on your preference, and unzip or untar it. Once done, find the mysql-connector-java-3.1.8-bin.jar file (the number may be higher depending on when you read this article) and copy it to somewhere that makes sense on your machine. You are going to have to tell Creator about this jar file when you want to use MySQL, so put it somewhere you can remember.

As an optional step, also grab the MySQL Control Center from

http://dev.mysql.com/downloads/other/mysqlcc.html

This is an older tool for which support has dropped, but still one I find very useful.

Depending on your install, you may have chosen not to run MySQL by default. On Windows if you did this, you should start the MySQL Service from the services; on Linux, start the service as standard for your distribution. If it is pre-installed on Linux, the chances are it is already running.

Finally, I can highly recommend phpmyadmin if you are so inclined (http://www.phpmyadmin.net/home_page/). It requires a PHP installation on a working Web server to run, and I will not cover the installation of that here. Suffice to say that I like it better than MySQLCC even though you have to have Apache with PHP or similar running first.

If you do have trouble with any of these steps, I would refer you to the excellent guide at

http://www.webdevelopernotes.com/…/…tutorial.php3

which is an excellent step-by-step guide for setting up MySQL on Linux and Windows. Hopefully, it will also provide some useful pointers to MacOSX users and others out there as well.

Add an Example Database

Download MySQLCreatorDemo with the example project in it and unpack it to a suitable directory. Inside, you will find a Java Studio Creator project, and also a file called creatordemo.sql. This is a MySQL dump file that you can restore into a complete schema with example data. To do this:

bring up a command window and log in in to MySQL using the following command:

mysql -u root -p

And enter the root password you selected on install when prompted.

If you chose not to use a root password, leave off the -p off at the end.

When you see the mysql> prompt, type the following command:

create database creatordemo;

then type

exit

Next, you want to import the schema and data. This step also creates a user called creatordemo with a password of creatordemo to access the database you are populating.

From the command line, type:

mysql -u root -p creatordemo < creatordemo.sql

You should now have the demo database ready to use in Creator.

Setting up a MySQL Data Source

After installing MySQL, unpacking and copying the jar file, and creating a user and loading in some test data, it is time to actually use that data from Java Studio Creator.

• First, start Java Creator Studio.
• When it has loaded, from the top-left pane, right-click on Data Sources and choose Add Data Source.
• A dialog will appear. For Server Type, select Add Server Type. Another dialog will appear.
• Hit the New… button and navigate to the jar file you downloaded and copied earlier (called mysql-connector-java-3.1.8-bin.jar).
• Next, set the display name to MySQL.
• For the Driver Class Name, enter com.mysql.jdbc.Driver.
• Finally, for the URL template, enter jdbc:mysql://#HOSTNAME:3306/#DATABASE.

Note: These same steps should be applied for any new JDBC-compliant database you want to use in Creator. There are already several defined (such as Oracle and DB2), so check those first, but if yours is not there, you can add it. You will obviously need different jar file settings, and will need to look up the Driver Class Name and the JDBC URL used in the documentation for the JDBC driver for that DB. Also, choose a name that describes the database.

Once you have finished entering the details here, click Close.

Next:

• In the Add Data Source dialog, select the new MySQL Server type.
• Set the Data Source Name to creatordemo, the Database Name to creatordemo, Host Name to localhost, User ID to creatordemo, and Password to creatordemo. The Database URL should be filled in for you automatically.
• Click the Test Connection, and you should get confirmation that the connection was successful. If not, doublecheck all of your fields and try again.

Once it works, click Add, and you should see a new Data Sources entry called creator demo. You can expand this to look at the tables defined (you should see three: courses, scores, and students).

The MySQL datasource is defined and ready to go.

Create a Simple Demo Creator App

Using your new Data Source, you can quickly create a new Creator Project that uses it. This will be nothing fancy, just a student selection list and a grid of courses and scores for now. In the next few articles, you will start doing more ambitious projects using third-party libraries and the like, but for now it’s enough to see the data flowing out of MySQL.

• Create a new project and call it something like MySQLCreatorDemo.
• Drag a Listbox and a Data Table onto the page.
• From the server navigator, drag and drop the students table over the top of the list box and select Fill the List when the dialog comes up.
• Now, drag and drop the scores table onto the Data Table.
• Double-click on the scores rowset at the bottom of the page; then drag the courses table into the top pane of the resulting query editor.
• Right-click on the student_id line and select Add Query Criteria.
• Select the Parameter radio button, and click OK
• Right-click on the courses_id line and select Add Query Criteria.
• Select the Value radio button (should already be active) and for the value field type courses.id (this joins the tables).
• Save all, and then switch back to the Page1.jsp tab. Click on the table until the whole thing is selected, and right-click and select Table Layout.
• Double-click on the scores.course_id and scores.student_id fields in the right pane to remove them. Then, in the left pane, scroll down to the bottom and double-click on courses.course_number and courses.course_name to add them to the grid. Then, select each of these new fields in turn and use the Up button to position them first and second in the table.
• Optionally, run through the Displayed columns and change the header text for each into something more descriptive than the field name.
• Click OK to update the table details.

Now, you need to hook up the dropdown list to affect the results of the table.

• First, right-click on the dropdown list and select the Auto Submit on Change option.
• Next, double-click on the dropdown list and it will create a new event handler for you to edit. Add the following code into that handler:

try {
   dataTable1Model.setObject(1, dropdown1.getValue());
   dataTable1Model.execute();
} catch (Exception e) {
   log(“person change exception”, e);
   error(“Exception changing person id: ” + e);
} // end try catch

• In the constructor for the page bean, add the following code:

// Initialization Code
try {
   this.studentsRowSet.execute();
   this.studentsRowSet.next();
   dataTable1Model.setObject(1,
      this.studentsRowSet.getObject(“ID”));
} catch (Exception ex) {
   throw new FacesException(ex);
} // end try catch

• Now, build your project, and assuming there are no errors, run it.
• You should see a list of Students last names. Selecting one should update the scores you see in the data table.

Conclusion

This article may already be familiar to many of you if you have already experimented with databases other than pointbase that came with Java Studio Creator. If so, I apologize and please know that I will be back on track with more interesting topics in the next article. However, moving on to more involved topics required example data to be in a database other than pointbase for my own comfort (and hopefully yours) and this article has covered the steps in making that possible. Now that I can provide MySQL dump files to populate example data, I can get back to concentrating on the cooler things that Java Studio Creator can do, including using third-party components, creating pages suitable for viewing on mobile devices, and so forth.

About the Author

Dick Wall is a Lead Systems Engineer for NewEnergy Associates, A Siemens Company based in Atlanta, GA that provides energy IT and consulting solutions for decision support and energy operations. He can be reached for comment on this and other matters at dick.wall@newenergyassoc.com, or check out his blog at http://www.voiceoftheresistance.com.