JDBC in Java - Delete a row in JDBC Hello World Example

Welcome to fourth part of 'JDBC in Java tutorial Series'. In previous discussions we came across, 'How to create a connection in JDBC ', 'Create table in Java JDBC ' and 'Insert data in a table in JDBC'. In this particular blog we will come across 'How to delete a row from a table in JDBC'. We will create a connection to db and that obtain a statement to execute query on it.

Project Structure

This is the overall project structure for today's discussion, we have added a 'my-sql-connector,jar' in lib folder to make the connection happen. Please make sure that the jar file is being added to the project's classpath. 'CreateConnection.java' class contains the code to create a connection with database and 'CreateTable.java' class contains the actual code to create a table in database using JDBC. 'InserData.java' is used to run a jdbc insert query. 'JdbcDelete.java' is the main implementation class where actual delete query in executed.



Database

This is the sample database that we have used to get a connection and create a table, execute the code below in your MySql query editor.

-- Dumping database structure for checkjdbc
CREATE DATABASE IF NOT EXISTS `checkjdbc` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `checkjdbc`;



How to delete a row from a table in JDBC

Deleting a row from a table in jdbc is a very simple process we just need to obtain a jdbc connection and suitable statement. Then a simple sql create query is executed in that statement.

src\com\beingjavaguys\jdbc\DeleteData.java

This is a simple java class, all useful parts of code are explained in inline comments :

package com.beingjavaguys.jdbc; import java.sql.Connection; import java.sql.SQLException; import java.sql.Statement; public class DeleteData { public void deleteData(int id) { Statement stmt = null; Connection con = null; String query = "delete from user where id=" + id; try { con = new CreateConnection().getConnection("checkjdbc", "root", "root"); stmt = con.createStatement(); stmt.execute(query); System.out.println("Data deleted for id = " + id + " !"); } catch (SQLException e1) { e1.printStackTrace(); } finally { if (stmt != null) { try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); } } if (con != null) { try { con.close(); } catch (SQLException e) { e.printStackTrace(); } } } } }


How to insert values in a table in JDBC

Insert values in a table in jdbc is a very simple process we just need to obtain a jdbc connection and suitable statement. Then a simple sql create query is executed in that statement.

src\com\beingjavaguys\jdbc\InsertData.java

This is a simple java class, all useful parts of code are explained in inline comments :

package com.beingjavaguys.jdbc; import java.sql.Connection; import java.sql.SQLException; import java.sql.Statement; import com.beingjavaguys.domain.Student; public class InsertData { public void insertData(Student student) { Statement stmt = null; Connection con = null; // create sql insert query String query = "insert into user values(" + student.getId() + ",'" + student.getFirstName() + "','" + student.getLastName() + "','" + student.getEmail() + "','" + student.getPhone() + "')"; try { // get connection to db con = new CreateConnection().getConnection("checkjdbc", "root", "root"); // get a statement to execute query stmt = con.createStatement(); // executed insert query stmt.execute(query); System.out.println("Data inserted in table !"); } catch (SQLException e1) { e1.printStackTrace(); } finally { if (stmt != null) { try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); } } if (con != null) { try { con.close(); } catch (SQLException e) { e.printStackTrace(); } } } } }


How to Create a table in JDBC

Create a table in jdbc is a very simple process we just need to obtain a jdbc connection and suitable statement. Then a simple sql create query is executed in that statement.

\src\com\beingjavaguys\jdbc\CreateTable.java

This is a simple java class, all useful parts of code are explained in inline comments :

package com.beingjavaguys.jdbc; import java.sql.Connection; import java.sql.SQLException; import java.sql.Statement; public class CreateTable { public void createTable() { Statement stmt = null; Connection con = null; // storing a simple sql query into string object String query = "create table user (" + "id int," + "first_name varchar(30)," + "last_name varchar(30)," + "email varchar(30)," + "phone varchar(30))"; try { // created connection to db con = new CreateConnection().getConnection("checkjdbc", "root", "root"); // obtained a statement object to execute the required query stmt = con.createStatement(); // executed the query on obtained statement stmt.execute(query); System.out.println("Table created !"); } catch (SQLException e1) { e1.printStackTrace(); } finally { if (stmt != null) { try { // close statement to free the resources stmt.close(); } catch (SQLException e) { e.printStackTrace(); } } if (con != null) { try { // closed the connection to db con.close(); } catch (SQLException e) { e.printStackTrace(); } } } } }


Create a connection in JDBC code

This class contains the code to create a connection to database, detailed explanation can be found from here.

src\com\beingjavaguys\jdbc\CreateConnection.java

package com.beingjavaguys.jdbc;  
  
import java.sql.DriverManager;  
import java.sql.SQLException;  
import java.sql.Connection;  
  
public class CreateConnection {  
  
 // defining driver name, mysql-jdbc driver in this case  
 String driverName = "com.mysql.jdbc.Driver";  
 Connection con = null;  
  
 // database url string with hostname and port on which db is running  
 String url = "jdbc:mysql://localhost:3306/";  
  
 public Connection getConnection(String dbName, String username,  
   String password) {  
  
  // creating connection url  
  String connectionUrl = url + dbName;  
  
  try {  
   // registers the specified driver class into memory  
   Class.forName(driverName);  
  } catch (ClassNotFoundException e) {  
   e.printStackTrace();  
  }  
  
  try {  
   // returns a connection objcet by selecting an appropriate driver  
   // for specified connection URL  
   con = DriverManager  
     .getConnection(connectionUrl, username, password);  
  } catch (SQLException e) {  
   e.printStackTrace();  
  }  
  return con;  
 }  
}  



Implementation class code

This is a simple java class, we have called the required code here to see the actual implementation.

src\com\beingjavaguys\impl\Implementation.java

package com.beingjavaguys.impl;  
  
import com.beingjavaguys.domain.Student;  
import com.beingjavaguys.jdbc.CreateTable;  
import com.beingjavaguys.jdbc.DeleteData;  
import com.beingjavaguys.jdbc.InsertData;  
  
public class Implementation {  
  
 public static void main(String args[]) {  
  
  CreateTable createTable = new CreateTable();  
  createTable.createTable();  
  
  Student student = new Student();  
  student.setId(1);  
  student.setFirstName("ankush");  
  student.setLastName("thakur");  
  student.setEmail("beingjavaguy@gmail.com");  
  student.setPhone("8987676754");  
  
  InsertData insertData = new InsertData();  
  insertData.insertData(student);  
  
  DeleteData deleteData = new DeleteData();  
  deleteData.deleteData(1);  
  
 }  
} 


In this particular blog we came across how to delete a row from a table in java JDBC. In upcoming blogs we will see more about JDBC including JDBC Create Connection & JDBC Create Table & JDBC Inset Query and JDBC Update Implementation in Java.