setParameter（）没有设置正确的引号 [英] setParameter() not setting proper quotes
本文介绍了setParameter（）没有设置正确的引号的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有以下代码：
字符串searchText =...;
String sqlQuery =
FROM Studio s+
WHERE fts（'english'，s.companyName，：q）= true;
Query q = JPA.em（）
.createQuery（sqlQuery）
.setParameter（q，searchText）;
当我传递一个单词给 searchText ，它的工作原理：
字符串searchText =one;
当我传递两个单词，如
字符串searchText =one two;
我得到
[PersistenceException：org.hibernate.exception.SQLGrammarException：
无法执行查询]
当我通过一个然而，它再次起作用：
字符串searchText ='one two';
SetParameter 不应设置正确的引号并转义文字？
PS fts 是一个PostgreSQL方言函数，定义如下：
public class MyPostgreSQLDialect扩展PostgreSQLDialect {
public MyPostgreSQLDialect（）{
registerFunction（fts，new PostgreSQLFullTextSearchFunction（））;
}
}
...
public class PostgreSQLFullTextSearchFunction实现SQLFunction {
public String render（@SuppressWarnings（rawtypes）列出参数，SessionFactoryImplementor工厂）{
if（args.size（）！= 3）{
throw new IllegalArgumentException（
该函数必须传递3个参数）;
}
String ftsConfig =（String）args.get（0）;
String field =（String）args.get（1）;
String value =（String）args.get（2）;
String fragment = null;
if（ftsConfig == null）{
fragment =to_tsvector（+ field +）@@+to_tsquery（'
+ value +'）;
} else {
fragment =to_tsvector（+ ftsConfig +:: regconfig，+ field +）@@
+to_tsquery（+ ftsConfig +，+值+）;
}
return fragment;
$覆盖
public Type getReturnType（Type columnType，映射映射）
抛出QueryException {
return new BooleanType（）;
}
@Override
public boolean hasArguments（）{
return true;
}
@Override
public boolean hasParenthesesIfNoArguments（）{
return false;
$ b @SuppressWarnings（rawtypes）
@Override
public String render（Type arg0，List arg1，SessionFactoryImplementor arg2）
抛出QueryException {
返回render（arg1，arg2）;
它将查询转换为：
to_tsvector（'english':: regconfig，studio0_.companyName）@@ to_tsquery（'english'，？）
pre
PostreSQL日志：
2014-02-11 15:10:39 CET错误：tsquery语法错误：one two
2014-02-11 15:10:39 CET声明：select studio0_.uuid as uuid164_，studio0_.addressScore as addressS2_164_，studio0_.adwordsAktivity as adwordsA3_164_，studio0_.affDiversityScore as affDiver4_164_，studio0_.allSources as allSources164_，studio0_.backgroundImg as backgrou6_164_，studio0_.city as city164_，studio0_.clusterDiversityScore as clusterD8_164_，studio0_.companyName为companyN9_164_，studio0_.companyNameCount为company10_164_，studio0_.companyType为company11_164_，studio0_.completenessScore为complet12_164_，studio0_.contentTags为content13_164_，studio0_.crefoId为crefoId164_，studio0_.decisionMaker1 a s decisio15_164_，studio0_.decisionMaker2 as decisio16_164_，studio0_.description as descrip17_164_，studio0_.email as email164_，studio0_.email2 as email19_164_，studio0_.emailCount as emailCount164_，studio0_.fax1 as fax21_164_，studio0_.fax2 as fax22_164_，studio0_.faxImpressum as faxImpr23_164_ ，studio0_.fbProfileUrl如fbProfi24_164_，studio0_.formOfAddress如formOfA25_164_，studio0_.hasGeoCode如hasGeoCode164_，studio0_.house如house164_，studio0_.imprintDataExtracted如imprint28_164_，studio0_.industry1如industry29_164_，studio0_.industry2如industry30_164_，studio0_.internetIq如internetIq164_，studio0_ .internetIqCluster如interne32_164_，studio0_.lastActivityDelta如lastAct33_164_，studio0_.lastActivitySource如lastAct34_164_，studio0_.lastActivityTime如lastAct35_164_，studio0_.logoImg如logoImg164_，studio0_.mgUuid如mgUuid164_，studio0_.mgcUuid如mgcUuid164_，studio0_.minCriticalityLevel如minCrit39_164_，studio0_.numCheckins作为numChec40_164_，studio0_.numL ikes as numLikes164_，studio0_.numLocations as numLoca42_164_，studio0_.numMentions as numMent43_164_，studio0_.numRatings as numRatings164_，studio0_.numSEMKeywords as numSEMK45_164_，studio0_.numVouchersSold as numVouc46_164_，studio0_.phone1 as phone47_164_，studio0_.phone2 as phone48_164_，studio0_.phone3 as phone49_164_，studio0_.phoneImpressum as phoneIm50_164_，studio0_.premiumRecordUrls as premium51_164_，studio0_.premiumRecords as premium52_164_，studio0_.qualityPrediction as quality53_164_，studio0_.rating as rating164_，studio0_.ratingSources as ratingS55_164_，studio0_.reachabilityScore as reachab56_164_，studio0_.sectorNameScore as sectorN57_164_， studio0_.sectorOverlapScore as sectorO58_164_，studio0_.sectorScore as sectorS59_164_，studio0_.socialMedia as socialM60_164_，studio0_.sourcesWithNoAff as sources61_164_，studio0_.starsOverall as starsOv62_164_，studio0_.street as street164_，studio0_.successfulWebsiteLookup as success64_164_，studio0_.targetGroupTags as targetG 65_164_，studio0_.teaser如teaser164_，studio0_.totalSrcEntities如totalSr67_164_，studio0_.totalSrcEntitiesWithNoAff如totalSr68_164_，studio0_.totalSummaryScore如totalSu69_164_，studio0_.videoUrl如videoUrl164_，studio0_.voucherUrls如voucher71_164_，studio0_.vouchers如vouchers164_，studio0_.website如website164_， studio0_.website2 as website74_164_，studio0_.website3 as website75_164_，studio0_.websiteAvailability as website76_164_，studio0_.websiteCount as website77_164_，studio0_.zip as zip164_ from studio studio0_ where to_tsvector（'german':: regconfig，studio0_.companyName）@@ to_tsquery（ 'german'，$ 1）= true limit $ 2
解决方案只有我想出的解决方案是手动转义searchText：
pre $ String $ escapepedSearchText = searchText.replace（'，''））;
I have following code:
String searchText = "...";
String sqlQuery =
"FROM Studio s " +
"WHERE fts('english', s.companyName, :q) = true";
Query q = JPA.em()
.createQuery(sqlQuery)
.setParameter("q", searchText);
When I pass a single word to searchText, it works:
String searchText = "one";
When I pass two words, like
String searchText = "one two";
I get
[PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query]
When I pass a quoted string however, it works again:
String searchText = "'one two'";
Is SetParameter not supposed to set proper quotes and escape the text?
P.S. fts is a PostgreSQL dialect function defined as follows:
public class MyPostgreSQLDialect extends PostgreSQLDialect {
public MyPostgreSQLDialect() {
registerFunction("fts", new PostgreSQLFullTextSearchFunction());
}
}
...
public class PostgreSQLFullTextSearchFunction implements SQLFunction {
public String render(@SuppressWarnings("rawtypes") List args, SessionFactoryImplementor factory) {
if (args.size() != 3) {
throw new IllegalArgumentException(
"The function must be passed 3 arguments");
}
String ftsConfig = (String) args.get(0);
String field = (String) args.get(1);
String value = (String) args.get(2);
String fragment = null;
if (ftsConfig == null) {
fragment = "to_tsvector(" + field + ") @@ " + "to_tsquery('"
+ value + "')";
} else {
fragment = "to_tsvector(" + ftsConfig + "::regconfig, " + field + ") @@ "
+ "to_tsquery(" + ftsConfig + ", " + value + ")";
}
return fragment;
}
@Override
public Type getReturnType(Type columnType, Mapping mapping)
throws QueryException {
return new BooleanType();
}
@Override
public boolean hasArguments() {
return true;
}
@Override
public boolean hasParenthesesIfNoArguments() {
return false;
}
@SuppressWarnings("rawtypes")
@Override
public String render(Type arg0, List arg1, SessionFactoryImplementor arg2)
throws QueryException {
return render(arg1, arg2);
}
}
It converts the query into:
to_tsvector('english'::regconfig, studio0_.companyName) @@ to_tsquery('english', ?)
P.P.S.
PostreSQL log:
2014-02-11 15:10:39 CET ERROR: syntax error in tsquery: "one two"
2014-02-11 15:10:39 CET STATEMENT: select studio0_.uuid as uuid164_, studio0_.addressScore as addressS2_164_, studio0_.adwordsAktivity as adwordsA3_164_, studio0_.affDiversityScore as affDiver4_164_, studio0_.allSources as allSources164_, studio0_.backgroundImg as backgrou6_164_, studio0_.city as city164_, studio0_.clusterDiversityScore as clusterD8_164_, studio0_.companyName as companyN9_164_, studio0_.companyNameCount as company10_164_, studio0_.companyType as company11_164_, studio0_.completenessScore as complet12_164_, studio0_.contentTags as content13_164_, studio0_.crefoId as crefoId164_, studio0_.decisionMaker1 as decisio15_164_, studio0_.decisionMaker2 as decisio16_164_, studio0_.description as descrip17_164_, studio0_.email as email164_, studio0_.email2 as email19_164_, studio0_.emailCount as emailCount164_, studio0_.fax1 as fax21_164_, studio0_.fax2 as fax22_164_, studio0_.faxImpressum as faxImpr23_164_, studio0_.fbProfileUrl as fbProfi24_164_, studio0_.formOfAddress as formOfA25_164_, studio0_.hasGeoCode as hasGeoCode164_, studio0_.house as house164_, studio0_.imprintDataExtracted as imprint28_164_, studio0_.industry1 as industry29_164_, studio0_.industry2 as industry30_164_, studio0_.internetIq as internetIq164_, studio0_.internetIqCluster as interne32_164_, studio0_.lastActivityDelta as lastAct33_164_, studio0_.lastActivitySource as lastAct34_164_, studio0_.lastActivityTime as lastAct35_164_, studio0_.logoImg as logoImg164_, studio0_.mgUuid as mgUuid164_, studio0_.mgcUuid as mgcUuid164_, studio0_.minCriticalityLevel as minCrit39_164_, studio0_.numCheckins as numChec40_164_, studio0_.numLikes as numLikes164_, studio0_.numLocations as numLoca42_164_, studio0_.numMentions as numMent43_164_, studio0_.numRatings as numRatings164_, studio0_.numSEMKeywords as numSEMK45_164_, studio0_.numVouchersSold as numVouc46_164_, studio0_.phone1 as phone47_164_, studio0_.phone2 as phone48_164_, studio0_.phone3 as phone49_164_, studio0_.phoneImpressum as phoneIm50_164_, studio0_.premiumRecordUrls as premium51_164_, studio0_.premiumRecords as premium52_164_, studio0_.qualityPrediction as quality53_164_, studio0_.rating as rating164_, studio0_.ratingSources as ratingS55_164_, studio0_.reachabilityScore as reachab56_164_, studio0_.sectorNameScore as sectorN57_164_, studio0_.sectorOverlapScore as sectorO58_164_, studio0_.sectorScore as sectorS59_164_, studio0_.socialMedia as socialM60_164_, studio0_.sourcesWithNoAff as sources61_164_, studio0_.starsOverall as starsOv62_164_, studio0_.street as street164_, studio0_.successfulWebsiteLookup as success64_164_, studio0_.targetGroupTags as targetG65_164_, studio0_.teaser as teaser164_, studio0_.totalSrcEntities as totalSr67_164_, studio0_.totalSrcEntitiesWithNoAff as totalSr68_164_, studio0_.totalSummaryScore as totalSu69_164_, studio0_.videoUrl as videoUrl164_, studio0_.voucherUrls as voucher71_164_, studio0_.vouchers as vouchers164_, studio0_.website as website164_, studio0_.website2 as website74_164_, studio0_.website3 as website75_164_, studio0_.websiteAvailability as website76_164_, studio0_.websiteCount as website77_164_, studio0_.zip as zip164_ from studio studio0_ where to_tsvector('german'::regconfig, studio0_.companyName) @@ to_tsquery('german', $1)=true limit $2
解决方案
The only solution I came up with was to escape searchText manually:
String escapedSearchText = String.format("'%s'", searchText.replace("'", "''"));
这篇关于setParameter（）没有设置正确的引号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
