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

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

//第一课

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

//一般不用*，查的不细，应该用哪个字段写哪个字段

//一般不用*，查的不细，应该用哪个字段写哪个字段

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

CREATE TABLE employee( idbigint(20) NOT NULL AUTO_INCREMENT, namevarchar(255) DEFAULT NULL, sextinyint(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 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 from employee where id=#{id} select from employee `