黑马学习MyBatis 用MyBatis对表进行条件查询 模糊查询 动态sql

package cn.itcast.domain;

/*
CREATE TABLE `message` (
  `id` int(11) NOT NULL,
  `command` varchar(16) DEFAULT NULL,
  `description` varchar(30) DEFAULT NULL,
  `content` varchar(2048) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 */
public class Message {
    private int id;
    private String command;
    private String description;
    private String content;

    public Message(){}

    public int getId() {
        return id;
    }

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

    public String getCommand() {
        return command;
    }

    public void setCommand(String command) {
        this.command = command;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    public String getContent() {
        return content;
    }

    public void setContent(String content) {
        this.content = content;
    }

    @Override
    public String toString() {
        return "Message{" +
                "id=" + id +
                ", command='" + command + '\'' +
                ", description='" + description + '\'' +
                ", content='" + content + '\'' +
                '}';
    }
}

package cn.itcast.dao;

import cn.itcast.domain.Message;

import java.util.List;

public interface MessageDao {

    Message findById(int id);

    //mybatis只支持单个参数，如果有多个参数将它们封装为对象
//    List<Message> queryMessageList(String command, String description);

    List<Message> queryMessageList(Message condition);
}

package cn.itcast.mapper;

import cn.itcast.domain.Message;
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 org.junit.Before;
import org.junit.Test;

import java.io.IOException;
import java.io.Reader;
import java.util.List;

public class MessageMapperTest {
    SqlSessionFactory sqlSessionFactory;

    @Before
    public void init() throws IOException {
        //加载配置
        Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
        //构建工厂
        sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
    }

    @Test
    public void testFindById() throws IOException {
        SqlSession sqlSession = sqlSessionFactory.openSession(true);
        Message message = sqlSession.selectOne("cn.itcast.mapper.MessageMapper.findById", 2);
        System.out.println(message);
    }


    @Test
    public void testQueryMessageList(){
        //准备条件
        /*Message message = new Message();
        message.setCommand("百");
        message.setDescription("百");*/

        String command = "百";
        String description = "百";
        Message message = new Message();
        message.setCommand("%" + command + "%");
        message.setDescription("%" + description + "%");

        SqlSession sqlSession = sqlSessionFactory.openSession(true);
        List<Message> messageList = sqlSession.selectList("cn.itcast.mapper.MessageMapper.queryMessageList", message);
        System.out.println(messageList);
    }

}

<?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 type="cn.itcast.domain.Message" alias="Message"></typeAlias>
    </typeAliases>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"></property>
                <property name="url" value="jdbc:mysql://localhost:3307/micro_message"></property>
                <property name="username" value="root"></property>
                <property name="password" value="root"></property>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper resource="cn/itcast/mapper/MessageMapper.xml"></mapper>
    </mappers>

</configuration>

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3307/micro_message
jdbc.username=root
jdbc.password=root

# priority  :debug<info<warn<error
#you cannot specify every priority with different file for log4j
log4j.rootLogger=debug,xx

#console
log4j.appender.xx=org.apache.log4j.ConsoleAppender 
log4j.appender.xx.layout=org.apache.log4j.PatternLayout 
log4j.appender.xx.layout.ConversionPattern= [%d{yyyy-MM-dd HH:mm:ss a}]:%p %l%m%n

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.itcast.mapper.MessageMapper">

    <select id="findById" resultType="Message">
        select * from message where id=#{id}
    </select>

    <!--<select id="queryMessageList" parameterType="Message" resultType="Message">
        select * from message
        <where>
            <if test="command != null">
                and command like "%"#{command}"%"
            </if>
            <if test="description != null">
                and description like "%"#{description}"%"
            </if>
        </where>
    </select>-->

    <!--<select id="queryMessageList" parameterType="Message" resultType="Message">
        select * from message
        <where>
            <if test="command != null">
                and command like #{command}
            </if>
            <if test="description != null">
                and description like #{description}
            </if>
        </where>
    </select>-->

    <!--<select id="queryMessageList" parameterType="Message" resultType="Message">
        select * from message
        <where>
            <if test="command != null">
                and command like '%' #{command} '%'
            </if>
            <if test="description != null">
                and description like '%' #{description} '%'
            </if>
        </where>
    </select>-->

    <select id="queryMessageList" parameterType="Message" resultType="Message">
        select * from message
        <where>
            <if test="command != null &amp;&amp; !&quot;&quot;.equals(command.trim())">
                and command like '%' #{command} '%'
            </if>
            <!--<if test="command != null and !&quot;&quot;.equals(command.trim())">
                and command like '%' #{command} '%'
            </if>-->
            <if test="description != null">
                and description like '%' #{description} '%'
            </if>
        </where>
    </select>

</mapper>