MyBatis的简单查询

1 查询返回结果集, 需要在测试类中体现

<select id="selectPersonAll" resultMap="BaseResultMap">
        select * from person
    </select>

package com.rl.test;

import java.io.InputStream;
import java.util.List;

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 com.rl.model1.Person;

public class MyBatisTest2 {

    SqlSessionFactory sqlSessionFactory;
    
    @Before
    public void setUp() throws Exception {
        //加载配置文件
        InputStream in = Resources.getResourceAsStream("sqlMapConfig.xml");
        //初始化
        sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
    }

    @Test
    public void test() {
        //创建一个session
        SqlSession sqlSession = sqlSessionFactory.openSession();
        try {
            List<Person> pList = sqlSession.selectList("com.rl.mapper.PersonMapper.selectPersonAll");
            for(Person p: pList){
                System.out.println(p);
            }
        } finally{
            sqlSession.close();
        }
    }
}

结果:

Person [personId=1, name=lisi, gender=2, personAddr=天津, birthday=Sat Sep 08 00:00:00 CST 2018]
Person [personId=2, name=王五, gender=1, personAddr=北京, birthday=Sat Sep 08 00:00:00 CST 2018]

2 多参数查询:

方式一: 封装查询对象

QueryCondition:

package com.rl.model1;

import java.util.Date;

public class QueryCondition {

    private Integer gender;
    
    private Date birthday;

    public Integer getGender() {
        return gender;
    }

    public void setGender(Integer gender) {
        this.gender = gender;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }
}

配置文件:

<select id="selectPersonByParams" parameterType="com.rl.model1.QueryCondition" resultMap="BaseResultMap">
    <![CDATA[
        select * from person p where p.gender = #{gender} and p.birthday < #{birthday}
        ]]>
    </select>

测试类:

package com.rl.test;

import java.io.InputStream;
import java.util.Date;
import java.util.List;

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 com.rl.model1.Person;
import com.rl.model1.QueryCondition;

public class MyBatisTest2 {

    SqlSessionFactory sqlSessionFactory;
    
    @Before
    public void setUp() throws Exception {
        //加载配置文件
        InputStream in = Resources.getResourceAsStream("sqlMapConfig.xml");
        //初始化
        sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
    }

    
    @Test
    public void test1() {
        //创建一个session
        SqlSession sqlSession = sqlSessionFactory.openSession();
        try {
            QueryCondition qc = new QueryCondition();
            qc.setBirthday(new Date());
            qc.setGender(1);
            List<Person> pList = sqlSession.selectList("com.rl.mapper.PersonMapper.selectPersonByParams", qc);
            for(Person p: pList){
                System.out.println(p);
            }
        } finally{
            sqlSession.close();
        }
    }
}

输出结果:

Person [personId=1, name=lisi, gender=1, personAddr=天津, birthday=Fri Sep 07 00:00:00 CST 2018]
Person [personId=2, name=王五, gender=1, personAddr=北京, birthday=Thu Sep 06 00:00:00 CST 2018]

3 利用Map集合

<select id="selectPersonByParams1" parameterType="java.util.Map" resultMap="BaseResultMap">
    <![CDATA[
        select * from person p where p.gender = #{gender} and p.birthday < #{birthday}
        ]]>
    </select>

测试类:

package com.rl.test;

import java.io.InputStream;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

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 com.rl.model1.Person;
import com.rl.model1.QueryCondition;

public class MyBatisTest2 {

    SqlSessionFactory sqlSessionFactory;
    
    @Before
    public void setUp() throws Exception {
        //加载配置文件
        InputStream in = Resources.getResourceAsStream("sqlMapConfig.xml");
        //初始化
        sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
    }

    
    @Test
    public void test2() {
        //创建一个session
        SqlSession sqlSession = sqlSessionFactory.openSession();
        try {
            Map<String, Object> map = new HashMap<String, Object>();
            map.put("gender", 1);
            map.put("birthday", new Date());
            List<Person> pList = sqlSession.selectList("com.rl.mapper.PersonMapper.selectPersonByParams1", map);
            for(Person p: pList){
                System.out.println(p);
            }
        } finally{
            sqlSession.close();
        }
    }
}

测试结果同上

4 模糊查询:

配置文件:

<select id="selectPersonByName" parameterType="java.util.Map" resultMap="BaseResultMap">
    <![CDATA[
        select * from person p where p.name like '%${name}%'
        ]]>
    </select>

此时只能用'%${name}%', 由于"${}"并不是预编译, 所以并不支持parameterType="java.lang.String"的格式, 只能使用"java.util.Map"或者封装查询对象的格式

测试类:

@Test
    public void test3() {
        //创建一个session
        SqlSession sqlSession = sqlSessionFactory.openSession();
        try {
            Map<String, Object> map = new HashMap<String, Object>();
            map.put("name", "五");
            List<Person> pList = sqlSession.selectList("com.rl.mapper.PersonMapper.selectPersonByName", map);
            for(Person p: pList){
                System.out.println(p);
            }
        } finally{
            sqlSession.close();
        }
    }

结果如下:

Person [personId=2, name=王五, gender=1, personAddr=北京, birthday=Thu Sep 06 00:00:00 CST 2018]