Mybatis的简单增删改查

1、创建数据库并新增表数据

use mydb;

DROP TABLE emp;

create table emp(
eid int  primary key auto_increment,
ename varchar(20),
dept varchar(20),
job varchar(50),
sal int,
phone  varchar(21),
address varchar(50)
)

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','商丘');

2、创建一个项目

lib:用来存储架包

bean：与数据库信息关联；text:进行方法测试类；Dao:方法私有类

 2.1:bean内的实体类

 private Integer eid;
    private String ename;
    private String dept;
    private String job;
    private Integer sal;
    private String phone;
    private String address;

    public Integer getEid() {
        return eid;
    }

    public void setEid(Integer eid) {
        this.eid = eid;
    }

    public String getEname() {
        return ename;
    }

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

    public String getDept() {
        return dept;
    }

    public void setDept(String dept) {
        this.dept = dept;
    }

    public String getJob() {
        return job;
    }

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

    public Integer getSal() {
        return sal;
    }

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

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

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

 2.2Dao内的创建

方法的私有类

package com.Dao;

import com.bean.Emp;

import java.util.List;

public interface EmpDao {
    /**
     * 查询
     * @return
     */
    List<Emp> select();

    /**
     * 根据员工id查询员工信息
     * @param eid
     * @return
     */
    Emp findByid(int eid);

    /**
     * 新增员工信息
     * @param emp
     * @return
     */
    int addemp(Emp emp);

    /**
     * 修改员工信息
     * @param emp
     * @return
     */
    int updatee(Emp emp);

    /**
     * 根据员工id删除
     * @param eid
     * @return
     */
    int delect(int eid);

    /**
     * 模糊查询
     * @param ename
     * @return
     */
    List<Emp> likeEmp(String ename);

}

在Dao里面创建一个xml，为了跟私有类进行相互关联，并存储sql语句进行数据的增删改查

<?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.Dao.EmpDao">
<!--   全查-->
    <select id="select" resultType="com.bean.Emp">
        select * from emp
    </select>
<!--    新增-->
    <insert id="addemp" parameterType="com.bean.Emp">
        insert into emp(ename,dept,job,sal,phone,address) values(#{ename},#{dept},#{job},#{sal},#{phone},#{address});
    </insert>
<!--    通过id查询员工信息-->
    <select id="findByid" parameterType="int" resultType="com.bean.Emp">
        select * from emp where eid=#{eid}
    </select>
<!--   通过员工id修改员工信息-->
    <update id="updatee" parameterType="com.bean.Emp">
        update  emp set ename=#{ename},dept=#{dept},job=#{job},sal=#{sal},phone=#{phone},address=#{address} where eid=#{eid};
    </update>
<!--    通过id删除员工-->
    <delete id="delect" parameterType="int">
        delete from emp where eid=#{eid};
    </delete>
<!--    模糊查询-->
   <select id="likeEmp" parameterType="String" resultType="com.bean.Emp">
       select * from emp where dept like concat('%',#{ename},'%')
   </select>
</mapper>

2.3在src内创建一个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="mysql">
        <environment id="mysql">
            <transactionManager type="jdbc"></transactionManager>
            <dataSource type="pooled">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://127.0.0.1:3306/mydb"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
        <environment id="oracle">
            <transactionManager type=""></transactionManager>
            <dataSource type=""></dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper class="com.Dao.EmpDao"/>
    </mappers>
</configuration>

 将Dao内的与其共联

<mappers>
        <mapper class="com.Dao.EmpDao"/>
    </mappers>

 使用注解开发

public interface EmpDao {
    /**
     * 查询
     * @return
     */
  @Select("select * from emp")
    List<Emp> select();

    /**
     * 根据员工id查询员工信息
     * @param eid
     * @return
     */
    Emp findByid(int eid);

    /**
     * 新增员工信息
     * @param emp
     * @return
     */
 @Insert("insert into emp (ename,address,sex,birthday)values(#{ename},#{address},#{sex},#{birthday})")
    int addemp(Emp emp);

    /**
     * 修改员工信息
     * @param emp
     * @return
     */
@Update("update emp set ename=#{ename},sex=#{sex},birthday=#{birthday},address=#{address} where eid=#{eid}")
    int updatee(Emp emp);

    /**
     * 根据员工id删除
     * @param eid
     * @return
     */
 @Delete("delete from emp where eid=#{eid}")
    int delect(int eid);

    /**
     * 模糊查询
     * @param ename
     * @return
     */
 @Select("select * from emp where ename like '%${value}%'")  
    List<Emp> likeEmp(String ename);

}

3、text的测试类

用来测试功能运行的代码

package com.text;

import com.Dao.EmpDao;
import com.bean.Emp;
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.junit.After;
import org.junit.Before;
import org.junit.Test;

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

public class EmpText {
    InputStream stream=null;
    SqlSessionFactoryBuilder builder=null;
    SqlSessionFactory factory=null;
    SqlSession session=null;
    EmpDao empDao=null;

    @Before
    public  void  init() throws IOException {
        stream= Resources.getResourceAsStream("mybatis.xml");
        builder=new SqlSessionFactoryBuilder();
        factory=builder.build(stream);
        session=factory.openSession();
        empDao=session.getMapper(EmpDao.class);
    }

    @After
    public void  da() throws IOException {
        session.commit();
        session.close();
        stream.close();
    }

    /**查询所有*/
    @Test
    public void textselect(){
        List<Emp> empList= empDao.select();
        for (Emp emp:empList){
            System.out.println(emp);
        }
    }

    /**根据id查询员工信息*/
    @Test
    public  void textfindByid(){
        Emp byid = empDao.findByid(1);
        System.out.println(byid);
    }

    /**新增员工*/
    @Test
    public  void  textAdd(){
        Emp emp=new Emp();
        emp.setEname("乔峰");
        emp.setDept("保安队");
        emp.setJob("保安队大队长");
        emp.setSal(10000);
        emp.setPhone("路边乞丐给纸钱");
        emp.setAddress("峨眉山");

      int a=  empDao.addemp(emp);
      if(a>0){
          System.out.println("新增成功!!");
      }else {
          System.out.println("新增失败!!!");
      }
    }

    /**修改员工信息*/
    @Test
    public  void  textuodate(){
        Emp oldemp=empDao.findByid(7);
        System.out.println("修改前信息"+oldemp);
        oldemp.setAddress("翻斗花园");
        empDao.updatee(oldemp);
        Emp newemp=empDao.findByid(7);
        System.out.println("修改后"+newemp);
    }

    /**通过员工id删除员工信息*/
    @Test
    public  void  textdelect(){
        int i = empDao.delect(7);
        if (i>0){
            System.out.println("删除成功");
        }else {
            System.out.println("删除失败");
        }
    }

    /**模糊查询*/
    @Test
    public  void  textlike(){
        List<Emp> empList=empDao.likeEmp("测");
        for (Emp emp:empList){
            System.out.println(emp);
        }
    }
}