How To Learn !!!: Spring 4 JdbcTemplate Annotation with Example

In this tutorial, we will learn how to connect to the database and execute CRUD SQL queries using Spring 4 JdbcTemplate. Java base configuration is used to load the JdbcTemplate, here we are not using XML base configuration for JdbcTemplate.

Spring 4 jdbctemplate annotation example eclipse project setup

Tools and Technologies

Apache Maven 3.0.4
JDK 1.8
Spring core, Spring webmvc and Spring context (4.1.4.RELEASE)
mysql(5.1.31)

Database table

Use below SQL script to create "trn_person" table in the database.

CREATE TABLE  `technicalkeeda`.`trn_person` (
  `person_id` int(10) unsigned NOT NULL auto_increment,
  `first_name` varchar(45) collate latin1_general_ci NOT NULL,
  `last_name` varchar(45) collate latin1_general_ci NOT NULL,
  `age` int(10) unsigned NOT NULL,
  PRIMARY KEY  USING BTREE (`person_id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

pom.xml

As we are using Maven project. Let's define the spring 4 specific maven dependencies.

<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>com.technicalkeeda</groupId>
    <artifactId>Spring4Examples</artifactId>
    <packaging>jar</packaging>
    <version>1.0</version>
    <name>Spring4Examples</name>
    <description></description>
    <build>
        <plugins>
            <plugin>
                <artifactId>maven-compiler-plugin</artifactId>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                </configuration>
            </plugin>
        </plugins>
    </build>
 
    <properties>
        <spring.version>4.1.4.RELEASE</spring.version>
    </properties>
 
    <dependencies>
 
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-core</artifactId>
            <version>${spring.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>${spring.version}</version>
        </dependency>
 
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context-support</artifactId>
            <version>${spring.version}</version>
        </dependency>
 
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>${spring.version}</version>
        </dependency>
 
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.31</version>
        </dependency>
 
 
    </dependencies>
</project>

Person Pojo

This is simple Person pojo class which contains different attributes like personId, firstName, lastName and age.

package com.technicalkeeda.bean;
 
public class Person {
 
    private int personId;
    private String firstName;
    private String lastName;
    private int age;
 
    public Person() {
 
    }
 
    public Person(int personId, String firstName, String lastName, int age) {
        super();
        this.personId = personId;
        this.firstName = firstName;
        this.lastName = lastName;
        this.age = age;
    }
 
    public int getPersonId() {
        return personId;
    }
 
    public void setPersonId(int personId) {
        this.personId = personId;
    }
 
    public String getFirstName() {
        return firstName;
    }
 
    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }
 
    public String getLastName() {
        return lastName;
    }
 
    public void setLastName(String lastName) {
        this.lastName = lastName;
    }
 
    public int getAge() {
        return age;
    }
 
    public void setAge(int age) {
        this.age = age;
    }
 
    @Override
    public String toString() {
        StringBuilder builder = new StringBuilder();
        builder.append("Person Id:- " + getPersonId() + " First Name:- " + getFirstName() + " Last Name:- " +
            getLastName() + " Age:- " + getAge());
        return builder.toString();
    }
 
}

application.properties

Create "application.properties" file under /resources folder. Define data source configuration properties like jdbc driverClassName, url, username and password.

jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/technicalkeeda
jdbc.username=root
jdbc.password=

Spring 4 Application Configuration

@Configuration annotation imports the Spring configuration. @Configuration objects are managed as Spring beans within the container, imported configurations are used to injected using @Autowired or @Inject.

@ComponentScan is equivalent to <context:component-scan base-package="..." used to lookup the beans and components classes in the spring context.

@PropertySource Annotation use to provide a convenient and declarative mechanism for adding a PropertySource to Spring's Environment.

To declare a bean, simply annotate a method with the @Bean annotation. When JavaConfig encounters such a method, it will execute that method and register the return value as a bean within a BeanFactory. Here we have registered JdbcTemplate and DataSource beans.

package com.technicalkeeda.configuration;
 
import javax.sql.DataSource;
 
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;
import org.springframework.core.env.Environment;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
 
@Configuration
@ComponentScan(basePackages = "com.technicalkeeda")
@PropertySource(value = { "classpath:application.properties" })
public class ApplicationConfig {
 
    @Autowired
    private Environment env;
 
    @Bean
    public DataSource dataSource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName(env.getRequiredProperty("jdbc.driverClassName"));
        dataSource.setUrl(env.getRequiredProperty("jdbc.url"));
        dataSource.setUsername(env.getRequiredProperty("jdbc.username"));
        dataSource.setPassword(env.getRequiredProperty("jdbc.password"));
        return dataSource;
    }
 
    @Bean
    public JdbcTemplate jdbcTemplate(DataSource dataSource) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        jdbcTemplate.setResultsMapCaseInsensitive(true);
        return jdbcTemplate;
    }
 
}

PersonService Interface

Creates PersonService interface along with CRUD methods.

Create new Person entity.
Edit Person entity.
Delete Person entity.
Returns Person entity identified by the given id.
Returns all Person entities

package com.technicalkeeda.services;
 
import java.util.List;
 
import com.technicalkeeda.bean.Person;
 
public interface PersonService {
 
    public void addPerson(Person person);
 
    public void editPerson(Person person, int personId);
 
    public void deletePerson(int personId);
 
    public Person find(int personId);
 
    public List < Person > findAll();
}

PersonService Implementation

Mark PersonServiceImpl class as "personService" using @Service annotation. Use @Autowired annotation to autowire PersonDao bean.

package com.technicalkeeda.services;
 
import java.util.List;
 
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
 
import com.technicalkeeda.bean.Person;
import com.technicalkeeda.dao.PersonDao;
 
@Service("personService")
public class PersonServiceImpl implements PersonService {
 
    @Autowired
    PersonDao personDao;
 
    public void addPerson(Person person) {
        personDao.addPerson(person);
 
    }
 
    public void editPerson(Person person, int personId) {
        personDao.editPerson(person, personId);
    }
 
    public void deletePerson(int personId) {
        personDao.deletePerson(personId);
    }
 
    public Person find(int personId) {
        return personDao.find(personId);
    }
 
    public List < Person > findAll() {
        return personDao.findAll();
    }
}

PersonDao Interface

Creates PersonDao interface.

package com.technicalkeeda.dao;
 
import java.util.List;
 
import com.technicalkeeda.bean.Person;
 
public interface PersonDao {
 
    public void addPerson(Person person);
 
    public void editPerson(Person person, int personId);
 
    public void deletePerson(int personId);
 
    public Person find(int personId);
 
    public List < Person > findAll();
}

PersonDao implementation

PersonDaoImpl marked with @Repository annotation, It allows the component scanning to find and configure the respected DAO. @Autowired JdbcTemplate to access to a persistence resource.

package com.technicalkeeda.dao;
 
import java.util.List;
 
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
 
import com.technicalkeeda.bean.Person;
 
@Repository
@Qualifier("personDao")
public class PersonDaoImpl implements PersonDao {
 
    @Autowired
    JdbcTemplate jdbcTemplate;
 
    public void addPerson(Person person) {
        jdbcTemplate.update("INSERT INTO trn_person (person_id, first_name, Last_name, age) VALUES (?, ?, ?, ?)",
            person.getPersonId(), person.getFirstName(), person.getLastName(), person.getAge());
        System.out.println("Person Added!!");
    }
 
    public void editPerson(Person person, int personId) {
        jdbcTemplate.update("UPDATE trn_person SET first_name = ? , last_name = ? , age = ? WHERE person_id = ? ",
            person.getFirstName(), person.getLastName(), person.getAge(), personId);
        System.out.println("Person Updated!!");
    }
 
    public void deletePerson(int personId) {
        jdbcTemplate.update("DELETE from trn_person WHERE person_id = ? ", personId);
        System.out.println("Person Deleted!!");
    }
 
    public Person find(int personId) {
        Person person = (Person) jdbcTemplate.queryForObject("SELECT * FROM trn_person where person_id = ? ",
            new Object[] { personId }, new BeanPropertyRowMapper(Person.class));
 
        return person;
    }
 
    public List < Person > findAll() {
        List < Person > persons = jdbcTemplate.query("SELECT * FROM trn_person", new BeanPropertyRowMapper(Person.class));
        return persons;
    }
}

App.java

To create standalone Spring Application Context, We are using AnnotationConfigApplicationContext which helps to register all the beans generated by the configuration class (@Configuration) at Spring runtime.

Use AbstractApplicationContext.getBean(String name) method is used to get bean object( "personService") from Spring application context.

Once you get the "personService" instance perform different CRUD operations.

package com.technicalkeeda.test;
 
import java.util.List;
 
import org.springframework.context.annotation.AnnotationConfigApplicationContext;
import org.springframework.context.support.AbstractApplicationContext;
 
import com.technicalkeeda.bean.Person;
import com.technicalkeeda.configuration.ApplicationConfig;
import com.technicalkeeda.services.PersonService;
 
public class App {
 
    public static void main(String args[]) {
 
        AbstractApplicationContext context = new AnnotationConfigApplicationContext(ApplicationConfig.class);
        PersonService personService = (PersonService) context.getBean("personService");
 
        Person yashwant = new Person(1, "Yashwant", "Chavan", 32);
        Person mahesh = new Person(2, "Mahesh", "Patil", 25);
        Person vishal = new Person(3, "Vishal", "Naik", 40);
 
        personService.addPerson(yashwant);
        personService.addPerson(mahesh);
        personService.addPerson(vishal);
 
        System.out.println("Find All");
        List < Person > persons = personService.findAll();
        for (Person person: persons) {
            System.out.println(person);
        }
 
        System.out.println("Delete person Id = 3");
        int deleteMe = 3;
        personService.deletePerson(deleteMe);
 
        yashwant.setFirstName("Yashwant - Updated");
        yashwant.setLastName("Chavan - Updated");
        yashwant.setAge(40);
 
        System.out.println("Update person Id = 1");
        int updateMe = 1;
        personService.editPerson(yashwant, updateMe);
 
        System.out.println("Find person Id = 2");
        Person person = personService.find(2);
        System.out.println(person);
 
        System.out.println("Find All Again");
        persons = personService.findAll();
        for (Person p: persons) {
            System.out.println(p);
        }
 
        context.close();
    }
 
}

Output

Dec 12, 2016 8:06:19 PM org.springframework.context.annotation.AnnotationConfigApplicationContext prepareRefresh
INFO: Refreshing org.springframework.context.annotation.AnnotationConfigApplicationContext@3d646c37: startup date [Mon Dec 12 20:06:19 IST 2016]; root of context hierarchy
Dec 12, 2016 8:06:19 PM org.springframework.jdbc.datasource.DriverManagerDataSource setDriverClassName
INFO: Loaded JDBC driver: com.mysql.jdbc.Driver
 
Person Added!!
Person Added!!
Person Added!!
 
Find All
Person Id:- 1 First Name:- Yashwant Last Name:- Chavan Age:- 32
Person Id:- 2 First Name:- Mahesh Last Name:- Patil Age:- 25
Person Id:- 3 First Name:- Vishal Last Name:- Naik Age:- 40
 
Delete person Id = 3
Person Deleted!!
 
Update person Id = 1
Person Updated!!
 
Find person Id = 2
Person Id:- 2 First Name:- Mahesh Last Name:- Patil Age:- 25
 
Find All Again
Person Id:- 1 First Name:- Yashwant - Updated Last Name:- Chavan - Updated Age:- 40
Person Id:- 2 First Name:- Mahesh Last Name:- Patil Age:- 25
 
Dec 12, 2016 8:06:20 PM org.springframework.context.annotation.AnnotationConfigApplicationContext doClose
INFO: Closing org.springframework.context.annotation.AnnotationConfigApplicationContext@3d646c37: startup date [Mon Dec 12 20:06:19 IST 2016]; root of context hierarchy

How To Learn !!!

Search This Blog

Thursday, July 23, 2020

Spring 4 JdbcTemplate Annotation with Example