Chapter 10. Connecting Hibernate to MySQL
Setting Up a MySQL Database
As nifty and self-contained as HSQLDB is, your project may not be in the market for an embedded Java database. In fact, you’re likely going to want to interface with some existing, external database server. Luckily, that’s just as easy (assuming you have the database already up and running, which is certainly beyond the scope of this walkthrough).
Note
This example assumes you’ve already got a working MySQL instance, and can administer it.
To highlight this flexibility in database choices, let’s take a look at what we’d change in Chapter 2 if we wanted to connect Hibernate to a MySQL database.
How do I do that?
Connect to your MySQL server and set up a new database to play with, as shown in Example 10-1.
% mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 3 to server version: 5.0.21
Type 'help;' or 'h' for help. Type 'c' to clear the buffer.
mysql> CREATE DATABASE notebook_db;
Query OK, 1 row affected (0.03 sec)
mysql> GRANT ALL ON notebook_db.* TO 'jim'@'janus.reseune.pvt' IDENTIFIED BY 's3
cret';
Query OK, 0 rows affected (0.02 sec)
mysql> quit;
ByeMake a note of the database name you create, as well as the user
and password that grant access to it. You will need to enter these into
hibernate.cfg.xml, as shown later
in Example 10-3. (And
hopefully you’ll use a more robust password than this in your real
databases!) If your database server is a different machine than where
you’re running the examples, put the example machine’s hostname in the
GRANT line rather than localhost (or use '%' to
indicate any host is acceptable if you’re on a secure private
network).
Connecting to MySQL
Next, we’ll need a JDBC driver capable of connecting to MySQL.
We can get this by adding another dependency in
build.xml, as shown in Example 10-2. It’s fine
to have drivers for several different databases available to your code;
they won’t conflict with each other, since the Hibernate configuration
file specifies which driver class to use. (If you’re curious about how to
get this driver “by hand,” you can download
Connector/J from the MySQL site). If you want to avoid confusion on the
part of future readers of the file, though, feel free to delete the
hsqldb dependency, as well as the
db target since data will no longer
be showing up in HSQLDB to be viewed by that GUI.
...
<artifact:dependencies pathId="dependency.class.path">
<dependency groupId="hsqldb" artifactId="hsqldb" version="1.8.0.7"/>
<dependency groupId="mysql" artifactId="mysql-connector-java"
version="5.0.5"/>
<dependency groupId="org.hibernate" artifactId="hibernate"
version="3.2.5.ga">
...Speaking of the configuration file, it’s time to edit hibernate.cfg.xml to use the new driver and
database we’ve just made available. Example 10-3 shows how to set up a connection to my own
MySQL instance using the database created by Example 10-1 earlier. You’ll need
to tweak these values to correspond to your own server, your database, and
the login credentials you chose. (If you’re using MM.MySQL, the older
incarnation of the MySQL JDBC driver, the driver_class will need to be
com.mysql.jdbc.Driver.)
Also, delete the jdbc.batch_size property
towards the bottom of the file. The MySQL driver doesn’t have any problems
reporting actual errors from inside batches, unlike the HSQLDB driver
we’ve been using, and there is an even larger performance penalty from
turning off batches when talking to an out-of-process database.
<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
<session-factory>
<!-- SQL dialect -->
<property name="dialect">org.hibernate.dialect.MySQL5Dialect</property>
<!-- Database connection settings -->
<property name="connection.driver_class">com.mysql.jdbc.Driver</property>
<property
name="connection.url">jdbc:mysql://localhost/notebook_db</property>
<property name="connection.username">jim</property>
<property name="connection.password">s3cret</property>
<property name="connection.shutdown">true</property>
...Tip
If you’re running MySQL on a different machine than on the one on
which you’re testing the examples, the third property definition here
will need to reflect your database server name. Also, the
connection.shutdown property is no longer
needed, but it won’t hurt anything if you leave it in.
Trying It Out
Once this is all set, you can rerun the schema-creation example
that was set up in “Cooking Up a Schema” way back in Chapter 2. This time
it will build the schema on your MySQL server rather than in the embedded
HSQLDB world. You’ll see output like what’s shown in Example 10-4, although it
will be longer than the example, in which we’ve cut out some of the
repetitive and boring output, and highlighted a few particularly salient
bits that prove our new settings have taken effect. (Note that we’re
starting with a separate ant prepare
invocation, since this is the first time we’re working in this chapter’s
example directory, and we need the right files in the class path before
Ant first starts up to run the schema
target.)
%ant prepare Buildfile: build.xml Downloading: mysql/mysql-connector-java/5.0.5/mysql-connector-java-5.0.5.pom Transferring 1K Downloading: mysql/mysql-connector-java/5.0.5/mysql-connector-java-5.0.5.jar Transferring 500K prepare: [mkdir] Created dir: /Users/jim/svn/oreilly/hibernate/current/examples/ch10/ classes [copy] Copying 5 files to /Users/jim/svn/oreilly/hibernate/current/examples /ch10/classes BUILD SUCCESSFUL Total time: 3 seconds%ant schema Buildfile: build.xml prepare: usertypes: [javac] Compiling 2 source files to /Users/jim/svn/oreilly/hibernate/current /examples/ch10/classes codegen: [hibernatetool] Executing Hibernate Tool with a Standard Configuration [hibernatetool] 1. task: hbm2java (Generates a set of .java files) [hibernatetool] 16:46:38,403 INFO Environment:514 - Hibernate 3.2.5 [hibernatetool] 16:46:38,415 INFO Environment:547 - hibernate.properties not fo und [hibernatetool] 16:46:38,419 INFO Environment:681 - Bytecode provider name : cg lib [hibernatetool] 16:46:38,434 INFO Environment:598 - using JDK 1.4 java.sql.Time stamp handling [hibernatetool] 16:46:38,561 INFO Configuration:1460 - configuring from file: h ibernate.cfg.xml [hibernatetool] 16:46:38,740 INFO Configuration:553 - Reading mappings from res ource : com/oreilly/hh/data/Track.hbm.xml [hibernatetool] 16:46:38,932 INFO HbmBinder:300 - Mapping class: com.oreilly.hh .data.Track -> TRACK ... [hibernatetool] 16:46:39,110 INFO HbmBinder:1422 - Mapping collection: com.orei lly.hh.data.Artist.tracks -> TRACK_ARTISTS [hibernatetool] 16:46:39,239 INFO Configuration:1541 - Configured SessionFactor y: null [hibernatetool] 16:46:39,411 INFO Version:15 - Hibernate Tools 3.2.0.b9 compile: [javac] Compiling 9 source files to /Users/jim/svn/oreilly/hibernate/current /examples/ch10/classes schema: [hibernatetool] Executing Hibernate Tool with a Standard Configuration [hibernatetool] 1. task: hbm2ddl (Generates database schema) [hibernatetool] 16:46:41,502 INFO Configuration:1460 - configuring from file: h ibernate.cfg.xml [hibernatetool] 16:46:41,515 INFO Configuration:553 - Reading mappings from res ource : com/oreilly/hh/data/Track.hbm.xml ... [hibernatetool] 16:46:41,629 INFO Configuration:1541 - Configured SessionFactor y: null [hibernatetool] 16:46:41,659 INFO Dialect:152 -Using dialect: org.hibernate.dialect.MySQL5Dialect[hibernatetool] 16:46:41,714 INFO SchemaExport:154 - Running hbm2ddl schema exp ort [hibernatetool] 16:46:41,716 INFO SchemaExport:179 - exporting generated schema to database [hibernatetool] 16:46:41,725 INFO DriverManagerConnectionProvider:41 - Using Hi bernate built-in connection pool (not for production use!) [hibernatetool] 16:46:41,726 INFO DriverManagerConnectionProvider:42 - Hibernat e connection pool size: 1 [hibernatetool] 16:46:41,727 INFO DriverManagerConnectionProvider:45 - autocomm it mode: false [hibernatetool] 16:46:41,745 INFO DriverManagerConnectionProvider:80 -using driver: com.mysql.jdbc.Driver at URL: jdbc:mysql://localhost/notebook_db[hibernatetool] 16:46:41,746 INFO DriverManagerConnectionProvider:86 -connection properties: {user=jim, password=****, shutdown=true}[hibernatetool] alter table ALBUM_ARTISTS drop foreign key FK7BA403FC76BBFFF9; ... [hibernatetool] alter table TRACK_COMMENTS drop foreign key FK105B2688E424525B; [hibernatetool] drop table if exists ALBUM; [hibernatetool] drop table if exists ALBUM_ARTISTS; [hibernatetool] drop table if exists ALBUM_COMMENTS; [hibernatetool] drop table if exists ALBUM_TRACKS; [hibernatetool] drop table if exists ARTIST; [hibernatetool] drop table if exists TRACK; [hibernatetool] drop table if exists TRACK_ARTISTS; [hibernatetool] drop table if exists TRACK_COMMENTS; [hibernatetool] create table ALBUM (ALBUM_ID integer not null auto_increment, TI TLE varchar(255) not null, numDiscs integer, added date, primary key (ALBUM_ID)) ; [hibernatetool] create table ALBUM_ARTISTS (ALBUM_ID integer not null, ARTIST_ID integer not null, primary key (ALBUM_ID, ARTIST_ID)); [hibernatetool] create table ALBUM_COMMENTS (ALBUM_ID integer not null, COMMENT varchar(255)); [hibernatetool] create table ALBUM_TRACKS (ALBUM_ID integer not null, TRACK_ID i nteger, disc integer, positionOnDisc integer, LIST_POS integer not null, primary key (ALBUM_ID, LIST_POS)); [hibernatetool] create table ARTIST (ARTIST_ID integer not null auto_increment, NAME varchar(255) not null unique, actualArtist integer, primary key (ARTIST_ID) ); [hibernatetool] create table TRACK (TRACK_ID integer not null auto_increment, TI TLE varchar(255) not null, filePath varchar(255) not null, playTime time, added date, VOL_LEFT smallint, VOL_RIGHT smallint, sourceMedia varchar(255), primary k ey (TRACK_ID)); [hibernatetool] create table TRACK_ARTISTS (TRACK_ID integer not null, ARTIST_ID integer not null, primary key (TRACK_ID, ARTIST_ID)); [hibernatetool] create table TRACK_COMMENTS (TRACK_ID integer not null, COMMENT varchar(255)); [hibernatetool] create index ALBUM_TITLE on ALBUM (TITLE); [hibernatetool] alter table ALBUM_ARTISTS add index FK7BA403FC76BBFFF9 (ARTIST_I D), add constraint FK7BA403FC76BBFFF9 foreign key (ARTIST_ID) references ARTIST (ARTIST_ID); [hibernatetool] alter table ALBUM_ARTISTS add index FK7BA403FCF2AD8FDB (ALBUM_ID ), add constraint FK7BA403FCF2AD8FDB foreign key (ALBUM_ID) references ALBUM (AL BUM_ID); [hibernatetool] alter table ALBUM_COMMENTS add index FK1E2C21E4F2AD8FDB (ALBUM_I D), add constraint FK1E2C21E4F2AD8FDB foreign key (ALBUM_ID) references ALBUM (A LBUM_ID); [hibernatetool] alter table ALBUM_TRACKS add index FKD1CBBC78E424525B (TRACK_ID) , add constraint FKD1CBBC78E424525B foreign key (TRACK_ID) references TRACK (TRA CK_ID); [hibernatetool] alter table ALBUM_TRACKS add index FKD1CBBC78F2AD8FDB (ALBUM_ID) , add constraint FKD1CBBC78F2AD8FDB foreign key (ALBUM_ID) references ALBUM (ALB UM_ID); [hibernatetool] create index ARTIST_NAME on ARTIST (NAME); [hibernatetool] alter table ARTIST add index FK7395D347A1422D3B (actualArtist), add constraint FK7395D347A1422D3B foreign key (actualArtist) references ARTIST ( ARTIST_ID); [hibernatetool] create index TRACK_TITLE on TRACK (TITLE); [hibernatetool] alter table TRACK_ARTISTS add index FK72EFDAD8E424525B (TRACK_ID ), add constraint FK72EFDAD8E424525B foreign key (TRACK_ID) references TRACK (TR ACK_ID); [hibernatetool] alter table TRACK_ARTISTS add index FK72EFDAD876BBFFF9 (ARTIST_I D), add constraint FK72EFDAD876BBFFF9 foreign key (ARTIST_ID) references ARTIST (ARTIST_ID); [hibernatetool] alter table TRACK_COMMENTS add index FK105B2688E424525B (TRACK_I D), add constraint FK105B2688E424525B foreign key (TRACK_ID) references TRACK (T RACK_ID); [hibernatetool] 16:46:42,630 INFO SchemaExport:196 - schema export complete [hibernatetool] 16:46:42,631 INFO DriverManagerConnectionProvider:147 - cleanin g up connection pool: jdbc:mysql://localhost/notebook_db [hibernatetool] 9 errors occurred while performing <hbm2ddl>. [hibernatetool] Error #1: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: T able 'notebook_db.album_artists' doesn't exist [hibernatetool] Error #1: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: T able 'notebook_db.album_artists' doesn't exist [hibernatetool] Error #1: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: T able 'notebook_db.album_comments' doesn't exist [hibernatetool] Error #1: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: T able 'notebook_db.album_tracks' doesn't exist [hibernatetool] Error #1: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: T able 'notebook_db.album_tracks' doesn't exist [hibernatetool] Error #1: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: T able 'notebook_db.artist' doesn't exist [hibernatetool] Error #1: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: T able 'notebook_db.track_artists' doesn't exist [hibernatetool] Error #1: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: T able 'notebook_db.track_artists' doesn't exist [hibernatetool] Error #1: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: T able 'notebook_db.track_comments' doesn't exist BUILD SUCCESSFUL Total time: 8 seconds
What just happened?
Hibernate configured itself to work with MySQL’s specific
features, examined the mapping document for our
Track class, connected to the MySQL server, and
executed the commands necessary to build a database schema for
persisting our examples. (As before, there are several SQL exceptions
reported at the end which, like Hibernate, you can ignore; they came
from Hibernate’s attempts to drop foreign keys that didn’t yet exist,
since this was the first time we were attempting to create the
schema.)
Again, don’t worry about the errors that appear at the end; these are caused by Hibernate’s preemptive attempts to drop tables in case the schema already partially existed. Even though it reports them, it doesn’t consider them a problem either, and proceeds with the rest of the schema creation task, reporting success in the end.
It’s interesting to compare this with the HSQLDB version, Example 2-7. The output is almost the same, but there are clear differences in the SQL used to actually create the tables. This is what Hibernate means by SQL“dialects.”
Looking at the Data
Back on the server, you can fire up the MySQL client again and
confirm that the Track mapping schema has been
created, as shown in Example 10-5.
%mysql -u jim -p Enter password: Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 21 to server version: 5.0.27 Type 'help;' or 'h' for help. Type 'c' to clear the buffer.mysql>use notebook_db Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changedmysql>show tables; +-----------------------+ | Tables_in_notebook_db | +-----------------------+ | ALBUM_ARTISTS | | ALBUM_COMMENTS | | ALBUM_TRACKS | | ARTIST | | TRACK_ARTISTS | | TRACK_COMMENTS | | album | | track | +-----------------------+ 8 rows in set (0.00 sec)mysql>describe track; +-------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+----------------+ | TRACK_ID | int(11) | NO | PRI | NULL | auto_increment | | TITLE | varchar(255) | NO | MUL | | | | filePath | varchar(255) | NO | | | | | playTime | time | YES | | NULL | | | added | date | YES | | NULL | | | VOL_LEFT | smallint(6) | YES | | NULL | | | VOL_RIGHT | smallint(6) | YES | | NULL | | | sourceMedia | varchar(255) | YES | | NULL | | +-------------+--------------+------+-----+---------+----------------+ 8 rows in set (0.03 sec)mysql>select * from TRACK; Empty set (0.00 sec)mysql>describe album_tracks; +----------------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------+---------+------+-----+---------+-------+ | ALBUM_ID | int(11) | NO | PRI | | | | TRACK_ID | int(11) | YES | MUL | NULL | | | disc | int(11) | YES | | NULL | | | positionOnDisc | int(11) | YES | | NULL | | | LIST_POS | int(11) | NO | PRI | | | +----------------+---------+------+-----+---------+-------+ 5 rows in set (0.00 sec)mysql>quit; Bye
It’s not surprising to find the table empty. But if you run ant ctest from your example directory and then try the select query again, you’ll see data like that in Example 10-6. One configuration file is the only change we need to make to completely switch the database with which Hibernate is working. This has come in handy more than a few times on production projects when clients change their minds about where they want data, or if multiple developers prefer to work on different operating systems.
mysql> select * from track; +----------+------------------------------------------+------------------------- --+----------+------------+----------+-----------+-------------+ | TRACK_ID | TITLE | filePath | playTime | added | VOL_LEFT | VOL_RIGHT | sourceMedia | +----------+------------------------------------------+------------------------- --+----------+------------+----------+-----------+-------------+ | 1 | Russian Trance | vol2/album610/track02.mp 3 | 00:03:30 | 2007-09-22 | 100 | 100 | CD | | 2 | Video Killed the Radio Star | vol2/album611/track12.mp 3 | 00:03:49 | 2007-09-22 | 100 | 100 | VHS | | 3 | Gravity's Angel | vol2/album175/track03.mp 3 | 00:06:06 | 2007-09-22 | 100 | 100 | CD | | 4 | Adagio for Strings (Ferry Corsten Remix) | vol2/album972/track01.mp 3 | 00:06:35 | 2007-09-22 | 100 | 100 | CD | | 5 | Adagio for Strings (ATB Remix) | vol2/album972/track02.mp 3 | 00:07:39 | 2007-09-22 | 100 | 100 | CD | | 6 | The World '99 | vol2/singles/pvw99.mp3 | 00:07:05 | 2007-09-22 | 100 | 100 | STREAM | | 7 | Test Tone 1 | vol2/singles/test01.mp3 | 00:00:10 | 2007-09-22 | 50 | 75 | NULL | +----------+------------------------------------------+------------------------- --+----------+------------+----------+-----------+-------------+ 7 rows in set (0.00 sec)
What about…
…a graphical way to work with MySQL? If you’d rather be in an environment like the graphical interface we illustrated for HSQLDB, you’ll want to check out the MySQL GUI tools that can be downloaded at http://dev.mysql.com/downloads/gui-tools/5.0.html.
…connecting to Oracle, or another favorite, shared, or legacy
database that doesn’t happen to be MySQL or HSQLDB? You’ve probably
already figured out that it’s just as easy. All you need to do is change
the hibernate.dialect property
in your hibernate.cfg.xml to reflect the kind of
database you want to use. There are many dialects available, covering nearly every free and commercial database I can
think of; the ones available at the time of writing this book are listed
in Appendix C,
but check the Hibernate documentation for the latest list. If you need
to work with a more obscure database, you may have to write your own
dialect to support it, but that seems unlikely (and check to see if
anyone’s already started that effort).
Once you’ve got the dialect chosen, you’ll also need to set the
hibernate.connection
properties (driver, URL,
username, and password) to the
proper values for establishing a JDBC connection to
your chosen database environment. If you’re porting an existing project
to use Hibernate, you’ll be able to obtain these from the code or
configuration of that project. And, naturally, you’ll need to include
the database’s JDBC driver as a dependency for your
project during build and runtime.
Of course, if you’re connecting to an existing or shared database, you won’t be using Hibernate to create the schema. Instead, you’ll write the mapping document to reflect the existing schema, either by hand or with the help of the Hibernate Tools (which we explore more deeply in Chapter 11), or a third-party package like Middlegen, and then start working with the data in the form of persistent objects.
You can even use Hibernate to talk to multiple different databases
at the same time; you just need to create multiple
SessionFactory instances with separate
configurations. This goes beyond the simple, automatic configuration
demonstrated here, but there are examples in the Hibernate reference
documentation. Of course, a persistent object can only be associated
with a single session at a time, which means it can only be linked to a
single database at once. With clever, careful coding, though, you can
copy or move objects between different database systems, even with a
different schema to represent them. That’s way out
of scope for this book, though!