webx学习笔记3--持久层配置

创建数据库message_board

webx学习笔记3--持久层配置

1. 数据库表结构的创建：

webx学习笔记3--持久层配置

-----------------------------------------------------------------------------------------------------------------------------------------------------

因为要连接数据库，以及用到了ibatis作为持久层框架(ORM:对象关系映射)。

所以需要在pom.xml配置所需要的依赖包。

依赖的包可以在maven仓库里查找（http://search.maven.org/#search%7Cga%7C1%7C）

需要找的驱动依赖包是：

查找ibatis，选择org.apache.servicemix.bundles.ibatis-sqlmap这个包。

查找dbcp，选择commons-dbcp。

查找mysql，选择mysql-connector-java。

这些包都是随便选的，只是为了实现项目的需要。没有特定的标准，如果有特殊需要可以选择别的包。

-----------------------------------------------------------------------------------------------------------------------------------------------------

找到需要的包版本号之后按照包的pom.xml文件，补充项目的pom.xml：

在properties标签中加入版本号：

 <ibatis-version>3.0-beta-10</ibatis-version>
        <dbcp-version>20030825.184428</dbcp-version>
        <mysql-version>5.1.33</mysql-version>

在dependencies中加入依赖的项目号和组号：

        <dependency>
            <groupId>org.apache.ibatis</groupId>
            <artifactId>ibatis-sqlmap</artifactId>
        </dependency>
        <dependency>
            <groupId>commons-dbcp</groupId>
            <artifactId>commons-dbcp</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>

在依赖管理( dependencyManagemen )中加入详细信息：

            <dependency>
            <groupId>org.apache.ibatis</groupId>
            <artifactId>ibatis-sqlmap</artifactId>
            <version>${ibatis-version}</version>
            </dependency>
            <dependency>
            <groupId>commons-dbcp</groupId>
            <artifactId>commons-dbcp</artifactId>
            <version>${dbcp-version}</version>
            </dependency>
            <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>${mysql-version}</version>
            </dependency>

-----------------------------------------------------------------------------------------------------------------------------------------------------

然后配置持久层框架：

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>

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>

usr-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>

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>

秒客网

webx学习笔记3--持久层配置

相关文章