MyBatis----多表联查&动态SQL&模糊查询&分页

目录

多表联查

        准备工作

         多对一(多个学生在一个班级)

 方法一:通过链表查询。

方法二:通过嵌套查询(两次查询)。

        一对多(一个班级有多个学生)

动态SQL(sql语句根据条件而发生改变)

动态查询

1.if和where一起用

2.[choose when otherwise] 和where

动态修改

动态(批量)删除

  模糊查询

 分页

---

多表联查

        准备工作

        pom.xml所用jar包:(具体配置可查看上篇"MyBatis的使用和优化")

<dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.27</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.7</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.22</version>
        </dependency><dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.11</version>
        <scope>test</scope>
    </dependency>
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>
    </dependencies>

      数据库信息

(student表的cla列作为外键链接cla表的classid主键列)

创建项目的如下:

 实体类:

 

         多对一(多个学生在一个班级)

 方法一:通过链表查询。

        StuDao接口:

public interface StuDao {
    public Student selectById(@Param("id") int id);
}

        StuMapper.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.kk.dao.StuDao">
    <resultMap id="map01" type="com.kk.entity.Student" autoMapping="true">
        <!--id必写    autoMapping="true" 数据库的列名和Student类名相对应才可用-->
        <id column="id" property="id"/>

        <association property="cla" javaType="com.kk.entity.Cla" autoMapping="true">
            <id property="classid" column="classid"/>
        </association>
        <!--association:表示多对一
            property:表示对象属性名
            javaType:表示该对象所属的类型
            autoMapping必须写-->
    </resultMap>

    <!--注意:使用了resultMap不能在使用resultType-->
    <!--这里的id必须和Dao中的方法名一致。-->
    <select id="selectById" resultMap="map01">
        select * from student s,cla c where s.cla=c.classid and s.id=#{id}
    </select>

</mapper>

       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="db.properties"/>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC" />
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.drivername}" />
                <property name="url" value="${jdbc.url}" />
                <property name="username" value="${jdbc.username}" />
                <property name="password" value="${jdbc.userpassword}" />
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="mapper/StuMapper.xml"/>
    </mappers>

</configuration>

测试类:

public class MyTest {
    SqlSession session;
    @Before
    public void init(){
        Reader reader= null;
        try {
            reader = Resources.getResourceAsReader("MyBatis.xml");
        } catch (IOException exception) {
            exception.printStackTrace();
        }
        SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(reader);
        session=sqlSessionFactory.openSession();
    }
    @Test
    public void method1(){
        StuDao sd=session.getMapper(StuDao.class);
        Student s=sd.selectById(3);
        System.out.println(s);
        session.close();
    }
}

结果:

2021-12-06 01:06:24,876 [main] DEBUG [com.kk.dao.StuDao.selectById] - ==>  Preparing: select * from student s,cla c where s.cla=c.classid and s.id=?
2021-12-06 01:06:24,921 [main] DEBUG [com.kk.dao.StuDao.selectById] - ==> Parameters: 3(Integer)
2021-12-06 01:06:24,953 [main] DEBUG [com.kk.dao.StuDao.selectById] - <==      Total: 1
Student(id=3, sname=阿鸭, sex=男, phone=110, cla=Cla(classid=2, classname=qy142))

Process finished with exit code 0