Java JDBC Tutorial – Part 4: Deleting Data from a Database

In this video tutorial, you will learn how to delete data from a database with Java JDBC.

Please subscribe 🙂

Video Transcript

Time – 00:00
Hello. In this video, you will learn how to use Java JDBC to delete data from a database. In regards to Prep Work, you must have a database installed. This demo will use MySQL.

Time: 00:13
You also need to have the database driver configured. Again, we’ll use MySQL. If you need help on how to do this, then please look at my other video “Connecting to a MySQL Database with Java.”

Time – 00:31
In this example, we will use the employees’ table. This table has columns for employee ID, last name, first name, and e-mail address. Let’s switch over to the MySQL tool so we can review the data.

Time – 00:45
As you can see, we have the employees’ table in place. Make note of the entry for David Brown. We will delete this employee from the database. The SQL command that we’ll use to delete an employee is delete from employees where last_name=’Brown’;

Time – 01:09
Let’s move and try development environment and get started. We’ll start with this main program, that’s going to have three variables for the user, password, and the connection information. The first thing we’ll do is we’ll get a connection to the database by saying DriverManager.getConnection, passing in the params. Next, we’ll create the statement using myConn.createStatement. Then we’ll define the SQL query string. In this case,
delete from employees where last_name=’Brown’

Time – 01:43
Now, actually execute that SQL statement by saying executeUpdate and the database will return the number of rows affected. Also, I will print those out. Then finally, I’ll just print out “Delete complete.” so that we know that I’m done.

Time – 02:01
Now, let’s run our application. I’ll right click; I’ll select “Run As”, “Java Application.” Now, let it execute. Now, it will print out the number of Rows affected and Delete complete. Now, let’s move over to our SQL Query tool and very the results.

Time – 02:24
If you note here with the previous entry, we had a listing for a David Brown. I’ll refresh this Query, and now note that David Brown is gone. A Java Application was actually successful in deleting that entry.

Time – 02:37
Great, so in summary, you learned how to use Java JDBC to delete data from a database. If you would like to download the source code, please visit luv2code.com.

Source Code: Driver.java

[code]
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class Driver {

public static void main(String[] args) throws SQLException {

String url = "jdbc:mysql://localhost:3306/demo";
String user = "student";
String password = "student";

Connection myConn = null;
Statement myStmt = null;

try {
// 1. Get a connection to database
myConn = DriverManager.getConnection(url, user, password);

// 2. Create a statement
myStmt = myConn.createStatement();

// 3. Execute SQL query
String sql = "delete from employees where last_name=’Brown’";

int rowsAffected = myStmt.executeUpdate(sql);

System.out.println("Rows affected: " + rowsAffected);
System.out.println("Delete complete.");
}
catch (Exception exc) {
exc.printStackTrace();
}
finally {
if (myStmt != null) {
myStmt.close();
}

if (myConn != null) {
myConn.close();
}
}
}
}
[/code]

MySQL Script for Creating the Database table

File: sql/table-setup.sql

[code]
create database if not exists demo;

use demo;

drop table if exists employees;

CREATE TABLE `employees` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`last_name` varchar(64) DEFAULT NULL,
`first_name` varchar(64) DEFAULT NULL,
`email` varchar(64) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;

INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`) VALUES (1,’Doe’,’John’,’john.doe@foo.com’);
INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`) VALUES (2,’Public’,’Mary’,’mary.public@foo.com’);
INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`) VALUES (3,’Queue’,’Susan’,’susan.queue@foo.com’);

[/code]
Download Source Code: jdbcdemo-delete.zip