CODEWITHSUNDEEP
javamysqlhibernate
Dejell
Dejell

Reputation: 14317

Restrictions.sqlRestriction Parameter index out of range

I would like to Search for “whole word match” in MySQL and Hibernate.

My code is:

public Collection<Template> findByOneTag(String tags) {
    // since the tags are in , format we will replace it with |
    return getSession().createCriteria(Template.class)
            .add(Restrictions.sqlRestriction("tags REGEXP '[[:<:]]?[[:>:]]'", tags.replaceAll(",", "|"), StandardBasicTypes.STRING)).list();
}

and Template:

    @Entity
public class Template implements Serializable {
    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Column(name="template_id")
    private int templateId;

    @Lob()
    private String content;

    @Column(name="method_id")
    private byte methodId;

    private String subject;

    private String tags;

    @Column(name="template_name")
    private String templateName;

    private String thumbnail;
}

However I get:

Caused by: java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:982)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:927)
at com.mysql.jdbc.PreparedStatement.checkBounds(PreparedStatement.java:3729)

as well as the ? is not being replaced with tags.

What is wrong?

Upvotes: 0

Views: 1687

Answers (1)

JB Nizet
JB Nizet

Reputation: 691893

Try using the following restriction:

"tags REGEXP ?"

and passing the following value as parameter:

"[[:<:]]" + yourOriginalParameter + "[[:>:]]"

Upvotes: 1

Related Questions