CODEWITHSUNDEEP
javasqlpostgresqlspring-mvc
Almas Abdrazak
Almas Abdrazak

Reputation: 3632

One sql query to check for unique

I have a Spring Mvc application, in registration form i have an area for email and name

Before registering i check that all fields are unique in the DB with the following code in service layer (I'm using spring data) 

@Override
    public Optional<String> isUnuque(final Users user) {
        if (this.repository.findByEmail(user.getEmail()) != null) {
            return Optional.of("Your email is not unique");
        } else if (this.repository.findByName(user.getName()) != null) {
            return Optional.of("Your name is not unique");
        }
        return Optional.empty();

    }

Then in controller i check if Optional is present. But with this code i make 2 requests to the DB, i can use native query like this to make only one request:

select * from users where name = 'dd' or email = '[email protected]'

But how would i know which parameter is not unique name or email?

Thank in advance

BTW i solved this problem with the following code

@Override
    public Optional<String> isUnuque(final Users user) {
        final Users dbUser = this.repository.findByEmailOrName(user.getEmail(), user.getName());
        if (dbUser != null) {
            if (user.getEmail().equals(dbUser.getEmail())) {
                return Optional.of("Not unique email");
            } else {
                return Optional.of("Not unique name");
            }
        }
        return Optional.empty();
    }

But if there are any sql query that can do exactly what i want without double checking in service layer please write it below

Upvotes: 0

Views: 74

Answers (2)

Kaushik Nayak
Kaushik Nayak

Reputation: 31676

I'm not a java expert but as a database guy, I would say a better way would be to enforce the UNIQUE constraint on name and email in the table and handle the exception, which maintains the data integrity regardless of the application.

Another option is to get the count of emails and names with the given arguments.

SELECT count(CASE 
            WHEN name = :p_name
                THEN 1
            END) AS name_count
    ,count(CASE 
            WHEN email = :p_email
                THEN 1
            END) AS email_count
FROM users;

The COUNT function does not count nulls, so when condition in CASE is false( by default NULL), it adds 0 to the count. So, effectively the query would return name_count and email_count as 0 or 1 depending on the existing value, which you should be able to use in your code.

Upvotes: 1

Bogdan Lukiyanchuk
Bogdan Lukiyanchuk

Reputation: 767

Union All can do this.

select 'name' from users where name = 'dd'
union all
select 'email' from users where name = 'ddd'@mail.ru

Upvotes: 0

Related Questions