Java MYSQL Prepared Statement Error: Check syntax

Question

I am trying to use the java mysql library but I am having issues using a prepared statement. I am not sure what I am missing. Below is what I have with the MYSQL error attempting to use the prepared statement.

String query = "SELECT id, clicks FROM mailer.links WHERE campaign_id=?";
    try {

        preparedStatement = connect.prepareStatement(query);
        preparedStatement.setInt(1, campaignId);
        preparedStatement.execute();
        Statement st = connect.createStatement();


        // execute the query, and get a java resultset
        ResultSet rs = st.executeQuery(query);

I am getting the following error:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 
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 '?' at line 1

It works if I do "campaign_id=" + campaignId , but is a SQL injection waiting to happen.

Answer 1

PreparedStatement method executeQuery() itself returns a ResultSet object

So assign this to a ResultSet object

ResultSet rs = preparedStatement.executeQuery();

Error:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 
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 '?' at line 1

And this error happens because When

ResultSet rs = st.executeQuery(query);

This statement executes it can't find any value in ? operator. so your query remains this "SELECT id, clicks FROM mailer.links WHERE campaign_id=?"; and this throws a MySQL Syntax Exception.

Answer 2

Try this

ResultSet rs = preparedStatement.executeQuery();