SpringBoot用JdbcTemplates访问Mysql

初始化mysql:

CREATE TABLE `account` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `money` double DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
INSERT INTO `account` VALUES ('1', 'aaa', '1000');
INSERT INTO `account` VALUES ('2', 'bbb', '1000');
INSERT INTO `account` VALUES ('3', 'ccc', '1000');

创建工程

引入依赖：

在pom文件引入spring-boot-starter-jdbc及mysql连接类和连接池依赖：

<dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.0.29</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>

数据库配置相关文件

spring:
  datasource:
    driver-class-name: com.mysql.jdbc.Driver
    url: jdbc:mysql://localhost:3306/xiami_db?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC
    username: root
    password: changeme_123

具体编码

实体类

package com.xiami.jdbctemplates.entity;

import lombok.Getter;
import lombok.Setter;

/**
 * 账户
 */
@Getter
@Setter
public class Account {
    private int id;
    private String name;
    private double money;
}

dao层

package com.xiami.jdbctemplates.dao;

import com.xiami.jdbctemplates.entity.Account;

import java.util.List;

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

dao实现类：

package com.xiami.jdbctemplates.dao.impl;

import com.xiami.jdbctemplates.dao.IAccountDao;
import com.xiami.jdbctemplates.entity.Account;
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.List;

@Repository
public class IAccountDaoImpl implements IAccountDao {

    @Autowired
    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 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){
            return list.get(0);
        }else{
            return null;
        }
    }

    @Override
    public List<Account> fnidAllAccont() {
        return jdbcTemplate.query("select * from account",
                new Object[]{},new BeanPropertyRowMapper<>(Account.class));
    }
}

service层

package com.xiami.jdbctemplates.service;

import com.xiami.jdbctemplates.entity.Account;

import java.util.List;

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

service实现类：

package com.xiami.jdbctemplates.service.impl;

import com.xiami.jdbctemplates.dao.IAccountDao;
import com.xiami.jdbctemplates.entity.Account;
import com.xiami.jdbctemplates.service.IAccountService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class IAccountServiceImpl implements IAccountService {

    @Autowired
    IAccountDao iAccountDao;

    @Override
    public int add(Account account) {
        return iAccountDao.add(account);
    }

    @Override
    public int update(Account account) {
        return iAccountDao.update(account);
    }

    @Override
    public int delete(int id) {
        return iAccountDao.delete(id);
    }

    @Override
    public Account findAccountById(int id) {
        return iAccountDao.findAccountById(id);
    }

    @Override
    public List<Account> fnidAllAccont() {
        return iAccountDao.fnidAllAccont();
    }
}

restful api controller层

package com.xiami.jdbctemplates.controller;

import com.xiami.jdbctemplates.entity.Account;
import com.xiami.jdbctemplates.service.IAccountService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;

import java.util.List;

@Controller
@RequestMapping("/account")
public class IAccountController {

    @Autowired
    IAccountService iAccountService;

    @RequestMapping(value= "/{id}",method = RequestMethod.GET)
    private void add(@PathVariable("id") int id){
        Account account = new Account();
        account.setId(id);
        account.setName("虾米");
        account.setMoney(100);
        int count = iAccountService.add(account);
    }

    @RequestMapping(value= "/update/{id}",method = RequestMethod.GET)
    private void update(@PathVariable("id") int id){
        Account account = new Account();
        account.setName("虾米");
        account.setMoney(100);
        account.setId(id);
        int count = iAccountService.update(account);
    }

    @RequestMapping(value= "/delete/{id}",method = RequestMethod.GET)
    private void delete(@PathVariable("id") int id){
        int count = iAccountService.delete(id);
    }

    @RequestMapping(value = "/searchOneResult/{id}", method = RequestMethod.GET)
    private void searchReslut(@PathVariable("id") int id){
        Account account = iAccountService.findAccountById(id);
    }

    @RequestMapping(value = "/searchAllReasult")
    private void searchReasult(){
        List<Account> list = iAccountService.fnidAllAccont();
    }


}