2.4 Connecting Hibernate to MySQL


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 www.mysql.com ).

2.4.1 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. 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 


Hopefully you'll use a less guessable password than this in your real databases!

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 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 .)

Example 2-9. Changes to hibernate.properties to connect to the new MySQL database
 hibernate.dialect=net.sf.hibernate.dialect.  MySQL  Dialect 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 

2.4.2 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 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.

2.4.3 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 ( 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!

Hibernate. A Developer's Notebook
Hibernate: A Developers Notebook
ISBN: 0596006969
EAN: 2147483647
Year: 2003
Pages: 65
Authors: James Elliott

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net