1

I wrote a Java app in which users can login/register. This is working already. But somehow when I tried coding the logic to check if the user already exists I get an error over error.

This is my code:

public static void checkRegister(String mail) {

        try {
            // create a mysql database connection

            Class.forName(myDriver);
            Connection conn = DriverManager.getConnection(myUrl, user, passwordDB);

              // the mysql insert statement
              String query = "SELECT COUNT(email) mailc, email from users where users.email = \"" + mail + "\"";

              // create the mysql insert preparedstatement
              PreparedStatement preparedStmt = conn.prepareStatement(query);

              // execute the preparedstatement
              preparedStmt.execute();
            conn.close();
        } catch (Exception e) {
            System.err.println("Got an exception!");
            System.err.println(e.getMessage());
        }
    }

I tried using preparedStmt.getResultSet().getInt("mailc") but it doesnt work.

example mail not registered yet:

example mail not registered yet

example mail already registered:

enter image description here

My aim is to check if mailc is > 0.

Improve this question

4 Answers 4

Reset to default
3

You have to use AS with Count(c) AS col like this :

String query = "SELECT COUNT(email) AS mailc, email from users ....";

I suggest to use ? of PreparedStatement instead, to avoid any SQL Injection. So your query should look like this:

String query = "SELECT COUNT(email) mailc, email from users where users.email =?";
PreparedStatement preparedStmt = conn.prepareStatement(query);
preparedStmt.setString(1, mail);

To get the results you have to use a ResultSet:

String query = "SELECT COUNT(email) AS mailc, email from users where users.email =?";
PreparedStatement preparedStmt = conn.prepareStatement(query);
preparedStmt.setString(1, mail);

ResultSet result = preparedStmt.executeQuery();
while (result.next()) {
   int count = result.getInt("mailc");
   String email = result.getString("email");
}
Improve this answer
Sign up to request clarification or add additional context in comments.

16 Comments

He is already using PrepapredStatement , just that his syntax is wrong
yes, i know @YoungMillie what did you mean? i don't get you
You are doing so many editing, at the time I commented, you said he should use PrepapredStatement which he was already using. Now that you have edited, everything is okay now.
Thanks for the advise. I changed it in my code. But how can I check the value col "mailc"? `preparedStmt.getResultSet().getInt("mailc")´ seems not to work
@piguy If you find the answer is correct or helpful, always upvote as people make effort to answer the questions. So please be kind enough to appreciate the efforts.
|
2

ResultSet rs = stmt.executeQuery(query);

Your code may work, the statement is not perfect but working. Can you provide the exeption and we will solve it? or you can Try Using ' instead of " in your QUERY

Improve this answer

6 Comments

Code above is working - my question is how can I work with the result to get the value from column "mailc"
Adding preparedStmt.getResultSet().getInt("mailc") gives me nullPointerException
resultSet = preparedStmt.executeQuery
can your explain your comment? I want to hear which game? I am trying to solve it without eclipse here.
Actually this answer isn't so great. When you have code, then format it accordingly. You should also explain why "that code isn't" perfect. And believe me: downvoting other answers is not a helpful pattern. Provide quality answers; then reputation flows in automatically.
|
1

Doing a little guesswork here: you want to check if users table already has at least one user with that Email address, and that query will always operate on one given address, not on a list of addresses.

You may want to consider rethinking your query on this one: you want to check if the value exists, yet you ask your database to count how many emails there are. It that particular example this may be fine, because there would be only one such user or no users at all. But in more complex use cases you would actually force your database into doing more work that it should, and that will hit your application performance.

I would suggest that correct query you should use is actually this one:

select case when
  exists (select 1 from users where mail = ?)
  then 1 else 0 end as answer
from dual //this is a synthetic table which some DB vendors need.
          //that whole line can be removed if your DB vendor doesn't require it.

And respective Java code is:

void checkRegistered(String email) {
  initDriver();
  // note the try block below: it will close all resources you have allocated
  try (Connection conn = DriverManager.getConnection(uri, credentials.name, credentials.pwd);
       PreparedStatement st = conn.prepareStatement(query);
       ResultSet result = st.executeQuery()) {
    if (result.next() && result.getInt("answer") == 1) {
      //do here whatever you do when user is invalid.
    }
  }
}
Improve this answer

1 Comment

Thank you - exactly what I needed and tried to do. This works nicely :)
0

Please modify your code as such:

String query = "SELECT COUNT(email) mailc, email from users where users.email =?";

// create the mysql insert preparedstatement
PreparedStatement preparedStmt = conn.prepareStatement(query);
preparedStmt.setString(1,mail);
// execute the preparedstatement
preparedStmt.execute();
Improve this answer

1 Comment

Thanks for the advise. I changed it in my code. But how can I check the value col "mailc"? `preparedStmt.getResultSet().getInt("mailc")´ seems not to work

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.