spring使用JdbcTemplate和jdbcDaosupport及具名参数使用

关于jdbctemplate:

个人感觉比Java链接mysql那一套方便好维护多了，只需在配置文件维护即可

需要的包：

com.springsource.net.sf.cglib-2.2.0.jar
com.springsource.org.aopalliance-1.0.0.jar
com.springsource.org.aspectj.weaver-1.6.8.RELEASE.jar
commons-logging-1.1.3.jar
mysql-connector-java-5.1.7-bin.jar
spring-aop-4.0.0.RELEASE.jar
spring-aspects-4.0.0.RELEASE.jar
spring-beans-4.0.0.RELEASE.jar
spring-context-4.0.0.RELEASE.jar
spring-core-4.0.0.RELEASE.jar
spring-expression-4.0.0.RELEASE.jar
spring-jdbc-4.0.0.RELEASE.jar
spring-orm-4.0.0.RELEASE.jar
spring-tx-4.0.0.RELEASE.jar
spring-web-4.0.0.RELEASE.jar
spring-webmvc-4.0.0.RELEASE.jar

具体步骤：

配置外部资源文件（db.properties）

配置mysal数据源

配置jdbctemplate

bean.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:jdbc="http://www.springframework.org/schema/jdbc"
    xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.1.xsd
        http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-4.1.xsd">
    <!-- 装配自导扫描的包 -->
    <context:component-scan base-package="com.spring.bean.jdbc"></context:component-scan>
    <!-- 加载外部资源文件 -->
    <context:property-placeholder location="classpath:db.properties"/>
    <!-- 配置MySQL数据源  -->
    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="${db.driverClassName}"></property>
        <property name="url" value="${db.url}"></property>
        <property name="username" value="${db.username}"></property>
        <property name="password" value="${db.password}"></property> 
    </bean>
    <!--配置jdbcTemplate模板  -->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource">
            <ref bean="dataSource"/>
        </property>
    </bean>
    <!-- 配置namedParameterJdbcTemplate ，具名参数 -->
    <bean id="namedParameterJdbcTemplate" class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">
        <constructor-arg ref="dataSource"></constructor-arg>
    </bean>
    
</beans>

外部资源文件

db.driverClassName=com.mysql.jdbc.Driver
db.url=jdbc:mysql://localhost:3306/students
db.username=root
db.password=root

实体students

package com.spring.bean.jdbc;

/**
 * 学生实体类
 * @author Administrator
 *
 */
public class Students {

    private Integer Id;
    private String name;
    private String sex;
    private int age;
    private String tel;
    public Integer getId() {
        return Id;
    }
    public void setId(Integer id) {
        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;
    }
    public String getTel() {
        return tel;
    }
    public void setTel(String tel) {
        this.tel = tel;
    }
    
    @Override
    public String toString() {
        return "Students [Id=" + Id + ", name=" + name + ", sex=" + sex
                + ", age=" + age + ", tel=" + tel + "]";
    }
    
}

实体course

package com.spring.bean.jdbc;

public class Course {

    private Integer id;
    private String coursename;
    private String coursenameid;
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getCoursename() {
        return coursename;
    }
    public void setCoursename(String coursename) {
        this.coursename = coursename;
    }
    public String getCoursenameid() {
        return coursenameid;
    }
    public void setCoursenameid(String coursenameid) {
        this.coursenameid = coursenameid;
    }
    @Override
    public String toString() {
        return "Course [id=" + id + ", coursename=" + coursename
                + ", coursenameid=" + coursenameid + "]";
    }
}

jdbctemplate

package com.spring.bean.jdbc;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
@Repository
public class StudentsDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    public Students getStudent(String sql, Integer id) {
        RowMapper<Students> rowMapper = new BeanPropertyRowMapper<Students>(Students.class);
        Students students = jdbcTemplate.queryForObject(sql, rowMapper, id);
        return students;
    }
}

jdbcDaosupport

package com.spring.bean.jdbc;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.stereotype.Repository;
@Repository
public class CourseDao extends JdbcDaoSupport {
    

    /**此处必须加入dataSource或jdbcTemplate要么报错如下
     * Invocation of init method failed; nested exception is java.lang.IllegalArgumentException: 'dataSource' or 'jdbcTemplate' is required
     * 如不加jdbcTemplate。要用dataSource
     * name只能重新加入dataSource，为什么我用了setDataSource22为DataSource赋值
     * 因在JdbcDaoSupport类中为final关键字修饰，不可重写
     * @param dataSource
     */
    @Autowired
    public void  setDataSource22(DataSource dataSource) {
        setDataSource(dataSource);
    }
    
    /**
     * 获取课程
     * @param sql
     * @param id
     * @return
     */
    public Course getCourse(String sql,int id){
        RowMapper<Course> rowMapper = new BeanPropertyRowMapper<Course>(Course.class);
        Course course = getJdbcTemplate().queryForObject(sql, rowMapper,id);
        return course;
    }
}

测试类具体见注释

package com.spring.bean.jdbc;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;

public class MainTest {
    
    private ApplicationContext ctx=null;
    private StudentsDao studentsDao;
    private JdbcTemplate jdbcTemplate;
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
    private CourseDao courseDao;
    
    {
        ctx=new ClassPathXmlApplicationContext("bean.xml");
        jdbcTemplate=(JdbcTemplate) ctx.getBean("jdbcTemplate");
        studentsDao=(StudentsDao) ctx.getBean("studentsDao");
        namedParameterJdbcTemplate=(NamedParameterJdbcTemplate)ctx.getBean("namedParameterJdbcTemplate");
        courseDao=(CourseDao) ctx.getBean("courseDao");
    }
    

  　/**
　　　*更新某一条操作 
 　　*/
    @Test
    public void getCourse(){
        String sql="SELECT * from course WHERE id=?";
        Course course = courseDao.getCourse(sql,1);
        System.out.println(course);
    }
    
    /**
     * 使用具名参数操作数据库(可以为字段起名)
     * 优点就是：当字段较多，不用考虑字段的顺序，效率高，即key,value的形式
     * 缺点：麻烦代码量多
     */
    @Test
    public void testnamedParameterJdbcTemplate(){
        String sql="INSERT INTO students ( name, sex, age, tel) VALUES (:n, :s, :a, :t)";
        Map<String, Object> paramMap=new HashMap<String,Object >();
        paramMap.put("n", "rongrong");
        paramMap.put("s", "man");
        paramMap.put("a", "25");
        paramMap.put("t", "18612396984");
        namedParameterJdbcTemplate.update(sql, paramMap);
    }
    
    
    /**
     * 使用具名参数操作数据库
     * 使用update(String sql, SqlParameterSource paramSource)方法传入实体参数
     * 要求：sql的字段名称与对象属性名称一致
     * SqlParameterSource paramSource
     */
    @Test
    public void testnamedParameterJdbc(){
        String sql="INSERT INTO course (coursename, coursenameid) VALUES (:coursename, :coursenameid);";
        Course course = new Course();
        course.setCoursenameid("As-1001");
        course.setCoursename("java");
        SqlParameterSource paramSource = new BeanPropertySqlParameterSource(course);
        namedParameterJdbcTemplate.update(sql, paramSource);
    }
    
    
    /**
     * 修改数据库某条属性
     */
    @Test
    public void testUpdate(){
        String sql="UPDATE students SET `name`=? WHERE id=? ";
        jdbcTemplate.update(sql, "lucy",1);
    }
    
    /**
     * 批量更新数据
     * List<Object[]>此处为对象数组
     * 必须为对象数组，插入一条数据如：{"MT-1","man",17,"13706063546"}
     * 如为多条数据，那么必须为对象数组
     */
    @Test
    public void testBatchUpdate(){
        String sql="INSERT INTO students ( name, sex, age, tel) VALUES (?, ?, ?, ?)";
        List<Object[]> batchArgs=new ArrayList<Object[]>();
        batchArgs.add(new Object[]{"MT-1","man",17,"13706063546"});
        batchArgs.add(new Object[]{"MT-2","man",18,"13706063547"});
        batchArgs.add(new Object[]{"MT-3","man",19,"13706063548"});
        batchArgs.add(new Object[]{"MT-4","man",20,"13706063549"});
        jdbcTemplate.batchUpdate(sql, batchArgs);
    }
    
    /**
     * 查询返回一个实体对象集合
     * 注意调用的不是 queryForList 方法
     */
    @Test
    public void testQueryForList(){
        String sql="SELECT id,name, sex, age, tel FROM students";
        RowMapper<Students> rowMapper = new BeanPropertyRowMapper<Students>(Students.class);
        List<Students> students = jdbcTemplate.query(sql, rowMapper);
//        不是 queryForList 方法
//        List<Students> students = jdbcTemplate.queryForList(sql, Students.class);
        System.out.println(students);
    }
    
    /**
     * 从数据库中获取一条记录, 实际得到对应的一个对象
     * 注意不是调用 queryForObject(String sql, Class<T> requiredType, Object... args) 方法!
     * 而需要调用 queryForObject(String sql, RowMapper<T> rowMapper, Object... args) 
     * 1. 其中的 RowMapper 指定如何去映射结果集的行, 常用的实现类为 BeanPropertyRowMapper
     * 2. 使用 SQL 中列的别名完成列名和类的属性名的映射. 例如 name, sex, age, tel 
     * 3. 不支持级联属性. JdbcTemplate 到底是一个 JDBC 的小工具, 而不是 ORM 框架
     */
    @Test
    public void testQueryForObject(){
        String sql="SELECT id,name, sex, age, tel FROM students WHERE id=?";
        RowMapper<Students> rowMapper = new BeanPropertyRowMapper<Students>(Students.class);
        Students students = jdbcTemplate.queryForObject(sql, rowMapper,1);
        System.out.println(students);
    }
    
    /**
     * 获取单个列的值, 或做统计查询
     * 使用 queryForObject(String sql, Class<Long> requiredType) 
     *     其中：requiredType为Integer.class可以达到统计查询的效果
     * 使用 int queryForInt(String sql) throws DataAccessException，可实现
     */
    @Test
    public void testQueryForINt(){
        String sql="SELECT count(id) FROM students ";
//        RowMapper<Students> rowMapper = new BeanPropertyRowMapper<Students>(Students.class);
        int long1 = jdbcTemplate.queryForInt(sql);
//        Integer long1 = jdbcTemplate.queryForObject(sql, Integer.class);
//        不是 queryForList 方法
//        List<Students> students = jdbcTemplate.queryForList(sql, Students.class);
        System.out.println(long1);
    }
    
    /**
     * 根据用户id，返回用户对象
     */
    @Test
    public void getStudents(){
        String sql="SELECT id,name, sex, age, tel FROM students WHERE id=?";
        System.out.println(studentsDao.getStudent(sql, 4));
    }
    
    
}