【SpringBoot series 】 six, SpringBoot database operation (integrated MyBatis)

This is the 13th day of my participation in the First Challenge 2022

Spring’s support for persistence, not just JDBC, provides simple templates and callbacks for all supported persistence technologies:

This chapter mainly explains how to access the operation database through SpringBoot, in the following three ways to operate the database:

• JdbcTemplate
• JPA
• MyBatis

This article takes a simple basic operation on a database table as an example to illustrate.

Database Use MySQL database, create t_user table.

CREATE TABLE `t_user` (
  `userid` varchar(10) NOT NULL,
  `username` varchar(255) DEFAULT NULL,
  `sex` varchar(2) DEFAULT NULL,
  `age` int(3) DEFAULT NULL,
  PRIMARY KEY (`userid`)
);
Copy the code

1, the JdbcTemplate

JdbcTemplate is Spring’s encapsulation of JDBC templates. It provides a set of JDBC templates that reduce redundancy when writing persistence layer code, simplify JDBC code, and make code more concise, a bit like DBUtils. JdbcTemplate is generally not used in the actual development, usually is the use of MyBatis, Hibernate and other more mature, excellent data persistence layer framework, here is just to its use to do a description, know how to use it can be.

1.1 Introducing the JdbcTemplate dependency package

In the project pom. XML, add the following dependency package information.

<! <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jdbc</artifactId> </dependency>Copy the code

If the mysql dependency package is not imported, you need to add mysql-connector-java.

<dependency>
	<groupId>mysql</groupId>
	<artifactId>mysql-connector-java</artifactId>
	<scope>runtime</scope>
</dependency>
Copy the code

1.2 Persistence Layer (DAO Layer)

Implement persistence layer DAO to complete data insertion.

com.xcbeyond.springboot.dao.UserDao.java

package com.xcbeyond.springboot.dao; import com.xcbeyond.springboot.model.User; Public interface UserDao {/** * insertUser * @param User * @return */ public int insertUser(User User); }Copy the code

com.xcbeyond.springboot.dao.impl.UserDaoJdbcTemplateImpl.java

package com.xcbeyond.springboot.dao.impl; import java.util.HashMap; import java.util.Map; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate; import org.springframework.stereotype.Repository; import com.xcbeyond.springboot.dao.UserDao; import com.xcbeyond.springboot.model.User; * @author xcbeyond * @author xcbeyond * @author xcbeyond * To indicate that the class is used to perform database-specific operations (dao objects), @repository public class UserDaoJdbcTemplateImpl implements UserDao {// Automatically import dependent beans @AutoWired Private  NamedParameterJdbcTemplate jdbcTemplate; @Override public int insertUser(User user) { String sql = "INSERT INTO t_user (userid, username, sex, age) VALUES (:userid, :username, :sex, :age)"; Map<String,Object> param = new HashMap<String,Object>(); param.put("userid", user.getUserId()); param.put("username", user.getUserName()); param.put("sex", user.getSex()); param.put("age", user.getAge()); return jdbcTemplate.update(sql, param); }}Copy the code

1.3 control Layer (Controller)

com.xcbeyond.springboot.controller.ControllerDemo.java

(The complete code can be obtained from github address at the end of the article)

/ * * * insert user information by way of JdbcTemplate * @ return * / @ RequestMapping ("/insertUserByJdbcTemplate ") public String insertUserByJdbcTemplate() { User user = new User(); user.setUserId("xcbeyond"); user.setUserName("xcbeyond"); user.setSex("F"); user.setAge(18); int ret = userDao.insertUser(user); return String.valueOf(ret); }Copy the code

Type http://localhost:8888/demo/insertUserByJdbcTemplate in your browser and complete the data insertion.

2, the JPA

JPA (Java Persistence API) is the Java Persistence layer API, a specification that simplifies object-relational mapping to manage relational databases in Java applications, using objects directly instead of SQL statements. In JPA, you can easily manipulate tables in a database through entity classes.

2.1. Introduce dependency packages

To implement database operations through JPA, the following dependency packages need to be introduced first.

<! <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jpa</artifactId> </dependency>Copy the code

2.2, the ORM

Add an annotation for Entiy to the User class in Model, specify the table name (if not, the default table name is the class name User), and then specify the ID and generation policy.

(These are all related to JPA knowledge, if not clear, you can view JPA in detail)

3, MyBatis

MyBatis method is highly recommended, simple operation, less code.

3.1 Importing dependency packages

Introduce the mybatis-spring-boot-starter dependency package.

<! <dependency> <groupId>org.mybatis.spring. Boot </groupId> < artifactId > mybatis - spring - the boot - starter < / artifactId > < version > 1.1.1 < / version > < / dependency >Copy the code

3.2 mapper interfaces

Mapper interface, the definition of the database to add, delete, change, check specific operations, do not need to implement classes.

/springboot/src/main/java/com/xcbeyond/springboot/mapper/UserMapper.java

package com.xcbeyond.springboot.mapper; import com.xcbeyond.springboot.model.User; /** * public interface UserMapper {/** * public interface UserMapper {/** * public interface UserMapper {/** * public interface UserMapper {/** * public interface UserMapper {/** * public interface UserMapper {/** * userid * @return */ public User queryUserByUserid(String userid); }Copy the code

3.3 configuration mapper

MyBatis can use XML or annotations to specify the SQL used to operate the database. XML is recommended. First of all, we need to configure the mapper folder: / springboot/SRC/main/resources/mybatis mapper /, and in the application. The properties in the configuration mapper path.

#mybatis #mybatis . For example, the basic types of alias mybatis config - location = classpath: mybatis/mybatis - config. Designated mapper XML # the location of the folder mybatis.mapper-locations=classpath:mybatis/mapper/*.xmlCopy the code

Created to operate table mapper. The XML SQL file, namely: / springboot/SRC/main/resources/mybatis mapper/UserMapper. The XML

<? The XML version = "1.0" encoding = "utf-8"? > <! DOCTYPE mapper PUBLIC "- / / mybatis.org//DTD mapper / 3.0 / EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" > < mapper namespace="com.xcbeyond.springboot.mapper.UserMapper"> <sql id="column"> userid,username,sex,age </sql> <select id="queryUserByUserid" parameterType="string" resultType="com.xcbeyond.springboot.model.User"> select <include refid="column"></include> from t_user where userid = #{userid} </select> </mapper>Copy the code

Note: Comply with THE SQL configuration of ByBatis. For detailed configuration, you can query the relevant knowledge of ByBatis.

3.4 the controller

Com. Xcbeyond. Springboot. Controller. ControllerDemo. Java, additional queryUserByUserid method, as follows:

@requestMapping (value="/queryUserByUserid", RequestMapping(value="/queryUserByUserid"); method=RequestMethod.GET) public String queryUserByUserid(@RequestParam("userid") String userid) { User user = userMapper.queryUserByUserid(userid); return user.toString(); }Copy the code

3.5 test

After startup SpringBoot program, enter http://localhost:8888/demo/queryUserByUserid? in your browser Userid =xcbeyond. The query result is displayed.

The following error is reported during startup:

***************************
APPLICATION FAILED TO START
***************************

Description:

A component required a bean of type 'com.xcbeyond.springboot.mapper.UserMapper' that could not be found.


Action:

Consider defining a bean of type 'com.xcbeyond.springboot.mapper.UserMapper' in your configuration.
Copy the code

This is because Mapper cannot be found during SpringBoot startup and cannot be managed by SpringBoot. You can manage the UserMapper interface in the following ways.

Method 1: Use @mapper annotations

To enable UserMapper to be referenced by other classes, we can add @mapper annotation to the UserMapper class:

package com.xcbeyond.springboot.mapper; import com.xcbeyond.springboot.model.User; /** * @author xcbeyond */ @mapper public interface UserMapper {/** * @param userid * @return */ public User queryUserByUserid(String userid); }Copy the code

Add @mapper annotation directly to Mapper class. This method requires that every Mapper class need to add this annotation.

Method 2: Use the @mapperscan annotation

Add the @mapperscan annotation to the startup class to specify the path to the package of the Mapper class to be scanned.

package com.xcbeyond.springboot; import org.mybatis.spring.annotation.MapperScan; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.boot.SpringApplication; import org.springframework.boot.autoconfigure.SpringBootApplication; /** * SpringBoot boot class * @author xcbeyond */ @springbootApplication // Specifies the path of the Mapper class package to be scanned @MapperScan("com.xcbeyond.springboot.mapper") public class SpringbootApplication { private static Logger logger = LoggerFactory.getLogger(SpringbootApplication.class); public static void main(String[] args) { if(logger.isDebugEnabled()) { logger.debug("SpringBoot starting..." ); } SpringApplication.run(SpringbootApplication.class, args); }}Copy the code

Conclusion:

This chapter mainly describes how to operate the database in SpringBoot, where JdbcTemplate and JPA only show that you can operate the database in SpringBoot, but in real projects recommended to use MyBaits, which will be very convenient.

Micro-service/Springboot at Master · xcBeyond/Micro-service · GitHub