转载

MyBatis 中的动态 SQL 特性

动态SQL

定义:SQL语句执行时,会根据传入的参数的个数及参数的内容而发生变化。


上代码:

application.properties

#MySQL数据源
spring.datasource.url=jdbc:mysql://121.41.98.23:3306/hr
#数据库名
spring.datasource.username=root
#数据库密码
spring.datasource.password=root
#数据库引擎
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

#显示日志信息
logging.level.web=debug

#显示详细信息
spring.http.log-request-details=true

Staff.java

package com.newer.dsql1.pojo;
/**
 * Staff实体类
 * @author Admin
 *
 */
public class Staff {

	/**
	 * 员工编号
	 */
	int id;
	
	/**
	 * 员工名字
	 */
	String name;
	
	/**
	 * 员工职位
	 */
	String job;
	
	/**
	 * 员工联系方式
	 */
	String phone;
	
	/**
	 * 部门id
	 */
	Dept dept=new Dept();

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getJob() {
		return job;
	}

	public void setJob(String job) {
		this.job = job;
	}

	public String getPhone() {
		return phone;
	}

	public void setPhone(String phone) {
		this.phone = phone;
	}

	public Dept getDept() {
		return dept;
	}

	public void setDept(Dept dept) {
		this.dept = dept;
	}

	@Override
	public String toString() {
		return "Staff [id=" + id + ", name=" + name + ", job=" + job + ", phone=" + phone + ", dept=" + dept + "]";
	}

	
	
}

Dept.java

package com.newer.dsql1.pojo;
/**
 * Dept实体类
 * @author Admin
 *
 */
public class Dept {

	/**
	 * 部门编号
	 */
	int id;
	
	/**
	 * 部门名称
	 */
	String title;
	
	/**
	 * 部门地址
	 */
	String loc;

	/**
	 * getters setters方法
	 * @return
	 */
	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getTitle() {
		return title;
	}

	public void setTitle(String title) {
		this.title = title;
	}

	public String getLoc() {
		return loc;
	}

	public void setLoc(String loc) {
		this.loc = loc;
	}

	/**
	 * tostring方法
	 */
	@Override
	public String toString() {
		return "Dept [id=" + id + ", title=" + title + ", loc=" + loc + "]";
	}
	
	
}

StaffMapper.java

package com.newer.dsql1.mapper;

import java.util.List;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.One;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;

import org.apache.ibatis.annotations.SelectProvider;
import org.apache.ibatis.jdbc.SQL;

import com.newer.dsql1.pojo.Dept;
import com.newer.dsql1.pojo.Staff;

/**
 * MySQL映射,数据持久化
 * @author Admin
 *
 */
@Mapper
public interface StaffMapper {

	/**
	 * 查询staff的数据
	 */
	@SelectProvider(type = SqlProvider.class,method = "findStaff")
	@Results(
			id = "staffResultMap",
			value = {
					@Result(
							column = "dept_id",
							property = "dept",
							javaType = Dept.class,
							one=@One(select="com.newer.dsql1.mapper.DeptMapper.findById")
							
							)
					
			}
			)
	List<Staff> findAll(Staff staff);
	
	
	/**
	 * 定义一个内部类
	 */
	
	static class SqlProvider{
		/**
		 * 定义一个返回字符串的方法
		 */
		
		public String findStaff(Staff staff) {
			return new SQL() {{
				SELECT("*");
				FROM("staff");
				if(staff !=null) {
					if(staff.getName() !=null && staff.getName().length()>0) {
						
						WHERE("name like concat('%',#{name},'%')");
					}
					if(staff.getJob() !=null && staff.getJob().length()>0) {
						WHERE("job like #{job}");
					}
					if(staff.getPhone() !=null && staff.getPhone().length()>0) {
						WHERE("phone like #{phone}");
					}
					if(staff.getDept().getId()>0) {
						WHERE("dept_id=#{dept.id}");
					}
				}
				
			}
			}
					
					
					
					.toString();
		}
	}
}

DeptMapper.java

package com.newer.dsql1.mapper;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import com.newer.dsql1.pojo.Dept;

/**
 * MySQL映射,数据持久化存储
 * @author Admin
 *
 */
@Mapper
public interface DeptMapper {

	/**
	 * 根据id查找dept表的数据
	 */
	@Select("select * from dept where id=#{id}")
	Dept findById(int id);
}

HomeController.java

package com.newer.dsql1.controller;

import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;

/**
 * 普通控制器
 * @author Admin
 *
 */
@Controller
public class HomeController {

	/**
	 * 返回视图名
	 * @return
	 */
	@GetMapping("/")
	public String home() {
		return "index.html";
	}
}

StaffController.java

package com.newer.dsql1.controller;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import com.newer.dsql1.mapper.StaffMapper;
import com.newer.dsql1.pojo.Staff;

/**
 * RESTful控制器
 * @author Admin
 *
 */
@RestController
public class StaffController {

	/**
	 * 
	 * 自动注入依赖
	 */
	@Autowired
	StaffMapper staffMapper;
	
	/**
	 * 查询staff表的方法
	 */
//	BeanUtil 反射,自动把参数填充特定的对象
	@GetMapping("/staff")
	public List<Staff> findAll(Staff staff,@RequestParam(required = false) Integer deptId){
		
//		如果deptId不为空
		if(deptId !=null) {
//			手动填充不匹配的参数
			staff.getDept().setId(deptId);
		}
		return staffMapper.findAll(staff);
	}
}

index.html

<!doctype html>
<html lang="en">

<head>
  <title>hr数据库的操作</title>
  <!-- Required meta tags -->
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">

  <!-- Bootstrap CSS -->
  <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css"
    integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">
  <!-- 导入axios -->
  <script src="https://unpkg.com/axios/dist/axios.min.js"></script>
  <!-- 导入vue -->
  <script src="https://cdn.jsdelivr.net/npm/vue/dist/vue.js"></script>

</head>

<body>

  <!-- 展板 -->
  <div class="jumbotron jumbotron-fluid">
    <div class="container">
      <h1 class="display-3">动态 SQL</h1>
    </div>
  </div>

  <div id="app" class="container">
    <div class="row">
      <div class="col">
        <!-- 卡片 -->
        <div class="card my-3">
          <div class="card-body">

            <label for="">姓名</label>
            <input type="text" v-model="name" class="mr-3">
            <label for="">电话</label>
            <input type="text" v-model="phone" class="mr-3">
            <label for="">职位</label>
            <input type="text" v-model="job" class="mr-3">
            <label for="">部门</label>
            <!-- 下拉框 -->
            <select v-model="dept">
              <option value="0">-</option>
              <option value="1">产品</option>
              <option value="2">研发</option>
              <option value="3">测试</option>
              <option value="4">实施</option>
            </select>
            <!-- 搜索按钮 -->
            <button @click="search" type="button" class="btn btn-outline-danger float-right">搜索</button>
          </div>
        </div>
        <!-- 表格 -->
        <table class="table">
          <thead>
            <tr>
              <th>编号</th>
              <th>姓名</th>
              <th>职位</th>
              <th>电话</th>
              <th>部门</th>
            </tr>
          </thead>
          <tbody>
            <tr v-for="(item, index) in  staffList" :key="index">
              <td>{{ item.id }}</td>
              <td>{{ item.name }}</td>
              <td>{{ item.job }}</td>
              <td>{{ item.phone }}</td>
              <td>{{ item.dept.title }}</td>
            </tr>
          </tbody>
        </table>
      </div>
    </div>
  </div>

  <!-- vue -->
  <script>
    new Vue({
      el: '#app',
      data: {
        // 员工列表
        staffList: [],
        name: '',
        job: '',
        phone: '',
        // 员工的编号
        dept: 0
      },
      methods: {
        // 搜索的方法
        search: function () {
          let url = `http://127.0.0.1:8080/staff`;
          params = {
            name: this.name,
            job: this.job,
            phone: this.phone,
            deptId: this.dept
          }
          // 从服务端获取数据
          axios.get(url, {
            params: params
          })
            .then(res => {
              console.log(res);
              // 把从服务端获的数据给staffList
              this.staffList = res.data;
            })
        }
      },

      created() {
        let url = `http://127.0.0.1:8080/staff`;
        axios.get(url)
          .then(res => {
            console.log(res)
            // 把从服务端获的数据给staffList
            this.staffList = res.data;
          })
          .catch(err => {
            console.error(err);
          })
      },
    })

  </script>

  <!-- Optional JavaScript -->
  <!-- jQuery first, then Popper.js, then Bootstrap JS -->
  <script src="https://code.jquery.com/jquery-3.3.1.slim.min.js"
    integrity="sha384-q8i/X+965DzO0rT7abK41JStQIAqVgRVzpbzo5smXKp4YfRvH+8abtTE1Pi6jizo"
    crossorigin="anonymous"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.7/umd/popper.min.js"
    integrity="sha384-UO2eT0CpHqdSJQ6hJty5KVphtPhzWj9WO1clHTMGa3JDZwrnQq4sF86dIHNDz0W1"
    crossorigin="anonymous"></script>
  <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js"
    integrity="sha384-JjSmVgyd0p3pXB1rRibZUAYoIIy6OrQ6VrjIEaFf/nJGzIxFDsf4x0xIM+B07jRM"
    crossorigin="anonymous"></script>
</body>

</html>

代码到这差不多结束啦!现在需要一个数据库来测试:


运行程序,打开浏览器查看效果

这里可以取到数据库里的数据:

 


现在看我们的主界面:(实现了模糊查询)

 

正文到此结束
本文目录