Spring Boot + Spring JDBC + MySQL CRUD Example

Author:
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"?>
<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>net.guides.springboot2</groupId>
    <artifactId>springboot2-jdbc-crud-example</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>jar</packaging>
    <name>springboot2-jpa-crud-example</name>
    <description>Demo project for Spring Boot</description>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.0.5.RELEASE</version>
        <relativePath />
        <!-- lookup parent from repository -->
    </parent>
    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
        </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>
</project>

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 URL, username, 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

logging.level.org.hibernate.stat=debug
# Show all queries
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
logging.level.org.hibernate.type=trace
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;

@Repository
public class EmployeeJDBCRepository {
    @Autowired
    JdbcTemplate jdbcTemplate;

    class EmployeeRowMapper implements RowMapper < Employee > {
        @Override
        public Employee mapRow(ResultSet rs, int rowNum) throws SQLException {
            Employee employee = new Employee();
            employee.setId(rs.getLong("id"));
            employee.setFirstName(rs.getString("first_name"));
            employee.setLastName(rs.getString("last_name"));
            employee.setEmailId(rs.getString("email_address"));
            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[] {
                id
            },
            new BeanPropertyRowMapper < Employee > (Employee.class)));
    }

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

    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;

@SpringBootApplication
public class Application implements CommandLineRunner {

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

        logger.info("Inserting -> {}", employeeRepository.insert(new Employee(10011L, "Ramesh", "Fadatare", "ramesh@gmail.com")));
        logger.info("Inserting -> {}", employeeRepository.insert(new Employee(10012L, "John", "Cena", "john@gmail.com")));
        logger.info("Inserting -> {}", employeeRepository.insert(new Employee(10013L, "tony", "stark", "stark@gmail.com")));
  
        logger.info("Employee id 10011 -> {}", employeeRepository.findById(10011L));

        logger.info("Update 10003 -> {}", employeeRepository.update(new Employee(10011L, "ram", "Stark", "ramesh123@gmail.com")));

        employeeRepository.deleteById(10013L);

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

Output

Conclusion

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.

Related Spring and Spring Boot Tutorials/Guides:

Spring Boot Tutorials [500+] Spring Boot Testing Tutorial Spring Boot Microservice Tutorial Spring Boot Kafka Microservices Spring Boot + Apache Kafka Tutorial Spring Core Tutorial Spring MVC Tutorial Spring Data JPA Tutorial Spring Framework for Beginners Spring AOP Tutorial Spring Security Tutorial Spring Exceptions Tutorial Spring Boot Interview Questions Spring Boot Microservices Interview Questions Apache Kafka Tutorials Docker Tutorials and Guides Spring Boot RabbitMQ Tutorials Angular CRUD Example with Spring Boot Spring Boot + Angular 12 CRUD Full Stack Spring Boot + Angular 8 CRUD Full Stack Spring Boot + Angular 10 CRUD Full Stack Spring Boot + React JS CRUD Full Stack React JS ( React Hooks) + Spring Boot Spring Boot Thymeleaf CRUD Full Stack Spring Boot User Registration and Login Node Js + Express + MongoDB CRUD Vue JS + Spring Boot REST API Tutorial

Comments

Post a Comment

Leave Comment