Mybatis入门级教程（一）

最新推荐文章于 2023-06-09 19:11:37 发布

夏虫语冰彡

最新推荐文章于 2023-06-09 19:11:37 发布

阅读量357

点赞数

文章标签：持久层框架 mybatis java xml 数据库

本文链接：https://blog.csdn.net/qq_34283987/article/details/78442533

版权

一、Mybatis介绍

MyBatis是支持普通SQL查询，存储过程和高级映射的优秀持久层框架。MyBatis消除了几乎所有的JDBC代码和参数的手工设置以及对结果集的检索封装。MyBatis可以使用简单的XML或注解用于配置和原始映射，将接口和Java的POJO（Plain Old Java Objects，普通的Java对象）映射成数据库中的记录.
JDBC-dbutils(自动封装)-MyBatis-Hibernate
http://blog.csdn.net/softwave/article/details/18604565

为什么要学Mybatis?而不使用传统的jdbc?
Mybatis是一个持久层框架，使用Mybatis代替传统的jdbc，代替vo+dao持久层。持久化：把内存中的数据保存到可掉电的存储设备中（硬盘、u盘）。
传统的jdbc存在缺点，操作起来相当繁琐，过多的set和get也相当繁琐。因此使用Mybatis简化操作。

二、如何使用Mybatis?

从一个简单的用户管理例子学起，使用步骤：

建库+表（使用mysql需要建库，oracle不用）
定义表所对应的实体类PO
添加jar包
添加Mybatis的配置文件conf.xml
定义操作user表的sql映射文件userMapper.xml
在conf.xml文件中注册userMapper.xml文件
编写测试代码，执行定义的select语句

1.建库和表
create database mybatis;
use mybatis;
CREATE TABLE user(id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20), age INT);
INSERT INTO user(NAME, age) VALUES('小明', 22);
INSERT INTO user(NAME, age) VALUES('小红', 23);

2.定义表所对应的实体类 PO持久化对象
public class User {
    private int id;
    private String name;
    private int age;
    //get,set方法 和toString()
}

3.添加jar包
【mybatis】
    mybatis-3.1.1.jar
【MYSQL驱动包】
mysql-connector-java-5.1.7-bin.jar

4.添加Mybatis的配置文件conf.xml
放到src文件夹下
<?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/mybatis" />
                <property name="username" value="root" />
                <property name="password" value="root" />
            </dataSource>
        </environment>
    </environments>
</configuration>
environments  表示程序的运行模式 有两个选项
development:开发模式
work:工作模式 发布以后写成它 效率高

environments下environment可以写多个,但一般写一个就可以
id中"development" 要和 default的development保持一致

<dataSource type="POOLED">
数据源类型:
POOLED:使用连接池的数据源
UNPOOLED:不使用连接池的数据源
JDNI：使用JNDI实现的数据源

5.定义操作user表的sql映射文件userMapper.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">
<!-- 定义sql文件的唯一标示  通过namespace这个标示 找到这个xml文件 -->
<mapper namespace="com.vo.userMapper">
    <select id="getUser" parameterType="int" 
        resultType="com.vo.User">
        select * from users where id=#{id}
    </select>
</mapper>

6.在conf.xml中注册userMapper.xml文件
在environments 下面加入以下代码
<mappers>
    <mapper resource="com/vo/userMapper.xml"/>
</mappers>

7.编写测试代码：执行定义的select语句
public class Test {
    public static void main(String[] args) throws IOException {
        String resource = "conf.xml"; 
        //加载mybatis的配置文件（它也加载关联的映射文件）
        Reader reader = Resources.getResourceAsReader(resource); 
        //构建sqlSession的工厂
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
        //创建能执行映射文件中sql的sqlSession
        SqlSession session = sessionFactory.openSession();
        //映射sql的标识字符串
        String statement = "com.vo.userMapper"+".getUser";
        //执行查询返回一个唯一user对象的sql
        User user = session.selectOne(statement, 1);
        System.out.println(user);
    }
}

或者用类加载器也可以

演示代码如下:
package com.vo;
import java.io.InputStream;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

public class Test {
    public static void main(String[] args) {
        String resource = "conf.xml";
        InputStream is = Test.class.getClassLoader().getResourceAsStream(resource);
        SqlSessionFactory sf = new SqlSessionFactoryBuilder().build(is);
        SqlSession session = sf.openSession();

        User user =  session.selectOne("com.vo.userMapper.getUser",1);
        System.out.println(user);
if(session!=null){
            session.close();
        }
    }
}

三、操作user表的CRUD

一）

XML的实现
1).创建SqlSessionFactory的工具类

package com.util;
import java.io.InputStream;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
public class SessionFactoryUtil {
private static SqlSessionFactory factory;
    static {
        String resource = "conf.xml";
        InputStream is = SessionFactoryUtil.class.getClassLoader().getResourceAsStream(resource);
        factory = new SqlSessionFactoryBuilder().build(is);
    }
public static SqlSessionFactory getFactory(){
        return factory;
    }
    public static SqlSession getSqlSession(){
        SqlSession session = null;
        //默认是false 事务手工提交 即开启事务,true 事务是自动提交,即关闭事务
        session = factory.openSession(true);
        return session;
    }
public static void closeSqlSession(SqlSession session){
        if(session!=null){
            session.close();
    }
}
    public static void main(String[] args) {
        System.out.println(SessionFactoryUtil.getSqlSession());
    }
}

2). 定义sql映射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">
<!-- 定义sql文件的唯一标示  通过namespace这个标示 找到这个xml文件 -->
<mapper namespace="com.vo.userMapper">

    <!-- 演示CRUD -->
    <!-- 1.增加 -->
    <insert id="addUser" parameterType="com.vo.User">
        insert into user values(null,#{name},#{age})
    </insert>

    <!-- 2.删除 -->
    <delete id="deleteUser" parameterType="com.vo.User">
        delete from user where id=#{id}
    </delete>

    <!-- 3.修改 -->
    <update id="updateUser" parameterType="com.vo.User">
        update user set name=#{name},age=#{age} where id=#{id}
    </update>

    <!-- 4.查1 -->
    <select id="getUserById" parameterType="java.lang.Integer" resultType="com.vo.User">
        select * from user where id=#{id}
    </select>

    <!-- 5.按sql查询 -->
    <select id="getPageByQuery" parameterType="String" resultType="com.vo.User">
        ${value}
    </select>

    <!-- 6.按总共有多少条记录 -->
    <select id="getTotalRecordSum" parameterType="String" resultType="int">
        ${value}
    </select>
</mapper>

2). 在conf.xml中注册这个映射文件
<mappers>
    <mapper resource="com/vo/userMapper.xml" />
</mappers>
3). 在dao中调用：
接口
package com.dao.inter;

import java.util.List;
import com.vo.User;

public interface UserDao {
    //一.增加
    public void addUser(User user);
    //二.删除
    public void deleteUser(User user);
    //三.修改
    public void updateUser(User user);

    //四.查1
    public User getUserById(Integer id);

    //五.按SQL语句查
    public List<User> getPageByQuery(String sql);

    //六.查询总共有多少条记录
    public int getTotalRecordSum(String sql);

}

实现类
package com.dao.impl;

import java.util.List;

import org.apache.ibatis.session.SqlSession;

import com.dao.inter.UserDao;
import com.po.User;
import com.util.SessionFactoryUtil;

public class UserDaoImpl implements UserDao{

    private SqlSession session;

    public UserDaoImpl(){
        session = SessionFactoryUtil.getSqlSession();
    }

    @Override
    public int addUser(User user){
        int count;
        String statement = "com.po.userMapper.addUser";
        try{
            count = session.insert(statement, user);
        }finally{
            SessionFactoryUtil.closeSqlSession(session);
        }

        return count;
    }

    @Override
    public int deleteUser(User user) {
        int count;
        String statement = "com.po.userMapper.deleteUser";
        try{
            count = session.delete(statement, user);
        }finally{
            SessionFactoryUtil.closeSqlSession(session);
        }

        return count;
    }

    @Override
    public int updateUser(User user){
        int count;
        String statement = "com.po.userMapper.updateUser";
        try{
            count = session.update(statement, user);
        }finally{
            SessionFactoryUtil.closeSqlSession(session);
        }

        return count;
    }

    @Override
    public User getUserById(Integer userid){
        User user = null;

        String statement = "com.po.userMapper.getUser";
        try {
            user = session.selectOne(statement, userid);
        } finally {
            SessionFactoryUtil.closeSqlSession(session);
        }
        return user;
    }

    @Override
    public List<User> getPageByQuery(String sql){
        List<User> list = null;

        String statement = "com.po.userMapper.getPageByQuery";
        try{
            list = session.selectList(statement, sql);
        }finally {
            SessionFactoryUtil.closeSqlSession(session);
        }

        return list;
    }

    @Override
    public int getTotalRecordSum(String sql){
        int count = 0;
        String statement = "com.po.userMapper.getTotalRecordSum";
        try{
            count = session.selectOne(statement, sql);
        }finally{
            SessionFactoryUtil.closeSqlSession(session);
        }

        return count;
    }

}


二）. 注解的实现
1). 定义sql映射的接口
package com.dao.inter;

import java.util.List;
import org.apache.ibatis.annotations.*;
import com.po.User;

public interface UserMapper {
    //一.增加
    @Insert("insert into user values(null,#{name},#{age})")
    public int addUser(User user);
    //二.删除
    @Delete("delete from user where id=#{id}")
    public int deleteUser(User user); 
    //三.修改
    @Update("update user set name=#{name},age=#{age} where id=#{id}")
    public int updateUser(User user);
    //四.查1
    @Select("select * from user where id=#{id}")
    public User getUserById(Integer userid);
    //五.按sql查询
    @Select("${value}")
    public List<User> getPageByQuery(String sql);
    //六.查询总共有多少条记录
    @Select("${value}")
    public int getTotalRecordSum(String sql);

}


2). 在conf.xml中注册这个映射接口
<mappers>
    <mapper class="com.dao.inter.UserMapper"/>
</mappers>

3). 在dao类中调用
package com.dao.impl;

import java.util.List;
import org.apache.ibatis.session.SqlSession;
import com.dao.inter.UserMapper;
import com.po.User;
import com.util.SessionFactoryUtil;

public class UserMapperImpl implements UserMapper {

    private SqlSession session;

    public UserMapperImpl() {
        session = SessionFactoryUtil.getSqlSession();
    }

    @Override
    public int addUser(User user) {
        int count;

        try {
            UserMapper userMapper = session.getMapper(UserMapper.class);

            count = userMapper.addUser(user);
        } finally {
            SessionFactoryUtil.closeSqlSession(session);
        }

        return count;
    }

    @Override
    public int deleteUser(User user) {
        int count;
        try {
            UserMapper userMapper = session.getMapper(UserMapper.class);

            count = userMapper.deleteUser(user);
        } finally {
            SessionFactoryUtil.closeSqlSession(session);
        }

        return count;
    }

    @Override
    public int updateUser(User user) {
        int count;
        try {
            UserMapper userMapper = session.getMapper(UserMapper.class);

            count = userMapper.updateUser(user);
        } finally {
            SessionFactoryUtil.closeSqlSession(session);
        }

        return count;
    }

    @Override
    public User getUserById(Integer userid) {
        User user = null;

        try {
            UserMapper userMapper = session.getMapper(UserMapper.class);

            user = userMapper.getUserById(userid);
        } finally {
            SessionFactoryUtil.closeSqlSession(session);
        }
        return user;
    }

    @Override
    public List<User> getPageByQuery(String sql) {
        List<User> list = null;

        try {
            UserMapper userMapper = session.getMapper(UserMapper.class);

            list = userMapper.getPageByQuery(sql);
        } finally {
            SessionFactoryUtil.closeSqlSession(session);
        }
        return list;
    }

    @Override
    public int getTotalRecordSum(String sql) {
        int count = 0;


        try {
            UserMapper userMapper = session.getMapper(UserMapper.class);

            count = userMapper.getTotalRecordSum(sql);
        } finally {
            SessionFactoryUtil.closeSqlSession(session);
        }


        return count;
    }

    public static void main(String[] args) {
        UserMapperImpl dao = new UserMapperImpl();

        User user = new User();
        user.setName("小刚");
        user.setAge(21);
        int count = dao.addUser(user);
        System.out.println(count);
    }

}



强烈推荐使用XML配置方式，原因如下:

1.Java注解是受限的,功能较少，而MyBatis的Mapper内容相当多,相当复杂,功能强大, 使用XML文件方式可以带来更为灵活的空间，显示出MyBatis功能的强大和灵活

2.如果你的SQL很复杂,条件很多,尤其是存在动态SQL的时候,写在Java文件里面可读性较差,增加了维护成本.
总结:
SqlSessionFactoryBuilder
SqlSessionFactory
SqlSession
Mapper 实例

四、几个可以优化的地方

4.1. 连接数据库的配置单独放在一个properties文件中db.properties
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mybatis
username=root
password=root
修改conf.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="db.properties"/>

    <environments default="development">
        <environment id="development">
            <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>
        <mapper resource="com/vo/userMapper.xml" />

    </mappers>


</configuration>

4.2. 为实体类定义别名,简化sql映射xml文件中的引用
在conf.xml中,environments节点前,加入如下代码即可
<typeAliases>
    <typeAlias type="com.vo.User" alias="_User"/>
</typeAliases>

注意:conf.xml配置文件中的节点位置有一定的顺序,如果位置颠倒,会报错

Caused by: org.xml.sax.SAXParseException; lineNumber: 30; columnNumber: 17; 元素类型为 "configuration" 的内容必须匹配 "(properties?,settings?,typeAliases?,typeHandlers?,objectFactory?,objectWrapperFactory?,plugins?,environments?,databaseIdProvider?,mappers?)"。

或者直接写上
<typeAliases>
        <package name="com.vo"/>
</typeAliases>
在userMapper.xml中就可以不需要写类全名com.vo.User了,只需要写简单类名User即可

4.3. 可以在src下加入log4j的配置文件,打印日志信息
1. 添加jar: 
    log4j-1.2.16.jar

2.1. log4j.properties(方式一)

    log4j.properties，
    log4j.rootLogger=DEBUG, Console
    #Console
    log4j.appender.Console=org.apache.log4j.ConsoleAppender
    log4j.appender.Console.layout=org.apache.log4j.PatternLayout
    log4j.appender.Console.layout.ConversionPattern=%d [%t] %-5p [%c] - %m%n
    log4j.logger.java.sql.ResultSet=INFO
    log4j.logger.org.apache=INFO
    log4j.logger.java.sql.Connection=DEBUG
    log4j.logger.java.sql.Statement=DEBUG
    log4j.logger.java.sql.PreparedStatement=DEBUG

2.2. log4j.xml(方式二)

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">
<log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/">
    <appender name="STDOUT" class="org.apache.log4j.ConsoleAppender">
        <layout class="org.apache.log4j.PatternLayout">
            <param name="ConversionPattern" 
                value="%-5p %d{MM-dd HH:mm:ss,SSS} %m  (%F:%L) \n" />
        </layout>
    </appender>
    <logger name="java.sql">
        <level value="debug" />
    </logger>
    <logger name="org.apache.ibatis">
        <level value="debug" />
    </logger>
    <root>
        <level value="debug" />
        <appender-ref ref="STDOUT" />
    </root>
</log4j:configuration>

五、解决字段名和实体类属性名不同的冲突

5. 解决字段名与实体类属性名不相同的冲突
5.1. 准备表和数据：
CREATE TABLE orders(
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    order_no VARCHAR(20), 
    order_price FLOAT
);
INSERT INTO orders(order_no, order_price) VALUES('aaaa', 23);
INSERT INTO orders(order_no, order_price) VALUES('bbbb', 33);
INSERT INTO orders(order_no, order_price) VALUES('cccc', 22);
5.2. 定义实体类：
public class Orders {
    private int orderId;
    private String orderNo;
    private float orderPrice;
}
5.3. 实现getOrderById(id)的查询：
方式一: 通过在sql语句中定义别名
<select id="selectOrder" parameterType="int" resultType="_Order">
    select order_id orderId, order_no orderNo,order_price orderPrice from orders where order_id=#{id}
</select>

方式二: 通过<resultMap>
<select id="selectOrderResultMap" parameterType="int" resultMap="orderResultMap">
    select * from orders where order_id=#{id}
</select>

<resultMap type="Order"  id="orderResultMap">
    <id property="orderId" column="order_id"/>
    <result property="orderNo" column="order_no"/>
    <result property="orderPrice" column="order_price"/>
</resultMap>

方式三:  mybatis开启数据库字段自动映射驼峰命名规则java属性  
在conf.xml中 <typeAliases> 节点前加入setting节点
<settings>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>

六、实现关联表查询和懒加载

6.1. 一对一关联
1). 提出需求
根据班级id查询班级信息(带老师的信息)
2). 创建表和数据
CREATE TABLE teacher(
    t_id INT PRIMARY KEY AUTO_INCREMENT, 
    t_name VARCHAR(20)
);
CREATE TABLE class(
    c_id INT PRIMARY KEY AUTO_INCREMENT, 
    c_name VARCHAR(20), 
    teacher_id INT UNIQUE
);
ALTER TABLE class ADD CONSTRAINT fk_teacher_id FOREIGN KEY (teacher_id) REFERENCES teacher(t_id);   

INSERT INTO teacher(t_name) VALUES('zhangzy');
INSERT INTO teacher(t_name) VALUES('liusp');

INSERT INTO class(c_name, teacher_id) VALUES('基地0620班', 1);
INSERT INTO class(c_name, teacher_id) VALUES('基地16班', 2);
3). 定义实体类：
public class Teacher {
    private int id;
    private String name;
}
public class Classes {
    private int id;
    private String name;
    private Teacher teacher;
}

4). 定义sql映射文件ClassMapper.xml
<!-- 
方式一：嵌套结果：使用嵌套结果映射来处理重复的联合结果的子集
封装联表查询的数据(去除重复的数据)
-->
<select id="getClass" parameterType="int" resultMap="ClassResultMap">
    select * from teacher t inner join class c on(t.t_id=c.teacher_id) 
    where c_id=#{c_id}
</select>
<resultMap type="Classes" id="ClassResultMap">
    <id property="id" column="c_id"/>
    <result property="name" column="c_name"/>
    <association property="teacher" javaType="Teacher">
        <id property="id" column="t_id"/>
        <result property="name" column="t_name"/>
    </association>
</resultMap>

<!-- 
方式二：嵌套查询：通过执行另外一个SQL映射语句来返回预期的复杂类型
    SELECT * FROM class WHERE c_id=1;
    SELECT * FROM teacher WHERE t_id=1   //1 是上一个查询得到的teacher_id的值
-->

<select id="getClass2" parameterType="int" resultMap="ClassResultMap2">
    select * from class where c_id=#{id}
</select>
<resultMap type="Classes" id="ClassResultMap2">
    <id property="id" column="c_id"/>
    <result property="name" column="c_name"/>
    <association property="teacher" column="teacher_id" select="getTeacher">
</association>
</resultMap>

<select id="getTeacher" parameterType="int" resultType="Teacher">
    SELECT t_id id, t_name name FROM teacher WHERE t_id=#{id}
</select>

5). 测试
public Classes getClassesById(Integer id) {
        Classes classes = null;
        String statement = "com.vo.classMapper.getClass";
            classes = session.selectOne(statement, id); 
        }finally{
            session.close();
        }
        return classes;
    }

public Classes getClassesById2(Integer id) {
        Classes classes = null;
        String statement = "com.vo.classMapper.getClass2";

        try{
            classes = session.selectOne(statement, id);

        }finally{
            session.close();
        }
        return classes;
    }


6.2.延迟加载(懒加载)
依赖两个jar包  cglib.jar、asm.jar,Cglib依赖于asm.jar
<settings>
        <!--打开延迟加载的开关  -->
        <setting name="lazyLoadingEnabled" value="true"/>
            <!--将积极加载改为消极加载及按需加载  -->
        <setting name="aggressiveLazyLoading" value="false"/>
</settings>


6.3. 一对多关联
1). 提出需求
根据班级id查询对应的班级信息,包括学生,老师
2). 创建表和数据：
CREATE TABLE student(
    s_id INT PRIMARY KEY AUTO_INCREMENT, 
    s_name VARCHAR(20), 
    class_id INT
);
ALTER TABLE student ADD CONSTRAINT fk_class_id FOREIGN KEY (class_id) REFERENCES class(c_id);   

INSERT INTO student(s_name, class_id) VALUES('赵英龙', 1);
INSERT INTO student(s_name, class_id) VALUES('杨帆', 1);
INSERT INTO student(s_name, class_id) VALUES('王爽', 1);
INSERT INTO student(s_name, class_id) VALUES(‘吴亚明’', 2);
INSERT INTO student(s_name, class_id) VALUES('赵文宇', 2);


3). 定义实体类：
public class Student {
    private int id;
    private String name;
}


public class Classes {
    private int id;
    private String name;
    private Teacher teacher;
    private List<Student> students;
}
4). 定义sql映射文件ClassMapper.xml
<!-- 
方式一: 嵌套结果: 使用嵌套结果映射来处理重复的联合结果的子集
SELECT * FROM class c, teacher t,student s WHERE c.teacher_id=t.t_id AND c.C_id=s.class_id AND  c.c_id=1
 -->
<select id="getClass3" parameterType="int" resultMap="ClassResultMap3">
    select * from teacher t inner join class c  on(t.t_id=c.teacher_id) 
    inner join student s on(c.c_id=s.class_id) where c.c_id=1
</select>
<resultMap type="Classes" id="ClassResultMap3">
    <id property="id" column="c_id"/>
    <result property="name" column="c_name"/>
    <association property="teacher"  javaType="Teacher">
        <id property="id" column="t_id"/>
        <result property="name" column="t_name"/>
    </association>
    <!-- ofType指定students集合中的对象类型 -->
    <collection property="students" ofType="Student">
        <id property="id" column="s_id"/>
        <result property="name" column="s_name"/>
    </collection>
</resultMap>

<!-- 
    方式二：嵌套查询：通过执行另外一个SQL映射语句来返回预期的复杂类型
        SELECT * FROM class WHERE c_id=1;
        SELECT * FROM teacher WHERE t_id=1   //1 是上一个查询得到的teacher_id的值
        SELECT * FROM student WHERE class_id=1  //1是第一个查询得到的c_id字段的值
 -->
<select id="getClass4" parameterType="int" resultMap="ClassResultMap4">
    select * from class where c_id=#{id}
</select>
<resultMap type="Classes" id="ClassResultMap4">
    <id property="id" column="c_id"/>
    <result property="name" column="c_name"/>
    <association property="teacher" column="teacher_id" javaType="Teacher" select="getTeacher2"></association>
    <collection property="students" ofType="Student" column="c_id" select="getStudents"></collection>
</resultMap>

<select id="getTeacher2" parameterType="int" resultType="Teacher">
    SELECT t_id id, t_name name FROM teacher WHERE t_id=#{id}
</select>

<select id="getStudents" parameterType="int" resultType="Student">
    SELECT s_id id, s_name name FROM student WHERE class_id=#{id}
</select>

5). 测试：
public Classes getClassesById3(Integer id) {
        Classes classes = null;
        String statement = "com.vo.classMapper.getClass3";
        try{
            classes = session.selectOne(statement, id);

        }finally{
            session.close();
        }
        return classes;
    }

public Classes getClassesById4(Integer id) {
        Classes classes = null;
        String statement = "com.vo.classMapper.getClass4";
        try{
            classes = session.selectOne(statement, id);
        }finally{
            session.close();
        }
        return classes;
    }

七、动态SQL与模糊查询

7.1. 提出需求: 
实现多条件查询用户(姓名模糊匹配, 年龄在指定的最小值到最大值之间)
7.2. 准备数据表和数据:
create table d_user(  
    id int primary key auto_increment,  
    name varchar(10),
    age int(3)
); 

insert into d_user(name,age) values('小明1',20);
insert into d_user(name,age) values('小明2',21);  
insert into d_user(name,age) values('小明3',22);  
insert into d_user(name,age) values('小明4',28);  

insert into d_user(name,age) values('小红',23);  
insert into d_user(name,age) values('小红',18);
7.3. User(表实体类)
private int id;
private String name;
private int age;
7.4. ConditionUser(查询条件实体类)
private String name;
private int minAge;
private int maxAge;


7.5. userMapper.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.po.userMapper">
    <select id="getUsers" parameterType="com.vo.ConditionUser"
        resultType="User">
        select * from d_user where 1=1

        <if test='name!=null and name!=""'> and name like CONCAT('%','${name}','%')</if>

        <if test='minAge!=null'> and age>=#{minAge}</if>

        <if test='maxAge!=null'> and age&lt;=#{maxAge}</if>
    </select>
</mapper>

7.6. UserTest(测试)
    public List<User> getUsers(ConditionUser conditionUser) {
        List<User> list;

        try {
            String statement = "com.po.userMapper.getUsers";

            list = sqlSession.selectList(statement, conditionUser);

        } finally {
            SessionFactoryUtil.closeSqlSession(sqlSession);
        }

        return list;
    }

public static void main(String[] args) {
        UserDaoImpl dao = new UserDaoImpl();
        ConditionUser conditionUser = new ConditionUser();
        conditionUser.setName("小明");
        conditionUser.setMinAge(21);
        conditionUser.setMaxAge(28);
        List<User> users = dao.getUsers(conditionUser);
        System.out.println(users);
    }       
}

这里写图片描述

夏虫语冰彡

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
Mybatis入门级教程（一）

一、Mybatis介绍MyBatis是支持普通SQL查询，存储过程和高级映射的优秀持久层框架。MyBatis消除了几乎所有的JDBC代码和参数的手工设置以及对结果集的检索封装。MyBatis可以使用简单的XML或注解用于配置和原始映射，将接口和Java的POJO（Plain Old Java Objects，普通的Java对象）映射成数据库中的记录. JDBC-dbutils(自动封装)-MyBa
复制链接

扫一扫