//动态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 <= #{salEnd} </if> </where> </select>
//批量增加
`@Test
public void test7() throws IOException {
List
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>