企业级 SpringBoot 教程 （三）SpringBoot用JdbcTemplates访问Mysql

准备工作

• jdk 1.8
• maven 3.0
• idea
• mysql

初始化mysql:

1 2 3 4 5 6 7 8 9 10 11

-- create table `account` DROP TABLE `account` IF EXISTS 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的依赖：

1 2 3 4

<dependency>             <groupId>org.springframework.boot</groupId>             <artifactId>spring-boot-starter-jdbc</artifactId>         </dependency>

　　引入mysql连接类和连接池：

1 2 3 4 5 6 7 8 9 10 11

<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>

　　开启web:

1 2 3 4

<dependency>             <groupId>org.springframework.boot</groupId>             <artifactId>spring-boot-starter-web</artifactId>         </dependency>

　　

配置相关文件

在application.properties文件配置mysql的驱动类，数据库地址，数据库账号、密码信息。

1 2 3 4

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

　　通过引入这些依赖和配置一些基本信息，springboot就可以访问数据库类。

具体编码

实体类

1 2 3 4 5 6 7 8

public class Account {     private int id ;     private String name ;     private double money;   ....省略了getter. setter   }

　　

dao层

1 2 3 4 5 6 7 8 9 10 11

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

　　具体的实现类：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58

package com.forezp.dao.impl;   import com.forezp.dao.IAccountDAO; import com.forezp.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;   /**  * Created by fangzhipeng on 2017/4/20.  */ @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 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;         }     } }