【MyBatis】2、MyBatis 的动态 SQL 和增删改操作

目录

一、添加

(1) 基本插入

<mappernamespace="student"><insertid="insert"parameterType="com.pojo.po.Student">
        INSERT INTO student(name, money) VALUES (#{name}, #{money})
    </insert></mapper>

publicclassTestStudent{@TestpublicvoidtestInsert(){try(SqlSession sqlSession =MyBatisUtil.openSession(true)){Student student =newStudent();
            student.setName("鹿晗");
            student.setMoney(100L);
            sqlSession.insert("student.insert", student);}}}

注意：

openSession()

的参数默认值是 false（不自动提交事务）

(2) 设置新插入记录的主键（id）★

🌼 设置新插入记录的主键（id）到参数对象中

<mappernamespace="student"><insertid="insert2"parameterType="com.pojo.po.Student">
        INSERT INTO student(name, money) VALUES (#{name}, #{money})

        <!-- resultType 需要和参数对象的主键的属性名的类型一样 --><!-- keyProperty 是属性名, 不是字段名 --><!-- order="AFTER" 插入语句执行完毕后才查询 --><selectKeyresultType="long"keyProperty="id"order="AFTER">
            SELECT LAST_INSERT_ID()
        </selectKey></insert></mapper>

二、更新

<mappernamespace="student"><updateid="update"parameterType="com.pojo.po.Student">
        UPDATE student SET money = #{money} WHERE name = #{name}
    </update></mapper>

三、删除

<mappernamespace="student"><deleteid="delete">
        DELETE FROM student WHERE id in ${ids}
    </delete></mapper>

publicclassTestStudent{@TestpublicvoidtestDelete(){try(SqlSession sqlSession =MyBatisUtil.openSession(true)){String ids ="(4, 9, 6)";

            sqlSession.insert("student.delete", ids);}}}

四、动态 SQL

动态 SQL 官方文档：https://mybatis.org/mybatis-3/zh/dynamic-sql.html

(1) if 标签

<mappernamespace="student"><selectid="dynamicSQL"resultType="com.pojo.po.Student">
        SELECT
        *
        FROM
        student
        WHERE 1 = 1
        <iftest="id != null and id > 0">
            AND id > #{id}
        </if><iftest="money != null">
            AND money > #{money}
        </if><iftest="name != null">
            AND NAME LIKE #{name}
        </if></select></mapper>

publicclassTestStudent{@TestpublicvoiddynamicSql(){try(SqlSession sqlSession =MyBatisUtil.openSession(true)){HashMap<String,Object> pMap =newHashMap<>();
            pMap.put("id",3);
            pMap.put("name","%杰%");// pMap.put("money", 5555);List<Student> list = sqlSession.selectList("student.dynamicSQL", pMap);for(Student student : list){System.out.println("dynamicSql student = "+ student);}}}}

(2) where 标签

<mappernamespace="student"><selectid="dynamicSQL"resultType="com.pojo.po.Student">
        SELECT
        *
        FROM
        student
        <where><iftest="id != null and id > 0">
                id > #{id}
            </if><iftest="money != null">
                AND money > #{money}
            </if><iftest="name != null">
                AND NAME LIKE #{name}
            </if></where></select></mapper>

(3) foreach 标签

☆ 批量插入：

<mappernamespace="student"><insertid="batchInsert1"parameterType="List">
        INSERT INTO student (name, money) VALUES
        <foreachcollection="list"item="item"separator=",">
            (#{item.name}, #{item.money})
        </foreach></insert></mapper>

💦 批量添加的执行效率比多次单个添加的执行效率要高，但是无法获取到新插入的记录的主键
💦 可以使用 useGeneratedKeys 获取新插入的记录的主键。
💦 假如要添加的记录的字段特别多， 批量添加操作生成的 SQL 语句字符串可能会特别长，SQL 语句的长度可能会超过数据库的限制 【分批插入】
💦 如果传进来的参数是

List

类型，collection 的属性值为

list

就可以遍历这个

List

💦 如果传进来的参数是数组，collection 的属性值为

array

就可以遍历这个数组

☆ 批量删除：

<mappernamespace="student"><deleteid="batchDelete1"parameterType="List">
        DELETE FROM student
        <where>
            id IN (
                <foreachcollection="list"separator=","item="item">
                    #{item}
                </foreach>
            )
        </where></delete></mapper>

<mappernamespace="student"><deleteid="batchDelete2">
        DELETE FROM student
        <where>
            id IN
            <foreachcollection="array"separator=","open="("close=")"item="item">
                #{item}
            </foreach></where></delete></mapper>

五、起别名

●

<typeAliases>

标签写在

mybatis-config.xml

核心配置文件的configuration 标签中
● 写在

<settings>

标签的后面
● 用于设置类型的别名（不区分大小写）

六、sql 标签

<mappernamespace="student"><!-- 有抽取公共 SQL 语句的作用 --><sqlid="sqlListAll">
        SELECT * FROM student
    </sql><resultMapid="resultMapStudent"type="com.pojo.po.Student"><idproperty="id"column="id"/><resultproperty="createTime"column="create_time"/></resultMap><selectid="list"resultMap="resultMapStudent"><includerefid="sqlListAll"/></select><selectid="getById"resultType="com.pojo.po.Student"><includerefid="sqlListAll"/>
        WHERE id = #{id}
    </select><selectid="listByIdAndMoney"resultType="com.pojo.po.Student"><includerefid="sqlListAll"/>
        WHERE id &lt; #{id} OR money >= #{money}
    </select><selectid="getByName"resultType="com.pojo.po.Student"><includerefid="sqlListAll"/>
        WHERE name LIKE #{name}
    </select><selectid="dynamicSQL"resultType="com.pojo.po.Student"><includerefid="sqlListAll"/><where><iftest="id != null and id > 0">
                id > #{id}
            </if><iftest="money != null">
                AND money > #{money}
            </if><iftest="name != null">
                AND NAME LIKE #{name}
            </if></where></select></mapper>

七、在 MyBatis 中集成 druid 连接池

① 引入 Maven 依赖

<dependency><groupId>com.alibaba</groupId><artifactId>druid</artifactId><version>1.1.2</version></dependency>

② 创建连接池工厂类并继承

PooledDataSourceFactory

或

UnpooledDataSourceFactory

；在连接池工厂类的构造方法中设置数据源 dataSource 为 Druid 的数据源实例

/**
 * MyBatis 中集成 Druid 数据库连接池
 */publicclassDruidDataSourceFactoryextendsPooledDataSourceFactory{publicDruidDataSourceFactory(){this.dataSource =newDruidDataSource();}}

③ 在 mybatis-config.xml 文件中配置 Druid 数据源

<environmentsdefault="development"><environmentid="development"><transactionManagertype="JDBC"/><dataSourcetype="com.common.DruidDataSourceFactory"><propertyname="driverClassName"value="com.mysql.jdbc.Driver"/><propertyname="url"value="jdbc:mysql://localhost:3306/study_mb?useUnicode=true&amp;characterEncoding=utf8"/><propertyname="username"value="root"/><propertyname="password"value="root"/><propertyname="initialSize"value="5"/><propertyname="maxActive"value="10"/><propertyname="maxWait"value="5000"/></dataSource></environment></environments>

把 druid 数据库连接池的配置放在 druid.properties 文件中

dev.driverClassName=com.mysql.jdbc.Driver
dev.url=jdbc:mysql://localhost:3306/study_mb?useUnicode=true&characterEncoding=utf8
dev.username=root
dev.password=root
dev.initialSize=5
dev.maxActive=10
dev.maxWait=5000

<environmentid="development"><!--采用JDBC的事务管理方法--><transactionManagertype="JDBC"/><!--采取druid数据库连接池管理连接--><dataSourcetype="com.common.DruidDataSourceFactory"><propertyname="driverClassName"value="${dev.driverClassName}"/><propertyname="url"value="${dev.url}"/><propertyname="username"value="${dev.username}"/><propertyname="password"value="${dev.password}"/><propertyname="initialSize"value="${dev.initialSize}"/><propertyname="maxActive"value="${dev.maxActive}"/><propertyname="maxWait"value="${dev.maxWait}"/></dataSource></environment>