实验三. 动态SQL（1）查询、删除、修改【if+trim、where】

Jay_27

已于 2023-06-23 18:22:29 修改

阅读量160

点赞数

分类专栏： SSM 文章标签： mysql mybatis 数据库 java

于 2023-05-31 21:28:58 首次发布

本文链接：https://blog.csdn.net/dsbss/article/details/130973006

版权

SSM 专栏收录该内容

14 篇文章 0 订阅

订阅专栏

一、利用@Param注解参数实现对teacher表中的数据的查询、删除、修改。

1、JOPO实体类（Teacher.java）

2、实体类的映射接口类（interface TeacherMapperjava)

List<Teacher> select_zhou(@Param("Tname") String firstname,
                          @Param("Ttitles") String titles);

——TeacherMapper.xml中，对应SQL

<mapper namespace="wzu.mapper.TeacherMapper">
<!--    （1）查询周姓副教授的教师记录；-->
    <select id="select_zhou" resultType="wzu.pojo.Teacher">
        select * from teacher where TNAME like '${Tname}%' and TITLES='${Ttitles}';
    </select>

package wzu.mapper;

import org.apache.ibatis.annotations.Param;
import wzu.pojo.Teacher;
import java.util.List;

public interface TeacherMapper {
    //（1）查询[周姓][副教授]的教师记录；
    List<Teacher> select_zhou(@Param("Tname") String firstname,
                              @Param("Ttitles") String titles);
    //（2）删除[手机号为135开头]的[讲师]的教师记录；
    int delete(@Param("Tphone") String phone_start,
                @Param("Ttitles") String titles);
    //（3）修改[TNO：121004]教师的[手机号为18811597853]
    int update(@Param("Tno") int tno,
                @Param("Tphone") String phone);
}

3、映射的配置文件（TeacherMapper.xml）

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="wzu.mapper.TeacherMapper">
<!--    （1）查询周姓副教授的教师记录；-->
    <select id="select_zhou" resultType="wzu.pojo.Teacher">
        select * from teacher where TNAME like '${Tname}%' and TITLES='${Ttitles}';
    </select>
<!--    （2）删除手机号为135开头的讲师的教师记录；-->
    <delete id="delete" parameterType="wzu.pojo.Teacher">
        DELETE FROM `mybatis`.`teacher` WHERE PHONE like '${Tphone}%' and TITLES='${Ttitles}';
    </delete>
<!--    （3）修改TNO：121004教师的手机号为18811597853-->
    <update id="update" parameterType="wzu.pojo.Teacher">
        UPDATE `mybatis`.`teacher` SET `PHONE` = '${Tphone}' WHERE (`TNO` = '${Tno}');
    </update>
</mapper>

4、有关SqlSession。。。（Util.java）

package wzu;

import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.IOException;
import java.io.InputStream;

public class Util {
    private static SqlSessionFactory factory;
//  静态 - 只创建一次factory
    static {
        String resource= "mybatis-config.xml";
        try {
            InputStream is = Resources.getResourceAsStream(resource);
            factory=new SqlSessionFactoryBuilder().build(is);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
//    * 创建SqlSession对象
    public static SqlSession createSqlSession(){
        return factory.openSession(false); // true - 自动提交事务
    }
//    * 关闭SqlSession
    public static void closeSqlSession(SqlSession sqlSession){
        if(null!=sqlSession){
            sqlSession.close();
        }
    }
}

5、配置文件mybatis（mybatis-config.xml）

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!--    引入database.properties文件-->
<!--    <properties resource="database.properties"/>-->
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis?serverTimezone=UTC"/>
<!--                useUnicode=true&characterEncoding=utf8&useSSL=false&-->
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
    </environments>
<!--    将mapper文件加入配置文件中-->
    <mappers>
        <mapper resource="wzu/mapper/TeacherMapper.xml"/>
    </mappers>
</configuration>

6、测试类

package wzu.test;

import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import wzu.Util;
import wzu.mapper.TeacherMapper;
import wzu.pojo.Teacher;
import java.util.List;

public class TeacherMapperTest {
    //（1）查询周姓副教授的教师记录；
    @Test
    public void select_zhou(){
        SqlSession sqlSession = Util.createSqlSession();
        TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
        List<Teacher> sel_first=mapper.select_zhou("周", "副教授");
        for (Teacher teacher:sel_first){
            System.out.println(teacher.toString());
        }
        Util.closeSqlSession(sqlSession);
    }
    //（2）删除手机号为135开头的讲师的教师记录；
    @Test
    public void delete(){
        SqlSession sqlSession = Util.createSqlSession();
        TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
        int count=0;
        count=mapper.delete("135","讲师");
        System.out.println("删除了"+count+"条");
        sqlSession.commit();
        Util.closeSqlSession(sqlSession);
    }
    //（3）修改TNO：121004教师的手机号为18811597853
    @Test
    public void update(){
        SqlSession sqlSession = Util.createSqlSession();
        TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
        int count=0;
        count=mapper.update(121004,"18811597853");
        System.out.println("修改了"+count+"条");
        sqlSession.commit();
        Util.closeSqlSession(sqlSession);
    }


}

二、动态SQL实现组合条件查询

（1）使用动态SQL中if+trim元素实现对teacher表中的数据的查询

（2）使用动态SQL中where元素实现对teacher表中的数据的查询