Experienced a variety of uncontrollable and unpredictable tribulations (including but not limited to idea flash back, Navicat flash back, system flash back, countless inexplicable bugs, crazy search for various annotations is why countless, why jdbcTemplate failure, @AutoWired failure, etc.) , finally nailed the subject ！！！！

This article will take you through the app!

1. General idea

First, to summarize what you should do if you want to start from scratch and use the SpringBoot framework to write a RESTful application that can access a database and perform CRUD operations:

  1. To install a mysql environment that does not/does not like using the command line, you need to install another database software such as Navicat or mysqlWorkbench, which is used in this article
  2. Create database, data table, user name + password to access database (preferably not directly to root permission)
  3. Recommendation:Install a Postman to facilitate the request interface to view the results
  4. Initialize a springBoot framework and install the plugin dependencies we need (mentioned later)
  5. inapplication.propertiesIn the file, you can configure the information needed to connect to the database, and you can configure the port number you want to start the application
  6. Write three Java classes:controller,entity,service.
  • entity: Used to write data table corresponding fields in Java corresponding object, need to have Bean characteristics: getter, setter, etc.
  • service: used to write mysql statements and corresponding JDBC requests
  • controller: Used to invoke service and make corresponding GET/POST interfaces
  1. Start and run the entire app

2. Create the mysql database

If you know how to do this, can you skip this part

Also, how to build a docker and install mysql in a container is not discussed here

2.1 Download a version of mysql suitable for your computer

Reference tutorial: blog.csdn.net/baidu_26315…

2.2 Downloading a mysql software

Free: mysqlWorkbench Paid: Navicat

2.3 Build databases, tables, data and users

2.3.1 start mysql

2.3.2 NavICat Connects to the database

2.3.3 Creating a Spring_example database

2.3.4 Creating a tablespace user

2.3.5 Filling In Data

2.3.6 Creating a User for Spring to access the database

Select New User

Add general information and set the user’s name and password

Add object permissions for a user

Select the database permissions that you want

3. Create a SpringBoot project

3.1 Initializing a SpringBoot Project

Use the official website to generate a Springboot project: start.spring. IO /

Required dependencies:

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

   <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <scope>runtime</scope>
   </dependency>
   <dependency>
      <groupId>org.projectlombok</groupId>
      <artifactId>lombok</artifactId>
      <optional>true</optional>
   </dependency>
   <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-jdbc</artifactId>
   </dependency>
   <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-test</artifactId>
      <scope>test</scope>
   </dependency>
   <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-jdbc</artifactId>
      <version>5.3.13</version>
      <scope>compile</scope>
   </dependency>
</dependencies>
Copy the code

Key dependency statements:

  • Lombok: A library that simplifies code by automatically generating Bean methods at debug compile time without having to write getters () or setters () yourself. See this article to understand: # Incorporating Lombok simplifies interface object code

  • Spring-boot-starter-web: a Web service used to create Springboot and provide APIS in the REST style

  • Mysql-connector-java and spring-boot-starter-JDBC: provide JDBC connection methods for mysql, especially JdbcTemplate

Then we also need to configure the connection information between the project and the database we created:

# mysql connection Settings spring. The datasource. Url = JDBC: mysql: / / 127.0.0.1:3306 / spring_example spring.datasource.username=spring spring.datasource.password=spring spring.datasource.driver-class-name=com.mysql.cj.jdbc.DriverCopy the code

3.2 Creating the relevant Package and Java Class

As mentioned at the beginning of this article, according to the specification, Java code should be hierarchical, so we will create the three packages shown above, each representing the following meanings:

  • entity: Used to write data table corresponding fields in Java corresponding object, need to have Bean characteristics: getter, setter, etc.
  • service: used to write mysql statements and corresponding JDBC requests
  • controller: Used to invoke service and make corresponding GET/POST interfaces

Then create the corresponding class, we will explain the following:

3.2.1 user. The class

First, we will create a class that corresponds to the contents of the data table fields so that we can store the data from the database in the instantiated object.

Typically, we declare a bunch of private variables and then write some public getters () and setters () methods to implement a standard Java bean so that we can instantiate objects of that type in other methods.

However, as the following code shows, we can easily add using Lombok’s method@DataAnnotations, so you don’t have to write a long list of getters () and setters () and have your code automatically generate them for you at compile time.

package shenling.example.springbootJDBC.entity;

import lombok.Data;

@Data
public class user {
    private Integer id;
    private String firstname;
    private String lastname;
}
Copy the code

Note that for Lombok to work, you also need to download and enable the plugin lombok in IDEA

3.2.2 userService. Class

In the Service layer, we provide JDBC connection services for this table. Basically, the logic of the following code is: write an SQL and use the JdbcTemplate to make database connections and requests.

In particular: Compared to the normal framework, we use JdbcTemplate methods, so we don’t have to worry about the additional operations of database.connection, crud-close connection, so we can focus on the implementation of business logic. This is also a feature of SpringBoot.

package shenling.example.springbootJDBC.service;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import org.springframework.stereotype.Service;
import shenling.example.springbootJDBC.entity.user;

import java.util.List;

@Service
@Component
public class userService {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    // Query the list
    public List<user> getList(a) {
        String sql = "SELECT * FROM user";
        List<user> result = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(user.class));
        return result;
    }


    / / new
    public int addUser(user newUser) {
        String sql = "INSERT INTO user(id, firstname, lastname)values(? ,? ,?) ";
        return jdbcTemplate.update(sql, newUser.getId(), newUser.getFirstname(), newUser.getLastname());
    }

    / / update
    public int updateUser(user newUser) {
        String sql = "update user set firstname = ?, lastname=?  where id = ?";
        return jdbcTemplate.update(sql, newUser.getFirstname(), newUser.getLastname(), newUser.getId());
    }

    / / delete
    public int deleteUser(int id) {
        String sql = "delete from user where id = ?";
        returnjdbcTemplate.update(sql, id); }}Copy the code

A few points to note:

  1. We introduced a JdbcTemplate object to use methods to make SQL query requests to the database for CRUD operations. Here you can refer to learn this article: www.jianshu.com/p/f0cbed671…

  2. In SpringBoot, whenever a class is annotated with @Component, instead of instantiating the object with new when called, it is instantiated with the @Autowired annotation. So:

  • JdbcTemplateAdd before variable declaration@Autowiredannotations
  • thisuserServiceClass, you have to go through the controller side@AutowiredSo the front of this class needs to be added@Componentannotations
  1. Query, generally, is usedList<Map<T, P>>The map class is used as the second argument to query. However, according to the discussion and recommendation of some articles, it is more recommended to use RowMapper to save data results, which is faster and better.
  • Relevant discussion thread: www.oschina.net/question/28…
  • Articles about JdbcTemplate and RowMapper: blog.csdn.net/cwr45282953…

3.2.3 userController. Class

In controller, you create these interfaces. You can refer to the previous demo on how to build interfaces to understand.

package shenling.example.springbootJDBC.controller;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.web.bind.annotation.*;
import shenling.example.springbootJDBC.entity.response;
import shenling.example.springbootJDBC.entity.user;
import shenling.example.springbootJDBC.service.userService;

import java.util.List;

@RestController
@RequestMapping("/user")
public class userController {

    @Autowired
    private userService userService;

    @Autowired
    private response res;


    @GetMapping("/list")
    public @ResponseBody response getUserList(a){
        try {
            List<user> result = userService.getList();

            res.setResult(result);
            res.setCode(10000);
            res.setMsg("Query successful");

        }
        catch(DataAccessException e) {
            e.printStackTrace();

            String exceptionMsg = e.getRootCause().getMessage();
            System.out.println("ERROR:" + exceptionMsg);
            res.setCode(500);
            res.setMsg(exceptionMsg);
        }

        return res;
    }

    @PostMapping("/add")
    public @ResponseBody response addUser(user newUser) {
        try {
            Integer result = userService.addUser(newUser);

            res.setResult(newUser);
            res.setCode(10000);
            res.setMsg("New success");

        }
        catch(DataAccessException e) {
            e.printStackTrace();

            String exceptionMsg = e.getRootCause().getMessage();
            System.out.println("ERROR:" + exceptionMsg);
            res.setCode(500);
            res.setMsg(exceptionMsg);
        }

        return res;
    }

    @PostMapping("/update")
    public @ResponseBody response updateUser(user newUser) {
        try {
            Integer result = userService.updateUser(newUser);

            res.setResult(newUser);
            res.setCode(10000);
            res.setMsg("Update successful");

        }
        catch(DataAccessException e) {
            e.printStackTrace();

            String exceptionMsg = e.getRootCause().getMessage();
            System.out.println("ERROR:" + exceptionMsg);
            res.setCode(500);
            res.setMsg(exceptionMsg);
        }

        return res;
    }

    @PostMapping("/delete")
    public @ResponseBody response deleteUser(int id) {
        try {
            Integer result = userService.deleteUser(id);

            res.setResult(id);
            res.setCode(10000);
            res.setMsg("Deleted successfully");

        }
        catch(DataAccessException e) {
            e.printStackTrace();

            String exceptionMsg = e.getRootCause().getMessage();
            System.out.println("ERROR:" + exceptionMsg);
            res.setCode(500);
            res.setMsg(exceptionMsg);
        }

        returnres; }}Copy the code

Again, we can see a few key points here:

  1. RestController: To make an interface, the class must be preceded by this annotation

  2. @requestMapping (“/user”) : Since we want the interface to access the first segment of the user, then the request list, new user add, etc., we need to write this in front of the class. Of course, if you don’t need this jump, you don’t have to write it.

  3. @autoWired: We used the @Component annotation in the userService earlier, so we can instantiate Autowired directly instead of new

  4. In this code, you can see that userService.getList() can fetch the result of the query and fetch the exception log by throwing an exception DataAccessException. However, in order to normalize our output as MSG /code/result, we also need to create a Response class to declare our result, which will be explained in the next section.

  5. ResponseBody: This is written in the method to make the result returned by the method jSON-style output, which is formatted and standardized to implement a very typical REST interface return style.

3.2.4 the response. The class

In order to standardize our output, we created another class in the Entity package to hold what our output looks like:

package shenling.example.springbootJDBC.entity;

import lombok.Data;
import org.springframework.stereotype.Component;

@Data
@Component
public class response {
    private String msg;
    private Integer code;
    private Object result;
}
Copy the code

Here we can see:

  1. Use @data to implement a bean-style class

  2. @Component is used to ensure that in controller, it can be used via autoWired

3.3 Create a class to run the application

Typically, a SpringBoot application initialized via the official website contains this section:

package shenling.example.springbootJDBC;

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


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

The @SpringBootApplication annotation lets the application find our Controller when it runs

4. Run the command and view the result

4.1 The default port 8080 is Occupied:

Sometimes the default port 8080 is used, so we can look at the PID of this port number and kill the process

You can also set a new port number in application.property, for example:

server.port=1234
Copy the code

4.2 Console Results

After the operation is successful, the console displays the port number of the current Web operation

4.3 Using Postman to initiate a Request for Query

Of course, we could have accessed the GET interface directly from the browser, but for the sake of being professional, intuitive, and aesthetically pleasing, we used the Postman interface management tool

  1. Download the local postman: www.postman.com/downloads/?…

  2. Send the request and view the results:

5. Summary

The Lombok library is recommended, which is very convenient to develop and reduces your code lines, but you need to ignore it when building because it will be automatically generated by the compilation

2. Use@autowiredBe aware of the class being declared@Componentannotations

@Component
public class FirstClass {... }public class SecondClass {
    @Autowired
    FirstClass f;
}
Copy the code

3. Want to knowcontroller,entity,serviceWhat to write (some frameworks require dao layer and so on)

4. You are advised to create a standard interface output template and throw the MSG of the request exception

5. Be sure to write in front of the controller class that writes the interface@RestController

@RestController
public class controller {... }Copy the code

6. Read the console error message and check for bugs