The Spring by Example JDBC module has some utilities to help creating and initializing a database. There is an extension of SimpleDriverDataSource
from Spring, which doesn't pool connections, that can be configured to run scripts once the class is initialized and another one based
on Commons DBCP BasicDataSource. The script processor that they use can also be used separately.
![[Note]](images/note.gif) | Note |
|---|---|
|
The InitializingBasicDataSource is an extension of Commons DBCP BasicDataSource. The key difference is that an initialization callback
is used to process SQL scripts on startup. The list of scripts can either be a SQL script or a Spring resource (classpath, file, http, etc.).
Also, the destruction method does not have to be defined in the XML because this class has a destruction callback.
InitializingBasicDataSource
<bean id="dataSource"
class="org.springbyexample.jdbc.datasource.InitializingBasicDataSource">
<property name="driverClassName" value="org.hsqldb.jdbcDriver"/>
<property name="url" value="jdbc:hsqldb:mem:Test"/>
<property name="username" value="sa"/>
<property name="password" value=""/>
<property name="sqlScriptProcessor">
<bean class="org.springbyexample.jdbc.core.SqlScriptProcessor">
<property name="sqlScripts">
<list>
<value>classpath:/schema.sql</value>
</list>
</property>
</bean>
</property>
</bean>
The InitializingDriverManagerDataSource is an extension of Spring's SimpleDriverDataSource, so Spring 2.5.5 or higher is
needed to use it. The key difference is that an initialization callback is used to process SQL scripts on startup. The list of scripts can either be
a SQL script or a Spring resource (classpath, file, http, etc.).
| Note |
|---|---|
This creates a new connection each time one is requested so this is primarily useful for testing. Use |
InitializingDriverManagerDataSource
<bean id="dataSource"
class="org.springbyexample.jdbc.datasource.InitializingDriverManagerDataSource">
<property name="driverClassName" value="org.hsqldb.jdbcDriver"/>
<property name="url" value="jdbc:hsqldb:mem:Test"/>
<property name="username" value="sa"/>
<property name="password" value=""/>
<property name="sqlScriptProcessor">
<bean class="org.springbyexample.jdbc.core.SqlScriptProcessor">
<property name="sqlScripts">
<list>
<value>classpath:/schema.sql</value>
</list>
</property>
</bean>
</property>
</bean>
The HsqldbInitializingDriverManagerDataSource is an extension of InitializingDriverManagerDataSource specifically
to use with HSQLDB. The key difference is that an initialization callback is used to process SQL scripts on startup.
The list of scripts can either be a SQL script or a Spring resource (classpath, file, http, etc.). It defaults to creating an
in memory database called Test with a username of sa and a blank password.
HsqldbInitializingDriverManagerDataSource
<bean id="dataSource"
class="org.springbyexample.jdbc.datasource.HsqldbInitializingDriverManagerDataSource">
<property name="sqlScriptProcessor">
<bean class="org.springbyexample.jdbc.core.SqlScriptProcessor">
<property name="charset" value="UTF-8" />
<property name="sqlScripts">
<list>
<value>classpath:/security_schema.sql</value>
<value>classpath:/schema.sql</value>
</list>
</property>
</bean>
</property>
</bean>
The SqlScriptProcessor is used to process SQL scripts which can either be a SQL script or a Spring resource (classpath, file, http, etc.).
The property initOnStartup defaults to false, but can be set to true if the scripts should be run on startup.
SqlScriptProcessor
<bean id="sqlScriptProcessor"
class="org.springbyexample.jdbc.core.SqlScriptProcessor">
<property name="dataSource" ref="dataSource" />
<property name="initOnStartup" value="true" />
<property name="sqlScripts">
<list>
<value>
DROP TABLE COMPANY IF EXISTS;
CREATE TABLE COMPANY (
ID integer identity primary key,
NAME varchar(50) not null,
DESCRIPTION varchar(255) not null,
ADDRESS varchar(255),
CITY varchar(50) not null,
STATE varchar(50) not null,
ZIP_POSTAL varchar(30) not null
);
</value>
<value>classpath:/schema.sql</value>
</list>
</property>
</bean>