image.png
上圖是BeetlSQL官網(wǎng)中對(duì)BeetlSQL的介紹誊垢，簡(jiǎn)單來說我們可以得到幾個(gè)點(diǎn)
- 開發(fā)效率高
- 維護(hù)性好
- 性能數(shù)倍于JPA MyBatis
關(guān)于BeetlSQL的更多介紹大家可以去到官網(wǎng)去看看，接下來我們來看看如何把這個(gè)DAO工具整合到項(xiàng)目中
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 +
'}';
}
}
在這里有兩個(gè)分支费封，一種是通過sqlManager來操作具篇，另一種是整合mapper淌实，在這里我們現(xiàn)看看第一種方式
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 {
//自動(dòng)注入即可
@Autowired
private SQLManager sqlManager;
/*
* @Author Gabriel
* @Description 根據(jù)主鍵查找記錄
* @Date 2018/10/16
* @Param [id] 主鍵
* @Return void
*/
@RequestMapping(value = "/{id}",method = RequestMethod.GET)
public User selectUserById(@PathVariable("id")int id){
//如果沒有查到數(shù)據(jù)則拋出異常
//return sqlManager.unique(User.class,id);
//如果沒有查到數(shù)據(jù)則返回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(){
//獲取所有數(shù)據(jù)
//return sqlManager.all(User.class);
//查詢?cè)摫淼目倲?shù)
//return sqlManager.allCount(User.class);
//獲取所有數(shù)據(jù) 分頁方式
return sqlManager.all(User.class,1,2);
}
/*
* @Author Gabriel
* @Description 單表?xiàng)l件查詢
* @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 新增數(shù)據(jù)
* @Date 2018/10/16
* @Param [user]
* @Return void*/
@RequestMapping(value = {"","/"},method = RequestMethod.POST)
public void addUser(User user){
//添加數(shù)據(jù)到對(duì)應(yīng)表中
//sqlManager.insert(User.class,user);
//添加數(shù)據(jù)到對(duì)應(yīng)表中缩搅，并返回自增id
sqlManager.insertTemplate(user,true);
System.out.println(user.getId());
System.out.println("新增成功");
}
/*
* @Author Gabriel
* @Description 根據(jù)主鍵修改
* @Date 2018/10/16
* @Param [user]
* @Return java.lang.String*/
@RequestMapping(value = {"","/"},method = RequestMethod.PUT)
public String updateById(User user){
//根據(jù)id修改越败，所有值都參與更新
//sqlManager.updateById(user);
//根據(jù)id修改,屬性為null的不會(huì)更新
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方式，則需要新建一個(gè)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的重點(diǎn)部分還不在這，BeetlSQL的重點(diǎn)是可以創(chuàng)建一個(gè)SQL模板堂鲤，到這大家可能會(huì)想亿傅，不就是個(gè)xml嘛，mybatis就有呀瘟栖，不一樣的地方就在這了葵擎，BeetlSQL的SQL模板是這樣的
selectByTest
===
select * from user where 1=1
怎么樣，是不是眼前一亮半哟，很明顯 selectByTest 是這條SQL語句的id , ===的作用是代表id和內(nèi)容的分割酬滤，而最后的部分當(dāng)然就是SQL語句啦
然后簡(jiǎn)單介紹一下調(diào)用SQL模板的方式
SQLManager方式
@RequestMapping(value = "/test",method = RequestMethod.GET)
public List<User> getUsersByTest(){
return sqlManager.select("user.selectByTest",User.class);
}
在SQLManager的方式中，通過sqlManager.select("模板id"寓涨，類型)的方式直接調(diào)用
Mapper的方式
@SqlResource("user")
public interface UserRepository extends BaseMapper<User>{
List<User> selectByTest();
}
在Mapper的方式盯串，需要先建立一個(gè)xxx.md的SQL模板文件,通過@SqlResource(模板文件名)這個(gè)注解找到模板文件，再在mapper中寫入與模板文件中同名的方法缅茉，即可在外部調(diào)用
注意嘴脾，BeetlSQL的模板文件位置默認(rèn)在resource/sql/xxx.md中，好啦蔬墩，關(guān)于BeetlSQL的介紹就到這里译打。
BeetlSQL的詳細(xì)介紹
Beetl官方文檔
BeetlSQL官方文檔
項(xiàng)目點(diǎn)此下載
