参考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)