springboot整合BeetSQL

一、导入依赖

<dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>com.ibeetl</groupId>
            <artifactId>sql-springboot-starter</artifactId>
            <version>3.12.0-RELEASE</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>com.zaxxer</groupId>
            <artifactId>HikariCP</artifactId>
            <version>3.2.0</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot</artifactId>
            <version>2.6.0</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot</artifactId>
            <version>2.6.0</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot</artifactId>
        </dependency>
    </dependencies>

#

二、书写配置

spring.datasource.url=jdbc:mysql://localhost:3306/study
spring.datasource.username=root
spring.datasource.password=000000
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
beetlsql.sqlManagers=sqlManager1
beetlsql.sqlManager1.ds=datasource
beetlsql.sqlManager1.basePackage=org.beetl.sql.springboot.simple
beetlsql.sqlManager1.dbStyle=org.beetl.sql.core.db.MySqlStyle

#

三、添加配置类

package com.hu.config;
import com.zaxxer.hikari.HikariDataSource;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.env.Environment;
import javax.sql.DataSource;
@Configuration
public class SimpleDataSourceConfig {
    @Bean(name = "datasource")
    public DataSource datasource(Environment env) {
        HikariDataSource ds = new HikariDataSource();
        ds.setJdbcUrl(env.getProperty("spring.datasource.url"));
        ds.setUsername(env.getProperty("spring.datasource.username"));
        ds.setPassword(env.getProperty("spring.datasource.password"));
        ds.setDriverClassName(env.getProperty("spring.datasource.driver-class-name"));
        return ds;
    }
}

#

四、书写sql

ET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` int(255) NOT NULL AUTO_INCREMENT,
  `age` int(11) NULL DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `department_id` int(255) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, 23, '修改name', 1001);
INSERT INTO `user` VALUES (2, 22, '李大本事', 1002);
INSERT INTO `user` VALUES (3, 24, '麻子', 1003);
INSERT INTO `user` VALUES (4, 34, '李四', 1005);
INSERT INTO `user` VALUES (5, 19, '19', 1005);
INSERT INTO `user` VALUES (6, 19, '王五', 1005);
SET FOREIGN_KEY_CHECKS = 1;

五、编写实体类

package com.hu.entry;
import lombok.Data;
import org.beetl.sql.annotation.entity.Column;
import org.beetl.sql.annotation.entity.Table;
@Data
@Table(name = "user")
public class User {
    private Integer id;
    private Integer age;
    private String name;
    @Column("department_id")  // @Column 映射到表字段
    private Integer departmentId;
}

六、书写mapper

package com.hu.mapper;
import com.hu.entry.User;
import org.beetl.sql.mapper.BaseMapper;
import org.beetl.sql.mapper.annotation.*;
public interface UserMapper extends BaseMapper<User> {
    @Sql("select * from user where id=?")
    @Select
    User queryUserById(Integer id);
    @Sql("update user set name=? where id=?")
    @Update
    Integer updateName(String name,Integer id);
    @Template("select * from user where id=#{id}")
    User getUserById(@Param("id") Integer id);
}

#

七、测试

package com.hu;
import com.hu.entry.User;
import com.hu.mapper.UserMapper;
import org.beetl.sql.core.SQLManager;
import org.beetl.sql.core.SQLReady;
import org.beetl.sql.core.SqlId;
import org.beetl.sql.core.query.LambdaQuery;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import java.util.HashMap;
import java.util.List;
@SpringBootTest
class TestqApplicationTests {
    @Autowired
    private SQLManager sqlManager;
    @Test
    void contextLoads(){
        LambdaQuery<User> query = sqlManager.lambdaQuery(User.class);
        List<User>  listUser =  query.andEq(User::getId, 2).select();
        System.out.println(listUser.toString());
    }
    @Test
    void unique(){
        User user = sqlManager.unique(User.class, 1);
        System.out.println(user);
    }
    @Test
    void  update(){
        User user = sqlManager.unique(User.class, 2);
        user.setName("李大本事");
        int i = sqlManager.updateById(user);
        System.out.println(i);
    }
    @Test
    void selectTemplate(){
        User user = new User();
        user.setId(2);
        for (User user1 : sqlManager.template(user)) {
            System.out.println(user1);
        }
    }
    @Test
    void  sqlReadyTest(){
        String sql =  "select * from user where id = ?";
        Integer id = 1;
        SQLReady sqlReady = new SQLReady(sql, new Object[]{id});
        List<User> userList = sqlManager.execute(sqlReady, User.class);
        for (User user : userList) {
            System.out.println(user);
        }
    }
    @Test
    void executeSQLTest(){
        String sql = "select * from user where id = #{id} and name = #{name}";
        HashMap map = new HashMap();
        map.put("id",1);
        map.put("name","张三");
        for (User user : sqlManager.execute(sql, User.class, map)) {
            System.out.println(user);
        }
    }
    @Test
    void lambdaQuery(){
        LambdaQuery<User> lambdaQuery = sqlManager.lambdaQuery(User.class);
        for (User user : lambdaQuery.andEq("id", 1).andIsNotNull("name").select()) {
            System.out.println(user);
        }
    }
    @Test
    void mapperMethod(){
        UserMapper mapper = sqlManager.getMapper(UserMapper.class);
        User unique = mapper.unique(1);
        System.out.println(unique);
    }
    @Test
    void mapperInsert(){
        UserMapper mapper = sqlManager.getMapper(UserMapper.class);
        User user = new User();
        user.setName("王五");
        user.setAge(19);
        user.setDepartmentId(1005);
        mapper.insert(user);
    }
    @Test
    void mapperQueryUserById(){
        UserMapper mapper = sqlManager.getMapper(UserMapper.class);
        User user = mapper.queryUserById(1);
        System.out.println(user);
    }
    @Test
    public void mapperUpdateName(){
        UserMapper mapper = sqlManager.getMapper(UserMapper.class);
        Integer i = mapper.updateName("修改name",1);
        System.out.println(i);
    }
    @Test
    public void mapperGetUserById(){
        UserMapper mapper = sqlManager.getMapper(UserMapper.class);
        User user = mapper.getUserById(1);
        System.out.println(user);
    }
    @Test
    public void LiekQueryByMd(){
        SqlId sqlId = SqlId.of("user", "select");
        HashMap map = new HashMap();
        map.put("name","%李%");
        for (User user : sqlManager.select(sqlId, User.class, map)) {
            System.out.println(user);
        }
    }
}

注意：在resources 创建 sql文件夹 在sql文件夹下创建user.md。

select
===
```sql
select * from user u where 1=1
AND name like #{name}
order BY u.id desc
```