spring jdbcTemplate使用-CSDN博客

本文链接：https://blog.csdn.net/pyzheng/article/details/84405891

关于java.lang.IllegalArgumentException: 'dataSource' or 'jdbcTemplate' is required 类似问题的解决总结:[url]http://www.cnblogs.com/MoShin/archive/2011/10/10/2204020.html[/url]

参考:
spring jdbcTemplate使用[url]http://log-cd.iteye.com/blog/215059[/url]
Spring JdbcTemplate 与事务管理学习 [url]http://www.iteye.com/topic/480432[/url]

SimpleJdbcTemplate在spring3.1已经过时了，我就改为使用jdbcTemplate和namedParameterJdbcOperations写sql查询
* @deprecated since Spring 3.1 in favor of {@link org.springframework.jdbc.core.JdbcTemplate} and
* {@link org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate}. The JdbcTemplate and
* NamedParameterJdbcTemplate now provide all the functionality of the SimpleJdbcTemplate.

Spring NamedParameterJdbcTemplate 详解 : [url]http://blog.csdn.net/dyllove98/article/details/7772470[/url]

pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>com.pandy</groupId>
	<artifactId>ssjdbc</artifactId>
	<packaging>war</packaging>
	<version>0.0.1-SNAPSHOT</version>
	<name>ssm3 Maven Webapp</name>
	<url>http://maven.apache.org</url>
	<properties>
		<spring.version>3.2.1.RELEASE</spring.version>
		<spring.jdbc.version>3.2.1.RELEASE</spring.jdbc.version>
		<aspectj.version>1.6.10</aspectj.version>
		<jetty.version>6.1.26</jetty.version>
		<tomcat.version>2.1-SNAPSHOT</tomcat.version>
		<junit.version>3.8.1</junit.version>
		<mysql.version>5.1.23</mysql.version>
		<jtds.version>1.2.4</jtds.version>
		<cglib.version>2.2.2</cglib.version>
	</properties>

	<dependencies>
		<dependency>
			<groupId>junit</groupId>
			<artifactId>junit</artifactId>
			<version>${junit.version}</version>
			<scope>test</scope>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-core</artifactId>
			<version>${spring.version}</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-web</artifactId>
			<version>${spring.version}</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-webmvc</artifactId>
			<version>${spring.version}</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-beans</artifactId>
			<version>${spring.version}</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-context</artifactId>
			<version>${spring.version}</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-aop</artifactId>
			<version>${spring.version}</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-tx</artifactId>
			<version>${spring.version}</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-orm</artifactId>
			<version>${spring.version}</version>
		</dependency>

		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-jdbc</artifactId>
			<version>${spring.jdbc.version}</version>
		</dependency>

		<dependency>
			<groupId>cglib</groupId>
			<artifactId>cglib</artifactId>
			<version>${cglib.version}</version>
		</dependency>

		<dependency>
			<groupId>net.sourceforge.jtds</groupId>
			<artifactId>jtds</artifactId>
			<version>${jtds.version}</version>
		</dependency>

		<dependency>
			<groupId>commons-beanutils</groupId>
			<artifactId>commons-beanutils</artifactId>
			<version>1.8.3</version>
		</dependency>

		<!-- <dependency> <groupId>org.aspectj</groupId> <artifactId>acpectjrt</artifactId> 
			<version>${aspectj.version}</version> </dependency> <dependency> <groupId>org.aspectj</groupId> 
			<artifactId>acpectjweaver</artifactId> <version>${aspectj.version}</version> 
			</dependency> -->

	</dependencies>
	<build>
		<finalName>ssjdbc</finalName>

		<pluginManagement>
			<plugins>
				<plugin>
					<groupId>org.mortbay.jetty</groupId>
					<artifactId>maven-jetty-plugin</artifactId>
					<version>${jetty.version}</version>
				</plugin>
				<plugin>
					<groupId>org.apache.tomcat.maven</groupId>
					<artifactId>tomcat7-maven-plugin</artifactId>
					<version>${tomcat.version}</version>
				</plugin>
			</plugins>

		</pluginManagement>
	</build>

</project>

web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app version="3.0" xmlns="http://java.sun.com/xml/ns/javaee"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://java.sun.com/xml/ns/javaee 
	http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd">
	<display-name></display-name>


	<context-param>
		<param-name>contextConfigLocation</param-name>
		<param-value>/WEB-INF/applicationContext.xml</param-value>
	</context-param>
	<listener>
		<listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
	</listener>

	<servlet>
		<servlet-name>ssjdbc</servlet-name>
		<servlet-class>
			org.springframework.web.servlet.DispatcherServlet
		</servlet-class>
		<load-on-startup>1</load-on-startup>
	</servlet>
	<servlet-mapping>
		<servlet-name>ssjdbc</servlet-name>
		<url-pattern>*.do</url-pattern>
	</servlet-mapping>

	<welcome-file-list>
		<welcome-file>index.jsp</welcome-file>
	</welcome-file-list>
</web-app>

jdbc.properties

jdbc.driver=net.sourceforge.jtds.jdbc.Driver
jdbc.url=jdbc:jtds:sqlserver://localhost:1433;DatabaseName=Test
jdbc.username=sa  
jdbc.password=sa

application.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:context="http://www.springframework.org/schema/context" xmlns:tx="http://www.springframework.org/schema/tx"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:p="http://www.springframework.org/schema/p"
	xsi:schemaLocation="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/beans 
	http://www.springframework.org/schema/beans/spring-beans-3.1.xsd">

	<context:component-scan base-package="com">
		<context:exclude-filter expression="org.springframework.stereotype.Controller"
			type="annotation" />
	</context:component-scan>
	<bean
		class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
		<property name="locations">
			<value>classpath:jdbc.properties</value>
		</property>
	</bean>

	<bean id="dataSource"
		class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<property name="driverClassName" value="${jdbc.driver}" />
		<property name="url" value="${jdbc.url}" />
		<property name="username" value="${jdbc.username}" />
		<property name="password" value="${jdbc.password}" />
	</bean>
	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
		<property name="dataSource" ref="dataSource" />
	</bean>
	<bean id="transactionManager"
		class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
		<property name="dataSource" ref="dataSource" />
	</bean>
	<tx:annotation-driven transaction-manager="transactionManager"
		proxy-target-class="true" />
</beans>

ssjdbc-servlet.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:mvc="http://www.springframework.org/schema/mvc"  
    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/mvc    
    http://www.springframework.org/schema/mvc/spring-mvc-3.0.xsd">

    <mvc:annotation-driven />  
    <context:component-scan base-package="com" use-default-filters="false">
    	<context:include-filter expression="org.springframework.stereotype.Controller" type="annotation"/>
    </context:component-scan>    

    <bean class="org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter"></bean>  
    <bean class="org.springframework.web.servlet.mvc.HttpRequestHandlerAdapter"></bean>  

    <bean id="viewResolver" class="org.springframework.web.servlet.view.InternalResourceViewResolver">
		<property name="prefix" value="/views/"></property>
		<property name="suffix" value=".jsp"></property>
	</bean>

</beans>

domain和mapper
--------------------------------------

package com.domain;


public class Table3 {

	private int id;
	private String name;
	private String password;
	private String email;

	public Table3() {

	}

	public Table3(int id, String name, String password, String email) {
		this.id = id;
		this.name = name;
		this.password = password;
		this.email = email;

	}

	..................

	@Override
	public String toString() {
		return "Table3 [id=" + id + ", name=" + name + ", password=" + password
				+ ", email=" + email + "]";
	}

}

package com.domain;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.RowMapper;

@SuppressWarnings("rawtypes")
public class Table3Mapper implements RowMapper {

	@Override
	public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
		Table3 table3 = new Table3(rs.getInt("ID"), rs.getString("NAME"),
				rs.getString("PASSWORD"), rs.getString("EMAIL"));
		return table3;
	}

}

DAO
-------------------------------

package com.dao;

import javax.annotation.Resource;

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

public abstract class BasicDao  extends JdbcDaoSupport {

	//这里使用注解,是为了父类被初始化,因为Spring启动的时候会做这些工作.
	@Resource
	public void setJdbc(JdbcTemplate jdbcTemplate) {
		super.setJdbcTemplate(jdbcTemplate);
	}
}

package com.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.stereotype.Repository;

import com.domain.Table3;
import com.domain.Table3Mapper;

@Repository
public class Table3Dao extends BasicDao {

	@Resource(name = "jdbcTemplate")
	private JdbcTemplate jdbcTemplate;

	@SuppressWarnings("unchecked")
	public Table3 findTable3ByName1(String name) {
		ParameterizedRowMapper<Table3> mapper = new ParameterizedRowMapper<Table3>() {
			public Table3 mapRow(ResultSet rs, int rowNum) throws SQLException {
				Table3 table3 = new Table3(rs.getInt("ID"),
						rs.getString("NAME"), rs.getString("PASSWORD"),
						rs.getString("EMAIL"));
				return table3;
			}
		};

		try {
			Table3 pb = (Table3) getJdbcTemplate().queryForObject(
					"select ID,NAME,PASSWORD,EMAIL from TABLE3 where NAME = ?",
					new Object[] { name }, mapper);
			return pb;
		} catch (org.springframework.dao.EmptyResultDataAccessException e) {
			return new Table3();
		}

	}

	/**
	 * 与上方法有点不一样,是直接在方法里面创建Mapper
	 * 
	 * @param name
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public Table3 findTable3ByName(String name) {
		try {
			Table3 pb = (Table3) getJdbcTemplate().queryForObject(
					"select ID,NAME,PASSWORD,EMAIL from TABLE3 where NAME = ?",
					new Object[] { name }, new Table3Mapper());
			return pb;
		} catch (org.springframework.dao.EmptyResultDataAccessException e) {
			return new Table3();
		}

	}

	@SuppressWarnings("unchecked")
	public Table3 listAll() {
		Table3 pb = (Table3) getJdbcTemplate()
				.queryForObject("select ID,NAME,PASSWORD,EMAIL from TABLE3",
						new Table3Mapper());
		return pb;
	}

	public void insert() {
		getJdbcTemplate().update(
				"INSERT INTO TABLE3(NAME,PASSWORD,EMAIL) VALUES(?,	?, ?)",
				new Object[] { "Pandy-Jdbc", "Pandy-PASSWORD", "Pandy-EMAIL" },
				new int[] { Types.VARCHAR, Types.VARCHAR, Types.VARCHAR });
	}

	public void update(String name) {
		getJdbcTemplate()
				.update("UPDATE TABLE3 SET NAME = ?,PASSWORD = ?,EMAIL = ? WHERE NAME = ?",
						new Object[] { "Pandy-UPDATE", "Pandy-UPDATE",
								"Pandy-UPDATE", name },
						new int[] { Types.VARCHAR, Types.VARCHAR,
								Types.VARCHAR, Types.VARCHAR });
	}

	public void delete(String name) {
		getJdbcTemplate().update("DELETE FROM dbo.TABLE3 WHERE NAME = ?",
				new Object[] { name }, new int[] { Types.VARCHAR });
	}

	public Object inserOneActor() {

		SimpleJdbcInsert inserActor = new SimpleJdbcInsert(getDataSource())
				.withTableName("TABLE3").usingColumns("NAME", "PASSWORD")// 插入这些字段
				.usingGeneratedKeyColumns("ID");// 带回生成的id

		Map<String, Object> parameters = new HashMap<String, Object>();
		parameters.put("NAME", "NAME");
		parameters.put("PASSWORD", "PASSWORD");
		parameters.put("EMAIL", "EMAIL");
		return inserActor.executeAndReturnKey(parameters).longValue();
	}

	@SuppressWarnings({ "rawtypes", "unchecked" })
	public Table3 findByName(String name) {
		String sql = "select ID,NAME,PASSWORD,EMAIL from TABLE3 where NAME = :NAME";
		NamedParameterJdbcTemplate simpleJdbcTemplate = new NamedParameterJdbcTemplate(
				getDataSource());
		Map paramMap = new HashMap();
		paramMap.put("NAME", name);

		final Table3 table3 = new Table3();
		simpleJdbcTemplate.query(sql, paramMap, new RowCallbackHandler() {
			@Override
			public void processRow(ResultSet rs) throws SQLException {
				table3.setId(rs.getInt("ID"));
				table3.setName(rs.getString("NAME"));
				table3.setPassword(rs.getString("PASSWORD"));
				table3.setEmail(rs.getString("EMAIL"));
			}
		});

		return table3;

	}
}