勇闯博客世界之我的Java大冒险之善良的小动态sql

//动态sql包括批量增加，批量减少，条件查询

//第一课

 <select id="queryById" resultType ="Employee">
        select * from employee where id=#{id}
    </select>`

//一般不用*，想查询具体字段，应该用哪个字段写哪个字段

//在navicat中，查询sql语句//在navicat中，查询sql语句

CREATE TABLE `employee` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `sex` tinyint(4) DEFAULT NULL,
  `sal` double DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

//定义具体字段

<sql id="emp_cols">
        id, name, sal as salary, sex
    </sql>
       .
       .
       .
 //添加具体字段
<select id="queryById" resultType ="Employee">
        select  <include refid="emp_cols"/>  from employee where id=#{id}
</select>
<select id="queryAll" resultMap ="Employee">
        select  <include refid="emp_cols"/>  from employee
    </select>

// 打开MybatisTest.java测试类，找到查询语句

@Test
    public void test4() throws IOException {
        // error  warn  info  debug  trace
        log.info("--------begin---------");
        System.out.println("begin");
        List<Employee> employeeList = employeeMapper.queryAll();
        System.out.println(employeeList);
        System.out.println("end");
    }

//执行程序后依然可以查询到结果

//条件查询

//新建类 EmployeeQO

package cn.wolfcode.qo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.math.BigDecimal;
@AllArgsConstructor
@NoArgsConstructor
@Data
public class EmployeeQO {
    private String name;
    private String Job;
    private BigDecimal SalStart;//范围有开始和结束
    private BigDecimal SalEnd;
    private String Key;//关键字  多个字段中，只要出现就符合
}

//打开测试类

@Test
    public void test6() throws IOException {
        //整形字段 区间 begin end String 模糊查询 like ‘%关键字%’
        // 条件 名字 岗位 工资区间
        EmployeeQO qo = new EmployeeQO();
        qo.setName("张三");
        qo.setSalStart(new BigDecimal(1000));
        qo.setSalEnd(new BigDecimal(10000.0));
        qo.setKey("三");
        List<Employee> employeeList = employeeMapper.queryByCondition(qo);
        System.out.println(employeeList);
    }

//alt+enter 从测试类中跳转，在EmployeeMapper.java中创建方法

List<Employee> queryByCondition(EmployeeQO qo);

//继续如上操作，先跳到实现类中实现方法

@Override public List<Employee> queryByCondition(EmployeeQO qo) { SqlSession sqlSession = MyBatisTool.getSqlSession(); List<Employee> list = sqlSession.selectList("cn.wolfcode.mapper.EmployeeMapper.queryByCondition",qo); MyBatisTool.close(sqlSession); return list; }

//返回EmployeeMapper.java，在sql语句中创建statement

<select id="queryByCondition" resultMap ="empMap"> select <include refid="emp_cols"/> from employee <where>（作用1：在有关键字的时候出现 作用2：删除多余的and,or） <if test="name !=null and name !=''"> and name like concat('%',#{name},'%') </if> <if test="salStart !=null and salStart !=''"> and sal >= #{salStart} </if> <if test="salEnd !=null and salEnd !=''"> and sal &lt;= #{salEnd} </if> </where> </select>

//批量增加

@Test
    public void test7() throws IOException {
        List<Employee> list = List.of(
                new Employee(null, "mao1", 1000.0, true),
                new Employee(null, "mao2", 2000.0, true),
                new Employee(null, "mao3", 3000.0, true));

        int m = employeeMapper.saveList(list);
        if (m > 0) {
            System.out.println("保存成功");
        } else {
            System.out.println("保存失败");`

//跳转操作 到实现类中

@Override
    public int saveList(List<Employee> list) {
        SqlSession sqlSession = MyBatisTool.getSqlSession();
        int m = sqlSession.insert("cn.wolfcode.mapper.EmployeeMapper.saveList",list);
        MyBatisTool.close(sqlSession);
        return m;
    }

//跳转操作 到sql中 使用for循环

<insert id="saveList">
        <foreach collection="list"（创建集合）
                 item="emp"(循环中每个元素叫做emp)
                 separator=","（分隔符）
                 open="insert into employee(name,sal,sex) values"（sql语句）
                 close="">（无）
            (#{emp.name},#{emp.sal},#{emp.sex})
        </foreach>
    </insert>

//批量删除

@Test
    public void test8() {
        Long[] ids = {15L, 16L, 17L};
        int m = employeeMapper.deleteByIds(ids);
        if (m > 0) {
            System.out.println("删除成功");
        } else {
            System.out.println("删除失败");
        }
    }`

//跳

int deleteByIds(Long[] ids);

//再跳

@Override
    public int deleteByIds(Long[] ids) {
        SqlSession sqlSession = MyBatisTool.getSqlSession();
        int m = sqlSession.delete("cn.wolfcode.mapper.EmployeeMapper.deleteByIds", ids);
        MyBatisTool.close(sqlSession);
        return m;
    }

//再再跳

<delete id="deleteByIds">
        delete from employee
        <foreach collection="array" item="id" separator="," open="where id in(" close= ")">
            #{id}
        </foreach>
    </delete>

//局部更新

 @Test
    public void test9() throws IOException {
        // 完全更新
        Employee emp = new Employee(13L, "maomaocong", null, false);
        boolean ret = employeeMapper.updatePartten(emp);
        if (ret) {
            System.out.println("更新成功");
        } else {
            System.out.println("更新失败");
        }
    }

//跳

boolean updatePartten(Employee emp);

//跳

@Override
    public boolean updatePartten(Employee emp) {
        SqlSession sqlSession = MyBatisTool.getSqlSession();
        int m = sqlSession.update("cn.wolfcode.mapper.EmployeeMapper.updatePartten", emp);
        MyBatisTool.close(sqlSession);
        return m>0;
    }

//跳

<update id="updatePartten">
        update employee
        <set>
        <if test="name !=null and name !=''">
            name=#{name},
        </if>
        <if test="sal !=null and sal !=''">
            sal=#{sal},
        </if>
        <if test="sex !=null and sex !=''">
            sex=#{sex},
        </if>
        </set>
         where id=#{id}
    </update>

//resultMap 在sql里面添加

<resultMap id="empMap" type="Employee">
        <id property="id" column="id"/>
        <result property="name" column="name" />
        <result property="sal" column="salary"/>
        <result property="sex" column="sex"/>

    </resultMap>

//下面更改 //下面更改

<select id="queryById" parameterType="java.lang.Long" resultMap ="empMap">
        select <include refid="emp_cols"/> from employee where id=#{id}
    </select>
    <select id="queryAll" resultMap ="empMap">
        select
        <include refid="emp_cols"/>
        from employee
    </select>