Copyright 2005-06 Peter Schäfer, peterschaefer@users.sourceforge.net
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...
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.
Sources and binaries for Windows, Linux, and Mac OS X are available from the Download Page at sourceforge.net.
|
the JDBC "client" library |
|
JNI library for Windows |
|
MySQL embedded library for Windows |
|
JNI + MySQL embedded library for Linux x86 systems |
|
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 |
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.
Make sure that your classpath contains mysql-je.jar
java -cp mysql-je.jar;...
In your Java code, register the JDBC driver class:
DriverManager.registerDriver(new com.mysql.embedded.jdbc.MySqlEmbeddedDriver());
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
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.
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).
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.
PreparedStatements
are implemented and make use of the binary
protocol introduced in MySQL 4.1. Statements
use the old,
text based protocol.
CallableStatements
can be used to call stored procedures.
Only named parameters are supported (see below).
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).
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)
Batched statements are not supported. Instead, you can separate statements by semicolons (see above).
CallableStatement
accepts only
parameter names, not paremeter indexes.
{call}
and {?=call}
escape sequences are not supported.
Use MySQL's native syntax for CallableStatements
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.
Updatable ResultSets
are not
supported.
Scroll sensitive ResultSets
are not supported.
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).
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.
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.
Basically, mysql-je needs three components:
|
the embedded library, as distributed by mysql.com |
|
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). |
|
implements the JDBC API. Add this file to your classpath. |
Java library
build.xml
is an ANT build script for
compiling Java sources and packaging mysql-je.jar
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
native libraries for Linux (Intel 32bit)
currently, both libraries
are linked into one file: libmysqlje.so
see
Makefile.linux
for details.
native libraries for Mac OS X (PowerPC)
currently, both libraries
are linked into one file: libmysqlje.jnilib
see
Makefile.darwin
for details
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.
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.
this site is hosted by |
|
|
|
JetBrains is supporting this project |