CODEWITHSUNDEEP
javaperformancejpaeclipselink
Warz
Warz

Reputation: 7746

JPA potential performance issue when querying users

Looking for some feedback on querying for a user when authenticating. The first thought i had when writing the code below was to get the username by querying all usernames in the database and checking if the username provided belongs to a list of users. Would this type of checking become a performance issue when the users table grows?

    EntityManager entityManager = factory.createEntityManager();
    /*Create a data structure to hold a list of users in our database*/
    List<String> allUsernames = new ArrayList<String>();  

    allUsernames = entityManager.createQuery("SELECT user.username FROM Users user").getResultList();

    /*Loop through each user in our available usernames checking if the username passed exists*/
    for (String user : allUsernames) {

        if (user.equals(username)) {
            System.out.println("Found real user\n");
            userFoundFLAG = 1; // set the flag equal to 1 when this user is found
        }

    }

Upvotes: 1

Views: 789

Answers (2)

Damo
Damo

Reputation: 11540

Yes, this would be a performance issue, growing into more of an issue as the number of Users increases. Definitely don't do it.

You should do something like:

try {
    User user = (User)entityManager.createQuery("SELECT user FROM Users user where user.username = :username")
        .setParameter ("username", username);
        .getSingleResult();
    userFoundFLAG = 1;
} catch (NoResultException e) {
    //There is no user with this username - do something
} catch (NonUniqueResultException e) {
    //There is more than one user with this username - do something
}

Upvotes: 1

Perception
Perception

Reputation: 80593

Why don't you just query for the user by name? If a non-zero list is returned then the user exists, else it doesn't. Even better, why not just read the entire user object since, conceivably, you will need it for additional authentication/authorization logic?

final TypedQuery<String> query = entityManager
      .createQuery("SELECT u.username FROM Users u WHERE u.username = :username", String.class);
query.setParameter("username", "BillyBob");
if (query.getResultList().size() > 0) {
   // ...
}

Or:

final TypedQuery<User> query = entityManager
      .createQuery("SELECT u FROM Users u WHERE u.username = :username");
query.setParameter("username", "BillyBob");
final User user = query.getFirstResult();

Upvotes: 0

Related Questions