JDBI SQL对象查询中的动态顺序 [英] Dynamic Order in JDBI SQL Object Queries
问题描述
如何在JDBI中使用SQL对象查询进行排序？
我想做类似的事情：
< pre class =lang-java prettyprint-override>
@SqlQuery（
SELECT * FROM users+
WHERE something =：something+
ORDER BY：orderBy：orderDir
）
List< User> getUsers（
@Bind（something）整数
，@ BondOrderBy（orderBy）String orderBy
，@ LabelOrderDir（orderDir）String orderDir
）;
或
@SqlQuery（
SELECT * FROM users+
WHERE something =：something+
ORDER BY：orderBy：orderDir
）
列表<用户> getUsers（
@Bind（something）整数
，@ bind（orderBy）OrderBy orderBy
，@ Bind（orderDir）OrderDir orderDir
）;
我最近一直在探索捆绑的DropWizard JDBI很快遇到了同样的问题。不幸的是，JDBI的文档乏善可陈（JavaDoc和它的git存储库上的一些样本单元测试并没有单独测试）这令人失望。
这是我发现的实现动态的基于我的示例DAO在JDBI的Sql Object API中排序：
@ UseStringTemplate3StatementLocator
public interface ProductsDao {
@RegisterMapperFactory（BeanMapperFactory.class）//将查询结果映射到产品POJO（Beans）列表
@SqlQuery（select * from products order by< orderby>< ; order> limit：limit offset：offset）
List< Product> getProducts（@Define（orderby）String orderBy，@ Define（order）字符串顺序，
@Bind（limit）int limit，@ Label（offset）int offset）;
@SqlQuery（从产品中选择计数（*））
int getProductsCount（）;
}
@ UseStringTemplate3StatementLocator - 此批注允许我们在查询中使用< arg> 语法。这些参数将被我们通过 @Define 注释提供的任何值替换。
为了能够使用此功能，我不得不另外将此依赖项添加到我的 pom.xml 文件中：
< dependency>
< groupId> antlr< / groupId>
< artifactId> stringtemplate< / artifactId>
< version> 2.3b6< / version> <！ - 我不确定是否要使用此特定版本 - >
< / dependency>
SQL INJECTION WARNING
应该注意这打开我们最多 Sql Injection ，因为这些值直接插入到查询中。 （与查询中的：arg 语法相对应， @Bind 注释使用预准备语句并防止sql注入） 。至少应该清理将用于 @Define 字段的参数。 （下面的DropWizard的简单示例）。
@Path（/ products）
@Produces（MediaType.APPLICATION_JSON） ）
public class ProductsResource {
private static ImmutableSet< String> orderByChoices = ImmutableSet.of（id，name，price，manufacturerDate）;
私人最终产品道道;
public ProductsResource（ProductsDao dao）{
this.dao = dao;
}
@GET
//使用@InjectParam将许多查询参数绑定到POJO（Bean）。
// https://jersey.java.net/apidocs/1.17/jersey/com/sun/jersey/api/core/InjectParam.html
//即公开列表<产品> index（@InjectParam ProductsRequest请求）
//也使用自定义Java类型来使用请求参数。这允许在索引方法之外移动这种验证/清理逻辑。
// https://jersey.java.net/documentation/1.17/jax-rs.html#d4e260
public List< Product> index（@DefaultValue（id）@ QueryParam（orderby）String orderBy，
@DefaultValue（asc）@ QueryParam（order）字符串顺序，
@DefaultValue（20 ）@QueryParam（perpage）IntParam perpage，
@DefaultValue（0）@ QueryParam（page）IntParam page）
int limit，offset;
order = order.toLowerCase（）;
orderBy = orderBy.toLowerCase（）;
if（！orderByChoices.contains（orderBy））orderBy =id; //清理< orderby>
if（order！=asc&& order！=desc）order =asc; //清理< order>
limit = perpage.get（）;
offset = page.get（）< 0？ 0：page.get（）* limit;
返回dao.getProducts（orderBy，order，limit，offset）;
}
}
How do you do ordering with SQL Object Queries in JDBI?
I want to do something like:
@SqlQuery(
"SELECT * FROM users " +
"WHERE something = :something " +
"ORDER BY :orderBy :orderDir"
)
List<User> getUsers(
@Bind("something") Integer something
, @BindOrderBy("orderBy") String orderBy
, @BindOrderDir("orderDir") String orderDir
);
or
@SqlQuery(
"SELECT * FROM users " +
"WHERE something = :something " +
"ORDER BY :orderBy :orderDir"
)
List<User> getUsers(
@Bind("something") Integer something
, @Bind("orderBy") OrderBy orderBy
, @Bind("orderDir") OrderDir orderDir
);
I've recently been exploring DropWizard which comes bundled with JDBI and quickly came across the same problem. Unfortunately JDBI has lackluster documentation (JavaDoc and some sample unit tests on it's git repository don't cut it alone) which is disappointing.
Here's what I found that achieves a dynamic order in a Sql Object API for JDBI based on my sample DAO:
@UseStringTemplate3StatementLocator
public interface ProductsDao {
@RegisterMapperFactory(BeanMapperFactory.class) // will map the result of the query to a list of Product POJOs(Beans)
@SqlQuery("select * from products order by <orderby> <order> limit :limit offset :offset")
List<Product> getProducts(@Define("orderby") String orderBy, @Define("order") String order,
@Bind("limit") int limit, @Bind("offset") int offset);
@SqlQuery("select count(*) from products")
int getProductsCount();
}
@UseStringTemplate3StatementLocator - this annotation is what allows us to use the <arg> syntax in the queries. These args are going to be replaced with whatever value we provide via the @Define annotation.
To be able to use this feature I had to additionally add this dependency to my pom.xml file:
<dependency>
<groupId>antlr</groupId>
<artifactId>stringtemplate</artifactId>
<version>2.3b6</version> <!-- I am not sure if this specific version is meant to be used though -->
</dependency>
SQL INJECTION WARNING
It should be noted that this opens us up to Sql Injection since the values are directly inserted to the query. (In contstrast to :arg syntax in the query and @Bind annotation which uses prepared statements and protects against sql injection). At the very least you should sanitize the parameters that are going to be used for the @Define fields. (Simple example for DropWizard below).
@Path("/products")
@Produces(MediaType.APPLICATION_JSON)
public class ProductsResource {
private static ImmutableSet<String> orderByChoices = ImmutableSet.of("id", "name", "price", "manufactureDate");
private final ProductsDao dao;
public ProductsResource(ProductsDao dao) {
this.dao = dao;
}
@GET
// Use @InjectParam to bind many query parameters to a POJO(Bean) instead.
// https://jersey.java.net/apidocs/1.17/jersey/com/sun/jersey/api/core/InjectParam.html
// i.e. public List<Product> index(@InjectParam ProductsRequest request)
// Also use custom Java types for consuming request parameters. This allows to move such validation/sanitization logic outside the 'index' method.
// https://jersey.java.net/documentation/1.17/jax-rs.html#d4e260
public List<Product> index(@DefaultValue("id") @QueryParam("orderby") String orderBy,
@DefaultValue("asc") @QueryParam("order") String order,
@DefaultValue("20") @QueryParam("perpage") IntParam perpage,
@DefaultValue("0") @QueryParam("page") IntParam page)
int limit, offset;
order = order.toLowerCase();
orderBy = orderBy.toLowerCase();
if (!orderByChoices.contains(orderBy)) orderBy = "id"; //sanitize <orderby>
if (order != "asc" && order != "desc") order = "asc"; //sanitize <order>
limit = perpage.get();
offset = page.get() < 0 ? 0 : page.get() * limit;
return dao.getProducts(orderBy, order, limit, offset);
}
}
这篇关于JDBI SQL对象查询中的动态顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
