CODEWITHSUNDEEP
javamysql
overlord9314
overlord9314

Reputation: 35

MySQL command: Search for sub-string in database -- return matches (Java)

// Use case 1.

    String authorName = "Wei She";

    String query = "SELECT * FROM articles WHERE authors LIKE %?%";

    PreparedStatement getAuthors = newConn.prepareStatement(query);
    getAuthors.setString(1, authorName);
    ResultSet resultSet = getAuthors.executeQuery();

    while (resultSet.next()) {
        String authors = resultSet.getString("authors");
        System.out.println(authors);
    }

Here is a subset of my code. The data is already in my local MySQL database. The rest of the connection code is present.

My question is: how should I format the %?% section in Java code? Is there something I am missing? Here is the error output.

Exception in thread "main" java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '/%'Wei She'/%' at line 1
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:975)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:1025)
at src.TestSQLQuery.main(TestSQLQuery.java:34)

I have a similar query used in MySQL Workbench: SELECT * FROM articles WHERE authors LIKE '%Wei She%';

The above query works fine in Workbench and returns multiple entries with other authors along with "Wei She".

Upvotes: 0

Views: 48

Answers (1)

Andreas
Andreas

Reputation: 159096

%?% is not valid. You need to just use ? and have the % signs in the value passed to PreparedStatement:

String query = "SELECT * FROM articles WHERE authors LIKE ?";

PreparedStatement getAuthors = newConn.prepareStatement(query);
getAuthors.setString(1, "%" + authorName + "%");

Upvotes: 1

Related Questions