第五篇:SpringBoot 2.x整合BeetlSQL

2022-06-08 13:05:20

第五篇:SpringBoot 2.x整合BeetlSQL
image.png

上图是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官方文档
项目点此下载

上一篇：Hibernate5.x Eclipse搭建


下一篇：openldap 2.4双主MirrorMode配置