Mybatis的select查询的三种方式

1、首先建立一个测试的dao

public interface IStudentDao {

    // 根据姓名查询
    List<Student> selectStudentsByName(String name);
}

2、对这个dao进行实现

public class StudentDaoImpl implements IStudentDao {

    private SqlSession sqlSession;

    // 根据姓名查询
    public List<Student> selectStudentsByName(String name) {
        List<Student> students = null;

        try {
            sqlSession = MybatisUtil.getSqlSession();
            students = sqlSession.selectList("selectStudentsByName", name);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (sqlSession != null) {
                sqlSession.close();
            }
        }
        return students;
    }
}

3、utils的书写

public class MybatisUtil {
    private static SqlSessionFactory sqlSessionFactory;

    public static SqlSession getSqlSession() {
        try {
            if (sqlSessionFactory == null) {
                // 读取配置文件
                InputStream inputStream = Resources
                        .getResourceAsStream("Mybatis.xml");
                // 创建工厂
                sqlSessionFactory = new SqlSessionFactoryBuilder()
                        .build(inputStream);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return sqlSessionFactory.openSession();
    }
}

4、配置文档

（1）Mybatis.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>

    <properties resource="jdbc.properties" />

    <!-- 别名标签 -->
    <typeAliases>
        <typeAlias type="com.liuya.demo.mybaits.crud.pojo.Student"
            alias="Student" />
    </typeAliases>

    <!-- 配置运行的数据库环境 -->
    <environments default="mysqlenvironment">
        <environment id="mysqlenvironment">
            <!-- 連接池在本地连接中使用,在SSM中不用,用C3P0和DBCP -->
            <transactionManager type="JDBC" />
            <dataSource type="POOLED">
                <property name="driver" value="${driver}" />
                <property name="url" value="${url}" />
                <property name="username" value="${username}" />
                <property name="password" value="${password}" />
            </dataSource>
        </environment>
    </environments>

    <!-- 连接映射文件 -->
    <mappers>
        <!-- 最终使用的都是package -->
        <mapper resource="com/liuya/demo/mybaits/crud/mapper/StudentMapper.xml" />
    </mappers>
</configuration>

（2）properties的书写

#
##正式服务器
driver = com.mysql.jdbc.Driver
url = jdbc:mysql://127.0.0.1:3306/crud?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true
username = root
password = 123456

（3）mapper的书写

<?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="test">

    <!-- 根据姓氏模糊查询 -->
    <select id="selectStudentsByName" resultType="Student">
        select
        NAME,AGE,SCORE from STUDENT where NAME like concat ('%',#{ooo},'%')
    </select>


</mapper>

5、测试的书写

public class MyTest {

    private IStudentDao idao;

    @Before
    public void before() {
        idao = new StudentDaoImpl();
    }
    // 根据name查询一个学生
    @Test
    public void testSelectStudentsByName() {
        System.out.println("开始查询学生");
        List<Student> students = idao.selectStudentsByName("张");
        for (Student student : students) {
            System.out.println(student);
        }
        System.out.println("查询学生成功");
    }
}

这就是一个完整的select查询的书写，重点在于mapper中的select书写

        写法一（比较复杂）：

<!-- 根据姓氏模糊查询 -->
    <select id="selectStudentsByName" resultType="Student">
        select
        NAME,AGE,SCORE from STUDENT where NAME like concat ('%',#{ooo},'%')
    </select>
  写法二（一般使用的比较多）：

<!-- 根据姓氏模糊查询 -->
    <select id="selectStudentsByName" resultType="Student">
        select
        NAME,AGE,SCORE from STUDENT where NAME like '%'#{ooo}'%'
    </select>
写法三（不建议使用，会有sql注入问题和加载速度慢的问题）：

<!-- 根据姓氏模糊查询 -->
    <select id="selectStudentsByName" resultType="Student">
        select
        NAME,AGE,SCORE from STUDENT where NAME like '%&{value}%'
    </select>
like后的括号值，只能是value。