Detailed explanation of Java - Mybatis framework

1. Pre SQL statement

Create a file named Tedu_ Database of UMS;

CREATE DATABASE tedu_ums;
USE tedu_ums;

In the above database, create a database named t_user's data table, which should at least include id, user name, password, age, mobile phone number and e-mail;

CREATE TABLE t_user (
	id INT AUTO_INCREMENT COMMENT 'user id',
	username VARCHAR(20) NOT NULL UNIQUE COMMENT 'user name',
	password VARCHAR(20) NOT NULL COMMENT 'password',
	age INT COMMENT 'Age',
	phone VARCHAR(20) COMMENT 'phone number',
	email VARCHAR(30) COMMENT 'E-mail',
	PRIMARY KEY (id)
) DEFAULT CHARSET=utf8;

Insert no less than 10 pieces of data, and each field value of each data shall be as random as possible;

INSERT INTO t_user (username,password,phone,age) VALUES 
	('Henry', 'fdas', '13800138001', 26),
	('Zeo', 'vcvx', '13800138002', 21),
	('Alex', 'reer', '13800138003', 28),
	('Frank', 'jhgd', '13800138004', 22),
	('David', 'hgdf', '13800138005', 23),
	('Mike', 'cxvn', '13800138006', 20),
	('Lucy', 'qrec', '13800138007', 29),
	('Lily', 'rfdx', '13800138008', 24),
	('LiLei', 'nmhr', '13800138009', 27),
	('HanMM', 'uhjy', '13800138010', 32)
;

Delete id =? Data of;

DELETE FROM t_user WHERE id=?;

One time deletion id = id=?, id=? Data of;

DELETE FROM t_user WHERE id IN (?,?,?);

DELETE FROM t_user WHERE id=? OR id=? OR id=?;

Set id =? The e-mail address of the data is changed to?;

UPDATE t_user SET email=? WHERE id=?;

Change the passwords of all users to?;

UPDATE t_user SET password=?;

Count the number of users in the current table;

SELECT COUNT(*) FROM t_user;

Query id =? User's data;

SELECT * FROM t_user WHERE ID=?;

Query user name =? User's data;

SELECT * FROM t_user WHERE username=?;

Query the data of all users;

SELECT * FROM t_user ORDER BY id;

Query the data of the oldest user (assuming that the age values of all users are different).

SELECT * FROM t_user WHERE age=(SELECT MAX(age) FROM t_user);

SELECT * FROM t_user ORDER BY age DESC LIMIT 0,1;

2. Role of mybatis framework

The most direct function of MyBatis framework is to simplify the development of persistence layer!

Persistence layer: a component that handles data persistence;

Persistence: store data permanently;

When using the MyBatis framework to handle addition, deletion, modification and query, you only need to define the abstract method of the function and configure the SQL statement corresponding to the abstract method!

3. Create project

Create Maven Project, check Create a simple Project, Group id cn.tedu, Artifact id MyBatis, packaging War (unnecessary).

After creation, you should: generate web. XML; Add Tomcat running environment (unnecessary); Copy dependencies from previous order items; Copy spring.xml from the previous project; Copy the configuration in web.xml from the previous project.

To use the mybatis framework this time, you need to add the dependency of mybatis:

<!-- MyBatis frame -->
<!-- Optional version: 3.5.0~3.5.3 -->
<dependency>
	<groupId>org.mybatis</groupId>
	<artifactId>mybatis</artifactId>
	<version>3.5.3</version>
</dependency>

The MyBaits framework can be used alone, but it is very cumbersome to configure. It is usually used together with the Spring framework. Therefore, it is also necessary to add the dependency of mybatis Spring:

<!-- MyBatis integration Spring -->
<!-- Optional version: 2.0.0~2.0.3 -->
<dependency>
	<groupId>org.mybatis</groupId>
	<artifactId>mybatis-spring</artifactId>
	<version>2.0.3</version>
</dependency>

After integrating Spring, you also need to add Spring context dependency. Of course, you can also directly use Spring webmvc dependency, because it includes Spring context dependency. At the same time, because the underlying implementation still uses JDBC related technologies, you need to add Spring JDBC dependency. The version of the dependency must be exactly the same as that of Spring webmvc:

<!-- Spring-JDBC -->
<!-- Version: and spring-webmvc Completely consistent -->
<dependency>
	<groupId>org.springframework</groupId>
	<artifactId>spring-jdbc</artifactId>
	<version>????????</version>
</dependency>

In addition, because it needs to be programmed in combination with MySQL database, you also need to add MySQL connector Java dependency for MySQL database connection:

<!-- MySQL -->
<!-- Optional version: 5.1.34~5.1.39，8.0.12~8.0.18 -->
<dependency>
	<groupId>mysql</groupId>
	<artifactId>mysql-connector-java</artifactId>
	<version>8.0.18</version>
</dependency>

Then, you should also add the dependency of the database connection pool commons DBCP:

<!-- Database connection pool -->
<!-- Optional version: 1.2~1.4 -->
<dependency>
	<groupId>commons-dbcp</groupId>
	<artifactId>commons-dbcp</artifactId>
	<version>1.4</version>
</dependency>

Finally, the developed functions will be verified through unit tests this time. Therefore, junit dependencies need to be added.

In addition, rename spring.xml to spring-mvc.xml, copy it to get spring-dao.xml, and delete the configuration in spring-dao.xml.

4. Connect to the database

Create the db.properties file under src/main/resources, and configure the database connection information in it:

url=jdbc:mysql://localhost:3306/tedu_ums?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai
driver=com.mysql.cj.jdbc.Driver
username=root
password=root
initialSize=2
maxActive=10

serverTimezone value can only be Asia/Shanghai, Asia/Chongqing

Then, you should configure the < util: Properties > node in spring-dao.xml to read the above configuration information:

<!-- Read the configuration file connecting to the database -->
<util:properties id="dbConfig" location="classpath:db.properties" />

Then, configure the BasicDataSource and inject the configuration information read above into the relevant properties:

<!-- Data source: BasicDataSource -->
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
	<property name="url" value="#{dbConfig.url}"/>
	<property name="driverClassName" value="#{dbConfig.driver}"/>
	<property name="username" value="#{dbConfig.username}"/>
	<property name="password" value="#{dbConfig.password}"/>
	<property name="initialSize" value="#{dbConfig.initialSize}"/>
	<property name="maxActive" value="#{dbConfig.maxActive}"/>
</bean>

After completion, you can create a cn.tedu.mybatis.Tests test class under src/test/java to test whether you can connect to the database correctly:

public class Tests {

	ClassPathXmlApplicationContext ac;
	
	@Test
	public void getConnection() throws SQLException {
		BasicDataSource dataSource = (BasicDataSource) ac.getBean("dataSource");
		Connection conn = dataSource.getConnection();
		System.out.println(conn);
	}
	
	@Before
	public void doBefore() {
		ac = new ClassPathXmlApplicationContext(
			"spring-dao.xml");
	}
	
	@After
	public void doAfter() {
		ac.close();
	}

}

5. Abstract method

Suppose you need to develop the function of "insert user data into user table".

MyBatis requires relevant abstract methods to be encapsulated in the interface, so you need to create the cn.tedu.mybatis.UserMapper interface first:

public interface UserMapper {

}

Then, the abstract method corresponding to the function to be developed is declared in the interface. The declaration principles of abstract methods are as follows:

If the SQL statement to be executed is an add, delete or change type SQL statement, the return value should be declared as Integer, indicating the "number of rows affected", or void, indicating that it does not care about the number of rows affected; If the SQL statement to be executed is a query type SQL statement, the return value can be designed on demand as long as the query result can be encapsulated;
Method names can be customized, but overloaded method names are not allowed;
The parameter list of the method can be determined according to which variables are required when executing the SQL statement, and which parameters are added according to which variables are required.

The format of SQL statement executing insert user data is roughly as follows:

insert into t_user (username,password,age,phone,email) values (?,?,?,?,?);

Therefore, the abstract method can be designed as:

Integer xxx(String username, String password, Integer age, String phone, String email);

Of course, when the number of parameters is large or the number of parameters may change, each data can also be encapsulated:

public class User {
	private String username;
	private String password;
	private Integer age;
	private String phone;
	private String email;
}

Then the abstract method can also be designed as:

public interface UesrMapper {
	Integer addnew(User user);
}

Next, you need to configure the location of the interface file, so configure mappercannerconfigurer in spring-dao.xml:

<!-- to configure MapperScannerConfigurer -->
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
	<!-- Configure the package where the interface file is located -->
	<property name="basePackage" value="cn.tedu.mybatis" />
</bean>

6. SQL statement

Create a folder named mappers under src/main/resources, and create SomeMapper.xml file under this folder (attached at the end of the article).

Open SomeMapper.xml. First, add the root node < mapper >, and configure the namespace attribute in this node. The attribute value is the full name of the corresponding interface file:

<mapper namespace="cn.tedu.mybatis.UserMapper">

</mapper>

Then, select the child node according to the type of SQL statement to be executed. The value of the id attribute of the child node is the name of the corresponding abstract method, and then configure the SQL statement to be executed at the child level:

<!-- id Property: the name of the corresponding abstract method -->
<insert id="addnew">
	INSERT INTO t_user (
		username, password,
		age, phone,
		email
	) VALUES (
		#{username}, #{password},
		#{age}, #{phone},
		#{email}
	)
</insert>

Note: in the above configuration, the placeholder of #{} in the value list part is the property name in the parameter User type of addnew() method!

Then, you need to configure SqlSessionFactoryBean to specify the location of the XML configuration file and which data source to use to connect to the database. Therefore, configure in spring-dao.xml:

<!-- to configure SqlSessionFactoryBean -->
<bean class="org.mybatis.spring.SqlSessionFactoryBean">
	<!-- to configure XML File location -->
	<property name="mapperLocations" value="classpath:mappers/*.xml" />
	<!-- Configure which data source to use -->
	<property name="dataSource" ref="dataSource" />
</bean>

Finally, add a new global variable to the test class first:

UserMapper userMapper;

Get the value of the object in doBefore():

@Before
public void doBefore() {
	ac = new ClassPathXmlApplicationContext(
		"spring-dao.xml");
	userMapper = ac.getBean(
		"userMapper", UserMapper.class);
}

Then, write and execute unit tests:

@Test
public void addnew() {
	User user = new User();
	user.setUsername("Tommy");
	user.setPassword("o8ur");
	user.setAge(35);
	user.setPhone("13800138035");
	user.setEmail("tommy@163.com");
	Integer rows = userMapper.addnew(user);
	System.out.println("rows=" + rows);
}

7. Delete id =? Data

Add an abstract method to the UserMapper interface:

Integer deleteById(Integer id);

Add a configuration in SomeMapper.xml:

<delete id="deleteById">
	DELETE FROM t_user WHERE id=#{id}
</delete>

After completion, you can write unit Tests in Tests:

@Test
public void deleteById() {
	Integer id = 5;
	Integer rows = userMapper.deleteById(id);
	System.out.println("rows=" + rows);
}

8. Change the passwords of all users to?

Add an abstract method to the UserMapper interface:

Integer updatePassword(String password);

Add a configuration in SomeMapper.xml:

<update id="updatePassword">
	UPDATE t_user SET password=#{password}
</update>

After completion, you can write unit Tests in Tests:

@Test
public void updatePassword() {
	String password = "8888";
	Integer rows = userMapper.updatePassword(password);
	System.out.println("rows=" + rows);
}

9. Count the number of users in the current table

Add an abstract method to the UserMapper interface:

Integer count();

Add a configuration in SomeMapper.xml:

<select id="count" resultType="java.lang.Integer">
	SELECT COUNT(*) FROM t_user;
</select>

Note: when configuring the < Select > node, you must specify one of the resultType or resultMap attributes.

After completion, you can write unit Tests in Tests:

@Test
public void count() {
	Integer count = userMapper.count();
	System.out.println("count=" + count);
}

10. Query id =? User's data

Add an abstract method to the UserMapper interface:

User findById(Integer id);

Add a configuration in SomeMapper.xml:

<select id="findById" resultType="cn.tedu.mybatis.User">
	SELECT * FROM t_user WHERE id=#{id}
</select>

After completion, you can write unit Tests in Tests:

@Test
public void findById() {
	Integer id = 1000;
	User result = userMapper.findById(id);
	System.out.println(result);
}

11. Query the data of all users

Add an abstract method to the UserMapper interface:

List<User> findAll();

Add a configuration in SomeMapper.xml:

<select id="findAll" resultType="cn.tedu.mybatis.User">
	SELECT * FROM t_user ORDER BY id
</select>

Note: if the query result is an object, whether it is to query one data or multiple data to form a List set, when configuring the SQL statement, the resultType only writes the type to which the object belongs, that is, when the query result is a List set, you only need to tell MyBatis what type of data is placed in the framework set!

After completion, you can write unit Tests in Tests:

@Test
public void findAll() {
	List<User> list = userMapper.findAll();
	System.out.println("size=" + list.size());
	for (User item : list) {
		System.out.println(item);
	}
}

----------------------------------------

Appendix 1: solution to prompt Invalid bound statement (not found)

If the above error prompt appears, the possible reasons are:

The maperlocations attribute of SqlSessionFactoryBean is not configured in spring-dao.xml, or the configuration value is wrong. For example, the folder where XML files are stored is called mappers, but the configuration value is classpath:mapper/*.xml;
In SomeMapper.xml, the namespace value of the root node is incorrect;
In SomeMapper.xml, the id value of the child's addition, deletion, modification and query node is not the name of the abstract method;

pom.xml full dependency configuration

<dependencies>
   <dependency>
     <groupId>junit</groupId>
     <artifactId>junit</artifactId>
     <version>4.11</version>
     <scope>test</scope>
   </dependency>

   <!--  Spring frame  -->
   <dependency>
     <groupId>org.springframework</groupId>
     <artifactId>spring-webmvc</artifactId>
     <version>4.3.8.RELEASE</version>
   </dependency>

   <!-- Spring-JDBC -->
   <!-- Version: and spring-webmvc Completely consistent -->
   <dependency>
     <groupId>org.springframework</groupId>
     <artifactId>spring-jdbc</artifactId>
     <version>4.3.8.RELEASE</version>
   </dependency>

   <!--  Thymeleaf Template  -->
   <dependency>
     <groupId>org.thymeleaf</groupId>
     <artifactId>thymeleaf</artifactId>
     <version>3.0.11.RELEASE</version>
   </dependency>

   <dependency>
     <groupId>org.thymeleaf</groupId>
     <artifactId>thymeleaf-spring4</artifactId>
     <version>3.0.11.RELEASE</version>
   </dependency>

   <!-- MyBatis frame -->
   <!-- Optional version: 3.5.0~3.5.3 -->
   <dependency>
     <groupId>org.mybatis</groupId>
     <artifactId>mybatis</artifactId>
     <version>3.5.3</version>
   </dependency>

   <!-- MyBatis integration Spring -->
   <!-- Optional version: 2.0.0~2.0.3 -->
   <dependency>
     <groupId>org.mybatis</groupId>
     <artifactId>mybatis-spring</artifactId>
     <version>2.0.3</version>
   </dependency>

   <!-- MySQL -->
   <!-- Optional version: 5.1.34~5.1.39，8.0.12~8.0.18 -->
   <dependency>
     <groupId>mysql</groupId>
     <artifactId>mysql-connector-java</artifactId>
     <version>8.0.18</version>
   </dependency>

   <!-- Database connection pool -->
   <!-- Optional version: 1.2~1.4 -->
   <dependency>
     <groupId>commons-dbcp</groupId>
     <artifactId>commons-dbcp</artifactId>
     <version>1.4</version>
   </dependency>

 </dependencies>

Complete spring-dao.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:jdbc="http://www.springframework.org/schema/jdbc"
       xmlns:jee="http://www.springframework.org/schema/jee"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xmlns:mvc="http://www.springframework.org/schema/mvc"
       xmlns:util="http://www.springframework.org/schema/util"
       xmlns:jpa="http://www.springframework.org/schema/data/jpa"
       xsi:schemaLocation="
		http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.2.xsd
		http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.2.xsd
		http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.2.xsd
		http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee-3.2.xsd
		http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.2.xsd
		http://www.springframework.org/schema/data/jpa http://www.springframework.org/schema/data/jpa/spring-jpa-1.3.xsd
		http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.2.xsd
		http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-3.2.xsd
		http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-3.2.xsd">

        <!-- Read the configuration file connecting to the database -->
        <util:properties id="dbConfig" location="classpath:db.properties"/>

        <!-- Data source: BasicDataSource -->
        <bean id="basicDataSource" class="org.apache.commons.dbcp.BasicDataSource">
            <property name="url" value="#{dbConfig.url}"/>
            <property name="driverClassName" value="#{dbConfig.driver}"/>
            <property name="username" value="#{dbConfig.username}"/>
            <property name="password" value="#{dbConfig.password}"/>
            <property name="initialSize" value="#{dbConfig.initialSize}"/>
            <property name="maxActive" value="#{dbConfig.maxActive}"/>
        </bean>

        <!--  to configure MapperScannerConfigurer  -->
        <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
            <property name="basePackage" value="cn.tedu.mybatis"></property>
        </bean>

        <!--  to configure SqlSessionFactoryBean  -->
        <bean class="org.mybatis.spring.SqlSessionFactoryBean">
            <!--  to configure xml file location -->
            <property name="mapperLocations" value="classpath:mappers/*.xml"/>
            <!-- Configure data sources -->
            <property name="dataSource" ref="basicDataSource"/>
        </bean>
</beans>

Complete SomeMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>  
<!DOCTYPE mapper PUBLIC "-//ibatis.apache.org//DTD Mapper 3.0//EN"      
 "http://ibatis.apache.org/dtd/ibatis-3-mapper.dtd">

<!-- namespace Property: the full name of the interface -->
<mapper namespace="cn.tedu.mybatis.UserMapper">
    <insert id="createUser">
        INSERT INTO t_user (
            username, password, age, phone, email
        ) VALUES (
            #{ username }, #{ password }, #{ age }, #{ phone }, #{ email }
        )
    </insert>

    <delete id="deleteById">
        DELETE FROM t_user WHERE id = #{ id }
    </delete>

    <update id="updatePassword">
        UPDATE t_user SET password = #{ password }
    </update>

    <!--  The query statement must be defined resultType perhaps resultMap Property, return value  -->
    <select id="selectCount" resultType="java.lang.Integer">
        SELECT COUNT(*) FROM t_user
    </select>

    <select id="findById" resultType="cn.tedu.mybatis.User">
        SELECT * FROM t_user WHERE id = #{ id }
    </select>
</mapper>

Complete UserMapper class

package cn.tedu.mybatis;

public interface UserMapper {

    // Method name can be customized, but cannot be overloaded!!! (myBatis cannot recognize which method is called)
    // The return value of the method is the number of affected rows (addition, deletion and modification)
    Integer createUser (User user);

    Integer deleteById (Integer id);

    Integer updatePassword (String password);

    Integer selectCount();

    User findById(Integer id);
}

Complete unit test class

package cn.tedu.mybatis.mybatis;

import cn.tedu.mybatis.User;
import cn.tedu.mybatis.UserMapper;
import org.apache.commons.dbcp.BasicDataSource;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.mapper.MapperScannerConfigurer;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import java.net.Inet4Address;
import java.sql.Connection;
import java.sql.SQLException;

public class Tests {
    MapperScannerConfigurer msc;
    ClassPathXmlApplicationContext ac;
    UserMapper userMapper;

    @Test
    public void getConnection () throws SQLException {
        BasicDataSource dataSource = (BasicDataSource) ac.getBean("basicDataSource");
        Connection conn = dataSource.getConnection();
        System.out.println(conn);
    }

    @Test
    public void createUser () {
        User user = new User();
        user.setUsername("Tom");
        user.setPassword("123456");
        user.setAge(25);
        user.setPhone("17520694906");
        user.setEmail("324224@qq.com");
        Integer rows =  userMapper.createUser(user);
        System.out.println("rows = " + rows);
    }

    @Test
    public void deleteById () {
        Integer id = 5;
        Integer rows = userMapper.deleteById(id);
        System.out.println("rows = " + rows);
    }

    @Test
    public void updatePassword () {
        String password = "123456";
        Integer rows = userMapper.updatePassword(password);
        System.out.println("rows = " + rows);
    }

    @Test
    public void selectCount () {
        Integer count = userMapper.selectCount();
        System.out.println("count = " + count);
    }

    @Test
    public void findById () {
        Integer id = 6;
        User user = userMapper.findById(id);
        System.out.println("user = " + user);
    }

    @Before
    public void doBefore () {
        ac = new ClassPathXmlApplicationContext("spring-dao.xml");
        userMapper = ac.getBean("userMapper", UserMapper.class);
    }

    @After
    public void doAfter () {
        ac.close();
    }
}

If this article is helpful to you, please give a simple praise, thank you~

Tags: Java Database

Posted on Fri, 22 Oct 2021 10:38:49 -0400 by EPJS

Programmer Help

Detailed explanation of Java - Mybatis framework

1. Pre SQL statement

2. Role of mybatis framework

3. Create project

4. Connect to the database

5. Abstract method

6. SQL statement

7. Delete id =? Data

8. Change the passwords of all users to?

9. Count the number of users in the current table

10. Query id =? User's data

11. Query the data of all users

----------------------------------------

Appendix 1: solution to prompt Invalid bound statement (not found)

pom.xml full dependency configuration

Complete spring-dao.xml

Complete SomeMapper.xml

Complete UserMapper class

Complete unit test class

Hot Tags