SpringMVC + Spring + SpringJDBC整合

SpringMVC +Spring+ SpringJDBC整合实例。文件结构：

3S3（3Spring3.x）的整合大致如下：

1）web.xml的配置：

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd" version="3.0">
  <display-name></display-name>
  <welcome-file-list>
    <welcome-file>index.jsp</welcome-file>
  </welcome-file-list>
  <listener>
    <listener-class>org.springframework.web.util.IntrospectorCleanupListener</listener-class>
  </listener>
  <servlet>
    <servlet-name>spring</servlet-name>
    <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
    <init-param>
      <param-name>contextConfigLocation</param-name>
      <param-value>classpath:*applicationContext.xml</param-value>
    </init-param>
    <load-on-startup>1</load-on-startup>
  </servlet>
  <servlet-mapping>
    <servlet-name>spring</servlet-name>
    <url-pattern>*.do</url-pattern>
  </servlet-mapping>
  <!-- 统一字符编码为UTF-8 -->
  <filter>
    <filter-name>setcharacter</filter-name>
    <filter-class>org.springframework.web.filter.CharacterEncodingFilter</filter-class>
    <init-param>
      <param-name>encoding</param-name>
      <param-value>utf-8</param-value>
    </init-param>
  </filter>
  <filter-mapping>
    <filter-name>setcharacter</filter-name>
    <url-pattern>/*</url-pattern>
  </filter-mapping>
</web-app>

2）applicationContext.xml的配置：

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

<?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:p="http://www.springframework.org/schema/p"
    xmlns:context="http://www.springframework.org/schema/context"
    xmlns:aop="http://www.springframework.org/schema/aop" 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/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">
 
    <context:property-placeholder location="classpath:jdbc.properties" />
 
    <!-- 扫描类包，将标注Spring注解的类自动转化Bean，同时完成Bean的注入 -->
    <context:component-scan base-package="com.controller" />
    <context:component-scan base-package="com.service" />
    <context:component-scan base-package="com.dao" />
 
    <!-- 配置数据源 -->
    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"
        destroy-method="close">
        <property name="driverClass">
            <value>${jdbc.driverClassName}</value>
        </property>
        <property name="jdbcUrl">
            <value>${jdbc.url}</value>
        </property>
        <property name="user">
            <value>${jdbc.username}</value>
        </property>
        <property name="password">
            <value>${jdbc.password}</value>
        </property>
        <!--连接池中保留的最小连接数。 -->
        <property name="minPoolSize">
            <value>5</value>
        </property>
        <!--连接池中保留的最大连接数。Default: 15 -->
        <property name="maxPoolSize">
            <value>30</value>
        </property>
        <!--初始化时获取的连接数，取值应在minPoolSize与maxPoolSize之间。Default: 3 -->
        <property name="initialPoolSize">
            <value>10</value>
        </property>
        <!--最大空闲时间,60秒内未使用则连接被丢弃。若为0则永不丢弃。Default: 0 -->
        <property name="maxIdleTime">
            <value>60</value>
        </property>
        <!--当连接池中的连接耗尽的时候c3p0一次同时获取的连接数。Default: 3 -->
        <property name="acquireIncrement">
            <value>5</value>
        </property>
        <!--JDBC的标准参数，用以控制数据源内加载的PreparedStatements数量。但由于预缓存的statements 属于单个connection而不是整个连接池。所以设置这个参数需要考虑到多方面的因素。
            如果maxStatements与maxStatementsPerConnection均为0，则缓存被关闭。Default: 0 -->
        <property name="maxStatements">
            <value>0</value>
        </property>
        <!--每60秒检查所有连接池中的空闲连接。Default: 0 -->
        <property name="idleConnectionTestPeriod">
            <value>60</value>
        </property>
        <!--定义在从数据库获取新连接失败后重复尝试的次数。Default: 30 -->
        <property name="acquireRetryAttempts">
            <value>30</value>
        </property>
        <!--获取连接失败将会引起所有等待连接池来获取连接的线程抛出异常。但是数据源仍有效 保留，并在下次调用getConnection()的时候继续尝试获取连接。如果设为true，那么在尝试
            获取连接失败后该数据源将申明已断开并永久关闭。Default: false -->
        <property name="breakAfterAcquireFailure">
            <value>true</value>
        </property>
        <!--因性能消耗大请只在需要的时候使用它。如果设为true那么在每个connection提交的 时候都将校验其有效性。建议使用idleConnectionTestPeriod或automaticTestTable
            等方法来提升连接测试的性能。Default: false -->
        <property name="testConnectionOnCheckout">
            <value>false</value>
        </property>
    </bean>
 
    <!-- 配置Jdbc模板 -->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"></property>
    </bean>
 
    <!-- 配置事务管理器 -->
    <bean id="transactionManager"
        class="org.springframework.jdbc.datasource.DataSourceTransactionManager"
        p:dataSource-ref="dataSource" />
 
    <!-- 通过AOP配置提供事务增强，让service包下所有Bean的所有方法拥有事务 -->
    <aop:config proxy-target-class="true">
        <aop:pointcut id="serviceMethod"
            expression=" execution(* com.service..*(..))" />
        <aop:advisor pointcut-ref="serviceMethod" advice-ref="txAdvice" />
    </aop:config>
    <tx:advice id="txAdvice" transaction-manager="transactionManager">
        <tx:attributes>
            <tx:method name="*" />
        </tx:attributes>
    </tx:advice>
 
    <!-- 启动Spring MVC的注解功能，完成请求和注解POJO的映射 -->
    <bean
        class="org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter" />
     
     
    <!-- 配置视图解析器，将ModelAndView及字符串解析为具体的页面 -->
    <bean
        class="org.springframework.web.servlet.view.InternalResourceViewResolver"
        p:viewClass="org.springframework.web.servlet.view.JstlView" p:prefix="/WEB-INF/jsp/"
        p:suffix=".jsp" />
 
</beans>

3）jdbc.properties的配置:

jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/test2
jdbc.username=root
jdbc.password=123456

4)log4j.properties的配置:

log4j.rootLogger=DEBUG,A1
# \u8f93\u51fa\u5230\u63a7\u5236\u53f0
log4j.appender.A1=org.apache.log4j.ConsoleAppender
log4j.appender.A1.layout=org.apache.log4j.PatternLayout
log4j.appender.A1.layout.ConversionPattern=%d{yyyy-MM-dd HH\:mm\:ss} [\u65E5\u5FD7\u4FE1\u606F] %m%n

5)User.java

package com.model;
 
import java.io.Serializable;
 
@SuppressWarnings("serial")
public class User implements Serializable {
     
    private int id;
    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 int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
     
 
}

6)UserController.java

package com.controller;
 
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.servlet.ModelAndView;
 
import com.model.User;
import com.service.UserService;
 
@Controller
public class UserComtroller {
     
    @Autowired
    private UserService userService;
     
    @RequestMapping("/user/loginView")
    public String loginView(){
        return "login";
    }
     
    @RequestMapping("/user/registerView")
    public String registerView(){
        return "register";
    }
     
    @RequestMapping("/user/login")
    public ModelAndView login(User user){
        ModelAndView mav = new ModelAndView();
        User u = userService.loginCheck(user);
        if(null==u){
            mav.setViewName("login");
            mav.addObject("errorMsg","用户名或密码有误！");
            return mav;
        }
        else{
            mav.setViewName("success");
            mav.addObject("user", u);
            return mav;
        }
    }
     
    @RequestMapping("/user/register")
    public ModelAndView register(User user){
        ModelAndView mav = new ModelAndView();
        if(userService.register(user)){
            mav.setViewName("register_succ");
            return mav;
        }
        else{
            mav.setViewName("register");
            mav.addObject("errorMsg","用户名已被占用，请更换！！");
            return mav;
        }
    }
}

7)UserService Interface

package com.service;
 
import com.model.User;
 
 
public interface UserService {
    public boolean register(User user);
    public User loginCheck(User user);
}

8)UserServiceImp.java

package com.service.implement;
 
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
 
import com.dao.UserDao;
import com.model.User;
import com.service.UserService;
 
@Service
public class UserServiceImpl implements UserService {
 
    @Autowired
    private UserDao userDao;
     
    @Override
    public User loginCheck(User user) {
        User u  = userDao.findUserByUserName(user.getUserName());
        System.out.println("id="+u.getId()+",  userName="+u.getUserName()+", password="+u.getPassword());
        if(user.getPassword().equals(u.getPassword())){
            return u;
        }
        else{
            return null;
        }
    }
 
    @Override
    public boolean register(User user) {
        User u =  userDao.findUserByUserName(user.getUserName());
        if(u.getId()==0){
            userDao.register(user);
            return true;
        }
        else{
            System.out.println("id="+u.getId()+",  userName="+u.getUserName()+", password="+u.getPassword());
            return false;
        }
    }
 
}

9)UserDao Interface

package com.dao;
 
import com.model.User;
 
public interface UserDao {
    public void register(User user);
    public User findUserByUserName(final String userName);
}

10)UserDaoImpl.java

package com.dao.implement;
 
import java.sql.ResultSet;
import java.sql.SQLException;
 
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.stereotype.Repository;
 
import com.dao.UserDao;
import com.model.User;
 
@Repository
public class UserDaoImpl implements UserDao {
     
    @Autowired
    private JdbcTemplate jdbcTemplate;
 
    @Override
    public void register(User user) {
        String sqlStr = "insert into user(uname,pwd) values(?,?)";
        Object[] params = new Object[]{user.getUserName(),user.getPassword()};
        jdbcTemplate.update(sqlStr, params);
    }
 
    @Override
    public User findUserByUserName(String userName) {
        String sqlStr = "select id,uname,pwd from user where uname=?";
        final User user = new User();
        jdbcTemplate.query(sqlStr, new Object[]{userName}, new RowCallbackHandler() {
            @Override
            public void processRow(ResultSet rs) throws SQLException {
                user.setId(rs.getInt("id"));
                user.setUserName(rs.getString("uname"));
                user.setPassword(rs.getString("pwd"));
            }
        });
        return user;
    }
 
}

11）单元测试：test/com.service.implement

-UserServiceImplTest.java

package com.service.implement;
 
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
 
import com.model.User;
import com.service.UserService;
 
 
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations="/applicationContext.xml")
public class UserServiceTest {
     
    @Autowired
    private UserService userService;
     
    @Test
    public void testLoginCheck(){
        User user = new User();
        user.setUserName("manager2");
        user.setPassword("123");
        if(null!=userService.loginCheck(user))
            System.out.println("------OK!!-----");
        else
            System.out.println("------Sorry!!-----");
    }
     
    @Test
    public void testRegister(){
        User user = new User();
        user.setUserName("manager");
        user.setPassword("123");
        System.out.println(userService.register(user));
    }
 
}

12）测试结果：