Spring Boot使用JdbcTmplate

一 pom

<project xmlns="http://maven.apache.org/POM/4.0.0"
     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
     xsi:schemaLocation="http://maven.apache.org/POM/4.0.0  http://maven.apache.org/xsd/maven-4.0.0.xsd">
     <modelVersion>4.0.0</modelVersion>
     <groupId>org.fkit</groupId>
     <artifactId>springbootjdbctemplate</artifactId>
     <version>0.0.1-SNAPSHOT</version>
     <packaging>jar</packaging>
     <name>springbootjdbctemplate</name>
     <url>http://maven.apache.org</url>
     <!-- spring-boot-starter-parent是Spring Boot的核心启动器，  包含了自动配置、日志和YAML等大量默认的配置，大大简化了我们的开发。
           引入之后相关的starter引入就不需要添加version配置， spring  boot会自动选择最合适的版本进行添加。 -->
     <parent>
           <groupId>org.springframework.boot</groupId>
           <artifactId>spring-boot-starter-parent</artifactId>
           <version>2.0.0.RELEASE</version>
           <relativePath />
     </parent>
     <properties>
           <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
           <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
           <java.version>1.8</java.version>
     </properties>
     <dependencies>
           <!-- 添加spring-boot-starter-web模块依赖 -->
           <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-web</artifactId>
           </dependency>
           <!-- 添加spring-boot-starter-thymeleaf模块依赖 -->
           <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-thymeleaf</artifactId>
           </dependency>
           <!-- 添加MySQL依赖 -->
           <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
           </dependency>
           <!-- 添加JDBC依赖 -->
           <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-jdbc</artifactId>
           </dependency>
           <dependency>
                <groupId>junit</groupId>
                <artifactId>junit</artifactId>
                <scope>test</scope>
           </dependency>
     </dependencies>
</project>

二 基本属性配置

# url
spring.datasource.url=jdbc:mysql://localhost:3306/jdbctemplate
# username
spring.datasource.username=root
# password
spring.datasource.password=
# driver
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

三 启动类

package org.fkit.springbootjdbctemplate;


import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;


@SpringBootApplication
public class App
{
    public static void main( String[] args )
    {
        // SpringApplication 用于从main方法启动Spring应用的类。
        SpringApplication.run(App.class, args);
    }
}

四 持久化类

package org.fkit.springbootjdbctemplate.bean;
import java.io.Serializable;
public class User implements Serializable{
     private static final long serialVersionUID = 1L;
     
     private int id ;
     private String loginName ;
     private String username ;
     private String password;
     public int getId() {
           return id;
     }
     public void setId(int id) {
           this.id = id;
     }
     public String getLoginName() {
           return loginName;
     }
     public void setLoginName(String loginName) {
           this.loginName = loginName;
     }
     public String getUsername() {
           return username;
     }
     public void setUsername(String username) {
           this.username = username;
     }
     public String getPassword() {
           return password;
     }
     public void setPassword(String password) {
           this.password = password;
     }
     
}

五 控制器

package org.fkit.springbootjdbctemplate.controller;
import java.util.List;
import javax.annotation.Resource;
import org.fkit.springbootjdbctemplate.bean.User;
import org.fkit.springbootjdbctemplate.service.UserService;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
@RestController
@RequestMapping("/user")
public class UserController {
    @Resource
    private UserService userService;
    
    @RequestMapping("/insertUser")
    public String insertUser(){
        return "插入数据["+userService.insertUser()+"]条";
    }
    
    @RequestMapping("/insertGetKey")
    public User insertGetKey(User user) {
        return userService.insertGetKey(user);
    }
    
    @RequestMapping("/selectByUsername")
    public User selectByUserNm(String username){
        return userService.selectByUsername(username);
    }
    
    @RequestMapping("/findAll")
    public List<User> findAll(){
        return userService.findAll();
    }
    
    @RequestMapping("/update")
    public void update(User user) {
        userService.update(user);
    }
    
    @RequestMapping("/delete")
    public void delete(Integer id) {
        userService.delete(id);
    }
}

六 service

package org.fkit.springbootjdbctemplate.service;


import java.util.List;


import javax.annotation.Resource;


import org.fkit.springbootjdbctemplate.bean.User;
import org.fkit.springbootjdbctemplate.repository.UserRepository;
import org.springframework.stereotype.Service;


@Service
public class UserService {
    
    // 注入UserRepository
    @Resource
    private UserRepository userRepository;
    
    public int insertUser(){
        return userRepository.insertUser();
    }
    
    public User selectByUsername(String username){
        return userRepository.selectByUsername(username);
    }
    
    public List<User> findAll(){
        return userRepository.findAll();
    }
    
    public User insertGetKey(User user) {
        return userRepository.insertGetKey(user);
    }
    
    public void update(User user) {
        userRepository.update(user);
    }
    
    public void delete(Integer id) {
        userRepository.delete(id);
    }
}

七 Repository

package org.fkit.springbootjdbctemplate.repository;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import javax.annotation.Resource;
import org.fkit.springbootjdbctemplate.bean.User;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
/**
* @Repository注解：标注这是一个持久化操作对象.
*/
@Repository
public class UserRepository {
    
    // 注入JdbcTemplate模板对象
    @Resource
    private JdbcTemplate jdbcTemplate;
    
    /***
     *  插入数据
     * @return 插入影响的行数
     */
    public int insertUser(){
        String sql = "insert into tb_user(login_name ,username ,passWord) "
                + "values (?,?,?),(?,?,?),(?,?,?)";
        Object[] args = new Object[]{"swk","孙悟空","123456","zbj","猪八戒","123456"
                ,"ts","唐僧","123456"};
        // 参数一：插入数据的sql语句 参数二: 对应sql语句中占位符?的参数
        return jdbcTemplate.update(sql, args);
    }
    
    /***
     *  根据userName查询数据
     * @param userName
     * @return User对象
     */
    public User selectByUsername(String username) {
        // 定义SQL语句
        String sql = "select * from tb_user where username = ?";
        // 定义一个RowMapper
        RowMapper<User> rowMapper = new BeanPropertyRowMapper<>(User.class);
        // 执行查询方法
        User user = jdbcTemplate.queryForObject(sql, new Object[] { username }, rowMapper);
        return user;
    }
    
    /***
     * 根据id查询数据
     * @return User对象
     */
    public User findUserById(int id) {
        // 定义SQL语句
        String sql = "select * from tb_user where id=?";
        RowMapper<User> rowMapper = new BeanPropertyRowMapper<>(User.class);
        // 执行查询方法
        return jdbcTemplate.queryForObject(sql, new Object[] { id }, rowMapper);
    }
    
    /***
     * 查询所有数据
     * @return 包含User对象的List集合
     */
    public List<User> findAll() {
        // 定义SQL语句
        String sql = "select * from tb_user";
        // 申明结果集的映射rowMapper，将结果集的数据映射成User对象数据
        RowMapper<User> rowMapper = new BeanPropertyRowMapper<>(User.class);
        return jdbcTemplate.query(sql, rowMapper);
    }
    
    /***
     * 根据id删除数据
     */
    public void delete(final Integer id) {
        // 定义SQL语句
        String sql = "delete from tb_user where id=?";
        // 执行
        jdbcTemplate.update(sql, new Object[] { id });
    }
    
    /***
     * 修改数据
     */
    public void update(final User user) {
        // 定义SQL语句
        String sql = "update tb_user set username=?, login_name=? where id=?";
        // 执行
        jdbcTemplate.update(sql,
            new Object[] { user.getUsername(), user.getLoginName(), user.getId()});
    }
    /**
     * 插入数据获取被插入数据的主键
     * */
    public User insertGetKey(User user) {
        // 1.申明插入的sql语句
        String sql = "insert into tb_user(username,login_name,password) values(?,?,?)";
        // 2.定义插入数据后获取主键的对象
        KeyHolder holder = new GeneratedKeyHolder();
        jdbcTemplate.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                // 3.插入数据后，将被插入数据的主键返回回来
                PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                ps.setString(1, user.getUsername());
                ps.setString(2, user.getLoginName());
                ps.setString(3, user.getPassword());
                return ps;
            }
        }, holder);
        // 4.获取被插入数据库的主键 然后注入到user对象中去
        int newUserId = holder.getKey().intValue();
        user.setId(newUserId);
        return user;
    }
}

八 新建数据库

CREATE DATABASE jdbctemplate;
USE jdbctemplate;
CREATE TABLE tb_user(
    id INT PRIMARY KEY auto_increment,
    login_name VARCHAR(23),
    PASSWORD VARCHAR(23),
    username VARCHAR(23)
);

九 测试