mybatis Framework Learning-CRUD Annotation

Preliminary preparation of mysql

create database cong
use cong;
create table account(
    id int primary key auto_increment,
    name varchar(40),
    money float
)character set utf8 collate utf8_general_ci;

insert into account(name,money) values('aaa',1000);
insert into account(name,money) values('bbb',1000);
insert into account(name,money) values('ccc',1000);


1.maven creates projects and imports dependencies

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns=""



2. Entity class

package com.cong.pojo;

public class Account {
    private int id;
    private String name;
    private float money;

    public String toString() {
        return "Account{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", money=" + money +

    public int getId() {
        return id;

    public void setId(int id) { = id;

    public String getName() {
        return name;

    public void setName(String name) { = name;

    public float getMoney() {
        return money;

    public void setMoney(float money) { = money;

3. Configuration file SqlMapConfig.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
        PUBLIC "-// Config 3.0//EN"
        <package name="com.cong.pojo"></package>
    <environments default="account">
        <environment id="account">
            <!-- Use JDBC Things Management -->
            <transactionManager type="JDBC"></transactionManager>
            <!-- Database connection pool -->
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"></property>
                <property name="url" value="jdbc:mysql://localhost:3306/cong"></property>
                <property name="username" value="root"></property>
                <property name="password" value="123456"></property>
    <!-- Specify the location of the mapping configuration file, which refers to each mapper Independent configuration file
     If you configure it with annotations, you should use it here class Property specifies annotated mapper Fully qualified class name-->
        <mapper class="com.cong.mapper.AccountMapper"></mapper>

# Set root category priority to INFO and its only appender to CONSOLE.
#log4j.rootCategory=INFO, CONSOLE            debug   info   warn error fatal
log4j.rootCategory=debug, CONSOLE, LOGFILE

# Set the enterprise logger category to FATAL and its only appender to CONSOLE., CONSOLE

# CONSOLE is set to be a ConsoleAppender using a PatternLayout.
log4j.appender.CONSOLE.layout.ConversionPattern=%d{ISO8601} %-6r [%15.15t] %-5p %30.30c %x - %m\n

# LOGFILE is set to be a File appender using a PatternLayout.
log4j.appender.LOGFILE.layout.ConversionPattern=%d{ISO8601} %-6r [%15.15t] %-5p %30.30c %x - %m\n

5.mapper interface

package com.cong.mapper;

import com.cong.pojo.Account;
import org.apache.ibatis.annotations.*;

import java.util.List;

public interface AccountMapper {
    @Select("select * from account")
    List<Account> findAll();
    @Select("select * from account where id = #{id}")
    Account findById(int id);
    @Select("select * from account where name like #{name}")
    //@Select("select * from account where name like '%${value}%'")
    List<Account> findByName(String name);
    @Select("select count(*) from account")
    int findTotal();
    @Insert("insert into account(name,money) values(#{name},#{money})")
    //@SelectKey(statement="select last_insert_id()",before=false,keyProperty="id",resultType=Integer.class,keyColumn="id")
    @Options(useGeneratedKeys=true, keyProperty="id", keyColumn="id")//Returns the self-incrementing field id,The same is true of the above statement.
    int saveAccount(Account account);//The return value is an impact entry for the database, not an insert id,Want to get self-increasing id,You only need to visit this object once.
    @Delete("delete from account where id = #{id}")
    void deleteAccount(int id);
    @Update("update account set name=#{name},money=#{money} where id = #{id}")
    void updateAccount(Account account);
     * We are now working on the object Account, if there are more Account attributes,
     * We're just updating a single attribute of Account. Shall we regenerate an Account and pass it in?
     * No,We can use the following form of update
     * We use the @Param parameter, just passing in the parameters we are interested in.
     * The incoming parameter is name, which is redefined as n in @Param.
     * Then it can be directly used in Sql through ${n}, which is actually to solve the impedance mismatch between function parameters and Sql parameters.
    @Update("update account set name=#{n} where id = #{i}")
    void updateAccountByParam(@Param("n")String name,@Param("i")int id);

6. Test class

import com.cong.mapper.AccountMapper;
import com.cong.pojo.Account;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.util.List;

public class TestCRUD {
    InputStream inputStream = null;
    SqlSession sqlSession = null;
    AccountMapper mapper = null;

    public void findAll() {
        List<Account> accounts = mapper.findAll();
        for (Account account : accounts) {

    public void findById() {
        Account account = mapper.findById(2);

    public void findByName() {
        List<Account> accounts = mapper.findByName("%on%");//like #Writing of {name}
//        List<Account> accounts = mapper.findByName("on");//like '%${value}%' Writing
        for (Account account : accounts) {
    public void findTotal(){
        int accountTotal = mapper.findTotal();
        System.out.println("account total:" + accountTotal);
    public void save() {
        Account account = new Account();
        //stay mapper Written in the returned self-incrementing object's of the __________ id,So the object here will contain id attribute

    public void update() {
        Account account = new Account();
    public void updateByParam() {
        String name = "Rainbow Cai";
        int id = 4;

    public void deleteAccount() {

    @Before//Execution before test method execution
    public void init() throws Exception {
        //1.Read configuration file to generate byte input stream
        inputStream = Resources.getResourceAsStream("SqlMapConfig.xml");
        //2.Obtain SqlSessionFactoryBuilder
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(inputStream);
        //3.Use factory production SqlSession object
        sqlSession = factory.openSession();
        //4.Use SqlSession Establish mapper Proxy object of interface
        mapper = sqlSession.getMapper(AccountMapper.class);
        //5.Then execute using proxy objects mapper Method in Interface

    @After//Execution after test method execution
    public void close() throws Exception {
        //Submission transaction,Additions, deletions and changes require transaction submission before database changes occur
        //Releasing resources

7. Directory structure



These annotations already provide a replacement for ordinary SQL statements, and if your requirements are more complex than this, it's best to configure them using xml.

It is better to write SQL directly to control the coordination between SQL and its functions. It is also a common practice to place the unified management of SQL.

Another point: Annotation and Xml configurations can exist at the same time, which is also necessary.

Some of our Sql s are very long and complex, so it is very difficult to manage them in code, so it is necessary to provide unified xml management.

Note that the annotations in this article are provided by mybatis itself and have nothing to do with Spring's AOP and IoC features.

What we're doing now is using mybatis alone for development. As for the combination of mybatis and Spring,

In fact, the SqlSession Factory of mybatis is delegated to Spring management, coupled with IoC features, which can save a lot of code when used.

Reference resources:

Tags: PHP log4j Apache Mybatis xml

Posted on Wed, 09 Oct 2019 01:23:19 -0700 by sampledformat