BeetlSql&&ProtoBuffer实践Demo

前言

由于开发架构问题，公司使用的基于SpringBoot+BeetlSql+ProtoBuffer的架子来处理业务,由于BeetlSQL和Protobuffer有些小众，特记录一下。

项目架构介绍

• 本文采用SpringBoot+BeetlSQL+ProtoBuffer进行快速搭建开发
• PDMAN表快速设计与维护

项目准备工作

SQL语句

员工信息表

CREATE TABLE `inter_employee` (
  `id` varchar(32) NOT NULL COMMENT 'id',
  `name` varchar(32) NOT NULL COMMENT '姓名',
  `email` varchar(128) NOT NULL COMMENT '邮箱',
  `sex` char(1) NOT NULL COMMENT '性别 0-男；1-女；2-未知',
  `dept_id` bigint NOT NULL COMMENT '所属部门id',
  `org_id` varchar(32) NOT NULL COMMENT '所属机构id',
  `status` char(1) NOT NULL COMMENT '状态 0-无效；1-有效',
  `created_by` varchar(32) DEFAULT NULL COMMENT '创建人',
  `created_time` datetime DEFAULT NULL COMMENT '创建时间',
  `updated_by` varchar(32) DEFAULT NULL COMMENT '更新人',
  `updated_time` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='机构员工信息';

员工岗位表

CREATE TABLE `inter_employee_job_duty` (
  `id` varchar(32) NOT NULL COMMENT 'id',
  `emp_id` varchar(32) NOT NULL COMMENT '员工ID',
  `emp_job` varchar(32) NOT NULL COMMENT '员工岗位 CODE-开发；HR-人事经理',
  `job_name` varchar(32) NOT NULL COMMENT '岗位名称 CODE-开发；HR-人事经理',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='机构员工岗位表 员工可能会存在多个岗位，权限以角色为主，数据权限以岗位为主,员工-岗位：one2many关系';

相关环境

• jdk 1.8+
• mysql 5.7
• Lombok

项目案例

项目层级

├── pom.xml
├── src
│   ├── main
│   │   ├── Doc
│   │   ├── java
│   │   ├── protobuf
│   │   └── resources
│   └── test
│       └── java

相关依赖

引入springboot父项目依赖

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.3.RELEASE</version>
    </parent>

设置相关版本

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <java.version>1.8</java.version>
        <log4jdbc.version>1.16</log4jdbc.version>
        <fastjson.version>1.2.54</fastjson.version>
        <druid.version>1.1.14</druid.version>
    </properties>

引入相关依赖

    <dependencies>
        <!--Spring boot start -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-configuration-processor</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <!--Spring boot end -->
        <!-- beetlSql -->
        <dependency>
            <groupId>com.ibeetl</groupId>
            <artifactId>beetl-framework-starter</artifactId>
            <version>1.1.81.RELEASE</version>
        </dependency>
        <!--监控sql日志 -->
        <dependency>
            <groupId>org.bgee.log4jdbc-log4j2</groupId>
            <artifactId>log4jdbc-log4j2-jdbc4.1</artifactId>
            <version>${log4jdbc.version}</version>
        </dependency>
        <!--Mysql依赖包 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <!-- druid数据源驱动 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>${druid.version}</version>
        </dependency>
        <!--lombok插件 -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <!-- fastjson -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>${fastjson.version}</version>
        </dependency>
        <!--google protobuf 引用 -->
        <dependency>
            <groupId>com.google.protobuf</groupId>
            <artifactId>protobuf-java</artifactId>
            <version>3.6.1</version>
        </dependency>
        <dependency>
            <groupId>com.google.protobuf</groupId>
            <artifactId>protobuf-java-util</artifactId>
            <version>3.6.1</version>
        </dependency>
    </dependencies>

引入ProtoBuffer和Maven编译工具

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
            <!-- 跳过单元测试 -->
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-surefire-plugin</artifactId>
                <configuration>
                    <skipTests>true</skipTests>
                </configuration>
            </plugin>

            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.8.0</version>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                    <encoding>UTF-8</encoding>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.7.0</version>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                    <encoding>UTF-8</encoding>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-resources-plugin</artifactId>
                <version>3.0.2</version>
                <!-- set encoding to something not platform dependent -->
                <configuration>
                    <encoding>UTF-8</encoding>
                </configuration>
            </plugin>
            <plugin>
                <groupId>com.github.os72</groupId>
                <artifactId>protoc-jar-maven-plugin</artifactId>
                <version>3.7.0.2</version>
                <executions>
                    <execution>
                        <phase>generate-sources</phase>
                        <goals>
                            <goal>run</goal>
                        </goals>
                        <configuration>
                            <addProtoSources>all</addProtoSources>
                            <includeStdTypes>true</includeStdTypes>
                            <includeMavenTypes>direct</includeMavenTypes>
                            <!-- <type>java-shaded</type> <addSources>none</addSources> -->
                            <outputDirectory>src/main/java</outputDirectory>
                            <includeDirectories>
                                <!-- <include>com/idasound/protobuf</include> -->
                            </includeDirectories>
                            <inputDirectories>
                                <include>src/main/protobuf</include>
                                <!-- <include>${project.basedir}</include> -->
                            </inputDirectories>
                        </configuration>
                    </execution>
                </executions>
            </plugin>
        </plugins>
        <pluginManagement>
            <plugins>
                <!--This plugin's configuration is used to store Eclipse m2e settings
                    only. It has no influence on the Maven build itself. -->
                <plugin>
                    <groupId>org.eclipse.m2e</groupId>
                    <artifactId>lifecycle-mapping</artifactId>
                    <version>1.0.0</version>
                    <configuration>
                        <lifecycleMappingMetadata>
                            <pluginExecutions>
                                <pluginExecution>
                                    <pluginExecutionFilter>
                                        <groupId>
                                            com.github.os72
                                        </groupId>
                                        <artifactId>
                                            protoc-jar-maven-plugin
                                        </artifactId>
                                        <versionRange>
                                            [3.6.0.1,)
                                        </versionRange>
                                        <goals>
                                            <goal>run</goal>
                                        </goals>
                                    </pluginExecutionFilter>
                                    <action>
                                        <ignore></ignore>
                                    </action>
                                </pluginExecution>
                            </pluginExecutions>
                        </lifecycleMappingMetadata>
                    </configuration>
                </plugin>
            </plugins>
        </pluginManagement>
    </build>

相关配置

配置application.yml文件

spring:
  profiles:
    active: dev
  application:
    name: beetlSqlDemo-api

server:
  servlet:
    context-path: /beetlSqlDemo

配置dev【application-dev.yml】环境

server:
  port: 8080
  tomcat:
    max-swallow-size: 100MB

#配置数据源
spring:
  datasource:
    druid:
      type: com.alibaba.druid.pool.DruidDataSource
      driverClassName: net.sf.log4jdbc.sql.jdbcapi.DriverSpy
      url: jdbc:log4jdbc:mysql://localhost:3306/beetsql_Demo?serverTimezone=Asia/Shanghai&characterEncoding=utf8&useSSL=false&&allowPublicKeyRetrieval=true
      username: root
      password: 12345678

      # 初始化连接大小
      initial-size: 5
      # 最小空闲连接数
      min-idle: 5
      max-active: 30
      max-wait: 60000
      # 可关闭的空闲连接间隔时间
      time-between-eviction-runs-millis: 60000
      # 配置连接在池中的最小生存时间
      min-evictable-idle-time-millis: 300000
      validation-query: select '1' from dual
      test-while-idle: true
      test-on-borrow: false
      test-on-return: false
      # 打开PSCache，并且指定每个连接上PSCache的大小
      pool-prepared-statements: true
      max-open-prepared-statements: 50
      max-pool-prepared-statement-per-connection-size: 20
      # 配置监控统计拦截的filters
      filters: stat

      stat-view-servlet:
        url-pattern: /druid/*
        reset-enable: false
        login-username: admin
        login-password: 123456

      web-stat-filter:
        url-pattern: /*
        exclusions: "*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*"

  servlet:
    multipart:
      max-file-size: 50MB
      max-request-size: 50MB

sys:
  log:
    isSaveLog: true

logging:
  file: D:/logs/beetsqlDemo/dev/beetsqlDemo.log
  config: classpath:logback-dev.xml

配置日志

• 配置logback文件

  • 创建log4jdbc.log4j2.properties文件

  • 配置如下属性：

    # If you use SLF4J. First, you need to tell log4jdbc-log4j2 that you want to use the SLF4J logger
    log4jdbc.spylogdelegator.name=net.sf.log4jdbc.log.slf4j.Slf4jSpyLogDelegator
    

• 配置logback

  • 创建logback-dev.xml

  • 配置如下属性：

    <?xml version="1.0" encoding="UTF-8"?>
    <configuration scan="true" scanPeriod="60 seconds" debug="false">
        <contextName>beetlSqlDemo</contextName>
        <property name="LOG_HOME" value="D:/logs/beetSqlDemo/dev"/>
        <property name="LOG_NAME" value="beetlSqlDemo"/>
        <!--<springProperty scope="context" name="LOG_HOME" source="sys.log.output.path"/>-->
    
        <!--输出到控制台-->
        <appender name="console" class="ch.qos.logback.core.ConsoleAppender">
            <encoder>
                <pattern>%black(%contextName-) %red(%d{yyyy-MM-dd HH:mm:ss}) %green([%thread]) %highlight(%-5level) %boldMagenta(%logger{36}) - %gray(%msg%n)</pattern>
                <charset>utf-8</charset>
            </encoder>
        </appender>
    
        <appender name="FILE_LOG_OUT" class="ch.qos.logback.core.rolling.RollingFileAppender">
            <file>${LOG_HOME}/${LOG_NAME}.log</file>
            <append>true</append>
            <encoder>
                <pattern>%contextName- %d{yyyy-MM-dd HH:mm:ss.SSS} [%thread] %-5level %class{36} %L %M - %msg%xEx%n</pattern>
            </encoder>
            <rollingPolicy class="ch.qos.logback.core.rolling.SizeAndTimeBasedRollingPolicy">
                <fileNamePattern>${LOG_HOME}${file.separator}${LOG_NAME}.%d{yyyy-MM-dd}.%i.log</fileNamePattern>
                <maxHistory>180</maxHistory>
                <maxFileSize>100MB</maxFileSize>
            </rollingPolicy>
        </appender>
    
        <!--普通日志输出到控制台-->
        <root level="info">
            <appender-ref ref="console" />
        </root>
    
        <logger name="com.wabestway" level="INFO">
            <appender-ref ref="FILE_LOG_OUT"/>
        </logger>
        <logger name="com.idasound.isb" level="INFO">
            <appender-ref ref="FILE_LOG_OUT"/>
        </logger>
    
        <!--监控sql日志输出 -->
        <logger name="jdbc.sqlonly" level="INFO" additivity="false">
            <appender-ref ref="console" />
            <appender-ref ref="FILE_LOG_OUT"/>
        </logger>
    
        <logger name="jdbc.resultset" level="ERROR" additivity="false">
            <appender-ref ref="console" />
            <appender-ref ref="FILE_LOG_OUT"/>
        </logger>
    
        <logger name="jdbc.resultsettable" level="OFF" additivity="false">
            <appender-ref ref="console" />
        </logger>
    
        <logger name="jdbc.connection" level="OFF" additivity="false">
            <appender-ref ref="console" />
        </logger>
    
        <logger name="jdbc.sqltiming" level="OFF" additivity="false">
            <appender-ref ref="console" />
        </logger>
    
        <logger name="jdbc.audit" level="OFF" additivity="false">
            <appender-ref ref="console" />
        </logger>
    </configuration>
    
    

Demo实现

Model

创建员工信息

package com.bossyang.beetsqlDemo.model;
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;
import org.beetl.sql.core.annotatoin.AssignID;
import org.beetl.sql.core.annotatoin.Table;

import java.util.Date;
import java.util.List;

/**
 * 机构员工信息
 * @Title: InterEmployee.java
 * @author: 
 * @date: 2020年05月31日 13:43:25
 * @version V1.0
 */
@Data
@Table(name="inter_employee")
public class InterEmployee {
    @AssignID
    private String id; //id
    private String name; //姓名
    private String email; //邮箱
    private String sex; //性别 0-男；1-女；2-未知
    private Long deptId; //所属部门id
    private String orgId; //所属机构id
    private String status; //状态 0-无效；1-有效
    private String createdBy; //创建人
    @JsonFormat(pattern="yyyy-MM-dd HH:mm:ss",timezone="GMT+8")
    private Date createdTime; //创建时间
    private String updatedBy; //更新人
    @JsonFormat(pattern="yyyy-MM-dd HH:mm:ss",timezone="GMT+8")
    private Date updatedTime; //更新时间
    private List<String> jobDuties;

}

创建岗位表

package com.bossyang.beetsqlDemo.model;

import lombok.Data;
import org.beetl.sql.core.annotatoin.AssignID;
import org.beetl.sql.core.annotatoin.Table;
/**
 * 机构员工岗位表 员工可能会存在多个岗位，权限以角色为主，数据权限以岗位为主,员工-岗位：one2many关系
 * @Title: InterEmployeeJobDuty.java
 * @author: 
 * @date: 2020年05月31日 16:24:13
 * @version V1.0
 */
@Data
@Table(name="inter_employee_job_duty")
public class InterEmployeeJobDuty {
    @AssignID
    private String id; //id
    private String empId; //员工ID
    private String empJob; //员工岗位 CODE-开发；HR-人事经理
    private String jobName; //岗位名称 CODE-开发；HR-人事经理
}

SQL文件

**BeetlSql的SQL文件扫描默认路径为resources/sql，所以我们为了简便开发使用Beetlsql的默认配置。

• 在resources下创建一个名字为sql文件夹

• 增加文件，实现相关业务

  • interEmployeeExtra.md

    listCols
    ===
        t.id id, t.name name, t.email email, t.sex sex, t.dept_id deptId, t.org_id orgId, t.status status
        , t.created_by createdBy, t.created_time createdTime, t.updated_by updatedBy, t.updated_time updatedTime
        
    queryByCondition
    ===
        select #use("listCols")# 
        from inter_employee t
        where #use("condition")# 
        
    queryByCondition$count
    ===
        select count(1) total,count(t.id) empNums
        from inter_employee t
        where #use("condition")# 
        
          
    condition
    ===
        1 = 1  
        @if(!isEmpty(id)){
            and t.id = #id#
        @}
        @if(!isEmpty(name)){
            and t.name = #name#
        @}
        @if(!isEmpty(email)){
            and t.email = #email#
        @}
        @if(!isEmpty(sex)){
            and t.sex = #sex#
        @}
        @if(!isEmpty(deptId)){
            and t.dept_id = #deptId#
        @}
        @if(!isEmpty(orgId)){
            and t.org_id = #orgId#
        @}
        @if(!isEmpty(status)){
            and t.status = #status#
        @}
        @if(!isEmpty(createdBy)){
            and t.created_by = #createdBy#
        @}
        @if(!isEmpty(createdTime)){
            and t.created_time = #createdTime#
        @}
        @if(!isEmpty(updatedBy)){
            and t.updated_by = #updatedBy#
        @}
        @if(!isEmpty(updatedTime)){
            and t.updated_time = #updatedTime#
        @}
        @if(!isEmpty(jobDuties)){
            and EXISTS (SELECT 1 FROM inter_employee_job_duty jd WHERE jd.emp_id = t.id AND jd.emp_job IN (#join(jobDuties)#))
        @}
        
    delInterEmployeeById
    ===
    * 逻辑删除
     
        update inter_employee t set t.status = '0', t.updated_by = #updatedBy#, t.updated_time = now() where t.id = #id#
        
    batchDelInterEmployeeByIds
    ===
    * 批量逻辑删除
     
        update inter_employee t set t.status = '0', t.updated_by = #updatedBy#, t.updated_time = now() where t.id  in( #join(ids)#)
    

  • interEmployeeJobDutyExtra.md

    listCols
    ===
        t.id id, t.emp_id empId, t.emp_job empJob, t.job_name jobName
        
    queryByCondition
    ===
        select #use("listCols")# 
        from inter_employee_job_duty t
        where #use("condition")# 
        
    queryByCondition$count
    ===
        select count(1)
        from inter_employee_job_duty t
        where #use("condition")# 
        
          
    condition
    ===
        1 = 1  
        @if(!isEmpty(id)){
            and t.id = #id#
        @}
        @if(!isEmpty(empId)){
            and t.emp_id = #empId#
        @}
        @if(!isEmpty(empJob)){
            and t.emp_job = #empJob#
        @}
        @if(!isEmpty(jobName)){
            and t.job_name = #jobName#
        @}
        
    delInterEmployeeJobDutyById
    ===
    * 逻辑删除
     
        update inter_employee_job_duty t set t.status = '0', t.updated_by = #updatedBy#, t.updated_time = now() where t.id = #id#
        
    batchDelInterEmployeeJobDutyByIds
    ===
    * 批量逻辑删除
     
        update inter_employee_job_duty t set t.status = '0', t.updated_by = #updatedBy#, t.updated_time = now() where t.id  in( #join(ids)#)
    

DAO

创建repository文件夹

• InterEmployeeDao

package com.bossyang.beetsqlDemo.repository;

import com.bossyang.beetsqlDemo.ext.MyBaseMapper;
import com.bossyang.beetsqlDemo.model.InterEmployee;
import org.beetl.sql.core.annotatoin.SqlResource;
import org.beetl.sql.core.annotatoin.SqlStatement;
import org.beetl.sql.core.engine.PageQuery;

import java.util.List;

/**
 * InterEmployee Dao
 * @author: 
 * @date: 2020年05月31日 13:43:25
 * @version V1.0
 */
@SqlResource("interEmployeeExtra")
public interface InterEmployeeDao extends MyBaseMapper<InterEmployee> {
    /**
     * 分页查询
     * @Title: queryByCondition
     * @param: query      
     * @return: PageQuery 
     * @throws
     */
    public PageQuery<InterEmployee> queryByCondition(PageQuery query);
    
    /**
     * 不分页查询
     * @Title: queryByCondition
     * @param: interEmployee      
     * @return: List 
     * @throws
     */
    public List<InterEmployee> queryByCondition(InterEmployee interEmployee);
    
    /**
     * 根据id逻辑删除
     * @Title: queryByCondition
     * @param: interEmployee      
     * @return: int 
     * @throws
     */
    @SqlStatement(params = "id,updatedBy")
    public int delInterEmployeeById(String id, String updatedBy);
    
    /**
     * 批量逻辑删除
     * @Title: batchDelAppUserByIds
     * @param: ids
     * @param: updatedBy
     * @return: int 
     * @throws
     */
    @SqlStatement(params = "ids,updatedBy")
    public int batchDelInterEmployeeByIds(List<String> ids, String updatedBy);
}

• InterEmployeeJobDutyDao

package com.bossyang.beetsqlDemo.repository;

import com.bossyang.beetsqlDemo.model.InterEmployeeJobDuty;
import org.beetl.sql.core.annotatoin.SqlResource;
import org.beetl.sql.core.annotatoin.SqlStatement;
import org.beetl.sql.core.engine.PageQuery;
import org.beetl.sql.core.mapper.BaseMapper;

import java.util.List;

/**
 * InterEmployeeJobDuty Dao
 * @author: 
 * @date: 2020年05月31日 16:24:13
 * @version V1.0
 */
@SqlResource("interEmployeeJobDutyExtra")
public interface InterEmployeeJobDutyDao extends BaseMapper<InterEmployeeJobDuty> {
    /**
     * 分页查询
     * @Title: queryByCondition
     * @param: query      
     * @return: PageQuery 
     * @throws
     */
    public PageQuery<InterEmployeeJobDuty> queryByCondition(PageQuery query);
    
    /**
     * 不分页查询
     * @Title: queryByCondition
     * @param: interEmployeeJobDuty      
     * @return: List 
     * @throws
     */
    public List<InterEmployeeJobDuty> queryByCondition(InterEmployeeJobDuty interEmployeeJobDuty);
    
    /**
     * 根据id逻辑删除
     * @Title: queryByCondition
     * @param: interEmployeeJobDuty      
     * @return: int 
     * @throws
     */
    @SqlStatement(params = "id,updatedBy")
    public int delInterEmployeeJobDutyById(String id, String updatedBy);
    
    /**
     * 批量逻辑删除
     * @Title: batchDelAppUserByIds
     * @param: ids
     * @param: updatedBy
     * @return: int 
     * @throws
     */
    @SqlStatement(params = "ids,updatedBy")
    public int batchDelInterEmployeeJobDutyByIds(List<String> ids, String updatedBy);
}

Service

接口定义

• InterEmployeeService

package com.bossyang.beetsqlDemo.repository;

import com.bossyang.beetsqlDemo.model.InterEmployeeJobDuty;
import org.beetl.sql.core.annotatoin.SqlResource;
import org.beetl.sql.core.annotatoin.SqlStatement;
import org.beetl.sql.core.engine.PageQuery;
import org.beetl.sql.core.mapper.BaseMapper;

import java.util.List;

/**
 * InterEmployeeJobDuty Dao
 * @author: 
 * @date: 2020年05月31日 16:24:13
 * @version V1.0
 */
@SqlResource("interEmployeeJobDutyExtra")
public interface InterEmployeeJobDutyDao extends BaseMapper<InterEmployeeJobDuty> {
    /**
     * 分页查询
     * @Title: queryByCondition
     * @param: query      
     * @return: PageQuery 
     * @throws
     */
    public PageQuery<InterEmployeeJobDuty> queryByCondition(PageQuery query);
    
    /**
     * 不分页查询
     * @Title: queryByCondition
     * @param: interEmployeeJobDuty      
     * @return: List 
     * @throws
     */
    public List<InterEmployeeJobDuty> queryByCondition(InterEmployeeJobDuty interEmployeeJobDuty);
    
    /**
     * 根据id逻辑删除
     * @Title: queryByCondition
     * @param: interEmployeeJobDuty      
     * @return: int 
     * @throws
     */
    @SqlStatement(params = "id,updatedBy")
    public int delInterEmployeeJobDutyById(String id, String updatedBy);
    
    /**
     * 批量逻辑删除
     * @Title: batchDelAppUserByIds
     * @param: ids
     * @param: updatedBy
     * @return: int 
     * @throws
     */
    @SqlStatement(params = "ids,updatedBy")
    public int batchDelInterEmployeeJobDutyByIds(List<String> ids, String updatedBy);
}

• InterEmployeeJobDutyService

package com.bossyang.beetsqlDemo.service;

import com.bossyang.beetsqlDemo.model.InterEmployeeJobDuty;
import org.beetl.sql.core.engine.PageQuery;

import java.util.List;

/**
 * InterEmployeeJobDuty Service
 * 
 * @author: 
 * @date: 2020年05月31日 16:24:13
 * @version V1.0
 */
public interface InterEmployeeJobDutyService {
    /**
     * 分页查询
     * @Title: queryByCondition
     * @param: pageNum 页码
     * @param: pageSize 每页数量
     * @param: InterEmployeeJobDuty 参数对象
     * @param: orderBy 排序方式
     * @return: pageQuery
     * @throws
     */
    public PageQuery<InterEmployeeJobDuty> queryByCondition(long pageNum, long pageSize, InterEmployeeJobDuty paramDto, String orderBy);
    
    /**
     * 根据条件查询，不分页
     * @Title: queryInterEmployeeJobDutyList
     * @param: interEmployeeJobDuty
     * @return: list
     * @throws
     */
    public List<InterEmployeeJobDuty> queryInterEmployeeJobDutyList(InterEmployeeJobDuty interEmployeeJobDuty);
    
    /**
     * 根据id查询InterEmployeeJobDuty
     * @Title: queryInterEmployeeJobDutyById  
     * @param: id      
     * @return: void 
     * @throws
     */
    public InterEmployeeJobDuty queryInterEmployeeJobDutyById(String id);
    
    /**
     * 保存InterEmployeeJobDuty
     * @Title: queryByCondition  
     * @param: interEmployeeJobDuty      
     * @throws
     */
    public void saveInterEmployeeJobDuty(InterEmployeeJobDuty interEmployeeJobDuty);
    
    /**
     * 更新InterEmployeeJobDuty
     * @Title: updateInterEmployeeJobDuty  
     * @param: interEmployeeJobDuty      
     * @return: boolean      
     * @throws
     */
    public boolean updateInterEmployeeJobDuty(InterEmployeeJobDuty interEmployeeJobDuty);
    
    /**
     * 逻辑删除InterEmployeeJobDuty
     * @Title: delInterEmployeeJobDutyById  
     * @param: id      
     * @param: updatedBy
     * @return: boolean 
     * @throws
     */
    public boolean delInterEmployeeJobDutyById(String id, String updatedBy);
    
    /**
     * 批量逻辑删除
     * @Title: batchDelInterEmployeeJobDutyByIds
     * @param: ids
     * @param: updatedBy
     * @return: boolean
     * @throws
     */
    public boolean batchDelInterEmployeeJobDutyByIds(List<String> ids, String updatedBy);
}

接口实现

在service下创建impl文件夹进行service的接口实现。

• InterEmployeeServiceImpl

package com.bossyang.beetsqlDemo.service.impl;

import com.bossyang.beetsqlDemo.common.MyPageQuery;
import com.bossyang.beetsqlDemo.model.InterEmployee;
import com.bossyang.beetsqlDemo.repository.InterEmployeeDao;
import com.bossyang.beetsqlDemo.service.InterEmployeeService;
import org.beetl.sql.core.SQLManager;
import org.beetl.sql.core.engine.PageQuery;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import javax.transaction.Transactional;
import java.util.Date;
import java.util.List;

/**
 * InterEmployee ServiceImpl
 *
 * @author:
 * @date: 2020年05月31日 13:43:25
 * @version V1.0
 */
@Service
public class InterEmployeeServiceImpl implements InterEmployeeService {
    @Autowired
    private SQLManager sqlManager;
    @Autowired
    private InterEmployeeDao interEmployeeDao;

    public PageQuery<InterEmployee> queryByCondition(long pageNum, long pageSize, InterEmployee paramDto, String orderBy) {
        PageQuery<InterEmployee> pageQuery = new PageQuery<InterEmployee>(pageNum, pageSize, paramDto);
        pageQuery.setOrderBy(orderBy);
        sqlManager.pageQuery("interEmployeeExtra.queryByCondition", InterEmployee.class, pageQuery);
        return pageQuery;
    }

    @Override
    public MyPageQuery<InterEmployee> querySummary(long pageNum, long pageSize, InterEmployee paramDto, String orderBy) {
        MyPageQuery<InterEmployee> myPageQuery = new MyPageQuery<>();
        PageQuery<InterEmployee> pageQuery = new PageQuery<InterEmployee>(pageNum, pageSize, paramDto);
        pageQuery.setOrderBy(orderBy);
        myPageQuery.setPageQuery(pageQuery);

        interEmployeeDao.pageQuery("queryByCondition", InterEmployee.class, myPageQuery);

        return myPageQuery;
    }

    public List<InterEmployee> queryInterEmployeeList(InterEmployee interEmployee) {
        return interEmployeeDao.queryByCondition(interEmployee);
    }

    public InterEmployee queryInterEmployeeById(String id) {
        return (InterEmployee) interEmployeeDao.single(id);
    }

    @Transactional
    public void saveInterEmployee(InterEmployee interEmployee) {
        if(StringUtils.isEmpty(interEmployee.getStatus())) {
            interEmployee.setStatus("1");
        }
        interEmployee.setCreatedTime(new Date());
        interEmployee.setUpdatedTime(new Date());
        interEmployeeDao.insert(interEmployee);
    }

    @Transactional
    public boolean updateInterEmployee(InterEmployee interEmployee) {
        interEmployee.setUpdatedTime(new Date());
        int result = interEmployeeDao.updateTemplateById(interEmployee);
        if(result > 0) {
            return true;
        } else {
            return false;
        }
    }

    @Transactional
    public boolean delInterEmployeeById(String id, String updatedBy) {
        int result = interEmployeeDao.delInterEmployeeById(id, updatedBy);
        if(result > 0) {
            return true;
        } else {
            return false;
        }
    }

    @Transactional
    public boolean batchDelInterEmployeeByIds(List<String> ids, String updatedBy){
        int result = interEmployeeDao.batchDelInterEmployeeByIds(ids, updatedBy);
        if(result > 0) {
            return true;
        } else {
            return false;
        }
    }
}

• InterEmployeeJobDutyServiceImpl

package com.bossyang.beetsqlDemo.service.impl;

import com.bossyang.beetsqlDemo.model.InterEmployeeJobDuty;
import com.bossyang.beetsqlDemo.repository.InterEmployeeJobDutyDao;
import com.bossyang.beetsqlDemo.service.InterEmployeeJobDutyService;
import org.beetl.sql.core.SQLManager;
import org.beetl.sql.core.engine.PageQuery;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.transaction.Transactional;
import java.util.List;

/**
 * InterEmployeeJobDuty ServiceImpl
 * 
 * @author: 
 * @date: 2020年05月31日 16:24:13
 * @version V1.0
 */
@Service
public class InterEmployeeJobDutyServiceImpl implements InterEmployeeJobDutyService {
    @Autowired
    private SQLManager sqlManager;
    @Autowired  
    private InterEmployeeJobDutyDao interEmployeeJobDutyDao;
    
    public PageQuery<InterEmployeeJobDuty> queryByCondition(long pageNum, long pageSize, InterEmployeeJobDuty paramDto, String orderBy) {
        PageQuery<InterEmployeeJobDuty> pageQuery = new PageQuery<InterEmployeeJobDuty>(pageNum, pageSize, paramDto);
        pageQuery.setOrderBy(orderBy);
        sqlManager.pageQuery("interEmployeeJobDutyExtra.queryByCondition", InterEmployeeJobDuty.class, pageQuery);
        return pageQuery;
    }
    
    public List<InterEmployeeJobDuty> queryInterEmployeeJobDutyList(InterEmployeeJobDuty interEmployeeJobDuty) {
        return interEmployeeJobDutyDao.queryByCondition(interEmployeeJobDuty);
    }
    
    public InterEmployeeJobDuty queryInterEmployeeJobDutyById(String id) {
        return interEmployeeJobDutyDao.single(id);
    }
    
    @Transactional
    public void saveInterEmployeeJobDuty(InterEmployeeJobDuty interEmployeeJobDuty) {

        interEmployeeJobDutyDao.insert(interEmployeeJobDuty);
    }
    
    @Transactional
    public boolean updateInterEmployeeJobDuty(InterEmployeeJobDuty interEmployeeJobDuty) {
        int result = interEmployeeJobDutyDao.updateTemplateById(interEmployeeJobDuty);
        if(result > 0) {
            return true;
        } else {
            return false;
        }
    }
    
    @Transactional
    public boolean delInterEmployeeJobDutyById(String id, String updatedBy) {
        int result = interEmployeeJobDutyDao.delInterEmployeeJobDutyById(id, updatedBy);
        if(result > 0) {
            return true;
        } else {
            return false;
        }
    }
    
    @Transactional
    public boolean batchDelInterEmployeeJobDutyByIds(List<String> ids, String updatedBy){
        int result = interEmployeeJobDutyDao.batchDelInterEmployeeJobDutyByIds(ids, updatedBy);
        if(result > 0) {
            return true;
        } else {
            return false;
        }
    }
}

ProtoBuffer

创建一个protobuf的文件夹，与resources、java平级

• 创建protobuf文件夹

• 创建protobuf文件

  • interEmployee.proto

  syntax = "proto3";
  package beetsqlDemo;
  option java_package = "com.bossyang.beetsqlDemo.proto.api";
  option java_outer_classname = "InterEmployeeProto";
  message InterEmployeeDto {
      string id = 1; //id
      string name = 2; //姓名
      string email = 3; //邮箱
      string sex = 4; //性别 0-男；1-女；2-未知
      string deptId = 5; //所属部门id
      string orgId = 6; //所属机构id
      string status = 7; //状态 0-无效；1-有效
      string createdBy = 8; //创建人
      string createdTime = 9; //创建时间
      string updatedBy = 10; //更新人
      string updatedTime = 11; //更新时间
  }
  message InterEmployeeQueryReq {
      int64 page = 1; //页码
      int64 size = 2; //每页数量
      string id = 3; //id
      string name = 4; //姓名
      string email = 5; //邮箱
      string sex = 6; //性别 0-男；1-女；2-未知
      string deptId = 7; //所属部门id
      string orgId = 8; //所属机构id
      string status = 9; //状态 0-无效；1-有效
      string createdBy = 10; //创建人
      string createdTime = 11; //创建时间
      string updatedBy = 12; //更新人
      string updatedTime = 13; //更新时间
      repeated string jobDuties = 14;//员工岗位集合
  }
  message InterEmployeeQueryRes {
      int64 total = 1; //总数
      int64 size = 2; //当前列表大小
      repeated InterEmployeeDto list=3;//列表
      int64 empNums = 4;//员工数量
  }
  

  • interEmployeeJobDuty.proto

syntax = "proto3";
package beetsqlDemo;
option java_package = "com.bossyang.beetsqlDemo.proto.api";
option java_outer_classname = "InterEmployeeJobDutyProto";
message InterEmployeeJobDutyDto {
    string id = 1; //id
    string empId = 2; //员工ID
    string empJob = 3; //员工岗位 CODE-开发；HR-人事经理
    string jobName = 4; //岗位名称 CODE-开发；HR-人事经理
}
message InterEmployeeJobDutyQueryReq {
    int64 page = 1; //页码
    int64 size = 2; //每页数量
    string id = 3; //id
    string empId = 4; //员工ID
    string empJob = 5; //员工岗位 CODE-开发；HR-人事经理
    string jobName = 6; //岗位名称 CODE-开发；HR-人事经理
}
message InterEmployeeJobDutyQueryRes {
    int64 total = 1; //总数
    int64 size = 2; //当前列表大小
    repeated InterEmployeeJobDutyDto list=3;//列表
}

业务实现

创建api文件夹进行业务实现以及前端交互

业务Service

业务接口定义

在api下面创建service文件夹，进行业务的接口定义

• InterEmployeeApiService

package com.bossyang.beetsqlDemo.api.service;

import com.alibaba.fastjson.JSONObject;
import com.bossyang.beetsqlDemo.common.BaseResponse;
import com.bossyang.beetsqlDemo.model.InterEmployee;
import com.bossyang.beetsqlDemo.proto.api.InterEmployeeProto;
import com.google.protobuf.InvalidProtocolBufferException;

/**
 * InterEmployee ApiService
 * 解析&校验&组装
 * @author: 
 * @date: 2020年05月31日 13:43:25
 * @version V1.0
 */
public interface InterEmployeeApiService {
    /**
     * 根据条件查询
     * @param queryReqProto
     * @return
     */
    public BaseResponse<JSONObject> queryInterEmployees(InterEmployeeProto.InterEmployeeQueryReq queryReqProto) throws InvalidProtocolBufferException;
    /**
     * 根据条件查询
     * @param queryReqProto
     * @return
     */
    public BaseResponse<JSONObject> test(InterEmployeeProto.InterEmployeeQueryReq queryReqProto) throws InvalidProtocolBufferException;
    /**
     * 根据id查询
     * @param id
     * @return
     */
    public BaseResponse<JSONObject> queryInterEmployeeById(String id);
    /**
     * 保存或更新
     * @param interEmployee
     * @return
     */
    public BaseResponse<JSONObject> saveOrUpdateInterEmployee(InterEmployee interEmployee);
    /**
     * 根据id逻辑删除
     * @param id
     * @return
     */
    public BaseResponse<JSONObject> delInterEmployeeById(String id);
    /**
     * 根据id批量逻辑删除
     * @param ids
     * @return
     */
    public BaseResponse<JSONObject> batchDelInterEmployeeByIds(String ids);
}

业务接口实现

在service下面创建impl文件夹，进行业务接口的实现

• InterEmployeeApiServiceImpl

package com.bossyang.beetsqlDemo.api.service.impl;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.bossyang.beetsqlDemo.api.service.InterEmployeeApiService;
import com.bossyang.beetsqlDemo.common.BaseResponse;
import com.bossyang.beetsqlDemo.common.MyPageQuery;
import com.bossyang.beetsqlDemo.model.InterEmployee;
import com.bossyang.beetsqlDemo.proto.api.InterEmployeeProto;
import com.bossyang.beetsqlDemo.service.InterEmployeeService;
import com.bossyang.beetsqlDemo.util.UuidUtil;
import com.google.protobuf.InvalidProtocolBufferException;
import com.google.protobuf.util.JsonFormat;
import org.beetl.sql.core.engine.PageQuery;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.util.StringUtils;

import java.util.Arrays;


/**
 * InterEmployee ApiServiceImpl
 * 
 * @author: 
 * @date: 2020年05月31日 16:28:46
 * @version V1.0
 */
@Component
public class InterEmployeeApiServiceImpl implements InterEmployeeApiService {
    private static JsonFormat.Parser parser = JsonFormat.parser().ignoringUnknownFields();
    private static JsonFormat.Printer printer = JsonFormat.printer().preservingProtoFieldNames().includingDefaultValueFields();
    private final Logger logger = LoggerFactory.getLogger(this.getClass());
    
    @Autowired
    private InterEmployeeService interEmployeeService;
    
    @Override
    public BaseResponse<JSONObject> queryInterEmployees(InterEmployeeProto.InterEmployeeQueryReq queryReqProto) throws InvalidProtocolBufferException {
        InterEmployee interEmployee = JSONObject.parseObject(JsonFormat.printer().print(queryReqProto), InterEmployee.class);
        String orderBy = " t.updated_time desc ";
        PageQuery<InterEmployee> pageQuery = interEmployeeService.queryByCondition(queryReqProto.getPage(), queryReqProto.getSize(), interEmployee, orderBy);
        InterEmployeeProto.InterEmployeeQueryRes.Builder interEmployeeRes = InterEmployeeProto.InterEmployeeQueryRes.newBuilder();
        pageQuery.getList().forEach(dto -> {
            try {
                InterEmployeeProto.InterEmployeeDto.Builder protoDto = InterEmployeeProto.InterEmployeeDto.newBuilder();
                parser.merge(JSONObject.toJSONStringWithDateFormat(dto, "yyyy-MM-dd HH:mm:ss"), protoDto);
                interEmployeeRes.addList(protoDto);
            } catch (InvalidProtocolBufferException e) {
                logger.error("查询结果转proto转换异常：", e);
                e.printStackTrace();
            }
        });
        interEmployeeRes.setSize(pageQuery.getPageSize());
        interEmployeeRes.setTotal(pageQuery.getTotalRow());
        return BaseResponse.setResultSuccess(JSONObject.parseObject(printer.print(interEmployeeRes.build())));
    }

    @Override
    public BaseResponse<JSONObject> test(InterEmployeeProto.InterEmployeeQueryReq queryReqProto) throws InvalidProtocolBufferException {
        InterEmployee interEmployee = JSONObject.parseObject(JsonFormat.printer().print(queryReqProto), InterEmployee.class);
        String orderBy = " t.updated_time desc ";
        MyPageQuery<InterEmployee> myPageQuery = interEmployeeService.querySummary(queryReqProto.getPage(), queryReqProto.getSize(), interEmployee, orderBy);
        InterEmployeeProto.InterEmployeeQueryRes.Builder interEmployeeRes = InterEmployeeProto.InterEmployeeQueryRes.newBuilder();
        PageQuery pageQuery = myPageQuery.getPageQuery();
        pageQuery.getList().forEach(dto -> {
            try {
                InterEmployeeProto.InterEmployeeDto.Builder protoDto = InterEmployeeProto.InterEmployeeDto.newBuilder();
                parser.merge(JSONObject.toJSONStringWithDateFormat(dto, "yyyy-MM-dd HH:mm:ss"), protoDto);
                interEmployeeRes.addList(protoDto);
            } catch (InvalidProtocolBufferException e) {
                logger.error("查询结果转proto转换异常：", e);
                e.printStackTrace();
            }
        });
        interEmployeeRes.setEmpNums(myPageQuery.getSummaryMap().get("empNums"));
        interEmployeeRes.setSize(pageQuery.getPageSize());
        interEmployeeRes.setTotal(pageQuery.getTotalRow());
        return BaseResponse.setResultSuccess(JSONObject.parseObject(printer.print(interEmployeeRes.build())));
    }

    @Override
    public BaseResponse<JSONObject> queryInterEmployeeById(String id) {
        InterEmployee interEmployee = interEmployeeService.queryInterEmployeeById(id);
        return BaseResponse.setResultSuccess(JSONObject.parseObject(JSON.toJSONStringWithDateFormat(interEmployee, "yyyy-MM-dd HH:mm:ss")));
    }
    
    @Override
    public BaseResponse<JSONObject> saveOrUpdateInterEmployee(InterEmployee interEmployee) {
        //校验参数
        if(StringUtils.isEmpty(interEmployee.getId())) {//保存
            interEmployee.setId(UuidUtil.getUUID32());
            interEmployee.setCreatedBy("admin");
            interEmployee.setOrgId(UuidUtil.getUUID32());
            interEmployeeService.saveInterEmployee(interEmployee);
        } else {//更新
            interEmployeeService.updateInterEmployee(interEmployee);
        }
        return BaseResponse.setResultSuccess(JSONObject.parseObject(JSON.toJSONStringWithDateFormat(interEmployee, "yyyy-MM-dd HH:mm:ss")));
    }
    
    @Override
    public BaseResponse<JSONObject> delInterEmployeeById(String id) {
        interEmployeeService.delInterEmployeeById(id,"admin");
        return BaseResponse.setResultSuccess();
    }
    
    @Override
    public BaseResponse<JSONObject> batchDelInterEmployeeByIds(String ids) {
        interEmployeeService.batchDelInterEmployeeByIds(Arrays.asList(ids.split(",")), "admin");
        return BaseResponse.setResultSuccess();
    }


}

Controller

在api文件夹下面创建controller文件夹

• InterEmployeeController

package com.bossyang.beetsqlDemo.api.controller;

import com.alibaba.fastjson.JSONObject;
import com.bossyang.beetsqlDemo.api.service.InterEmployeeApiService;
import com.bossyang.beetsqlDemo.common.BaseResponse;
import com.bossyang.beetsqlDemo.model.InterEmployee;
import com.bossyang.beetsqlDemo.proto.api.InterEmployeeProto;
import com.google.protobuf.InvalidProtocolBufferException;
import com.google.protobuf.util.JsonFormat;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
/**
 * InterEmployee 接口
 * 
 * @author: 
 * @date: 2020年05月31日 16:28:46
 * @version V1.0
 */
@RestController
@RequestMapping("/api")
public class InterEmployeeController {
    private final Logger logger = LoggerFactory.getLogger(this.getClass());
    private static JsonFormat.Parser parser = JsonFormat.parser().ignoringUnknownFields();
    @Autowired
    private InterEmployeeApiService interEmployeeApiService;
    
    private static final String ENTITY_NAME = "interEmployee";

    /**
     * 查询机构员工信息
     */
    @PostMapping(value = "/interEmployee/list")
    public BaseResponse<JSONObject> queryInterEmployees(@RequestBody String reqJson) throws InvalidProtocolBufferException {
        if(StringUtils.isEmpty(reqJson)) {
            return BaseResponse.lossParam("reqJson");
        }
        InterEmployeeProto.InterEmployeeQueryReq.Builder queryReqProto = InterEmployeeProto.InterEmployeeQueryReq.newBuilder();
        try {
            parser.merge(reqJson, queryReqProto);
        } catch (InvalidProtocolBufferException e) {
            e.printStackTrace();
            logger.error("请求Json转换异常", e);
            return BaseResponse.setResultError("查询异常");
        }
        return interEmployeeApiService.queryInterEmployees(queryReqProto.build());
    }

    /**
     * 查询机构员工信息
     */
    @PostMapping(value = "/interEmployee/listTest")
    public BaseResponse<JSONObject> queryInterEmployeesTest(@RequestBody String reqJson) throws InvalidProtocolBufferException {
        if(StringUtils.isEmpty(reqJson)) {
            return BaseResponse.lossParam("reqJson");
        }
        InterEmployeeProto.InterEmployeeQueryReq.Builder queryReqProto = InterEmployeeProto.InterEmployeeQueryReq.newBuilder();
        try {
            parser.merge(reqJson, queryReqProto);
        } catch (InvalidProtocolBufferException e) {
            e.printStackTrace();
            logger.error("请求Json转换异常", e);
            return BaseResponse.setResultError("查询异常");
        }
        return interEmployeeApiService.test(queryReqProto.build());
    }

    @GetMapping(value = "/interEmployee/info/{id}")
    public BaseResponse<JSONObject> queryInterEmployeeById(@PathVariable String id) {
        return interEmployeeApiService.queryInterEmployeeById(id);
    }

    @PostMapping(value = "/interEmployee/save")
    public BaseResponse<JSONObject> saveInterEmployee(@RequestBody InterEmployee interEmployee) {
        return interEmployeeApiService.saveOrUpdateInterEmployee(interEmployee);
    }
    @PutMapping(value = "/interEmployee/update")
    public BaseResponse<JSONObject> updateInterEmployee(@RequestBody InterEmployee interEmployee) {
        return interEmployeeApiService.saveOrUpdateInterEmployee(interEmployee);
    }

    @DeleteMapping(value = "/interEmployee/del/{id}")
    public BaseResponse<JSONObject> delInterEmployee(@PathVariable String id) {
        return interEmployeeApiService.delInterEmployeeById(id);
    }
}

运行

启动运行application

测试

本案例采用DocwayAPI接口管理平台存储和定义接口，具体接口如下：

• 运行查询接口进行测试

响应结果：

后台运行结果：