Update MySQL Table using JDBC [Easy Guide]

In this tutorial, you’ll learn how to update a table from a java application using JDBC Driver. In SQL, an update statement refers to modifying existing records in a table. For example, You have an employee table, which has columns like name, department, and email.

Also read: Insert Data into MySQL Database using JDBC

There was a particular record, where the department was named Textile but it was supposed to be Telecom. In such cases, we use an update statement to modify the record which already exists on our database or table.

Prerequisites

For this tutorial, make sure your desktop/laptop is already set up with the following applications:

• MySQL, as we’ll use MySQL RDBMS. https://dev.MySQL.com/downloads/
• MySQL Driver, as we need to connect to the java application and therefore we’ll need JDBC driver: https://dev.MySQL.com/downloads/connector/j/
• Java, as we’ll be coding in a java application: https://www.oracle.com/java/technologies/downloads/
• Eclipse is an IDE(Integrated Development Environment), we’ll be using Eclipse IDE for the application development today, but if you want to go with any other IDE like VScode, Netbeans, etc, you are welcome. It is recommended that you use IDE as it eases out a lot for you automatically which saves your time: https://www.eclipse.org/downloads/

If you are connecting to JDBC for the first time, We recommend you to follow the previous tutorial on how to connect MySQL database using JDBC.

Now, we are all through with our setup and good to go for the tutorial.

Create A Table In MySQL

For this tutorial, you must have an existing table in your database, if you do have a table that you want to update then you may proceed with the next step. Otherwise, you can follow along.

Open MySQL Command-Line client, we’ll be creating an employee table that will have three columns, name, department, and email.

To see view available databases:

SHOW DATABASES;Code language: SQL (Structured Query Language) (sql)

To create a new table or use an existing table:

CREATE DATABASE databasename;
USE tablename;Code language: SQL (Structured Query Language) (sql)

To view existing tables or to create a new table:

SHOW TABLES;
CREATE TABLE tablename (field1 datatype, field2 datatype,..);Code language: SQL (Structured Query Language) (sql)

To insert values into a table:

INSERT INTO TABLE (field1, field2,..)
VALUES
(value1, value2, ..);Code language: SQL (Structured Query Language) (sql)

Create Java Application in Eclipse IDE

Open Eclipse Workspace, create a java project by clicking on File -> New -> Java Project. If you already have an existing project you can just right-click on it and create a new class. Name the class as UpdateSQL or UpdateData.

Update MySQL table using JDBC

To update a MySQL table from a java application, we use JDBC Driver, there are 4 steps we need to follow:

1. Create a Connection
2. Create a Statement
3. Execute Query
4. Close connection

We are using the Employee table, we’ll update the record that has name = ‘Matt’ and we’ll change the department name form ‘Textile’ to ‘Telecom’. The below java code will help you to perform that:

package work;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class UpdateSQL {

	public static void main(String[] args) {
		String url = "jdbc:MySQL://localhost:3306/work";
		String user ="root";
		String password = "root";
		try {
			//1. Create a connection
			Connection myConn = DriverManager.getConnection(url,user,password);
			
			//2. Create a statement
			Statement myStmt = myConn.createStatement();
			
			//3. Execute Query
			String sql = "update employee "
					+"set dept = 'Telecom"
					+ " where name = 'Matt'";

			myStmt.executeUpdate(sql);
			System.out.println("Update complete.");

            //4. Execute Query
			myConn.close();
		}
		catch(Exception e){
			e.printStackTrace();
		}
	}
}Code language: Java (java)

After writing the java update code, you can right-click and run the application as Java Application. The application will run successfully and you’ll get the console output as Update complete.

To check if the table is updated, we can check in by writing the select statement for the employee table.

You can see in the above image, that Matt’s department has now been updated from ‘Textile’ to ‘Telecom’ using JDBC.

This is how you can update any record or records in a table through a java application using JDBC.