CODEWITHSUNDEEP
javamysqlsqlregex
Michael
Michael

Reputation: 433

Java/MySQL being weird with regex query

This is my regex expression I am using to match LA zipcodes from Wikipedia:

9(0[012]|1[0346])[0-9]{2}

I tried running on a couple of other platforms and it works well, but somehow on java my IDE is telling me I am missing a parenthesis somewhere. I tried this expression instead:

9(0[012]|1[0346])[0-9][0-9]

but I get the same error. I don't know if it's an issue with java/mysql.

EDIT:

Here is the code I am trying to run it on: 

@Override
public List<String> findUserNamesLocationFirst(Category category,
                                               boolean isSellers, String zipRegex) {
    StringBuffer where = new StringBuffer()
            .append(" where category_id='")
            .append(category.getId())
            .append("' AND ")
            .append(whereLocation(zipRegex));

    StringBuffer query = new StringBuffer(
            "select distinct u.user_id from users u")
            .append(" inner join items i on u.id=i.seller_id")
            .append(where)
            .append(" order by u.date_reported desc, ")
            .append(isSellers ? "feedback_seller desc"
                    : "feedback_buyer desc").append(" LIMIT 100");

    ResultSet rs = MysqlDBConnector.getInstance().executeSelect(
            query.toString());
    try {
        List<String> result = new ArrayList<String>();
        while (rs.next()) {
            result.add(rs.getString("user_id"));
        }
        return result;
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        if (rs != null) {
            try {
                rs.getStatement().close();
            } catch (SQLException ignore) {
            }
        }
    }
    return null;

}

private StringBuffer whereLocation(String addressRegex) {
    StringBuffer where = new StringBuffer();
    if (addressRegex != null) {
        int startZipRegex = StringUtils.indexOfAny(addressRegex,
                "0123456789");
        int endZipRegex = addressRegex.indexOf(")", startZipRegex);
        if (startZipRegex > 0 && endZipRegex > 0
                && endZipRegex > startZipRegex + 4) {
            // hopefully valid zip code regular expression
            String zipRegex = addressRegex.substring(startZipRegex,
                    endZipRegex);
            where.append(" i.postal_code REGEXP '").append(zipRegex)
                    .append("'");
        } else {
            Log.warn("whereLocation() could not find the regular expression for the zip code. Possible error with select"
                    + "ing the right users");
        }
    }
    return where;
}

This is the stacktrace:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Got error 'parentheses not balanced' from regexp
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
at com.mysql.jdbc.Util.getInstance(Util.java:384)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3562)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3494)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1960)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2114)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2690)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2619)
at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1465)
at com.company.engine.persist.MysqlDBConnector.execute(MysqlDBConnector.java:113)
at com.company.engine.persist.MysqlDBConnector.executeSelect(MysqlDBConnector.java:77)
at com.company.app.dao.JDBCUserDAO.findUserNamesLocationFirst(JDBCUserDAO.java:537)

Upvotes: 0

Views: 600

Answers (3)

Michael
Michael

Reputation: 433

After a little debugging I found the source of this error in the method whereLocation(), in case anyone was wondering.

int endZipRegex = addressRegex.indexOf(")", startZipRegex);

which should have been 

int endZipRegex = addressRegex.lastIndexOf(")");

Since the code

String zipRegex = addressRegex.substring(startZipRegex,
                endZipRegex);

a few lines later strips everything after ")" from the regex expression and passes it into MySQL, a regex expression having > 1 pair of parantheses will not be well-formed.

Upvotes: 0

mitunome
mitunome

Reputation: 51

looks like you're trying to get Java regexes working on MySQL regex syntax, which is slightly different. MySQL regexp doesn't like the [0-9]{2} part of the expression, but it doesn't mind the [0-9][0-9] part.

see http://dev.mysql.com/doc/refman/5.1/en/regexp.html

Upvotes: 1

Adrian Jandl
Adrian Jandl

Reputation: 3015

    Pattern p = Pattern.compile("9(0[012]|1[0346])[0-9]{2}");
    Matcher m = p.matcher("90001");
    System.out.println(m.matches());

Evaluates to true for me. Are you sure you're using the Pattern correctly?

Upvotes: 1

Related Questions