SpringBoot-JdbcTemplates-MySQL

SpringBoot 中用 JdbcTemplate 访问MySQL

一. 准备工作

  • IDEA
  • docker : 运行MySql

二. 启动 docker mysql 容器并进行表的创建

  1. 启动 docker,查看docker mysql 镜像,启动

    - docker images : 查看全部的docker 镜像
    - docker run -d -p 3333:3306 -e MYSQL_ROOT_PASSWORD=123 mysql:letest(mysql对应的images : id)
    - 启动 Navicat 连接mysql
    - 创建数据库 springboot,创建表
        create table account(
            id int(11) not null auto_increment,
            name varchar(20) not null,
            money double default null,
            premary 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');

三. 使用IDEA 创建 springBoot项目,选择 web,MySQL,jpa
四. 配置相关的文件

  1. 配置 application.yml

    spring:
         datasource:
           driver-class-name: com.mysql.jdbc.Driver
           url: jdbc:mysql://localhost:3333/springboot
           username: root
           password: 123
  2. 配置实体类 类名为 Account
  3. DAO层
    接口 :

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

    实现类 :

    @Repository
       public class AccountDao implements IAccountDao {
           @Autowired
           private JdbcTemplate jdbcTemplate;
           @Override
           public int add(Account account) {
               int add = jdbcTemplate.update("INSERT INTO account(NAME,money) values(?,?)", new Object[]{account.getName(), account.getMoney()});
               return add;
           }
       
           @Override
           public int update(Account account) {
               String sql = " update account set name = ?,money = ? where id = ?";
               int update = jdbcTemplate.update(sql, new Object[]{account.getName(),account.getMoney(),account.getId()});
               return update;
           }
       
           @Override
           public int delete(int id) {
               String sql = "delete from account where id = ?";
               return jdbcTemplate.update(sql,new Object[]{id});
           }
       
           @Override
           public Account findAccountById(int id) {
               String sql = "select id,name,money from account where id = ?";
               List<Account> query = jdbcTemplate.query(sql, new Object[]{id}, new BeanPropertyRowMapper(Account.class));
               if (query != null){
                   return query.get(0);
               }
               return null;
           }
       
           @Override
           public List<Account> findAccountList() {
               String sql = "select id,name,money from account";
               List<Account> accounts = jdbcTemplate.query(sql,new Object[]{}, new BeanPropertyRowMapper(Account.class));
               return accounts;
           }
       
           public List<Account> find() {
               String sql = "select id,name,money from account";
               List<Account> accounts = jdbcTemplate.query(sql, new Object[]{}, new RowMapper<Account>() {
                   List<Account> lists = new ArrayList<Account>();
                   @Override
                   public Account mapRow(ResultSet resultSet, int i) throws SQLException {
                       Account account = new Account();
                       account.setId(resultSet.getInt(1));
                       account.setName(resultSet.getString(2));
                       account.setMoney(resultSet.getDouble(3));
                       return account;
                   }
               });
               return accounts;
           }
       }
  4. service层
    接口 :

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

    实现类 :

    @Service
       public class AccountService implements IAccountService {
           @Autowired
           private 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.find();
           }
  5. controller层
    控制层

    @RestController
           @RequestMapping("account")
           public class AccountController {
           
               @Autowired
               private IAccountService accountService;
           
               @RequestMapping(value = "add",method = RequestMethod.POST)
               public int insert(@RequestParam(value = "name")String name,@RequestParam(value = "money") Double money){
                   Account account = new Account();
                   account.setName(name);
                   account.setMoney(money);
                   System.out.println("add");
                   int add = accountService.add(account);
                   return add;
               }
               @RequestMapping(value = "delete",method = RequestMethod.GET)
               public int delete(@RequestParam("id")  int id){
                   System.out.println("delete");
                   return accountService.delete(id);
               }
               @RequestMapping("update")
               public int modify(@RequestParam("id") int id,@RequestParam("name") String name,@RequestParam("money") Double money){
                   System.out.println("update");
                   Account account = new Account();
                   account.setId(id);
                   account.setName(name);
                   account.setMoney(money);
                   return accountService.update(account);
               }
               @RequestMapping(value = "/{id}",method = RequestMethod.GET)
               public Account findOne(@PathVariable("id") int id){
                   System.out.println("queryById");
                   return accountService.findAccountById(id);
               }
               @RequestMapping(value = "findAll",method = RequestMethod.GET)
               public List<Account> findAll(){
                   System.out.println("query");
                   return accountService.findAccountList();
               }
           }

五. 使用postman进行测试

没有安装的话可以使用 brew cask install postman; 安装完成后直接打开就可以了

全部测试通过;

六. 参考

[SpringBoot非官方教程 | 第三篇:SpringBoot用JdbcTemplates访问Mysql][1]

相关推荐