SpringBoot使用JdbcTemplate访问MySQL

秒速五厘米 2022-02-04 02:29 231阅读 0赞

**准备工作**

*  IDEA
 *  MySQL
 *  Maven
 *  JDK1.8及以上  
    初始化数据库：

SET NAMES utf8mb4;
    SET FOREIGN_KEY_CHECKS = 0;
    
    -- ----------------------------
    -- Table structure for account
    -- ----------------------------
    DROP TABLE IF EXISTS `account`;
    CREATE TABLE `account`  (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
    `money` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
    PRIMARY KEY (`id`) USING BTREE
    ) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
    
    -- ----------------------------
    -- Records of account
    -- ----------------------------
    INSERT INTO `account` VALUES (1, 'zhangsan', '888.0');
    INSERT INTO `account` VALUES (2, 'lisi', '888.0');
    INSERT INTO `account` VALUES (3, 'wangwu', '888.0');
    
    SET FOREIGN_KEY_CHECKS = 1;

**创建工程springrest**

*  引入依赖

<dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-jdbc</artifactId>
            </dependency>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-web</artifactId>
            </dependency>
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>5.1.47</version>
            </dependency>
    
            <dependency>
                <groupId>org.projectlombok</groupId>
                <artifactId>lombok</artifactId>
                <optional>true</optional>
            </dependency>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-test</artifactId>
                <scope>test</scope>
    </dependency>

**相关文件配置**  
配置数据库驱动，数据库URL，用户名，密码

spring:
      datasource:
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/test
        data-username: root
        data-password: root

**具体编码**

*  实体类Account（用到lombok插件）

@Data
    public class Account { 
        private int id;
        private String name;
        private double money;
    }

*  Dao层

public interface AccountDao {
        int add(Account account);
    
        int update(Account account);
    
        int delete(int id);
    
        Account findAccountById(int id);
        
        List<Account> findAccountList();
    }

*  Dao层的实现

@Repository
    public class AccountDaoImpl implements AccountDao {
        @Autowired
        private JdbcTemplate jdbcTemplate;
    
        @Override
        public int add(Account account) {
            return jdbcTemplate.update("insert into account(name,money) values(?,?)",account.getName(),account.getMoney());
        }
    
        @Override
        public int update(Account account) {
            return jdbcTemplate.update("update account set name=?,money=? where id=?",
                    account.getName(),account.getMoney(),account.getId());
        }
    
        @Override
        public int delete(int id) {
            return jdbcTemplate.update("DELETE from table account where id=?",id);
        }
        
        @Override
        public Account findAccountById(int id) {
            List<Account> list = jdbcTemplate.query("select * from account where id = ?", new Object[]{id}, new BeanPropertyRowMapper(Account.class));
                 if(list!=null&&list.size()>0){
                     Account account=list.get(0);
                     return account;
                 }
                 else
                     return null;
        }
    
        @Override
        public List<Account> findAccountList() {
            List<Account> list=jdbcTemplate.query("select *from account",new Object[]{},new BeanPropertyRowMapper<>(Account.class));
            if(list!=null&&list.size()>0){
                return list;
            }
            else
                return null;
        }
    }

*  Service层

public interface AccountService { 
        int add(Account account);
    
        int update(Account account);
    
        int delete(int id);
    
        Account findAccountById(int id);
    
        List<Account> findAccountList();
    }

*  Service层实现

@Service
    public class AccountServiceImpl implements AccountService { 
        @Autowired
        private AccountDao AccountDao;
    
        @Override
        public int add(Account account) { 
            return AccountDao.add(account);
        }
    
        @Override
        public int update(Account account) { 
            return AccountDao.update(account);
        }
    
        @Override
        public int delete(int id) { 
            return AccountDao.delete(id);
        }
    
        @Override
        public Account findAccountById(int id) { 
            return AccountDao.findAccountById(id);
        }
    
        @Override
        public List<Account> findAccountList() { 
            return AccountDao.findAccountList();
        }
    }

*  返回结果实体类

@Data
    public class R<T> { 
        private Integer code;
        private String message;
        private T data;
    
        private R(T data){ 
            this.code=200;
            this.message="success";
            this.data=data;
        }
    
        private R(String message){ 
            this.code=400;
            this.message=message;
            this.data=null;
        }
    
        public static <T> R<T> success(T data){ 
            return new R<T>(data);
        }
    
        public static <T> R<T> fail(String message){ 
            return new R<T>(message);
        }
    }

*  Controller层

@RestController
    @RequestMapping("/account")
    public class AccountController { 
        @Autowired
        private IAccountService iAccountService;
    
        @GetMapping("/list")
        public List<Account> getAccounts(){ 
            return iAccountService.findAccountList();
        }
    
        @GetMapping("/{id}")
        public Account getAccountById(@PathVariable("id") int id){ 
            return iAccountService.findAccountById(id);
        }
    
        @PutMapping("/{id}")
        public R updateAccount(@PathVariable("id")int id,
                               @RequestParam(value = "name",required = true) String name,
                               @RequestParam(value = "money")double money){ 
            Account account=new Account();
            account.setMoney(money);
            account.setName(name);
            account.setId(id);
            int t=iAccountService.update(account);
            if(t==1){ 
                return R.success(account);
            }
            else{ 
                return R.fail("更新失败");
            }
        }
    
        @PostMapping("")
        public R postAccount(@RequestParam(value="name") String name,
                                  @RequestParam(value="money")double money){ 
            Account account=new Account();
            account.setMoney(money);
            account.setName(name);
            int t=iAccountService.add(account);
            if(t==1){ 
                return R.success(account);
            }
            else{ 
                return R.fail("添加失败");
            }
        }
    }

以上Controller层通过Postman进行测试，完全正确。以上请求方式完全符合Restful风格的API。下边是一些测试结果截图：

1.  查询所有账户：  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODMzOTAyNQ_size_16_color_FFFFFF_t_70]
2.  查询指定账户  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODMzOTAyNQ_size_16_color_FFFFFF_t_70 1]
3.  更新账户信息  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODMzOTAyNQ_size_16_color_FFFFFF_t_70 2]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODMzOTAyNQ_size_16_color_FFFFFF_t_70]: /images/20220204/05cdfac4b18f4076aca5f9f464264cd7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODMzOTAyNQ_size_16_color_FFFFFF_t_70 1]: /images/20220204/4500a56e60624bb2b08422b9ba2038f2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODMzOTAyNQ_size_16_color_FFFFFF_t_70 2]: /images/20220204/ab6db344f64248c68ee6e58f06740572.png