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

score 2 · Accepted Answer · edited May 23 '17 at 11:45

2

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

edited May 23 '17 at 11:45

Community

1
1

answered Nov 16 '13 at 07:27

zimi

1,526
10
25

It may not be primary key. But it should be unique key to use it that way – zimi Nov 16 '13 at 07:33

brettw · Answer 2 · 2013-11-16T14:21:45.090

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.

edited Nov 16 '13 at 14:21

answered Nov 16 '13 at 08:52

brettw

9,718
2
37
52

Thanks for your answer +1,well I need to know in which way will it be fast.Suppose if i have some 1000000 records then for inserting one record it will check 1000000. – SpringLearner Nov 16 '13 at 08:59
Yep, if ``registerSmsUsers`` is already creating declaring ``mobile`` unique, there will be an existing index and it should be very fast. Please accept this answer when you find that it works. – brettw Nov 16 '13 at 09:08
see I found your answer useful so already upvoted it,Please give me sometime to implement your idea in my app. – SpringLearner Nov 16 '13 at 09:12
this will be `rs.setString(1, name); rs.setString(2, email); rs.setString(3, mobile);` rs.updateString() instead of setString,right? – SpringLearner Nov 16 '13 at 09:14
I just copied your code to eclipse.In eclipse it giving copilation error and suggests to use rs.setString() to rs.UpdateString()(this error it shows in else loop) – SpringLearner Nov 16 '13 at 09:20
Oh, you're right, I just went and looked at some of my code. Sorry for misremembering, I have updated my answer. – brettw Nov 16 '13 at 09:23
( cc: @javaBeginner ) FYI, I had to use `con.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE)` in order for this approach to work. – Gord Thompson Nov 16 '13 at 10:44
@GordThompson can you please post your answer please – SpringLearner Nov 16 '13 at 10:50

Rahul Tripathi · Answer 3 · 2013-11-16T07:30:42.527

1

I think it would be better to create a stored procedure and then in that stored procedure you can first use the IF NOT EXISTS clause to check if the user exists using the select statement. If the user is not present you can insert the user in database.

Something like this:

IF NOT EXISTS(SELECT 1 FROM `registerSmsUsers` WHERE mobile= @mobile) THEN
    BEGIN
    INSERT INTO 
        `registerSmsUsers`
        (
            //column names
        ) 
        VALUES 
        (
            //values
        );
    END;
END IF;

Also there is a INSERT IGNORE statement which you can use like this:

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

edited Nov 16 '13 at 07:30

answered Nov 16 '13 at 07:17

Rahul Tripathi

152,732
28
233
299

Thanks for your reply,+1.Well What could be the jdbc codes for it? – SpringLearner Nov 16 '13 at 07:24
@javaBeginner:- I think this would be helpful for you:- http://docs.oracle.com/javase/tutorial/jdbc/basics/storedprocedures.html – Rahul Tripathi Nov 16 '13 at 07:27

Just code · Answer 4 · 2013-11-16T07:36:43.467

1

if not exists(select * from registerSmsUsers where mobile='232323') <-- will check your mobile no
begin
 insert into registerSmsUsers values(?,?,?)
end

This one is also an efficient way to check your method is also working fine but this also can be done See difference is you will have only one query here i hope this will help you thanks

[Edit]

Your questions answer

Ya there is a execution time diff between yours and mine query its depends upon a database size what you are using if you are using small size database (probably 1000 people) then you will not see any diff between your query and mine query but if your are using lakhs of users then your will have a performace issues check include execution plan in mysql you will get realtime difference between two

edited Nov 16 '13 at 07:36

answered Nov 16 '13 at 07:22

Just code

12,567
10
45
82

Thank for answering,+1 for your help.Well can you please tell me how to know that above way is better than my way.Can we know the execution time or any thing else – SpringLearner Nov 16 '13 at 07:26
1

i did this way `if not exists(select * from registerSmsUsers where mobile='232323') -> begin -> insert into registerSmsUsers values(?,?,?) -> end; ` error is`You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'if not exists(select * from registerSmsUsers where mobile='232323') begin inse' at line 1 ` – SpringLearner Nov 16 '13 at 08:39
Which version you are using? – Just code Nov 16 '13 at 08:46
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/41313/discussion-between-javabeginner-and-dholakiyaankit) – SpringLearner Nov 16 '13 at 08:46

score 1 · Answer 5 · answered Nov 16 '13 at 11:53

As requested, here is my tweaked version of brettw's answer:

import java.sql.*;

public class MySQLtest {

    public static void main(String[] args) {
        Connection con;
        try {
            con = DriverManager.getConnection(
                    "jdbc:mysql://192.168.1.3/zzzTest?" +
                    "useUnicode=yes&characterEncoding=UTF-8" +
                    "&user=root&password=whatever");
            String newName = "Gord";
            String newEmail = "gord@example.com";
            String newMobile = "416-555-1212";
            String sql = 
                    "SELECT " +
                        "id, " +
                        "name, " +
                        "email, " +
                        "mobile " +
                    "FROM registerSmsUsers " +
                    "WHERE mobile = ? " +
                    "FOR UPDATE";
            PreparedStatement pst = con.prepareStatement(
                    sql, 
                    ResultSet.TYPE_FORWARD_ONLY, 
                    ResultSet.CONCUR_UPDATABLE);
            pst.setString(1, newMobile);
            ResultSet rs = pst.executeQuery();
            if (rs.next()) {
                rs.moveToCurrentRow();
                rs.updateString("name", newName);
                rs.updateString("email", newEmail);
                rs.updateRow();
                System.out.println("Existing row updated.");
            }
            else {
                rs.moveToInsertRow();
                rs.updateString("name", newName);
                rs.updateString("email", newEmail);
                rs.updateString("mobile", newMobile);
                rs.insertRow();
                System.out.println("New row inserted.");
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

}

Note that id is the Primary Key for the table: int(11) NOT NULL AUTO_INCREMENT

How to insert and select from mysql simultaneously

5 Answers5