本文介绍了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; 

我得到

当我通过一个然而,它再次起作用:

 字符串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

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()没有设置正确的引号的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

10-11 04:52