4年前 (2021-06-08)  相关技术 |   抢沙发  293 
文章评分 0 次，平均分 0.0 ：
[收起] 文章目录

关于spring-native-query

使用Java对关系数据库运行本机查询常常会使源代码变得混乱和繁杂，因为过滤条件太多，而且表绑定也发生了变化。

因此，我决定创建“Spring Native Query”库，以方便执行本机查询，重点是简化源代码，使其更具可读性和干净性，创建包含本机查询的文件，并动态注入assets以执行这些查询。

该库的思想是运行约定查询，类似于Spring数据，并且只用于springboot和Spring Data Jpa。

在创建扩展NativeQuery接口的新接口时，我们从这些接口创建假对象，使用代理拦截方法调用并执行查询，最后动态注册这些接口的bean，以便将这些接口注入到Spring的所有组件中。

约定的工作方式如下：方法名是包含sql查询的文件名，方法的参数将作为参数传递给实体管理器，方法返回是将使用查询返回的结果转换的对象。

包含SQL查询的文件是Jtwig模板，我们可以在其中应用验证修改整个查询、添加过滤器、更改表之间的链接，最后是SQL中的任何更改。

默认情况下，必须将本机查询文件添加到资源文件夹中名为“nativeQuery”的文件夹中。请记住，文件名必须与方法名相同。

在上面的1.0.28版本中，我们开始使用Hibernate类型来正确转换数据类型，对于以前的版本，请考虑通过下面的配置禁用Hibernate类型的使用。

native-query.use-hibernate-types=false

代码示例

下面是一些更好理解的例子。让我们创建一个springboot项目，其中包含依赖项、springdatajpa和H2数据库。在启动项目时，让我们通过创建一个新表并插入一些记录来创建一个sql脚本。所有示例源代码都在github中。

在您的项目中添加库的依赖项，让我们以maven为例。

<dependency>
    <groupId>io.github.gasparbarancelli</groupId>
    <artifactId>spring-native-query</artifactId>
    <version>1.0.28</version>
</dependency>

在资源文件夹中创建一个名为data.sql的文件并插入脚本。

CREATE TABLE USER (
  cod INT NOT NULL,
  full_name VARCHAR(45) NULL,
  active INT NULL,
  PRIMARY KEY (cod)
);

INSERT INTO USER (cod, full_name, active)
VALUES (1, 'Gaspar', 1),
       (2, 'Elton', 1),
       (3, 'Lucini', 1),
       (4, 'Diogo', 1),
       (5, 'Daniel', 1),
       (6, 'Marcos', 1),
       (7, 'Fernanda', 1),
       (8, 'Maicon', 1),
       (9, 'Rafael', 0);

首先在配置文件中定义项目的包扫描，支持文件application.properties、bootstrap.properties、application.yaml、application.yml、bootstrap.yml和bootstrap.yaml。

如果使用属性文件

native-query.package-scan=io.github.gasparbarancelli.demospringnativequery
native-query.file.sufix=sql
native-query.use-hibernate-types=false

如果使用yml文件

native-query:
  package-scan: io.github.gasparbarancelli.demospringnativequery
  file:
    sufix: sql
  use-hibernate-types: false

我们还可以通过编程方式定义并实现接口NativeQueryConfig。

import io.github.gasparbarancelli.NativeQueryConfig;

public class NativeQueryDefaultConfig implements NativeQueryConfig {

    @Override
    public String getPackageScan() {
        return "io.github.gasparbarancelli.demospringnativequery";
    }

    @Override
    public String getFileSufix() {
        return "sql";
    }
    
    @Override
    public boolean getUseHibernateTypes() {
        return false;
    }

}

UserTO文件示例

import lombok.*;

@Data
public class UserTO {

  private Number id;
  private String name;

}

UserTO文件示例

import io.github.gasparbarancelli.NativeQueryOperator;
import io.github.gasparbarancelli.NativeQueryParam;
import lombok.*;

@Data
public class UserFilter {
  private Number id;
  
  // Custom operator, when add parameter value in query and jwitg, the paramter is transformed
  @NativeQueryParam(value = "name", operator = NativeQueryOperator.CONTAINING)
  private String name;

}

UserNativeQUery文件示例

import io.github.gasparbarancelli.NativeQuery;
import io.github.gasparbarancelli.NativeQueryParam;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;

import java.util.List;


public interface UserNativeQuery extends NativeQuery {

  List<UserTO> findUsers();

  // When using the NativeQuerySql annotation it is not necessary to have the file containing the sql statement
  @NativeQuerySql("SELECT cod as \"id\", full_name as \"name\" FROM USER")
  List<UserTO> findBySqlInline();

  List<UserTO> findWithMap(Map<String, Object> params);
  
  // Add fields children of parameter
  List<UserTO> findUsersByFilter(@NativeQueryParam(value = "filter", addChildren = true) UserFilter filter);
  
  // Add pagination
  List<UserTO> findActiveUsers(Pageable pageable);

  // Ordering
  List<UserTO> findActiveUsersWithSort(Sort sort);

  // Add pagination and return object with values for the pagination (count, page, size)
  Page<UserTO> findActiveUsersWithPage(Pageable pageable);
  
  // Custom parameter name
  UserTO findUserById(@NativeQueryParam(value = "codigo") Number id);
  
  List<Number> getUsersId();
  
  String getUserName(Number id);
  
  Optional<String> getOptionalUserName(Number id);
  
  Optional<UserTO> findOptionalUserById(@NativeQueryParam(value = "codigo") Number id);
  
}

findUsers.sql文件示例

SELECT cod as "id", full_name as "name" FROM USER

findWithMap.sql文件示例

SELECT cod as "id", full_name as "name" FROM USER
WHERE 1=1
/* for item in params */
AND  = :
/* endfor */

findUsersByFilter.sql文件示例，仅在变量不为null时添加参数

SELECT cod as "id", full_name as "name" FROM USER
WHERE 1=1
/* if (filterId != null) */
AND cod = :filterId
/* endif  */
/* if (filterName != null) */
AND full_name like :filterName
/* endif  */

FindActiveUsers.sql文件示例

SELECT cod as "id", full_name as "name" FROM USER WHERE ACTIVE = true

FindActiveUserWithPage.sql文件示例

SELECT cod as "id", full_name as "name" FROM USER WHERE ACTIVE = true

FindActiveUserWithSort.sql文件示例

SELECT cod as "id", full_name as "name" FROM USER WHERE ACTIVE = true

findUserById.sql文件示例

SELECT cod as "id", full_name as "name" FROM USER WHERE cod = :codigo

getUsersId.sql文件示例

SELECT cod as "id" FROM USER

getUserName.sql文件示例

SELECT full_name as "name" FROM USER WHERE cod = :id

getOptionalUserName.sql文件示例

SELECT full_name as "name" FROM USER WHERE cod = :id

findOptionalUserById.sql文件示例

SELECT cod as "id", full_name as "name" FROM USER WHERE cod = :codigo

UserController示例

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Sort;
import org.springframework.web.bind.annotation.*;

import java.util.List;

@RestController
@RequestMapping("user")
public class UserController {

  @Autowired private UserNativeQuery userNativeQuery;
  
  @GetMapping()
  public List<UserTO> findUsers() {
    return userNativeQuery.findUsers();
  }
  
  @GetMapping("inline")
  public List<UserTO> findBySqlInline() {
    return userNativeQuery.findBySqlInline();
  }
  
  @GetMapping("map")
  public List<UserTO> findWithMap() {
    Map<String, Object> map = new HashMap<>();
    map.put("cod", 1);
    map.put("full_name", "Gaspar");
    return userNativeQuery.findWithMap(map);
  }

  @PostMapping("filter")
  public List<UserTO> findUsers(@RequestBody UserFilter filter) {
    return userNativeQuery.findUsersByFilter(filter);
  }
  
  @GetMapping("active")
  public List<UserTO> findUsers(
          @RequestParam(value = "page", defaultValue = "0", required = false) int page,
          @RequestParam(value = "size", defaultValue = "10", required = false) int size) {
    return userNativeQuery.findActiveUsers(PageRequest.of(page, size));
  }
  
  @GetMapping("activeWithPage")
  public Page<UserTO> findActiveUsersWithPage(
          @RequestParam(value = "page", defaultValue = "0", required = false) int page,
          @RequestParam(value = "size", defaultValue = "5", required = false) int size) {
    return userNativeQuery.findActiveUsersWithPage(PageRequest.of(page, size));
  }
 
  @GetMapping("activeWithSort")
  public List<UserTO> findActiveUsersWithSort(
        @RequestParam(value = "columnName") String columnName) {
    return userNativeQuery.findActiveUsersWithSort(Sort.by(columnName));
  }

  
  @GetMapping("{id}")
  public UserTO findUsers(@PathVariable("id") Number id) {
    return userNativeQuery.findUserById(id);
  }
  
  @GetMapping("ids")
  public List<Number> getIds() {
    return userNativeQuery.getUsersId();
  }
  
  @GetMapping("{id}/name")
  public String getUserName(@PathVariable("id") Number id) {
    return userNativeQuery.getUserName(id);
  }

  @GetMapping("{id}/optional/name")
  public Optional<String> getOptionalUserName(@PathVariable("id") Number id) {
    return userNativeQuery.getOptionalUserName(id);
  }

  @GetMapping("{id}/optional")
  public Optional<UserTO> findOptionalUser(@PathVariable("id") Number id) {
    return userNativeQuery.findOptionalUserById(id);
  }


}

完整代码地址：https://github.com/gasparbarancelli/spring-native-query

  
 

除特别注明外，本站所有文章均为老K的Java博客原创，转载请注明出处来自https://javakk.com/1994.html

关于
上一篇  
下一篇  

发表评论

表情 格式

暂无评论

登录

忘记密码 ?

切换登录

注册

繁