1，前言使用Mybatis的開發(fā)者哗魂，大多數(shù)都會遇到一個問題，就是要寫大量的SQL在xml文件中漓雅，除了特殊的業(yè)務邏輯SQL之外录别，還有大量結(jié)構(gòu)類似的增刪改查SQL。而且邻吞，當數(shù)據(jù)庫表結(jié)構(gòu)改動時组题，對應的所有SQL以及實體類都需要更改。這工作量和效率的影響或許就是區(qū)別增刪改查程序員和真正程序員的屏障抱冷。這時崔列，通用Mapper便應運而生……什么是通用Mapper通用Mapper就是為了解決單表增刪改查，基于Mybatis的插件。開發(fā)人員不需要編寫SQL赵讯，不需要在DAO中增加方法盈咳，只要寫好實體類，就能支持相應的增刪改查方法边翼。如何使用以MySQL為例鱼响，假設存在這樣一張表：
CREATE TABLE `test_table` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT '',
`create_time` datetime DEFAULT NULL,
`create_user_id` varchar(32) DEFAULT NULL,
`update_time` datetime DEFAULT NULL,
`update_user_id` varchar(32) DEFAULT NULL,
`is_delete` int(8) DEFAULT NULL,
PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
2，導入jar
<!--sql-->
<dependencies>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>6.0.6</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.6</version>
</dependency>
<!--mybaits-->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.1</version>
</dependency>
<!--pageHelp分頁代碼-->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>1.2.3</version>
</dependency>
<!--web--->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!--tomact-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-tomcat</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>com.google.guava</groupId>
<artifactId>guava</artifactId>
<version>25.1-jre</version>
<scope>compile</scope>
</dependency>
<dependency>
<groupId>tk.mybatis</groupId>
<artifactId>mapper-spring-boot-starter</artifactId>
<version>RELEASE</version>
</dependency>
</dependencies>
3组底，yml配置
spring:
datasource:
# type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/sd_roulette?useUnicode=true&characterEncoding=utf8&autoReconnect=true&allowMultiQueries=true
username: root
password: root
#com.taotao.tongyongmapper.mapper.BaseMapper 基礎mapper接口
#com.taotao.tongyongmapper.pojo 實體類的包路徑
mybatis:
type-aliases-package: com.taotao.tongyongmapper.pojo
mapper-locations: classpath:mapper/*.xml
mapper-mappers: com.taotao.tongyongmapper.mapper.BaseMapper
mapper-identity: MYSQL
server:
port: 8080
jpa:
properties:
hibernate:
show_sql: true
format_sql: true
#顯示sql日志
logging:
level:
com.taotao.tongyongmapper.mapper: DEBUG #mapper包路徑
3,實體類
package com.taotao.tongyongmapper.pojo;
import com.alibaba.fastjson.annotation.JSONField;
import javax.persistence.*;
import java.io.Serializable;
import java.util.Date;
@Table(name = "test_table")
public class TestTablePojo implements Serializable {
private static final long serialVersionUID=1l;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String name;
@JSONField(format="yyyy-MM-dd HH:mm:ss")
private Date createTime;
private String createUserId;
@JSONField(format="yyyy-MM-dd HH:mm:ss")
private Date updateTime;
private String updateUserId;
private Integer isDelete;
...........//省去set丈积，get方法
}
4，枚舉 ResponseCode
package com.taotao.tongyongmapper.enums;
public enum ResponseCode {
SUCCESS,
FAILED,
NO_LOGIN,
INNER_ERROR,
}
5 ,定義時間格式
package com.taotao.tongyongmapper.model;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.serializer.SerializeConfig;
import com.alibaba.fastjson.serializer.SimpleDateFormatSerializer;
import java.util.Date;
public abstract class BaseModel {
public String toString(){ //定義時間輸出格式
SerializeConfig sc=new SerializeConfig();
sc.put(Date.class,new SimpleDateFormatSerializer("yyyy-MM-dd HH:mm:ss"));
return JSON.toJSONString(this,sc);
}
}
6,ResponseModel 類
package com.taotao.tongyongmapper.model;
public class ResponseModel<E,T>extends BaseModel {
private static final long serialVersionUID = 2761035997605462938L;
private Long timestamp;
private E code;
private T data;
private String msg;
private Long time;
private Exception exception;
private Object datas;
.....//省去set债鸡，get
}
7江滨，基礎mapper 接口 其他的mapper接口繼承這個接口就可以
package com.taotao.tongyongmapper.mapper;
import tk.mybatis.mapper.common.Mapper;
import tk.mybatis.mapper.common.MySqlMapper;
public interface BaseMapper <T> extends Mapper<T>,MySqlMapper<T> {
}
8,,TestTableMapper 繼承BaseMapper 接口 通用mapper
package com.taotao.tongyongmapper.mapper;
import com.taotao.tongyongmapper.pojo.TestTablePojo;
import org.apache.ibatis.annotations.Mapper;
@Mapper
public interface TestTableMapper extends BaseMapper<TestTablePojo> {
}
9，controller 類
package com.taotao.tongyongmapper.controller;
import com.taotao.tongyongmapper.enums.ResponseCode;
import com.taotao.tongyongmapper.model.ResponseModel;
import com.taotao.tongyongmapper.pojo.TestTablePojo;
import com.taotao.tongyongmapper.service.TestTableService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
@RestController
public class TestTableController {
@Autowired
private TestTableService testTableService;
/**
* 添加
* @param testTablePojo
* @return
*/
@RequestMapping("/insert") public String insert(TestTablePojo testTablePojo) {
ResponseModel<ResponseCode,Object> responseModel =testTableService.insert(testTablePojo);
return responseModel.toString();
}
/**
* 根據(jù)id刪除
* @param id
* @return
*/
@RequestMapping("/delete")
public String hello(Long id){
return testTableService.delete(id).toString();
}
/**
* 修改
* @param testTablePojo
* @return
*/
@RequestMapping("/update")
public String update(TestTablePojo testTablePojo){
return testTableService.update(testTablePojo).toString();
}
/**
* 模糊查詢和分頁
* @param keyword
* @param page
* @return
*/
@RequestMapping("/search")
public String search(String keyword,Integer page, Integer pageSize){
return testTableService.search(keyword,page,pageSize).toString();
}
}
10 service
package com.taotao.tongyongmapper.service;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.google.common.base.Strings;
import com.taotao.tongyongmapper.enums.ResponseCode;
import com.taotao.tongyongmapper.
import com.taotao.tongyongmapper.model.PageBeanModel;
import com.taotao.tongyongmapper.model.ResponseModel;
import com.taotao.tongyongmapper.pojo.TestTablePojo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.
import org.springframework.transaction.annotation.Transactional;
import tk.mybatis.mapper.entity.Example;
import java.util.Date;
import java.util.List;
@Transactional(rollbackFor = { RuntimeException.class, Exception.class })//保證事務一致性厌均，發(fā)生異郴；回滾事務
@Service
public class TestTableService {
@Autowired
private TestTableMapper testTableMapper;
/**
* 添加
* @param testTablePojo
* @return
*/
public ResponseModel<ResponseCode, Object> insert(TestTablePojo testTablePojo) {
ResponseModel<ResponseCode, Object> responseModel=new ResponseModel<>();
testTablePojo.setCreateTime(new Date());
testTablePojo.setUpdateTime( new Date());
if(testTableMapper.selectOne(new TestTablePojo().setName(testTablePojo.getName()))==null){ //根據(jù)字段條件查詢方法
testTableMapper.selectByPrimaryKey(testTablePojo.getId());//根據(jù)id主鍵來查詢方法
if ( testTableMapper.insertSelective(testTablePojo)==1){ //添加方法
responseModel.setCode(ResponseCode.SUCCESS)
.setMsg("添加成功");
}
}
return responseModel;
}
/**
* 根據(jù)id刪除
* @param id
* @return
*/
public ResponseModel<ResponseCode, Object> delete(Long id) {
ResponseModel<ResponseCode,Object>responseModel =new ResponseModel<>();
if(testTableMapper.deleteByPrimaryKey(id)==1){//根據(jù)主鍵刪除
responseModel.setCode(ResponseCode.SUCCESS)
.setMsg("刪除成功");
}
return responseModel;
}
/**
* 修改
* @param testTablePojo
* @return
*/
public ResponseModel<ResponseCode, Object> update(TestTablePojo testTablePojo) {
ResponseModel<ResponseCode,Object>responseModel =new ResponseModel<>();
if(testTablePojo==null
||testTablePojo.getId()==null
|| Strings.isNullOrEmpty(testTablePojo.getName())
||Strings.isNullOrEmpty(testTablePojo.getCreateUserId())){
responseModel.setCode(ResponseCode.FAILED)
.setMsg("參數(shù)錯誤");
}else{
//把name設置為null，代表賬號不能修改
testTablePojo.setName(null);
testTablePojo.setCreateTime(new Date());
testTablePojo.setUpdateTime( new Date());
if(testTableMapper.updateByPrimaryKeySelective(testTablePojo)==1){
responseModel.setCode(ResponseCode.SUCCESS)
.setMsg("修改成功");
}
}
return responseModel;
}
/**
* 模糊查詢和分頁 通用mapper的example 方法
* @param keyword
* @param page
* @return
*/
public ResponseModel<ResponseCode, PageBeanModel<TestTablePojo>> search(String keyword, Integer page, int pageSize) {
ResponseModel<ResponseCode,PageBeanModel<TestTablePojo>> responseModel=new ResponseModel<>();
keyword=(keyword==null?"": keyword.trim());
page=(page==null ?1:page);
try {
PageHelper.startPage(page, pageSize); //一行代碼實現(xiàn)分頁
//創(chuàng)建條件查詢對象
Example example =new Example(TestTablePojo.class);
Example.Criteria criteria =example.createCriteria();
criteria.orLike("name","%"+keyword+"%")
.orLike("createUserId","%"+keyword+"%"); //模糊查詢
example.setOrderByClause("id desc");
List<TestTablePojo> testTablePojoList =testTableMapper.selectByExample(example);
for (TestTablePojo testTablePojo:testTablePojoList)testTablePojo.setName(null);//設置name為空（當不需要再前臺出現(xiàn)的數(shù)據(jù)都可以這樣設置）
PageInfo<TestTablePojo> pojoPageInfo =new PageInfo<>(testTablePojoList); //裝填數(shù)據(jù)
responseModel.setCode(ResponseCode.SUCCESS)
.setMsg("用戶查詢成功")
.setDatas(pojoPageInfo);
}catch (Exception e){
e.getStackTrace();
responseModel.setCode(ResponseCode.INNER_ERROR)
.setMsg("內(nèi)部錯誤")
.setException(e);
}
return responseModel;
}
}
