Mybatis解决sql中like通配符模糊匹配 构造方法覆盖 mybits 增删改

<select id="getRecByNameWildcard" parameterType="Student" resultMap="result">

    SELECT * FROM STUDENT
    <if test="name != null">
        WHERE name LIKE  "%"#{name}"%"
    </if>

</select>

src\main\java\com\test\mybatisSelect.java

package com.test;

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.Reader;
import java.util.List;
import java.util.concurrent.ExecutionException;

public class mybatisSelect {

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

        Reader reader = Resources.getResourceAsReader("SqlMapConfig.xml");
        try {
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
            SqlSession session = sqlSessionFactory.openSession();
            try {
                Student stud00 = new Student("Jim");
                Student student00 = (Student) session.selectOne("Student.getRecByNameLimit1", stud00);

                System.out.println(student00.getId() + student00.getName());

                Student stud01 = new Student();
                Student student01 = (Student) session.selectOne("Student.getRecByNameLimit1", stud01);

                System.out.println(student01.getId() + student01.getName());

                System.out.println("session.selectList----------------------");


                Student stud02 = new Student("Jim");
                List<Student> students02 = session.selectList("Student.getRecByNameWildcard", stud02);

                for (Student student : students02) {
                    System.out.println(student.getId() + student.getName());
                }
                System.out.println("<---测试通配符---");


                Student stud1 = new Student();
                List<Student> students1 = session.selectList("Student.getRecByName", stud1);
                for (Student student : students1) {
                    System.out.println(student.getId() + student.getName());
                }

                // OK
                List<Student> students = session.selectList("Student.getAll");
                for (Student student : students) {
                    System.out.println(student.getId() + student.getName());
                }

            } finally {
                session.close();
            }
        } catch (Exception e) {
        }
    }
}

.
├── pom.xml
└── src
├── main
│   ├── java
│   │   └── com
│   │   └── test
│   │   ├── mybatisInsert.java
│   │   ├── mybatisSelect.java
│   │   └── Student.java
│   └── resources
│   ├── SqlMapConfig.xml
│   └── StudentMapper.xml
└── test
└── java

8 directories, 6 files

 

 

src\main\java\com\test\Student.java

package com.test;

public class Student {
    private int id;
    private String name;
    private String branch;
    private int percentage;
    private int phone;
    private String email;

    public Student() {
    }

    public Student(int id, String name, String branch, int percentage, int phone, String email) {
        this.id = id;
        this.name = name;
        this.branch = branch;
        this.percentage = percentage;
        this.phone = phone;
        this.email = email;
    }

    public Student(String name, String branch, int percentage, int phone, String email) {
        this.name = name;
        this.branch = branch;
        this.percentage = percentage;
        this.phone = phone;
        this.email = email;
    }

    public Student(String name) {
        this.name = name;
    }


    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 int getPhone() {
        return phone;
    }

    public void setPhone(int phone) {
        this.phone = phone;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getBranch() {
        return branch;
    }

    public void setBranch(String branch) {
        this.branch = branch;
    }

    public int getPercentage() {
        return percentage;
    }

    public void setPercentage(int percentage) {
        this.percentage = percentage;
    }

}

src\main\resources\SqlMapConfig.xml

<?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>
    <typeAliases>
        <typeAlias alias="Student" type="com.test.Student"/>
    </typeAliases>

    <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://rm-o.mysql.rds.aliyuncs.com:3306/video_test"/>
                <property name="username" value="t"/>
                <property name="password" value="tI"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="StudentMapper.xml"/>
    </mappers>
</configuration>

src\main\resources\StudentMapper.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="Student">

    <insert id="insert" parameterType="Student">
        INSERT INTO STUDENT (NAME, BRANCH, PERCENTAGE, PHONE, EMAIL ) VALUES (#{name}, #{branch}, #{percentage},
        #{phone}, #{email});

        <selectKey keyProperty="id" resultType="int" order="AFTER">
            select last_insert_id() as id
        </selectKey>

    </insert>

    <select id="getRecByName" parameterType="Student" resultMap="result">

        SELECT * FROM STUDENT
        <if test="name != null">
            WHERE name LIKE %#{name}%
        </if>

    </select>

    <select id="getRecByNameWildcard" parameterType="Student" resultMap="result">

        SELECT * FROM STUDENT
        <if test="name != null">
            WHERE name LIKE  "%"#{name}"%"
        </if>

    </select>


    <!--TODO 优化：limit传参-->
    <select id="getRecByNameLimit1" parameterType="Student" resultMap="result">

        SELECT * FROM STUDENT
        <if test="name != null">
            WHERE name LIKE #{name}
        </if>
         LIMIT 1
    </select>
    <select id="getAll" resultMap="result">
        SELECT * FROM STUDENT;
    </select>

    <resultMap id="result" type="Student">
        <result property="id" column="ID"/>
        <result property="name" column="NAME"/>
        <result property="branch" column="BRANCH"/>
        <result property="percentage" column="PERCENTAGE"/>
        <result property="phone" column="PHONE"/>
        <result property="email" column="EMAIL"/>
    </resultMap>

</mapper>

2Jim
1Jim232
session.selectList----------------------
1Jim232
2Jim
<---测试通配符---
1Jim232
2Jim
3Mohammad
4Mohammad
1Jim232
2Jim
3Mohammad
4Mohammad

 

ID NAME
1 Jim232
2 Jim
3 Mohammad
4 Mohammad

 

 构造方法 override 重写

mybits 增删改