SpringBoot之JDBC（I）

JDBC 连接数据库

1.属性配置文件（application.properties）

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

2.pom.xml 配置maven依赖

<!-- 5.jdbc -->
<!-- MYSQL -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
 </dependency>
 <!-- Spring Boot JDBC -->
 <dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
 </dependency>

User.java

package cn.wuyang.springboot.entity;

public class User {

    private String user_id;
    private String user_name;
    public String getUser_id() {
        return user_id;
    }
    @Override
    public String toString() {
        return "User [user_id=" + user_id + ", user_name=" + user_name + "]";
    }
    public void setUser_id(String user_id) {
        this.user_id = user_id;
    }
    public String getUser_name() {
        return user_name;
    }
    public void setUser_name(String user_name) {
        this.user_name = user_name;
    }

}

UserController.java

package cn.wuyang.springboot.controller;

import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import cn.wuyang.springboot.entity.User;
import cn.wuyang.springboot.service.UserService;

@RestController
@RequestMapping("/user")
public class UserController {
    private static final Logger logger = LoggerFactory.getLogger(UserController.class);

    @Autowired
    private UserService userService;

    @RequestMapping("/list")
    public List<User> getUsers(){
        logger.info("从数据库读取Student集合");
        return userService.getList();
    }

}

UserService.java

package cn.wuyang.springboot.service;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;

import cn.wuyang.springboot.entity.User;

@Service
public class UserService {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public List<User> getList(){
        String sql = "SELECT *   FROM user_info";
        return (List<User>) jdbcTemplate.query(sql, new RowMapper<User>(){
            @Override
            public User mapRow(ResultSet rs, int rowNum) throws SQLException {
                User user = new User();
                user.setUser_id(rs.getString("user_id"));
                user.setUser_name(rs.getString("user_name"));
                return user;
            }
        });
    }

}

user_info.sql

/*
Navicat MySQL Data Transfer

Source Server         : Android
Source Server Version : 50714
Source Host           : localhost:3306
Source Database       : springboot

Target Server Type    : MYSQL
Target Server Version : 50714
File Encoding         : 65001

Date: 2017-08-17 14:31:02
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for user_info
-- ----------------------------
DROP TABLE IF EXISTS `user_info`;
CREATE TABLE `user_info` (
  `user_id` varchar(255) COLLATE utf8_bin NOT NULL,
  `user_name` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- ----------------------------
-- Records of user_info
-- ----------------------------
INSERT INTO `user_info` VALUES ('1', 'wuyang');
INSERT INTO `user_info` VALUES ('2', 'admin');

然后启动项目，访问地址：http://127.0.0.1:8080/user/list 响应结果如下：

[{"user_id":"1","user_name":"wuyang"},{"user_id":"2","user_name":"admin"}]