Spring连接数据库MySQL

参考spring官方指导：https://spring.io/guides/gs/accessing-data-mysql/

准备环境：

1）安装MySQL，可以直接官网下载一个免安装版的，可以参考这篇文章，在启动mysql服务（net start mysql）的过程中有碰到启动不起来，报错：服务没有响应控制功能，这个是因为少了VC的运行库，可以到微软官网（https://support.microsoft.com/en-us/help/2977003/the-latest-supported-visual-c-downloads）上下载安装下。

2）初始化项目： https://start.spring.io

选择：Spring Web, Spring Data JPA,  MySQL Driver这个三个生成初始化项目然后下载下来。

3）创建数据库

mysql> create database db_example;
Query OK, 1 row affected (0.08 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| db_example         |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> create user 'springuser'@'%' identified by 'ThePassword';
Query OK, 0 rows affected (0.09 sec)

mysql> grant all on db_example.* to 'springuser'@'%';
Query OK, 0 rows affected (0.08 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| db_example         |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.03 sec)

4）项目中配置数据库的连接参数src/main/resources/application.properties

spring.jpa.hibernate.ddl-auto=update
spring.datasource.url=jdbc:mysql://${MYSQL_HOST:localhost}:3306/db_example
spring.datasource.username=springuser
spring.datasource.password=ThePassword
spring.datasource.driver-class-name =com.mysql.jdbc.Driver
#spring.jpa.show-sql: true

5）创建实体（Entity）模型：src/main/java/com/example/accessingdatamysql/User.java

package com.example.accessingdatamysql;

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

@Entity // This tells Hibernate to make a table out of this class
public class User {
  @Id
  @GeneratedValue(strategy=GenerationType.AUTO)
  private Integer id;

  private String name;

  private String email;

  public Integer getId() {
    return id;
  }

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

  public String getName() {
    return name;
  }

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

  public String getEmail() {
    return email;
  }

  public void setEmail(String email) {
    this.email = email;
  }
}

6）创建Repository：src/main/java/com/example/accessingdatamysql/UserRepository.java

package com.example.accessingdatamysql;

import org.springframework.data.repository.CrudRepository;

import com.example.accessingdatamysql.User;

// This will be AUTO IMPLEMENTED by Spring into a Bean called userRepository
// CRUD refers Create, Read, Update, Delete

public interface UserRepository extends CrudRepository<User, Integer> {

}

7：创建Controller：src/main/java/com/example/accessingdatamysql/MainController.java

package com.example.accessingdatamysql;

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.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;

@Controller // This means that this class is a Controller
@RequestMapping(path="/demo") // This means URL's start with /demo (after Application path)
public class MainController {
    @Autowired // This means to get the bean called userRepository
    // Which is auto-generated by Spring, we will use it to handle the data
    private UserRepository userRepository;

    @GetMapping(path="/add") // Map ONLY POST Requests
    public @ResponseBody String addNewUser (@RequestParam String name
            , @RequestParam String email) {
        // @ResponseBody means the returned String is the response, not a view name
        // @RequestParam means it is a parameter from the GET or POST request

        User n = new User();
        n.setName(name);
        n.setEmail(email);
        userRepository.save(n);
        return "Saved";
    }

    @GetMapping(path="/all")
    public @ResponseBody Iterable<User> getAllUsers() {
        // This returns a JSON or XML with the users
        return userRepository.findAll();
    }
}

8：创建主函数启动类：src/main/java/com/example/accessingdatamysql/AccessingDataMysqlApplication.java

package com.example.accessingdatamysql;

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

@SpringBootApplication
public class AccessingDataMysqlApplication {

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

}

9：启动主程序，启动之前可以查询下数据库中的表，启动之后再查询下，可以发现，启动主程序后，数据库中的User表就已经创建出来了

mysql> show tables;
+----------------------+
| Tables_in_db_example |
+----------------------+
| hibernate_sequence   |
+----------------------+
1 row in set (0.00 sec)

mysql> show tables;
+----------------------+
| Tables_in_db_example |
+----------------------+
| hibernate_sequence   |
| user                 |
+----------------------+
2 rows in set (0.00 sec)

在浏览器中进行添加数据：http://localhost:8080/demo/add?name=test2&email=ee@qq.com

通过数据库表查询可以看到，记录也添加进去了：

mysql> select * from user;
Empty set (0.00 sec)

mysql> select * from user;
+----+-----------+-------+
| id | email     | name  |
+----+-----------+-------+
|  3 | ee@qq.com | test2 |
+----+-----------+-------+
1 row in set (0.00 sec)

查询数据：http://localhost:8080/demo/all

10）这个当然是我们测试的环境，如果真正要应用还有需要做很多的安全措施来防止SQL注入攻击，比如给spring的数据库用户添加特定的权限，除了增删改查，其他权限就取消掉:

mysql> revoke all on db_example.* from 'springuser'@'%';
Query OK, 0 rows affected (0.22 sec)

mysql> grant select, insert, delete, update on db_example.* to 'springuser'@'%';
Query OK, 0 rows affected (0.11 sec)