Java and J2EE Tutorials, Jsp and Servlet Tutorials, Spring MVC, Solr, XML, JSON Examples, Hibernate & Struts 2 Hello World projects



Monday, 24 June 2013

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.







Thanks for reading !
Being Java Guys Team




0 comments:

Post a Comment

Like Us on Facebook


Like Us On Google+



Contact

Email: neel4soft@gmail.com
Skype: neel4soft