第五篇:SpringBoot 2.x整合BeetlSQL

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

1. 开发效率高
2. 维护性好
3. 性能数倍于JPA MyBatis

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

pom.xml

 <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <!-- 引入beetlsql --> <dependency> <groupId>com.ibeetl</groupId> <artifactId>beetlsql</artifactId> <version>2.10.34</version> </dependency> <!-- 引入beetl --> <dependency> <groupId>com.ibeetl</groupId> <artifactId>beetl</artifactId> <version>2.9.3</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> </dependencies> 

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<com.priv.gabriel.entity.User>*/ @RequestMapping(value = {"","/"},method = RequestMethod.GET) public List<User> 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<T>
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<User>{ } 

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<User> 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<User> getUsersByTest(){ return sqlManager.select("user.selectByTest",User.class); } 

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

Mapper的方式

@SqlResource("user") public interface UserRepository extends BaseMapper<User>{ List<User> selectByTest(); } 

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