CODEWITHSUNDEEP
javasqljdbc
piguy
piguy

Reputation: 526

Java SQL access row data

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.

Upvotes: 1

Views: 348

Answers (4)

Youcef LAIDANI
Youcef LAIDANI

Reputation: 60046

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");
}

Upvotes: 3

M. Prokhorov
M. Prokhorov

Reputation: 3993

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.
    }
  }
}

Upvotes: 1

strash
strash

Reputation: 1321

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

Upvotes: 2

Young Emil
Young Emil

Reputation: 2276

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();

Upvotes: 0

Related Questions