Posts Tagged ‘MySQL’
(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
(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 attributecatalog="..."
- replace the types with fully qualified object types, eg:
long
withjava.lang.Long
,string
withjava.lang.String
,timestamp
withjava.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.