Spring boot 与 MySQL 的使用

建一个user表

SET FOREIGN_KEY_CHECKS=0;

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int(10) unsigned zerofill NOT NULL AUTO_INCREMENT COMMENT '主键',
  `username` varchar(10) DEFAULT NULL COMMENT '用户名',
  `password` varchar(10) DEFAULT NULL COMMENT '密码',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

INSERT INTO `user` VALUES ('0000000001', '张三', '123');
INSERT INTO `user` VALUES ('0000000002', '李四', '123456');
INSERT INTO `user` VALUES ('0000000003', ' 王五', '1234567');

创建一个maven项目：

pom.xml文件

<?xml version="1.0" encoding="UTF-8"?>
<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/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.example</groupId>
    <artifactId>spring-boot-demo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>jar</packaging>

    <name>spring-boot-demo1</name>
    <description>Demo project for Spring Boot</description>

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

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <java.version>1.8</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</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <!-- JPA Data (包含 Repositories, Entities, Hibernate) -->

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

        <!-- mysql连接驱动 -->

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>


</project>

在 src/main/resources/ 下创建 application.properties文件（编码改为utf8）

spring.jpa.hibernate.ddl-auto=update
#none mysql默认，不改变表结构，其他数据库多数为create-drop
#update 加载hibernate时，自动更新数据库结构
#create 每次加载hibernate，重新创建数据库表结构，
#create-drop 加载hibernate时创建，退出时删除表结构
spring.datasource.url=jdbc:mysql://localhost:3306/test
spring.datasource.username=root
spring.datasource.password=root

创建实体类 User.java

package com.demo.bean;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;

@Entity
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Integer id;

    private String username;

    private String password;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

}

创建 repository(类似于dao的东西吧~)

package com.demo.repository;

import org.springframework.data.repository.CrudRepository;

import com.demo.bean.User;

//由Spring自动实现，包含CRUD(Create, Read, Update, Delete)
public interface UserRepository extends CrudRepository<User, Long>{

}

创建一个Controller，UserController.java

package com.demo.controller;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;

import com.demo.bean.User;
import com.demo.repository.UserRepository;

@Controller
@RequestMapping(path = "/user")
public class UserController {
    @Autowired // 自动注入
    private UserRepository userRepository;

    @GetMapping(path = "/add") // 只响应GET请求
    public @ResponseBody String addUser(@RequestParam String username, @RequestParam String password) {
        // @ResponBody表示返回String类型数据
        // @RequestParam表示GET或POST请求的参数
        User user = new User();
        user.setUsername(username);
        user.setPassword(password);
        User savedUser = userRepository.save(user);
        if (savedUser == null) {
            return "error";
        } else {
            return "add success!";
        }
    }

    @GetMapping(path = "/all")
    public @ResponseBody Iterable<User> getAllUsers() {
        // 返回Json格式或xml格式
        return userRepository.findAll();
    }
}

创建Application类

package com.demo;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

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

运行程序 Run As -> Maven build... -> spring-boot:run Run

测试，浏览器输入 http://localhost:8080/user/add?username=admin&password=admin

返回 add success，回来看控制台的sql

浏览器输入 http://localhost:8080/user/getAllUser，获取全部用户，可在控制台查看sql语句

源码