Mybatis增删改查(CURD)操作

Mybatis实现数据的增删改查

什么是 MyBatis ？

MyBatis 是一款优秀的持久层框架，它支持定制化 SQL、存储过程以及高级映射。MyBatis 避免了几乎所有的 JDBC 代码和手动设置参数以及获取结果集。MyBatis 可以使用简单的 XML 或注解来配置和映射原生信息，将接口和 Java 的 POJOs(Plain Old Java Objects,普通的 Java对象)映射成数据库中的记录。
源于Mybatis官方网站点>>这里<<进入官网

如果使用IDEA工具需要导入相关依赖

<dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.12</version>
      <scope>test</scope>
    </dependency>
    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis</artifactId>
      <version>3.4.6</version>
    </dependency>
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.46</version>
    </dependency>
  </dependencies>

对一个TbHotel表的CRUD操作

package com.kmyang.mybatis.beans;


public class TbHotel {

  private int hotelId;
  private String hotelName;
  private String hotelImage;
  private TbHotelType hotelType;


  public TbHotelType getHotelType() {
    return hotelType;
  }

  public void setHotelType(TbHotelType hotelType) {
    this.hotelType = hotelType;
  }

  public int getHotelId() {
    return hotelId;
  }

  public void setHotelId(int hotelId) {
    this.hotelId = hotelId;
  }


  public String getHotelName() {
    return hotelName;
  }

  public void setHotelName(String hotelName) {
    this.hotelName = hotelName;
  }


  public String getHotelImage() {
    return hotelImage;
  }

  public void setHotelImage(String hotelImage) {
    this.hotelImage = hotelImage;
  }

}

在Src目录下建一个mybatis的xml核心配置文件mybatis.cfg.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="jdbc.properties"/>
    <!--数据库配置,default表示默认采用哪一个数据库环境-->
    <environments default="development">
        <!--生产环境数据库-->
        <environment id="product">
            <!--使用JDBC事务管理，事务控制由mybatis-->
            <transactionManager type="JDBC"/>
            <!--连接池配置，type为POOLED采用连接池-->
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/test?useSSL=TRUE"/>
                <property name="username" value="root"/>
                <property name="password" value="admin1806"/>
            </dataSource>
        </environment>
        <!--开发环境数据库-->
        <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>
    <!--加载映射文件-->
    <mappers>
        <mapper resource="mapper/hotel.mapper.xml"/>
    </mappers>
</configuration>

采用development方式则需要配置jdbc.properties文件

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test?useSSL=TRUE
username=root
password=admin1806

定义hotel mappers的hotel.mapper.xml配置文件,配置文件实现了接口和SQL语句的映射关系。采用resultMap可以把属性和数据库列名映射关系定义好，property为类的属性，column是表的列名，也可以是表列名的别名

<?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">

<!--namespace 命名空间，类似包名，起到的作用作用跟其他文件中的配置隔离-->
<!--命名空间需要是唯一的-->
<!--规范：项目的Bean文件的包名+Bean类名+Mapper单词，比如：com.qianfeng.mybatis.beans.TbHotelMapper-->
<mapper namespace="com.kmyang.project.hotel.beans.TbHotelMapper">

    <!--结果集映射-->
    <!--id是唯一标识，type是要映射的Java对象的地址-->
    <resultMap id="hotelResultMap" type="com.kmyang.project.hotel.beans.TbHotel">
        <id column="hotel_id" property="hotelId" javaType="java.lang.Integer"/>
        <result column="hotel_img" property="hotelImg" javaType="java.lang.String"/>
        <result column="hotel_name" property="hotelName" javaType="java.lang.String"/>
        <result column="hotel_tel" property="hotelTel" javaType="java.lang.String"/>
        <result column="hotel_star" property="hotelStar" javaType="java.lang.String"/>
    </resultMap>

    <!--在当前namespace中id唯一-->
    <!--根据ID查询单个属性值-->
    <select id="queryHotelInfoById" resultType="java.lang.String">
        select hotel_name from tb_hotel where hotel_id = 1;
    </select>

    <!--在当前namespace中id唯一-->
    <!--查询所有酒店名称-->
    <select id="queryHotelNameList" resultType="java.lang.String">
        select hotel_name from tb_hotel;
    </select>

    <!--resultMap关联结果映射配置-->
    <!--根据Id查询该酒店的所有信息-->
    <select id="queryHotelById" resultMap="hotelResultMap">
        select hotel_id, hotel_name, hotel_tel, hotel_star, hotel_img from tb_hotel where hotel_id = 2;
    </select>

    <!--查询酒店信息总条数-->
    <select id="queryCount" resultType="java.lang.Integer">
        select count(hotel_id) from tb_hotel;
    </select>

    <!--酒店列表一页显示四条数据-->
    <select id="queryHotelByPage" resultMap="hotelResultMap">
        select hotel_id, hotel_tel, hotel_star, hotel_img, hotel_name from tb_hotel limit #{page}, #{pageSize};
    </select>

    <!--添加酒店相关信息-->
    <insert id="save">
        insert into tb_hotel(hotel_name, hotel_tel, hotel_star, hotel_img) value (#{hotelName}, #{hotelTel}, #{hotelStar}, #{hotelImg});
    </insert>

    <!--根据ID删除酒店信息-->
    <delete id="remove" parameterType="java.lang.String">
        delete from tb_hotel where hotel_id = 11;
    </delete>

    <!--根据酒店ID修改酒店信息-->
    <update id="update" parameterType="java.lang.String">
        update tb_hotel set hotel_name = '乔家大酒店' where hotel_id = 9;
    </update>
    
    <!--#与$的区别,#防止spl注入攻击,安全性更高-->
    <select id="checkHotelName" resultType="java.lang.String">
        select hotel_name from tb_hotel where hotel_id = ${id};
    </select>
    <!--<select id="checkHotelName" resultType="java.lang.String">-->
        <!--select hotel_name from tb_hotel where hotel_id = #{id};-->
    <!--</select>-->
</mapper>

这里直接开始测试

package com.kmyang.project.mybatis;

import com.kmyang.project.hotel.beans.TbHotel;
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.Test;

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

/**
 * @Auther: DELL
 * @Date: 2018/12/25 17:11
 * @Description: Mybatis相关功能测试
 */
public class MybatisTest {
    
    @Test
    /**
     * 功能描述: 根据ID查询单个属性值
     * @auther: kmyang
     * @date: 2018/12/25
     */
    public void testCaseOne(){
        try {
            InputStream inputStream = Resources.getResourceAsStream("mybatis.cfg.xml");
            //初始化MyBatis
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            SqlSession sqlSession = sqlSessionFactory.openSession();
            String hotelName = sqlSession.selectOne("com.kmyang.project.hotel.beans.TbHotelMapper.queryHotelInfoById");
            System.out.println(hotelName);
            sqlSession.close();
            inputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
   
    /**
     * 功能描述: 查询所有酒店名称
     * @auther: kmyang
     * @date: 2018/12/25 
     */
    @Test
    public void testCaseTwo(){
        try {
            InputStream inputStream = Resources.getResourceAsStream("mybatis.cfg.xml");
            //初始化mybatis
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            SqlSession sqlSession = sqlSessionFactory.openSession();
            List<Object> hotelNameList = sqlSession.selectList("com.kmyang.project.hotel.beans.TbHotelMapper.queryHotelNameList");
            System.out.println(hotelNameList);
            sqlSession.close();
            inputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 功能描述: 根据Id查询该酒店的所有信息
     * @auther: DELL
     * @date: 2018/12/25
     */
    @Test
    public void testCaseThree(){
        try {
            InputStream inputStream = Resources.getResourceAsStream("mybatis.cfg.xml");
            //初始化mybatis
            SqlSessionFactory build = new SqlSessionFactoryBuilder().build(inputStream);
            SqlSession session = build.openSession();
            TbHotel hotel = session.selectOne("com.kmyang.project.hotel.beans.TbHotelMapper.queryHotelById");
            System.out.println(hotel.toString());
            session.close();
            inputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 功能描述: 查询酒店信息总条数
     * @auther: kmyang
     * @date: 2018/12/25
     */
    @Test
    public void testCaseFour(){
        try {
            InputStream inputStream = Resources.getResourceAsStream("mybatis.cfg.xml");
            //初始化mybatis
            SqlSessionFactory build = new SqlSessionFactoryBuilder().build(inputStream);
            SqlSession sqlSession = build.openSession();
            Integer count = sqlSession.selectOne("com.kmyang.project.hotel.beans.TbHotelMapper.queryCount");
            System.out.println(count);
            sqlSession.close();
            inputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 功能描述: 酒店列表一页显示四条数据
     * @auther: kmyang
     * @date: 2018/12/25
     */
    @Test
    public void testCaseFive(){
        try {
            InputStream inputStream = Resources.getResourceAsStream("mybatis.cfg.xml");
            //初始化mybatis
            SqlSessionFactory build = new SqlSessionFactoryBuilder().build(inputStream);
            SqlSession sqlSession = build.openSession();
            //传参
            HashMap<String, Object> hashMap = new HashMap<String, Object>();
            //key需要和mybatis映射文件中的#(index)单词一致
            hashMap.put("page", 0);
            hashMap.put("pageSize", 4);
            List<TbHotel> hotels = sqlSession.selectList("com.kmyang.project.hotel.beans.TbHotelMapper.queryHotelByPage", hashMap);
            System.out.println(hotels);
            sqlSession.close();
            inputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 功能描述: 添加酒店相关信息
     * @auther: kmyang
     * @date: 2018/12/25
     */
    @Test
    public void testCaseSix(){
        try {
            InputStream inputStream = Resources.getResourceAsStream("mybatis.cfg.xml");
            //初始化mybatis
            SqlSessionFactory build = new SqlSessionFactoryBuilder().build(inputStream);
            SqlSession sqlSession = build.openSession();
            TbHotel hotel = new TbHotel();
            hotel.setHotelName("武汉大酒店");
            hotel.setHotelTel("13345621325");
            hotel.setHotelStar("三星级");
//            for (int i = 0; i < 5000000; i++) {
//                hotel.setHotelName("武汉大酒店");
//                hotel.setHotelTel("10" + i);
//                hotel.setHotelStar("三星级");
//                int a = sqlSession.insert("com.kmyang.project.hotel.beans.TbHotelMapper.save", hotel);
//            }
            //开启事务,mybatis已默认开启
            int i = sqlSession.insert("com.kmyang.project.hotel.beans.TbHotelMapper.save", hotel);
            //提交事务,需要手动提交
            sqlSession.commit();
//            System.out.println(i);
            sqlSession.close();
            inputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 功能描述: 根据ID删除酒店信息
     * @auther: kmyang
     * @date: 2018/12/26
     */
    @Test
    public void testCaseServen(){
        try {
            InputStream inputStream = Resources.getResourceAsStream("mybatis.cfg.xml");
            //初始化mybatis
            SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            SqlSession session = sessionFactory.openSession();
            int i = session.delete("com.kmyang.project.hotel.beans.TbHotelMapper.remove");
            session.commit();
            System.out.println(i);
            session.close();
            inputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 功能描述: 根据酒店ID修改酒店信息
     * @auther: kmyang
     * @date: 2018/12/26
     */
    @Test
    public void testCaseEight(){
        try {
            InputStream inputStream = Resources.getResourceAsStream("mybatis.cfg.xml");
            //初始化mybatis
            SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            SqlSession session = sessionFactory.openSession();
            int i = session.update("com.kmyang.project.hotel.beans.TbHotelMapper.update");
            System.out.println(i);
            session.commit();
            session.close();
            inputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    /**
     * 功能描述: #与$的区别,#防止spl注入攻击,安全性更高
     * @auther: kmyang
     * @date: 2018/12/25
     */
    @Test
    public void testCaseNine(){
        try {
            InputStream inputStream = Resources.getResourceAsStream("mybatis.cfg.xml");
            //初始化mybatis
            SqlSessionFactory build = new SqlSessionFactoryBuilder().build(inputStream);
            SqlSession sqlSession = build.openSession();
            HashMap<String, Object> hashMap = new HashMap<String, Object>();
            hashMap.put("id", "111 or 1=1");
            List<Object> list = sqlSession.selectList("com.kmyang.project.hotel.beans.TbHotelMapper.checkHotelName", hashMap);
            System.out.println(list);
            sqlSession.close();
            inputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}