Insert Record in DB with SpringBoot NamedParameter JdbcTemplate

Hello guys, my last post was about inserting record in DB using jdbctemplate provided by Spring.In this post you will learn how to insert record in database with spring boot namedparameterjdbctemplate.We will be using the artifact spring-boot-starter-jdbc provided by spring boot to configure our spring jdbc configurations.Using this artifact,we can avoid creating specific beans for datasource and namdparameterjdbctemplate.Let us start coding to insert record in database with spring boot namdparameterjdbctemplate.

Maven Dependencies

spring-boot-starter-parent: provides useful Maven defaults. It also provides a dependency-management section so that you can omit version tags for existing dependencies.

spring-boot-starter-jdbc: provides all the maven dependecies for using JDBC with the Tomcat JDBC connection pool.

    <properties>
        <java.version>1.8</java.version>
    </properties>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.3.3.RELEASE</version>
    </parent>
	
    <dependencies>
	<dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
	<dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
	</dependency>
		
	<dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
	    <scope>test</scope>
        </dependency>
    </dependencies>
	
	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>

Spring Bean Configuration

package com.devglan.config;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;

@Configuration
@ComponentScan(basePackages = "com.devglan")
@SpringBootApplication
public class Application {
	

    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }

}

Following is the model class with getters and setters.

package com.devglan.model;

public class User {

    private int id;
    private String name;
    private String address;
    private String email;
	
    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 getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

}

 Other Interesting Posts
Spring 5 Features and Enhancements
Insert Record in DB with Spring Boot JdbcTemplate
Fetch Auto Generated Primary Key Value After Insert Spring Jdbc
Working with Spring Boot Jdbctemplate
Working with Spring Boot NamedParameter Jdbctemplate
Execute Stored Procedure in Spring Jdbc
Spring Security Hibernate Example with complete JavaConfig
Securing REST API with Spring Security Basic Authentication
Websocket spring Boot Integration without STOMP with complete JavaConfig
Spring Boot Spring MVC Example
Spring Boot Thymeleaf Example
Spring Boot MVC with Jsp Example

Now let us define our properties class which will be considered by spring-boot automatically to create the bean for datasource at run time. application.properties

spring.datasource.url=jdbc:mysql://localhost/devglan
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

Now let us define our Dao class which will have implementation of namdparameterjdbctemplate to insert rows in the db.We are using KeyHolder to hold the auto generated primary key.

package com.devglan.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import com.devglan.model.User;

public class UserDaoImpl {
	
	private final String INSERT_SQL = "INSERT INTO USERS(name, address, email) values(:name,:address,:email)";

	@Autowired
	private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

	public User create(final User user) {
		KeyHolder holder = new GeneratedKeyHolder();
			SqlParameterSource parameters = new MapSqlParameterSource()
					.addValue("name", user.getName())
					.addValue("address", user.getAddress())
					.addValue("email", user.getEmail());
			namedParameterJdbcTemplate.update(INSERT_SQL, parameters, holder);
			user.setId(holder.getKey().intValue());
			return user;
		}

}

Now, it is time to define our test cases to test whether the insert record in database with spring boot namdparameterjdbctemplate.If you need to learn more on spring Boot Junit integration, then visit my earlier post Spring Boot Junit Integration Example

package com.devglan.dao.test;

import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertNotNull;
import static org.junit.Assert.assertTrue;

import java.util.List;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.SpringApplicationConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import com.devglan.config.Application;
import com.devglan.dao.UserDao;
import com.devglan.model.User;


@RunWith(SpringJUnit4ClassRunner.class)
@SpringApplicationConfiguration (Application.class)
public class UserDaoTest {

	@Autowired 
	private UserDao userDao;

	@Test 
	public void createUser() {
		User savedUser = userDao.create(getUser());
		User userFromDb = userDao.findUserById(savedUser.getId());
		assertEquals(savedUser.getName(), userFromDb.getName());
		assertEquals(savedUser.getEmail(), userFromDb.getEmail());
	}

        private User getUser() {
		User user = new User();
		user.setAddress("Bangalore, Karnataka");
		user.setEmail("johndoe@gmail.com");
		user.setName("John Doe");
		return user;
	}

}

SQL

CREATE TABLE users (record_id bigint NOT NULL AUTO_INCREMENT, name varchar(100), address varchar(250), email varchar(100), PRIMARY KEY (record_id));

Run Application

Run the test case defined as Junit test case and check the result.You can also verify that a single user row is created in db.

Conclusion

I hope this article served you that you were looking for. If you have anything that you want to add or share then please share it below in the comment section.