spring-boot(二)数据库操作

分类:spring-boot
阅读:2170
作者:majingjing
发布:2016-12-21 15:50

之前介绍过了spring-boot的web应用搭建
下面介绍下数据库的操作,方式有很多种,这里就拿spring-jdbc来简单介绍下如何操作DATASOURCE

项目搭建依赖代码请参考文章: spring-boot(一) web应用
spring-boot太强大了,基本上帮我们集成了各种框架.当我们想要使用某种框架的时候,就直接引入依赖即可

项目结构
QQ截图20161221154120.png

  1. pom引入依赖

    1. <dependency>
    2. <groupId>org.springframework.boot</groupId>
    3. <artifactId>spring-boot-starter-jdbc</artifactId>
    4. </dependency>
    5. <dependency>
    6. <groupId>mysql</groupId>
    7. <artifactId>mysql-connector-java</artifactId>
    8. </dependency>
  2. 编写application.properties,加入连接数据库的配置

    1. spring.datasource.driver-class-name=com.mysql.jdbc.Driver
    2. spring.datasource.url=jdbc:mysql://localhost:3306/springboot?useUnicode=true&characterEncoding=UTF-8
    3. spring.datasource.username=root
    4. spring.datasource.password=root
  3. 建立数据库,脚本见附件

    1. SET FOREIGN_KEY_CHECKS=0;
    2. -- ----------------------------
    3. -- Table structure for `hello`
    4. -- ----------------------------
    5. DROP TABLE IF EXISTS `hello`;
    6. CREATE TABLE `hello` (
    7. `id` int(11) NOT NULL AUTO_INCREMENT,
    8. `msg` varchar(255) DEFAULT NULL,
    9. `date` datetime DEFAULT NULL,
    10. PRIMARY KEY (`id`)
    11. ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
    12. -- ----------------------------
    13. -- Records of hello
    14. -- ----------------------------
    15. INSERT INTO `hello` VALUES ('1', '你好', '2016-12-21 15:32:33');
    16. INSERT INTO `hello` VALUES ('2', '我好', '2016-12-21 15:32:45');
    17. INSERT INTO `hello` VALUES ('3', '大家好', '2016-12-21 15:32:54');
  4. 编写数据库成java代码,此处我已经写了通用的dao层代码

    1. package hello.dao;
    2. import java.sql.ResultSet;
    3. import java.sql.SQLException;
    4. import java.util.ArrayList;
    5. import java.util.List;
    6. import org.springframework.beans.factory.annotation.Autowired;
    7. import org.springframework.dao.DataAccessException;
    8. import org.springframework.jdbc.core.BeanPropertyRowMapper;
    9. import org.springframework.jdbc.core.JdbcTemplate;
    10. import org.springframework.jdbc.core.ResultSetExtractor;
    11. import org.springframework.stereotype.Repository;
    12. import org.springframework.transaction.annotation.Transactional;
    13. /**
    14. * @author majinding888@foxmail.com
    15. * @date 2016-11-14 下午1:36:25
    16. */
    17. @Repository
    18. public class Dao {
    19. @Autowired
    20. private JdbcTemplate jdbcTemplate;
    21. // @Transactional(readOnly = true)
    22. public <T> List<T> queryForList(final Class<T> clazz, String sql, Object... args) {
    23. final List<T> items = new ArrayList<T>();
    24. jdbcTemplate.query(sql, args, new ResultSetExtractor<Object>() {
    25. @SuppressWarnings({ "rawtypes", "unchecked" })
    26. public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
    27. int currentRow = 0;
    28. while (rs.next()) {
    29. items.add((T) new BeanPropertyRowMapper(clazz).mapRow(rs, currentRow));
    30. currentRow++;
    31. }
    32. return items;
    33. }
    34. });
    35. return items;
    36. }
    37. @SuppressWarnings({ "rawtypes", "unchecked" })
    38. // @Transactional(readOnly = true)
    39. public <T> T queryForObject(final Class<T> clazz, String sql, Object... args) {
    40. if (clazz == String.class || clazz == Integer.class) {
    41. return (T) jdbcTemplate.queryForObject(sql, clazz, args);
    42. }
    43. return (T) jdbcTemplate.query(sql, args, new ResultSetExtractor<Object>() {
    44. public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
    45. if (rs.next()) {
    46. return (T) new BeanPropertyRowMapper(clazz).mapRow(rs, 0);
    47. } else {
    48. return null;
    49. }
    50. }
    51. });
    52. }
    53. @Transactional
    54. public int update(String sql, Object... args) {
    55. return jdbcTemplate.update(sql, args);
    56. }
    57. }
  5. 编写service层

    1. @Service
    2. public class HelloService {
    3. @Autowired
    4. Dao dao;
    5. public List<HelloBean> queryHellos() {
    6. List<HelloBean> hellos = dao.queryForList(HelloBean.class, "select * from hello ");
    7. return hellos;
    8. }
    9. public HelloBean queryHello(Integer id) {
    10. HelloBean hello = dao.queryForObject(HelloBean.class, "select * from hello where id = ? ", id);
    11. return hello;
    12. }
    13. }
  6. 编写controller

    1. @Controller
    2. public class HelloController {
    3. @Autowired
    4. HelloService service;
    5. @RequestMapping("/hello/list")
    6. public String list(Model model) {
    7. model.addAttribute("list", service.queryHellos());
    8. return "list";
    9. }
    10. @RequestMapping("/hello/info")
    11. public String info(Integer id, Model model) {
    12. model.addAttribute("v", service.queryHello(id));
    13. return "info";
    14. }
    15. 省略...
  7. 编写页面

    • list.html

      1. <!DOCTYPE html>
      2. <html>
      3. <head>
      4. <title>list.html</title>
      5. <meta name="keywords" content="keyword1,keyword2,keyword3" />
      6. <meta name="description" content="this is my page" />
      7. <meta name="content-type" content="text/html; charset=UTF-8" />
      8. </head>
      9. <body>
      10. <table>
      11. <thead>
      12. <tr>
      13. <th>编号</th>
      14. <th>消息</th>
      15. <th>时间</th>
      16. </tr>
      17. </thead>
      18. <tbody>
      19. <tr th:each="v,vStat:${list}">
      20. <td th:text="${v.id}">1</td>
      21. <td th:text="${v.msg}">你好</td>
      22. <td th:text="${v.date}">2016-12-22</td>
      23. </tr>
      24. </tbody>
      25. </table>
      26. </body>
      27. </html>
    • info.html

      1. <!DOCTYPE html>
      2. <html>
      3. <head>
      4. <title>info.html</title>
      5. <meta name="keywords" content="keyword1,keyword2,keyword3" />
      6. <meta name="description" content="this is my page" />
      7. <meta name="content-type" content="text/html; charset=UTF-8" />
      8. </head>
      9. <body>
      10. <div th:text="${v.id}">编号</div>
      11. <div th:text="${v.msg}">消息</div>
      12. <div th:text="${v.date}">时间</div>
      13. </body>
      14. </html>

到此整个例子就编写完成了,启动下主启动类, 打开浏览器看下效果
QQ截图20161221161428.png
QQ截图20161221161440.png

源代码附件: my-springboot-2.zip