spring jdbcTemplate使用
关于java.lang.IllegalArgumentException: ‘dataSource’ or ‘jdbcTemplate’ is required 类似问题的解决总结:http://www.cnblogs.com/MoShin/archive/2011/10/10/2204020.html
参考:
spring jdbcTemplate使用http://log-cd.iteye.com/blog/215059
Spring JdbcTemplate 与 事务管理 学习 http://www.iteye.com/topic/480432
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 详解 : http://blog.csdn.net/dyllove98/article/details/7772470
pom.xml
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
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
|
1 2 3 4 5 |
Java代码 jdbc.driver=net.sourceforge.jtds.jdbc.Driver jdbc.url=jdbc:jtds:sqlserver://localhost:1433;DatabaseName=Test jdbc.username=sa jdbc.password=sa |
application.xml
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
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
————————————–
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
Java代码 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 + "]"; } } |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
Java代码 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
——————————-
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
Java代码 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); } } |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 |
Java代码 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; } } |
