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

Community · Accepted Answer · 2017-05-23 11:45:54Z

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, 2017 at 11:45

CommunityBot

11 silver badge

answered Nov 16, 2013 at 7:27

zimi

1,62413 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

zimi Over a year ago

It may not be primary key. But it should be unique key to use it that way

brettw · Accepted Answer · 2013-11-16 14:21:45Z

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, 2013 at 14:21

answered Nov 16, 2013 at 8:52

brettw

11.1k2 gold badges47 silver badges62 bronze badges

8 Comments

SpringLearner Over a year ago

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.

brettw Over a year ago

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.

SpringLearner Over a year ago

see I found your answer useful so already upvoted it,Please give me sometime to implement your idea in my app.

SpringLearner Over a year ago

this will be rs.setString(1, name); rs.setString(2, email); rs.setString(3, mobile); rs.updateString() instead of setString,right?

SpringLearner Over a year ago

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)

|

Rahul Tripathi · Accepted Answer · 2013-11-16 07:30:42Z

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, 2013 at 7:30

answered Nov 16, 2013 at 7:17

Rahul Tripathi

173k33 gold badges292 silver badges341 bronze badges

2 Comments

SpringLearner Over a year ago

Thanks for your reply,+1.Well What could be the jdbc codes for it?

Rahul Tripathi Over a year ago

@javaBeginner:- I think this would be helpful for you:- docs.oracle.com/javase/tutorial/jdbc/basics/…

Just code · Accepted Answer · 2013-11-16 07:36:43Z

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, 2013 at 7:36

answered Nov 16, 2013 at 7:22

Just code

13.8k10 gold badges54 silver badges94 bronze badges

4 Comments

SpringLearner Over a year ago

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 Over a year ago

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

Just code Over a year ago

Which version you are using?

SpringLearner Over a year ago

let us continue this discussion in chat

Gord Thompson · Accepted Answer · 2013-11-16 11:53:00Z

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 = "[email protected]";
            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

Collectives™ on Stack Overflow

How to insert and select from mysql simultaneously

5 Answers 5

1 Comment

8 Comments

2 Comments

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

8 Comments

2 Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related