Spring Boot使用JdbcTmplate

ゝ一世哀愁。 2021-07-24 23:06 197阅读 0赞

**一 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>
         
         <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>
               
               <dependency>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-starter-web</artifactId>
               </dependency>
               
               <dependency>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-starter-thymeleaf</artifactId>
               </dependency>
               
               <dependency>
                    <groupId>mysql</groupId>
                    <artifactId>mysql-connector-java</artifactId>
               </dependency>
               
               <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)
    );

**九 测试**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5ncWl1bWluZw_size_16_color_FFFFFF_t_70][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5ncWl1bWluZw_size_16_color_FFFFFF_t_70 1][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5ncWl1bWluZw_size_16_color_FFFFFF_t_70]: /images/20210724/c21ce8cd695b41cb8cf6991428513c53.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5ncWl1bWluZw_size_16_color_FFFFFF_t_70 1]: /images/20210724/b16af3b7304147b6a2fb694d3f3387e7.png