JDBC

Update records in database with JdbcTemplate

Byron KiourtzoglouNovember 11th, 2012Last Updated: September 25th, 2013

0 852 2 minutes read

In this example we shall show you how to update 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. To update records in a database using the JdbcTemplate class one should perform the following steps:

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 implement it using the org.springframework.jdbc.datasource.DriverManagerDataSource. 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 having the above configuration. Here in getDatasource() method we create a new Datasource and configure it.
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 update 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,

as described in the code snippet below.

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 UpdateRecordsInDatabaseWithJdbcTemplate {
	
	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 updateSql = "UPDATE employee SET name = ? WHERE id = ?";

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

  JdbcTemplate template = new JdbcTemplate(dataSource);


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

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


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

  

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

  
    }
	
	public static DriverManagerDataSource getDataSource() {

  DriverManagerDataSource dataSource = new DriverManagerDataSource();

  dataSource.setDriverClassName(driverClassName);

  dataSource.setUrl(url);

  dataSource.setUsername(dbUsername);

  dataSource.setPassword(dbPassword);

  return dataSource;
    }
	
}

CREATE TABLE `companydb`.`employee` (
  `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `surname` VARCHAR(45) NOT NULL,
  `title` VARCHAR(45) NOT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
);

Output: