JdbcTemplate

原始的JDBC在操作数据库方面是比较麻烦的，所以Spring为了提高开发的效率，把JDBC进行封装、改造了一番，而JdbcTemplate就是Spring对原始JDBC封装之后提供的一个操作数据库的工具类。

JdbcTemplate提供以下三种类型的方法来完成所有数据库的增删改查等操作。

• executeXXX()：执行任何SQL语句，对数据库、表进行新建、修改、删除操作。
• update()：执行新增、修改、删除等操作。
• queryXXX()：执行查询相关的操作。

基于JdbcTemplate的User表的增删改查操作。

    4.0.0

    org.example
    HelloJdbcTemplate
    1.0-SNAPSHOT
    jar

    
        9
        9
    

    
        
        
            org.springframework
            spring-context
            5.0.2.RELEASE
        

        
        
            org.springframework
            spring-jdbc
            5.0.2.RELEASE
        

        
        
            mysql
            mysql-connector-java
            8.0.18
        

        
        
            junit
            junit
            4.12
            test
        
    

package com.hc;

import com.hc.domain.User;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import java.util.Date;
import java.util.List;

public class TestJdbcTemplate {
    JdbcTemplate jdbcTemplate = null;
    DriverManagerDataSource dataSource = null;

    @Before
    public void init() {
        // 创建数据源
        dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://localhost:3306/SSM?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT");
        dataSource.setUsername("root");
        dataSource.setPassword("1234");

        // 创建JdbcTemplate对象，并设置数据源（Spring自带数据源）
        jdbcTemplate = new JdbcTemplate(dataSource);
    }

    @Test
    public void testSave() {
        jdbcTemplate.update("insert into user(username,birthday,sex,address) values (?,?,?,?)", "HCC", new Date(), "男", "湖北宜昌");
    }

    @Test
    public void testDelete() {
        jdbcTemplate.update("delete from user where id = ?", 51);
    }

    @Test
    public void testUpdate() {
        jdbcTemplate.update("update user set username = ? where id = ?", "HCCC", 52);
    }

    @Test
    public void testFindAll() {
        List users = jdbcTemplate.query("select * from user", new BeanPropertyRowMapper(User.class));
        for (User u : users) {
            System.out.println(u);
        }
    }

    @Test
    public void testFindOneBuId() {
        List users = jdbcTemplate.query("select * from user where id = ?", new BeanPropertyRowMapper(User.class), 50);
        System.out.println(users.size() == 0 ? "该用户不存在" : users.get(0));
    }
}

很明显，不管是数据源还是JdbcTemplate都可以进一步提交给Spring容器进行管理。

• User

package com.hc.domain;

import java.util.Date;

public class User {
    private Integer id;
    private String username;
    private Date birthday;
    private String sex;
    private String address;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + ''' +
                ", birthday=" + birthday +
                ", sex='" + sex + ''' +
                ", address='" + address + ''' +
                '}';
    }
}

• UserDaoImpl

package com.hc.dao.impl;

import com.hc.dao.UserDao;
import com.hc.domain.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.List;

@Repository(value = "userDao")
public class UserDaoImpl implements UserDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public void save(User user) {
        jdbcTemplate.update("insert into user(username, birthday, sex, address) values (?, ?, ?, ?)", user.getUsername(), user.getBirthday(), user.getSex(), user.getAddress());
    }

    @Override
    public void delete(Integer id) {
        jdbcTemplate.update("delete from user where id = ?", id);
    }

    @Override
    public void update(User user) {
        jdbcTemplate.update("update user set username=?,birthday=?,sex=?,address=? where id = ?", user.getUsername(), user.getBirthday(), user.getSex(), user.getAddress(), user.getId());
    }

    @Override
    public User findOneById(Integer id) {
        List users = jdbcTemplate.query("select * from user where id = ?", new BeanPropertyRowMapper(User.class), id);
        return users.size() == 0 ? null : users.get(0);
    }

    @Override
    public List findAll() {
        return jdbcTemplate.query("select * from user", new BeanPropertyRowMapper(User.class));
    }
}

• UserServiceImpl

package com.hc.service.impl;

import com.hc.dao.UserDao;
import com.hc.domain.User;
import com.hc.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service(value = "userService")
public class UserServiceImpl implements UserService {

    @Autowired
    private UserDao userDao;

    @Override
    public void save(User user) {
        userDao.save(user);
    }

    @Override
    public void delete(Integer id) {
        userDao.delete(id);
    }

    @Override
    public void update(User user) {
        userDao.update(user);
    }

    @Override
    public User findOneById(Integer id) {
        return userDao.findOneById(id);
    }

    @Override
    public List findAll() {
        return userDao.findAll();
    }
}

package com.hc;

import com.hc.domain.User;
import com.hc.service.UserService;
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.util.Date;
import java.util.List;


@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = "classpath:bean.xml")
public class TestSpringJdbcTemplate {

    @Autowired
    private UserService userService;

    @Test
    public void testSave() {
        User user = new User();
        user.setUsername("HFH");
        user.setBirthday(new Date());
        user.setSex("男");
        user.setAddress("广东佛山");

        userService.save(user);
    }

    @Test
    public void testDelete() {
        userService.delete(52);
    }

    @Test
    public void testUpdate() {
        User user = new User();
        user.setId(53);
        user.setUsername("HFH");
        user.setBirthday(new Date());
        user.setSex("男");
        user.setAddress("HK");

        userService.update(user);
    }

    @Test
    public void testFindOneById() {
        User user = userService.findOneById(53);
        System.out.println(user);
    }

    @Test
    public void testFindAll() {
        List users = userService.findAll();
        for (User u : users) {
            System.out.println(u);
        }
    }
}