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.