Java后端05(初识MyBatis)

Mybatis

举个小栗子

mybatis配置文件(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">
<!--通过这个配置文件,完成mybatis与数据库的连接  -->
<configuration>
<!--mybatis 在实例化的时候,会自动扫描这个包下的所有类,用于后续 sql 语句的 resultType-->
    <typeAliases>
        <package name="com.iweb.entity"/>
    </typeAliases>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="url" value="jdbc:mysql://localhost:3306/iweb?characterEncoding=utf-8"/>
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="mapper/user.xml"/>
    </mappers>
</configuration>

user.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.iweb.entity">
    <select id="listUser" resultType="User">select * from user</select>
<!--    parameterType:表示传入的参数类型,可以是基本数据类型,也可以是引用类型
        基本类型中需要注意:如果传入的参数为整型,参数“_int” 表示 int 类型,参数“int” 表示 Integer 类型-->
    <insert id="addUser" parameterType="User">insert into user values (#{userId},#{username},#{password})</insert>
    <delete id="deleteUser" parameterType="String">delete from user where userId = #{userId}</delete>
    <update id="updateUser" parameterType="User">update user set username = #{username},password = #{password} where userId = #{userId}</update>
    <select id="getUser" parameterType="String" resultType="User">select * from user where userId = #{userId}</select>
<!--    模糊查询-->
    <select id="listUserByNameLike" parameterType="String" resultType="User">select * from user where username like concat('%',#{0},'%')</select>
<!--    复杂查询-->
    <select id="listUserByIdAndNameLike" parameterType="map" resultType="User">select * from user where userId > #{userId} and username like concat('%',#{username},'%')</select>
</mapper>

使用做sql查询(Test)

public void test1() throws IOException {
    private SqlSession sqlSession;
    @Before
    public void init() throws IOException {
        // 输入流读取配置文件信息
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        // 基于配置文件获取 mybatis 的一级缓存对象
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        // 基于这个一级缓存,创建一个二级缓存
        sqlSession = sqlSessionFactory.openSession();
    }

    @Test
    public void test01(){
        // 使用二级缓存实现sql语句调用
        List<User> userList = sqlSession.selectList("listUser");
        // 遍历集合
        for (User user : userList) {
            System.out.println(user);
        }
    }

    @Test
    public void test02(){
        User user = new User("3","robot01","123456");
        sqlSession.insert("addUser",user);
        // mybatis 需要手动提交缓存
        sqlSession.commit();
        test01();
    }

    @Test
    public void test03(){
        User user = new User();
        user.setUserId("3");
        sqlSession.delete("deleteUser",user);
        sqlSession.commit();
        test01();
    }

    @Test
    public void test04(){
        User user = sqlSession.selectOne("getUser","2");
        System.out.println(user);
    }

    @Test
    public void test05(){
        User user = new User("2","HSS","123456");
        sqlSession.update("updateUser",user);
        sqlSession.commit();
        test01();
    }

    @Test
    public void test06(){
        List<User> users = sqlSession.selectList("listUserByNameLike","ss");
        System.out.println(users);
    }

    @Test
    public void test07(){
        Map<String,Object> params = new HashMap<>();
        params.put("userId",2);
        params.put("username","ss");
        List<User> users = sqlSession.selectList("listUserByIdAndNameLike",params);
        System.out.println(users);
    }
}

一对多关系查询

配置文件(⭐注意:每一个配置文件都需要在 mybatis-config.xml 中进行注册!!!!!!!!!)

<resultMap id="productBean" type="Product">
    <id column="productId" property="productId"/>
    <result column="productName" property="productName"/>
    <result column="price" property="price"/>
    <result column="stock" property="stock"/>
    <association property="user" javaType="User">
        <id column="uId" property="userId"/>
        <result column="username" property="username"/>
    </association>
</resultMap>
<!--    多对一关系查询-->
<select id="listProduct" resultMap="productBean">
    select productName,productId,price,stock,u.userId 'uId',username from product left join user u on product.userId = u.userId
</select>

测试类

public class TestMybatis {
    private SqlSession sqlSession;
    @Before
    public void init() throws IOException {
        // 输入流读取配置文件信息
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        // 基于配置文件获取 mybatis 的一级缓存对象
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        // 基于这个一级缓存,创建一个二级缓存
        sqlSession = sqlSessionFactory.openSession();
    }

    @Test
    public void test01(){
        List<Product> productList = sqlSession.selectList("listProduct");
        for (Product product : productList) {
            System.out.println(product);
        }
    }
}

动态sql查询

配置文件(⭐注意:每一个配置文件都需要在 mybatis-config.xml 中进行注册!!!!!!!!!)

<select id="listProduct" resultType="product">
    select * from product
    <if test="productName != null">
        where productName like concat('%',#{productName},'%')
    </if>
</select>

测试类

@Test
public void test01(){
    List<Product> productList = sqlSession.selectList("listProduct");
    for (Product product : productList) {
        System.out.println(product);
    }
}

@Test
public void test02(){
    Map<String,String> params = new HashMap<>();
    params.put("productName","1");
    List<Product> productList = sqlSession.selectList("listProduct",params);
    for (Product product : productList) {
        System.out.println(product);
    }
}

多条件查询

<!--多条件查询的矛盾-->
<!--    where 标签会进行自动判断,如果所有的 if 条件都不成立,那么 sql 语句中不会出现 where 关键字
        只要有一个条件成立,就会自动去除冗余的 and,并自动添加 where-->
<select id="listProduct" resultType="Product">
    select * from product
    <where>
        <if test="productName != null">
            and productName like concat('%',#{productName},'%')
        </if>
        <if test="price != 0">
            and price > #{price}
        </if>
    </where>
</select>

测试类

@Test
public void test01(){
    Map<String,Object> params = new HashMap<>();
    params.put("productName","1");
    params.put("price",0);
    List<Product> productList = sqlSession.selectList("listProduct",params);
    for (Product product : productList) {
        System.out.println(product);
    }
}

动态sql更新

<update id="updateProduct">
    update product
    <set>
        <if test="productName != null">
            productName = #{productName},
        </if>
        <if test="price != null">
            price = #{price},
        </if>
        <if test="stock != null">
            stock = #{stock},
        </if>
    </set>
    where productId = #{productId}
</update>

测试类

@Test
public void test01(){
    Map<String,Object> params = new HashMap<>();
    params.put("productId","1");
    params.put("productName","product");
    params.put("price","10086");
    params.put("stock","10000");
    sqlSession.update("updateProduct",params);
    sqlSession.commit();
}

when otherwise 标签

<mapper namespace="com.iweb.entity">
<!--    mybatis 没有 else 标签,只能使用 when otherwise 表示 else,如果提供了任何条件,则进行条件铲鲟,如果没有提供任何条件参数,则使用 otherwise 作为条件-->
    <select id="listProduct" resultType="Product">
        select * from product
        <where>
            <choose>
                <when test="productName != null">
                    and name like concat('%',#{productName},'%')
                </when>
                <when test="price != null">
                    and price > #{price}
                </when>
                <otherwise>
                    and id > 5
                </otherwise>
            </choose>
        </where>
    </select>
</mapper>

使用注解方式实现简单的sql

mapper接口文件(一定要注册!!!!!!)

/**
 * 通过接口(底层是java的JDK动态代理)实现 mybatis 调用
 * 开发人员只需要关心接口,实现类由 mybatis 动态生成
 * 1. 注解开发方式: 适用于简单场景(注解场景下编写一对多 多对一 或者是动态sql非常麻烦)
 * 2. xml配置文件开发方式:适用于所有场景(推荐)
 * @author te9uila
 * @since 2023/8/5
 */
public interface ProductMapper {
    @Insert("insert into product values (#{productId},#{productName},#{price},#{stock},#{userId})")
    void add(Product product);
    @Delete("delete from product where productId = #{productId}")
    void delete(String id);
    @Select("select * from product where productId = #{productId}")
    Product get(String id);
    @Update("update product set productName = #{productName},price = #{price},stock = #{stock} where productId = #{productId}")
    int update(Product product);
    @Select("select * from product")
    List<Product> list();
}

测试类

@Test
public void test01(){
    ProductMapper productMapper = sqlSession.getMapper(ProductMapper.class);
    List<Product> productList = productMapper.list();
    System.out.println(productList);
}

热门相关:有个人爱你很久   拒嫁豪门,前妻太抢手   今天也没变成玩偶呢   未来兽世:买来的媳妇,不生崽   后福