Spring Boot 揭秘与实战之数据存储篇 - MySQL

本文讲解 Spring Boot 基础下，如何使用 JDBC，配置数据源和通过 JdbcTemplate 编写数据访问。
原文地址：Spring Boot 揭秘与实战（二） 数据存储篇 - MySQL
博客地址：http://blog.720ui.com/

环境依赖

修改 POM 文件，添加spring-boot-starter-jdbc依赖。

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

添加mysql依赖。

<dependency>  <groupId>mysql</groupId>  <artifactId>mysql-connector-java</artifactId>  <version>5.1.35</version></dependency><dependency>  <groupId>com.alibaba</groupId>  <artifactId>druid</artifactId>  <version>1.0.14</version></dependency>

数据源

方案一 使用 Spring Boot 默认配置

使用 Spring Boot 默认配置，不需要在创建 dataSource 和 jdbcTemplate 的 Bean。

在 src/main/resources/application.properties 中配置数据源信息。

spring.datasource.driver-class-name=com.mysql.jdbc.Driverspring.datasource.url=jdbc:mysql://localhost:3307/springboot_dbspring.datasource.username=rootspring.datasource.password=root

这里springboot_db是mysql数据库的名字

方案二 手动创建

在 src/main/resources/config/source.properties 中配置数据源信息。

#mysqlsource.driverClassName = com.mysql.jdbc.Driversource.url = jdbc:mysql://localhost:3306/springboot_dbsource.username = rootsource.password = root

通过 Java Config 创建 dataSource 和jdbcTemplate。

@Configuration@EnableTransactionManagement@PropertySource(value = {"classpath:config/source.properties"})public class BeanConfig {    @Autowired    private Environment env;    @Bean(destroyMethod = "close")    public DataSource dataSource() {        DruidDataSource dataSource = new DruidDataSource();        dataSource.setDriverClassName(env.getProperty("source.driverClassName").trim());        dataSource.setUrl(env.getProperty("source.url").trim());        dataSource.setUsername(env.getProperty("source.username").trim());        dataSource.setPassword(env.getProperty("source.password").trim());        return dataSource;    }    @Bean    public JdbcTemplate jdbcTemplate() {        JdbcTemplate jdbcTemplate = new JdbcTemplate();        jdbcTemplate.setDataSource(dataSource());        return jdbcTemplate;    }}

连接JNDI数据源

当你将应用部署于应用服务器上的时候想让数据源由应用服务器管理，那么可以使用如下配置方式引入JNDI数据源。

spring.datasource.jndi-name=java:jboss/datasources/springboot_db

脚本初始化

先初始化需要用到的SQL脚本。

CREATE DATABASE /*!32312 IF NOT EXISTS*/`springboot_db` /*!40100 DEFAULT CHARACTER SET utf8 */;USE `springboot_db`;DROP TABLE IF EXISTS `t_author`;CREATE TABLE `t_author` (  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '用户ID',  `real_name` varchar(32) NOT NULL COMMENT '用户名称',  `nick_name` varchar(32) NOT NULL COMMENT '用户匿名',  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

使用JdbcTemplate操作

实体对象

public class Author {    private Long id;    private String realName;    private String nickName;    // SET和GET方法}

DAO相关

public interface AuthorDao {    int add(Author author);    int update(Author author);    int delete(Long id);    Author findAuthor(Long id);    List<Author> findAuthorList();}

我们来定义实现类，通过JdbcTemplate定义的数据访问操作。

@Repositorypublic class AuthorDaoImpl implements AuthorDao {    @Autowired    private JdbcTemplate jdbcTemplate;    @Override    public int add(Author author) {        return jdbcTemplate.update("insert into t_author(real_name, nick_name) values(?, ?)",                 author.getRealName(), author.getNickName());    }    @Override    public int update(Author author) {        return jdbcTemplate.update("update t_author set real_name = ?, nick_name = ? where id = ?",                 new Object[]{author.getRealName(), author.getNickName(), author.getId()});          }    @Override    public int delete(Long id) {        return jdbcTemplate.update("delete from t_author where id = ?", id);    }    @Override    public Author findAuthor(Long id) {        List<Author> list = jdbcTemplate.query("select * from t_author where id = ?", new Object[]{id}, new BeanPropertyRowMapper<Author>(Author.class));        if(null != list && list.size()>0){            Author auhtor = list.get(0);            return auhtor;        }else{            return null;        }    }    @Override    public List<Author> findAuthorList() {        List<Author> list = jdbcTemplate.query("select * from t_author", new Object[]{}, new BeanPropertyRowMapper<Author>(Author.class));        return list;    }}

Service相关

public interface AuthorService {    int add(Author author);    int update(Author author);    int delete(Long id);    Author findAuthor(Long id);    List<Author> findAuthorList();}

我们来定义实现类，Service层调用Dao层的方法，这个是典型的套路。

@Service("authorService")public class AuthorServiceImpl implements AuthorService {    @Autowired    private AuthorDao authorDao;    @Override    public int add(Author author) {        return this.authorDao.add(author);    }    @Override    public int update(Author author) {        return this.authorDao.update(author);          }    @Override    public int delete(Long id) {        return this.authorDao.delete(id);    }    @Override    public Author findAuthor(Long id) {        return this.authorDao.findAuthor(id);    }    @Override    public List<Author> findAuthorList() {        return this.authorDao.findAuthorList();    }}

Controller相关

为了展现效果，我们先定义一组简单的　RESTful API 接口进行测试。

@RestController@RequestMapping(value="/data/jdbc/author")public class AuthorController {  @Autowired  private AuthorService authorService;  /**   * 查询用户列表   */  @RequestMapping(method = RequestMethod.GET)  public Map<String,Object> getAuthorList(HttpServletRequest request) {            List<Author> authorList = this.authorService.findAuthorList();    Map<String,Object> param = new HashMap<String,Object>();    param.put("total", authorList.size());    param.put("rows", authorList);    return param;  }  /**   * 查询用户信息   */  @RequestMapping(value = "/{userId:\\d+}", method = RequestMethod.GET)  public Author getAuthor(@PathVariable Long userId, HttpServletRequest request) {    Author author = this.authorService.findAuthor(userId);    if(author == null){        throw new RuntimeException("查询错误");    }    return author;  }  /**   * 新增方法   */  @RequestMapping(method = RequestMethod.POST)  public void add(@RequestBody JSONObject jsonObject) {    String userId = jsonObject.getString("user_id");    String realName = jsonObject.getString("real_name");    String nickName = jsonObject.getString("nick_name");    Author author = new Author();    if (author!=null) {        author.setId(Long.valueOf(userId));    }    author.setRealName(realName);    author.setNickName(nickName);    try{        this.authorService.add(author);    }catch(Exception e){        e.printStackTrace();        throw new RuntimeException("新增错误");    }  }  /**   * 更新方法   */  @RequestMapping(value = "/{userId:\\d+}", method = RequestMethod.PUT)    public void update(@PathVariable Long userId, @RequestBody JSONObject jsonObject) {    Author author = this.authorService.findAuthor(userId);    String realName = jsonObject.getString("real_name");    String nickName = jsonObject.getString("nick_name");    author.setRealName(realName);    author.setNickName(nickName);    try{        this.authorService.update(author);    }catch(Exception e){        e.printStackTrace();        throw new RuntimeException("更新错误");    }   }  /**   * 删除方法   */  @RequestMapping(value = "/{userId:\\d+}", method = RequestMethod.DELETE)    public void delete(@PathVariable Long userId) {    try{        this.authorService.delete(userId);    }catch(Exception e){        throw new RuntimeException("删除错误");    }  }}

注意会出现 jdbcTemplate变量无法绑定JdbcTemplate类的错误，原因是在pom文件中缺依赖文件：

       <dependency>            <groupId>org.springframework.boot</groupId>            <artifactId>spring-boot-starter-jdbc</artifactId>        </dependency>        <dependency>            <groupId>mysql</groupId>            <artifactId>mysql-connector-java</artifactId>            <version>5.1.35</version>        </dependency>

总结

通过，上面这个简单的案例，我们发现 Spring Boot 仍然秉承了 Spring 框架的一贯套路，并简化 Spring 应用的初始搭建以及开发过程。