Mybatis

官网： http://www.mybatis.org/mybatis-3/zh/index.html

MybatisDemo

mybatis使用时需要目标表都有主键，否则会出现读取数据错乱的现象。

1.jar包：mybatis-3.4.4.jar和mysql-connector-java-8.0.15.jar

2.创建libs，加入jar包

3.创建实体类Students，设定基本的属性

4.创建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>
    <!-- 配置全局属性 -->
    <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://127.0.0.1:3306/study01?serverTimezone=UTC"/>
                <property name="username" value="root"/>
                <property name="password" value="zxcvbnm"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <!--注意此处注册目录名称中间用/-->
        <mapper resource="com/java/mapper.xml"/>
    </mappers>

</configuration>

<?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>
    <!-- 配置全局属性 -->
    <settings>
        <!-- 使用jdbc的getGeneratedKeys获取数据库自增主键值 -->
        <setting name="useGeneratedKeys" value="true" />

        <!-- 使用列别名替换列名（使用列标签替换列别名） 默认:true -->
        <setting name="useColumnLabel" value="true" />

        <!-- 开启驼峰命名转换:Table{create_time} -> Entity{createTime} -->
        <setting name="mapUnderscoreToCamelCase" value="true" />
    </settings>

</configuration>

5.创建mapper.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.java.GetStudent">

<!--方式1-->
    <resultMap type="com.java.Students" id="studentResultMap" >
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="age" column="age"/>
    </resultMap>
    <select id="getStudent" resultMap="studentResultMap" parameterType="int">
        select id,name,age from students where id =#{id};
    </select>

 <!--方式2-->
<!--id 定义要实现的与namespace对应的接口中的方法名;resultType对应与具体的类对象（Students）-->
<!--    <select id="getStudent" resultType="com.java.Students" parameterType="int">
    select * from students where id = #{id}
    </select>-->

</mapper>

6.创建GetStudent接口，并定义响应的方法

package com.java;
//对应Dao层
public interface GetStudent {
    public Students getStudent(int id);
}

7.创建测试类

package com.java;

import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.InputStream;

//测试类
public class MybatisDemoTest {
    public static void main(String [] args){
        //1.声明配置文件的目录位置
        String resource = "com/java/mybatis-config.xml";
        //2.加载应用配置文件
        InputStream inputStream = MybatisDemoTest.class.getClassLoader().getResourceAsStream(resource);
        //3.创建SqlSessionFactory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        //4.获取Session
        SqlSession session = sqlSessionFactory.openSession(true);//设置为true，使mabatis默认使用事务，从而不用显式的调用commit

        try{
            //5.获取操作类
            GetStudent getStudent = session.getMapper(GetStudent.class);
            //6.完成查询操作
            Students students = getStudent.getStudent(1);
            System.out.println(students.getId()+"  name: "+students.getName()+"  age:"+students.getAge());
        }catch(Exception e){
            e.printStackTrace();
        }
        finally {
            session.close();
        }
    }
}

目录结构:

使用注解代替创建mapper.xml：

新建GetStudentAnnotation接口类：

package com.java;

import org.apache.ibatis.annotations.Select;

public interface GetStudentAnnotation {
    @Select("select * from user where id = #{id}")
    public Students getStudent(int id);
}

创建测试类

package com.java;

import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.InputStream;

public class MybatisDemoTest2 {
    public static void main(String [] args){
        //1.声明配置文件的目录位置
        String resource = "com/java/mybatis-config.xml";
        //2.加载应用配置文件
        InputStream inputStream = MybatisDemoTest.class.getClassLoader().getResourceAsStream(resource);
        //3.创建SqlSessionFactory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        //********************不同的地方
        Configuration configuration = sqlSessionFactory.getConfiguration();
        configuration.addMapper(GetStudentAnnotation.class);
        //********************
        //4.获取Session
        SqlSession session = sqlSessionFactory.openSession();

        try{
            //5.获取操作类
            GetStudentAnnotation getStudent =  session.getMapper(GetStudentAnnotation.class);
            //6.完成查询操作
            Students students = getStudent.getStudent(1);
            System.out.println(students.getId()+"  name: "+students.getName()+"  age:"+students.getAge());
        }catch(Exception e){
            e.printStackTrace();
        }
        finally {
            session.close();
        }
    }
}

复杂表之间的关联：

collection容器:

待补充

association关联：

待补充

mybatis支持动态sql语句：

项目：E:\project\schoolStoreSystem\o2o

mapper文件：

<!--<if test="xxx !=null"> xxx_xxx=#{xxx},</if>-->/*xxx是实体类(shop)中的成员变量名xxx_xxx是数据库表字段名*/

if标签里的字段是定义可能会被修改的字段，而像shop_id这种字段则不能被用户更新，则不会出现在if标签中。

<?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.schoolStoreSystem.o2o.dao.ShopDao">
    <!--        <resultMap id="shopResultMap" type="Shop">
                <id property="areaId" column="area_id"/>
                <result property="areaName" column="area_name"/>
                <result property="priority" column="priority"/>
                <result property="createTime" column="create_time"/>
                <result property="updateTime" column="last_edit_time"/>
            </resultMap>-->
    <!-- useGeneratedKeys="true",插入成功以后返回自增主键的值-->

    <update id="updateShop" parameterType="com.schoolStoreSystem.o2o.entity.Shop">
        <!--<if test="xxx !=null"> xxx_xxx=#{xxx},</if>-->/*xxx是实体类(shop)中的成员变量名xxx_xxx是数据库表字段名*/
        update tb_shop
        <set>
            <if test="shopName !=null">shop_name=#{shopName},</if>
            <if test="shopDesc !=null">shop_desc=#{shopDesc},</if>
            <if test="shopAddr !=null">shop_addr=#{shopAddr},</if>
            <if test="phone !=null">phone=#{phone},</if>
            <if test="shopImg =null">shop_img=#{shopImg},</if>
            <if test="priority !=null">priority=#{priority},</if>
            <if test="lastEditTime !=null">last_edit_time=#{lastEditTime},</if>
            <if test="enableStatus !=null">enable_status=#{enableStatus},</if>
            <if test="advice !=null">advice=#{advice},</if>
            <if test="area_id !=null">area_id=#{area_id.areaId},</if>
            <if test="shopCategoryId !=null">shop_category_id=#{shopCategoryId.shopCategoryId}</if>
        </set>
        where shop_id=#{shopId}
    </update>
</mapper>

mybatis中mapper语句编写

在插入数据后返回自增的主键id值

第一种：

o2o项目：ShopDaoMapper.xml

<!-- useGeneratedKeys="true",插入成功以后返回自增主键的值-->
<insert id="insertShop" useGeneratedKeys="true" keyColumn="shop_id" keyProperty="shopId">
    insert into tb_shop(owner_id, area_id, shop_category_id, shop_name, shop_desc, shop_addr, phone, shop_img,
                        priority, create_time, last_edit_time, enable_status, advice)
    values (#{owner.userId}, #{area_id.areaId}, #{shopCategoryId.shopCategoryId}, #{shopName}, #{shopDesc},
            #{shopAddr}, #{phone}, #{shopImg}, #{priority}, #{createTime}, #{lastEditTime}, #{enableStatus},
            #{advice})
</insert>

第二种：

taotao：TbcontentMapper.xml

利用mysql中的函数：select last_insert_id()。

/*mapper中用以代替下面的mysql语句*/ 
 <selectKey keyProperty="id" order="AFTER" resultType="long">
    select last_insert_id()
  </selectKey>

/*mysql语句*/
SELECT LAST_INSERT_ID()

/*mapper语句*/
<insert id="insert" parameterType="com.taotao.taotaomanager.taotaomanagerdto.TbContent" >
  <selectKey keyProperty="id" order="AFTER" resultType="long">
    select last_insert_id()
  </selectKey>
  insert into tb_content (id, category_id, title, 
    sub_title, title_desc, url, 
    pic, pic2, created, 
    updated, content)
  values (#{id,jdbcType=BIGINT}, #{categoryId,jdbcType=BIGINT}, #{title,jdbcType=VARCHAR}, 
    #{subTitle,jdbcType=VARCHAR}, #{titleDesc,jdbcType=VARCHAR}, #{url,jdbcType=VARCHAR}, 
    #{pic,jdbcType=VARCHAR}, #{pic2,jdbcType=VARCHAR}, #{created,jdbcType=TIMESTAMP}, 
    #{updated,jdbcType=TIMESTAMP}, #{content,jdbcType=LONGVARCHAR})
</insert>

Mybatis之o2o笔记mybatis中sql语句

使用 @Param注释，实现dao层有条件的查询

dao层文件：

 public List<ShopCategory> queryShopCategory( @Param("shopCategoryCondition") ShopCategory shopCategoryCondition);

package com.schoolStoreSystem.o2o.dao;

import com.schoolStoreSystem.o2o.entity.ShopCategory;
import org.apache.ibatis.annotations.Param;

import java.util.List;


public interface ShopCategoryDao {
    //@Param("shopCategoryCondition")  传入参数，在执行数据库select操作时传入条件
    public List<ShopCategory> queryShopCategory( @Param("shopCategoryCondition") ShopCategory shopCategoryCondition);
}

mapper文件

<where>
            <if test="shopCategoryCondition.parent!=null">
             and parent_id = #{shopCategoryCondition.parent.shopCategoryId}
            </if>
        </where>

<?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.schoolStoreSystem.o2o.dao.ShopCategoryDao">
<!--    <resultMap id="shopCategoryResultMap" type="ShopCategory">
        <id property="shopCategoryId" column="shop_category_id"/>
        <result property="shopCategoryName" column="shop_category_name"/>
        <result property="shopCategoryDesc" column="shop_category_desc"/>
        <result property="priority" column="priority"/>
        <result property="createTime" column="create_time"/>
        <result property="lastEditTime" column="last_edit_time"/>
    </resultMap>-->

    <select id="queryShopCategory" resultType="com.schoolStoreSystem.o2o.entity.ShopCategory" parameterType="com.schoolStoreSystem.o2o.entity.ShopCategory" >
        select
            shop_category_id,
            shop_category_name,
            shop_category_desc,
            priority,
            create_time,
            last_edit_time
        from
             tb_shop_category
        <where>
            <if test="shopCategoryCondition.parent!=null">
             and parent_id = #{shopCategoryCondition.parent.shopCategoryId}
            </if>
        </where>
order by priority desc
    </select>
</mapper>

测试文件：

package com.schoolStoreSystem.o2o.dao;

import com.schoolStoreSystem.o2o.BaseTest;
import com.schoolStoreSystem.o2o.entity.ShopCategory;
import org.junit.Test;
import org.springframework.beans.factory.annotation.Autowired;

import java.util.List;

import static org.junit.Assert.assertEquals;

public class ShopCategoryDaoTest extends BaseTest {
    @Autowired
    private ShopCategoryDao shopCategoryDao;

    @Test
    public void queryShopCategoryTest(){
        //1测试没有条件时的查询
        ShopCategory shopCategory = new ShopCategory();
        List<ShopCategory> shopCategoryList = shopCategoryDao.queryShopCategory(new ShopCategory());
        assertEquals(2,shopCategoryList.size());
        //2测试有条件的查询
        ShopCategory parentShopCategory = new ShopCategory();//创建父shopCategory
        parentShopCategory.setShopCategoryId(2L);

        shopCategory.setParent(parentShopCategory);//创建带条件的shopCategory
        List<ShopCategory> shopCategoryList2 = shopCategoryDao.queryShopCategory(shopCategory);
        assertEquals(1,shopCategoryList2.size());
        System.out.println(shopCategoryList2.get(0).getShopCategoryDesc());

    }
}

java对象转为数据库数据，即在mapper文件中使用 #{areaId}获得java的成员变量值

dao层文件：

package com.schoolStoreSystem.o2o.dao;

import com.schoolStoreSystem.o2o.entity.Shop;

public interface ShopDao {

    //新增店铺
    int insertShop(Shop shop);

    //更新店铺
    int updateShop(Shop shop);

}

mapper文件：

<?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.schoolStoreSystem.o2o.dao.ShopDao">
    <!--        <resultMap id="shopResultMap" type="Shop">
                <id property="areaId" column="area_id"/>
                <result property="areaName" column="area_name"/>
                <result property="priority" column="priority"/>
                <result property="createTime" column="create_time"/>
                <result property="updateTime" column="last_edit_time"/>
            </resultMap>-->
    <!-- useGeneratedKeys="true",插入成功以后返回自增主键的值-->
    <insert id="insertShop" useGeneratedKeys="true" keyColumn="shop_id" keyProperty="shopId">
        insert into tb_shop(owner_id, area_id, shop_category_id, shop_name, shop_desc, shop_addr, phone, shop_img,
                            priority, create_time, last_edit_time, enable_status, advice)
        values (#{owner.userId}, #{area_id.areaId}, #{shopCategoryId.shopCategoryId}, #{shopName}, #{shopDesc},
                #{shopAddr}, #{phone}, #{shopImg}, #{priority}, #{createTime}, #{lastEditTime}, #{enableStatus},
                #{advice})
    </insert>

    <update id="updateShop" parameterType="com.schoolStoreSystem.o2o.entity.Shop">
        <!--<if test="xxx !=null"> xxx_xxx=#{xxx},</if>-->/*xxx是实体类(shop)中的成员变量名xxx_xxx是数据库表字段名*/
        update tb_shop
        <set>
            <if test="shopName !=null">shop_name=#{shopName},</if>
            <if test="shopDesc !=null">shop_desc=#{shopDesc},</if>
            <if test="shopAddr !=null">shop_addr=#{shopAddr},</if>
            <if test="phone !=null">phone=#{phone},</if>
            <if test="shopImg !=null">shop_img=#{shopImg},</if>
            <if test="priority !=null">priority=#{priority},</if>
            <if test="lastEditTime !=null">last_edit_time=#{lastEditTime},</if>
            <if test="enableStatus !=null">enable_status=#{enableStatus},</if>
            <if test="advice !=null">advice=#{advice},</if>
            <if test="area_id !=null">area_id=#{area_id.areaId},</if>
            <if test="shopCategoryId !=null">shop_category_id=#{shopCategoryId.shopCategoryId}</if>
        </set>
        where shop_id=#{shopId}
    </update>
</mapper>

测试文件：

package com.schoolStoreSystem.o2o.dao;

import com.schoolStoreSystem.o2o.BaseTest;
import com.schoolStoreSystem.o2o.entity.Area;
import com.schoolStoreSystem.o2o.entity.PersonInfo;
import com.schoolStoreSystem.o2o.entity.Shop;
import com.schoolStoreSystem.o2o.entity.ShopCategory;
import org.junit.Ignore;
import org.junit.Test;
import org.springframework.beans.factory.annotation.Autowired;

import java.util.Date;

import static org.junit.Assert.assertEquals;

public class ShopDaoTest extends BaseTest {
    @Autowired
    private ShopDao shopDao;
    @Test
    @Ignore
    public void insertShopTest(){
        Shop shop = new Shop();
        PersonInfo personInfo = new PersonInfo();
        ShopCategory shopCategory = new ShopCategory();
        Area area = new Area("北京",3,3);
        personInfo.setUserId(1L);
        shopCategory.setShopCategoryId(1L);
        shop.setArea_id(area);
        shop.setAdvice("test3");
        shop.setCreateTime(new Date());
        shop.setEnableStatus(1);
        shop.setOwner(personInfo);
        shop.setPhone("test3");
        shop.setPriority(1);
        shop.setShopAddr("test3");
        shop.setShopDesc("test3");
        shop.setShopName("test3");
        shop.setShopCategoryId(shopCategory);
        int effectedNum = shopDao.insertShop(shop);
        assertEquals(1,effectedNum);
    }
    @Test
    public void updateShopTest(){
        Shop shop = new Shop();
        shop.setShopId(1L);
        shop.setShopAddr("updateTest");
        shop.setShopDesc("updateTest");
        shop.setLastEditTime(new Date());
        int effectedNum = shopDao.updateShop(shop);
        assertEquals(1,effectedNum);
    }
}

经测试，上面两种方法都可以实现从数据库查询到shopCategory。

sql语句实现批量插入数据（foreach、list）

首次传入列表参数

在mybatis中使用foreach语句，循环遍历列表参数

<insert id="batchInsertProductCategory" parameterType="java.util.List">
        insert into
          tb_product_category(product_category_name,priority,create_time,shop_id)
          values
            <foreach collection="list" item="productCategory" index="index" separator=",">
                (<!--以“,”分隔-->
                #{productCategory.productCategoryName},
                #{productCategory.priority},
                #{productCategory.createTime},
                #{productCategory.shopId}
                )
            </foreach>
    </insert>

dao层：

package com.schoolStoreSystem.o2o.dao;

import com.schoolStoreSystem.o2o.entity.ProductCategory;

import java.util.List;

public interface ProductCategoryDao {
    List<ProductCategory> listProductCategory(Long shopId);

    /*
    * 批量新增商品类别
    * */
    int batchInsertProductCategory(List<ProductCategory> productCategoryList);
}

mapper层：

<?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.schoolStoreSystem.o2o.dao.ProductCategoryDao">
    <resultMap id="productCategoryResultMap" type="ProductCategory">
        <id property="productCategoryId" column="product_category_id"/>
        <result property="shopId" column="shop_id"/>
        <result property="productCategoryName" column="product_category_name"/>
        <result property="priority" column="priority"/>
        <result property="createTime" column="create_time"/>
    </resultMap>
    
    <insert id="batchInsertProductCategory" parameterType="java.util.List">
        insert into
          tb_product_category(product_category_name,priority,create_time,shop_id)
          values
            <foreach collection="list" item="productCategory" index="index" separator=",">
                (
                #{productCategory.productCategoryName},
                #{productCategory.priority},
                #{productCategory.createTime},
                #{productCategory.shopId}
                )
            </foreach>
    </insert>
</mapper>

测试：

package com.schoolStoreSystem.o2o.dao;

import com.schoolStoreSystem.o2o.BaseTest;
import com.schoolStoreSystem.o2o.entity.ProductCategory;
import org.junit.Ignore;
import org.junit.Test;
import org.springframework.beans.factory.annotation.Autowired;

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

public class ProductCategoryDaoTest extends BaseTest {
    @Autowired
    private ProductCategoryDao productCategoryDao
    @Test
    public void batchProductCategoryTest(){
        List<ProductCategory> products = new ArrayList<>();
        ProductCategory productCategory1 = new ProductCategory(4L,2L,"测试2",0,new Date());
        ProductCategory productCategory2 = new ProductCategory(5L,2L,"测试2",0,new Date());
        ProductCategory productCategory3 = new ProductCategory(6L,2L,"测试2",0,new Date());
        products.add(productCategory1);
        products.add(productCategory2);
        products.add(productCategory3);
        int success = productCategoryDao.batchInsertProductCategory(products);
        System.out.println(success);
    }
}

if标签中使用大于(&gt)小于(&lt)等操作符时需要使用转义符

<if test="beginTime!=null">
	and psd.end_time &lt;=#{endTime};
</if>

测试