MyBatis - 初学笔记

super-荣

于 2023-03-15 22:34:43 发布

阅读量67

点赞数

文章标签： mybatis 数据库 mysql

本文链接：https://blog.csdn.net/m0_51972342/article/details/129571824

版权

MyBatis - 初学笔记

前提准备：

mysql假数据：

/*
Navicat MySQL Data Transfer

Source Server         : test
Source Server Version : 50520
Source Host           : localhost:3306
Source Database       : employeedb

Target Server Type    : MYSQL
Target Server Version : 50520
File Encoding         : 65001

Date: 2021-05-09 16:46:28
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `dept`
-- ----------------------------
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `dept_name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of dept
-- ----------------------------
INSERT INTO `dept` VALUES ('1', '开发部');
INSERT INTO `dept` VALUES ('2', '市场部');
INSERT INTO `dept` VALUES ('3', '客服部');

-- ----------------------------
-- Table structure for `userinfo`
-- ----------------------------
DROP TABLE IF EXISTS `userinfo`;
CREATE TABLE `userinfo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `e_name` varchar(20) DEFAULT NULL,
  `sex` varchar(10) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `deptid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_userinfo` (`deptid`),
  CONSTRAINT `FK_userinfo` FOREIGN KEY (`deptid`) REFERENCES `dept` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=72 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of userinfo
-- ----------------------------
INSERT INTO `userinfo` VALUES ('1', '小明', '女', 'ee33@.com', '3');
INSERT INTO `userinfo` VALUES ('2', '张三', '女', 'zhangshan@126.com', '2');
INSERT INTO `userinfo` VALUES ('3', '李四', '男', 'lisi@126.com', '1');
INSERT INTO `userinfo` VALUES ('4', '王五', '男', 'wangwu@126.com', '3');
INSERT INTO `userinfo` VALUES ('5', '刘兴', '男', 'ldh@126.com', '3');
INSERT INTO `userinfo` VALUES ('6', '李晓红', '女', 'lixh@126.com', '3');
INSERT INTO `userinfo` VALUES ('7', '王璐璐', '男', 'wlulu@126.com', '1');
INSERT INTO `userinfo` VALUES ('8', '刘亮亮', '男', 'liull@126.com', '1');
INSERT INTO `userinfo` VALUES ('9', 'jim', '男', 'jim@126.com', '1');
INSERT INTO `userinfo` VALUES ('10', '李晓辉', '男', 'lxh@126.com', '1');
INSERT INTO `userinfo` VALUES ('11', '王明', '男', 'wm@126.com', '1');
INSERT INTO `userinfo` VALUES ('12', '杨丹丹', '女', 'yangdd@126.com', '1');
INSERT INTO `userinfo` VALUES ('13', '郭欣莉', '女', 'guoxl@126.com', '1');
INSERT INTO `userinfo` VALUES ('14', '刘璐', '女', 'ere@23.com', '3');
INSERT INTO `userinfo` VALUES ('15', 'tom', '男', 'tom@126.com', '1');
INSERT INTO `userinfo` VALUES ('16', '于洪', '女', 'yh@12.com', '2');
INSERT INTO `userinfo` VALUES ('17', '刘小莉', '女', 'yy@23.com', '1');

-- ----------------------------
-- Procedure structure for `CountProc`
-- ----------------------------
DROP PROCEDURE IF EXISTS `CountProc`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `CountProc`(OUT param1 INT)
BEGIN
    SELECT COUNT(*) INTO param1 FROM student;
    END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for `mycursor`
-- ----------------------------
DROP PROCEDURE IF EXISTS `mycursor`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `mycursor`(IN id INT)
BEGIN
     ##声明游标
      DECLARE testcursor CURSOR FOR SELECT `name`,age FROM student WHERE id < id;
      
    END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for `proc`
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc`()
BEGIN
	SELECT * FROM student;
    END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for `proc2`
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc2`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc2`(IN `name` VARCHAR(100), IN age INT, IN address VARCHAR(100))
BEGIN
    INSERT INTO  student VALUES(NULL,`name`,age,address);
    END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for `proc3`
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc3`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc3`(INOUT param VARCHAR(100))
BEGIN
     SELECT param;
     SET param = 'hello world';
    END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for `var1`
-- ----------------------------
DROP PROCEDURE IF EXISTS `var1`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `var1`()
BEGIN
	    DECLARE age INT DEFAULT 10;
	    DECLARE myname VARCHAR(10) ;
	    DECLARE address VARCHAR(10) ;
	    
	    SET age = 23;
	    SET myname = 'jim';
	    SELECT `name` INTO myname FROM student WHERE id = 2;
	    SET address = '哈尔滨';
	    SELECT age , myname , address; 
    END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for `var2`
-- ----------------------------
DROP PROCEDURE IF EXISTS `var2`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `var2`()
BEGIN
	    DECLARE age INT ;
	    SET age = 10;
	    IF (age < 5)  
	       THEN SELECT '年龄太小了' ;  
	    ELSEIF (age >=5 AND age <=10 )
	    THEN SELECT '年龄合适' ;
	    ELSE
	      SELECT '年龄太大了' ;
	    END IF;  
    END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for `var3`
-- ----------------------------
DROP PROCEDURE IF EXISTS `var3`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `var3`()
BEGIN
	    DECLARE avgage INT ;
	    SELECT AVG(age) INTO avgage FROM student; 
	    IF (avgage > 23)
	      THEN SELECT * FROM student ORDER BY age DESC LIMIT 0,3;
	    END IF;
    END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for `var4`
-- ----------------------------
DROP PROCEDURE IF EXISTS `var4`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `var4`()
BEGIN
	    DECLARE temp INT ;
	    SET temp = 1;
	    WHILE( temp <= 10 )
	    DO
	      SET temp = temp + 1;
	    END WHILE;
	    
	    SELECT temp;
    END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for `var5`
-- ----------------------------
DROP PROCEDURE IF EXISTS `var5`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `var5`()
BEGIN
     DECLARE temp INT ;
      SET temp = 1;
      add_loop: LOOP   ##add_loop 代表一个循环标记
      SET temp = temp + 1 ;
      IF(temp >= 50) THEN LEAVE add_loop; ## LEAVE 表示退出  add_loop 循环
      END IF;
     END LOOP add_loop;
     SELECT temp;
    END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for `var6`
-- ----------------------------
DROP PROCEDURE IF EXISTS `var6`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `var6`()
BEGIN
     DECLARE temp INT ;
      SET temp = 1;
      add_loop: LOOP   ##add_loop 代表一个循环标记
      SET temp = temp + 1 ;
      INSERT INTO student VALUES(NULL,CONCAT('张三',temp),23,'北京');
      IF(temp >= 1000) THEN LEAVE add_loop; ## LEAVE 表示退出  add_loop 循环
      END IF;
     END LOOP add_loop;
    END
;;
DELIMITER ;

MyBatis_config.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="database.properties">
<!--        <property name="driver" value="com.mysql.jdbc.Driver"/>-->
<!--        <property name="url" value="jdbc:mysql://localhost:3306/employeedb?characterEncoding=utf-8&amp;serverTimezone=UTC"/>-->
<!--        <property name="username" value="root"/>-->
<!--        <property name="password" value="root1234"/>-->
    </properties>

    <typeAliases>
<!--        <typeAlias alias="Userinfo" type="com.kzr.entity.Userinfo"></typeAlias>-->
        <package name="com.kzr.entity"/><!-- 默认小写 -->
    </typeAliases>

    <environments default="development">

        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <!-- 数据库连接信息 -->
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>

    </environments>

    <!-- 加载sql映射文件 -->
    <mappers>
            <!-- 基于xml -->
<!--        <mapper resource="com/kzr/mapper/UserinfoMapper.xml"/>-->
<!--        <mapper resource="com/kzr/mapper/DeptMapper.xml"/>-->
            <!-- 基于接口 支持注解开发 -->
<!--            <mapper class="com.kzr.mapper.UserinfoMapper"/>-->
            <!-- 基于接口(按包扫描)-->
            <package name="com.kzr.mapper"/>
    </mappers>

</configuration>

database.properties:

driver = com.mysql.jdbc.Driver
url = jdbc:mysql://localhost:3306/employeedb?characterEncoding=utf-8&amp;serverTimezone=UTC
username = root
password = root1234

util:

MybatisUnit单例模式:

package com.kzr.util;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;
import java.io.InputStream;

public class MyBatisUnit {
    /**
     * 单例： 饿汉式(用到创建) 和 懒汉式(用不用到都创建)
     * 1.对象私有化且静态
     * 2.构造方法私有化
     * 3.对单例对象提供对外访问的方法
     */
    //静态 应用程序还在 其就在
    private static SqlSessionFactory sqlSessionFactory;

    private MyBatisUnit(){
        super();
    }

    public static SqlSessionFactory getSqlSessionFactory() throws IOException {
        if (sqlSessionFactory == null) {
            String resource = "MyBatis_config.xml";
            // 读取配置文件
            InputStream inputStream = Resources.getResourceAsStream(resource);
            // Session工厂
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        }
            return sqlSessionFactory;
    }

}

MyBatis：

entity类：

dept对应的实体类：

package com.kzr.entity;

public class Dept {
    private Integer id;
    private String dept_name;

    @Override
    public String toString() {
        return "Dept{" +
                "id=" + id +
                ", dept_name='" + dept_name + '\'' +
                '}';
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getDept_name() {
        return dept_name;
    }

    public void setDept_name(String dept_name) {
        this.dept_name = dept_name;
    }
}

Userinfo对应的实体类：

package com.kzr.entity;

public class Userinfo {
    private Integer id;
    private String eName;
    private String sex;
    private String email;
    private Integer deptid;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String geteName() {
        return eName;
    }

    public void seteName(String eName) {
        this.eName = eName;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public Integer getDeptid() {
        return deptid;
    }

    public void setDeptid(Integer deptid) {
        this.deptid = deptid;
    }
    @Override
    public String toString() {
        return "Userinfo{" +
                "id=" + id +
                ", eName='" + eName + '\'' +
                ", sex='" + sex + '\'' +
                ", email='" + email + '\'' +
                ", deptid=" + deptid +
                '}';
    }

    public Userinfo(String eName, String sex, String email, Integer deptid) {
        this.eName = eName;
        this.sex = sex;
        this.email = email;
        this.deptid = deptid;
    }

    public Userinfo() {
    }
}

mapper类:(对应接口和xml文件)

DeptMapper接口：

package com.kzr.mapper;

import com.kzr.entity.Dept;

import java.util.List;

public interface DeptMapper {
    /**
     * 查询数量
     * @return
     */
    Integer selectdept();

    /**
     * 查询所有
     * @return
     */
    List<Dept> selectAll();

    /**
     * 添加部门
     * @param dept
     * @return
     */
    Integer insertdept(Dept dept);

    /**
     * 修改部门
     * @param dept
     * @return
     */
    Integer updatedept(Dept dept);

    /**
     * 删除部门
     * @param id
     * @return
     */
    Integer delectdept(Integer id);
}

UserinfoMapper接口：

package com.kzr.mapper;

import com.kzr.entity.Userinfo;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface UserinfoMapper {
    /**
     * 查询数量
     * @return
     */
    Integer selectcount();

    /**
     * 查询所有
     * @return
     */
    List<Userinfo> selectAll();

    /**
     * 添加数据
     */
    Integer insertuserinfo(Userinfo userinfo);

    /**
     * 修改
     */
    Integer updateuserinfo(Userinfo userinfo);

    /**
     * 删除
     */
    Integer deleteuserinfo(Integer userinfonumber);

    /**
     * 通过id 查用户
     * @param id
     * @return
     */
    Userinfo selectByID(Integer id);

    /**
     * 姓别 和 部门id 查询
     * @param sex
     * @param deptid
     * @return
     */
      List<Userinfo> selectByMany(@Param("sex") String sex, @Param("deptid") Integer deptid);
//    List<Userinfo> selectByMany(Map map);

    /**
     * 按名字模糊查询
     * @param eName
     * @return
     */
      List<Userinfo> selectByName(String eName);
}

xml配置文件：

DeptMapper.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.kzr.mapper.DeptMapper">
    <select id="selectdept"  resultType="int">
        select count(*) from dept;
    </select>

    <select id="selectAll" resultType="dept">
        select * from dept;
    </select>

    <insert id="insertdept" parameterType="dept">
        insert into dept(dept_name) values (#{dept_name});
    </insert>

    <update id="updatedept" parameterType="dept">
        update dept set dept_name = #{dept_name} where id = #{id}
    </update>

    <delete id="delectdept" parameterType="int">
        delete from dept where id = #{id}
    </delete>
</mapper>

UserinfoMapper.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.kzr.mapper.UserinfoMapper">
    
    <resultMap type="userinfo" id="userinfoMap">
        <id column="id" property="id"/>
        <result column="e_name" property="eName"/>
        <result column="sex" property="sex"/>
        <result column="email" property="email"/>
        <result column="deptid" property="deptid"/>
    </resultMap>

    <select id="selectcount"  resultType="int">
        select count(*) from userinfo;
    </select>

    <select id="selectByID" resultMap="userinfoMap" resultType="userinfo">
        select * from userinfo where id = #{id}
    </select>

    <select id="selectByMany" resultMap="userinfoMap" resultType="userinfo">
        select * from userinfo where sex = #{sex} and deptid = #{deptid}
    </select>

    <!-- 基于默认映射，同名映射 -->
    <select id="selectAll" resultMap="userinfoMap" resultType="userinfo">
        select * from userinfo;
    </select>

    <select id="selectByName" resultMap="userinfoMap"  resultType="userinfo" >
        select * from userinfo where e_name like concat(#{eName},'%')
    </select>

    <insert id="insertuserinfo" parameterType="userinfo">
        insert into userinfo(e_name,sex,email,deptid) values (#{eName},#{sex},#{email},#{deptid});
    </insert>

    <update id="updateuserinfo" parameterType="userinfo">
        update userinfo set
        e_name = #{eName},
        sex = #{sex},
        email = #{email},
        deptid = #{deptid}
        where id = #{id}
    </update>

    <delete id="deleteuserinfo" parameterType="int">
    delete from userinfo where id = #{id}
    </delete>
</mapper>

注意：

1、id与接口名字相同
2、ResultType相对与ResultMap而言更简单一点。只有满足ORM时，即数据库表中的字段名和实体类中的属性完全一致时，才能使用，否则会出现数据不显示的情况。
3、ResultMap和ResultType的功能类似，但是ResultMap更强大一点，ResultMap可以实现将查询结果映射为复杂类型的pojo。
4、当需要多个参数时候需要使用@Param进行传参

本测试用例使用字段e_name 与实体类中字段名不相符合使用下述代码

    <resultMap type="userinfo" id="userinfoMap">
        <id column="id" property="id"/>
        <result column="e_name" property="eName"/>
        <result column="sex" property="sex"/>
        <result column="email" property="email"/>
        <result column="deptid" property="deptid"/>
    </resultMap>

column --- 对应字段名
property --- 实体类属性名
使用resultMap进行映射

测试类：

一：基于接口

Dept测试类：

package com.kzr.test;

import com.kzr.entity.Dept;
import com.kzr.mapper.DeptMapper;
import com.kzr.util.MyBatisUnit;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.junit.Test;

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

public class DeptTest {
    /**
     * 查询条数(统计) xml
     * @throws IOException
     */
    @Test
    public void countUserInfo1() throws IOException {
        SqlSessionFactory sqlSessionFactory = MyBatisUnit.getSqlSessionFactory();
        SqlSession sqlSession = sqlSessionFactory.openSession();
        int number = sqlSession.getMapper(DeptMapper.class).selectdept();
        System.out.println("查询条数共：" + number + "条");
    }

    @Test
    public void countUserInfo2() throws IOException {
        SqlSessionFactory sqlSessionFactory = MyBatisUnit.getSqlSessionFactory();
        SqlSession sqlSession = sqlSessionFactory.openSession();
        int number = sqlSession.getMapper(DeptMapper.class).selectdept();
        System.out.println("查询条数共：" + number + "条");
    }

    /**
     * 查询所有表名
     * @throws IOException
     */
    @Test
    public void selectAll() throws IOException {
        SqlSessionFactory sqlSessionFactory = MyBatisUnit.getSqlSessionFactory();
        SqlSession sqlSession = sqlSessionFactory.openSession();
        List<Dept> depts = sqlSession.getMapper(DeptMapper.class).selectAll();
        for (Dept dept : depts) {
            System.out.println("部门名：" + dept.getDept_name());
        }
    }

    /**
     * 添加部门
     * @throws IOException
     */
    @Test
    public void insertDept() throws IOException {
        SqlSessionFactory sqlSessionFactory = MyBatisUnit.getSqlSessionFactory();
        SqlSession sqlSession = sqlSessionFactory.openSession();
        Dept dept = new Dept();
        dept.setDept_name("咸鱼部门");
        int row = sqlSession.getMapper(DeptMapper.class).insertdept(dept);
        if (row > 0){
            System.out.println("添加成功");
            sqlSession.commit();
        }else {
            System.out.println("添加失败");
        }
        sqlSession.close();
    }

    /**
     * 修改部门
     * @throws IOException
     */
    @Test
    public void updataDept() throws IOException {
        SqlSessionFactory sqlSessionFactory = MyBatisUnit.getSqlSessionFactory();
        SqlSession sqlSession = sqlSessionFactory.openSession();
        Dept dept = new Dept();
        dept.setDept_name("永杰部门");
        dept.setId(4);
        int row = sqlSession.getMapper(DeptMapper.class).updatedept(dept);
        if (row > 0){
            System.out.println("修改成功");
            sqlSession.commit();
        }else {
            System.out.println("修改失败");
        }
        sqlSession.close();
    }

    /**
     * 删除部门
     * @throws IOException
     */
    @Test
    public void delectDept() throws IOException {
        SqlSessionFactory sqlSessionFactory = MyBatisUnit.getSqlSessionFactory();
        SqlSession sqlSession = sqlSessionFactory.openSession();
        int number = 5;
        int row = sqlSession.getMapper(DeptMapper.class).delectdept(number);
        if (row > 0){
            System.out.println("删除成功");
            sqlSession.commit();
        }else {
            System.out.println("删除失败");
        }
        sqlSession.close();
    }
}

Userinfo测试类：

package com.kzr.test;

import com.kzr.entity.Userinfo;
import com.kzr.mapper.UserinfoMapper;
import com.kzr.util.MyBatisUnit;
import com.kzr.util.SessionFactoryUtil;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.junit.Test;

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

/**
 * 基于接口执行 getMapper()
 */
public class TEST2 {
    /**
     * 查询条数(统计)
     * @throws IOException
     */
    @Test
    public void countUserInfo() throws IOException {

        SqlSession sqlSession = SessionFactoryUtil.getSqlSession();
        int number = sqlSession.getMapper(UserinfoMapper.class).selectcount();
        System.out.println("查询条数共：" + number + "条");

    }
    /**
     * 查询所有表
     * @throws IOException
     */
    @Test
    public void query() throws IOException {
        SqlSession sqlSession = SessionFactoryUtil.getSqlSession();

        List<Userinfo> userinfos = sqlSession.getMapper(UserinfoMapper.class).selectAll();
        for (Userinfo userinfo : userinfos) {
            System.out.println("编号：" + userinfo.getId()+ "\t" + "姓名："+ userinfo.geteName() + "\t" + "emil："+ userinfo.getEmail() + "\t" +
            "性别：" + userinfo.getSex() +"\t" + "部门编号：" + userinfo.getDeptid());
        }

        sqlSession.close();
    }

    /**
     * 插入数据
     * @throws IOException
     */
    @Test
    public void insert() throws IOException {
        SqlSession sqlSession = SessionFactoryUtil.getSqlSession();

        Userinfo userinfo = new Userinfo();
        userinfo.seteName("李康");
        userinfo.setEmail("990242941@qq.com");
        userinfo.setSex("男");
        userinfo.setDeptid(3);

       int row = sqlSession.getMapper(UserinfoMapper.class).insertuserinfo(userinfo);

       if (row > 0){
           System.out.println("插入成功");
       }else {
           System.out.println("插入失败");
       }
        sqlSession.commit();
        sqlSession.close();
    }

    /**
     * 修改数据
     * @throws IOException
     */
    @Test
    public void update() throws IOException {
        SqlSession sqlSession = SessionFactoryUtil.getSqlSession();

        Userinfo userinfo = new Userinfo();
        userinfo.seteName("李康");
        userinfo.setEmail("1469025049@qq.com");
        userinfo.setSex("男");
        userinfo.setDeptid(3);
        userinfo.setId(72);

        sqlSession.getMapper(UserinfoMapper.class).updateuserinfo(userinfo);


        sqlSession.commit();
        sqlSession.close();
    }

    /**
     * 删除数据
     * @throws IOException
     */
    @Test
    public void delect() throws IOException {
        SqlSessionFactory sf = SessionFactoryUtil.getSessionFactory();
        SqlSession sqlSession = sf.openSession();
        int num = 72;

        sqlSession.getMapper(UserinfoMapper.class).deleteuserinfo(num);

        sqlSession.commit();
        sqlSession.close();
    }

    /**
     * 根据id 查询用户
     */
    @Test
    public void selectByID() throws IOException {
        SqlSession sqlSession = MyBatisUnit.getSqlSessionFactory().openSession();
        int id = 2;
        Userinfo userinfo = sqlSession.getMapper(UserinfoMapper.class).selectByID(id);
        System.out.println("姓名：" + userinfo.geteName() + "部门：" + userinfo.getDeptid());
    }

    /**
     * 根据性别和部门查询
     * @throws IOException
     */
    @Test
    public void selectByMany() throws IOException {
        SqlSession sqlSession = MyBatisUnit.getSqlSessionFactory().openSession();
        String sex = "男";
        Integer id = 1;
        /**
        Map<String,String> map = new HashMap<String,String>();
        map.put("sex",sex);
        map.put("deptid", String.valueOf(id));
        List<Userinfo> userinfos = sqlSession.getMapper(UserinfoMapper.class).selectByMany(map);map
       **/
        List<Userinfo> userinfos = sqlSession.getMapper(UserinfoMapper.class).selectByMany(sex,id);
        for (Userinfo userinfo : userinfos){
            System.out.println("姓名" + userinfo.geteName() + "性别" + userinfo.getSex());
        }
        sqlSession.close();
    }

    /**
     *
     * @throws IOException
     */
    @Test
    public void selectByname() throws IOException {
        SqlSession sqlSession = MyBatisUnit.getSqlSessionFactory().openSession();
        List<Userinfo> userinfos = sqlSession.getMapper(UserinfoMapper.class).selectByName("李");
        for (Userinfo userinfo : userinfos){
            System.out.println("姓名" + userinfo.geteName() + "性别" + userinfo.getSex());
        }
        sqlSession.close();
    }
}

二：直接调用：

UserInfo对应的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="test">
    
    <resultMap type="com.kzr.entity.Userinfo" id="userinfoMap">
        <id column="id" property="id"/>
        <result column="e_name" property="eName"/>
        <result column="sex" property="sex"/>
        <result column="email" property="email"/>
        <result column="deptid" property="deptid"/>
    </resultMap>

    <select id="selectcount" resultMap="userinfoMap" resultType="int">
        select count(*) from userinfo;
    </select>

    <select id="selectAll" resultMap="userinfoMap" resultType="com.kzr.entity.Userinfo">
        select * from userinfo;
    </select>

    <insert id="insertuserinfo">
        insert into userinfo(e_name,sex,email,deptid) values (#{eName},#{sex},#{email},#{deptid});
    </insert>

    <update id="updateuserinfo">
        update userinfo set
        e_name = #{eName},
        sex = #{sex},
        email = #{email},
        deptid = #{deptid}
        where id = #{id}
    </update>

    <delete id="deleteuserinfo">
    delete from userinfo where id = #{id}
    </delete>
</mapper>

Userinfo实现类：

package com.kzr.test;
import com.kzr.entity.Userinfo;
import com.kzr.util.SessionFactoryUtil;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.junit.Test;
import java.io.IOException;
import java.util.List;

public class Main {

    /**
     * 查询所有表
     * @throws IOException
     */
    @Test
    public void query() throws IOException {
        SqlSession sqlSession = SessionFactoryUtil.getSqlSession();

        List<Userinfo> userinfos = sqlSession.selectList("test.selectAll");
        System.out.println(userinfos);

        sqlSession.close();
    }

    /**
     * 插入数据
     * @throws IOException
     */
    @Test
    public void insert() throws IOException {
        SqlSession sqlSession = SessionFactoryUtil.getSqlSession();


        Userinfo userinfo = new Userinfo();
        userinfo.seteName("康振荣");
        userinfo.setEmail("990242941@qq.com");
        userinfo.setSex("男");
        userinfo.setDeptid(3);

        int row = sqlSession.insert("test.insertuserinfo",userinfo);

        if (row > 0){
            System.out.println("恭喜您插入了" + row + "条数据");
        }else {
            System.out.println("执行操作失败");
        }

        sqlSession.commit();
        sqlSession.close();
    }

    /**
     * 修改数据
     * @throws IOException
     */
    @Test
    public void update() throws IOException {
        SqlSession sqlSession = SessionFactoryUtil.getSqlSession();

        Userinfo userinfo = new Userinfo();
        userinfo.seteName("康振荣");
        userinfo.setEmail("1469025049@qq.com");
        userinfo.setSex("男");
        userinfo.setDeptid(2);
        userinfo.setId(72);

        int row = sqlSession.update("test.updateuserinfo",userinfo);
        if (row > 0){
            System.out.println("已修改成功");
        }else {
            System.out.println("修改失败");
        }

        sqlSession.commit();
        sqlSession.close();
    }

    /**
     * 删除数据
     * @throws IOException
     */
    @Test
    public void delect() throws IOException {
        SqlSessionFactory sf = SessionFactoryUtil.getSessionFactory();
        SqlSession sqlSession = sf.openSession();

        int row = sqlSession.delete("test.deleteuserinfo",73);
        if (row > 0){
            System.out.println("删除成功");
        }else {
            System.out.println("删除失败");
        }

        sqlSession.commit();
        sqlSession.close();
    }
}

调用方法方式：

1、基于接口执行 getMapper()
sqlSession.getMapper(UserinfoMapper.class).selectByMany(map);
2、session.selectList("mybatis.xml中的id")

super-荣

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫