github:https://github.com/wade6/messageboard-webx3
配置持久层
4、配置数据源
首先在本地新建一个mysql数据库,端口设为3306,数据库的名字为message_board,其中有两个表
user表
message表
在持久层使用spring框架中的ibatis,所以需要在项目中集成ibaits框架,而且数据库使用的是mysql,也要在项目中导入mysql的驱动依赖包,此外这个项目中会使用数据库连接池,所以也要导入数据库连接池的依赖包。
依赖的包可以在maven仓库里查找(http://search.maven.org/#search%7Cga%7C1%7C);
查找ibatis,选择org.apache.servicemix.bundles.ibatis-sqlmap这个包,版本号2.3.4.726_4;
查找dbcp,选择commons-dbcp,版本号1.2.2;
查找mysql,选择mysql-connector-java,版本号5.1.6
注意:以上选的包只是随便选的,只是为了实现本项目的功能,没有什么标准,如果有其他要求,可以选择其他的包。
因为我们使用maven管理项目,可以方便的在pom.xml中配置这些依赖。
在pom.xml中增加的配置项如下:
-----------------------------------------------------------------------
设置依赖的版本号:
<properties>
......
<ibatis-version>2.3.4.726_4</ibatis-version>
<dbcp-version>1.2.2</dbcp-version>
<mysql-version>5.1.6</mysql-version>
</properties>
设置依赖的包:
<dependencies> ...... <!-- 集成 ibatis -导入依赖包--> <dependency> <groupId>commons-dbcp</groupId> <artifactId>commons-dbcp</artifactId> </dependency> <dependency> <groupId>org.apache.servicemix.bundles</groupId> <artifactId>org.apache.servicemix.bundles.ibatis-sqlmap</artifactId> </dependency> <!-- 导入mysql驱动jar包 --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> </dependency> </dependencies>
设置管理依赖项:
<dependencyManagement> <dependencies> <!-- 集成 ibatis -导入依赖包--> <dependency> <groupId>commons-dbcp</groupId> <artifactId>commons-dbcp</artifactId> <version>${dbcp-version}</version> </dependency> <dependency> <groupId>org.apache.servicemix.bundles</groupId> <artifactId>org.apache.servicemix.bundles.ibatis-sqlmap</artifactId> <version>${ibatis-version}</version> </dependency> <!-- 导入mysql驱动jar包 --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>${mysql-version}</version> </dependency> </dependencies> ...... <dependencyManagement>
-----------------------------------------------------------------------
然后配置项目的持久层框架
首先在src/main/resources中建立以下文件:
配置上面的文件
dal-data-source.xml
-----------------------------------------------------------------------
<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context" xmlns:p="http://www.springframework.org/schema/p" xmlns:tx="http://www.springframework.org/schema/tx" xmlns:aop="http://www.springframework.org/schema/aop" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-2.5.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-2.5.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-2.5.xsd"> <!-- 数据库配置 --> <bean id= "dataSource" class="org.apache.commons.dbcp.BasicDataSource"> <property name="driverClassName" value="com.mysql.jdbc.Driver"></property> <property name="url" value="jdbc:mysql://127.0.0.1:3306/message_board"></property> <property name="username" value="webx3"></property> <property name="password" value="webx3"></property> <property name="minIdle" value="0" ></property> <property name="maxWait" value="-1"></property> </bean> <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"> <property name="dataSource" ref="dataSource" /> </bean> <bean id="transactionTemplate" class="org.springframework.transaction.support.TransactionTemplate"> <property name="transactionManager" ref="transactionManager"></property> </bean> <!-- iBatis SQL map定义 --> <bean id="sqlMapClient" class="org.springframework.orm.ibatis.SqlMapClientFactoryBean"> <property name="dataSource" ref="dataSource" /> <property name="configLocation" value="classpath:sqlmap-config.xml" /> </bean> </beans>
-----------------------------------------------------------------------
sqlmap-config.xml
-----------------------------------------------------------------------
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE sqlMapConfig PUBLIC "-//iBATIS.com//DTD SQL Map Config 2.0//EN" "http://www.ibatis.com/dtd/sql-map-config-2.dtd"> <sqlMapConfig> <sqlMap resource="sqlmap/user-sqlmap.xml" ></sqlMap> <sqlMap resource="sqlmap/message-sqlmap.xml" ></sqlMap> </sqlMapConfig>
-----------------------------------------------------------------------
message-sqlmap.xml
-----------------------------------------------------------------------
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd" >
<sqlMap namespace="message" >
<typeAlias alias="messageDO" type="com.alibaba.webx3.messageboard.dao.object.MessageDO" />
<insert id="insertMessage" parameterClass="messageDO" >
insert into message (
id,
title,
author,
content,
gmt_create,
gmt_modified
) values (
#id#,
#title#,
#author#,
#content#,
now(),
now()
)
<selectKey resultClass="int" keyProperty="id">
<![CDATA[SELECT LAST_INSERT_ID() AS ID ]]>
</selectKey>
</insert>
<select id="selectById" parameterClass="int" resultClass="messageDO" >
select
id,
title,
author,
content,
gmt_create,
gmt_modified
from
message
where
id=#id#
</select>
<select id="selectBylist" parameterClass="map" resultClass="messageDO" >
select
id,
title,
author,
content,
gmt_create,
gmt_modified
from
message
order by gmt_modified desc
limit #from#,#size#
</select>
<update id="updateMessage" parameterClass="messageDO">
update
message
set
gmt_modified = now()
<dynamic prepend="" >
<isNotEmpty prepend="," property="title">
title = #title#
</isNotEmpty>
<isNotEmpty prepend="," property="author">
author = #author#
</isNotEmpty>
<isNotEmpty prepend="," property="content">
content = #content#
</isNotEmpty>
</dynamic>
where id = #id#
</update>
<delete id="deleteMessage" parameterClass="int">
delete
from
message
where
id=#id#
</delete>
</sqlMap>
-----------------------------------------------------------------------
user-sqlmap.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd" >
<sqlMap namespace="user">
<typeAlias alias="userDO" type="com.alibaba.webx3.messageboard.dao.object.UserDO" />
<insert id="insertUser" parameterClass="userDO" >
insert into user (
id,
username,
password,
gmt_create,
gmt_modified
) values (
#id#,
#username#,
#password#,
now(),
now()
)
<selectKey resultClass="int" keyProperty="id">
<![CDATA[SELECT LAST_INSERT_ID() AS ID ]]>
</selectKey>
</insert>
<select id="selectByUsername" parameterClass="String" resultClass="userDO" >
select
id,
username,
password,
gmt_create,
gmt_modified
from
user
where
username=#username#
</select>
<update id="updateuser" parameterClass="userDO">
update
user
set
gmt_modified = now()
<dynamic prepend="" >
<isNotEmpty prepend="," property="username">
username = #username#
</isNotEmpty>
<isNotEmpty prepend="," property="password">
password = #password#
</isNotEmpty>
</dynamic>
where id = #id#
</update>
<delete id="deleteuser" parameterClass="userDO">
delete
from
user
where
id=#id#
</delete>
</sqlMap>
-----------------------------------------------------------------------
最后,在webx配置文件webx-messageboard.xml中添加如下配置,将持久层加载到webx容器中:
<beans:import resource="classpath:dal/dal-data-source.xml" />
至此,数据源配置结束。