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.

mysql-je.jar

the JDBC "client" library

libmysqlje.dll

JNI library for Windows

libmysqld.dll

MySQL embedded library for Windows

libmysqlje.so

JNI + MySQL embedded library for Linux x86 systems

libmysqlje.jnilib

JNI + MySQL embedded library for Mac OS X 10.3

share

contains runtime support files for MySQL

src

contains Java source files

c-src

contains C source files

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

Compiling

Basically, mysql-je needs three components:

libmysqld

the embedded library, as distributed by mysql.com

libmysqlje

contains "glue" to integrate native code into Java applications. This library contains Java (JNI) bindings for the MySQL API (which is originally written in C).

mysql-je.jar

implements the JDBC API. Add this file to your classpath.

  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

this site is hosted by

SourceForge.net Logo


JetBrains is supporting this project

IntelliJ IDEA