How to change a value in mysql database using conditions from java code?

Question

I'm trying to change a value Dr_status that only contain one int even 0 or 1. So if Dr_status equal to 1 change it to 0 and vice versa. Here is the code :

String query = "Select Bluetooth_Address FROM dr";
String str = "40D32DBBE665";//in the database I have only two fields in `Bluetooth_Address` column 40D32DBBE665 and another value 
String check = "";
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(query);
PreparedStatement preparedStmt= con.prepareStatement("update `dr` set `Dr_status` = '1'");
PreparedStatement preparedStmt1= con.prepareStatement("update `dr` set `Dr_status` = '0'");

  dbtime = rs.getString(1);               
   if (dbtime.equals(str)){
       check = "Select `Dr_status` From `dr` Where `Bluetooth_Address` = " + " " + str ;
       if(check.equals(0)){ 
            preparedStmt.executeUpdate();
                    }
                    if(check.equals(1)){
                        preparedStmt1.executeUpdate();
                    }

I don't know where is the problem !!! please help. Thanks in advance.

Remove if(check.equals(0)). check is a string and will never be 0 or 1. — Bala R
– Bala R, Commented Jun 21, 2011 at 14:58

Bill Karwin · Accepted Answer · 2011-06-21 18:06:53Z

3

I give +1 to the answer from @Marcelo Hernández Rishmawy. Instead of testing the condition in Java code, do the test and the update in an SQL expression that converts 0 to 1 and 1 to 0 automatically, for the rows that match your Bluetooth address condition.

I'll also give you a tip that in MySQL, 1 and 0 are integers, but they are also used for true and false. So you can use either of the following tricks to make the statement more compact:

"update `dr` set `Dr_status` = ! `Dr_status` where `Bluetooth_Address = " + str

This trick works too:

"update `dr` set `Dr_status` = 1 - `Dr_status` where `Bluetooth_Address = " + str

It's a nice way to simplify, but FWIW it's specific to MySQL, not standard SQL. Other databases brands use proper boolean values, not 1 and 0.

Re your comment: the error is not related to the solutions above, it's because you're interpolating a string of hex digits. You need to either quote the string, or better yet use a query parameter.

You should learn how to use query parameters in any case, because they're good for writing secure code to defend against SQL injection issues, and it's generally easier and more robust than trying to interpolate variables into SQL query strings.

See Using Prepared Statements at The Java Tutorials.

edited Jun 21, 2011 at 18:06

answered Jun 21, 2011 at 15:38

Bill Karwin

567k87 gold badges711 silver badges872 bronze badges

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

3 Comments

Mohannad.Z Over a year ago

Thanks, but it show me this error : com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column '40D32DBBE665' in 'where clause' for the two ways you have mentioned

Mohannad.Z Over a year ago

It works now but after a small edit : "update dr set Dr_status = 1 - Dr_status where Bluetooth_Address = '" + str + "'" Thanks for helping me, you are the best :)

Bill Karwin Over a year ago

Please do remember to learn how to use prepared statements instead of interpolating Java variables into your queries. All programmers have a responsibility to write secure code, or else hackers will continue to steal Sony Playstation account data forever.

Marcelo · Accepted Answer · 2011-06-21 17:56:40Z

2

Use something similar to:

"update `dr` set `Dr_status` = CASE `Dr_status` WHEN '1' THEN '0' ELSE '1' END CASE Where `Bluetooth_Address` = '" + str + "'"

edited Jun 21, 2011 at 17:56

answered Jun 21, 2011 at 15:02

Marcelo

11.3k1 gold badge40 silver badges53 bronze badges

5 Comments

Mohannad.Z Over a year ago

Thanks, but it show me this error : com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'CASE Where Bluetooth_Address = 40D32DBBE665' at line 1

Marcelo Over a year ago

@Mohannad.Z Corrected it, was not sure if the column Bluetooth_Address was a varchar or not.

Mohannad.Z Over a year ago

Thanks for quick response :) but the same error appears :( : com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'CASE Where Bluetooth_Address = '40D32DBBE665'' at line 1

Marcelo Over a year ago

@Mohannad.Z Then you can try @Bill Karwin's method adding the apostrophes for the Bluetooth_Address value.

Mohannad.Z Over a year ago

It doesn't work also but thanks anyway I'll try to fined another solution. Regards

Buhake Sindi · Accepted Answer · 2011-07-04 12:24:53Z

0

The line if(check.equals(0)){ is invalid. check is a String and will never equal 0

edited Jul 4, 2011 at 12:24

Buhake Sindi

89.5k30 gold badges176 silver badges234 bronze badges

answered Jun 21, 2011 at 15:00

Johnny

6781 gold badge6 silver badges16 bronze badges

2 Comments

Mohannad.Z Over a year ago

Thanks, but I have tried this : if(check.equals("0")) and change the type of Dr_status to varchar in the database but nothing happened !!!

Johnny Over a year ago

I think you are misunderstanding my original comment. You have a String object named check whose value equals Select Dr_status From dr Where Bluetooth_Address = 40D32DBBE665. If you print out the value of check you will see the text Select Dr_status From dr Where Bluetooth_Address = 40D32DBBE665. Later you are checking to see if that text equals 1 or 0. It's like you are saying does "Dog" equal 1. The if statements if(check.equals(0)) and if(check.equals(1)) will never evaluate to true regardless of the databases contents, because you are comparing text to a number

Collectives™ on Stack Overflow

How to change a value in mysql database using conditions from java code?

3 Answers 3

3 Comments

5 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

5 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related