CODEWITHSUNDEEP
javamysql
user225269
user225269

Reputation: 10893

mysql select query in java

I'm new to connecting java with a mysql database. What's wrong with my query here:

PreparedStatement statement = conn.prepareStatement("SELECT * FROM q_table, choices, answers  WHERE q_table.QID='" + number_input + "' AND choices.CID='" + number_input + "' AND answers.AID='" + number_input + "'");

Upvotes: 1

Views: 27163

Answers (5)

Jigar Joshi
Jigar Joshi

Reputation: 240996

In your statement " ... q_table.QID='" + number_input + "' AND ... the variable number_input is enclosed in a single quote ('). This is used for string lieterals. If you remove the single quote it should work:

 String prest= "SELECT * FROM q_table, choices, answers  WHERE questions.QID=? AND choices.CID=? AND answers.AID=?";

 prest.setInt(1,1980);
 prest.setInt(2,2004);
 .
 .
 ResultSet rs = prest.executeQuery();
 while (rs.next()){
     String mov_name = rs.getString(1);
     int mov_year = rs.getInt(2);
     count++;
     System.out.println(mov_name + "\t" + "- " + mov_year);
 }
 System.out.println("Number of records: " + count);
 prest.close();
 con.close();

Upvotes: 4

asgs
asgs

Reputation: 3984

That is not a proper prepareStatement. It's a concatenated query that will only lead you to sql injection horrors. Look here

Upvotes: 1

Cid54
Cid54

Reputation: 406

SELECT * FROM q_table, choices, answers WHERE q_table.QID='" + number_input + "' AND choices.CID='" + number_input + "' AND answers.AID='" + number_input + "'"

or

SELECT * FROM questions, choices, answers WHERE questions.QID='" + number_input + "' AND choices.CID='" + number_input + "' AND answers.AID='" + number_input + "'"

Upvotes: 1

adarshr
adarshr

Reputation: 62613

When you use prepared statements, you can't set the values there.

You have to first prepare the statement using question marks and then set the parameters later.

Here is an example:

public void updateCoffeeSales(HashMap<String, Integer> salesForWeek) throws SQLException {

    PreparedStatement updateSales = null;
    PreparedStatement updateTotal = null;

    String updateString = "update " + dbName + ".COFFEES " +
                          "set SALES = ? where COF_NAME = ?";

    String updateStatement = "update " + dbName + ".COFFEES " +
                             "set TOTAL = TOTAL + ? where COF_NAME = ?";

    try {
      con.setAutoCommit(false);
      updateSales = con.prepareStatement(updateString);
      updateTotal = con.prepareStatement(updateStatement);

      for (Map.Entry<String, Integer> e : salesForWeek.entrySet()) {
        updateSales.setInt(1, e.getValue().intValue());
        updateSales.setString(2, e.getKey());
        updateSales.executeUpdate();

        updateTotal.setInt(1, e.getValue().intValue());
        updateTotal.setString(2, e.getKey());
        updateTotal.executeUpdate();
        con.commit();
      }
    } catch (SQLException e ) {
      JDBCTutorialUtilities.printSQLException(e);
      if (con != null) {
        try {
          System.err.print("Transaction is being rolled back");
          con.rollback();
        } catch(SQLException excep) {
          JDBCTutorialUtilities.printSQLException(excep);
        }
      }
    } finally {
      updateSales.close();
      updateTotal.close();
      con.setAutoCommit(true);
    }
  }

Upvotes: 1

Jon Skeet
Jon Skeet

Reputation: 1503799

Well, the first problem is that you're opening yourself up to a SQL injection attack by including values directly in your SQL. Use a parameterized query instead.

Now we can't really tell what's wrong beyond that, although the fact that you're quoting a number seems suspicious, as does the fact that you're using the same value for a question ID, a choice ID and an answer ID. That seems unlikely to be appropriate.

If you could give us more information about what's happening vs what you expected to happen, that would really help.

Upvotes: 1

Related Questions