本文讲解 Spring Boot 基础下,如何使用 JDBC,配置数据源和通过 JdbcTemplate 编写数据访问。
修改 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 默认配置,不需要在创建 dataSource 和 jdbcTemplate 的 Bean。
在 src/main/resources/application.properties 中配置数据源信息。
spring.datasource.driver-class-name=com.mysql.jdbc.Driver spring.datasource.url=jdbc:mysql://localhost:3307/springboot_db spring.datasource.username=root spring.datasource.password=root
在 src/main/resources/config/source.properties 中配置数据源信息。
# mysql source.driverClassName = com.mysql.jdbc.Driver source.url = jdbc:mysql://localhost:3306/springboot_db source.username = root source.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; } }
先初始化需要用到的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;
public class Author { private Long id; private String realName; private String nickName; // SET和GET方法 }
public interface AuthorDao { int add(Author author); int update(Author author); int delete(Long id); Author findAuthor(Long id); List<Author> findAuthorList(); }
我们来定义实现类,通过JdbcTemplate定义的数据访问操作。
@Repository public 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.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; } }
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(); } }
为了展现效果,我们先定义一组简单的 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("删除错误"); } } }
通过,上面这个简单的案例,我们发现 Spring Boot 仍然秉承了 Spring 框架的一贯套路,并简化 Spring 应用的初始搭建以及开发过程。