Introduction
This is the first part of a series of tutorials based on the code of springSourcery showing how to use Spring, Hibernate and JPA to interact with a MySql database.
Remember all of the source and configuration can be downloaded from this URL.
This part of the tutorial goes through setting up MySql and Spring to talk to each other.
First you need to install MySql on your PC.
Download the latest version of MySql from this URL.
http://dev.mysql.com/downloads/mysql/5.1.html
Installing Mysql on Windows
Click next.
Accept the license and click next.
Select Typical Install and click next.
Then click on next.
A window like this will load. The progress bar will keep moving until the installation is finished.
Next you want to configure MySql so tick on Configure the server and then click next.
Click next.
Select Detailed Configuration.
As I'm setting up MySql on a small development laptop I selected "Developer Machine". You might want to select one of the other options if this installation is for deployment.
I selected Multifunctional Database as SpringSourcery is a Content Management System.
Click on next unless you want to change the defaults.
I selected the first option. If you are expecting lots more users then select manual and set the concurrent connections to be slightly larger than any connection pool that you plan to use.
Leave the default options (as above) and select next.
Select the standard character set.
You will normally want to install MySql as a service. So leave the defaults then click next.
Enter a username and password for the root user. I entered test and test. Naturally a better password would be required in a production environment!
Once you click on Execute MySql will be installed.
Once you click on Finish MySql will be installed. Now run the MySql Command Line Client to configure the database using the Windows Start button.
You will be asked for the password that you entered earlier.
Now you want to create the springSourcery database. Type create database springsourcery.
Then you want to create a user for the springSourcery web-site.
Finally give the springsourcery account privileges to the database by typing grant all privileges on springsourcery.* to test@localhost;
Spring Set-up
Once MySql was installed I set-up spring. To simplify the configuration I put all of the database configuration within a file called applicationContext-dao.xml, and then used an import within the main web-application-config.xml to load it at runtime.
This is a copy of my applicationContext-dao.xml file.
<?xml version="1.0" encoding="UTF-8"?>
<!-- DATASOURCE DEFINITON-->
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:tx="http://www.springframework.org/schema/tx"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.0.xsd">
<bean id="dataSource"
class="com.mchange.v2.c3p0.ComboPooledDataSource"
destroy-method="close">
<property name="driverClass"><value>com.mysql.jdbc.Driver</value></property>
<property name="jdbcUrl"><value>jdbc:mysql://localhost/springsourcery</value></property>
<property name="user"><value>test</value></property>
<property name="password"><value>test</value></property>
<property name="initialPoolSize"><value>3</value></property>
<property name="minPoolSize"><value>3</value></property>
<property name="maxPoolSize"><value>50</value></property>
<property name="idleConnectionTestPeriod"><value>200</value></property>
<property name="acquireIncrement"><value>1</value></property>
<property name="maxStatements"><value>0</value></property> <!-- 0 means: statement caching is turned off. -->
<property name="numHelperThreads"><value>3</value></property> <!-- 3 is default -->
</bean>
<!-- HIBERNATE CONFIGURATION -->
<bean id="sessionFactory"
class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
<property name="dataSource" ref="dataSource" />
<property name="annotatedClasses">
<list>
<value>com.springsourcery.community.model.User</value>
<value>com.springsourcery.community.model.Authorities</value>
<value>com.springsourcery.community.model.Topic</value>
<value>com.springsourcery.community.model.Blog</value>
<value>com.springsourcery.community.model.Question</value>
<value>com.springsourcery.community.model.Content</value>
<value>com.springsourcery.community.model.Comment</value>
<value>com.springsourcery.community.model.Scores</value>
</list>
</property>
<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">
org.hibernate.dialect.MySQLInnoDBDialect
</prop>
<prop key="hibernate.show_sql">true</prop>
<prop key="hibernate.hbm2ddl.auto">update</prop>
</props>
</property>
</bean>
<tx:annotation-driven transaction-manager="txManager"/>
<bean id="txManager" class="org.springframework.orm.hibernate3.HibernateTransactionManager">
<property name="sessionFactory" ref="sessionFactory"/>
</bean>
</beans>
So what does this configuration do?
First I set-up a datasource, which is used by spring to connect to the MySql database. Simple systems with low throughput can get away with using a single database connection, but as I'm hoping that eventually many people will use SpringSourcery I set-up a connection pool. A connection pool will improve the scalability of your web site by sharing database access across multiple connections and saving the time required to establish a database connection for each hit to the database. I chose the c3po connection pool library. You'll see that I add it as a dependency in my gradle.build file later on.
<bean id="dataSource"
class="com.mchange.v2.c3p0.ComboPooledDataSource"
destroy-method="close">
<property name="driverClass"><value>com.mysql.jdbc.Driver</value></property>
<property name="jdbcUrl"><value>jdbc:mysql://localhost/springsourcery</value></property>
<property name="user"><value>test</value></property>
<property name="password"><value>test</value></property>
<property name="initialPoolSize"><value>3</value></property>
<property name="minPoolSize"><value>3</value></property>
<property name="maxPoolSize"><value>50</value></property>
<property name="idleConnectionTestPeriod"><value>200</value></property>
<property name="acquireIncrement"><value>1</value></property>
<property name="maxStatements"><value>0</value></property> <!-- 0 means: statement caching is turned off. -->
<property name="numHelperThreads"><value>3</value></property> <!-- 3 is default -->
</bean>
Next I set-up hibernate. The first stage in this process is to tell hibernate about the JPA annotated classes that will perform database access.
<!-- HIBERNATE CONFIGURATION -->
<bean id="sessionFactory"
class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
<property name="dataSource" ref="dataSource" />
<property name="annotatedClasses">
<list>
<value>com.springsourcery.community.model.User</value>
<value>com.springsourcery.community.model.Authorities</value>
<value>com.springsourcery.community.model.Topic</value>
<value>com.springsourcery.community.model.Blog</value>
<value>com.springsourcery.community.model.Question</value>
<value>com.springsourcery.community.model.Content</value>
<value>com.springsourcery.community.model.Comment</value>
<value>com.springsourcery.community.model.Scores</value>
</list>
</property>
<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">
org.hibernate.dialect.MySQLInnoDBDialect
</prop>
<prop key="hibernate.show_sql">true</prop>
<prop key="hibernate.hbm2ddl.auto">update</prop>
</props>
</property>
</bean>
Then I set-up the dialect that hibernate will use to talk to MySql.
Finally I set-up a transaction-manager so that I can take advantage of hibernates transaction support.
<tx:annotation-driven transaction-manager="txManager"/>
<bean id="txManager" class="org.springframework.orm.hibernate3.HibernateTransactionManager">
<property name="sessionFactory" ref="sessionFactory"/>
</bean>
Dependencies
Before any of this will work you need to set-up your environment. I have been using the gradle build management tool.
I have already added some tutorials on gradle at these URLs.
http://www.springsourcery.org/springSourcery/app/viewBlog/1/2/CACHEY,viewContent,1,2
http://www.springsourcery.org/springSourcery/app/viewBlog/1/4/CACHEY,viewContent,1,4
My build.gradle looks like this:
apply plugin : 'war'
apply plugin : 'eclipse'
//springVersion = "3.1.0.M2"
springVersion = "3.0.5.RELEASE"
springSecurityVersion = "3.0.5.RELEASE"
sourceCompatibility = 1.6
slf4jVersion = "1.6.1"
sourceSets.main.resources.srcDir '/Users/danmacklin/Documents/websites/springSourcery/src/main/webapp/WEB-INF/'
group = 'com.springsourcery.community'
version = '1.0'
repositories {
mavenCentral()
}
repositories {
mavenRepo urls:"http://maven.springframework.org/milestone"
}
dependencies{
compile "org.slf4j:jcl-over-slf4j:$slf4jVersion",
"org.slf4j:jul-to-slf4j:$slf4jVersion"
compile "org.hibernate:hibernate-entitymanager:3.4.0.GA",
"c3p0:c3p0:0.9.1",
"mysql:mysql-connector-java:5.0.5",
"cglib:cglib:2.2"
compile "org.springframework:spring-tx:$springVersion",
"org.springframework:spring-orm:$springVersion",
"org.springframework:spring-jdbc:$springVersion",
"org.springframework:spring-aspects:$springVersion",
"org.aspectj:aspectjrt:1.6.11"
compile "org.springframework:spring-webmvc:$springVersion",
"org.springframework:spring-aop:$springVersion"
compile "org.springframework.security:spring-security-web:$springSecurityVersion",
"org.springframework.security:spring-security-config:$springSecurityVersion"
compile "javax.validation:validation-api:1.0.0.GA",
"org.hibernate:hibernate-validator:4.0.2.GA"
compile "javax.servlet:servlet-api:2.5"
compile "javax.faces:jsf-api:1.2_02"
compile "javax.faces:jsf-impl:1.2-b19"
compile "javax.servlet:jstl:1.1.2",
"taglibs:standard:1.1.2",
"opensymphony:sitemesh:2.4.2"
compile "commons-fileupload:commons-fileupload:1.2.1"
compile "commons-io:commons-io:1.3.2"
compile "commons-lang:commons-lang:2.2"
compile "org.codehaus.jackson:jackson-core-asl:1.8.0"
compile "org.codehaus.jackson:jackson-core-lgpl:1.8.0"
compile "org.codehaus.jackson:jackson-mapper-asl:1.8.0"
compile "org.codehaus.jackson:jackson-mapper-lgpl:1.8.0"
runtime "org.slf4j:slf4j-log4j12:$slf4jVersion"
testCompile "junit:junit:4.8.2"
testCompile "org.springframework:spring-test:$springVersion"
}
Once you have installed gradle unzip the SpringSourcery source zip and type gradle cleanEclipse eclipse to create the development environment.
Then when you run the project on a web-server in your development environment springSourcery will connect to the database you created and install all of the tables!
NB. I built SpringSourcery using a mac. When I built it on a Windows 7 pc to write the tutorials I had to move the communityFileStore.properties to the resources directory (where log4j.properties file is) in order for it to be picked up on the classpath.
Conclusion
Within this tutorial I have shown you how to install MySql on windows and configure Spring to talk to it via a connection pool.
In the next tutorial we will write some code using JPA to talk to the database!
