Learn these points and master Mybatis easily

1, Mybatis quick start

1.1 introduction to mybatis

MyBatis is an excellent persistence layer framework that supports common SQL queries, stored procedures and advanced mapping. MyBatis eliminates almost all the manual setting of JDBC code and parameters and the retrieval encapsulation of result sets. MyBatis can use simple XML or annotations for configuration and original mapping to map the interface and Java POJO (Plain Old Java Objects) into records in the database. JDBC -  MyBatis -  Hibernate

1.2 Mybatis environment construction

1.2.1 add Maven coordinates

<dependencies>
        <!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
<dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.4.4</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.21</version>
        </dependency>
    </dependencies>

1.2.2 table building

CREATE TABLE users(id iNT PRIMARY KEY AUTO_INCREMENT, NAME VARcHAR(20), age iNT);
INSERT INTO users(NAME, age) VALUES('Tom', 12);
INSERT INTO users(NAME, age) VALUES('Jack', 11);

1.2.3 add mybatis configuration 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>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC" />
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver" />
                <property name="url" value="jdbc:mysql://localhost:3306/test" />
                <property name="username" value="root" />
                <property name="password" value="root" />
            </dataSource>
        </environment>
    </environments>
</configuration>

1.2.4 define the entity class of the table

package com.entity;
public class User {
    private int id;
    private String name;
    private int age;
    //get,set method
}

1.2.5 define userMapper interface

package com.itmayiedu.mapper;
import com.itmayiedu.entity.User;
public interface UserMapper {
    public User getUser(int id);
}

1.2.6 define the sql mapping file userMapper.xml that operates the users table

<?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 namespace="com.itmayiedu.mapper.UserMapper">
    <select id="getUser" parameterType="int" resultType="com.itmayiedu.entity.User">
        SELECT *
        FROM users where id =#{id}
    </select>
</mapper>

1.2.7 load configuration file in mybatis.xml file

<mappers>
<mapper resource="mapper/userMapper.xml" />
</mappers>

1.2.8 mybatis.xml test method

import java.io.File;
import java.io.IOException;
import java.io.Reader;
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 com.itmayiedu.entity.User;
public class TestMybatis {
    public static void main(String[] args) throws IOException {
        String resource = "mybatis.xml";
        // Read configuration file
        Reader reader = Resources.getResourceAsReader(resource);
        // Get session factory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
        SqlSession openSession = sqlSessionFactory.openSession();
        // query
        String sql = "com.itmayiedu.mapper.UserMapper.getUser";
        // Call api query
        User user = openSession.selectOne(sql, 1);
        System.out.println(user.toString());
    }
}

1.2.9 add case Xml:

  <insert id="addUser" parameterType="com.itmayiedu.entity.User" >
    
    INSERT INTO users(NAME, age) VALUES(#{name}, #{age});
    </insert>

code:

static public void add() throws IOException{
        String resource = "mybatis.xml";
        // Read configuration file
        Reader reader = Resources.getResourceAsReader(resource);
        // Get session factory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
        SqlSession openSession = sqlSessionFactory.openSession();
        // query
        String sql = "com.itmayiedu.mapper.UserMapper.addUser";
        // Call api query
        User userPa = new User();
        userPa.setAge(19);
        userPa.setName("Zhang San");
        int reuslt = openSession.insert(sql, userPa);
        System.out.println(reuslt);
    }

1.2.10 delete Xml:

 <delete id="delUser" parameterType="int" >
      delete from users where id=#{id}
    </delete>

code:

    static public void delUser() throws IOException{
        String resource = "mybatis.xml";
        // Read configuration file
        Reader reader = Resources.getResourceAsReader(resource);
        // Get session factory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
        SqlSession openSession = sqlSessionFactory.openSession();
        // query
        String sql = "com.itmayiedu.mapper.UserMapper.delUser";
        int reuslt = openSession.delete(sql,1);
        System.out.println(reuslt);
    }

2, sql injection case

2.1 create table + test data

create table user_table(  
    id      int Primary key,  
    username    varchar(30),  
    password    varchar(30)  
);  
insert into user_table values(1,'yushengjun-1','12345');  
insert into user_table values(2,'yushengjun-2','12345');

2.2 loading JDBC

String username = "yushengjun-1";
String password = "12345";
String sql = "SELECT id,username FROM user_table WHERE " + "username='" + username + "'AND " + "password='"
                + password + "'";
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "root");
PreparedStatement stat = con.prepareStatement(sql);
System.out.println(stat.toString());
ResultSet rs = stat.executeQuery();
while (rs.next()) {
String id = rs.getString(1);
String name = rs.getString(2);
System.out.println("id:" + id + "---name:" + name);
}

2.3 set the value of username to username = 'or 1 = 1 -- or username or 1='1

Because -- represents SQL comments, the following statements are ignored;

Because 1 = 1 is constant, username='' OR 1=1 is constant, so the SQL statement is equivalent to:

2.4 SQL injection solution

Step 1: compile sql
Step 2: execute sql
Advantages: precompiled sql statements

String username = "username='  OR 1=1 -- ";
        String password = "12345";
        // String sql = "SELECT id,username FROM user_table WHERE " +
        // "username='" + username + "'AND " + "password='"
        // + password + "'";
        String sql = "SELECT id,username FROM user_table WHERE username=? AND password=?";
        Class.forName("com.mysql.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "root");
        PreparedStatement stat = con.prepareStatement(sql);
        stat.setString(1, username);
        stat.setString(2, password);
        System.out.println(stat.toString());
        ResultSet rs = stat.executeQuery();
        while (rs.next()) {
            String id = rs.getString(1);
            String name = rs.getString(2);
            System.out.println("id:" + id + "---name:" + name);
        }

2.4 difference between # and $in mybatis

Dynamic sql is one of the main features of mybatis. After the parameters defined in mapper are transferred to xml, mybatis will dynamically parse them before querying. Mybatis provides us with two syntax to support dynamic sql: #{} and ${}.

In the following statement, if the value of username is zhangsan, there is no difference between the two methods:

select * from user where name = #{name};
select * from user where name = ${name};

The analytical results are

select * from user where name = 'zhangsan';

However, #{} and ${} are handled differently in precompiling# {} during preprocessing, the parameter part is used as a placeholder? Instead, it becomes the following sql statement:

select * from user where name = ?;

${} is just a simple string replacement. In the dynamic parsing phase, the sql statement will be parsed into

select * from user where name = 'zhangsan';

Above, #{} parameter replacement occurs in DBMS, while ${} occurs in dynamic parsing.

So, which method should we use in the process of use?

The answer is, give priority to #{}. Because ${} will cause sql injection problems. Take the following example:

　select * from ${tableName} where name = #{name}

In this example, if the table name is

 user; delete user; --

The sql after dynamic parsing is as follows:

select * from user; delete user; -- where name = ?;

--The subsequent statements are commented out, and the original statement of querying users has become the statement of querying all user information + deleting user tables, which will cause significant damage to the database and may lead to server downtime.

However, when the table name is passed in with parameters, you can only use ${}. You can guess the specific reason and verify it. This also reminds us to be careful about sql injection in this usage.

2.4.1 create UserTable

package com.itmayiedu.entity;
public class UserTable {
    private int id;
    private String userName;
    private String passWord;
}

2.4.2 create UserTable

package com.itmayiedu.mapper;
import com.itmayiedu.entity.UserTable;
public interface UserTableMapper {
    public UserTable login(UserTable userTable);
}

2.4.3userTableMapper.xml

<?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 namespace="com.itmayiedu.mapper.UserTableMapper">
    <select id="login" parameterType="com.itmayiedu.entity.UserTable"
        resultType="com.itmayiedu.entity.UserTable">
        SELECT id ,username as userName FROM user_table WHERE
        username=${userName} AND password=${passWord}
    </select>

</mapper>

2.4.4 test SQL injection

public class TestLoginMybatis3 {

    public static void main(String[] args) throws IOException, SQLException, ClassNotFoundException {

        String resource = "mybatis.xml";
        // Read configuration file
        Reader reader = Resources.getResourceAsReader(resource);
        // Get session factory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
        SqlSession openSession = sqlSessionFactory.openSession();
        // query
        String sql = "com.itmayiedu.mapper.UserTableMapper.login";
        // Call api query
        UserTable userTable = new UserTable();
        userTable.setUserName("''  OR 1=1 -- ");
        userTable.setPassWord("12345");
        List<UserTable> listUserTable = openSession.selectList(sql, userTable);
        for (UserTable ub : listUserTable) {
            System.out.println(ub.getUserName());
        }
    }
}

2.4.5 summary

Preferred use #{}. Because ${} will cause sql injection problems

3, Mybatis annotation usage

Mybatis provides comments on adding, deleting, modifying and querying, @ select @delete @update

3.1 create Mapper annotation

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import com.itmayiedu.entity.User;
public interface UserTestMapper {
    @Select("select * from users where id = ${id};")
    public User getUser(@Param("id") String id);
}

3.2 add mybatis.xml

<mapper class="com.itmayiedu.mapper.UserTestMapper" />

3.3 operation test

public class TestMybatis3 {
    public static void main(String[] args) throws IOException {
        String resource = "mybatis.xml";
        // Read configuration file
        Reader reader = Resources.getResourceAsReader(resource);
        // Get session factory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
        SqlSession openSession = sqlSessionFactory.openSession();
        // Call api query
        UserTestMapper userTestMapper=openSession.getMapper(UserTestMapper.class);
        System.out.println(userTestMapper.getUser("2"));
    }
}

4, Generator usage

Generator reverse generation usage

Posted by TheUnknown on Fri, 26 Nov 2021 14:47:38 -0800

Programmer Group

Learn these points and master Mybatis easily

1, Mybatis quick start

2, sql injection case

3, Mybatis annotation usage

4, Generator usage

Hot Keywords