用Mybatis简单实现数据库的增删改查

在pom.xml文件中添加依赖

依赖导入

<dependencies>
        <!--mybatis-->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.11</version>
        </dependency>
 
        <!--mysql驱动-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <!--注意mysql驱动这里的版本，一定要与本地下载的版本一样，否则会报红，我电脑里下载的版本就是8.0.32-->
            <version>8.0.32</version>
        </dependency>
        
        <!--junit 单元测试-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>3.8.2</version>
            <scope>test</scope>
        </dependency>
    </dependencies>

EmployeeMapper.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">
<!--
    namespace：命名空间
-->
<mapper namespace="org.example.mapper.EmployeeMapper">
    <!-- id：每一个sql语句的唯一标识，即外部通过id来调用语句   -->
    <!-- resultType：返回的结果类型，即定义的POJO类-->
    <resultMap id="EmployeeResultMap" type="org.example.pojo.Department">

    </resultMap>
    <insert id="InsertDept">
        insert into ddept (deptno,dname,loc)values (#{deptno},#{dname},#{loc});
    </insert>
    <!-- where 1=2 为假，所以有相同结构但没有数据-->
    <!-- where 1=1 为真 所以选择所有数据-->
    <update id="CreateTable">
        Create table ${newTable} as select * from ${originalTable} where 1=2;
    </update>
    <delete id="DeleteEmployee">
        delete from emp where ename =#{ename};
    </delete>
    <select id="selectAll" resultType="org.example.pojo.Employee">
        select * from emp;
    </select>
    <select id="selectByJob" resultType="org.example.pojo.Employee">
        select * from emp where job =#{job};
    </select>
    <select id="selectByComm" resultType="org.example.pojo.Employee">
        select * from emp where comm is null or comm
        <![CDATA[
            <
        ]]>
        #{comm};
    </select>
    <resultMap id="EmpDeptMap" type="org.example.pojo.Employee">
        <id property="empno" column="empno"/>
        <result property="ename" column="ename"/>
        <result property="comm" column="comm"/>
        <result property="job" column="job"/>
        <result property="mgr" column="mgr"/>
        <result property="hiredate" column="hiredate"/>
        <result property="sal" column="sal"/>
        <result property="deptno" column="deptno"/>

        <association property="department">
            <result column="deptno" property="deptno"/>
            <result column="dname" property="dname"/>
            <result column="loc" property="loc"/>
        </association>
    </resultMap>
    <select id="selectNameDepartment" resultMap="EmpDeptMap">
        select * from dept inner join emp on emp.deptno=dept.deptno;
    </select>
    <select id="selectName" resultType="org.example.pojo.Employee">
        select * from emp where ename like #{lanme};
    </select>
    <select id="selectDepartment" resultType="org.example.pojo.Department">
        select * from ddept;
    </select>


</mapper>

Test.java

package org.example.test;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.example.mapper.EmployeeMapper;
import org.example.pojo.Department;
import org.example.pojo.Employee;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

public class Test {
    public static void main(String[] args) {
//        1.加载mybatis的核心配置文件a，获取SqlSessionFactory对象
        String resource = "mybatis-config.xml";
        InputStream inputStream;
        try {
            inputStream = Resources.getResourceAsStream(resource);
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

//            2.获取SqlSession对象，执行sql语句
            SqlSession sqlSession = sqlSessionFactory.openSession(true);

            // 3.获取Mapper接口的代理对象
            EmployeeMapper employeeMapper= sqlSession.getMapper(EmployeeMapper.class);

            //            3.执行sql语句

//            List<Employee> employees=employeeMapper.selectAll();
//            for (Employee emp:employees) {
//                System.out.println(emp.getJob()+"   "+emp.getDeptno());
//            }
//            List<Employee> employees=employeeMapper.selectByJob("salesman");
//            for (Employee emp:employees) {
//                System.out.println(emp.getEname());
//            }
//            List<Employee> employees=employeeMapper.selectByComm(300);
//            for (Employee emp:employees) {
//                System.out.println(emp.getEname()+"   "+ emp.getComm());
//            }
//            List<Employee> employees=employeeMapper.selectNameDepartment();
//            for(Employee e:employees){
//                System.out.println(e.getEname()+"   "+e.getDepartment().getDname());
//            }

//            String lname="%A%";
//            List<Employee> employees=employeeMapper.selectName(lname);
//            for(Employee e:employees){
//                System.out.println(e.getEname());
//            }
//            employeeMapper.CreateTable("ddept","dept");
//            employeeMapper.CreateTable("eemp","emp");
//            List <Department> departments=new ArrayList<>();
//            Department d1=new Department(1,"Factory","China");
//            Department d2=new Department(2,"Game","Japan");
//            Department d3=new Department(3,"Anime","Japan");
//            Department d4=new Department(4,"Association","USA");
//            Department d5=new Department(5,"Trips","China");
//            departments.add(d1);
//            departments.add(d2);
//            departments.add(d3);
//            departments.add(d4);
//            departments.add(d5);
//
//            for(Department d:departments){
//                employeeMapper.InsertDept(d);
//            }
//
//            sqlSession.commit();//提交
//
//            List<Department> depts=employeeMapper.selectDepartment();
//            for(Department d:depts){
//                System.out.println(d);
//            }

            employeeMapper.DeleteEmployee("Smith");
            List<Employee> e =  employeeMapper.selectName("Smith");
            System.out.println(e);

//            4.释放资源
            sqlSession.close();

        } catch (IOException e) {
            throw new RuntimeException(e);
        }

    }
}

EmployeeMapper.java

package org.example.mapper;

import org.apache.ibatis.annotations.Param;
import org.example.pojo.Department;
import org.example.pojo.Employee;

import java.util.ArrayList;
import java.util.List;

public interface EmployeeMapper {
    public List<Employee> selectAll();
    public List<Employee> selectByJob(String job);
    public List<Employee>selectByComm(double comm);
    public List<Employee>selectNameDepartment();
    public List<Employee>selectName(String lname);
    public void CreateTable(@Param("newTable") String newTable, @Param("originalTable") String originalTable);
    public void InsertDept(Department department);
    public  List<Department>selectDepartment();
    public void DeleteEmployee(String ename);

}

Employeet.java

package org.example.pojo;

public class Employee {
    private int empno;
    private String ename;
    private String Job;
    private int mgr;
    private String Hiredate;
    private double sal;
    private double comm;
    private int deptno;
    private Department department;

    public Department getDepartment() {
        return department;
    }

    public void setDeptment(Department department) {
        this.department = department;
    }
    public Employee() {
    }

    public Employee(int empno, String ename, String job, int mgr, String Hiredate, double sal, double comm, int deptno) {
        this.empno = empno;
        this.ename = ename;
        Job = job;
        this.mgr = mgr;
        this.Hiredate = Hiredate;
        this.sal = sal;
        this.comm = comm;
        this.deptno = deptno;
    }
    public int getMgr() {
        return mgr;
    }

    public void setMgr(int mgr) {
        this.mgr = mgr;
    }

    public int getEmpno() {
        return empno;
    }

    public String getEname() {
        return ename;
    }

    public String getJob() {
        return Job;
    }

    public String getHiredate() {
        return Hiredate;
    }

    public double getSal() {
        return sal;
    }

    public double getComm() {
        return comm;
    }

    public int getDeptno() {
        return deptno;
    }

    public void setEmpno(int empno) {
        this.empno = empno;
    }

    public void setEname(String ename) {
        this.ename = ename;
    }

    public void setJob(String job) {
        this.Job = job;
    }

    public void setHiredate(String hiredate) {
        this.Hiredate = hiredate;
    }

    public void setSal(double sal) {
        this.sal = sal;
    }

    public void setComm(double comm) {
        this.comm = comm;
    }

    public void setDeptno(int deptno) {
        this.deptno = deptno;
    }

    @Override
    public String toString() {
        return "Employee{" +
                "empno=" + empno +
                ", ename='" + ename + '\'' +
                ", Job='" + Job + '\'' +
                ", mgr=" + mgr +
                ", Hiredate='" + Hiredate + '\'' +
                ", sal=" + sal +
                ", comm=" + comm +
                ", deptno=" + deptno +
                '}';
    }
}

Department.java

package org.example.pojo;

public class Department {

        private Integer deptno;
        private String dname;
        private String loc;

        public Department(Integer deptno, String dname, String loc) {
            this.deptno = deptno;
            this.dname = dname;
            this.loc = loc;
        }

        public Integer getDeptno() {
            return deptno;
        }

        public String getDname() {
            return dname;
        }

        public String getLoc() {
            return loc;
        }

        public void setDeptno(Integer deptno) {
            this.deptno = deptno;
        }

        public void setDname(String dname) {
            this.dname = dname;
        }

        public void setLoc(String loc) {
            this.loc = loc;
        }

        @Override
        public String toString() {
            return "Deptment{" +
                    "deptno=" + deptno +
                    ", dname='" + dname + '\'' +
                    ", loc='" + loc + '\'' +
                    '}';
        }
    }

1.检索所有的工作和相应的部门编号

Test.java

mployee> employees=employeeMapper.selectAll();
            for (Employee emp:employees) {
                System.out.println(emp.getJob()+"   "+emp.getDeptno());
            }

EmployeeMapper.xml

<select id="selectAll" resultType="org.example.pojo.Employee">
        select * from emp;
    </select>

EmployeeMapper.java

public List<Employee> selectAll();

2.检索工作是salesman的员工姓名；

EmployeeMapper.xml

  <select id="selectByJob" resultType="org.example.pojo.Employee">
        select * from emp where job =#{job};
    </select>

Test.java

 List<Employee> employees=employeeMapper.selectByJob("salesman");
            for (Employee emp:employees) {
                System.out.println(emp.getEname());
            }

EmployeeMapper.java

 public List<Employee> selectByJob(String job);

3.检索员工津贴comm为null或comm小于300的员工姓名和津贴值；

EmployeeMapper.xml

<select id="selectByComm" resultType="org.example.pojo.Employee">
        select * from emp where comm is null or comm
        <![CDATA[
            <        
        ]]>
        #{comm};
    </select>

Test.java

List<Employee> employees=employeeMapper.selectByComm(300);
            for (Employee emp:employees) {
                System.out.println(emp.getEname()+"   "+ emp.getComm());
            }

EmployeeMapper.java

public List<Employee>selectByComm(double comm);

4.检索所有职员的姓名和所在部门名称

EmployeeMapper.xml

 <resultMap id="EmpDeptMap" type="org.example.pojo.Employee">
        <id property="empno" column="empno"/>
        <result property="ename" column="ename"/>
        <result property="comm" column="comm"/>
        <result property="job" column="job"/>
        <result property="mgr" column="mgr"/>
        <result property="hiredate" column="hiredate"/>
        <result property="sal" column="sal"/>
        <result property="deptno" column="deptno"/>

        <association property="department">
            <result column="deptno" property="deptno"/>
            <result column="dname" property="dname"/>
            <result column="loc" property="loc"/>
        </association>
    </resultMap>
    <select id="selectNameDepartment" resultMap="EmpDeptMap">
        select * from dept inner join emp on emp.deptno=dept.deptno;
    </select>

Test.java

 List<Employee> employees=employeeMapper.selectNameDepartment();
            for(Employee e:employees){
                System.out.println(e.getEname()+"   "+e.getDepartment().getDname());
            }

Employee.java

private Department department;

    public Department getDepartment() {
        return department;
    }

    public void setDeptment(Department department) {
        this.department = department;
    }

EmployeeMapper.java

public List<Employee>selectNameDepartment();

5.检索在任何位置有字母“A”的员工姓名

EmployeeMapper.xml

 <select id="selectName" resultType="org.example.pojo.Employee">
        select * from emp where ename like #{lanme};
    </select>

Test.java

      String lname="%A%";
            List<Employee> employees=employeeMapper.selectName(lname);
            for(Employee e:employees){
                System.out.println(e.getEname());
            }

EmployeeMapper.java

public List<Employee>selectName(String lname);

6.请使用你的账户创建两个表，包括属性、数据类型

EmployeeMapper.xml

<update id="CreateTable">
        Create table ${newTable}as select * from ${originalTable} where 1=2;
    </update>

Test.java

   employeeMapper.CreateTable("ddept","dept");
            employeeMapper.CreateTable("eemp","emp");

EmployeeMapper.java

public void CreateTable(@Param("newTable") String newTable, @Param("originalTable") String originalTable);

@param

当在查询的时候，一个参数的时候，在直接在入参中写入就可以了，但是当两个参数的时候，怎么办？

两种办法：第一用Map ,第二：就用@param，[可读性比较好，参数少的时候]

 注意：当参数为2-5个时候，用@param最佳，当大于5的时候，肯定会选择map了

7.向表eemp中插入至少5条员工记录

IDEA 修改所有相同代码段

ctrl+F
ctrl+R

insert 注意事项

insert into (字段名) values ()
必须都写，不然会报错

EmployeeMapper.xml

  <insert id="InsertDept">
        insert into ddept (deptno,dname,loc)values (#{deptno},#{dname},#{loc});
    </insert>

<select id="selectDepartment" resultType="org.example.pojo.Department">
        select * from ddept;
    </select>

Test.java

List <Department> departments=new ArrayList<>();
            Department d1=new Department(1,"Factory","China");
            Department d2=new Department(2,"Game","Japan");
            Department d3=new Department(3,"Anime","Japan");
            Department d4=new Department(4,"Association","USA");
            Department d5=new Department(5,"Trips","China");
            departments.add(d1);
            departments.add(d2);
            departments.add(d3);
            departments.add(d4);
            departments.add(d5);

            for(Department d:departments){
                employeeMapper.InsertDept(d);
            }

            sqlSession.commit();//提交

            List<Department> depts=employeeMapper.selectDepartment();
            for(Department d:depts){
                System.out.println(d);
            }

EmployeeMapper.java

   public void InsertDept(Department department);
   public  List<Department>selectDepartment();

8.从emp中删除名为“SMITH”的记录;

EmployeeMapper.xml

 <delete id="DeleteEmployee">
        delete from emp where ename =#{ename};
    </delete>

Test.java

   employeeMapper.DeleteEmployee("Smith");
            List<Employee> e =  employeeMapper.selectName("Smith");
            System.out.println(e);

EmployeeMapper.java

   public void DeleteEmployee(String ename);

而后出现报错

花费大量时间没办法解决

Error updating database. Cause: java.sql.SQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails (mybatis.emp, CONSTRAINT emp_ibfk_1 FOREIGN KEY (Mgr) REFERENCES emp (empno))
而后发现可以自己删除外键，但只能其中删除一个，多删不行

这个真的不会弄