SpringBoot-JdbcTemplates-MySQL

摘要：中用访问一准备工作运行二启动容器并进行表的创建启动查看镜像启动查看全部的镜像对应的启动连接创建数据库创建表三使用创建项目选择四配置相关的文件配置配置实体类类名为层接口实现类层接口实现类层控制层五使用进行测试没有安装的话可以

一. 准备工作

IDEA

docker : 运行MySql

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

启动 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
四. 配置相关的文件

配置 application.yml

   spring:
     datasource:
       driver-class-name: com.mysql.jdbc.Driver
       url: jdbc:mysql://localhost:3333/springboot
       username: root
       password: 123

配置实体类 类名为 Account

DAO层
接口 :

   public interface IAccountDao {
       int add(Account account);

       int update(Account account);

       int delete(int id);

       Account findAccountById(int id);

       List 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 query = jdbcTemplate.query(sql, new Object[]{id}, new BeanPropertyRowMapper(Account.class));
           if (query != null){
               return query.get(0);
           }
           return null;
       }
   
       @Override
       public List findAccountList() {
           String sql = "select id,name,money from account";
           List accounts = jdbcTemplate.query(sql,new Object[]{}, new BeanPropertyRowMapper(Account.class));
           return accounts;
       }
   
       public List find() {
           String sql = "select id,name,money from account";
           List accounts = jdbcTemplate.query(sql, new Object[]{}, new RowMapper() {
               List lists = new ArrayList();
               @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;
       }
   }

service层
接口 :

   public interface IAccountService {
       int add(Account account);
   
       int update(Account account);
   
       int delete(int id);
   
       Account findAccountById(int id);
   
       List 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 findAccountList() {
           return accountDao.find();
       } 

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 findAll(){
               System.out.println("query");
               return accountService.findAccountList();
           }
       }

五. 使用postman进行测试

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

全部测试通过;

六. 参考

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