Spring，Struts2，MyBatis，Activiti，Maven，H2，Tomcat集成（三）——H2，MyBatis集成

 1、配置h2，连接池，MyBatis Maven依赖；

    <!-- spring与数据库访问集成（非Hibernate） -->
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-jdbc</artifactId>
      <version>4.1.4.RELEASE</version>
    </dependency>
    <!-- h2数据库核心包 -->
    <dependency>
      <groupId>com.h2database</groupId>
      <artifactId>h2</artifactId>
      <version>1.4.189</version>
    </dependency>
    
    <!-- 添加druid连接池包 -->
    <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>druid</artifactId>
      <version>1.0.12</version>
    </dependency>
    
    <!-- 添加mybatis的核心包 -->
    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis</artifactId>
      <version>3.2.8</version>
    </dependency>
    <!-- 添加mybatis与Spring整合的核心包 -->
    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis-spring</artifactId>         
      <version>1.2.2</version>
    </dependency>

2、spring.xml中增加连接池配置；

    <!-- 引入属性文件,config.properties位于src/main/resources目录下 -->
    <context:property-placeholder location="classpath:config.properties"/>
    
    <!-- ========================================配置数据源========================================= -->
    <!-- 配置数据源，使用的是alibaba的Druid(德鲁伊)数据源 -->
    <bean name="dataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
        <property name="url" value="${jdbc_url}" />
        <property name="username" value="${jdbc_username}" />
        <property name="password" value="${jdbc_password}" />
        <!-- 初始化连接大小 -->
        <property name="initialSize" value="0" />
        <!-- 连接池最大使用连接数量 -->
        <property name="maxActive" value="20" />
        <!-- 连接池最大空闲 -->
        <property name="maxIdle" value="20" />
        <!-- 连接池最小空闲 -->
        <property name="minIdle" value="0" />
        <!-- 获取连接最大等待时间 -->
        <property name="maxWait" value="60000" />
        <!-- 
        <property name="poolPreparedStatements" value="true" /> 
        <property name="maxPoolPreparedStatementPerConnectionSize" value="33" /> 
        -->
        <property name="validationQuery" value="${validationQuery}" />
        <property name="testOnBorrow" value="false" />
        <property name="testOnReturn" value="false" />
        <property name="testWhileIdle" value="true" />
        <!-- 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒 -->
        <property name="timeBetweenEvictionRunsMillis" value="60000" />
        <!-- 配置一个连接在池中最小生存的时间，单位是毫秒 -->
        <property name="minEvictableIdleTimeMillis" value="25200000" />
        <!-- 打开removeAbandoned功能 -->
        <property name="removeAbandoned" value="true" />
        <!-- 1800秒，也就是30分钟 -->
        <property name="removeAbandonedTimeout" value="1800" />
        <!-- 关闭abanded连接时输出错误日志 -->
        <property name="logAbandoned" value="true" />
        <!-- 监控数据库 -->
        <!-- <property name="filters" value="stat" /> -->
        <property name="filters" value="mergeStat" />
    </bean>

3、添加数据库连接信息config.properties；

driverClassName=org.h2.Driver
validationQuery=SELECT 1
jdbc_url=jdbc:h2:tcp://localhost/D:/software/h2/db/h2learn
jdbc_username=sa
jdbc_password=sa

4、spring文件中配置数据库事务；

    <!-- ========================================spring事务配置================================= -->
    <!-- 配置Spring的事务管理器 -->
    <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource" />
    </bean>

    <!-- 注解方式配置事物 -->
    <!-- <tx:annotation-driven transaction-manager="transactionManager" /> -->
    
    <!-- 拦截器方式配置事物 -->
    <tx:advice id="transactionAdvice" transaction-manager="transactionManager">
        <tx:attributes>
            <tx:method name="insert*" propagation="REQUIRED" />
            <tx:method name="update*" propagation="REQUIRED" />
            <tx:method name="delete*" propagation="REQUIRED" />

            <tx:method name="get*" propagation="SUPPORTS" />

            <tx:method name="*" propagation="SUPPORTS" />
        </tx:attributes>
    </tx:advice>
    <aop:config>
        <aop:pointcut id="transactionPointcut" expression="execution(* com.coshaho.learn.service..*Impl.*(..))" />
        <aop:advisor pointcut-ref="transactionPointcut" advice-ref="transactionAdvice" />
    </aop:config>

注：spring文件中申明tx，aop标签定义；

<?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: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-3.0.xsd 
http://www.springframework.org/schema/context 
http://www.springframework.org/schema/context/spring-context-3.0.xsd
http://www.springframework.org/schema/tx 
http://www.springframework.org/schema/tx/spring-tx-3.0.xsd
http://www.springframework.org/schema/aop 
http://www.springframework.org/schema/aop/spring-aop-3.0.xsd">

5、添加spring事务jar包依赖；

    <!-- ===========================spring事务========================== -->
    <!--添加aspectjweaver包，spring-jdbc已经包含spring-tx -->
    <dependency>
      <groupId>org.aspectj</groupId>
      <artifactId>aspectjweaver</artifactId>
      <version>1.8.5</version>
    </dependency>

6、增加pojo，dao，service，action层相应测试类；

package com.coshaho.learn.pojo;

public class User 
{
    private int id;
    
    private String username;
    
    private String password;
    
    private String description;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }
}

package com.coshaho.learn.dao;

import com.coshaho.learn.pojo.User;

public interface UserDao 
{
    User getUser(int id);

    int insertUser(User user);
    
    int updateUser(User user);
    
    int deleteUser(int id);
    
    int getUserNum(User user);
}

package com.coshaho.learn.service;

import com.coshaho.learn.pojo.User;

public interface UserServiceI 
{
    User getUser(int id);

    int insertUser(User user);
    
    int updateUser(User user);
    
    int deleteUser(int id);
    
    int getUserNum(User user);
}

package com.coshaho.learn.service.impl;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.coshaho.learn.dao.UserDao;
import com.coshaho.learn.pojo.User;
import com.coshaho.learn.service.UserServiceI;

@Service("userServiceImpl")
public class UserServiceImpl implements UserServiceI
{
    @Autowired
    private UserDao userDao;

    public User getUser(int id) 
    {
        return userDao.getUser(id);
    }

    public int insertUser(User user) 
    {
        return userDao.insertUser(user);
    }

    public int updateUser(User user) 
    {
        return userDao.updateUser(user);
    }

    public int deleteUser(int id) 
    {
        return userDao.deleteUser(id);
    }

    public int getUserNum(User user) 
    {
        return userDao.getUserNum(user);
    }

}

package com.coshaho.learn.action;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;

import com.coshaho.learn.pojo.User;
import com.coshaho.learn.service.UserServiceI;

@Controller("loginDB")
public class LoginDBAction 
{
    @Autowired
    private UserServiceI userService;
    
    private String username;
    private String password;
    public String getUsername() {
        return username;
    }
    public void setUsername(String username) {
        this.username = username;
    }
    public String getPassword() {
        return password;
    }
    public void setPassword(String password) {
        this.password = password;
    }
    
    public String authority()
    {
        User user = new User();
        user.setUsername(username);
        user.setPassword(password);
        
        if(userService.getUserNum(user) > 0 )
        {
            return "success";
        }
        
        return "error";
    }
}

7、增加MyBatis映射文件UserMapper.xml;

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.coshaho.learn.dao.UserDao">
    <select id="getUser" parameterType="int" resultType="com.coshaho.learn.pojo.User">
        SELECT * FROM USER WHERE ID=#{id}
    </select>
    
    <select id="getUserNum" parameterType="com.coshaho.learn.pojo.User" resultType="int">
        SELECT count(0) FROM USER WHERE USERNAME=#{username} AND PASSWORD=#{password}
    </select>

    <insert id="insertUser" parameterType="com.coshaho.learn.pojo.User">
        insert into user(id, username, password, description) 
        values(#{id,jdbcType=INTEGER},#{username,jdbcType=VARCHAR},
        #{password,jdbcType=VARCHAR},#{description,jdbcType=VARCHAR});
    </insert>

    <update id="updateUser" parameterType="com.coshaho.learn.pojo.User">
        update user
        <set>
            <if test="username!=null">
                username=#{username,jdbcType=VARCHAR},
            </if>
            <if test="password!=null">
                password=#{password,jdbcType=VARCHAR},
            </if>
            <if test="description!=null">
                description=#{description,jdbcType=VARCHAR},
            </if>
        </set>
        where id=#{id,jdbcType=INTEGER}
    </update>

    <delete id="deleteUser" parameterType="int">
        delete from user where id=#{id}
    </delete>
</mapper>

注：映射文件中namespace要和dao层接口名称一致。
8、spring.xml中集成MyBatis；

    <!-- ========================================针对myBatis的配置项============================== -->
    <!-- 配置sqlSessionFactory -->
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource"/>
        <property name="mapperLocations" value="classpath:com/coshaho/learn/pojo/*.xml"/>
    </bean>
    
    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="basePackage" value="com.coshaho.learn.dao"/>
        <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>
    </bean>

9、配置struts2文件;

    <action name="authorityDB" class="loginDB" method="authority">
        <result name="success">/success.jsp</result>
        <result name="error">/error.jsp</result>
    </action>

10、H2数据库USER表并插入测试数据；

 

11、增加登陆页面logindb.jsp；

<html>
<head>
<%String path = request.getContextPath();%>
</head>
<body>
    <form action="<%=path%>/framework/authorityDB.action" method="post">
        UserName:<input type="text" name="username"/><p/>
        Password:<input type="password" name="password"/><p/>
        <input type="submit" name="submit"/>
    </form>
</body>
</html>

12、启动tomcat，访问http://localhost:8080/FrameIntegrationWeb/logindb.jsp。