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

时间：2021-07-25 08:40:22

关于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));

    }

}

相关文章