How to insert and select from mysql simultaneously

Question

I have a requirement where I need to insert mobile number in mysql if and only if the number is is not present.So for this I am first checking if a number is present in mysql using select query .If number is not present then insert.Following is my code

PreparedStatement pt1=con.prepareStatement("select * from registerSmsUsers where mobile='"+mobile+"'");
PreparedStatement pt=con.prepareStatement("insert into registerSmsUsers values(?,?,?)");
        pt.setString(1, name);
        pt.setString(2, email);
        pt.setString(3, mobile);
ResultSet rs1=pt1.executeQuery();
        if(rs1.next())

{pt.executeUpdate();}

i dont know whether this is a efficient way or not.Please suggest me a better way then this

Answer 1

Probably the easiest way in mysql is:

insert ignore into registerSmsUsers values(?,?,?)

When assuming you have unique key on mobile

You may check it here: How to 'insert if not exists' in MySQL?

Or here: http://dev.mysql.com/doc/refman/5.6/en/insert.html

Answer 2

Many of the proposed solutions (including yours) have a race condition that can cause a primary key or unique constraint violation. You code also have a possible SQL injection attack by concatenating SQL rather than using prepared statement parameters. Use SELECT...FOR UPDATE.

PreparedStatement ps = con.prepareStatement("SELECT name, email, mobile FROM registerSmsUsers WHERE mobile=? FOR UPDATE",
                                            ResultSet.TYPE_FORWARD_ONLY,
                                            ResultSet.CONCUR_UPDATABLE);
ps.setString(1, mobile);
ResultSet rs = ps.executeQuery();
if (rs.next()) { // it exists already
   rs.moveToCurrentRow();
   rs.updateString(3, mobile);
   rs.updateRow();
} else { // it does NOT exist
   rs.moveToInsertRow();
   rs.updateString(1, name);
   rs.updateString(2, email);
   rs.updateString(3, mobile);
   rs.insertRow();
}
rs.close();
ps.close();

EDIT: Just make sure you have an index on registerSmsUsers.

CREATE INDEX registerSmsUsers_mobile_ndx ON registerSmsUsers(mobile)

or a unique contraint (which implicitly creates an index):

ALTER TABLE registerSmsUsers ADD CONSTRAINT registerSmsUsers_mobile_unq UNIQUE (mobile)

With an index, even with millions of records the update/insert will basically be instant.

EDIT2: Added cursor/result set options.