Home » Enterprise Java » spring » JDBC » Delete records in database with JdbcTemplate

About Byron Kiourtzoglou

Byron Kiourtzoglou
Byron is a master software engineer working in the IT and Telecom domains. He is an applications developer in a wide variety of applications/services. He is currently acting as the team leader and technical architect for a proprietary service creation and integration platform for both the IT and Telecom industries in addition to a in-house big data real-time analytics solution. He is always fascinated by SOA, middleware services and mobile development. Byron is co-founder and Executive Editor at Java Code Geeks.

Delete records in database with JdbcTemplate

With this example we are going to demonstrate how to delete records in a database, using the JdbcTemplate class provided by the Spring Framework. The JdbcTemplate class is the central class in the JDBC core package. It simplifies the use of JDBC and helps to avoid common errors. It executes core JDBC workflow, leaving application code to provide SQL and extract results. This class executes SQL queries or updates, initiating iteration over ResultSets and catching JDBC exceptions. In short, to delete records in a database, using the JdbcTemplate class you should:

  • Use the DataSource class, a utility class that provides connection to the database. It is part of the JDBC specification and allows a container or a framework to hide connection pooling and transaction management issues from the application code. We use org.springframework.jdbc.datasource.DriverManagerDataSource to implement it.
  • Set the credentials needed to the datasource, using the inherited methods setPassword(String password), setUrl(String url) and setUsername(String username) API methods of AbstractDriverBasedDataSource class, as also the setDriverClassName(String driverClassName) API method of DriverManagerDataSource. Create a new Datasource object with the above configuration.
  • Create a new JdbcTemplate object, with the given datasource to obtain connections from.
  • Use the update(String sql, Object args, int[] argTypes) API method of JdbcTemplate, to issue the SQL delete operation via a prepared statement, binding the given arguments. The given parameters are the String containing the sql query, the arguments to bind the query, and the types of the arguments.

Let’s take a look at the code snippet that follows:

package com.javacodegeeks.snippets.enterprise;

import java.sql.Types;

import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

public class DeleteRecordsInDatabaseWithJdbcTemplate {

	private static final String driverClassName = "com.mysql.jdbc.Driver";
	private static final String url = "jdbc:mysql://localhost/companydb";
	private static final String dbUsername = "jcg";
	private static final String dbPassword = "jcg";

	private static final String deleteSql = "DELETE FROM employee WHERE id = ?";

	private static DataSource dataSource;
	
	public static void main(String[] args) throws Exception {
	
		dataSource = getDataSource();
		
		deleteRecord(1L);
		
	}
	
	public static void deleteRecord(Long id) {
		

  JdbcTemplate template = new JdbcTemplate(dataSource);


  // define query arguments
		Object[] params = { id };

  
		// define SQL types of the arguments
		int[] types = {Types.BIGINT};


  int rows = template.update(deleteSql, params, types);

  

  System.out.println(rows + " row(s) deleted.");

  
    }
	
	public static DriverManagerDataSource getDataSource() {

  DriverManagerDataSource dataSource = new DriverManagerDataSource();

  dataSource.setDriverClassName(driverClassName);

  dataSource.setUrl(url);

  dataSource.setUsername(dbUsername);

  dataSource.setPassword(dbPassword);

  return dataSource;
    }
	
}

Output:

1 row(s) deleted.

 
This was an example of how to delete records in a database, using the JdbcTemplate class provided by the Spring Framework.

(No Ratings Yet)
Start the discussion Views Tweet it!

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

 

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design

 

and many more ....

 

Receive Java & Developer job alerts in your Area

 

Leave a Reply

avatar
  Subscribe  
Notify of