How to update data in a MySQL database with Java?

To update data into a MySQL database table, use UPDATE command. The syntax is as follows −

update yourTableName set yourColumnName1 = value1,....N where condition;

First, we need to create a table. The query is as follows −

mysql> create table UpdateDemo
   -> (
   -> id int,
   -> Name varchar(200)
   -> );
Query OK, 0 rows affected (0.67 sec)

Let us insert records into the table. The following is the query −

mysql> insert into UpdateDemo values(101,'John');
Query OK, 1 row affected (0.19 sec)
mysql> truncate table UpdateDemo;
Query OK, 0 rows affected (0.86 sec)
mysql> insert into UpdateDemo values(1,'John');
Query OK, 1 row affected (0.13 sec)
mysql> insert into UpdateDemo values(2,'Carol');
Query OK, 1 row affected (0.13 sec)
mysql> insert into UpdateDemo values(3,'Smith');
Query OK, 1 row affected (0.18 sec)
mysql> insert into UpdateDemo values(4,'David');
Query OK, 1 row affected (0.15 sec)

Now, display all records from the table with the help of select statement. The query is as follows −

mysql> select *from UpdateDemo;

Here is the output −

+------+-------+
| id   | Name  |
+------+-------+
|    1 | John  |
|    2 | Carol |
|    3 | Smith |
|    4 | David |
+------+-------+
4 rows in set (0.00 sec)

Here is the JAVA code to update records from a MySQL database. We will establish a Java Connection to our MySQL database −

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
import com.mysql.jdbc.Statement;
public class JavaUpdateDemo {
   public static void main(String[] args) {
      Connection conn = null;
      Statement stmt = null;
      try {
         try {
            Class.forName("com.mysql.jdbc.Driver");
         } catch (Exception e) {
            System.out.println(e);
         }
         conn = (Connection) DriverManager.getConnection("jdbc:mysql://localhost/business", "Manish", "123456");
         System.out.println("Connection is created successfully:");
         stmt = (Statement) conn.createStatement();
         String query1 = "update UpdateDemo set Name='Johnson' " + "where id in(1,4)";
         stmt.executeUpdate(query1);
         System.out.println("Record has been updated in the table successfully..................");
      } catch (SQLException excep) {
         excep.printStackTrace();
      } catch (Exception excep) {
         excep.printStackTrace();
      } finally {
         try {
            if (stmt != null)
            conn.close();
         } catch (SQLException se) {}
         try {
            if (conn != null)
            conn.close();
         } catch (SQLException se) {
            se.printStackTrace();
         }
      }
      System.out.println("Please check it in the MySQL Table. Record is now updated.......");
   }
}

Here is the output −

We have updated data with id 1 and 4. The Name column with “Johnson” have been updated. The following is the query to check whether the table data has been updated or not with the help of select statement.

mysql> select *from UpdateDemo;

The following is the output −

+------+---------+
| id   | Name    |
+------+---------+
| 1    | Johnson |
| 2    | Carol   |
| 3    | Smith   |
| 4    | Johnson |
+------+---------+
4 rows in set (0.00 sec)

Look at the above output, the id 1 and 4 is updated.

George John

Published on 21-Dec-2018 08:05:41

Previous Page Print Page