jdbcTemplate模板和JdbcDaoSupport工具

       Spring提供了基于Jdbc的jdbcTemplate模板和JdbcDaoSupport工具，可以非常方便的对数据库进行操作，并且很容易集成到项目中。一般会和HibernateTemplate模板配合使用，虽然HibernateTemplate非常强大，但是因为有时使用基于Jdbc的数据操做要比HibernateTemplate性能高，比如 批量处理，还有由于业务原因往往我们更倾向于写最原始的sql语句。废话不说了看代码。

1.项目整体结构图

2.配置文件

1> 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/xsd/maven-4.0.0.xsd">  <modelVersion>4.0.0</modelVersion>  <groupId>hys.web.app</groupId>  <artifactId>hys_demo_springJdbcDao</artifactId>  <version>0.0.1-SNAPSHOT</version>    <packaging>jar</packaging>  <name>hys_demo_springJdbcDao</name>  <url>http://maven.apache.org</url>    <properties>    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>  </properties>    <dependencies>  <dependency>    <groupId>junit</groupId>      <artifactId>junit</artifactId>      <version>4.10</version>      <scope>test</scope>    </dependency>        <!-- 数据源 --><dependency>    <groupId>commons-dbcp</groupId>    <artifactId>commons-dbcp</artifactId>    <version>1.4</version></dependency><dependency>   <groupId>mysql</groupId>   <artifactId>mysql-connector-java</artifactId>   <version>5.0.8</version></dependency>    <!-- Spring-4.2.0 -->    <dependency>    <groupId>org.springframework</groupId>    <artifactId>spring-web</artifactId>    <version>4.2.0.RELEASE</version></dependency><dependency>        <groupId>org.springframework</groupId>    <artifactId>spring-orm</artifactId>    <version>4.2.0.RELEASE</version>    </dependency>        <dependency>    <groupId>org.springframework</groupId>    <artifactId>spring-expression</artifactId>    <version>4.2.0.RELEASE</version></dependency>  </dependencies>  </project>

2> applicationContext.xml

<?xml version="1.0" encoding="UTF-8"?><beans xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.springframework.org/schema/beans"   xmlns:aop="http://www.springframework.org/schema/aop" xmlns:context="http://www.springframework.org/schema/context"   xmlns:tx="http://www.springframework.org/schema/tx" xmlns:cache="http://www.springframework.org/schema/cache"   xmlns:p="http://www.springframework.org/schema/p" xsi:schemaLocation="http://www.springframework.org/schema/beans    http://www.springframework.org/schema/beans/spring-beans.xsd     http://www.springframework.org/schema/aop     http://www.springframework.org/schema/aop/spring-aop.xsd     http://www.springframework.org/schema/context     http://www.springframework.org/schema/context/spring-context.xsd     http://www.springframework.org/schema/tx     http://www.springframework.org/schema/tx/spring-tx.xsd     http://www.springframework.org/schema/cache      http://www.springframework.org/schema/cache/spring-cache.xsd">      <!-- 自动扫描 -->    <context:component-scan base-package="com.hys.demo.**.service,com.hys.demo.**.dao,com.hys.demo.**.action" />        <!-- 引入外置文件 -->    <bean id="propertyConfigurer" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">        <property name="location" value="classpath:jdbc.properties" />    </bean><!--数据库连接池配置-->    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">       <property name="driverClassName" value="${jdbc.driverClassName}" />    <property name="url" value="${jdbc.url}" />    <property name="username" value="${jdbc.username}" />    <property name="password" value="${jdbc.password}" />    </bean>    <!-- Spring提供的JdbcDaoSupport工具 -->  <bean id="jdbcDao" class="com.hys.demo.student.dao.JdbcSupportDao">  <property name="dataSource" ref="dataSource" />  </bean>    <!-- Spring提供的jdbcTemplate模板 -->  <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"><property name="dataSource" ref="dataSource"></property>  </bean>  </beans>

3> jdbc.properties

jdbc.driverClassName=com.mysql.jdbc.Driverjdbc.url=jdbc:mysql://localhost:3306/demo?characterEncoding=UTF-8jdbc.username=rootjdbc.password=root

2.源码奉上
JdbcSupportDao.java

package com.hys.demo.student.dao;import java.sql.Connection;import java.util.List;import java.util.Map;import org.springframework.jdbc.core.RowMapper;import org.springframework.jdbc.core.support.JdbcDaoSupport;public class JdbcSupportDao extends JdbcDaoSupport {/** * spring提供JdbcDaoSupport工具 * Object... args：args是个数组，此参数可传也可不传 */public void update(String sql){super.getJdbcTemplate().update(sql);}//update t_sys_user set name = ?, age = ? where id = ?;public void update(String sql, Object... args){super.getJdbcTemplate().update(sql, args);}//update t_sys_user set name = ?, age = ?//argTypes：指定args参数的类型，例如： Types.DATE, Types.VARCHARpublic void update(String sql, Object[] args, int[] argTypes){super.getJdbcTemplate().update(sql, args, argTypes);}//批量处理//update t_sys_user set name = ?, age = ? where id = ?;public void batchUpdate(String sql){super.getJdbcTemplate().batchUpdate(sql);}//insert into t_sys_user (id, name, sex, age) values (?, ?, ?, ?)public void batchUpdate(String sql, List<Object[]> batchArgs){super.getJdbcTemplate().batchUpdate(sql, batchArgs);}//insert into t_sys_user (id, name, sex, age) values (?, ?, ?, ?)//argTypes：指定batchArgs参数的类型，例如： Types.DATE, Types.VARCHARpublic void batchUpdate(String sql, List<Object[]> batchArgs, int[] argTypes){super.getJdbcTemplate().batchUpdate(sql, batchArgs, argTypes);}//返回list集合，list里存放的是实体对象，不是map集合//select * from t_sys_user where name = ""public <T> List<T> queryForList(String sql, Class<T> elementType){return super.getJdbcTemplate().queryForList(sql, elementType);}//select * from t_sys_user where name = ?;public <T> List<T> queryForList(String sql, Object[] args, Class<T> elementType){return super.getJdbcTemplate().queryForList(sql, args, elementType);}//select * from t_sys_user where name = ?;public <T> List<T> queryForList(String sql, Class<T> elementType, Object... args){return super.getJdbcTemplate().queryForList(sql, elementType, args);}//select * from t_sys_user where name = ?;//argTypes：指定args参数的类型，例如： Types.DATE, Types.VARCHARpublic <T> List<T> queryForList(String sql, Object[] args, int[] argTypes, Class<T> elementType){return super.getJdbcTemplate().queryForList(sql, args, argTypes, elementType);}//返回list集合，list里存放的是map集合，不是实体对象//select * from t_sys_user where name = ""public List<Map<String, Object>> queryForList(String sql){return super.getJdbcTemplate().queryForList(sql);}//select * from t_sys_user where name = ?public List<Map<String, Object>> queryForList(String sql, Object... args){return super.getJdbcTemplate().queryForList(sql, args);}//select * from t_sys_user where name = ?//argTypes：指定args参数的类型，例如： Types.DATE, Types.VARCHARpublic List<Map<String, Object>> queryForList(String sql, Object[] args, int[] argTypes){return super.getJdbcTemplate().queryForList(sql, args, argTypes);}//返回map集合，不是实体对象//select * from t_sys_user where id = "";public Map<String, Object> queryMap(String sql){return super.getJdbcTemplate().queryForMap(sql);}//select * from t_sys_user where id = ?;public Map<String, Object> queryForMap(String sql, Object... args){return super.getJdbcTemplate().queryForMap(sql, args);}//select * from t_sys_user where id = ?;//argTypes：指定传入参数的类型，例如： Types.DATE, Types.VARCHARpublic Map<String, Object> queryMap(String sql, Object[] args, int[] argTypes){return super.getJdbcTemplate().queryForMap(sql, args, argTypes);}//返回基本数据类型，如：String、Integer、Long//select count(id) from t_sys_user sex = ''public <T> T queryObject(String sql, Class<T> requiredType){return super.getJdbcTemplate().queryForObject(sql, requiredType);}//select count(id) from t_sys_user sex = ?public <T> T queryForObject(String sql, Class<T> requiredType, Object... args){return super.getJdbcTemplate().queryForObject(sql, requiredType, args);}//select count(id) from t_sys_user sex = ?public <T> T queryObject(String sql, Object[] args, Class<T> requiredType){return super.getJdbcTemplate().queryForObject(sql, args, requiredType);}//select count(id) from t_sys_user sex = ?//argTypes：指定args参数的类型，例如： Types.DATE, Types.VARCHARpublic <T> T queryObject(String sql, Object[] args, int[] argTypes, Class<T> requiredType){return super.getJdbcTemplate().queryForObject(sql, args, argTypes, requiredType);}//返回实体对象,不是map集合//select * from t_sys_user where loginname = '' //RowMapper的实现类是：new BeanPropertyRowMapper<User>(User.class)public <T> T queryObject(String sql, RowMapper<T> rowMapper){return super.getJdbcTemplate().queryForObject(sql, rowMapper);}//select * from t_sys_user where loginname = ?public <T> T queryObject(String sql, Object[] args, RowMapper<T> rowMapper){return super.getJdbcTemplate().queryForObject(sql, args, rowMapper);}//select * from t_sys_user where loginname = ?public <T> T queryForObject(String sql, RowMapper<T> rowMapper, Object... args){return super.getJdbcTemplate().queryForObject(sql, rowMapper, args);}//select * from t_sys_user where loginname = ?//argTypes：指定args参数的类型，例如： Types.DATE, Types.VARCHARpublic <T> T queryForObject(String sql, Object[] args, int[] argTypes, RowMapper<T> rowMapper){return super.getJdbcTemplate().queryForObject(sql, args, argTypes, rowMapper);}//创建一个Statement对象来将SQL语句发送到数据库//创建一个PreparedStatement对象来将参数化的 SQL 语句发送到数据库public Connection openConnection(){Connection connection = super.getConnection();return connection;}}

JdbcTemplateDao.java

package com.hys.demo.student.dao;import java.sql.Connection;import java.sql.SQLException;import java.util.List;import java.util.Map;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.jdbc.core.JdbcTemplate;import org.springframework.jdbc.core.RowMapper;import org.springframework.stereotype.Repository;/** * Spring JdbcTemplate模板  * Object... args：args是个数组，此参数可传也可不传 * @author hys */@Repositorypublic class JdbcTemplateDao {@Autowiredprivate JdbcTemplate jdbcTemplate;public void update(String sql){jdbcTemplate.update(sql);}//update t_sys_user set name = ?, age = ?public void update(String sql, Object... args){jdbcTemplate.update(sql, args);}//update t_sys_user set name = ?, age = ?//argTypes：指定args参数的类型，例如： Types.DATE, Types.VARCHARpublic void update(String sql, Object[] args, int[] argTypes){jdbcTemplate.update(sql, args, argTypes);}//批量处理//update t_sys_user set name = ?, age = ? where id = ?;public void batchUpdate(String sql){jdbcTemplate.batchUpdate(sql);}//insert into t_sys_user (id, name, sex, age) values (?, ?, ?, ?)public void batchUpdate(String sql, List<Object[]> batchArgs){jdbcTemplate.batchUpdate(sql, batchArgs);}//insert into t_sys_user (id, name, sex, age) values (?, ?, ?, ?)//argTypes：指定batchArgs参数的类型，例如： Types.DATE, Types.VARCHARpublic void batchUpdate(String sql, List<Object[]> batchArgs, int[] argTypes){jdbcTemplate.batchUpdate(sql, batchArgs, argTypes);}//返回list集合，list里存放的是实体对象，不是map集合//select * from t_sys_user where name = ""public <T> List<T> queryForList(String sql, Class<T> elementType){return jdbcTemplate.queryForList(sql, elementType);}//select * from t_sys_user where name = ?;public <T> List<T> queryForList(String sql, Object[] args, Class<T> elementType){return jdbcTemplate.queryForList(sql, args, elementType);}//select * from t_sys_user where name = ?;public <T> List<T> queryForList(String sql, Class<T> elementType, Object... args){return jdbcTemplate.queryForList(sql, elementType, args);}//select * from t_sys_user where name = ?;//argTypes：指定args参数的类型，例如： Types.DATE, Types.VARCHARpublic <T> List<T> queryForList(String sql, Object[] args, int[] argTypes, Class<T> elementType){return jdbcTemplate.queryForList(sql, args, argTypes, elementType);}//返回list集合，list里存放的是map集合，不是实体对象//select * from t_sys_user where name = ""public List<Map<String, Object>> queryForList(String sql){return jdbcTemplate.queryForList(sql);}//select * from t_sys_user where name = ?public List<Map<String, Object>> queryForList(String sql, Object... args){return jdbcTemplate.queryForList(sql, args);}//select * from t_sys_user where name = ?//argTypes：指定args参数的类型，例如： Types.DATE, Types.VARCHARpublic List<Map<String, Object>> queryForList(String sql, Object[] args, int[] argTypes){return jdbcTemplate.queryForList(sql, args, argTypes);}//返回map集合，不是实体对象//select * from t_sys_user where id = "";public Map<String, Object> queryMap(String sql){return jdbcTemplate.queryForMap(sql);}//select * from t_sys_user where id = ?;public Map<String, Object> queryForMap(String sql, Object... args){return jdbcTemplate.queryForMap(sql, args);}//select * from t_sys_user where id = ?;//argTypes：指定传入参数的类型，例如： Types.DATE, Types.VARCHARpublic Map<String, Object> queryMap(String sql, Object[] args, int[] argTypes){return jdbcTemplate.queryForMap(sql, args, argTypes);}//返回基本数据类型，如：String、Integer、Long//select count(id) from t_sys_user sex = ''public <T> T queryObject(String sql, Class<T> requiredType){return jdbcTemplate.queryForObject(sql, requiredType);}//select count(id) from t_sys_user sex = ?public <T> T queryForObject(String sql, Class<T> requiredType, Object... args){return jdbcTemplate.queryForObject(sql, requiredType, args);}//select count(id) from t_sys_user sex = ?public <T> T queryObject(String sql, Object[] args, Class<T> requiredType){return jdbcTemplate.queryForObject(sql, args, requiredType);}//select count(id) from t_sys_user sex = ?//argTypes：指定args参数的类型，例如： Types.DATE, Types.VARCHARpublic <T> T queryObject(String sql, Object[] args, int[] argTypes, Class<T> requiredType){return jdbcTemplate.queryForObject(sql, args, argTypes, requiredType);}//返回实体对象,不是map集合//select * from t_sys_user where loginname = '' //RowMapper的实现类是：new BeanPropertyRowMapper<User>(User.class)public <T> T queryObject(String sql, RowMapper<T> rowMapper){return jdbcTemplate.queryForObject(sql, rowMapper);}//select * from t_sys_user where loginname = ?public <T> T queryObject(String sql, Object[] args, RowMapper<T> rowMapper){return jdbcTemplate.queryForObject(sql, args, rowMapper);}//select * from t_sys_user where loginname = ?public <T> T queryForObject(String sql, RowMapper<T> rowMapper, Object... args){return jdbcTemplate.queryForObject(sql, rowMapper, args);}//select * from t_sys_user where loginname = ?//argTypes：指定args参数的类型，例如： Types.DATE, Types.VARCHARpublic <T> T queryForObject(String sql, Object[] args, int[] argTypes, RowMapper<T> rowMapper){return jdbcTemplate.queryForObject(sql, args, argTypes, rowMapper);}//创建一个Statement对象来将SQL语句发送到数据库//创建一个PreparedStatement对象来将参数化的 SQL 语句发送到数据库public Connection openConnection(){Connection connection = null;try {connection = jdbcTemplate.getDataSource().getConnection();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}return connection;}}

Student.java

package com.hys.demo.student.entity;public class Student {private String id;private String name;private String sex;private int age;public Student() {}public Student(String id, String name, String sex, int age) {this.id = id;this.name = name;this.sex = sex;this.age = age;}public String getId() {return id;}public void setId(String id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public String getSex() {return sex;}public void setSex(String sex) {this.sex = sex;}public int getAge() {return age;}public void setAge(int age) {this.age = age;}@Overridepublic String toString() {return "Student [id=" + id + ", name=" + name + ", sex=" + sex+ ", age=" + age + "]";}}

StudentService.java

package com.hys.demo.student.service;import java.util.Map;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Service;import com.hys.demo.student.dao.JdbcSupportDao;import com.hys.demo.student.dao.JdbcTemplateDao;import com.hys.demo.student.entity.Student;@Servicepublic class StudentService {@Autowiredprivate JdbcSupportDao jdbcSupportDao;@Autowiredprivate JdbcTemplateDao jdbcTemplateDao;public void save(Student student){String sql = "insert into t_app_student(id,name,sex,age) values('"+student.getId()+"','"+student.getName()+"','"+student.getSex()+"',"+student.getAge()+")";jdbcSupportDao.update(sql);}public Map<String, Object> getStudent(String id){String sql = "select * from t_app_student where id = '"+id+"'";return jdbcTemplateDao.queryMap(sql);}}

StudentAction.java

package com.hys.demo.student.action;import java.util.Map;import org.junit.Before;import org.junit.Test;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.context.ApplicationContext;import org.springframework.context.support.ClassPathXmlApplicationContext;import org.springframework.stereotype.Controller;import com.hys.demo.student.service.StudentService;/** * 因为当前studentAction对象不是Spring创建的 * 所以studentService为空，因此不能直接使用studentService * @author hys * */@Controllerpublic class StudentAction {@Autowiredprivate StudentService studentService;private ApplicationContext context;@Beforepublic void getContext(){//获取上下文对象this.context = new ClassPathXmlApplicationContext("applicationContext.xml");}@Testpublic void test_dao(){StudentAction studentAction = (StudentAction)context.getBean("studentAction");//studentAction.studentService.save(new Student("012","小明","男",20));Map<String, Object> map = studentAction.studentService.getStudent("001");System.out.println(map);}}

3.建表

CREATE TABLE `t_app_student` (  `id` varchar(32) NOT NULL,  `name` varchar(10) DEFAULT NULL,  `sex` varchar(2) DEFAULT NULL,  `age` int(5) DEFAULT NULL,  PRIMARY KEY (`id`))

         到此整个项目就完了，简单吧。看代码可发现jdbcTemplate和JdbcDaoSupport的方法一样，那是因为JdbcDaoSupport就是基于jdbcTemplate开发的。当然了还有其它方法，有兴趣了可以自己研究研究。