How to embed and select from mysql at the same time

Question

I have a requirement when I need to insert a mobile number in mysql, if and only if the number is not present. So for this, I first check to see if a number is present in mysql using select query. If the number is missing 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 do not know if this is an effective way or not. Please suggest me a better way than this

Answer 1

Probably the easiest way in mysql:

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

Provided that you have a unique key on your mobile device

You can check it here: How to "paste if does not exist" in MySQL?

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

Answer 2

( ) , . SQL-, SQL, . 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: , registerSmsUsers.

CREATE INDEX registerSmsUsers_mobile_ndx ON registerSmsUsers(mobile)

( ):

ALTER TABLE registerSmsUsers ADD CONSTRAINT registerSmsUsers_mobile_unq UNIQUE (mobile)

, , / .

EDIT2: /.

Answer 3

, , IF NOT EXISTS, , select. , insert .

- :

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

INSERT IGNORE, :

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

Answer 4

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

, , . : ,

[]

Ya time diff , , , ( , 1000 ), , lakhs , , mysql,

Answer 5

, brettw:

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 = "[e-mail]";
            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();
        }
    }

}

, id : int(11) NOT NULL AUTO_INCREMENT