Matt  Watson Matt Watson - 7 months ago 35
SQL Question

What is the best way to get around these Hibernate exepctions when swithching from MySql to an in memory database for testing?

I am working on a Spring Boot application that uses MySql as it's data source. There is an entity in the application that has a field with the defintion:

@Column(name = "Vendor", columnDefinition="TEXT")
private String vendor;


The definition of the column in the database is
varchar(255)
which is why I suspect the entity has been given the
columnDefintion
of
'TEXT'
, since without it I get the exception:

Caused by: org.hibernate.HibernateException: Wrong column type in database.example for column Vendor. Found: text, expected: varchar(255)


This is fine when I'm running the application and it talks to the MySql database. The problems come when I want to run some tests that talk to an in memory database (hsqldb in this case) configured as below:

@Bean
public DataSource meshlium()
{
DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName("org.hsqldb.jdbcDriver");
dataSource.setUrl("jdbc:hsqldb:mem:meshlium");
dataSource.setUsername("sa");
dataSource.setPassword("");
return dataSource;
}

/**
* Declare the JPA entity manager factory.
*/
@Primary
@Bean
public LocalContainerEntityManagerFactoryBean entityManagerFactory()
{
LocalContainerEntityManagerFactoryBean entityManagerFactoryOffice = new LocalContainerEntityManagerFactoryBean();

entityManagerFactoryOffice.setDataSource(meshlium());

// Classpath scanning of @Component, @Service, etc annotated class
entityManagerFactoryOffice
.setPackagesToScan("com.mottmac.meshlium.scan");

// Vendor adapter
HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
entityManagerFactoryOffice.setJpaVendorAdapter(vendorAdapter);

// Hibernate properties
Properties additionalProperties = new Properties();
additionalProperties.put("hibernate.show_sql",
true);
additionalProperties.put("hibernate.hbm2ddl.auto", "create-drop");
additionalProperties.put("hibernate.dialect", "org.hibernate.dialect.HSQLDialect");
entityManagerFactoryOffice.setJpaProperties(additionalProperties);

return entityManagerFactoryOffice;
}


When I run my tests and hibernate tries to create the database I get exceptions in the log file:

2016-05-05 15:44:31.020(GMT)[main] ERROR o.h.tool.hbm2ddl.SchemaExport - HHH000389: Unsuccessful: create table bluetoothData (ID_frame integer not null, RSSI varchar(255), MAC varchar(255), TimeStamp timestamp, Vendor TEXT, ID TEXT, cod varchar(255), primary key (ID_frame))
2016-05-05 15:44:31.020(GMT)[main] ERROR o.h.tool.hbm2ddl.SchemaExport - Wrong data type: TEXT in statement [create table bluetoothData (ID_frame integer not null, RSSI varchar(255), MAC varchar(255), TimeStamp timestamp, Vendor TEXT]


If I remove the
columnDefinition
property from
vendor
the problem goes away and my tests can run, however as I described above the application won't start. So I'm stuck in a situation where I can change the code to make the application work but the tests fail, or the tests fail and the application works.

One solution that I've seen mentioned elsewhere was to change the
@Column
annotation to use the
length
property and remove
columnDefintion
so that I have:

@Column(name = "Vendor", length=255)
private String vendor;


This change allows my tests pass but still throws the original exception on start up.

Is there a solution that will allow both my tests and my application to function properly while using MySql in production and hsqldb in my unit tests?

Sam Sam
Answer

So a little research reveals that you can set H2 to act in MYSQL mode.

I believe if you change your database URL to have MODE=MySQLat the end it should work!

Edit: If you wish to stick with HSQL, they also have an equivalent property - sql.syntax_mys=true