SpringBoot uses MySQL through Spring Data JPA

Original text: spring. IO/guides/gs/a…

There are cuts in the project initialization section

This article shows you how to create a Spring application that connects to a MySQL database. This article uses Spring Data JPA to connect to the database. Users can also choose other libraries as needed (such as using Spring JDBC directly)

The ultimate goal

This article will walk you through creating a MySQL database and a Spring application, and connecting to the newly created database in the Spring application.

See github.com/spring-guid…

Create projects using Spring Initializr

IO, select Spring Web, Spring Data JPA, and MySQL Driver from the dependencies list, then download the initialized package and open it using the IDE

Creating a database

Open MySQL on the terminal

$ sudo mysql --password
Copy the code

In the mysql command line interface, use the following command to create a new database

mysql> create database db_example; Create a new database
mysql> create user 'springuser'@The '%' identified by 'ThePassword'; Create user
mysql> grant all on db_example.* to 'springuser'@The '%'; Grant all permissions to the new user
Copy the code

create`application.properties`file

Spring Boot itself comes with a bunch of defaults. For example, the default database is H2. Therefore, when we want to change the database, we need to explicitly configure it in the application.properties file.

Create a resource file, SRC/main/resources/application. The properties, add the following content:

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
Copy the code

The above spring.jpa.hibernate. dcl-auto values can be None, update, create, or create-drop. Read the Hibernate documentation for details.

none:MySQLThe default value of. Does not change the database structure;
updateHibernate will modify the database based on the given entity structure (if the format is inconsistent at startup, it will update the tables and keep the original data);
createCreate a new table if there is no table, update the structure if there is already data in the table, and do not delete the database when closed;
create-drop: Creates the database and creates theSessionFactoryDelete libraries on shutdown (that is, empty tables every time the program closes).

By “modifying” I mean modifying the database structure

Initially, this value must be set to create or update — there is no database structure yet. After the first run, you can change the value to UPDATE or None, depending on your application’s needs — for example, update when you need to change the database structure

The default value Spring Boot prepares for embedded databases such as H2 is create-DROP. For other databases, such as MySQL, the default value is None.

To enhance security, you are advised to change the value to None when connecting to the database in the production environment and revoke all permissions of the Spring Boot database user except the basic add, delete, modify, and query permissions. More on security at the end of this article.

create`@Entity`model

You need to create a data entity model (SRC/main/Java/com/example/accessingdatamysql/User. The data) :

package com.example.accessingdatamysql;

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

@Entity     This annotation tells Hibernate to create a table from the class
public class User {
    @Id
    @GeneratedValue(strategy=GenerationType.AUTO)
    private Integer id;
    private String name;
    private String email;
    
    public Integer getId(a) {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getName(a) {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getEmail(a) {
        return email;
    }
    public void setEmail(String email) {
        this.email = email; }}Copy the code

Hibernate automatically translates entities into tables.

Create a warehouse

Then you need to create a repository to store user records as follows:

(code is located in the SRC/main/Java/com/example/accessingdatamysql/UserRepository. Java)

package com.example.accessingdatamysql;

import org.springframework.data.repository.CrudRepository;
import com.example.accessingdatamysql.User;

public interface UserRepository extends CrudRepository<User.Integer> {}Copy the code

Creating a Controller

We need to create a controller to handle HTTP requests as follows:

(code is located in the 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.ReponseBody;

@Controller        // Indicates that the class is a controller
@RequestMapping(path="/demo")
public class MainController {
    /** * This annotation means to get the bean named userRepository, which is automatically generated by Spring and will be used to process data */
    @Autowired
    private UserRepository userRepository;
    
    @PostMapping(path="/add")
    public @ResponseBody String addNewUser(@RequestParam String name, @RequestParam String email) {
        / * *@ResponseBodyThe function returns a response string instead of the view name *@RequestParamNote The parameter is */ from GET or POST
        User n = new User();
        n.setName(name);
        n.setEmail(email);
        userRepository.save(n);
        return "Saved";
    }
    
    @GetMapping(path="/all")
    public @ResponseBody Iterable<User> getAllUsers(a) {
        // This function returns a JSON or XML list of users
        returnuserRepository.findAll(); }}Copy the code

The above example explicitly specifies the POST and GET methods for the two request addresses. By default, @RequestMapping can map all types of HTTP operations

Create an Application Class.

Spring Initializr creates a simple class for the application. As follows:

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); }}Copy the code

In this case, we don’t need to modify the class

@springBootApplication is shorthand for the following three annotations:

@Configuration: Marks the class as the definition of an Application Context bean;
@EnableAutoConfiguration: Have Spring Boot add beans based on classpath Settings, various property Settings, etc. For example, if there is one in the classpathspring-webmvc, the tag marks the application as a Web application and enables related actions — such as starting oneDispatcherServlet;
@ComponentScan: Let Spring find incom/examplePackage below other components, configurations, services, etc., and find all controllers.

The main() method uses Spring Boot’s SpringApplication.run() function to start the application.

Create a running JAR file

This section and juejin.cn/post/707639… Same, omitted here

The test application

After launching the app, you can use a tool like curl to test it:

GET localhost:8080/demo/all Obtains all data. POST localhost:8080/demo/add Adds a new user to the data.

For example, the following command will add a new user:

$ curl localhost:8080/demo/add -d name=First -d [email protected]
Copy the code

The server will return:

Saved
Copy the code

The following command will display all existing users:

$ curl 'localhost:8080/demo/all'
Copy the code

The server will return:

[{"id":1."name":"First"."email":"[email protected]"}]
Copy the code

Enhanced security

In a real production environment, we would need to defend against SQL injection attacks — hackers could inject SQL instructions such as DROP TABLE or other destructive SQL instructions. Therefore, we need to make some changes to the database before making the application available externally.

The following command can revoke the privileges of all database users used by Spring applications:

mysql> revoke all on db_example.* from 'springuser'@The '%';
Copy the code

As a result, Spring applications cannot make any changes to the database itself.

However, the application must have some minimal permissions, and the following command ensures that the application has these permissions:

mysql> grant select.insert.delete, update on db_example.* to 'springuser'@The '%';
Copy the code

This will only allow Spring applications to modify the data content, not the database structure

When you need to modify the database, you can:

Reassign permissions;
Modify thespring.jpa.hibernate.ddl-autoA value ofupdate;
Rerun program

Then repeat the preceding two commands to restore security. If possible, it is best to use a separate migration tool such as Flyway or Liquibase

SpringBoot uses MySQL through Spring Data JPA

The ultimate goal

Create projects using Spring Initializr

Creating a database

createapplication.propertiesfile

create@Entitymodel

Create a warehouse

Creating a Controller

Create an Application Class.

Create a running JAR file

The test application

Enhanced security

Related Posts

Golang Microservices Framework GO-Zero series 4: Go-Zeroz file services

In-depth understanding of K8S network principles

Changbuy Mall (12) : Access to wechat scan code payment

create`application.properties`file

create`@Entity`model