Spring JDBCTemplate学习笔记

为了简化持久化操作，Spring在JDBC API之上提供了JDBC Template组件，JDBC Template提供统一的模板方法，在保留代码灵活性的基础上，尽量减少持久化代码。

 下面介绍JDBC Template基本使用

新建student表与实体类字段一样

—execute方法：一般用来操作ddl语句

@Test
public void demo1(){ //execute方法，一般用来操作DDL语句
    String sql = "create table customer (id int,name varchar(20))";
    jdbcTemplate.execute(sql);
}

—update方法

 @Test
 public void demo2(){ //update方法,对数据库进行增删改操作
     String sql = "insert into student(name,sex) values (?,?)";
     jdbcTemplate.update(sql,"赵信","男");
 }
 @Test
 public void demo3(){ //update方法,对数据库进行增删改操作
     String sql = "update student set sex = ? where id = ?";
     jdbcTemplate.update(sql,new Object[]{"女",2});
 }

—batchUpdate方法

@Test
public void demo4(){ //batchUpdate方法,批量增删改操作
    String[] sqls = {"insert into student (name,sex) values ('发条魔灵','女')" ,
                     "insert into student (name,sex) values ('辛德拉','女')" ,
                     "update student set sex = '男',name = 'EZ' where id = 2"};
    jdbcTemplate.batchUpdate(sqls);
}
@Test
public void demo5(){ //batchUpdate方法,批量增删改操作
    String sql = "insert into selection (student,course) values(?,?)";
    List<Object[]> list = new ArrayList<Object[]>();
    list.add(new Object[]{1,1001});
    list.add(new Object[]{2,1001});
    list.add(new Object[]{2,1002});
    list.add(new Object[]{2,1003});
    jdbcTemplate.batchUpdate(sql,list);
}

—query方法：返回Map和实体类这两种使用的比较多

@Test
public void demo6(){ //查询简单数据项，获取一个
    String sql = "select name from student where id = ?";
    String name = jdbcTemplate.queryForObject(sql,String.class,1);
    System.out.println("姓名："+name);
}
@Test
public void demo7(){ //查询简单数据项，获取多个
    String sql = "select name from student where sex = ?";
    List<String> names = jdbcTemplate.queryForList(sql,String.class,"女");
    System.out.println(names);
}
@Test
public void demo8(){ //查询复杂对象(封装为Map) 获取一个
    String sql = "select * from student where id = ?";
    Map<String,Object> stu = jdbcTemplate.queryForMap(sql,2);
    System.out.println(stu);
}
@Test
public void demo9(){ //查询复杂对象(封装为Map) 获取多个
    String sql = "select * from student";
    List<Map<String,Object>> stus = jdbcTemplate.queryForList(sql);
    System.out.println(stus);
}
@Test
public void demo10(){ //查询复杂对象(封装为实体对象) 获取一个 需使用RowMapper接口
    String sql = "select * from student where id = ?";
    Student stu = jdbcTemplate.queryForObject(sql,new StudentRowMapper(),4);
    System.out.println(stu.toString());
}
@Test
public void demo11(){ //查询复杂对象(封装为实体对象) 获取多个 需使用RowMapper接口
    String sql = "select * from student";
    List<Student> stus = jdbcTemplate.query(sql,new StudentRowMapper());
    System.out.println(stus);
}

private class StudentRowMapper implements RowMapper<Student>{
    public Student mapRow(ResultSet resultSet, int i) throws SQLException {
        Student stu = new Student();
        stu.setId(resultSet.getInt("id"));
        stu.setName(resultSet.getString("name"));
        stu.setSex(resultSet.getString("sex"));
        stu.setBorn(resultSet.getDate("born"));
        return stu;
    }
}

以上是语法使用，下面以Student表为例简单写一套持久层案例

实体类：Student.java

package com.cj.sc.entity;

import java.util.Date;

public class Student {
    private int id;
    private String name;
    private String sex;
    private Date born;
    public int getId() {
        return id;
    }

    public void setId(int 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 Date getBorn() {
        return born;
    }

    public void setBorn(Date born) {
        this.born = born;
    }

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", sex='" + sex + '\'' +
                ", born=" + born +
                '}';
    }

}

接口类：StudentDao.java

package com.cj.sc.dao;

import com.cj.sc.entity.Student;

import java.util.List;

public interface StudentDao {
    void insert(Student stu);
    void update(Student stu);
    void delete(int id);
    Student select(int id);
    List<Student> selectAll();
}

接口实现类：StudentDaoImpl.java

package com.cj.sc.dao.impl;

import com.cj.sc.dao.StudentDao;
import com.cj.sc.entity.Student;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
@Repository
public class StudentDaoImpl implements StudentDao{

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public void insert(Student stu) {
        String sql = "insert into student(name,sex,born) values(?,?,?)";
        jdbcTemplate.update(sql,stu.getName(),stu.getSex(),stu.getBorn());
    }

    public void update(Student stu) {
        String sql = "update student set name = ? , sex = ? , born = ? where id = ?";
        jdbcTemplate.update(sql,stu.getName(),stu.getSex(),stu.getBorn(),stu.getId());
    }

    public void delete(int id) {
        String sql = "delete from student where id = ?";
        jdbcTemplate.update(sql,id);
    }

    public Student select(int id) {
        String sql = "select * from student where id = ?";
        return jdbcTemplate.queryForObject(sql,new StudentRowMapper(),id);
    }

    public List<Student> selectAll() {
        String sql = "select * from student";
        return jdbcTemplate.query(sql,new StudentRowMapper());
    }

    private class StudentRowMapper implements RowMapper<Student>{
        public Student mapRow(ResultSet resultSet, int i) throws SQLException {
            Student stu = new Student();
            stu.setId(resultSet.getInt("id"));
            stu.setName(resultSet.getString("name"));
            stu.setSex(resultSet.getString("sex"));
            stu.setBorn(resultSet.getDate("born"));
            return stu;
        }
    }
}

配置文件：spring.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:aop="http://www.springframework.org/schema/aop"
       xmlns:tx="http://www.springframework.org/schema/tx"
       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.xsd
       http://www.springframework.org/schema/aop
       http://www.springframework.org/schema/aop/spring-aop.xsd
       http://www.springframework.org/schema/tx
       http://www.springframework.org/schema/tx/spring-tx.xsd
       ">

    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/selection_course?useUnicode=true&amp;characterEncoding=utf-8"/>
        <property name="username" value="root"/>
        <property name="password" value="root"/>
    </bean>

    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"/>
    </bean>

    <context:component-scan base-package="com.cj.sc"/>
</beans>

测试类：JDBCTemplateDemo2.java

import com.cj.sc.dao.StudentDao;
import com.cj.sc.dao.impl.StudentDaoImpl;
import com.cj.sc.entity.Student;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:spring.xml")
public class JDBCTemplateDemo2 {

    @Autowired
    private StudentDao studentDao;

    @Test
    public void demo1() throws ParseException {
        DateFormat df = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
        String d = "1992-06-10 05:10:49";
        Date born = df.parse(d);

        Student stu = new Student();
        stu.setName("劫");
        stu.setSex("男");
        stu.setBorn(born);
        studentDao.insert(stu);
    }

    @Test
    public void demo2(){
        Student stu = new Student();
        stu.setId(2);
        stu.setName("VN");
        stu.setSex("女");
        studentDao.update(stu);
    }
    @Test
    public void demo3(){
        studentDao.delete(5);
    }
    @Test
    public void demo4(){
        Student stu = studentDao.select(6);
        System.out.println(stu);
    }
    @Test
    public void demo5(){
        List<Student> stus = studentDao.selectAll();
        System.out.println(stus);
    }

}

这样就简单实现了持久层的增删改查

优点：

——使用最少的代码和配置完成功能实现

——简单、灵活

缺点：

——数据库代码写在JAVA程序中，不方便管理

——功能不丰富（例如对象里面包含对象这种处理比较麻烦，不如mybatis直接配置文件中写映射关系）

源码demo链接：https://pan.baidu.com/s/1PETazjy0Fa6DL98BmRNQuQ   密码：0u9s