Spring框架之jdbcTemplate实现数据库的增删改查

首先我们要知道jdbc是什么？jdbcTemplate是什么？
jdbc是一种用于执行SQL语句的Java API，可以为多种关系型数据库提供统一的访问，它由一组用Java语言编写的类和接口组成。
jdbcTemplate是Spring对jdbc封装的模板。
这里用一个常见的java例子对jdbcTemplate进行理解：
连接数据库操作，实现对学生系统的增删改查
Java源代码：

定义一个学生类

package cdcas.pojo;

public class Student {
	private int 	id;
	private String 	xh;					// 学号
	private String 	name;				// 姓名
	private int 	classId;			// 班级id
	private String 	pswd;				// 密码
	private String 	gender;				// 性别
	private int 	age;				// 年龄
	private String 	email;				// 邮箱
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getXh() {
		return xh;
	}
	public void setXh(String xh) {
		this.xh = xh;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public int getClassId() {
		return classId;
	}
	public void setClassId(int classId) {
		this.classId = classId;
	}
	public String getPswd() {
		return pswd;
	}
	public void setPswd(String pswd) {
		this.pswd = pswd;
	}
	public String getGender() {
		return gender;
	}
	public void setGender(String gender) {
		this.gender = gender;
	}
	public int getAge() {
		return age;
	}
	public void setAge(int age) {
		this.age = age;
	}
	public String getEmail() {
		return email;
	}
	public void setEmail(String email) {
		this.email = email;
	}
}

学生数据库接口

package cdcas.dao;

import java.util.List;

import cdcas.pojo.Student;

public interface StudentDao {
	
	public void save(Student stu);
	
	public void delete(Student stu);
	
	public void update(Student stu);
	
	public Student findById(int id);
	
	public List<Student> findAll();
	
	public int count();	
}

数据库的实现类

package cdcas.dao.impl;

import java.util.List;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.rowset.SqlRowSet;

import cdcas.dao.StudentDao;
import cdcas.pojo.Student;

public class StudentDaoImpl implements StudentDao {
	JdbcTemplate jdbcTemplate;
	
	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	public void save(Student stu) {
		
		String sql = "INSERT INTO student(xh,name,classId,pswd,gender,age,email) VALUES(?,?,?,?,?,?,?)";
		Object[] params = {stu.getXh(),stu.getName(),stu.getClassId(),stu.getPswd(),stu.getGender(),stu.getAge(),stu.getEmail()};
		
		jdbcTemplate.update(sql,params);
	}

	public void delete(Student stu) {
		String sql = "DELETE FROM student WHERE id=?";
		Object[] params = {stu.getId()};
		
		jdbcTemplate.update(sql,params);
		
	}

	public void update(Student stu) {
		String sql = "UPDATE student SET xh=?,name=?,classId=?,pswd=?,gender=?,age=?,email=? WHERE id=?";
		Object[] params = {stu.getXh(),stu.getName(),stu.getClassId(),stu.getPswd(),stu.getGender(),stu.getAge(),stu.getEmail(),stu.getId()};
		
		jdbcTemplate.update(sql,params);
		
	}

	public Student findById(int id) {
		String sql = "SELECT * FROM student WHERE id=?";
		Object[] params = {id};
		
		RowMapper<Student> rowMapper = new BeanPropertyRowMapper<Student>(Student.class);
		
		Student student = jdbcTemplate.queryForObject(sql, params, rowMapper);
		
		return student;
	}

	public List<Student> findAll() {
		String sql = "SELECT * FROM student";
		
		RowMapper<Student> rowMapper = new BeanPropertyRowMapper<Student>(Student.class);
		
		List<Student> list = jdbcTemplate.query(sql, rowMapper);

		return list;
	}

	public int count() {
		String sql = "SELECT COUNT(*) FROM student";
		SqlRowSet rs = jdbcTemplate.queryForRowSet(sql);
		if (rs.next()) {
			return rs.getInt(1);
		}
		return 0;
	}

}

定义测试类，实现增删改查操作：

package test;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import cdcas.dao.StudentDao;
import cdcas.pojo.Student;

public class Test {

	public static void main(String[] args) {
		
		ApplicationContext ctx = new ClassPathXmlApplicationContext("applicationcontext.xml");
		
		StudentDao stuDao = (StudentDao) ctx.getBean("studentDao");
		
		Student stu = new Student();
		
		/*增
		stu.setXh("2020010103");
		stu.setName("cc");
		stu.setClassId(3);
		stu.setPswd("33");
		stu.setGender("女");
		stu.setAge(20);
		stu.setEmail("33@qq.com");
		
		stuDao.save(stu);
		*/
		
		/*删除
		stu.setId(1);
		stuDao.delete(stu);
		*/
		
		
		/*改
		 stu.setXh("2020010104");
		stu.setName("dd");
		stu.setClassId(4);
		stu.setPswd("054422");
		stu.setGender("女");
		stu.setAge(21);
		stu.setEmail("44@qq.com");
		stu.setId(2);
		
		stuDao.update(stu);
		*/
		
		/*查
		stuDao.findById(2);
		*/
		
		/*查找所有
		stuDao.findAll();
		*/
		
		/*统计数量*/
		stuDao.count();

	}

}

创建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:aop="http://www.springframework.org/schema/aop"
    xmlns:context="http://www.springframework.org/schema/context" 
    xsi:schemaLocation="
        http://www.springframework.org/schema/beans 
        http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/aop 
        http://www.springframework.org/schema/aop/spring-aop.xsd        
        http://www.springframework.org/schema/context 
        http://www.springframework.org/schema/context/spring-context.xsd"> 
	
	<!-- 1.配置数据源，连接数据库 -->
	<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<property name="driverClassName">
			 <value>com.mysql.jdbc.Driver</value>
		</property>
		<property name="url">
			<value>jdbc:mysql://localhost:3306/studentms?characterEncoding=utf8</value>
		</property>
		<property name="username">
			<value>root</value>
		</property>
		<property name="password">
			<value>054422</value>
		</property>
	</bean>
	
	<!-- 2.配置jdbcTemplate模板，注入dataSource -->
	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
		<property name="dataSource" ref="dataSource"></property>
	</bean>
	
	<!-- 配置DAO，注入jdbcTemplate属性值 -->
	<bean id="studentDao" class="cdcas.dao.impl.StudentDaoImpl">
		<property name="jdbcTemplate" ref="jdbcTemplate"></property>
	</bean>
</beans>

在Navicat for MySQL软件中创建数据库，并建立java代码中所需要的表

在Java Application中运行java代码，例如数据库表数据的插入：