What mysql driver do I use with spring/hibernate?

Question

Little confused, is 'driverclassname' and 'hibernate.dialect' both referring to the mysql driver?

What should I be using? Is the connectorJ the one I should use?

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="org.hsqldb.jdbcDriver"/>
        <property name="url" value="jdbc:mysql://localhost/blah"/>



<property name="hibernateProperties">
            <value>
                hibernate.dialect=org.hibernate.dialect.MySQLDialect

I'm using Maven so if I can get the driver from maven that would be ideal.

Running my app in tomcat I get the error:

Cannot create JDBC driver of class 'org.hsqldb.jdbcDriver' for connect URL 

Answer 1

The driverClassName should be referring to the class name of the JDBC driver you'd like to load (as you usually would do using Class#forName() in "plain" JDBC). The one you're currently specifying is correct for the MySQL JDBC driver (Update: you quickly edited it into a HSQLDB JDBC driver one, this is only correct for a Hypersonic DB, not for a MySQL DB).

The hibernate.dialect should be referring to the class name of the Hibernate Dialect implementation you'd like to use for the particular database, so that Hibernate knows what the DB in question understands so that it can autogenerate the suitable SQL statements. The one you're currently specifying is correct for the MySQL database.

---

That said, it sounds like that you're having problems with it. Probably you haven't installed the MySQL JDBC driver? Are you getting a ClassNotFoundException on it? Just downloading Connector/J, extracting the zip and placing the JAR file in the runtime classpath ought to be sufficient.

Answer 2

regarding maven mysql definition, here's one that seems to work.

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.12</version>
</dependency>

Answer 3

Little confused, is 'driverclassname' and 'hibernate.dialect' both referring to the mysql driver?

No, they are not. The driverclassname is referring to, well, the driver class name which is the class from a given JDBC driver that implements java.sql.Driver. The driver class name is driver specific.

When using MySQL's JDBC driver aka MySQL Connector/J, this class is com.mysql.jdbc.Driver as explained in the MySQL Connector/J documentation:

20.3.4.1. Driver/Datasource Class Names, URL Syntax and Configuration Properties for Connector/J

The name of the class that implements java.sql.Driver in MySQL Connector/J is com.mysql.jdbc.Driver. (...)

And actually, they even provide instructions to use their driver with Spring. See the section 20.3.5.2.4. Using Connector/J with Spring.

The hibernate.dialect is different, this configuration property is used to define the classname of a Hibernate org.hibernate.dialect.Dialect which allows Hibernate to generate SQL optimized for a particular relational database. Again this is explained in the Hibernate documentation:

3.4. Optional configuration properties

(...) The classname of a Hibernate org.hibernate.dialect.Dialect which allows Hibernate to generate SQL optimized for a particular relational database.

e.g. full.classname.of.Dialect

In most cases Hibernate will actually be able to choose the correct org.hibernate.dialect.Dialect implementation based on the JDBC metadata returned by the JDBC driver.

For MySQL 5.x, you should use org.hibernate.dialect.MySQL5InnoDBDialect if you are using InnoDB tables (this would be my recommendation) or org.hibernate.dialect.MySQL5Dialect if you're not. See the section 3.4.1. SQL Dialects for a (non exhaustive) list.

Last point, the Maven part that you didn't even mention in your question... The MySQL JDBC driver is available in the Maven central repository and you should use a repository search engine (as I already suggested). For example, the following query:

http://www.jarvana.com/jarvana/search?search_type=project&project=mysql

allows to find the maven coordinates of the ultimate version in two clicks:

<dependency>
   <groupId>mysql</groupId>
   <artifactId>mysql-connector-java</artifactId>
   <version>5.1.13</version>
</dependency>

PS: I don't mean to be rude and I'm glad to help but you should really try to leverage the documentation of the products or frameworks you're using. What you're asking in this question is well documented (as I showed) and can be found easily. Learning to find basic information by yourself is a fundamental skill for a software developer in my opinion.