1. pom.xml
Detailed POM XML file reference blogger source code
Where h2's scope can be test.
2. Spring boot uses mybatis for database operation
When using mybatis, you need org mybatis. spring. boot:mybatis-spring-boot-starter:2.1. 3 [compile] dependency.
Then create an interface and use the @ Mapper tag. mybatis will search for this tag and parse this interface into a database operation interface.
package com.ws.product.blogger.dao;
import com.ws.product.blogger.dao.pojo.User;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Component;
@Mapper // mybatis is used to discover the mapper interface
@Component // Can be omitted. This is mainly to avoid error reporting when @ Autowired is in UserDaoTest in IDEA
public interface UserDao {
User getById(int id);
User getByUsername(String username);
// Using Java beans to pass multiple parameters, you can directly use the bean's property name, namely #{username}, in xml
int insert(User user);
int delete(int id);
// Two parameters. The second parameter is a java bean. You need to use #{user.username}
int update(int id, User user);
}User is a POJO used to save data in the database:
package com.ws.product.blogger.dao.pojo;
import lombok.Data;
@Data
public class User {
private int id;
private String username;
private String password;
}Then create a new mapper directory under src/main/resources directory, and then create a new userdao XML file, and the specific SQL statements are written here. It doesn't matter where this file is, and then through application Mybatis. In the YML configuration file mapper-locations: classpath:mapper/*. The XML attribute found the file.
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org/DTD Mapper 3.0" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.ws.product.blogger.dao.UserDao"><!--namespace yes java interface and xml Matching relationship of-->
<resultMap id="UserMap" type="com.ws.product.blogger.dao.pojo.User">
<id column="id" property="id" jdbcType="INTEGER"/>
<result column="username" property="username" jdbcType="VARCHAR"/>
<result column="password" property="password" jdbcType="VARCHAR"/>
</resultMap>
<select id="getById" resultMap="UserMap">
select * from t_user where id = #{id}
</select>
<select id="getByUsername" resultMap="UserMap">
select * from t_user where username = #{username}
</select>
<insert id="insert" parameterType="com.ws.product.blogger.dao.pojo.User">
insert into t_user (username, password) values ( #{username}, #{password} )
</insert>
<delete id="delete">
delete from t_user where id = #{id}
</delete>
<update id="update">
update t_user
<set>
<if test="user.username != null">username = #{user.username},</if>
<if test="user.password != null">password = #{user.password}</if>
</set>
where id = #{id}
</update>
</mapper>In this way, mybatis has the ability to operate the database. This is sufficient for a single DAO module. If you want to really perform database operations, you need to create a new main function, use the @ SpringBootApplication tag, application Configuring spring.com in YML datasource. URL, username, password and driver class name attributes, which will automatically generate an sqlSession, which mybatis will use to operate the database. Don't forget, application In YML, you also need to configure mybatis Mapper locations property.
3. Test mybatis
We use org mybatis. spring. boot:mybatis-spring-boot-starter-test:2.1. 3 [test] test mybatis and use the memory database of H2 database to make the database test repeatable.
First, create an empty @ SpringBootApplication tagged class to load all used bean s:
package com.ws.product.blogger.dao;
import org.springframework.boot.autoconfigure.SpringBootApplication;
/**
* This is a sentinel class. You need to add the @ SpringBootApplication annotation so that @ MybatisTest can automatically load the required classes. essential
*/
@SpringBootApplication
public class MapperTestApplication {
}Then create a test class and use the @ MybatisTest tag:
package com.ws.product.blogger.dao;
import com.ws.product.blogger.dao.pojo.User;
import org.junit.jupiter.api.Test;
import org.mybatis.spring.boot.test.autoconfigure.MybatisTest;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.autoconfigure.jdbc.AutoConfigureTestDatabase;
import org.springframework.test.context.ActiveProfiles;
import static org.junit.jupiter.api.Assertions.assertEquals;
@MybatisTest
@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE) // Using a real database, here we use H2 memory database
// @Rollback(value = false) / / the default value is true, and the database operation will be rolled back. If it is changed to false, it will not be rolled back
@ActiveProfiles("test") // Use appplication test YML as configuration file
public class UserDaoTest {
@Autowired
private UserDao userDao;
@Test
public void test01() {
User user = new User();
user.setUsername("aaa");
user.setPassword("aaa");
int i = userDao.insert(user);
assertEquals(1, i);
}
@Test
public void test02() {
User user = userDao.getByUsername("root");
assertEquals(1, user.getId());
assertEquals("root", user.getUsername());
assertEquals("rootqqq", user.getPassword());
}
@Test
public void test03() {
User user = userDao.getById(1);
assertEquals(1, user.getId());
assertEquals("root", user.getUsername());
assertEquals("rootqqq", user.getPassword());
}
@Test
public void test04() {
int i = userDao.delete(1);
assertEquals(1, i);
}
@Test
public void test05() {
User user = new User();
// user.setUsername("root");
user.setPassword("root2");
userDao.update(1, user);
User user1 = userDao.getById(1);
assertEquals("root", user1.getUsername());
assertEquals("root2", user1.getPassword());
}
}Create the configuration file Src / test / resources / application test yml. spring.datasource.shcema and data are table building statements and preliminary data statements
spring:
datasource:
url: jdbc:h2:mem:testdb
username: sa
password: sa
driver-class-name: org.h2.Driver
schema: classpath:sql/db/schema-h2.sql
data: classpath:sql/db/data-h2.sql
mybatis:
mapper-locations: classpath:mapper/*.xmlschema-h2. The contents in SQL are:
drop table if exists t_user;
create table t_user(
id integer not null auto_increment,
username varchar(100) not null,
password varchar(200) not null,
primary key (id)
);data-h2. The contents in SQL are:
insert into t_user values(1, 'root', 'rootqqq');
The test process is as follows: start UserDaoTest, find the @ SpringBootApplication tag ide class according to @ MybatisTest, load all bean s, and load application test yml. According to spring Create an H2 memory database according to the configuration of datasource, and load schema-h2 SQL and data-h2 SQL creates tables and tables. Then, perform the mybatis operation to generate the UserDao instance, which is automatically injected into the UserDaoTest for testing.