Search
Calendar
October 2017
S M T W T F S
« Sep    
1234567
891011121314
15161718192021
22232425262728
293031  
Your widget title
Archives

Posts Tagged ‘MySQL’

PostHeaderIcon (long tweet) How to get the average of a Date column in MySQL?

Case

You have a column of type Date in MySQL. How to get the average value of this series?

(Don’t think to execute select AVG(myColumnDate), it won’t work!)

Fix

Use a query similar to this:

SELECT FROM_UNIXTIME( ROUND( AVG( UNIX_TIMESTAMP( myDateColumn ) ) ) )

FROM `myTable`

WHERE 1

PostHeaderIcon (quick tutorial) Migration from MySQL to HSQLDB

Case

I got the project described in MK Yong’s website. This projects is a sample code of JSF + Spring + Hibernate. The laying DB is a MySQL. For many reasons, I’d rather not to detail, I prefered to a HSQLDB instead of the MySQL.

(Notice: the zip you can download at MK Yong’s website contains many errors, not related to the persistance layer but to JSF itself.)

How to migrate any project from MySQL to HSQLDB?

Solution

You have to follow these steps:

Maven

In the pom.xml, replace:

<!-- MySQL database driver -->
        <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.9</version>
        </dependency>

with:

        <!-- driver for HSQLdb -->
        <dependency>
            <groupId>org.hsqldb</groupId>
            <artifactId>hsqldb</artifactId>
            <version>2.2.8</version>
        </dependency>

By the way, you can add Jetty plugin to have shorter development cycles:

            <plugin>
                <groupId>org.mortbay.jetty</groupId>
                <artifactId>maven-jetty-plugin</artifactId>
                <configuration>
                    <webApp>${basedir}/target/jsf.war</webApp>
                    <port>8088</port>
                </configuration>
            </plugin>

Persistence

Properties

Replace the content of db.properties file with:

jdbc.driverClassName=org.hsqldb.jdbcDriver
jdbc.url=jdbc:hsqldb:hsql://localhost:9001
jdbc.username=sa
jdbc.password=

Hibernate Mapping

In the *.hbm.xml files:

  • in the tag <class>, remove the attribute catalog="..."
  • replace the types with fully qualified object types, eg:
    • long with java.lang.Long,
    • string with java.lang.String,
    • timestamp with java.util.Date,
    • etc.

Hibernate Properties

For the property of key hibernate.dialect, replace the value: org.hibernate.dialect.MySQLDialectorg.hibernate.dialect.HSQLDialect with the value: org.hibernate.dialect.HSQLDialect.
To match my needs, I tuned Hibernate properties a bit more, but it may not be needed in all situations:

        <property name="hibernateProperties">
            <props>
                <prop key="hibernate.dialect">org.hibernate.dialect.HSQLDialect</prop>
                <prop key="hibernate.show_sql">true</prop>
                <prop key="hbm2ddl.auto">create-drop</prop>
                <prop key="hibernate.hbm2ddl.auto">create-drop</prop>
                <prop key="connection.pool_size">1</prop>
                <prop key="current_session_context_class">thread</prop>
                <prop key="cache.provider_class">org.hibernate.cache.NoCacheProvider</prop>
            </props>
        </property>

Run

Run the HSQLDB server. IMHO, the quickest is to run the following Maven command:

mvn exec:java -Dexec.mainClass="org.hsqldb.Server"

But you may prefer the old school java -cp hsqldb-XXX.jar org.hsqldb.Server ;-).

Tip! To get a GUI to check the content of the DB instance, you can run:

mvn exec:java -Dexec.mainClass="org.hsqldb.util.DatabaseManager"

Then build and launch Jetty:

mvn clean install jetty:run-exploded

Here you can see the great feature of HSQLDB, that will allow you to create, alter and delete tables on the fly (if hibernate.hbm2ddl.auto is set to create-drop), without any SQL scripts, but only thanks to HBM mapping files.