Spring Boot 操作mysql 数据库

JdbcTemplate操作数据库

CREATE DATABASE test;
USE test;
CREATE TABLE `account` (
  `id` int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `money` double DEFAULT NULL
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
INSERT INTO `account`(id,name,money) VALUES ( 1,'zhangsan', 100.0);
INSERT INTO `account`(name,money) VALUES ('lisi', 100.0);
INSERT INTO `account`(name,money) VALUES ( 'wangwu', 100.0);

依赖

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.3.RELEASE</version>
    </parent>
    <dependencies>
        <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>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
    </dependencies>

首先要在application.properties 或者application.yml中配置mysql连接,这里以properties为例

server.port=8888
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/test
spring.datasource.username=root
spring.datasource.password=root

启动类 Application

package com.anjiplus;

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

@SpringBootApplication
public class Application {
    public static void main(String[] args) {
        SpringApplication.run(Application.class,args);
    }
}

pojo

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

    public int getId() {
        return id;
    }

    public String getName() {
        return name;
    }

    public double getMoney() {
        return money;
    }

    public void setId(int id) {
        this.id = id;
    }

    public void setName(String name) {
        this.name = name;
    }

    public void setMoney(double money) {
        this.money = money;
    }

    @Override
    public String toString() {
    return "Account{" +
            "id=" + id +
            ", name='" + name + '\'' +
            ", money=" + money +
            '}';
}
}

dao 层

接口
import com.anjiplus.pojo.Account;
import java.util.List;

public interface IAccountDao {
   public int add(Account account);
   public int update(Account account);
   public int delete(int id);
   public Account findAccountById(int id);
   public List<Account> findAccountList();
}
----------------------------------------------
实现类
import com.anjiplus.dao.IAccountDAO;
import com.anjiplus.pojo.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 AccountDaoImpl implements IAccountDao {
    @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 account where id = ?",id);
    }

    @Override
    public Account findAccountById(int id) {
        List<Account> query = jdbcTemplate.query("select * from account where id =?", new Object[]{id}, new BeanPropertyRowMapper(Account.class));
        if (query!=null && query.size()>0){
            Account account = query.get(0);
            return account;
        }else{
            return null;
        }
    }

    @Override
    public List<Account> findAccountList() {
        List<Account> query = jdbcTemplate.query("select * from account", new Object[]{}, new BeanPropertyRowMapper<>(Account.class));
        if(query.size()>0&& query !=null){
            return query;
        }else{
            return null;
        }
    }
}

service层

接口
import com.anjiplus.pojo.Account;
import java.util.List;

public interface IAccountService {
    public int add(Account account);
    public int update(Account account);
    public int delete(int id);
    public Account findAccountById(int id);
    public List<Account> findAccountList();
}
-----------------------------------
实现类
import com.anjiplus.dao.IAccountDAO;
import com.anjiplus.pojo.Account;
import com.anjiplus.service.IAccountService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;
@Service
public class AccoutService implements IAccountService {
    @Autowired
    IAccountDao 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();
    }
}

controller层

import com.anjiplus.pojo.Account;
import com.anjiplus.service.IAccountService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import java.util.List;

@RestController
@RequestMapping("/account")
public class AccountController {
    @Autowired
    IAccountService accountService;
    @RequestMapping(value = "/list",method = RequestMethod.GET)
    public List<Account>  getAccounts(){
        return accountService.findAccountList();
    }

    @RequestMapping(value = "/{id}",method = RequestMethod.GET)
    public Account getAccountById(@PathVariable("id") int id){
        return accountService.findAccountById(id);
    }

    @RequestMapping(value = "{id}",method = RequestMethod.PUT)
    public String updateAccount(@PathVariable("id") int id,@RequestParam(value = "name",required =true)String name,
                                @RequestParam(value = "money",required = true)Double money){
        Account account = new Account();
        account.setId(id);
        account.setName(name);
        account.setMoney(money);
        int t = accountService.update(account);
        if(t==1){
            return account.toString();
        }else{
            return "fail";
        }
    }
    @RequestMapping(value = "",method = RequestMethod.POST)
    public String postAccount(@RequestParam(value = "name")String name,
                              @RequestParam(value = "money")double money){
        Account account=new Account();
        account.setMoney(money);
        account.setName(name);
        int t= accountService.add(account);
        if(t==1){
            return account.toString();
        }else {
            return "fail";
        }
    }
}

启动后访问localhost:8888/account/*
使用postman进行测试