SpringBoot（二十二）整合Mybatis，使用SqlSessionTemplate实现增删改查[通俗易懂]

<!-- 添加依赖以后Mybatis就自动配置好了，可以直接使用，具体自动配置代码到mybatis-spring-boot-autoconfigure包下查看 -->
<!-- 上一篇博客里添加的spring-boot-starter-jdbc的依赖也可以去掉了，在这个里面已经有了，去不去掉都不影响(个人强迫症，哈哈哈) -->

<dependency>
	<groupId>org.mybatis.spring.boot</groupId>
	<artifactId>mybatis-spring-boot-starter</artifactId>
	<version>1.1.1</version>
</dependency>

#设置一下日志级别和格式，等会看sql打印
logging.level.com.eastcom.sql=debug
logging.pattern.console=%d{HH:mm:ss.SSS}  %-5p %c - %m%n

# 指定mapper.xml所在位置
mybatis.mapper-locations=classpath:mapper/*.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration
  PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
  "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">

<generatorConfiguration>

	<!-- JDBC连接驱动JAR包 -->
	<classPathEntry location="E:\MyFile\mybatisRun\ojdbc6-11.2.0.3.jar" />

	<context id="OracleTables" targetRuntime="MyBatis3">

		<!-- 取消生成的代码注释 -->
		<commentGenerator>
			<property name="suppressAllComments" value="true" />
		</commentGenerator>
	
		<jdbcConnection 
			driverClass="oracle.jdbc.driver.OracleDriver"
			connectionURL="jdbc:oracle:thin:localhost:scott" 
			userId="scott"	
			password="tiger" />
			
		<!-- 类型转换器 -->
		<javaTypeResolver>
			<property name="forceBigDecimals" value="false" />
		</javaTypeResolver>
		
		<!--生成实体类，targetProject:自动生成代码的位置，targetPackage：包名 -->
		<javaModelGenerator targetPackage="com.eastcom.sql.bean" targetProject="GenertorTest">
			<property name="enableSubPackages" value="true" />
			<property name="trimStrings" value="false" />
		</javaModelGenerator>
		
		<!-- 生成mapper.xml -->
		<sqlMapGenerator targetPackage="com.eastcom.sql.bean" targetProject="GenertorTest">
			<property name="enableSubPackages" value="true" />
		</sqlMapGenerator>
		
		<!-- tableName:用于自动生成代码的数据库表；domainObjectName:对应于数据库表的javaBean类名 -->
		<table tableName="Dept" 
		domainObjectName="Dept"></table>
	</context>

</generatorConfiguration>

package com.eastcom.sql.dao;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.exceptions.TooManyResultsException;
import org.apache.ibatis.session.ExecutorType;
import org.apache.ibatis.session.SqlSession;
import org.mybatis.spring.MyBatisSystemException;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;

/**
 *  Record：实体类，Example：generator生成的用于拼接where条件的帮助类，可以在不修改mapper文件的基础上添加where条件
 *  下面NameSpace后面 + 的是mapper文件里的标签id。
 */
public class BaseDao<Record, Example> {
	
	protected String NameSpace = this.getClass().getName() + ".";
	
	@Autowired
	protected SqlSessionTemplate sqlSessionTemplate;
	
	/**
	 * count(*)
	 */
	public int countByExample(Example example) {
		Integer count = this.sqlSessionTemplate.selectOne(NameSpace
				+ "countByExample", example);
		return count.intValue();
	}
	
	/**
	 * 删除
	 */
	@Transactional(rollbackFor=Exception.class)
	public int deleteByExample(Example example) {
		return this.sqlSessionTemplate.delete(NameSpace + "deleteByExample",
				example);
	}
	
	/**
	 * 查询，不带分页
	 */
	public List<Record> selectByExample(Example example) {
		return sqlSessionTemplate.selectList(NameSpace + "selectByExample",
				example);
	}
	
	/**
	 * 查一条数据
	 */
	@SuppressWarnings("unchecked")
	public Record selectOneByExample(Example example) {
		try{
		return sqlSessionTemplate.selectOne(NameSpace + "selectByExample",
				example);
		}catch(MyBatisSystemException e){
			if(e.getRootCause() instanceof TooManyResultsException)
				return (Record) sqlSessionTemplate.selectList(NameSpace + "selectByExample",
					example).get(0);
			else
				throw e;
		}
	}
	
	/**
	 * 根据主键查一条数据
	 */
	public Record selectByPrimaryKey(Object key){
		return sqlSessionTemplate.selectOne(NameSpace+"selectByPrimaryKey", key);
	}
	
	/**
	 * 修改record中有值的字段 
	 */
	@Transactional(rollbackFor=Exception.class)
	public int updateByExampleSelective(@Param("record") Record record,
			@Param("example") Example example) {
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("record", record);
		map.put("example", example);
		return this.sqlSessionTemplate.update(NameSpace
				+ "updateByExampleSelective", map);
	}
	
	/**
	 * 全量修改
	 */
	@Transactional(rollbackFor=Exception.class)
	public int updateByExample(@Param("record") Record record,
			@Param("example") Example example) {
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("record", record);
		map.put("example", example);
		return this.sqlSessionTemplate.update(NameSpace + "updateByExample",
				map);
	}
	
	/**
	 * 全量新增
	 */
	@Transactional(rollbackFor=Exception.class)
	public int insert(Record record) {
		return sqlSessionTemplate.insert(NameSpace + "insert", record);
	}
	
	/**
	 * 仅新增record中有值的列
	 */
	@Transactional(rollbackFor=Exception.class)
	public int insertList(List<Record> record) {
		SqlSession session = sqlSessionTemplate.getSqlSessionFactory().openSession(ExecutorType.BATCH, false);
		for(Record rec : record)
			sqlSessionTemplate.insert(NameSpace + "insertSelective", rec);
		session.commit();		
		return 1;
	}
	
	/**
	 * 同上，新增
	 */
	@Transactional(rollbackFor=Exception.class)
	public int insertSelective(Record record) {
		return sqlSessionTemplate.insert(NameSpace + "insertSelective", record);
	}
}

package com.eastcom.sql.dao;

import org.springframework.stereotype.Repository;

import com.eastcom.sql.bean.Dept;
import com.eastcom.sql.bean.DeptExample;

@Repository
public class DeptDao extends BaseDao<Dept, DeptExample> {

}

package com.eastcom.controller;

import javax.servlet.http.HttpServletRequest;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.ResponseEntity;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;

import com.eastcom.controller.util.JsonResult;
import com.eastcom.controller.util.ParamDto;
import com.eastcom.controller.util.Status;
import com.eastcom.service.DeptService;
import com.eastcom.sql.bean.Dept;
import com.eastcom.sql.bean.DeptExample;
import com.eastcom.sql.dao.DeptDao;

@Controller
@RequestMapping("/jdbc")
public class DetpController {
	
	@Autowired
	private DeptDao deptDao;
	
	@RequestMapping(value="/select",method=RequestMethod.GET)
	public ResponseEntity<JsonResult> selectById(Integer id){
		JsonResult result = new JsonResult();
		try {
			Dept data = deptDao.selectByPrimaryKey(id);
//			Dept data = deptService.selectById(id);
			result.setData(data);
			result.setStatus(Status.OK);
		} catch (Exception e) {
			result.setStatus(Status.ERROR);
			result.setData(e.getClass()+":"+e.getMessage());
			e.printStackTrace();
		}
		return ResponseEntity.ok(result); 
	}
	
	//@RequestBody:把接收到的json字符串自动转换为所对应的对象
	/**
	 * @param dept  { "deptno":"60", "dname":"开发", "location":"上海" }
	 * @return
	 */
	@PostMapping(value="/insert")
	public ResponseEntity<JsonResult> insert(@RequestBody Dept dept){
		JsonResult result = new JsonResult();
		try {
			Integer insert = deptDao.insertSelective(dept);
//			Integer insert = deptService.insert(dept);
			result.setData(insert);
			result.setStatus(Status.OK);
		} catch (Exception e) {
			result.setStatus(Status.ERROR);
			result.setData(e.getClass()+":"+e.getMessage());
			e.printStackTrace();
		}
		return ResponseEntity.ok(result); 
	}
	
	@PostMapping(value="/update")
	public ResponseEntity<JsonResult> update(@RequestBody ParamDto dto){
		JsonResult result = new JsonResult();
		try {
			System.out.println("dto:"+ dto.toString());
			
			DeptExample example = new DeptExample();
			example.createCriteria().andDeptnoEqualTo(dto.getId());
			Integer update = deptDao.updateByExampleSelective(dto.getDept(), example);
			
//			Integer update = deptService.updateById(dto.getId(),dto.getDept());
			result.setData(update);
			result.setStatus(Status.OK);
		} catch (Exception e) {
			result.setStatus(Status.ERROR);
			result.setData(e.getClass()+":"+e.getMessage());
			e.printStackTrace();
		}
		return ResponseEntity.ok(result); 
	}
	
	@PostMapping(value="/delete")
	public ResponseEntity<JsonResult> delete(@RequestParam("id") String id,HttpServletRequest request){
		JsonResult result = new JsonResult();
		try {
			DeptExample example = new DeptExample();
			example.createCriteria().andDeptnoEqualTo(Integer.parseInt(id));
			Integer delete = deptDao.deleteByExample(example);
			
//			Integer delete = deptService.deleteById(Integer.parseInt(id));
			result.setData(delete);
			result.setStatus(Status.OK);
		} catch (Exception e) {
			result.setStatus(Status.ERROR);
			result.setData(e.getClass()+":"+e.getMessage());
			e.printStackTrace();
		}
		return ResponseEntity.ok(result); 
	}

}