一、导入jar包

• mysql-connector-java-8.0.16.jar
• spring-jdbc-5.1.0.RELEASE.jar
• spring-test-5.1.0.RELEASE.jar

domain（Student）

package com.revanwang.jdbc.domain;

import lombok.Data;

@Data
public class Student {
    private Long    id;
    private String  name;
    private Integer age;
}

二、JdbcTemplate

数据库配置

db.properties

jdbc.driverClassName=com.mysql.cj.jdbc.Driver
jdbc.username=root
jdbc.password=admin
jdbc.url=jdbc:mysql:///springdemo?useSSL=true&serverTimezone=UTC
jdbc.maxActive=5

applicationContext.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       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">

    <!--  引入属性配置文件  -->
    <context:property-placeholder location="classpath:db.properties"/>

    <!--配置缓冲池-->
    <bean id="dataBaseDB" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
        <property name="driverClassName" value="${jdbc.driverClassName}"/>
        <property name="username" value="${jdbc.username}"/>
        <property name="password" value="${jdbc.password}"/>
        <property name="url" value="${jdbc.url}"/>
        <property name="maxActive" value="${jdbc.maxActive}"/>
    </bean>

    <!--配置DAO-->
    <bean id="studentDAO" class="com.revanwang.jdbc.dao.impl.StudentDAOImpl">
        <property name="dataSource" ref="dataBaseDB"/>
    </bean>

    <!--配置Service-->
    <bean id="studentService" class="com.revanwang.jdbc.service.impl.StudentServiceImpl">
        <property name="studentDAO" ref="studentDAO"/>
    </bean>

    <!--配置Action-->
    <bean id="studentAction" class="com.revanwang.jdbc.action.StudentAction">
        <property name="studentService" ref="studentService"/>
    </bean>

</beans>

dao（StudentDAOImpl）

package com.revanwang.jdbc.dao.impl;

import com.revanwang.jdbc.dao.IStudentDAO;
import com.revanwang.jdbc.domain.Student;
import lombok.Setter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import javax.sql.DataSource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

public class StudentDAOImpl implements IStudentDAO {

    @Setter
    private JdbcTemplate jdbcTemplate;

    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }

    @Override
    public void save(Student student) {
        String sql = "INSERT INTO Student (name, age) VALUES(?, ?)";
        this.jdbcTemplate.update(sql, student.getName(), student.getAge());
    }

    @Override
    public void delete(Student student) {
        String sql = "DELETE FROM Student WHERE id = ?";
        this.jdbcTemplate.update(sql, student.getId());
    }

    @Override
    public void update(Student student) {
        String sql = "UPDATE Student SET name = ?, age = ? WHERE id = ?";
        this.jdbcTemplate.update(sql, student.getName(), student.getAge(), student.getId());
    }

    @Override
    public Student get(Long id) {
        String sql = "SELECT * FROM Student WHERE id = ?";
        List<Student> students = this.jdbcTemplate.query(sql, new RowMapper<Student>() {
            @Override
            public Student mapRow(ResultSet result, int i) throws SQLException {
                Student student = new Student();
                student.setId(result.getLong("id"));
                student.setName(result.getString("name"));
                student.setAge(result.getInt("age"));
                return student;
            }
        }, id);
        return students.size() == 1 ? students.get(0) : null;
    }

    @Override
    public List<Student> getList() {
        String sql = "SELECT * FROM Student";
        List<Student> studentList = this.jdbcTemplate.query(sql, new RowMapper<Student>() {

            /**
             * 把数据库中一行数据封装成 Student 对象
             * 自动把每一行封装的Student对象存储在List集合中，并返回集合
             */
            @Override
            public Student mapRow(ResultSet resultSet, int i) throws SQLException {
                Student student = new Student();
                student.setId(resultSet.getLong("id"));
                student.setName(resultSet.getString("name"));
                student.setAge(resultSet.getInt("age"));
                return student;
            }
        });
        return studentList;
    }
}

service（StudentServiceImpl）

package com.revanwang.jdbc.service.impl;

import com.revanwang.jdbc.dao.IStudentDAO;
import com.revanwang.jdbc.domain.Student;
import com.revanwang.jdbc.service.IStudentService;
import lombok.Setter;

import java.util.List;

public class StudentServiceImpl implements IStudentService {

    @Setter
    private IStudentDAO studentDAO;

    @Override
    public void save(Student student) {
        studentDAO.save(student);
    }

    @Override
    public void delete(Student student) {
        studentDAO.delete(student);
    }

    @Override
    public void update(Student student) {
        studentDAO.update(student);
    }

    @Override
    public Student get(Long id) {
        return studentDAO.get(id);
    }

    @Override
    public List<Student> getList() {
        return studentDAO.getList();
    }
}

Action（StudentAction）

package com.revanwang.jdbc.action;

import com.revanwang.jdbc.domain.Student;
import com.revanwang.jdbc.service.IStudentService;
import lombok.Setter;

import java.util.List;

public class StudentAction {
    @Setter
    private IStudentService studentService;

    public void save(Student student) {
        this.studentService.save(student);
    }

    public void delete(Student student) {
        this.studentService.delete(student);
    }

    public void update(Student student) {
        this.studentService.update(student);
    }

    public Student get(Long id) {
        return this.studentService.get(id);
    }

    public List<Student> getList() {
        return this.studentService.getList();
    }
}

JdbcTest

package com.revanwang.jdbc.test;


import com.revanwang.jdbc.action.StudentAction;
import com.revanwang.jdbc.domain.Student;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.ApplicationContext;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import java.util.List;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:applicationContext.xml")
public class JdbcTest {

    @Autowired
    private ApplicationContext ctx;

    @Test
    public void testSave() {

        Student student = new Student();
        student.setName("WRW");
        student.setAge(28);

        StudentAction action = ctx.getBean("studentAction", StudentAction.class);
        action.save(student);
    }

    @Test
    public void testDelete() {

        Student student = new Student();
        student.setId(1L);

        StudentAction action = ctx.getBean("studentAction", StudentAction.class);
        action.delete(student);
    }


    @Test
    public void testUpdate() {

        Student student = new Student();
        student.setId(4L);
        student.setName("乔峰");
        student.setAge(119);

        StudentAction action = ctx.getBean("studentAction", StudentAction.class);
        action.update(student);
    }

    @Test
    public void testGet() {
        StudentAction action = ctx.getBean("studentAction", StudentAction.class);
        Student student = action.get(2L);
        System.out.println(student);
    }

    @Test
    public void testGetList() {
        StudentAction action = ctx.getBean("studentAction", StudentAction.class);
        List<Student> studentList = action.getList();
        for (Student student:studentList) {
            System.out.println(student);
        }
    }
}

三、JdbcDaoSupport

使用继承的方式来获取JdbcTemplate

package com.revanwang.jdbc.dao.impl;

import com.revanwang.jdbc.dao.IStudentDAO;
import com.revanwang.jdbc.domain.Student;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

public class StudentDAOImpl extends JdbcDaoSupport implements IStudentDAO {

    @Override
    public void save(Student student) {
        String sql = "INSERT INTO Student (name, age) VALUES(?, ?)";
        super.getJdbcTemplate().update(sql, student.getName(), student.getAge());
    }

    @Override
    public void delete(Student student) {
        String sql = "DELETE FROM Student WHERE id = ?";
        super.getJdbcTemplate().update(sql, student.getId());
    }

    @Override
    public void update(Student student) {
        String sql = "UPDATE Student SET name = ?, age = ? WHERE id = ?";
        super.getJdbcTemplate().update(sql, student.getName(), student.getAge(), student.getId());
    }

    @Override
    public Student get(Long id) {
        String sql = "SELECT * FROM Student WHERE id = ?";
        List<Student> students = super.getJdbcTemplate().query(sql, new RowMapper<Student>() {
            @Override
            public Student mapRow(ResultSet result, int i) throws SQLException {
                Student student = new Student();
                student.setId(result.getLong("id"));
                student.setName(result.getString("name"));
                student.setAge(result.getInt("age"));
                return student;
            }
        }, id);
        return students.size() == 1 ? students.get(0) : null;
    }

    @Override
    public List<Student> getList() {
        String sql = "SELECT * FROM Student";
        List<Student> studentList = super.getJdbcTemplate().query(sql, new RowMapper<Student>() {

            /**
             * 把数据库中一行数据封装成 Student 对象
             * 自动把每一行封装的Student对象存储在List集合中，并返回集合
             */
            @Override
            public Student mapRow(ResultSet resultSet, int i) throws SQLException {
                Student student = new Student();
                student.setId(resultSet.getLong("id"));
                student.setName(resultSet.getString("name"));
                student.setAge(resultSet.getInt("age"));
                return student;
            }
        });
        return studentList;
    }
}