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

<?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="url"="${db.url}"="username"="${db.username}"="password"="${db.password}"> 
    </bean配置jdbcTemplate模板  ="jdbcTemplate"="org.springframework.jdbc.core.JdbcTemplate"="dataSource">
            ref />
         配置namedParameterJdbcTemplate ，具名参数 ="namedParameterJdbcTemplate"="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate"constructor-arg refconstructor-arg>
    
beans>

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

package com.spring.bean.jdbc;

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

    private Integer Id;
     String name;
     String sex;
    private int age;
     String tel;
    public Integer getId() {
        return Id;
    }
    void setId(Integer id) {
        Id = id;
    }
     String getName() {
         name;
    }
     setName(String name) {
        this.name = String getSex() {
         sex;
    }
     setSex(String sex) {
        this.sex = getAge() {
         age;
    }
    void setAge( age) {
        this.age = String getTel() {
         tel;
    }
     setTel(String tel) {
        this.tel = tel;
    }
    
    @Override
     String toString() {
        return "Students [Id=" + Id + ",name=" + name + ",sex=" + sex
                + ",age=" + age + ",tel=" + tel + "]";
    }
    
}

 Course {

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

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

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

 javax.sql.DataSource;

 org.springframework.jdbc.core.support.JdbcDaoSupport;
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
      setDataSource22(DataSource dataSource) {
        setDataSource(dataSource);
    }
    
    
     * 获取课程
     *  sql
     *  id
     * @return
     */
    public Course getCourse(String sql, id){
        RowMapper<Course> rowMapper = new BeanPropertyRowMapper<Course>(Course.);
        Course course = getJdbcTemplate().queryForObject(sql,1)"> course;
    }
}

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

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

 MainTest {
    
    private ApplicationContext ctx=null;
     StudentsDao studentsDao;
     JdbcTemplate jdbcTemplate;
     NamedParameterJdbcTemplate namedParameterJdbcTemplate;
     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
     getCourse(){
        String sql="SELECT * from course WHERE id=?";
        Course course = courseDao.getCourse(sql,1);
        System.out.println(course);
    }
    
    
     * 使用具名参数操作数据库(可以为字段起名)
     * 优点就是：当字段较多，不用考虑字段的顺序，效率高，即key,value的形式
     * 缺点：麻烦代码量多
     
    @Test
     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
      testnamedParameterJdbc(){
        String sql="INSERT INTO course (coursename,coursenameid) VALUES (:coursename,:coursenameid);";
        Course course = new Course();
        course.setCoursenameid("As-1001");
        course.setCoursename("java");
        SqlParameterSource paramSource =  BeanPropertySqlParameterSource(course);
        namedParameterJdbcTemplate.update(sql,paramSource);
    }
    
    
    
     * 修改数据库某条属性
      testUpdate(){
        String sql="UPDATE students SET `name`=? WHERE id=? ";
        jdbcTemplate.update(sql,"lucy",1)">);
    }
    
    
     * 批量更新数据
     * List<Object[]>此处为对象数组
     * 必须为对象数组，插入一条数据如：{"MT-1","man",17,"13706063546"}
     * 如为多条数据，那么必须为对象数组
      testBatchUpdate(){
        String sql="INSERT INTO students ( name,tel) VALUES (?,?,?)";
        List<Object[]> batchArgs=new ArrayList<Object[]>();
        batchArgs.add(new Object[]{"MT-1","13706063546"});
        batchArgs.add(new Object[]{"MT-2",18,"13706063547"new Object[]{"MT-3",19,"13706063548"new Object[]{"MT-4",20,"13706063549"});
        jdbcTemplate.batchUpdate(sql,batchArgs);
    }
    
    
     * 查询返回一个实体对象集合
     * 注意调用的不是 queryForList 方法
      testQueryForList(){
        String sql="SELECT id,name,tel FROM students";
        RowMapper<Students> rowMapper = );
        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,tel 
     * 3. 不支持级联属性. JdbcTemplate 到底是一个 JDBC 的小工具,而不是 ORM 框架
      testQueryForObject(){
        String sql="SELECT id,tel FROM students WHERE id=?");
        Students students = jdbcTemplate.queryForObject(sql,1)">);
        System.out.println(students);
    }
    
    
     * 获取单个列的值,或做统计查询
     * 使用 queryForObject(String sql,Class<Long> requiredType) 
     *     其中：requiredType为Integer.class可以达到统计查询的效果
     * 使用 int queryForInt(String sql) throws DataAccessException，可实现
      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);
        System.out.println(long1);
    }
    
    
     * 根据用户id，返回用户对象
      getStudents(){
        String sql="SELECT id,1)">;
        System.out.println(studentsDao.getStudent(sql,4));
    }
    
    
}