MySQL, and MM.MySQL, a JDBC driver for MySQL, are open-source tools that allow Java developers on Linux and other UNIX-like systems to develop full-featured applications. Both have similar histories - they started out as internal tools to fix a particular problem and grew to be much larger projects than the developers originally intended.

I started developing MM.MySQL in May of 1998, to learn socket and JDBC development and to fix some problems with the original JDBC driver for MySQL, GWE. GWE development had ceased at that point and some features I required in a JDBC driver for MySQL weren't present.

Since that time MM.MySQL has developed into a fairly capable Type IV (all Java) JDBC driver for MySQL. Depending on the JVM you're using, it supports the JDBC-1.2 or JDBC-2.0 API. It's been used with a majority of open-source Java tools like JBoss, Enhydra, and Tomcat, as well as Cocoon and Turbine. It's also supported by popular development environments such as Forté, JBuilder, IBM VisualAge for Java, and Macromedia UltraDev.

The MySQL Database System
MySQL is an open-source relational database management system developed by MySQL-AB. It's fast, reliable, and easy-to-use with a full client/server protocol. MySQL also has a very practical set of features developed in close cooperation with MySQL's users. Originally developed to handle large databases much faster than existing solutions, it's been successfully used in highly demanding production environments for several years. Though under constant development, MySQL offers a rich and useful set of features such as:

• Full multithreaded operation using operating system-level threads and SMP if available and supported by the operating system
• C, C++, Eiffel, Java, Perl, PHP, Python, and Tcl APIs

• The server runs on many different platforms, including Windows, Linux, FreeBSD, and Solaris, as well as many common UNIX variants
• Rich data-type support, including signed/unsigned integers 1, 2, 3, 4, and 8 bytes long, FLOAT, DOUBLE, CHAR, VARCHAR, TEXT, BLOB, DATE, TIME, DATETIME, TIMESTAMP, YEAR, SET, and ENUM types
• Full support for SQL GROUP BY and ORDER BY clauses. Support for group functions (COUNT(), COUNT(DISTINCT), AVG(), STD(), SUM(), MAX(), and MIN())
• Support for LEFT OUTER JOIN with ANSI SQL and ODBC syntax
• Ability to mix tables from different databases in the same query (as of Version 3.22)
• A privilege and password system that's flexible and secure and allows host-based verification; secure passwords because all password traffic is encrypted when you connect to a server
• Handles large databases; installations of MySQL with some databases contain 50,000,000 records
• Full support for several different character sets including ISO-8859-1 (Latin1), big5, ujis, and more
• Current versions of MySQL support the creation of full text indexes on tables

Installing MySQL
If you're using a Red hat-based Linux distribution or your Linux distribution has support for Red hat Package Management (RPMs), it's easier to install MySQL from RPMs. The RPM files you want to download from www.mysql.com/ are MySQL-VERSION.i386.rpm and MySQL-client-VERSION.i386.rpm, where VERSION is the version number of the server you want to install. These RPMs contain the MySQL server and the MySQL command-line client utilities that allow you to administer and query your databases. Once you've downloaded the RPMs, issue the following command as root:

rpm -i MySQL-VERSION.i386.rpm MySQL-client-VERSION.i386.rpm

This will install the MySQL server and client and also place entries in /etc/rc.d/ to start the MySQL server at boot up.

If your system doesn't support installations via RPM, you'll need to download a binary distribution of MySQL. Their names are similar to mysql-VERSION-OS.tar.gz where VERSION is the version of the MySQL server and OS is the operating system the binary distribution has been compiled for. Once you've downloaded the binary distribution for your operating system, perform the following steps as root:

shell> groupadd mysql
shell> useradd -g mysql mysql
shell> cd /usr/local
shell> gunzip < /path/to/mysql-VERSION-OS.tar.gz | tar xvf -
shell> ln -s mysql-VERSION-OS mysql
shell> cd mysql
shell> scripts/mysql_install_db
shell> chown -R mysql /usr/local/mysql
shell> chgrp -R mysql /usr/local/mysql
shell> bin/safe_mysqld --user=mysql &

After installing MySQL using RPMs or from a binary distribution, you need to create logins for users in MySQL. If you have Perl and DBI installed on your system as many standard Linux distributions do, you can use the "mysql_setpermissions" script to create users and passwords for your MySQL server. If you don't have Perl or DBI installed, read Chapter 6 in the manual that comes with the MySQL server since the topic of MySQL security could take up an entire magazine article!

Installing MM.MySQL
The latest versions of MM.MySQL are always available at the MM.MySQL distribution Web site, www.worldserver.com/mm.mysql. There's usually a stable and a beta release.

If you're using a Java-2 JDK, the easiest installation method is to place the mysql-bin JAR file in the jre/lib/ext subdirectory that exists in your JDK's home directory. The JVM will then automatically load the driver when needed.

If you're using a Java-1 JDK or don't have access to the jre/lib/ext subdirectory of your Java-2 JDK, add mysql-bin.jar to your CLASSPATH environment variable. For example, in UNIX with CSH you'd do the following:

setenv CLASSPATH $CLASSPATH:/path/to/mysql-bin.jar

Often application servers don't refer to the CLASSPATH variable when determining where to load third-party libraries from. For example, with any J2EE-compliant application server you're deploying JSPs on, you can place the mysql-bin JAR file in the WEB-INF/lib sub-directory of your application. The application server will automatically load the driver from there. Other application servers require CLASSPATH to be set via their configuration files. Details for this configuration option can be found in the documentation for your application server.

Typical Usage Patterns
In most cases, to use a JDBC driver from your code you should follow these steps:

1. Call Class. for Name ("org.gjt.mm. mysql.Driver").newInstance()
2. Get a java.sql.Connection by calling DriverManager.getConnection("some jdbc URL")

   MM.MySQL's JDBC URL syntax is the following (items in square brackets are optional):

   jdbc:mysql://[hostname][:port]/[dbname][?param1=value1][¶m2=value2]

Where the hostname is the host that the database server is running on (defaults to localhost), the port is the TCP/IP port the server is listening on (only if it's listening on a nonstandard port), and dbname is the name of the database to connect to. The parameters (see Table 1) after the "?" allow you to pass more configuration information to the driver.

3. Create a java.sql.Statement by calling Connection.createStatement() or Connection.prepareStatement() on the Connection instance you created in Step 2.
4. Issue a query on the statement from Step 3 by calling executeQuery() for queries that select rows, or executeUpdate() for queries that insert/update/delete rows.
5. Use Connection.prepareStatement() to create a PreparedStatement that allows you to issue parameterized queries that also take care of quoting special characters and dealing with binary data.

Using MySQL-Specific Functionality
MySQL has some features that can't be accessed from the methods provided in the standard JDBC API. To access them you need to cast the Statement or PreparedStatement object you're using to org.gjt.mm.mysql.Statement or org. gjt.mm.mysql.PreparedStatement respectively.

From either of these classes you can call the methods getLastInsertID() to get the value created for any AUTO_INCREMENT field and getLongUpdateCount() to get the larger update count that MySQL can produce as a long. Listing 1 shows how to do this.

The JDBC API version 3, under development by JavaSoft, will have a portable way of retrieving AUTO_INCREMENT values; MM.MySQL will support that method when it becomes available.

Storing/Retrieving Serialized Java Objects
Because MySQL has support for BLOBs, it's relatively easy to store serialized Java objects in the database. The easiest way to do this is to use java.sql.PreparedStatement's setObject() method to get the objects into the database. If you're using a version of MM.MySQL earlier than 2.0.3, you'll need to use org.gjt. mm.mysql.Util's readObject() method to read serialized objects from the database. If you have MM.MySQL version 2.0.3 or newer, use java.sql.ResultSet's getObject() method to retrieve serialized objects from the database. Listing 2 provides an example of how to do this, assuming you have a table named "serObject" in your database with a column named "user" and one named "cert" (e.g., to store a cryptographic certificate).

As you've seen, MM.MySQL makes it extremely easy to store and retrieve Java objects. Because MySQL requires no special syntax to work with BLOBs, it's easy to manipulate data that contains them, which makes it easier to write queries that deal with BLOBs.

Conclusion
I hope you've seen that MM.MySQL and MySQL are simple to use, yet robust and powerful. They're both flexible tools to use in developing Web-based applications, but simple enough for beginning Java developers to learn.

As MM.MySQL is an open-source product, I'm always looking for suggestions for further development and assistance with development, so please contact me at mmatthew@thematthews.org for more information.