springboot学习笔记(二):基于MySql数据库的JDBC操作

1 , 连接数据库

SpringBoot的Web应用中，基于MySql数据库的JDBC操作

• JDBC 连接数据库主要配置

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

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

JNDI配置项

spring.datasource.jndi-name= # JNDI location of the datasource. Class, url, username & password are ignored when set.

POM配置Maven依赖

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

• 实例

1 , 添加依赖

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>Demo</groupId>
    <artifactId>SpringBoot</artifactId>
    <packaging>war</packaging>
    <version>0.0.1-SNAPSHOT</version>
    <name>SpringBoot Maven Webapp</name>
    <url>http://maven.apache.org</url>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.4.0.RELEASE</version>
    </parent>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <java.version>1.7</java.version>
    </properties>

    <dependencies>

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

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

        <!-- https://mvnrepository.com/artifact/org.springframework.boot/spring-boot-starter-logging -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-logging</artifactId>
        </dependency>

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

    </dependencies>

    <build>
        <finalName>SpringBoot</finalName>

        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>2.0.2</version>
                <configuration>
                    <source>1.7</source>
                    <target>1.7</target>
                </configuration>
            </plugin>
        </plugins>

    </build>
</project>

2 , 实体类

package com.springboot.jdbc.entity;

/**
 * Created by nickzhang on 2016/9/1.
 */
public class UserInfo {

    private String name;
    private String age;
    private String address;


    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getAge() {
        return age;
    }

    public void setAge(String age) {
        this.age = age;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    @Override
    public String toString() {
        return "UserInfo{" +
                "name='" + name + '\'' +
                ", age='" + age + '\'' +
                ", address='" + address + '\'' +
                '}';
    }
}

3 , Service

package com.springboot.jdbc.Service;

import com.springboot.jdbc.entity.UserInfo;
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 java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

/**
 * Created by nickzhang on 2016/9/1.
 */
@Service
public class UserInfoService {

    @Autowired
    private JdbcTemplate jdbcTemplate;


    public List<UserInfo> query(){
        String sql = "select name,age,address from tb_userinfo";

        List<UserInfo> userinfoList = jdbcTemplate.query(sql, new RowMapper<UserInfo>() {
            @Override
            public UserInfo mapRow(ResultSet resultSet, int i) throws SQLException {
                UserInfo userinfo = new UserInfo();
                userinfo.setName(resultSet.getString("name"));
                userinfo.setAge(resultSet.getString("age"));
                userinfo.setAddress(resultSet.getString("address"));
                return userinfo;
            }
        });

        return userinfoList;
    }

}

4 , Controller

package com.springboot.jdbc;

import com.springboot.jdbc.Service.UserInfoService;
import com.springboot.jdbc.entity.UserInfo;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;

/**
 * Created by nickzhang on 2016/9/1.
 */
@RestController
@SpringBootApplication
public class SpringBootJDBCTest {

    private static final Logger logger = LoggerFactory.getLogger(SpringBootJDBCTest.class);

    @Autowired
    private UserInfoService userInfoService;

    @RequestMapping("/query")
    public List<UserInfo> query(){
        logger.info(">>>>从数据库查询userinfo数据>>>>");
        return userInfoService.query();
    }

    public static void main(String[] args) {
        SpringApplication.run(SpringBootJDBCTest.class,args);
    }
}

5 , application.properties配置信息

server.port=8011
spring.datasource.url=jdbc:mysql://localhost:3306/springbootdb
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

spring.datasource.max-idle=10
spring.datasource.max-wait=10000
spring.datasource.min-idle=5
spring.datasource.initial-size=5
spring.datasource.validation-query=SELECT 1
spring.datasource.test-on-borrow=false
spring.datasource.test-while-idle=true
spring.datasource.time-between-eviction-runs-millis=18800
spring.datasource.jdbc-interceptors=ConnectionState;SlowQueryReport(threshold=0)

目录结构

6 , 数据库表

CREATE TABLE `tb_userinfo` (
  `name` varchar(255) NOT NULL,
  `age` varchar(255) NOT NULL,
  `address` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

7 , logback.xml日志配置信息

<?xml version="1.0" encoding="UTF-8"?>
<configuration>
    <include resource="org/springframework/boot/logging/logback/base.xml"/>
    <logger name="org.springframework.boot" level="DEBUG"/>
</configuration>

剩下的就是运行服务 , 浏览器访问http://localhost:8011/query

实测返回结果为:

[
    {
        "name": "张三",
        "age": "27",
        "address": "广东深圳"
    },
    {
        "name": "李四",
        "age": "25",
        "address": "湖北武汉"
    },
    {
        "name": "王五",
        "age": "29",
        "address": "首都北京"
    }
]