Introduction to Mapping Redux - Connecting Hibernate to MySQL
(Page 5 of 5 )
If you were skimming through this chapter (or, more likely, the table of contents) you may not have even noticed that Hibernate connected to and manipulated a database in the previous section, “Cooking Up a Schema.” Since working with databases is the whole point of Hibernate, it makes this as easy as possible. Once you’ve set up a configuration file like the one in Example 2-4, the schema generation tool can get in and work with your database, and your Java code can use it for persistence sessions as demonstrated in Chapter 3.
This example assumes you’ve already got a working MySQL instance installed and running, since explaining how to do that would be quite a detour.
In the interest of further clarifying this aspect of working with Hibernate, let’s take a look at what we’d change in that example to set up a connection with the popular, free, and open source MySQL database (available from http://www.mysql.com).
How do I do that?
Connect to your MySQL server and set up a new database to play with, along the lines of Example 2-8.
Example 2-8. Setting up the MySQL database notebook_db as a Hibernate playground
% mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 764 to server version: 3.23.44-Max-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
Hopefully you’ll use a less guessable password than
this in your real databases!
mysql> CREATE DATABASE notebook_db;
Query OK, 1 row affected (0.00 sec)
mysql> GRANT ALL ON notebook_db.* TO jim IDENTIFIED BY "s3cret";
Query OK, 0 rows affected (0.20 sec)
mysql> quit;
Bye
Make a note of the database name you create, as well as the username and password that can access to it. These will need to be entered into hibernate.properties, as shown in Example 2-9.Next, you’ll need a JDBC driver capable of connecting to MySQL. If you’re already using MySQL for your Java projects, you’ll have one. otherwise,you can download Connector/J from http://www.mysql.com/downloads/api-jdbc-stable.html. However you obtain it, copy the driver library jar (which will be named something like mysql-connector-java-3.0.10-stable-bin.jar) to your project’s lib directory alongside the HSQLDB, Hibernate, and other libraries that are already there. It’s fine to have drivers for several different databases available to your code; they won’t conflict with each other, since the configuration file specifies which driver class to use.Speaking of which, it’s time to edit hibernate.properties to use the new driver and database we’ve just made available. Example 2-9 shows how it is set up to connect to my MySQL instance using the database created in Example 2-8. You’ll need to tweak these values to correspond to your own server, 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.)
Connecting Hibernate to MySQL
Example 2-9. Changes to hibernate.properties to connect to the new MySQL database
hibernate.dialect=net.sf.hibernate.dialect. MySQLDialect
hibernate.connection.driver_class=com.mysql. jdbc.Driver
hibernate.connection.url=jdbc:mysql://slant. reseune.pvt/notebook_db
hibernate.connection.username=jim
hibernate.connection.password=s3cret
The URL on the third line will need to reflect your server; you won’t be able to resolve my private internal domain name, let alone route to it.
Once this is all set, you can rerun the schema creation example that was set up in the previous section. This time it will build the schema on your MySQL server rather than in the embedded HSQLDB world. You’ll see output like that in Example 2-10.
Example 2-10. Schema creation when connecting to MySQL
% ant schema
Buildfile: build.xml
prepare:
compile:
schema:
[schemaexport] 23:02:13,614 INFO Environment:462 - Hibernate 2.1.2
[schemaexport] 23:02:13,659 INFO Environment:496 - loaded properties from
resource hibernate.properties: {hibernate.connection.username=jim, hibernate.
connection.password=s3cret, hibernate.cglib.use_reflection_optimizer=true,
hibernate.dialect=net.sf.hibernate.dialect. MySQLDialect, hibernate.connection.
url=jdbc:mysql://slant.reseune.pvt/notebook_db, hibernate.connection.driver_
class=com.mysql.jdbc.Driver}
[schemaexport] 23:02:13,711 INFO Environment:519 - using CGLIB reflection
optimizer
[schemaexport] 23:02:13,819 INFO Configuration:166 - Mapping file: /Users/jim/
Documents/Work/OReilly/Hibernate/Examples/ch02/ classes/com/oreilly/hh/Track.hbm.xml
[schemaexport] 23:02:15,568 INFO Binder:229 - Mapping class: com.oreilly.hh.
Track -> TRACK
[schemaexport] 23:02:16,164 INFO Dialect:82 - Using dialect: net.sf.hibernate.
dialect.MySQLDialect
[schemaexport] 23:02:16,175 INFO Configuration:595 - processing one-to-many
association mappings
[schemaexport] 23:02:16,188 INFO Configuration:604 - processing one-to-one
association property references
[schemaexport] 23:02:16,209 INFO Configuration:629 - processing foreign key
constraints
[schemaexport] 23:02:16,429 INFO Configuration:595 - processing one-to-many
association mappings
[schemaexport] 23:02:16,436 INFO Configuration:604 - processing one-to-one
association property references [schemaexport] 23:02:16,440 INFO Configuration:629 - processing foreign key
constraints
[schemaexport] 23:02:16,470 INFO SchemaExport:98 - Running hbm2ddl schema export
[schemaexport] 23:02:16,488 INFO SchemaExport:117 - exporting generated schema
to database
[schemaexport] 23:02:16,543 INFO DriverManagerConnectionProvider:41 - Using
Hibernate built-in connection pool (not for production use!)
[schemaexport] 23:02:16,549 INFO DriverManagerConnectionProvider:42 - Hibernate
connection pool size: 20
[schemaexport] 23:02:16,583 INFO DriverManagerConnectionProvider:71 - using
driver: com.mysql.jdbc.Driver at URL: jdbc:mysql://slant.reseune.pvt/notebook_db
[schemaexport] 23:02:16,597 INFO DriverManagerConnectionProvider:72 -connection
properties: {user=jim, password=s3cret}
[schemaexport] drop table if exists TRACK
[schemaexport] 23:02:18,129 DEBUG SchemaExport:132 - drop table if exists TRACK
[schemaexport] create table TRACK (
[schemaexport] TRACK_ID INTEGER NOT NULL AUTO_INCREMENT,
[schemaexport] title VARCHAR(255) not null,
[schemaexport] filePath VARCHAR(255) not null,
[schemaexport] playTime TIME,
[schemaexport] added DATE,
[schemaexport] volume SMALLINT,
[schemaexport] primary key (Track_id)
[schemaexport] )
[schemaexport] 23:02:18,181 DEBUG SchemaExport:149 - create table TRACK (
[schemaexport] TRACK_ID INTEGER NOT NULL AUTO_INCREMENT,
[schemaexport] title VARCHAR(255) not null,
[schemaexport] filePath VARCHAR(255) not null,
[schemaexport] playTime TIME,
[schemaexport] added DATE,
[schemaexport] volume SMALLINT,
[schemaexport] primary key (Track_id)
[schemaexport] )
[schemaexport] 23:02:18,311 INFO SchemaExport:160 - schema export complete
[schemaexport] 23:02:18,374 INFO DriverManagerConnectionProvider:137 - cleaning
up connection pool: jdbc:mysql://slant.reseune.pvt/notebook_db
BUILD SUCCESSFUL
Total time: 9 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 MySQLserver, and executed the commands necessary to build a database schema for persisting Track instances.It’s interesting to compare Example 2-11 with Example 2-7. Most of the output is the same, but there are subtle differences in the SQL used to actually create the table. This is what Hibernate means by SQL “dialects.”
Back on the server, you can fire up the MySQL client again, and confirm that the Track mapping schema has been created.
Example 2-11. Checking the newly created MySQL schema
% mysql -u jim -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 772 to server version: 3.23.44-Max-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> USE notebook_db
Database changed
mysql> SHOW TABLES;
+-----------------------+
| Tables_in_notebook_db |
+-----------------------+
| TRACK |
+-----------------------+
1 row in set (0.03 sec)
mysql> DESCRIBE TRACK;
Field | Type | Null | Key | Default | Extra |
TRACK_ID | int(11) | | PRI | NULL | auto_increment |
title | varchar(255) | | | | |
filePath | varchar(255) | | | | |
playTime | time | YES | | NULL | |
added | date | YES | | NULL | |
volume | smallint(6) | YES | | NULL | |
6 rows in set (0.02 sec)
mysql> SELECT * FROM TRACK;
Empty set (0.00 sec)
mysql> quit;
Bye
It’s not surprising to find the table empty. We’ll investigate how to populate it with data in the first part of Chapter 3.
If you’ve followed this example and set up a MySQL database, and you’d prefer to continue working with it throughout the rest of the book, feel free to do so, but bear in mind you’ll need to know how to look at the results of the examples yourself. The text will assume you’re still working with HSQLDB, and it will show you how to check your progress in that context. You will also see slight differences in the schema, as databases all have slightly different column types and features. Apart from these minor details, it really makes no difference what database you’re using—that’s part of the appeal of an O/R mapping layer like Hibernate.If you do want to switch back to HSQLDB for the ease of following the discussion, change your hibernate.properties back to the values shown in Example 2-4.
What about...
...Connecting to Oracle, or another favorite, shared, or legacy database that doesn’t happen to be MySQL or HSQLDB? You’ve probably figured out that it’s just as easy. All you need to do is change the hibernate.
dialect setting in your hibernate.properties to reflect the kind of database you want to use. There are many dialects available, covering every free and commercial database I can think of. These built-in dialects are
listed in Appendix C. 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 chosen the dialect, you’ll also need to set the hibernate.connection properties (driver, URL, username, and password—the other entries in Example 2-4 and Example 2-9) 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 put the database’s JDBC driver into your project’s library directory.
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 a tool like Middlegen (http://boss.bekk.no/boss/middlegen), and then start working with the data in the form of persistent objects, as described in Chapter 3.
You can even use Hibernate to talk to multiple databases at the same time; you just need to create multiple SessionFactory instances with separate configurations. This goes beyond the simple, automatic configuration we demonstrate in Chapter 3, 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 notebook, though!
* We’re assuming you’ve already generated the code shown in Example 2-3, or there won’t be any Java source to compile, and the schema generation will fail. The schema target doesn’t invoke codegen to automatically generate code, in case you’ve manually extended any of your generated classes.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
|
 This article is excerpted from chapter two of Hibernate: A Developer's Notebook, written by James Elliott (O'Reilly; ISBN: 0596006969). Check it out today at your favorite bookstore. Buy this book now.
|
|
