0

I am getting the same total (which is incorrect) for "Count" in code below. If i use "Statement", the total is correct. The incorrect result which is shown on the console is ...

run:

Connection Successful
Members 500
Payment 500
BUILD SUCCESSFUL (total time: 0 seconds)

package preparedstatement2;

import java.sql.*;

public class Main {

    public static void main(String[] args) {

        Connection conn = SqliteConn.connectDB();
        try {
            PreparedStatement pst = conn.prepareStatement("Select Count (?) From members");
            pst.setString(1, "mem_id");
            ResultSet rs = pst.executeQuery();
            while (rs.next()) {
                int mem_id = rs.getInt(1);
                System.out.println("Members " + mem_id);
            }

            pst.setString(1, "payment");
            rs = pst.executeQuery();
            while (rs.next()) {
                int payment = rs.getInt(1);
                System.out.println("Payment " + payment);
            }

        } catch (SQLException ex) {
            ex.getMessage();
        }
    }
}

edit: the expected output is supposed to be:

Connection Successful
Members 500
Payment 400
BUILD SUCCESSFUL (total time: 0 seconds)
shkhoo
  • 115
  • 2
  • 8
  • What is the expected output? – Blasanka Aug 25 '17 at 05:06
  • You are misunderstanding what count does. `count(1)` is **exactly** the same thing as `count(*)`. Your statement will always count all rows in the table, regardless of which value you pass to the statement. Maybe you meant `select count(*) from members where some_column = ?` What is the definition of the table `members`? –  Aug 25 '17 at 06:16
  • when i use statement (createStatement) ...ResultSet rs = st.executeQuery("Select Count (mem_id) From members"); ResultSet rs = st.executeQuery("Select Count (payment) From members"); The result is correct. – shkhoo Aug 25 '17 at 06:26
  • That is because `count(payment)` will count all rows where the column `payment` is not null. However your code is equivalent to `count('payment')`, which essentially means count all rows, or more precisely "count all rows where the string value 'payment' is not null" (which it never is, so it counts all rows). You can't parameterise object names like columns. You can only parameterise **values**. – Mark Rotteveel Aug 25 '17 at 08:46
  • I tried " Select Count (payment) From members Where payment != null ". It worked this way... giving the count as 400 But when i adapt it this way... [preparedStatement]" Select Count (?) From members Where (?) != null " ,it would not work. – shkhoo Aug 25 '17 at 12:03
  • I repeat: you can only use parameters for **values**, not for a column name. When you use `count(?)`, and set a value for that parameter, then that is simply a string **value**, not a column name. Compare the difference between `count(payment)` and `count('payment')`. – Mark Rotteveel Aug 25 '17 at 13:58

0 Answers0