jdbcTemplate整合SpringMVC

最新推荐文章于 2019-12-02 17:37:09 发布

最新推荐文章于 2019-12-02 17:37:09 发布 · 186 阅读

文章标签：

#jdbcTemplate

Spring 专栏收录该内容

8 篇文章

订阅专栏

本文介绍了一个基于SpringMVC框架与JdbcTemplate实现的用户管理模块案例，详细展示了如何通过JdbcTemplate操作数据库，包括增删改查等基本功能。

package com.org;

import javax.annotation.PostConstruct;
import javax.annotation.Resource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.support.JdbcDaoSupport;


/**
 * @author liangjilong
 */
public class JdbcTempBaseDao extends JdbcDaoSupport{

	/**
	 */
	@Resource(name = "jdbcTemplate")
	public JdbcTemplate jdbcTemplate;  
  
    @PostConstruct  
    public void initSqlMapClient() {  
         super.setJdbcTemplate(jdbcTemplate);
    } 
}

package com.org.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.stereotype.Repository;

import com.org.JdbcTempBaseDao;
import com.org.dao.IUserDao;
import com.org.model.User;

@Repository
@SuppressWarnings("all")
public class UserDaoImpl extends JdbcTempBaseDao implements IUserDao {

	@Override
	public List<User> getUserList() {
		String sql="select * from user ";
		final List<User>  list= new ArrayList<User>();
		jdbcTemplate.query(sql, new RowCallbackHandler(){
			@Override
			public void processRow(ResultSet rs) throws SQLException {
				User u=new User();
				u.setId(rs.getInt("id"));
				u.setUsername(rs.getString("username"));
				u.setPassword(rs.getString("password"));
				u.setCreateDate(rs.getString("createDate"));
				u.setModifyDate(rs.getString("modifyDate"));
				u.setType(rs.getString("type"));
				list.add(u);
			}
		});
		
		return list;
	}

	@Override
	public List<User> getUserLists(Map<String, Object> map) {
		return null;
	}

	@Override
	public Integer getUserCount(Map<String, Object> map) {
		 String sql = "select count(1) from User where id=? ";
	     return getJdbcTemplate().queryForObject(sql, Integer.class,map);
	}

	@Override
	public User getUserById(Integer primaryKeyId) {
		 String sql = "select id,username, password, createDate, modifyDate,type from User where id=?";
        List<User> userList = getJdbcTemplate().query(sql, new BeanPropertyRowMapper(User.class), primaryKeyId);
        if(userList.size() == 0) {
            return null;
        }
        return userList.get(0);
	}

	@Override
	public void delUserById(Integer primaryKeyId) {
		 String sql = "delete from user where id=?";
	     getJdbcTemplate().update(sql, primaryKeyId);
	}

	@Override
	public User addUser(final User entity) {
		final String sql = "insert into User(username, password, createDate, modifyDate,type) values(?,?,?,?,?)";

        GeneratedKeyHolder keyHolder = new GeneratedKeyHolder();
        getJdbcTemplate().update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement psst = connection.prepareStatement(sql, new String[]{"id"});
                psst.setString(1, entity.getUsername());
                psst.setString(2, entity.getPassword());
                psst.setString(3, entity.getCreateDate());
                psst.setString(4, entity.getModifyDate());
                psst.setString(5, entity.getType());
                return psst;
            }
        }, keyHolder);

        entity.setId(keyHolder.getKey().intValue());
        return entity;
	}

	@Override
	public void editUser(User entity) {
		String sql="update user set username=?,password=?";
		//jdbcTemplate.update(sql, User.class,entity);
	} 
}

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:mvc="http://www.springframework.org/schema/mvc" 
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
	xmlns:context="http://www.springframework.org/schema/context"
	xmlns:aop="http://www.springframework.org/schema/aop" 
	xmlns:tx="http://www.springframework.org/schema/tx"
	xmlns:oscache="http://www.springmodules.org/schema/oscache"
	xsi:schemaLocation="http://www.springframework.org/schema/beans
	 	 http://www.springframework.org/schema/beans/spring-beans-3.1.xsd 
	 	 http://www.springframework.org/schema/context
	 	 http://www.springframework.org/schema/context/spring-context-3.1.xsd 
	 	 http://www.springframework.org/schema/aop 
	 	 http://www.springframework.org/schema/aop/spring-aop-3.1.xsd 
	 	 http://www.springframework.org/schema/tx 
	 	 http://www.springframework.org/schema/tx/spring-tx-3.1.xsd 
	 	 http://www.springmodules.org/schema/oscache 
	 	 http://www.springmodules.org/schema/cache/springmodules-oscache.xsd
	 	 http://www.springframework.org/schema/mvc
	 	 http://www.springframework.org/schema/mvc/spring-mvc-3.1.xsd">

	<!--
		 对web包中的所有类进行扫描，以完成Bean创建和自动依赖注入的功能 
		 mvc:annotation-driven
	--> 
	<mvc:annotation-driven/>
	<!-- 扫描包 -->
	<context:annotation-config/>  
	<context:component-scan base-package="com.org.*" />
	
	
	<bean id="jspViewResolver" class="org.springframework.web.servlet.view.InternalResourceViewResolver">
		<property name="viewClass" value="org.springframework.web.servlet.view.JstlView" />
		<!-- 匹配jsp文件下面的所有.jsp的页面 -->
		<property name="prefix" value="/jsp/" />
		<property name="suffix" value=".jsp" /> 
	</bean>
	 <!-- 
	 	<bean class="org.springframework.web.servlet.view.InternalResourceViewResolver"
		p:prefix="/" p:suffix=".jsp" />-->
	
	<!-- 配置jdbc -->
	<bean class="org.springframework.beans.factory.config.PreferencesPlaceholderConfigurer">
		<property name="locations">
			<value>classpath:jdbc.properties</value>
		</property>
	</bean>
	<!-- 配置數據源 -->
	<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
		destroy-method="close">
		<property name="driverClassName" value="${jdbc.driver}" />
		<property name="url" value="${jdbc.url}" />
		<property name="username" value="${jdbc.username}" />
		<property name="password" value="${jdbc.password}" />
		<!-- 连接池启动时的初始值 -->
        <property name="initialSize" value="${initialSize}"/>  
        <property name="maxActive" value="${maxActive}"/>    
        <property name="maxIdle" value="${maxIdle}"/>        
        <property name="minIdle" value="${minIdle}"/> 
	</bean>
		
   <!-- 配置jdbcTemplate模板 -->
	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
		<property name="dataSource" ref="dataSource" />
	</bean>
	<!-- 配置 transactionManager事物管理-->
	<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
		<property name="dataSource" ref="dataSource"/>
	</bean>
	
	
	<!-- Spring AOP config配置切点 -->  
	<aop:config>
		<aop:pointcut expression="execution(* com.org.service.*.*(..))" id="bussinessService" />
		<aop:advisor advice-ref="txAdvice" pointcut-ref="bussinessService"/>
	</aop:config>

	<!-- 配置那个类那个方法用到事务处理 -->
	<tx:advice id="txAdvice" transaction-manager="transactionManager">
		<tx:attributes>
			<tx:method name="get*" read-only="true"/>
			<tx:method name="add*" propagation="REQUIRED"/>
			<tx:method name="update*" propagation="REQUIRED"/>
			<tx:method name="delete*" propagation="REQUIRED"/>
			<tx:method name="*" propagation="REQUIRED"/>
		</tx:attributes>
	</tx:advice>
	 
	 
<!-- 这个映射配置主要是用来进行静态资源的访问 -->
 <mvc:resources mapping="/js/**" location="/js/" cache-period="31556926"/> 
 <mvc:resources mapping="/resource/**" location="/resource/" />  
 <mvc:resources mapping="/jsp/**" location="/jsp/" /> 
 
</beans>

<?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_2_5.xsd" id="WebApp_ID" version="2.5">
  <!--
		#####################################配置处理乱码#####################################
	-->
	<filter>
		<filter-name>encodingFilter</filter-name>
		<filter-class>org.springframework.web.filter.CharacterEncodingFilter</filter-class>
		<init-param>
			<param-name>encoding</param-name>
			<param-value>GBK</param-value>
		</init-param>
	</filter>
	<filter-mapping>
		<filter-name>encodingFilter</filter-name>
		<url-pattern>/*</url-pattern>
	</filter-mapping>
	
	<!-- spring监听器 -->
	<listener>
		<listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
	</listener>
	<context-param>
		<param-name>contextConfigLocation</param-name>
		<!--
			默认的配置
			<param-value>/WEB-INF/applicationContext-*.xml,classpath*:applicationContext-*.xml</param-value>
			<param-value>/WEB-INF/application-servlet.xml</param-value>
		-->
		<param-value>classpath:application-*.xml</param-value>
	</context-param>

	<!--
		#####################################Spring	MVC配置#################################
		application-servlet.xml,规定:xxx-servlet.xml-->
	<servlet>
		<servlet-name>application</servlet-name>
		<servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
		<init-param>
			<param-name>contextConfigLocation</param-name>
			<param-value>classpath:application-*.xml</param-value>
		</init-param>
		<load-on-startup>1</load-on-startup>
	</servlet>
	<servlet-mapping>
		<servlet-name>application</servlet-name>
		<url-pattern>/</url-pattern>
	</servlet-mapping>
	
	 
  <welcome-file-list>
    <welcome-file>userList1.do</welcome-file>
    <welcome-file>index.htm</welcome-file>
    <welcome-file>index.jsp</welcome-file>
    <welcome-file>default.html</welcome-file>
    <welcome-file>default.htm</welcome-file>
    <welcome-file>default.jsp</welcome-file>
  </welcome-file-list>
</web-app>

访问 http://localhost:8080/SpringMVC_jdbcTemplate/userList1.do