英文:
How to cast string to IP Address in Spring Data JPA Query
问题
我有一个包含IP地址范围的表，其中包含其起始和结束值的单独列。这些列的数据类型是varchar。我需要查询数据库，以获取范围覆盖该IP地址的行。
以下是在PGSQL中完美运行的查询：
select * from ip_whitelist iw where iw.ip_address::inet <= '1.1.1.54'::inet and iw.ip_address_end::inet >= '1.1.1.54'::inet
这里的1.1.1.54只是一个虚拟值，在我的JPA查询中将被变量替换：
以下是我如何通过JPA Repository运行此查询的方式：
@Query("select ip from AllowedIp ip where CAST(ip.ipAddress AS inet) <= CAST(?1 AS inet) and CAST(ip.ipAddressEnd AS inet) >= CAST(?1 AS inet)")
List<AllowedIp> findDuplicatesByIP(String ipAddress);
我的问题是，我需要在这个JPA查询中将ipAddress和ipAddressEnd列转换为inet，以便可以使用<=和>=运算符来搜索是否已经覆盖了给定的IP。上面提到的JPA查询在我尝试启动我的Tomcat应用程序时抛出错误。以下是堆栈跟踪：
Caused by: java.lang.IllegalArgumentException: org.hibernate.QueryException: Could not resolve requested type for CAST : inet [select ip from com.uxl.dataobjects.domain.AllowedIp ip where CAST(ip.ipAddress AS inet) <= CAST((?1) AS inet) and CAST(ip.ipAddressEnd AS inet) >= CAST((?1) AS inet)]
...
Caused by: org.hibernate.QueryException: Could not resolve requested type for CAST : inet [select ip from com.uxl.dataobjects.domain.AllowedIp ip where CAST(ip.ipAddress AS inet) <= CAST((?1) AS inet) and CAST(ip.ipAddressEnd AS inet) >= CAST((?1) AS inet)]
...
Caused by: org.hibernate.QueryException: Could not resolve requested type for CAST : inet
...
我已经尝试过的可能解决方案，但没有成功：
- 在Java中将String IP转换为InetAddress数据类型。这不能工作，因为表列仍然是字符串类型，所以仍然需要进行转换以使用运算符。
- 获取所有行并在Java中进行筛选。虽然这可能有效，但这似乎是一个不切实际的解决方案，更不用说是一种不好的做法。
- 我在Vlad Mihalcea的指南中找到了使用自定义Hibernate类型的方法，并在我的项目中包含了该包。然而，我无法弄清楚如何在查询中使用它进行转换，因为它主要用于实体，而列数据类型为Inet。在我的情况下，表中的列的类型是varchar。
有人能告诉我我做错了什么吗？非常感谢任何关于如何使这个运行起来的建议。
编辑1：添加了我尝试过的另一个可能的解决方案。
英文:
I have a table that contains ranges of IP addresses with starting and ending values in their own columns. These columns are of type varchar. I need to query the database to get rows for which the range covers that IP Address.
Here is my query that works perfectly fine with PGSQL:
select * from ip_whitelist iw where iw.ip_address::inet <= '1.1.1.54'::inet and iw.ip_address_end::inet >= '1.1.1.54'::inet
1.1.1.54 is just a dummy value here that will be replaced by a variable in my JPA query:
Here's how I'm trying to run this query via JPA Repository:
@Query("select ip from AllowedIp ip where CAST(ip.ipAddress AS inet) <= CAST((?1) AS inet) and CAST(ip.ipAddressEnd AS inet) >= CAST((?1) AS inet)")
List<AllowedIp> findDuplicatesByIP(String ipAddress);
My problem is I need to cast the ipAddress and ipAddressEnd columns to inet within this JPA query so I can use the <= and >= operators to search if a range already covers the given IP. The JPA query mentioned above is throwing an error when I try to start my Tomcat application. Here's the stack trace:
Caused by: java.lang.IllegalArgumentException: org.hibernate.QueryException: Could not resolve requested type for CAST : inet [select ip from com.uxl.dataobjects.domain.AllowedIp ip where CAST(ip.ipAddress AS inet) <= CAST((?1) AS inet) and CAST(ip.ipAddressEnd AS inet) >= CAST((?1) AS inet)]
at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1750)
at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1677)
at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1683)
at org.hibernate.jpa.spi.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:331)
at sun.reflect.GeneratedMethodAccessor69.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.springframework.orm.jpa.ExtendedEntityManagerCreator$ExtendedEntityManagerInvocationHandler.invoke(ExtendedEntityManagerCreator.java:344)
at com.sun.proxy.$Proxy152.createQuery(Unknown Source)
at org.springframework.data.jpa.repository.query.SimpleJpaQuery.validateQuery(SimpleJpaQuery.java:86)
... 101 more
Caused by: org.hibernate.QueryException: Could not resolve requested type for CAST : inet [select ip from com.uxl.dataobjects.domain.AllowedIp ip where CAST(ip.ipAddress AS inet) <= CAST((?1) AS inet) and CAST(ip.ipAddressEnd AS inet) >= CAST((?1) AS inet)]
at org.hibernate.QueryException.generateQueryException(QueryException.java:137)
at org.hibernate.QueryException.wrapWithQueryString(QueryException.java:120)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:234)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:158)
at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:131)
at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:93)
at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:167)
at org.hibernate.internal.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:301)
at org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:236)
at org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:1836)
at org.hibernate.jpa.spi.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:328)
... 107 more
Caused by: org.hibernate.QueryException: Could not resolve requested type for CAST : inet
at org.hibernate.hql.internal.ast.tree.CastFunctionNode.resolve(CastFunctionNode.java:87)
at org.hibernate.hql.internal.ast.HqlSqlWalker.processCastFunction(HqlSqlWalker.java:1097)
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.functionCall(HqlSqlBaseWalker.java:2748)
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.expr(HqlSqlBaseWalker.java:1342)
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.exprOrSubquery(HqlSqlBaseWalker.java:4686)
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.comparisonExpr(HqlSqlBaseWalker.java:4252)
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.logicalExpr(HqlSqlBaseWalker.java:2104)
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.logicalExpr(HqlSqlBaseWalker.java:2029)
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.whereClause(HqlSqlBaseWalker.java:796)
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:597)
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:301)
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:249)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:278)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:206)
... 115 more
Possible Solutions I've explored that didn't work out:
- Cast the String IP to InetAddress data type in java instead. This cannot work because the table columns are still in string type so they will still need to be cast in order to use the operators.
- Get all rows and filter in Java. While this may work, this seems to be an impractical solution, not to mention bad practice.
- I found Vlad Mihalcea's guide to using custom hibernate types and inlcuded the package in my project. However, I can't figure how to use it for casting in my query, because its primarily used in entities where the column data type is Inet. In my case, columns in the table are of type varchar.
Can someone please tell me what I'm doing wrong? Any suggestion on how I can get this to run will be greatly appreciated.
EDIT 1: Added another possible solution I've tried.
答案1
得分: 0
经过一些研究，我意识到答案始终在那里。虽然这不是一个理想的解决方案，但在 Hibernate 原生支持 inet 数据类型之前，这已经足够好了。
解决方案是在 @Query 中使用 nativeQuery = true 参数。因此，最终查询变为：
@Query(nativeQuery = true, value = "select * from ip_whitelist where CAST(ip_address AS inet) <= CAST((?1) AS inet) and CAST(ip_address_end AS inet) >= CAST((?1) AS inet)")
英文:
After some research, I have realized the answer was there all along. This is not an ideal solution, but good enough for now until Hibernate natively supports inet data type.
The solution is to use nativeQuery = true parameter in @Query. So the final query becomes:
@Query(nativeQuery = true, value = "select * from ip_whitelist where CAST(ip_address AS inet) <= CAST((?1) AS inet) and CAST(ip_address_end AS inet) >= CAST((?1) AS inet)")
答案2
得分: -1
内置的 cidr 和 inet 类型将会满足您的需求，并提供适当的操作符。关于在 PostgreSQL 中的转换详细信息，请使用这个 链接
SELECT '192.168.1.19'::inet << '192.168.1.0/24'::cidr
上面的查询可以直接给出 IP 地址，如果需要转换，可以使用以下链接来进行转换：
https://www.postgresql.org/message-id/Pine.LNX.4.20.0107211126100.4270-100000@Larry.bks
https://www.postgresql.org/message-id/F28A2B83DFE0D411A7B3006097487147468FDB%40sv7007.scania.se
https://www.postgresql.org/docs/9.1/datatype-net-types.html#:~:text=PostgreSQL%20提供了用于存储,%20functions%20(see%20Section%209.12).
英文:
The built-in cidr and inet types will do what you want and provide suitable operators and for more details regarding cast in postgres use this link
SELECT '192.168.1.19'::inet << '192.168.1.0/24'::cidr
The above query helps you give ip address directly and if needed to cast it then
make use of the below links that helps in those conversions
https://www.postgresql.org/message-id/Pine.LNX.4.20.0107211126100.4270-100000@Larry.bks
https://www.postgresql.org/message-id/F28A2B83DFE0D411A7B3006097487147468FDB%40sv7007.scania.se
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论