# jqlquery

**Repository Path**: albb0608/jqlquery

## Basic Information

- **Project Name**: jqlquery
- **Description**: 基于javascript配置查询sql（类似mybatis的xml）并通过接口声明调用的数据库查询工具
- **Primary Language**: Java
- **License**: Apache-2.0
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 6
- **Created**: 2023-02-04
- **Last Updated**: 2023-02-04

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

# jqlquery

#### 介绍
基于javascript配置查询sql（类似mybatis的xml）并通过接口调用的数据库查询框架。

适用项目：基于springboot jpa的项目


#### 使用步骤

在jpa的基础上：


1.添加依赖

```xml

<dependency>
    <groupId>cc.iotkit</groupId>
    <artifactId>jqlquery</artifactId>
    <version>0.0.1-SNAPSHOT</version>
</dependency>
```


2.初始化配置对象和配置扫描包

```java
@Configuration
public class AutoBeanConfiguration {

    @Bean
    public JqlQueryConfiguration getJqlQueryConfiguration() {
        return new JqlQueryConfiguration("cc.iotkit.sws.dao");
    }

}
```

3.添加sql配置

在resources目录中创建第2步中配置的扫描包文件夹:
cc/iotkit/sws/dao

创建文件: WorkFlowQuery.js

添加sql配置

```javascript

function getWorkFlows(id){
    var sql=`
    SELECT a.id,a.title,a.status,b.work_result
    from work_order a JOIN work_flow b ON a.id=b.work_id
    where a.id=:id
    `;

    return sql;
}

function getWorkFlows2(param){
    var sql=`
    SELECT a.id,a.title,a.status,b.work_result
    from work_order a JOIN work_flow b ON a.id=b.work_id
    where a.id=:id
    `;

    return sql;
}

function getWorkFlowTotal(id){
    var sql=`
    SELECT count(*) from work_order a JOIN work_flow b ON a.id=b.work_id
    where a.id=:id
    `;

    return sql;
}


```

 ##### 注：可根据入参使用js语法，任意构造动态组合条件的sql，画面请自行脑补 :smirk: 

 **举个例子：** 

mybatis写法：

```xml
    <select id="findSqlWhere" resultType="User">
        select * from demo_user
            <where>
                <if test="id != null"> id = #{id}</if>
                <if test="name != null">and name = #{name}</if>
                <if test="age != null ">and age  = #{age}</if>
                <if test="sex != null ">and sex  = #{sex}</if>
            </where>
    </select>
```
js写法：

```javascript
function findSqlWhere(user){
    var sql="select * from demo_user where 1=1 ";
    sql+=user.id?"and id=:id":"";
    sql+=user.name?"and name=:name":"";
    sql+=user.age?"and age=:age":"";
    sql+=user.sex?"and sex=:sex":"";
    return sql;
}
```





4.在扫描包中添加接口类

WorkFlowQuery.java


```java
@JqlQuery
public interface WorkFlowQuery {

    List<WorkFlowVo> getWorkFlows(String id);

    List<WorkFlowVo> getWorkFlows2(WorkFlowVo param);

    long getWorkFlowTotal(String id);

}

```

5.调用

在需要调用的类中注入WorkFlowQuery调用：

```java
    @Autowired
    private WorkFlowQuery workFlowQuery;

    
    workFlowQuery.getWorkFlows("1")

    WorkFlowVo param = new WorkFlowVo();
    param.setId("1");
    workFlowQuery.getWorkFlows2(param);

    workFlowQuery.getWorkFlowTotal("1")
```

## 示例
### 动态条件+分页


JS: AlarmQuery.js

```javascript
function getAlarmWhere(alarm){
    var where=" where 1=1 ";
    where+=alarm.state?" and state=:state":"";
    where+=alarm.projectName?" and project_name like :projectName":"";
    where+=alarm.level?" and level=:level":"";
    return where;
}

function queryAlarms(alarm,start,size){
    var sql=`
    select * from alarm ${this.getAlarmWhere(alarm)}
    order by create_at desc
    limit :start,:size
    `;
    return sql;
}

function countAlarms(alarm){
    var sql="select count(*) from alarm ${this.getAlarmWhere(alarm)}";
    return sql;
}

```
sql写法示例2：

```javascript

function getBuildingEpidemics(tour_code, return_hometown, community_id, page_size, page_num){
    var cond="";
    cond+=tour_code?"tour_code=:tour_code and ":"";
    cond+=return_hometown?"return_hometown=:return_hometown and":"";

    var sql=`
        select
            a.id,a.tour_code,a.temperature,a.return_hometown,a.created_at,
            b.type,b.area,b.building,b.unit,b.number
        from ejyy_epidemic a left join ejyy_building_info b on a.building_id=b.id
        where ${cond} a.community_id=:community_id
        order by a.id desc
        limit ${(page_num-1)*page_size},${page_size}
    `;

    return sql;
}

```



java：AlarmQuery.java

```java
@JqlQuery
public interface AlarmQuery {

    List<Alarm> queryAlarms(Alarm param, int start, int size);

    long countAlarms(Alarm param);

}
```
java：controller

```
@PostMapping("/alarms/{size}/{page}")
public Paging<Alarm> getAlarms(
        @PathVariable("size") int size,
        @PathVariable("page") int page,
        int level,
        String state,
        String projectName) {

    Alarm param = Alarm.builder()
            .level(level)
            .state(state)
            .projectName("%" + projectName + "%")
            .build();

    return new Paging<>(alarmQuery.countAlarms(param),
            alarmQuery.queryAlarms(param, (page - 1) * size, size));
}
```