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.