mysql-je 1.3

MySQL Embedded Library for Java

Copyright 2005-06 Peter Schäfer, peterschaefer@users.sourceforge.net

What is it ?

mysql-je is a library for integrating MySQL into Java applications.
You can run your own, private instance of MySQL and access it through a standard JDBC interface.

It's based upon the MySQL embedded library, version 5.0.15
Earliers versions of the embedded library (>=4.1 upwards) should work too, but they are not actively supported.
Newer versions of the embedded library (>= 5.0.16) must be used with caution, because they are not quite reliable.
According to MySQL, a more stable library will be released with 5.1.

For more details on the embedded library see the MySQL manual

This project is a spin-off from Jose Chess Database.
Download jose to see embedded MySQL in live action...

What's it not

mysql-je is not a replacement for a full-featured MySQL server.

If you need a client/server environment, download the server from mysql.com, and use Connector/J, MySQL's client library for Java.

Connector/MXJ is another library for embedding MySQL into Java applications, but it's targeted at server-side applications.
mysql-je is better suited for single-user desktop applications.

Some parts of the JDBC API are not implemented. See Limitations.

File Download

Sources and binaries for Windows, Linux, and Mac OS X are available from the Download Page at sourceforge.net.

Usage

Using mysql-je is similar to using other JDBC drivers. Note however, that mysql-je is not just a JDBC client, it runs also the server process.
To put it another way: the client is the server.

1. Make sure that your classpath contains mysql-je.jar

           java -cp mysql-je.jar;...

2. In your Java code, register the JDBC driver class:

           DriverManager.registerDriver(new com.mysql.embedded.jdbc.MySqlEmbeddedDriver());

3. Set up the connection URL and properties.
   Note that we do not actually create network connections. The URL is only meant to identify the database type and the database scheme.

           String url = "jdbc:mysql-embedded/TestDatabase";

   additional parameters can be appended to the URL but it's usually more convenient to use a Properties map:

           Properties props = new Properties();

   pass the path to the native library files:

           props.put("library.path", ".../mysql-je/lib");

   alternatively, you can use System.setProperty("java.library.path")

   Since we're about to launch a new MySQL server, we have to pass a couple of initialisation parameters.
   These parameters are more or less identical for a standalone MySQL server.

   Most important is the --datadir parameter that points to the location of your database files.
   --basedir contains runtime support files for MySQL (where the mysql/share directory is located).

   For more details, see the MySQL manual

           props.put("--datadir", "data");
           props.put("--basedir", "C:/mysql");
           props.put("--default-character-set","utf8");
           props.put("--default-collation","utf8_general_ci");

   You can also put MySQL initialisation parameters into a separate configuration file (mysql.ini):

           props.put("--defaults-file","mysql.ini");

   For more details on MySQL configuration files, see the MySQL manual.
   The following configuration group sections are enabled in mysql.ini

      mysql-je, embedded, mysqld, mysqld-5.0

4. Create the connection:

           Connection connection = DriverManager.getConnection(url,props);

   The server is initialized when you create the very first connection. Subsequent calls to DriverManager.getConnection() connect to the same server.
   Configuration parameters are ignored for all but the first connection.

5. There are two ways to shutdown the database. By default, it will shutdown automatically when the last open connection is closed.
   If you pass the configuration parameter shutdown=false, the server process has to be shut down explicitly by issuing a SHUTDOWN command (see below).

   In any case, make sure that all connections are closed when exiting your application (and do avoid System.exit()).

   If the application exits (or crashes) while there are still open connections, you risk corrupting database files.
   The configuration parameter --myisam-recover is useful for repairing cases of data corruption (on MyISAM tables, that is).
   
   

6. Scrollable (scroll insensitive) ResultSets are supported, but be aware that the complete result set will be stored in memory.
   The preferred way to create a scrollable result set is

      Connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READONLY)

   The default behaviour is to use forward-only result sets. This is different from Connector/J, which uses scrollable result sets by default.

   For compability with Connector/J the following methods are supported, too:
   Statement.setFetchSize(-1) creates a scroll insensitive result set.
   Statement.setFetchSize(Integer.MIN_VALUE) creates a forward-only ("streaming") result set.

7. PreparedStatements are implemented and make use of the binary protocol introduced in MySQL 4.1.
   Statements use the old, text based protocol.

8. CallableStatements can be used to call stored procedures. Only named parameters are supported (see below).

9. Several statements can be executed with one call. Separate the statements with semicolons (;) and use Statement.getMoreResults() to retrieve a series of result sets.
   This is not possible for PreparedStatements (a limitation imposed by MySQL).

Limitations

1. mysql-je is not fully compliant with the JDBC specifications.

   Large parts of DatabaseMetaData and ResultSetMetaData are not implemented.
   If you need to retrieve meta data, you can use MySQL's meta data facilities (Information Schema, and the SHOW statements)

2. Batched statements are not supported. Instead, you can separate statements by semicolons (see above).

3. CallableStatement accepts only parameter names, not paremeter indexes.
   {call} and {?=call} escape sequences are not supported. Use MySQL's native syntax for CallableStatements

4. Character data are always encoded in UTF-8.

   The collation sequence defined for the database is ignored. But it does influence sort order and string comparison.
   To avoid confusion, you should set up your database tables with a UTF-8 collation.

5. Updatable ResultSets are not supported.
   Scroll sensitive ResultSets are not supported.

6. Multi-Threading: Connection objects can be shared among multiple threads, provided that two threads do not execute different statements over the same connection, at the same time.

   For prepared statements on Unix systems it is important that preparation and execution are done within the same thread. You must not prepare the statement in one thread, then pass it for execution to another thread. If you keep a pool of prepared statements, make sure that your pool is able to handle this. Sharing a ResultSet among threads is safe.

   This restriction does not apply on Windows (i.e. you are allowed to pass a prepared statement to another thread).

   Note that creating connections is quite cheap (a matter of few milliseconds). You might consider allocating a new connection for each thread (instead of using pooled connections).

7. Don't have two application instances write to the same data directory. Doing so will risk data corruption and/or program crashes. Within one instance, you may allocate as many connections as you like.

Extensions

• In com.mysql.embedded.jdbc.MyResultSetMetaData:
  getOriginalTableName()
getOriginalColumnName()
  extend the JDBC API. These methods retrieve the original names of aliased result columns (e.g. USER.Name AS UserName).

• KILL QUERY and KILL CONNECTION can be used to interrupt long-running queries (note that these commands are not available in the embedded library. They're an extension for mysql-je).

  The interrupted query will immediately throw an SQLException. Here's an example:

  Query Thread

  User-Interface Thread

  Connection connection = ...; PreparedStatement pstm = null; ResultSet res = null; try { pstm = connection.prepareStatement("..."); pstm.execute(); // this call might take a long time res = pstm.getResultSet(); while (res.next()) { ... } } catch (SQLException exception) { if (exception.getErrorCode()==MySQLError.ER_QUERY_INTERRUPTED) { // this exception was thrown in // response to killQuery() // it's intentional, not an error } else { // otherwise it's an error throw exception; } } finally { if (res!=null) res.close(); }

  ... // interrupt long running query Statement stm = connection.createStatement(); stm.executeUpdate("KILL QUERY"); // note that this call has no // noticeable effect in this thread, // but it in the parallel thread ...

• SHUTDOWN will shut down the server immediately. By default, the server shuts down automatically when the last open connection is closed.
  If you want more control over the shut down process, pass shutdown=false as connection parameter.

• The connection parameter unzip=... will read a Zip file and unpack it into the data directory. Use this parameter with caution, as all data will be clobbered.
  However, this is useful for quickly restoring a test database.

Compiling

Basically, mysql-je needs three components:

1. Java library

   build.xml is an ANT build script for compiling Java sources and packaging mysql-je.jar

2. native libraries for Windows

   for Windows, there are two native libraries:

   libmysqld.dll contains the embedded library code. This is the exact library that is distributed with MySQL

   libmysqlje.dll contains JNI "glue" for linking native methods to Java

3. native libraries for Linux (Intel 32bit)

   currently, both libraries are linked into one file: libmysqlje.so
   see Makefile.linux for details.

4. native libraries for Mac OS X (PowerPC)

   currently, both libraries are linked into one file: libmysqlje.jnilib
   see Makefile.darwin for details

5. native libraries for other Platforms

   I haven't built native libraries for other platforms. The code is supposed to compile on 64 bit platforms, though I haven't tried that, yet. Examining the Makefiles for Linux, or Mac OS X might be helpful.

Licensing

mysql-je is distributed under the terms of the GNU Lesser General Public License

MySQL embedded library is copyrighted by MySQL AB. There are different licensing options available, for commercial users and Open Source projects.

Before distributing mysql-je with your project, make sure that you comply with MySQL's Licensing Policy.

Acknowledgements