第五篇:SpringBoot 2.x整合BeetlSQL

image.png

上圖是BeetlSQL官網(wǎng)中對BeetlSQL的介紹，簡單來說我們可以得到幾個點

  1. 開發(fā)效率高
  2. 維護性好
  3. 性能數(shù)倍于JPA MyBatis

關(guān)于BeetlSQL的更多介紹大家可以去到官網(wǎng)去看看，接下來我們來看看如何把這個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，在這里我們現(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 {

    //自動注入即可
    @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);
        //查詢該表的總數(shù)
        //return sqlManager.allCount(User.class);
        //獲取所有數(shù)據(jù) 分頁方式
        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 新增數(shù)據(jù)
     * @Date 2018/10/16
     * @Param [user]
     * @Return void*/
    @RequestMapping(value = {"","/"},method = RequestMethod.POST)
    public void addUser(User user){
        //添加數(shù)據(jù)到對應表中
        //sqlManager.insert(User.class,user);
        //添加數(shù)據(jù)到對應表中，并返回自增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的不會更新
        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的重點是可以創(chuàng)建一個SQL模板，到這大家可能會想，不就是個xml嘛，mybatis就有呀，不一樣的地方就在這了，BeetlSQL的SQL模板是這樣的

selectByTest
===
select * from user where 1=1

怎么樣，是不是眼前一亮，很明顯 selectByTest 是這條SQL語句的id , ===的作用是代表id和內(nèi)容的分割，而最后的部分當然就是SQL語句啦
然后簡單介紹一下調(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的方式，需要先建立一個xxx.md的SQL模板文件,通過@SqlResource(模板文件名)這個注解找到模板文件，再在mapper中寫入與模板文件中同名的方法，即可在外部調(diào)用
注意，BeetlSQL的模板文件位置默認在resource/sql/xxx.md中，好啦，關(guān)于BeetlSQL的介紹就到這里。
BeetlSQL的詳細介紹
Beetl官方文檔
BeetlSQL官方文檔
項目點此下載

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容