Mybatis (1) : Basic use

A preface

Mybatis is a common persistence layer framework that supports custom SQL, stored procedures, and advanced mapping. Mybatis encapsulates some JDBC logic and simplifies our development.

This article is the first article of Mybatis. In this article, we mainly introduce the basic knowledge of Mybatis, including the use of Mybatis alone, Spring integration and Spring Boot.

I will submit the sample code mentioned in this article to Github. The address will be given at the end of this article. If you need it, you can download it yourself.

The following versions of dependencies are used in the example:

JDK 1.8
Mybatis 3.5.7
Spring 5.3.9
Druid 1.2.6
Mysql – connector 8.0.19

Mybatis document

Ii Basic Use

The database table structure is as follows

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL DEFAULT ' ' COMMENT 'name',
  `age` int(3) NOT NULL DEFAULT '0' COMMENT 'age',
  `gender` varchar(20) NOT NULL DEFAULT ' ' COMMENT 'gender'.PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='User table';
Copy the code

1 Using JDBC

The flow of our direct JDBC data operation is as follows:

The load driver
Establish a connection
Creating a Statement object
Execute SQL statement
Processing result set
Close the resource

Sample code is as follows

public class JDBCSample {
    private static final String DS_URL = "jdbc:mysql://localhost:3306/user";
    private static final String DS_USERNAME = "root";
    private static final String DS_PASSWORD = "xiehua123";
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            // Load the driver
            Class.forName("com.mysql.cj.jdbc.Driver");
            // Establish a connection
            connection = DriverManager.getConnection(DS_URL, DS_USERNAME, DS_PASSWORD);
            / / SQL statements
            String querySql = "SELECT * FROM user";
            // Create a Statement object
            statement = connection.prepareStatement(querySql);
            / / SQL execution
            resultSet = statement.executeQuery(querySql);
            // Parse the result
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                int age = resultSet.getInt("age");
                String gender = resultSet.getString("gender");

                System.out.println("id:" + id + " name:" + name + " age:" + age + " gender:" + gender);
            }
            resultSet.close();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // Close the resource
            try {
                if(statement ! =null) { statement.close(); }}catch (SQLException e) {
                e.printStackTrace();
            }

            try {
                if(connection ! =null) { connection.close(); }}catch(SQLException e) { e.printStackTrace(); }}}}Copy the code

2 Mybatis

In the above content, we introduced the use of JDBC. In this department, we introduced how to use Mybatis separately for database operation.

Create a Maven project and introduce the following dependencies

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>

<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
</dependency>

<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
</dependency>

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>fastjson</artifactId>
</dependency>
Copy the code

Create mybatis. XML and datasource. Properties files in the resource directory with the following contents:

mybatis.xml


      
<! DOCTYPEconfiguration
        PUBLIC "- / / mybatis.org//DTD Config / 3.0 / EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <properties resource="datasource.properties"/>
    <environments default="dev">
        <environment id="dev">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${datasource.driver}"/>
                <property name="url" value="${datasource.url}"/>
                <property name="username" value="${datasource.username}"/>
                <property name="password" value="${datasource.password}"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper resource="mapper/UserMapper.xml"/>
    </mappers>
</configuration>
Copy the code

datasource.properties

datasource.driver = com.mysql.cj.jdbc.Driver
datasource.url = jdbc:mysql://localhost:3306/user? useUnicode=true&characterEncoding=utf-8
datasource.username = root
datasource.password = xiehua123
Copy the code

The User entity object is as follows

import lombok.Getter;
import lombok.Setter;

import java.io.Serializable;

/**
 * Create By IntelliJ IDEA
 *
 * @author: XieHua
 * @date: "* / 2021-08-03
@Setter
@Getter
public class User implements Serializable {
    private Integer id;
    private String name;
    private Integer age;
    private String gender;
}
Copy the code

Create the UserMapper class and the usermapper.xml file

import com.xh.sample.mybatis.entity.User;
import org.apache.ibatis.annotations.Param;

/**
 * Create By IntelliJ IDEA
 *
 * @author: XieHua
 * @date: the 2021-08-02 sets * /
public interface UserMapper {
    User getById(@Param("id") Integer id);
}
Copy the code


      
<! DOCTYPEmapper
        PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.xh.sample.mybatis.mapper.UserMapper">
    <resultMap id="BaseResultMap" type="com.xh.sample.mybatis.entity.User">
        <result column="id" property="id" jdbcType="INTEGER"/>
        <result column="age" property="age" jdbcType="INTEGER"/>
        <result column="gender" property="gender" jdbcType="VARCHAR"/>
        <result column="name" property="name" jdbcType="VARCHAR"/>
    </resultMap>

    <select id="getById" resultMap="BaseResultMap">
        SELECT * FROM user WHERE id = ${id}
    </select>
</mapper>
Copy the code

At this point our project has been created, the project directory is as follows:

Create a test class to test. The code of the test class is as follows

import com.alibaba.fastjson.JSON;
import com.xh.sample.mybatis.entity.User;
import com.xh.sample.mybatis.mapper.UserMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;
import java.io.InputStream;

/**
 * Create By IntelliJ IDEA
 *
 * @author: XieHua
 * @date: the 2021-08-02 15:01 * /
public class TestDemo {
    public static void main(String[] args) throws IOException {
        String resource = "mybatis.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        User user = mapper.getById(1); System.out.println(JSON.toJSONString(user)); }}Copy the code

We found that we can normally get the corresponding data in the database, through the comparison with THE JDBC operation code, we will find that Mybatis for us to encapsulate the driver loading, connection establishment, SQL execution and result set mapping.

3 Spring integration with Mybatis

In normal work, we usually use Mybatis in the Spring environment.

Next we will use Spring to integrate Mybatis and Druid for database operations.

Create a Maven project and add dependencies

<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-core</artifactId>
</dependency>

<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-beans</artifactId>
</dependency>

<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-context</artifactId>
</dependency>

<! -- https://mvnrepository.com/artifact/org.springframework/spring-tx -->
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-tx</artifactId>
</dependency>

<! -- https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
</dependency>

<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-test</artifactId>
</dependency>

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
</dependency>

<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
</dependency>

<! -- https://mvnrepository.com/artifact/org.mybatis/mybatis-spring -->
<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis-spring</artifactId>
</dependency>

<dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
</dependency>

<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
</dependency>

<! -- https://mvnrepository.com/artifact/org.slf4j/slf4j-api -->
<dependency>
    <groupId>org.slf4j</groupId>
    <artifactId>slf4j-api</artifactId>
</dependency>

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>fastjson</artifactId>
</dependency>
Copy the code

Create applicationContext.xml and application.properties files in the Resources directory as follows


      
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:p="http://www.springframework.org/schema/p"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.3.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.3.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.3.xsd">

    <context:property-placeholder location="classpath:application.properties"/>

    <context:component-scan base-package="com.xh.sample.mybatis.spring"/>

    <! -- druid connection pool -->
    <bean name="datasource" class="com.alibaba.druid.pool.DruidDataSource"
        init-method="init" destroy-method="close">
        <property name="driverClassName" value="${datasource.druid.driver-class-name}"/>
        <property name="url" value="${datasource.druid.url}"/>
        <property name="username" value="${datasource.druid.username}"/>
        <property name="password" value="${datasource.druid.password}"/>
    </bean>

    <bean name="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="datasource"/>

        <property name="mapperLocations" value="classpath:mapper/*Mapper.xml"/>
    </bean>

   <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>
        <property name="basePackage" value="com.xh.sample.mybatis.spring.mapper"/>
    </bean>

    <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="datasource"/>
    </bean>

    <tx:annotation-driven transaction-manager="transactionManager"/>
</beans>
Copy the code

datasource.druid.url = jdbc:mysql://localhost:3306/user? useUnicode=true&characterEncoding=utf-8
datasource.druid.driver-class-name = com.mysql.cj.jdbc.Driver
datasource.druid.username = root
datasource.druid.password = xiehua123
Copy the code

The User, UserMapper, and usermapper. XML files are the same as above.

Create UserService and its implementation class

/**
 * Create By IntelliJ IDEA
 *
 * @author: XieHua
 * @date: 2021-08-03 17:45
 */
public interface UserService {
    /** * Add user **@paramUser User object *@returnSuccessful */
    boolean insert(User user);

    /** * Obtain user ** by id@paramId User ID *@returnUser object */
    User getById(Integer id);
}

@Service
public class UserServiceImpl implements UserService {
    @Resource
    private UserMapper userMapper;

    public boolean insert(User user) {
        return userMapper.insert(user) > 0;
    }

    public User getById(Integer id) {
        returnuserMapper.getById(id); }}Copy the code

Create the test class as follows

/**
 * Create By IntelliJ IDEA
 *
 * @author: XieHua
 * @date: the 2021-08-03 saveth * /
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = {"classpath:applicationContext.xml"})
public class UserServiceTest {
    @Resource
    private UserService userService;

    @Test
    public void insertTest(a) {
        User user = new User();
        user.setAge(17);
        user.setName("Bill");
        user.setGender("Male");

        boolean result = userService.insert(user);
        System.out.println("Add user result:" + result);
    }

    @Test
    public void getByIdTest(a) {
        User user = userService.getById(1);
        System.out.println("User acquisition result is:"+ JSON.toJSONString(user)); }}Copy the code

The project catalog is shown below

Run the test class to find a database that works.

4 SpringBoot integrates Mybatis

It will be much easier to integrate Mybatis under the SpringBoot project.

The code for User, UserMapper, usermapper. XML, UserService, and UserServiceImpl is the same as in the previous section, so I won’t show it here.

Create the application.yml file in the Resources directory with the following contents:

spring:
  datasource:
    druid:
      url: jdbc:mysql://localhost:3306/user? useUnicode=true&characterEncoding=utf-8
      username: root
      password: xiehua123
      driver-class-name: com.mysql.cj.jdbc.Driver
Copy the code

Create DataSourceConfig class as follows:

/**
 * Create By IntelliJ IDEA
 *
 * @author: XieHua
 * @date: the 2021-08-05 18:00 * /
@Configuration
@EnableTransactionManagement
@MapperScan("com.xh.sample.mybatis.springboot.mapper")
public class DataSourceConfig {
    @Bean
    @ConfigurationProperties("spring.datasource.druid")
    public DataSource dataSource(a) {
        return DruidDataSourceBuilder.create().build();
    }

    @Bean
    public SqlSessionFactory sqlSessionFactory(a) throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource());

        PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        sqlSessionFactoryBean.setMapperLocations(resolver.getResources("classpath:mapper/**/*Mapper.xml"));

        SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBean.getObject();
        return sqlSessionFactory;
    }

    @Bean
    public PlatformTransactionManager transactionManager(a) {
        return newDataSourceTransactionManager(dataSource()); }}Copy the code

Create the project’s Bootstarp class as follows:

/**
 * Create By IntelliJ IDEA
 *
 * @author: XieHua
 * @date: the 2021-08-05 18:09 * /
@SpringBootApplication
public class Bootstrap {
    public static void main(String[] args) { SpringApplication.run(Bootstrap.class, args); }}Copy the code

The test classes are as follows

@SpringBootTest
public class UserServiceTest {
    @Resource
    private UserService userService;

    @Test
    public void insertTest(a) {
        User user = new User();
        user.setAge(17);
        user.setName("SpringBoot");
        user.setGender("Male");

        boolean result = userService.insert(user);
        System.out.println("Add user result:" + result);
    }

    @Test
    public void getByIdTest(a) {
        User user = userService.getById(1);
        System.out.println("User acquisition result is:"+ JSON.toJSONString(user)); }}Copy the code

At this point, we have completed the code to integrate Mybatis in the SpringBoot project. The project directory is as follows:

Three summary

This article is the first article related to Mybatis, in this article mainly introduces the basic use of Mybatis. If you want to know more about the use of Mybatis, you can go to see its official document, the address is: mybatis.org/mybatis-3/z… .

The code for this article has been uploaded to Github. If you need it, go to github.com/xiehuaa/myb…

A preface

Ii Basic Use

1 Using JDBC

2 Mybatis

3 Spring integration with Mybatis

4 SpringBoot integrates Mybatis

Three summary

Related Posts

JVM Optimization Essentials – Monitoring tools

ThreadLocal has a few key points

To understand the concept of ETL in data processing