Spring Boot + Spring JDBC + MySQL CRUD Example

In this tutorial, we will learn how to create a Spring boot application that connects to a database using Spring JDBC. We will build an application using Spring’s JdbcTemplate to access data stored in a relational database.
In this tutorial, we will learn -
  • How to use Spring Boot Started JDBC?
  • How to connect a Spring Boot project to a database using Spring JDBC?
  • How to write a simple repository class with all the CRUD methods?
  • How to execute basic queries using Spring JDBC?
  • How to create a project using Spring Boot, Spring JDBC, and MySQL?
  • How to use a spring-boot-starter-jdbc starter for using JDBC with the HikariCP connection pool.

Tools and Technologies used

1. Spring boot 2+
2. Spring JDBC
3. Maven 3+
4. JDK 1.8
5. IDE - Eclipse or STS
6. MySQL connector and database

Development Steps

  1. Create a Spring Boot Application
  2. Maven dependencies
  3. Database Setup
  4. MySQL Database configuration
  5. Creating Employee Bean
  6. Create Employee JDBC Repository
  7. Run Application

1. Create a Spring Boot Application

There are many ways to create a Spring Boot application. You can refer to the below articles to create a Spring Boot application.

2. Maven Dependencies

Here is the complete pom.xml file for your reference:
<?xml version="1.0" encoding="UTF-8"?>
 xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <description>Demo project for Spring Boot</description>
        <relativePath />
        <!-- lookup parent from repository -->

3. Database Setup

We will create a table called employees with a few simple columns. We can initialize a schema by creating a schema.sql file in the resources.
Let's create the schema.sql file under /resources folder and add the following content to it:
create table employees
   id integer not null,
   first_name varchar(255) not null, 
   last_name varchar(255) not null,
   email_address varchar(255) not null,
   primary key(id)

4. MySQL Database and Logging Configuration

First, let's create a database named demo in MySQL server.

Since we’re using MySQL as our database, we need to configure the database URLusername, and password so that Spring can establish a connection with the database on startup.

Open src/main/resources/application.properties file and add the following properties to it:
## Spring DATASOURCE (DataSourceAutoConfiguration & DataSourceProperties)
spring.datasource.url = jdbc:mysql://localhost:3306/demo?useSSL=false
spring.datasource.username = root
spring.datasource.password = root

## Hibernate Properties
#The SQL dialect makes Hibernate generate better SQL for the chosen database
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5InnoDBDialect

# Hibernate ddl auto (create, create-drop, validate, update)
spring.jpa.hibernate.ddl-auto = update

# Show all queries
Don’t forget to change the spring.datasource.username and spring.datasource.password as per your MySQL installation. 

5. Creating Employee Bean

Let's create a simple Employee bean.
package net.guides.springboot2.jdbc.model;

public class Employee {

    private long id;
    private String firstName;
    private String lastName;
    private String emailId;

    public Employee() {


    public Employee(long id, String firstName, String lastName, String emailId) {
        this.id = id;
        this.firstName = firstName;
        this.lastName = lastName;
        this.emailId = emailId;

    public long getId() {
        return id;
    public void setId(long id) {
        this.id = id;

    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 String getEmailId() {
        return emailId;
    public void setEmailId(String emailId) {
        this.emailId = emailId;

6. Create Employee JDBC Repository

We would want to start with creating a simple repository. To talk to the database we will use a JdbcTemplate.
package net.guides.springboot2.jdbc.repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Optional;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import net.guides.springboot2.jdbc.model.Employee;

public class EmployeeJDBCRepository {
    JdbcTemplate jdbcTemplate;

    class EmployeeRowMapper implements RowMapper < Employee > {
        public Employee mapRow(ResultSet rs, int rowNum) throws SQLException {
            Employee employee = new Employee();
            return employee;

    public List < Employee > findAll() {
        return jdbcTemplate.query("select * from employees", new EmployeeRowMapper());

    public Optional < Employee > findById(long id) {
        return Optional.of(jdbcTemplate.queryForObject("select * from employees where id=?", new Object[] {
            new BeanPropertyRowMapper < Employee > (Employee.class)));

    public int deleteById(long id) {
        return jdbcTemplate.update("delete from employees where id=?", new Object[] {

    public int insert(Employee employee) {
        return jdbcTemplate.update("insert into employees (id, first_name, last_name, email_address) " + "values(?, ?, ?, ?)",
            new Object[] {
                employee.getId(), employee.getFirstName(), employee.getLastName(), employee.getEmailId()

    public int update(Employee employee) {
        return jdbcTemplate.update("update employees " + " set first_name = ?, last_name = ?, email_address = ? " + " where id = ?",
            new Object[] {
                employee.getFirstName(), employee.getLastName(), employee.getEmailId(), employee.getId()

7. Run Application

To keep things simple we will make the Application class implement CommandLineRunner and implement a run method to test JDBC methods.
package net.guides.springboot2.jdbc;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

import net.guides.springboot2.jdbc.model.Employee;
import net.guides.springboot2.jdbc.repository.EmployeeJDBCRepository;

public class Application implements CommandLineRunner {

    private Logger logger = LoggerFactory.getLogger(this.getClass());
    private EmployeeJDBCRepository employeeRepository;
    public void run(String... args) throws Exception {

        logger.info("Inserting -> {}", employeeRepository.insert(new Employee(10011L, "Ramesh", "Fadatare", "[email protected]")));
        logger.info("Inserting -> {}", employeeRepository.insert(new Employee(10012L, "John", "Cena", "[email protected]")));
        logger.info("Inserting -> {}", employeeRepository.insert(new Employee(10013L, "tony", "stark", "[email protected]")));
        logger.info("Employee id 10011 -> {}", employeeRepository.findById(10011L));

        logger.info("Update 10003 -> {}", employeeRepository.update(new Employee(10011L, "ram", "Stark", "[email protected]")));


        logger.info("All users -> {}", employeeRepository.findAll());
    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);



In this tutorial, we have learned how to create a Spring boot application that connects to a database using Spring JDBC. We will build an application using Spring’s JdbcTemplate to access data stored in a relational database.

Get the source code of this tutorial on my GitHub Repository.


Post a Comment

Leave Comment