Spring JDBC Example

Filed Under: Spring

Spring JDBC is the topic of this tutorial. Databases are integral part of most of the Enterprise Applications. So when it comes to a Java EE framework, having good integration with JDBC is very important.

Spring JDBC

spring jdbc, spring jdbc example, spring JDBCTemplate, JDBCTemplate

Spring Framework provides excellent integration with JDBC API and provides JdbcTemplate utility class that we can use to avoid bolier-plate code from our database operations logic such as Opening/Closing Connection, ResultSet, PreparedStatement etc.

Let’s first look at a simple Spring JDBC example application and then we will see how JdbcTemplate class can help us in writing modular code with ease, without worrying whether resources are closed properly or not.

Spring Tool Suite to develop Spring based applications is very helpful, so we will use STS to create our Spring JDBC application. Our final project structure will look like below image.

Spring JDBC Example, Spring JDBC, Spring JdbcTemplate

Create a simple Spring Maven Project from the STS Menu, you can choose whatever name you like or stick with my project name as SpringJDBCExample.

Spring JDBC Dependencies

First of all we need to include Spring JDBC and Database drivers in the maven project pom.xml file. My final pom.xml file looks like below.


<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>org.springframework.samples</groupId>
	<artifactId>SpringJDBCExample</artifactId>
	<version>0.0.1-SNAPSHOT</version>

	<properties>

		<!-- Generic properties -->
		<java.version>1.6</java.version>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>

		<!-- Spring -->
		<spring-framework.version>4.0.2.RELEASE</spring-framework.version>

		<!-- Logging -->
		<logback.version>1.0.13</logback.version>
		<slf4j.version>1.7.5</slf4j.version>

	</properties>

	<dependencies>
		<!-- Spring and Transactions -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-context</artifactId>
			<version>${spring-framework.version}</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-tx</artifactId>
			<version>${spring-framework.version}</version>
		</dependency>

		<!-- Spring JDBC Support -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-jdbc</artifactId>
			<version>${spring-framework.version}</version>
		</dependency>
		
		<!-- MySQL Driver -->
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>5.0.5</version>
		</dependency>

		<!-- Logging with SLF4J & LogBack -->
		<dependency>
			<groupId>org.slf4j</groupId>
			<artifactId>slf4j-api</artifactId>
			<version>${slf4j.version}</version>
			<scope>compile</scope>
		</dependency>
		<dependency>
			<groupId>ch.qos.logback</groupId>
			<artifactId>logback-classic</artifactId>
			<version>${logback.version}</version>
			<scope>runtime</scope>
		</dependency>

	</dependencies>
</project>

Most of the part is automatically generated by STS, however I have update Spring Framework version to use latest version as 4.0.2.RELEASE.

Also we have added required artifacts spring-jdbc and mysql-connector-java. First one contains the Spring JDBC support classes and second one is database driver. I am using MySQL database for our testing purposes, so I have added MySQL JConnector jar dependencies. If you are using some other RDBMS then you should make the corresponding changes in the dependencies.

Spring JDBC Example – Database Setup

Let’s create a simple table that we will use in our application for CRUD operations example.


CREATE TABLE `Employee` (
  `id` int(11) unsigned NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `role` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Spring JDBC Example – Model Class

We will use DAO Pattern for JDBC operations, so let’s create a java bean that will model our Employee table.


package com.journaldev.spring.jdbc.model;

public class Employee {

	private int id;
	private String name;
	private String role;
	
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getRole() {
		return role;
	}
	public void setRole(String role) {
		this.role = role;
	}
	
	@Override
	public String toString(){
		return "{ID="+id+",Name="+name+",Role="+role+"}";
	}
}

Spring JDBC Example – DAO Interface and Implementation

For DAO pattern, we will first have an interface declaring all the operations we want to implement.


package com.journaldev.spring.jdbc.dao;

import java.util.List;

import com.journaldev.spring.jdbc.model.Employee;

//CRUD operations
public interface EmployeeDAO {
	
	//Create
	public void save(Employee employee);
	//Read
	public Employee getById(int id);
	//Update
	public void update(Employee employee);
	//Delete
	public void deleteById(int id);
	//Get All
	public List<Employee> getAll();
}

package com.journaldev.spring.jdbc.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import com.journaldev.spring.jdbc.model.Employee;

public class EmployeeDAOImpl implements EmployeeDAO {

	private DataSource dataSource;

	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}

	@Override
	public void save(Employee employee) {
		String query = "insert into Employee (id, name, role) values (?,?,?)";
		Connection con = null;
		PreparedStatement ps = null;
		try{
			con = dataSource.getConnection();
			ps = con.prepareStatement(query);
			ps.setInt(1, employee.getId());
			ps.setString(2, employee.getName());
			ps.setString(3, employee.getRole());
			int out = ps.executeUpdate();
			if(out !=0){
				System.out.println("Employee saved with id="+employee.getId());
			}else System.out.println("Employee save failed with id="+employee.getId());
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			try {
				ps.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	@Override
	public Employee getById(int id) {
		String query = "select name, role from Employee where id = ?";
		Employee emp = null;
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			con = dataSource.getConnection();
			ps = con.prepareStatement(query);
			ps.setInt(1, id);
			rs = ps.executeQuery();
			if(rs.next()){
				emp = new Employee();
				emp.setId(id);
				emp.setName(rs.getString("name"));
				emp.setRole(rs.getString("role"));
				System.out.println("Employee Found::"+emp);
			}else{
				System.out.println("No Employee found with id="+id);
			}
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			try {
				rs.close();
				ps.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return emp;
	}

	@Override
	public void update(Employee employee) {
		String query = "update Employee set name=?, role=? where id=?";
		Connection con = null;
		PreparedStatement ps = null;
		try{
			con = dataSource.getConnection();
			ps = con.prepareStatement(query);
			ps.setString(1, employee.getName());
			ps.setString(2, employee.getRole());
			ps.setInt(3, employee.getId());
			int out = ps.executeUpdate();
			if(out !=0){
				System.out.println("Employee updated with id="+employee.getId());
			}else System.out.println("No Employee found with id="+employee.getId());
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			try {
				ps.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	@Override
	public void deleteById(int id) {
		String query = "delete from Employee where id=?";
		Connection con = null;
		PreparedStatement ps = null;
		try{
			con = dataSource.getConnection();
			ps = con.prepareStatement(query);
			ps.setInt(1, id);
			int out = ps.executeUpdate();
			if(out !=0){
				System.out.println("Employee deleted with id="+id);
			}else System.out.println("No Employee found with id="+id);
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			try {
				ps.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	@Override
	public List<Employee> getAll() {
		String query = "select id, name, role from Employee";
		List<Employee> empList = new ArrayList<Employee>();
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			con = dataSource.getConnection();
			ps = con.prepareStatement(query);
			rs = ps.executeQuery();
			while(rs.next()){
				Employee emp = new Employee();
				emp.setId(rs.getInt("id"));
				emp.setName(rs.getString("name"));
				emp.setRole(rs.getString("role"));
				empList.add(emp);
			}
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			try {
				rs.close();
				ps.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return empList;
	}

}

The implementation of CRUD operations are simple to understand. If you want to learn more about DataSource, please read JDBC DataSource Example.

Spring JDBC Example – Bean Configuration

If you look at all the classes above, they are all using standard JDBC API and there is no reference to Spring JDBC framework. Spring JDBC framework classes comes into picture when we create Spring Bean Configuration file and define the beans. We will create the DataSource in the Spring Bean context file and set it to our DAO implementation class.

My Spring Bean Configuration file looks like below.


<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd">

	<bean id="employeeDAO" class="com.journaldev.spring.jdbc.dao.EmployeeDAOImpl">
		<property name="dataSource" ref="dataSource" />
	</bean>
	
	<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">

		<property name="driverClassName" value="com.mysql.jdbc.Driver" />
		<property name="url" value="jdbc:mysql://localhost:3306/TestDB" />
		<property name="username" value="pankaj" />
		<property name="password" value="pankaj123" />
	</bean>

</beans>

First of all we are creating a DataSource object of class DriverManagerDataSource. This class provides the basic implementation of DataSource that we can use. We are passing MySQL database URL, username and password as properties to the DataSource bean.

Again dataSource bean is set to the EmployeeDAOImpl bean and we are ready with our Spring JDBC implementation. The implementation is loosely coupled and if we want to switch to some other implementation or move to other database server, all we need is to make corresponding changes in the bean configurations. This is one of the major advantage provided by Spring JDBC framework.

Spring JDBC Test Class

Let’s write a simple test class to make sure everything is working fine.


package com.journaldev.spring.jdbc.main;

import java.util.List;
import java.util.Random;

import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.journaldev.spring.jdbc.dao.EmployeeDAO;
import com.journaldev.spring.jdbc.model.Employee;

public class SpringMain {

	public static void main(String[] args) {
		//Get the Spring Context
		ClassPathXmlApplicationContext ctx = new ClassPathXmlApplicationContext("spring.xml");
		
		//Get the EmployeeDAO Bean
		EmployeeDAO employeeDAO = ctx.getBean("employeeDAO", EmployeeDAO.class);
		
		//Run some tests for JDBC CRUD operations
		Employee emp = new Employee();
		int rand = new Random().nextInt(1000);
		emp.setId(rand);
		emp.setName("Pankaj");
		emp.setRole("Java Developer");
		
		//Create
		employeeDAO.save(emp);
		
		//Read
		Employee emp1 = employeeDAO.getById(rand);
		System.out.println("Employee Retrieved::"+emp1);
		
		//Update
		emp.setRole("CEO");
		employeeDAO.update(emp);
		
		//Get All
		List<Employee> empList = employeeDAO.getAll();
		System.out.println(empList);
		
		//Delete
		employeeDAO.deleteById(rand);
		
		//Close Spring Context
		ctx.close();
		
		System.out.println("DONE");
	}

}

I am using Random Class to generate random number for employee id. When we run above program, we get following output.


Mar 25, 2014 12:54:18 PM org.springframework.context.support.ClassPathXmlApplicationContext prepareRefresh
INFO: Refreshing org.springframework.context.support.ClassPathXmlApplicationContext@4b9af9a9: startup date [Tue Mar 25 12:54:18 PDT 2014]; root of context hierarchy
Mar 25, 2014 12:54:18 PM org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
INFO: Loading XML bean definitions from class path resource [spring.xml]
Mar 25, 2014 12:54:19 PM org.springframework.jdbc.datasource.DriverManagerDataSource setDriverClassName
INFO: Loaded JDBC driver: com.mysql.jdbc.Driver
Employee saved with id=726
Employee Found::{ID=726,Name=Pankaj,Role=Java Developer}
Employee Retrieved::{ID=726,Name=Pankaj,Role=Java Developer}
Employee updated with id=726
[{ID=726,Name=Pankaj,Role=CEO}]
Employee deleted with id=726
Mar 25, 2014 12:54:19 PM org.springframework.context.support.ClassPathXmlApplicationContext doClose
INFO: Closing org.springframework.context.support.ClassPathXmlApplicationContext@4b9af9a9: startup date [Tue Mar 25 12:54:18 PDT 2014]; root of context hierarchy
DONE

Spring JdbcTemplate Example

If you look at the DAO implementation class, there is a lot of boiler-plate code where we are opening and closing Connection, PreparedStatements and ResultSet. This can lead to resource leak if someone forgets to close the resources properly. We can use org.springframework.jdbc.core.JdbcTemplate class to avoid these errors. Spring JdbcTemplate is the central class in Spring JDBC core package and provides a lot of methods to execute queries and automatically parse ResultSet to get the Object or list of Objects.

All we need is to provide the arguments as Object array and implement Callback interfaces such as PreparedStatementSetter and RowMapper for mapping arguments or converting ResultSet data to bean objects.

Let’s look at another implementation of EmployeeDAO where we will use Spring JdbcTemplate class for executing different types of queries.


package com.journaldev.spring.jdbc.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import com.journaldev.spring.jdbc.model.Employee;

public class EmployeeDAOJDBCTemplateImpl implements EmployeeDAO {

	private DataSource dataSource;

	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}
	
	@Override
	public void save(Employee employee) {
		String query = "insert into Employee (id, name, role) values (?,?,?)";
		
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		
		Object[] args = new Object[] {employee.getId(), employee.getName(), employee.getRole()};
		
		int out = jdbcTemplate.update(query, args);
		
		if(out !=0){
			System.out.println("Employee saved with id="+employee.getId());
		}else System.out.println("Employee save failed with id="+employee.getId());
	}

	@Override
	public Employee getById(int id) {
		String query = "select id, name, role from Employee where id = ?";
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		
		//using RowMapper anonymous class, we can create a separate RowMapper for reuse
		Employee emp = jdbcTemplate.queryForObject(query, new Object[]{id}, new RowMapper<Employee>(){

			@Override
			public Employee mapRow(ResultSet rs, int rowNum)
					throws SQLException {
				Employee emp = new Employee();
				emp.setId(rs.getInt("id"));
				emp.setName(rs.getString("name"));
				emp.setRole(rs.getString("role"));
				return emp;
			}});
		
		return emp;
	}

	@Override
	public void update(Employee employee) {
		String query = "update Employee set name=?, role=? where id=?";
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		Object[] args = new Object[] {employee.getName(), employee.getRole(), employee.getId()};
		
		int out = jdbcTemplate.update(query, args);
		if(out !=0){
			System.out.println("Employee updated with id="+employee.getId());
		}else System.out.println("No Employee found with id="+employee.getId());
	}

	@Override
	public void deleteById(int id) {

		String query = "delete from Employee where id=?";
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		
		int out = jdbcTemplate.update(query, id);
		if(out !=0){
			System.out.println("Employee deleted with id="+id);
		}else System.out.println("No Employee found with id="+id);
	}

	@Override
	public List<Employee> getAll() {
		String query = "select id, name, role from Employee";
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		List<Employee> empList = new ArrayList<Employee>();

		List<Map<String,Object>> empRows = jdbcTemplate.queryForList(query);
		
		for(Map<String,Object> empRow : empRows){
			Employee emp = new Employee();
			emp.setId(Integer.parseInt(String.valueOf(empRow.get("id"))));
			emp.setName(String.valueOf(empRow.get("name")));
			emp.setRole(String.valueOf(empRow.get("role")));
			empList.add(emp);
		}
		return empList;
	}

}

Important points to look into above code for Spring JdbcTemplate are:

  • Use of Object array to pass PreparedStatement arguments, we could also use PreparedStatementSetter implementation but passing Object array seems easy to use.
  • No code related to opening and closing connections, statements or result set. All that is handled internally by Spring JdbcTemplate class.
  • RowMapper anonymous class implementation to map the ResultSet data to Employee bean object in queryForObject() method.
  • queryForList() method returns list of Map whereas Map contains the row data mapped with key as the column name and value from the database row matching the criteria.

To use Spring JdbcTemplate implementation, all we need is to change the employeeDAO class in the Spring Bean configuration file as shown below.


<bean id="employeeDAO" class="com.journaldev.spring.jdbc.dao.EmployeeDAOJDBCTemplateImpl">
	<property name="dataSource" ref="dataSource" />
</bean>

When you will run the main class, the output of Spring JdbcTemplate implementation will be similar to the one seen above with normal JDBC implementation. That’s all for Spring JDBC Example tutorial, download the sample project from below link and play around with it to learn more.

Comments

  1. Divya says:

    Clear Explanation, Thank you!

  2. Meghana says:

    Hi.,

    Is it possible to use SpringJDBC to call a stored procedure that has TABLE TYPE as its IN and OUT Parameters?
    I have tried “sqlArrayValues” and “SqlReturnArray()” i still get type mismatch.
    I had to use simple JDBC to send TYPE TABLE parameters from a spring boot application.
    Any help would be very much appreciated.

  3. J Manas Kumar says:

    How can I fetch multiple records by single Id??
    even if I write while loop instead of if…the result is same…can anyone help…???

  4. J Manas Kumar says:

    public void delete(int empId) {
    String sql = “delete from Employee where id=?”;
    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

    int out = jdbcTemplate.update(sql,empId);

    if(out !=0){
    System.out.println(“Employee deleted with id=”+empId);
    }else System.out.println(“No Employee found with id=”+empId);

    }

    in the above code I am facing issue at update(sql,,empId);
    but in your code you just simply passed it .
    its saying >>>>>> The method update(String, PreparedStatementSetter) in the type JdbcTemplate is not applicable for the arguments
    (String, int)>>>>>>>>>>>

    and another thing if my emp id is String how can I insert that into db??

    and I m also facing this issue …..

    Employee emp = jdbcTemplate.queryForObject(query, new Object[]{empId}, new RowMapper()

    The type RowMapper is not generic; it cannot be parameterized with arguments

    so can u suggest me anything???

    m Waiting For your reply….

    Thank you./..

  5. Manas Naik says:

    Nice example … Grate efforts. ๐Ÿ™‚

    I found 2 example for retrieve data from database.
    1 – findAll() : Which return multiple record.
    2 – findById() : Which return single value based on primary key as “Id”.

    Can you help me to resolve below query

    How to retrieve data using jdbcTemplate based on name findByName() which return multiple record from data base?

  6. Udit says:

    Precise and Clear. Thanks.

  7. Anupama says:

    Simple and straight forward explanation. Thanks Pankaj.

  8. Colm says:

    Thanks for your efforts, much appreciated.

    I’m using JDBCTemplate to do batch update, but PreparedStatement.setLong() throws a null pointer exception if the value is null:

    preparedStatement.setLong(1, myBean.getLongVal()); // throws null pointer exception if myBean.getLongVal() = null

    Is there a workaround for this? The corresponding database column is nullable, so its ok for it to have a null value. This is not a problem for string fields, i.e. the following is perfectly fine if the value is null:

    preparedStatement.setLong(2, myBean.getStrVal()); // ok if myBean.getStrVal() = null

  9. Praveen Kumar Natarajan says:

    Hi , I’m trying to make it connect to my database(sybase) by joining the drivername,url ,username and password.but when i do so i get an exception like this
    Exception in thread “main” org.springframework.beans.factory.BeanCreationException: Error creating bean with name ’employeeDAO’ defined in class path resource [spring.xml]: Cannot resolve reference to bean ‘dataSource’ while setting bean property ‘dataSource’; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name ‘dataSource’ defined in class path resource [spring.xml]: Error setting property values; nested exception is org.springframework.beans.PropertyBatchUpdateException; nested PropertyAccessExceptions (1) are:
    PropertyAccessException 1: org.springframework.beans.MethodInvocationException: Property ‘driverClassName’ threw exception; nested exception is java.lang.IllegalStateException: Could not load JDBC driver class [com.sybase.jdbc3.jdbc.SybDriver]
    at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveReference(BeanDefinitionValueResolver.java:328)

    1. Pankaj says:

      Looks like JDBC jar is missing from your project.

  10. sandeep says:

    when i run programme with public class EmployeeDAOJDBCTemplateImpl {

    }
    Exception in thread “main” java.lang.NoClassDefFoundError: Could not initialize class org.springframework.jdbc.core.StatementCreatorUtils
    please guys help me

    1. Atif says:

      The version of your Spring core and JDBC should be the same.You can change that in pom.xml

      1. Pankaj says:

        Please check the versions and compare with my pom.xml code. These issue happens usually because of version mismatch and some class not compatible with other versions.

  11. Sayali says:

    How we can call methods asynchronously to allow the queries to be performed in parallel. I would like to apply multi-threading concept here to load many-many users data parallel. How we can do that? Please help/suggest.

    Thank you,
    Sayali

    1. Pankaj says:

      Hi Sayali,

      I have not tried this, so not sure. I would love to see our readers to pitch in for some solution.

  12. vdep says:

    Hi All,
    I’am trying to query join operations between 2 model classes (lets say there is a manager class in addition to employee class), How does one do the below step:

    while(rs.next()){
    Employee emp = new Employee();
    emp.setId(rs.getInt(“id”));
    emp.setName(rs.getString(“name”));
    emp.setRole(rs.getString(“role”));
    empList.add(emp);
    }

    I have class variables from both classes(lets say empid, managerid) in select part and class employee doesnt have managerid. so do i have to create a new class containing all the variables?

    thanks

    1. Pankaj says:

      Look for ResultSetExtractor API, you need to implement it based on your requirements.

  13. Ravi says:

    Nice example for beginners. 100% clear !!!

  14. deekonba says:

    So far so good, Pankaj. Thanks for your example.
    Do you have any spring’s jdbctemplate example in spring4 with annotations and WITHOUT xml files but just only the coding?

    1. Pankaj says:

      I don’t have it handy, but you can certainly convert XML based mapping to Annotation based mapping, check below tutorial for getting started.

      https://www.journaldev.com/2461/spring-ioc-container-and-spring-bean-example-tutorial

  15. Zac C says:

    Is your Employee.sql db related to your spring.xml file where it says::

    1. Zac C says:

      Sorry. I don’t know what happened, I meant to attach:

      1. Pankaj says:

        You can’t attach a file, please paste the content here. Query??

  16. Gerson Pereyra Garayar says:

    Hi, Pankaj
    I’m from Peru and this tutotial is a great work but i have a mistake with the list of Products(instead of employee)
    the others methods are OK (Save, Update, Delete and Get) but the method List all Products
    in your example is the result: [{ID=726,Name=Pankaj,Role=CEO}]
    but in my case is a project of 2 tutorials ( this and Spring MVC Tutorial for Beginners with Spring Tool Suite) and the resulta was that:[com.tienda.spring.model.Producto@23d2a7e8]

    Thanks
    PD: Sorry if my english is bad

    1. Pankaj says:

      You need to override toString() method in your model bean.

  17. Chandra says:

    Good tutorial for all.

    1. Pankaj says:

      Thanks Chandra, appreciate it.

  18. bhanu says:

    I like tutorials with good terminology like your’s….we can find bunch of examples in internet but less with proper java terminology

    1. Pankaj says:

      Thanks Bhanu for nice words. ๐Ÿ™‚

  19. Sanjay Jain says:

    Hi Nice article,
    Just one query, In case of Spring Jdbc if id (primary key) is auto generated at database level, then How can I get that id in save api call?

    1. Pankaj says:

      You can use SimpleJdbcInsert class in this case, check executeAndReturnKey method.

  20. Ferna says:

    thank you very much ๐Ÿ™‚

    1. Pankaj says:

      you are welcome friend.

  21. Rahul Gupta says:

    Hello Sir,

    I always follow your tutorials. Thanks for sharing such a great knowledge.
    While running above project, the values are not getting reflect into the database. I am using Oracle DB.
    I made all the required changes. Its showing required output on console but not in database.!! Could you please solve my this query..??
    I will truly appreciate your help..!!

    Thanks in advance..!!

    1. Pankaj says:

      I think auto commit is false in your case and you have missed the commit call.

  22. oak says:

    thanks for the example, but could you explain how to do it with a join?
    For example, if we have two tables: Employees and Salary and we want to join them on their id to get salararies for each employee, how should be the Model class?
    It just has to have all the fields that we use in sql Select and all the rest logic remains the same?

    1. Pankaj says:

      Check ResultSetExtractor interface.

  23. Monirul Islam says:

    Hi,
    Thank you for your tutorial. I am following your tutorial. But I am using mssql 2012 DB. Could you please tell me the changes i should made for MS SQL 2012 compability?

    1. Evan says:

      You can try to use the sql directly, because these sqls are very common and most possibly MS SQL 2012 use the same.

      1. Pankaj says:

        Thanks Evan, yes these should work just fine.

  24. Hi-

    Thanks a lot for this post. Clear, loud and helpful. Thank you again ๐Ÿ™‚ I am new to Spring

    1. Pankaj says:

      you are welcome my friend.

  25. Tony says:

    How can integrate it in a Spring Web Application? For example in my class called “Home Controller”:
    */
    @RequestMapping(value = “/”, method = RequestMethod.GET)
    public String home(Locale locale, Model model) {
    //Get the Spring Context
    ClassPathXmlApplicationContext ctx = new ClassPathXmlApplicationContext(“spring.xml”);

    //Get the EmployeeDAO Bean
    //EmployeeDAO employeeDAO = ctx.getBean(“employeeDAO”, EmployeeDAO.class);
    //To use JdbcTemplate
    EmployeeDAO employeeDAO = ctx.getBean(“employeeDAOJDBCTemplate”, EmployeeDAO.class);

    //Run some tests for JDBC CRUD operations
    Employee emp = new Employee();
    int rand = new Random().nextInt(1000);
    emp.setId(rand);
    emp.setName(“Pankaj”);
    emp.setRole(“Java Developer”);

    //Create
    employeeDAO.save(emp);
    ctx.close();

    return “home”;
    }

    This code return the follow error in Tomcat Server: Exception in thread “main” java.lang.NoClassDefFoundError: Could not initialize class org.springframework.jdbc.core.StatementCreatorUtils
    at org.springframework.jdbc.core.ArgumentPreparedStatementSetter.cleanupParameters(ArgumentPreparedStatementSetter.java:72)
    at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:924)
    at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:909)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:644)
    at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:909)
    at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:970)
    at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:980)
    at com.journaldev.spring.jdbc.dao.EmployeeDAOJDBCTemplateImpl.save(EmployeeDAOJDBCTemplateImpl.java:32)

    1. TJ says:

      Tony,

      Did you find the solution? I have the same error

      1. Francis says:

        I resolved the errors that Tony, TJ and myself saw when hitting the insert statements with parameters at runtime. Setting the same version as Pankaj did for both the framework and the jdbc worked for me, and here is what led me to that conclusion. I had been using some different versions since I was working on a different problem and reference material.

        That is, the following basic error was thrown:

        java.lang.NoClassDefFoundError: Could not initialize class org.springframework.jdbc.core.StatementCreatorUtils

        I saw that StatementCreatorUtils was an abstract class, with a static method and call, and since there is no “new” object of this class and no constructor, guessed there was just a release compatibility issue between the springframework-version and the springframework.jdbc-version so I tried a few Jar version combinations. When I just set the same versions as Pankaj had, apparently they were compatible and it worked. Also, I am working with MySql connector v. 5.1.30 (and MySql server itself is 5.6.17) as in below dependency:

        4.0.2.RELEASE

        4.0.2.RELEASE
        …..

        mysql
        mysql-connector-java
        5.1.30
        compile

        1. Murilo says:

          Hey there!

          BIG thanks from Brazil! ;D

          Cheers!

          1. praveen says:

            good example

        2. Atif says:

          Thanks.That solved the problem for me

        3. Pankaj says:

          Thanks Francis for pitching in and helping others. ๐Ÿ™‚

    2. Evan says:

      Try to add spring’s @Transactional annotation to DAO impl class.

    3. Francis says:

      Yes, any clues to Tony’s question, Pankaj?? I am getting the same stack trace as Tony. Attempting to call a static method on the abstract StatementCreatorUtils class that is in the same library/jar.

      I get the same whether I run in Eclipse/STS or maven spring-boot:run and it is clear the spring-jdbc dependency is defined otherwise the class doing the referencing would not be called since it is in the same jar.

      package org.springframework.jdbc.core;
      public abstract class StatementCreatorUtils {

      public static void cleanupParameters(Object… paramValues) {
      if (paramValues != null) {
      cleanupParameters(Arrays.asList(paramValues));
      }
      }

      Thanks.

      1. Pankaj says:

        Thanks for responding with fix, sorry for late response.

  26. Kofi says:

    Friend thanks a lot for sharing, I have not taken the time to try because although it looks like a very good tutorial, the starting point in STS is always missing. Not enough to show the structure, STS has too many projects and it is hard to figure out which one to use for your tutorial. You could say for example “New-Spring-SpringMVC-create project.”.

    1. Pankaj says:

      You can easily figure it out. Its a standalone aplication, so obviously “Simple Spring Project”

  27. Tricia says:

    Where do you put the Employee.sql? How do you create the db for that?

    1. Pankaj says:

      You need to create the database and run the acript for table generation. That part is out of acipe of this tutorial.

      1. Zac C says:

        I don’t understand your reply? Are you saying we are to figure this out on our own? We need to have that database table talk to our spring project?

  28. nithesh says:

    Cannot make a static reference to the non-static method save(Employee) from the type
    EmployeeInterface…..is the error which i m geting. coukld u plz help me to resolve this error???

    1. Pankaj says:

      the error is clearly saying that you are trying to invoke non-static method from static method. Check your method signatures for save() and the method calling it.

  29. akg says:

    Great work on all your tutorials !

    I am mainly a c++ guy trying to learn spring

    However on this one I am getting several build errors in STS of type –

    Multiple markers at this line
    – The method save(Employee) of type EmployeeDAOJDBCTemplateImpl must override a
    superclass method
    – implements com.journaldev.spring.jdbc.dao.EmployeeDAO.save

    thanks a lot

    1. akg says:

      Found solution – it is java compiler setting in STS for the project required is >= 1.6 to override interface through annotation.

    2. Manish says:

      Same here .. I am also C++ guy but learning now Spring .. but it is quite easy to switch from C++ to Java.

Leave a Reply

Your email address will not be published. Required fields are marked *

close
Generic selectors
Exact matches only
Search in title
Search in content
Search in posts
Search in pages