When injecting into a sql query using java, gets an error

Question

public ArrayList<Message> searchMessages(String word) throws DaoException{
    ArrayList<Message> messages = new ArrayList<>();
            Connection con = null;
    PreparedStatement ps = null;
    ResultSet rs = null;

    try {
        con = getConnection();

        //String query = "SELECT * FROM messages WHERE text LIKE %?% order by date";
        String query = "SELECT * FROM messages WHERE text LIKE '%?%'";
        ps = con.prepareStatement(query);
        ps.setString(1,word);
        rs = ps.executeQuery();

        while (rs.next()) {
            int messageId = rs.getInt("messageId");
            String text = rs.getString("text");

            String date = rs.getString("date");
            int memberId2 = rs.getInt("memberId");
            Message m = new Message(messageId,text,date,memberId2);
            messages.add(m);

            //Company c = new Company(companyId, symbol, companyName, sharePrice, high, low);
            //companies.add(c);
        }
    } catch (SQLException e) {
        throw new DaoException("searchMessages(): " + e.getMessage());
    } finally {
        try {
            if (rs != null) {
                rs.close();
            }
            if (ps != null) {
                ps.close();
            }
            if (con != null) {
                freeConnection(con);
            }
        } catch (SQLException e) {
            throw new DaoException("searchMessages(): " + e.getMessage());
        }
    }

    return messages;

}

Just explain the code a little first.It simply just searches the messages table and its field of text for whatever is supplied.I use a prepared statement to insert it into the query and run it.No matter what string i supply it gives this error

oow_package.DaoException: searchMessages(): Parameter index out of range (1 > number of parameters, which is 0).

No idea why it isn't working in the slightest. Would appreciate any help.

If you found a correct answer, check the ticker beside it to mark it as accepted answer. — Alfabravo
– Alfabravo, Commented Oct 22, 2012 at 21:18

JB Nizet · Accepted Answer · 2012-10-22 21:09:49Z

5

You can't use such a parameter in a prepared statement. The query should be

SELECT * FROM messages WHERE text LIKE ?

And you should use

ps.setString(1, "%" + word + "%");

answered Oct 22, 2012 at 21:09

JB Nizet

694k94 gold badges1.3k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Daniel Over a year ago

That is correct it fixed it thanks alot.Will update post now and mark your post as the correct answer when it allows me.Thanks alot

Daren · Accepted Answer · 2012-10-22 21:10:15Z

0

I'm no expert, but i'd say your prepared statement is recognized with no parameters, and you still insert one (word)... maybe the trouble comes from the % sign?

EDIT: agree with the guy above... seems legit.

answered Oct 22, 2012 at 21:10

Daren

3,4475 gold badges25 silver badges35 bronze badges

Collectives™ on Stack Overflow

When injecting into a sql query using java, gets an error

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related