Can Spring's Embedded HSQL database support the Sybase dialect?
I would like to be able to support the following Sybase 15 ASE syntax in my unit/integration tests that use HSQL...
create table #myTable (value varchar(12) NULL)
HSQL won't recognise how the temp table is named, and baulks at the # character. Instead HSQL would like to use something like this...
create temporary table myTable (value varchar(12) NULL)
or, HSQL also supports most of ANSI-92 SQL according to their docs, however Sybase ASE 15 doesn't have great support for ANSI-92 SQL including how temporary tables are created so the following won't work in Sybase but does in HSQL...
DECLARE LOCAL TEMPORARY TABLE mytable (value varchar(12) NULL)
From everything I have tried I cannot come up with a common syntax that will work with both Sybase and HSQL. Does anyone know of a clean way around this?
The only option I think I have is to create separate DAO's for each database dialect, and control which one is used in the Spring Application Context XML files.
I don't use Hibernate for my datasource, only Spring's JdbcTemplate.
I chose to resolve this issue by implementing a couple of dialect helper classes for my DAO. My goals were to
My DAO ended up looking like this (note the DialectHelper being injected) ...
@Repository
public class MyDaoJdbc MyDao {
private DialectHelper dialectHelper;
/* the meat of the DAO removed for clarity */
@Override
public void createTemporaryTable() {
getSimpleJdbcTemplate().update(dialectHelper.getTempTableCreateSql());
}
@Autowired
public final void setDialectHelper(DialectHelper dialectHelper) {
this.dialectHelper = dialectHelper;
}
}
... my production Spring configuration (spring-db.xml) looks like this and injects the Sybase dialect
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName" value="com.sybase.jdbc2.jdbc.SybDriver" />
<property name="url" value="${jdbc.url}" />
<property name="username" value="${jdbc.username}" />
<property name="password" value="${jdbc.password}" />
</bean>
<bean id="dialectHelper" class="com.acme.myapp.jdbc.DialectHelperSybase" />
... and my Test Spring configuration (spring-db-test.xml) looks like this and injects the HSQL dialect
<jdbc:embedded-database id="dataSource" type="HSQL">
<jdbc:script location="classpath:/resources/schema.sql"/>
<jdbc:script location="classpath:/resources/test-data.sql"/>
</jdbc:embedded-database>
<bean id="dialectHelper" class="com.acme.myapp.dao.jdbc.DialectHelperHsql" />
The DialectHelper classes provide a way of separating out the incompatible database syntax from the DAO ...
public class DialectHelperHsql implements DialectHelper {
@Override
public String getTempTableCreateSql() {
return "create temporary table myTable (value varchar(12) NULL)";
}
}
public class DialectHelperSybase implements DialectHelper {
@Override
public String getTempTableCreateSql() {
return "create table #myTable (value varchar(12) NULL)";
}
}
The Test class itself initialises Spring with the HSQL dialectHelper by loading the file spring-db-test.xml
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations={
"classpath:resources/spring-context.xml",
"classpath:resources/spring-db-test.xml"})
@Transactional
@TransactionConfiguration(defaultRollback = true)
public class MyDaoIntegrationHsqlTest {
...
}
上一篇: HSQLDB对象名称已经存在