第五篇 SpringBoot 2 x整合BeetlSQL-白红宇

上图是中对BeetlSQL的介绍，简单来说我们可以得到几个点

开发效率高

维护性好

性能数倍于JPA MyBatis

关于BeetlSQL的更多介绍大家可以去到官网去看看，接下来我们来看看如何把这个DAO工具整合到项目中

pom.xml


    		
     			
      
       org.springframework.boot
      			
      
       spring-boot-starter-web
      		
     		
     		
     			
      
       com.ibeetl
      			
      
       beetlsql
      			
      
       2.10.34
      		
     		
     		
     			
      
       com.ibeetl
      			
      
       beetl
      			
      
       2.9.3
      		
     		
     			
      
       mysql
      			
      
       mysql-connector-java
      			
      
       runtime
      		
     		
     			
      
       org.springframework.boot
      			
      
       spring-boot-starter-test
      			
      
       test
      		
     	
    复制代码

sql文件，我这里用的是mysql

CREATE TABLE `test`.`Untitled`  (  `id` int(11) NOT NULL AUTO_INCREMENT,  `username` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,  `nickname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,  `age` int(11) NULL DEFAULT 18,  `cdate` timestamp(0) NULL DEFAULT CURRENT_TIMESTAMP(0),  `udate` timestamp(0) NULL DEFAULT CURRENT_TIMESTAMP(0),  PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;复制代码

User.java

package com.priv.gabriel.entity;/** * Created with Intellij IDEA. * * @Author: Gabriel * @Date: 2018-10-14 * @Description: */public class User {	private long id;	private String username;	private String nickname;	private int age;	public long getId() {		return id;	}	public void setId(long id) {		this.id = id;	}	public String getUsername() {		return username;	}	public void setUsername(String username) {		this.username = username;	}	public String getNickname() {		return nickname;	}	public void setNickname(String nickname) {		this.nickname = nickname;	}	public int getAge() {		return age;	}	public void setAge(int age) {		this.age = age;	}	@Override	public String toString() {		return "User{" +				"id=" + id +				", username='" + username + '\'' +				", nickname='" + nickname + '\'' +				", age=" + age +				'}';	}}复制代码

在这里有两个分支，一种是通过sqlManager来操作，另一种是整合mapper，在这里我们现看看第一种方式

SQLManager方式

UserControllerForSQLManager.java

package com.priv.gabriel.controller;import com.priv.gabriel.entity.User;import com.priv.gabriel.repository.UserRepository;import org.beetl.sql.core.SQLManager;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.web.bind.annotation.PathVariable;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.bind.annotation.RequestMethod;import org.springframework.web.bind.annotation.RestController;import java.util.List;/** * Created with Intellij IDEA. * * @Author: Gabriel * @Date: 2018-10-14 * @Description: */@RestController@RequestMapping("/sqlManager/users")public class UserControllerForSQLManager {	//自动注入即可	@Autowired	private SQLManager sqlManager;	/*	 * @Author Gabriel	 * @Description 根据主键查找记录	 * @Date 2018/10/16	 * @Param [id] 主键	 * @Return void	 */	@RequestMapping(value = "/{id}",method = RequestMethod.GET)	public User selectUserById(@PathVariable("id")int id){		//如果没有查到数据则抛出异常		//return sqlManager.unique(User.class,id);		//如果没有查到数据则返回null		return sqlManager.single(User.class,id);	}	/*	 * @Author Gabriel	 * @Description 查询所有	 * @Date 2018/10/16	 * @Param []	 * @Return java.util.List
    
     */	@RequestMapping(value = {
   "","/"},method = RequestMethod.GET)	public List
     
       getUsers(){		//获取所有数据		//return sqlManager.all(User.class);		//查询该表的总数		//return sqlManager.allCount(User.class);		//获取所有数据 分页方式		return sqlManager.all(User.class,1,2);	}	/*	 * @Author Gabriel	 * @Description 单表条件查询	 * @Date 2018/10/16	 * @Param []	 * @Return void*/	public void singletonTableQuery(){		//通过sqlManager.query()可以在后面追加各种条件		sqlManager.query(User.class).andLike("username","admin").orderBy("age").select();	}	/*	 * @Author Gabriel	 * @Description 新增数据	 * @Date 2018/10/16	 * @Param [user]	 * @Return void*/	@RequestMapping(value = {
   "","/"},method = RequestMethod.POST)	public void addUser(User user){		//添加数据到对应表中		//sqlManager.insert(User.class,user);		//添加数据到对应表中，并返回自增id		sqlManager.insertTemplate(user,true);		System.out.println(user.getId());		System.out.println("新增成功");	}		/*	 * @Author Gabriel	 * @Description 根据主键修改	 * @Date 2018/10/16	 * @Param [user]	 * @Return java.lang.String*/	@RequestMapping(value = {
   "","/"},method = RequestMethod.PUT)	public String updateById(User user){		//根据id修改，所有值都参与更新		//sqlManager.updateById(user);		//根据id修改,属性为null的不会更新		if(sqlManager.updateTemplateById(user)>0){			return "修改成功";		}else{			return "修改失败";		}	}		/*	 * @Author Gabriel	 * @Description 删除记录	 * @Date 2018/10/16	 * @Param [id]	 * @Return java.lang.String*/	@RequestMapping(value = "/id",method = RequestMethod.DELETE)	public String deleteById(@PathVariable("id") int id){		if(sqlManager.deleteById(User.class,id) >0 ){			return "删除成功";		}else{			return "删除失败";		}	}}复制代码

Mapper方式

如果要使用mapper方式，则需要新建一个mapper接口，并继承BaseMapper UserRepository.java

package com.priv.gabriel.repository;import com.priv.gabriel.entity.User;import org.beetl.sql.core.mapper.BaseMapper;/** * Created with Intellij IDEA. * * @Author: Gabriel * @Date: 2018-10-14 * @Description: */public interface UserRepository extends BaseMapper
    
     {}复制代码

UserControllerForMapper.java

package com.priv.gabriel.controller;import com.priv.gabriel.entity.User;import com.priv.gabriel.repository.UserRepository;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.web.bind.annotation.PathVariable;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.bind.annotation.RequestMethod;import org.springframework.web.bind.annotation.RestController;import java.util.List;/** * Created with Intellij IDEA. * * @Author: Gabriel * @Date: 2018-10-14 * @Description: */@RestController@RequestMapping("/mapper/users")public class UserControllerForMapper {	@Autowired	private UserRepository repository;	@RequestMapping(value = {
   "","/"},method = RequestMethod.POST)	public void addUser(User user){		repository.insert(user);	}	@RequestMapping(value = {
   "","/"},method = RequestMethod.DELETE)	public String deleteUserById(User user){		if(repository.deleteById(user) >0 ){			return "删除成功";		}else{			return "删除失败";		}	}	@RequestMapping(value = {
   "","/"},method = RequestMethod.PUT)	public String updateUserById(User user){		//repository.updateById(user)		if(repository.deleteById(user) > 0){			return "修改成功";		}else{			return "修改失败";		}	}	@RequestMapping(value = "/{id}",method = RequestMethod.GET)	public User selectUserById(@PathVariable("id")int id){		//repository.unique(id);		return repository.single(id);	}	@RequestMapping(value = {
   "","/"},method = RequestMethod.GET)	public List
    
      getUsers(){		//repository.all(1,2);		//repository.allCount();		return repository.all();	}}复制代码

两种方式都介绍完毕了，但是BeetlSQL的重点部分还不在这，BeetlSQL的重点是可以创建一个SQL模板，到这大家可能会想，不就是个xml嘛，mybatis就有呀，不一样的地方就在这了，BeetlSQL的SQL模板是这样的

selectByTest===select * from user where 1=1复制代码

怎么样，是不是眼前一亮，很明显 selectByTest 是这条SQL语句的id , ===的作用是代表id和内容的分割，而最后的部分当然就是SQL语句啦然后简单介绍一下调用SQL模板的方式

SQLManager方式

@RequestMapping(value = "/test",method = RequestMethod.GET)	public List
    
      getUsersByTest(){		return sqlManager.select("user.selectByTest",User.class);	}复制代码

在SQLManager的方式中，通过sqlManager.select("模板id"，类型)的方式直接调用

Mapper的方式

@SqlResource("user")public interface UserRepository extends BaseMapper
    
     {	List
     
       selectByTest();}复制代码

在Mapper的方式，需要先建立一个xxx.md的SQL模板文件,通过@SqlResource(模板文件名)这个注解找到模板文件，再在mapper中写入与模板文件中同名的方法，即可在外部调用注意，BeetlSQL的模板文件位置默认在resource/sql/xxx.md中，好啦，关于BeetlSQL的介绍就到这里。 BeetlSQL的详细介绍