SpringBoot使用JdbcTemplate访问MySQL

准备工作

• 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. 查询所有账户：
   
2. 查询指定账户
   
3. 更新账户信息