CODEWITHSUNDEEP
javamysqljdbc
The31StReaper
The31StReaper

Reputation: 231

How can I avoid entering duplicate entries in my MySQL database?

So, what I'm trying to do here is check the user table in my users mySQL database for duplicate entries in the username row, before inserting a new username. Here's an example of my code. Currently, the results ResultSet does not do anything and I'm not exactly sure how to implement it into the IF statement that follows. And yes, I have the catches for the try, just not in this example. Sorry if this is a rather simple question, I just started programming with Java last week. Also, it's my first question on here and I definitely appreciate the help.

 try{


      String sequel = ("SELECT username FROM `users`.`user`");
      PreparedStatement userNameInfo = conn.prepareStatement(sequel);
      userNameInfo.executeQuery(sequel);
      ResultSet results = userNameInfo.getResultSet();

      if (sequel.equals("")) {
          conn.setAutoCommit(false);
          String sql = "INSERT INTO `users`.`user`(`username`,`password`,`email`) VALUES('" + newusername +"', '" + newpassword + "', '" + newemail +"')";
          PreparedStatement prest = conn.prepareStatement(sql);
          prest.executeUpdate(sql);
          conn.commit();
          conn.close();
          System.out.println("Added Successfully!");
      }
      else {
          System.out.println("Add failed!");
      }
  }

Upvotes: 0

Views: 13114

Answers (4)

Matt Harrison
Matt Harrison

Reputation: 358

So I think what you trying to do - and should do I think, is select if the username is in the table then add or not. So the sql needs to be like:

select username from users where username = ?

then set the param in the query. See docs here: http://docs.oracle.com/javase/6/docs/api/java/sql/PreparedStatement.html

You then need to check what is in the resultset after the query, and see if anything is in there. The API docs for this will be in about the same place as the PreparedStatement docs.

Adding a constraint in the db will also give you a belt and braces.

Hope this helps

Upvotes: 2

amilaishere
amilaishere

Reputation: 408

Umm, I am not a java guy. But this may help you.

You can retrieve the row count of the first result set after the query executes. If the row count is equal to 0, that means database does not contain a similar record.

Upvotes: 1

Arpit Patel
Arpit Patel

Reputation: 1571

Have you created the primary key in the table? A primary key automatically prevents duplicate values.

If you want to prevent duplicate usernames, then make your username column the primary key.

Upvotes: 1

Matthieu
Matthieu

Reputation: 16397

When you define your SQL table, you can define some items to be unique.

So in your example, if you want usernames to be unique, you would add:

UNIQUE(username)

to your table declaration.

If you want the pair username / email to be unique, you would add:

UNIQUE(username, email)

The documentation is here

Upvotes: 2

Related Questions