Mybatis learning record

Posted by aquayle on Fri, 10 Dec 2021 16:02:34 +0100

Introduction to Mybatis

MyBatis is an excellent persistence layer framework, which supports custom SQL, stored procedures and advanced mapping. MyBatis eliminates almost all JDBC code and the work of setting parameters and obtaining result sets. MyBatis can configure and map primitive types, interfaces and Java POJO s (Plain Old Java Objects) to records in the database through simple XML or annotations.

Persistent layer

  • Persistence is the process of transforming a program into a persistent and transient state
Why do you need mybatis
  • Traditional JDBC code is too complex and needs a framework to simplify it
    • Help programmers store data into the database

Start trying to create Mybatis

  • Create a simple Maven project
  • Delete the src directory and take the simple Maven directory as the parent directory
  • Import dependency (jar package)
<!-- https://mvnrepository.com/artifact/junit/junit -->
<dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.12</version>
    <scope>test</scope>
</dependency>

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

<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.16</version>
</dependency>

step

  • Writing entity classes
  • Write core configuration file
  • Write interface
  • Write mapper XML file
  • test

Core profile

The XML configuration file contains the core settings for the MyBatis system, including the data source to obtain the database connection instance and the transaction manager to determine the transaction scope and control mode

  • Create mybatis config. Under resources XML file

  • <?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
     PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
     "http://mybatis.org/dtd/mybatis-3-config.dtd">
<!--        configuration Core profile-->
<configuration>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/book?useSSL=true&amp;useUnicode=true&amp;serverTimezone=CST&amp;characterEncoding=utf-8"/>
                <property name="username" value="root"/>
                <property name="password" value=""/>
            </dataSource>
        </environment>
    </environments>

    <!--    <mappers>-->
    <!--        <mapper resource="org/mybatis/example/BlogMapper.xml"/>-->
    <!--    </mappers>-->
</configuration>

    Building SqlSessionFactory from XML

  • Create a tool class utils

  • Add static method

public class MybatisUtils {

    private static SqlSessionFactory sqlSessionFactory;

    static {
        try {
            //Get sqlSessionFactory object
            String resource = "mybatis-config.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static SqlSession getSqlSession() {//Get SqlSession object
        return sqlSessionFactory.openSession();
    }
}
Create entity class
public class User {
    private int id;
    private String name;
    private String pwd;

    public User() {
    }

    public User(int id, String name, String pwd) {
        this.id = id;
        this.name = name;
        this.pwd = pwd;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getPwd() {
        return pwd;
    }

    public void setPwd(String pwd) {
        this.pwd = pwd;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", pwd='" + pwd + '\'' +
                '}';
    }
}

dao interface

public interface UserDao {

    List<User> getUserList();
}

The interface implementation class is changed from the original DaoImpl to a mapper XML configuration file

<?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 Bind a Dao/Mapper Interface-->
    <mapper namespace="com.suwenlong.dao.UserDao">

    <select id="getUserList" resultType="com.suwenlong.bean.User">
        select * from book.adminstor;
    </select>
</mapper>

junit package for testing

  • During the test, I encountered a problem - error: Java does not support release 5

Solution 1

① Click "File" - > "Project Structure" in Intellij to see whether the Java version in the "Project" and "Module" columns is consistent with the local version:

② Click "Settings" – > "Bulid, Execution,Deployment" – > "Java Compiler", and set the Target bytecode version to the local Java version. (you can configure Project bytecode version to the local Java version once and for all in Default Settings)

But in the end, I can run it. It's no longer this error

Solution 2

① At maven address \ conf \ setting xml
Set default jdk version in

The code is as follows:

<profile>
    <id>development</id>
    <activation>
        <jdk>1.8</jdk>
        <activeByDefault>true</activeByDefault>
    </activation>
    <properties>
        <maven.compiler.source>1.8</maven.compiler.source>
        <maven.compiler.target>1.8</maven.compiler.target>
        <maven.compiler.compilerVersion>1.8</maven.compiler.compilerVersion>
    </properties>
</profile>

② And copy a copy to C:\Users \ local user name In m2

Official introduction to SQLSessionFactoryBuiler, SQLSessionFactory and SQLSession

NullPointerException has been bothering me for a long time, which is mybatisutils The sqlSessionFactory global variable declaration in the java file has bothered me all day to delete the local variable declaration.

CLUD

  • Add, delete, modify and query

namespace

  • The package name in the namespace should be consistent with that in the Mapper interface

Update

Delete

select

  • id is the corresponding method name in the namespace
  • resultType: return value of SQL statement (result set)
  • parameterType parameter type (common types can be used without writing)

Insert

  • Write interface
public interface UserMapper {

 List<User> getUserList();//Get all users

 User getUserById(int id);//Get user id

 int addUser(User user);//Insert a user

 int updateUser(User user);//Update user

 int deleteUser(int id);//Update user
}

  • Write the corresponding SQL statement in Mapper

  • <?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">
<!--        namespace Bind a corresponding Mapper Interface-->
 <mapper namespace="com.suwenlong.dao.UserMapper">

 <select id="getUserList" resultType="com.suwenlong.bean.User">
     select * from mybatis.user;
 </select>

 <select id="getUserById" parameterType="int" resultType="com.suwenlong.bean.User">
     select * from mybatis.user  where id = #{id};
 </select>

<!--    Object. It can be accessed directly-->
 <insert id="addUser" parameterType="com.suwenlong.bean.User">
     insert into mybatis.user(id,name,pwd) values (#{id},#{name},#{pwd})
 </insert>

 <update id="updateUser" parameterType="com.suwenlong.bean.User">
     update mybatis.user set name=#{name},pwd=#{pwd} where id=#{id};
 </update>

 <delete id="deleteUser" parameterType="int">
     delete from mybatis.user where id=#{id};
 </delete>
</mapper>

  • test

public class UserDaoTest {
    @Test
    public void test(){
        SqlSession sqlSession = null;
        List<User> userList = null;
        try {
            //Get SqlSession object
            sqlSession = MybatisUtils.getSqlSession();
            //Execute SQL
            //Method 1: getMapper
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            userList = userMapper.getUserList();
         /*//        Mode II
     List<User> userList1 = sqlSession.selectList("com.suwenlong.dao.UserMapper.getUserList");*/
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            sqlSession.close();
        }
        for (User user : userList) {
            System.out.println(user);        }

    }

    @Test
    public void getUserById(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();

        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        User user = mapper.getUserById(1);
        System.out.println(user);

        sqlSession.close();
    }

    @Test
    public void addUser(){//Add, delete and modify transactions that need to be committed
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        int res = mapper.addUser(new User(4, "Ha ha ha", "12356"));
        sqlSession.commit();//Commit transaction
        sqlSession.close();
    }

    @Test
    public void updateUser(){//Add, delete and modify transactions that need to be committed
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        int res = mapper.updateUser(new User(4,"updete","123456"));
        sqlSession.commit();//Commit transaction
        sqlSession.close();
    }

    @Test
    public void deleteUser(){//Add, delete and modify transactions that need to be committed
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        int res = mapper.deleteUser(4);
        sqlSession.commit();//Commit transaction
        sqlSession.close();
    }
}

Two implementations of fuzzy query

  • java code query execution depends on, pass wildcard%%
List<User> userList = mappser.getUserList("%Lee%")
  • Use wildcards in SQL splicing
select * from mybatis.user where name like %#{values}%

to configure

properties(Properties)
settings(Settings)
typeAliases(Type alias)
typeHandlers(Type (processor)
objectFactory(Object factory)
plugins(Plug in)
environments(Environment configuration)
environment(Environment variables)
transactionManager(Transaction manager)
dataSource((data source)
databaseIdProvider(Database (vendor ID)
mappers(Mapper)

Environment configuration

  • mybatis can be configured to adapt to a variety of environments
  • However, remember that although multiple environments can be configured, only one environment can be selected for each SqlSessionFactory instance.
  • The default transaction manager of Mybatis is JDBC and the connection pool is POOLED

properties

  • Attributes can be configured externally, dynamically replaced, configured in java files, or passed through child elements of the properties element.
  • Write a DP Properties configuration file
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mybatis?useSSL=true&serverTimezone=CST&useUnicode=true&characterEncoding=utf-8
uername=root
password=
In mybatis config Importing external file DP. XML properties
<properties resource="dp.properties"/>

  • If two files have the same field, the external configuration file DP. Is preferred properties

Type aliases

  • Type alias sets an abbreviated name for a Java type.
  • Fully qualified class name writing intended to reduce redundancy.
<!--    You can give an alias to an entity class-->
<typeAliases>
    <typeAlias type="com.suwenlong.bean.User" alias="User"/>
</typeAliases>

  • You can also specify a package name. MyBatis will search for the required Java Bean under the package name
  • In the absence of annotations, the initial lowercase unqualified class name of the Bean will be used as its alias. For example, domain blog. The alias of author is author
<!--    You can give an alias to an entity class-->
<typeAliases>
    <pa type="com.suwenlong.bean" />
</typeAliases>
  • If there is an annotation, the alias is its annotation value

  • If there are few entity classes, you can use mybatis config XML configuration alias
  • When there are many entity classes, the entity class annotation can be aliased
@Alias("user")
public class User{}

set up

  • These are extremely important tuning settings in MyBatis, which change the runtime behavior of MyBatis.

mappers

  • Is to register that thing
Method 1: use resource
<mappers>
        <mapper resource="com/suwenlong/dao/UserMapper.xml"/>
</mappers>
Method 2: register with class file binding
<mappers>
        <mapper class="com/suwenlong/dao/UserMapper"/>
</mappers>
  • Mode II defect
    • Interface and its configuration Mapper file must have the same name
    • Interface and its Mapper configuration file must be in the same directory
  • Mode 2 benefits
    • You can use comments
Method 3: use scanning package
<mappers>
        <package class="com/suwenlong/dao"/>
</mappers>
  • Mode III defect
    • Interface and its configuration Mapper file must have the same name
    • Interface and its Mapper configuration file must be in the same directory

resultMap result set mapping

id name pwd

id name password

  • To solve the problem that the data retrieved from the database is null (the query result is empty because the User attribute in the entity class is inconsistent with the attribute name in the database)

  • Map the result set to the same User entity class

Log logImpl

  • Troubleshooting method: South debug
  • Log project

[the external chain image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-yzx58xwu-1639146255495) (C: \ users \ 86152 \ appdata \ roaming \ typora user images \ image-20211121160634657. PNG)]

  • SLF4J
  • LOG4J [Master]
  • LOG4J2
  • JDK_LOGGING
  • COMMONS_LOGGING
  • STDOUT_LOGGING [mastering]
  • NO_LOGGING
<settings>
        <setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>

LOG4J

  • LOG4J is troublesome. It's said separately here
  • Baidu Encyclopedia

Log4j is an open source project of Apache. By using log4j, we can control the destination of log information transmission to console, files and GUI components. We can also control the output format of each log

  • Import package first
<!-- https://mvnrepository.com/artifact/log4j/log4j -->
<dependency>
    <groupId>log4j</groupId>
    <artifactId>log4j</artifactId>
    <version>1.2.17</version>
</dependency>
  • Configure log4j Properties configuration file
# priority  :debug<info<warn<error
#you cannot specify every priority with different file for log4j
#Control output
log4j.rootLogger=debug,stdout,info,warn

#console
log4j.appender.stdout=org.apache.log4j.ConsoleAppender 
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout 
log4j.appender.stdout.layout.ConversionPattern= [%d{yyyy-MM-dd HH:mm:ss a}]:%p %l%m%n
#info log
log4j.logger.info=info
log4j.appender.info=org.apache.log4j.DailyRollingFileAppender 
log4j.appender.info.DatePattern='_'yyyy-MM-dd'.log'
log4j.appender.info.File=./src/com/hp/log/info.log
log4j.appender.info.Append=true
log4j.appender.info.Threshold=INFO
log4j.appender.info.layout=org.apache.log4j.PatternLayout 
log4j.appender.info.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss a} [Thread: %t][ Class:%c >> Method: %l ]%n%p:%m%n
#debug log
log4j.logger.debug=debug
log4j.appender.debug=org.apache.log4j.DailyRollingFileAppender 
log4j.appender.debug.DatePattern='_'yyyy-MM-dd'.log'
log4j.appender.debug.File=./src/com/hp/log/debug.log
log4j.appender.debug.Append=true
log4j.appender.debug.Threshold=DEBUG
log4j.appender.debug.layout=org.apache.log4j.PatternLayout 
log4j.appender.debug.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss a} [Thread: %t][ Class:%c >> Method: %l ]%n%p:%m%n
#warn log
log4j.logger.warn=warn
log4j.appender.warn=org.apache.log4j.DailyRollingFileAppender 
log4j.appender.warn.DatePattern='_'yyyy-MM-dd'.log'
log4j.appender.warn.File=./src/com/hp/log/warn.log
log4j.appender.warn.Append=true
log4j.appender.warn.Threshold=WARN
log4j.appender.warn.layout=org.apache.log4j.PatternLayout 
log4j.appender.warn.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss a} [Thread: %t][ Class:%c >> Method: %l ]%n%p:%m%n
#error
log4j.logger.error=error
log4j.appender.error = org.apache.log4j.DailyRollingFileAppender
log4j.appender.error.DatePattern='_'yyyy-MM-dd'.log'
log4j.appender.error.File = ./src/com/hp/log/error.log 
log4j.appender.error.Append = true
log4j.appender.error.Threshold = ERROR 
log4j.appender.error.layout = org.apache.log4j.PatternLayout
log4j.appender.error.layout.ConversionPattern = %d{yyyy-MM-dd HH:mm:ss a} [Thread: %t][ Class:%c >> Method: %l ]%n%p:%m%n
  • Configure settings
<settings>
    <!--        Standard log engineering implementation-->
    <setting name="logImpl" value="LOG4J"/>
</settings>
  • Direct test run
Or write a test class
@Test
public void testLog4j(){

    logger.info("info:get into testLog4j");
    logger.debug("debug:get into testLog4j");
    logger.error("error:get into testLog4j");
}

Paging query

  • Similar to select * from user limit 0,2; But not yet. Leave a slot here and have time to fill it later

  • First implement the interface class

List<User> getUserByLimit(Map<String,Integer> map);//paging
  • Implement Mapper
<!--    Paging query-->
<select id="getUserByLimit" resultType="User" parameterType="map">
    select * from mybatis.user limit #{startIndex},#{pageSize};
</select>
  • Writing test classes
@Test
public void getUserByLimit(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    HashMap<String, Integer> map = new HashMap<>();
    map.put("startIndex",0);
    map.put("pageSize",2);
    List<User> userByLimit = mapper.getUserByLimit(map);
    for (User user : userByLimit) {
        System.out.println(user);
    }
    sqlSession.close();
}

Using annotation development

  • Just understand it and annotate it directly on the interface
@Select("select * from user")

  • Then bind the interface in the core configuration file
<mappers>
        <mapper class="com/suwenlong/dao/UserMapper"/>
</mappers>

Many to one query

  • To tell you the truth, I didn't understand it. Come back and take time to read it

One to many query

  • I haven't seen it yet. Take time to see it

Dynamic SQL

  • Dynamic SQL refers to generating different SQL statements according to different conditions
  • The official introduction is as follows

If you have used JSTL or any text processor based on XML like language before, you may feel familiar with dynamic SQL elements. In previous versions of MyBatis, it took time to understand a large number of elements. With the powerful expression based on OGNL, MyBatis 3 replaces most of the previous elements and greatly simplifies the element types. Now there are fewer element types to learn than half of the original ones.

  • if
  • choose (when, otherwise)
  • trim (where, set)
  • foreach
Build environment
CREATE TABLE `blog`(
`id` VARCHAR(50) NOT NULL COMMENT 'Blog id',
`title` VARCHAR(100) NOT NULL COMMENT 'Blog title',
`author` VARCHAR(30) NOT NULL COMMENT 'Create author',
`create_time` DATETIME NOT NULL COMMENT 'Creation time', 
`view` INT(30) NOT NULL COMMENT 'Reading volume'
)ENGINE=INNODB DEFAULT CHARSET=UTF8
Create a basic project
  • Guide Package
  • Write configuration file
  • Writing entity classes
  • Write Mapper interface and Mapper.xml corresponding to entity class XML file

IF statement

This statement provides an optional function to find text. If "title" is not passed in, all blogs in "ACTIVE" status will be returned; If the "title" parameter is passed in, the "title" column will be vaguely searched and the corresponding BLOG results will be returned

<select id="queryBlogIF" parameterType="map" resultType="blog">
    select * from mybatis.blog where 1=1
    <if test="title != null">
        and title=#{title}
    </if>
    <if test="author != null">
        and author=#{author}
    </if>
</select>

choose(when,otherwise)

Sometimes, we don't want to use all the conditions, but just want to choose one from multiple conditions. In this case, MyBatis provides a choose element, which is a bit like a switch statement in Java.

  • choose must select one to execute
<select id="queryBlogChoose" parameterType="map" resultType="blog">
    select * from mybatis.blog
    <where>
        <choose>
            <when test="title != null">
                title=#{title}
            </when>
            <when test="author != null">
                and author=#{author}
            </when>
            <otherwise>
                and view=#{view}
            </otherwise>
        </choose>
    </where>
</select>

trim(where,set)

The WHERE element inserts the "WHERE" clause only if the child element returns anything. Moreover, if clause starts with the "AND" OR ", WHERE element removes them.

<select id="queryBlogIF" parameterType="map" resultType="blog">
    select * from mybatis.blog
    <where>
        <if test="title != null">
            and title=#{title}
        </if>
        <if test="author != null">
            and author=#{author}
        </if>
    </where>
</select>

<update id="updateBlog" parameterType="map">
    update mybatis.blog
    <set>
        <if test="title != null">
            title = #{title},
        </if>
        <if test="author != null">
            author = #{author}
        </if>
    </set>
    where id = #{id}
</update>

SQL fragment

<sql id="if-title-author">
    <if test="title != null">
        and title=#{title}
    </if>
    <if test="author != null">
        and author=#{author}
    </if>
</sql>

<select id="queryBlogIF" parameterType="map" resultType="blog">
    select * from mybatis.blog
    <where>
        <include refid="if-title-author"></include>
    </where>
</select>

foreach

The foreach element is very powerful. It allows you to specify a collection and declare collection items and index variables that can be used in the element body. It also allows you to specify the beginning and end strings and the separator between collection item iterations. This element will not add extra delimiters by mistake. See how smart it is!

<!--    select * from mybatis.blog where 1=1 and (id=1 or id=2 or id=3)-->
<select id="queryBlogForeach" parameterType="map" resultType="blog">
    select * from mybatis.blog
    <where>
        <foreach collection="ids" item="id" open="and (" close=")" separator="or">
            id=#{id}
        </foreach>
    </where>
</select>

@Test
    public void queryBlogForeach(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        Blogmapper mapper = sqlSession.getMapper(Blogmapper.class);
        HashMap hashMap = new HashMap();
        ArrayList<String> ids = new ArrayList<>();
        ids.add("6a638b43921b4a3fa720022b3093061c");
        ids.add("040d32ef814248cc8d9425566afe991c");
        hashMap.put("ids",ids);
        List<Blog> blogs = mapper.queryBlogForeach(hashMap);
        for (Blog blog : blogs) {
            System.out.println(blog);

        }
        sqlSession.close();
    }
Dynamic SQL is splicing statements. We just need to ensure the correctness of SQL statements and arrange and combine them according to the format of SQL

cache

  • Because each connection to the database consumes resources and time, but we can store the results of a query in memory, so we don't need to call the database every time

  • Caching is to solve the problem of high concurrency

Cache invalidation
  • Query different things
  • The addition, deletion and modification operation will change the original data, and the cache must be refreshed
  • Query different mapper xml
  • Manual cache cleanup
sqlSession.cleanCache();//Manual cache cleanup

Entity classes are best serialized
  • implements Serializable

[the external chain image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (IMG gknebees-1639146255497) (C: \ users \ 86152 \ appdata \ roaming \ typora \ user images \ image-20211122161540294. PNG)]

Topics: Java Maven Mybatis intellij-idea

Hot Topics

©2024 Programmer Think