Spring Boot + Spring JDBC + MySQL CRUD Example

🎓 Top 15 Udemy Courses (80-90% Discount): My Udemy Courses - Ramesh Fadatare — All my Udemy courses are real-time and project oriented courses.

▶️ Subscribe to My YouTube Channel (178K+ subscribers): Java Guides on YouTube

▶️ For AI, ChatGPT, Web, Tech, and Generative AI, subscribe to another channel: Ramesh Fadatare on YouTube

← Back to Spring Boot Tutorial

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.

>> Create Spring Boot Project With Spring Initializer
>> Create Spring Boot Project in Spring Tool Suite [STS]

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.

← Back to Spring Boot Tutorial