使用Mybatis完成增删改查操作_mybatis+jsp实现删除和修改-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_67358175/article/details/129484462

一.环境准备

1.建造数据库及表和插入数据

drop database if exists mydb;

create database mydb;

use mydb;

create table emp
(
eid int primary key AUTO_INCREMENT, #员工工号
ename varchar(20), #员工姓名
dept varchar(20), #员工部门
job varchar(20),#职位
sal double,#薪资
phone varchar(11),#电话
address varchar(100)#地址
);

insert into emp(ename,dept,job,sal,phone,address) values('张翠萍','测试部','测试工程
师',5800,'15821563548','郑州');
insert into emp(ename,dept,job,sal,phone,address) values('李耀菲','测试部','测试经
理',9800,'13658942168','许昌');
insert into emp(ename,dept,job,sal,phone,address) values('王长林','研发部','开发工程
师',8800,'13954865721','南阳');
insert into emp(ename,dept,job,sal,phone,address) values('陈清泰','研发部','开发经
理',14900,'13785463249','洛阳');
insert into emp(ename,dept,job,sal,phone,address) values('赵德保','运维部','运维工程
师',5200,'15785642139','信阳');
insert into emp(ename,dept,job,sal,phone,address) values('刘瑞琳','运维部','产品经
理',12600,'17956248563','商丘');


select * from emp;

2.创建实体类Emp

public class Emp {
    public Integer eid;
    public String ename;
    public String dept;
    public String job;
    public Double sal;
    public String phone;
    public String address;


    public Emp() {
    }

    public Emp(Integer eid, String ename, String dept, String job, Double sal, String phone, String address) {
        this.eid = eid;
        this.ename = ename;
        this.dept = dept;
        this.job = job;
        this.sal = sal;
        this.phone = phone;
        this.address = address;
    }

    /**
     * 获取
     * @return eid
     */
    public Integer getEid() {
        return eid;
    }

    /**
     * 设置
     * @param eid
     */
    public void setEid(Integer eid) {
        this.eid = eid;
    }

    /**
     * 获取
     * @return ename
     */
    public String getEname() {
        return ename;
    }

    /**
     * 设置
     * @param ename
     */
    public void setEname(String ename) {
        this.ename = ename;
    }

    /**
     * 获取
     * @return dept
     */
    public String getDept() {
        return dept;
    }

    /**
     * 设置
     * @param dept
     */
    public void setDept(String dept) {
        this.dept = dept;
    }

    /**
     * 获取
     * @return job
     */
    public String getJob() {
        return job;
    }

    /**
     * 设置
     * @param job
     */
    public void setJob(String job) {
        this.job = job;
    }

    /**
     * 获取
     * @return sal
     */
    public Double getSal() {
        return sal;
    }

    /**
     * 设置
     * @param sal
     */
    public void setSal(Double sal) {
        this.sal = sal;
    }

    /**
     * 获取
     * @return phone
     */
    public String getPhone() {
        return phone;
    }

    /**
     * 设置
     * @param phone
     */
    public void setPhone(String phone) {
        this.phone = phone;
    }

    /**
     * 获取
     * @return address
     */
    public String getAddress() {
        return address;
    }

    /**
     * 设置
     * @param address
     */
    public void setAddress(String address) {
        this.address = address;
    }

    public String toString() {
        return "Emp{eid = " + eid + ", ename = " + ename + ", dept = " + dept + ", job = " + job + ", sal = " + sal + ", phone = " + phone + ", address = " + address + "}";
    }
}

3.依赖

<properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
    </properties>
    <dependencies>
        <!--mybatis-->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.5</version>
        </dependency>
        <!--        mysql-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.28</version>
        </dependency>
        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>javax.servlet-api</artifactId>
            <version>3.1.0</version>
            <scope>provided</scope>
        </dependency>
        <dependency>
            <groupId>javax.servlet.jsp</groupId>
            <artifactId>jsp-api</artifactId>
            <version>2.2</version>
            <scope>provided</scope>
        </dependency>

        <dependency>
            <groupId>taglibs</groupId>
            <artifactId>standard</artifactId>
            <version>1.1.2</version>
        </dependency>

        <dependency>
            <groupId>jstl</groupId>
            <artifactId>jstl</artifactId>
            <version>1.2</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>RELEASE</version>
            <scope>test</scope>
        </dependency>
    </dependencies>

4.核心配置文件和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>


    <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:///mydb?useSSL=false&amp;useServerPrepStmts=true"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
    </environments>
<mappers>
      <mapper resource="com/w/dao/EmpDao.xml"/>

    </mappers>
</configuration>

<?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.w.dao.EmpDao">

</mapper>

二.完成增删改查操作

创建一个EmpDao接口

创建一个EmpTest测试类

 //获取SqlSessionFactory
SqlSessionFactory sqlSessionFactory= SqlSessionFactorytest.getSqlSessionFactory();
  //获取SqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession(true);
    //获取Mapper接口的代理对象
EmpDao mapper = sqlSession.getMapper(EmpDao.class);

1.查询

接口方法

@Select("select * from emp")
    List<Emp> select();

测试类中查询方法

@Test
    public void testSelectAll(){
        List<Emp> select = mapper.select();
        for (Emp emp:select) {
            System.out.println(emp);
        }

    }

2.添加

接口方法

@Insert("INSERT INTO Emp(eid,ename,dept,job,sal,phone,address) VALUES (#{eid},#{ename},#{dept},#{job},#{sal},#{phone},#{address});")
    int add(Emp emp);

测试类中添加方法

 @Test
    public void add(){
        Emp emp=new Emp();
        emp.setEname("1");
        emp.setDept("1");
        emp.setJob("1");
        emp.setSal(Double.parseDouble("1"));
        emp.setPhone("1");
        emp.setAddress("1");
        int n=mapper.add(emp);
        if(n>0){
            System.out.println("add");
        }
    }

3.删除

接口方法

 @Delete("DELETE FROM Emp WHERE eid=#{eid}")
    int delete(Integer eid);

测试类中删除方法

 @Test
    public void delete(){
        int a= mapper.delete(2);
        if(a>0){
            System.out.println("delete");
        }
    }

4.根据Id查询

接口方法

@Select("select * from Emp where eid=#{eid}")
    List<Emp> selectId(int eid);

测试类中Id查询方法

@Test
    public void testSelectAllId(){
        List<Emp> select = mapper.selectId(3);
        for (Emp emp : select) {
            System.out.println(emp);
        }
    }

5.修改

接口方法

@Update("update Emp set  ename =#{ename},dept=#{dept}, sal = #{sal},phone=#{phone},address=#{address} where  eid=#{eid}")
    int update(Emp emp);

测试类中修改方法

@Test
    public void update(){
       Emp emp=new Emp(2,"2","2","2",Double.parseDouble("2"),"2","2");
        int update = mapper.update(emp);
        if (update>0){
            System.out.println("update");
        }
    }

6.模糊查询

接口方法

@Select("select * from Emp where ename like concat('%',#{ename},'%')")
     List<Emp> seach(String keyword);

测试类中模糊查询方法

 @Test
    public void seach(){
        List<Emp> seach=mapper.seach("刘");
        System.out.println(seach);
    }

After方法可以在执行之后运行这里的代码

 @After
    public void a(){
        sqlSession.close();
    }