In this tutorial, we will learn how to create a Spring boot application that connects to an H2 database using MyBatis. You’ll build an application using MyBatis to access data stored in an in-memory H2 database.
Note: We configure the H2 database with Spring boot to create and use an in-memory database in runtime, generally for unit testing or POC purposes. Remember an in-memory database is created/initialized when an application starts up; and destroyed when the application shuts down.
Note: We configure the H2 database with Spring boot to create and use an in-memory database in runtime, generally for unit testing or POC purposes. Remember an in-memory database is created/initialized when an application starts up; and destroyed when the application shuts down.
MyBatis removes the need for manually writing code to set parameters and retrieve results. It provides simple XML or Annotation-based configuration to map Java POJOs to a database. In this example, we will use MyBatis annotations for configuration to map Java POJOs to a database.
In this tutorial, we will learn -
- How to connect a Spring Boot project to the database using myBatis?
- How to write a simple repository class with all the CRUD methods to execute queries using myBatis?
- How to execute basic queries using myBatis?
- How to create a project using Spring Boot, myBatis and H2?
1. Spring boot 2+
2. MyBatis
3. Maven 3+
4. JDK 1.8
5. IDE - Eclipse or STS
6. H2 database
Development Steps
- Create a Spring Boot Application
- Maven dependencies
- Database Setup
- Database and Logging Configuration
- Creating Student Bean
- Create Employee JDBC Repository
- Run Application
1. Create a Spring Boot Application
There are many ways to create a Spring Boot application. You can refer below articles to create a Spring Boot application.
>> Create Spring Boot Project With Spring Initializer
>> Create Spring Boot Project in Spring Tool Suite [STS]
>> Create Spring Boot Project in Spring Tool Suite [STS]
2. Maven dependencies
<?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-mybatis-crud-example</artifactId> <version>0.0.1-SNAPSHOT</version> <packaging>jar</packaging> <name>springboot2-mybatis-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.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>1.2.1</version> </dependency> <dependency> <groupId>com.h2database</groupId> <artifactId>h2</artifactId> <scope>runtime</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.
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. Database and Logging Configuration
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
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 MyBatis Repository
We would want to start with creating a simple repository. @Mapper indicates that this is a MyBatis mapper class.
package net.guides.springboot2.jdbc.repository;
import java.util.List;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import net.guides.springboot2.jdbc.model.Employee;
@Mapper
public interface EmployeeMyBatisRepository {
@Select("select * from employees")
public List < Employee > findAll();
@Select("SELECT * FROM employees WHERE id = #{id}")
public Employee findById(long id);
@Delete("DELETE FROM employees WHERE id = #{id}")
public int deleteById(long id);
@Insert("INSERT INTO employees(id, first_name, last_name,email_address) " +
" VALUES (#{id}, #{firstName}, #{lastName}, #{emailId})")
public int insert(Employee employee);
@Update("Update employees set first_name=#{firstName}, " +
" last_name=#{lastName}, email_address=#{emailId} where id=#{id}")
public int update(Employee employee);
}
Let's understand the above code. The findById method to retrieve an employee by id:
@Select("SELECT * FROM employees WHERE id = #{id}")
public Employee findById(long id);
@Select("SELECT * FROM employees WHERE id = #{id}") - @Select highlights that this is a select query. #{id} is used to indicate that id is a parameter to this query.
You can observe that are defining a simple interface method without the implementation.
@Select("select * from employees")
public List<Employee> findAll();
@Select("select * from student") - Since we want to return a list of students, we do not need to pass any parameters. It's a simple @Select query.
7. Run Application
To keep things simple we will make the Application class implement CommandLineRunner and implement 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.EmployeeMyBatisRepository;
@SpringBootApplication
public class Application implements CommandLineRunner {
private Logger logger = LoggerFactory.getLogger(this.getClass());
@Autowired
private EmployeeMyBatisRepository employeeRepository;
@Override
public void run(String...args) throws Exception {
logger.info("Inserting -> {}", employeeRepository.insert(new Employee(10011 L, "Ramesh", "Fadatare", "ramesh@gmail.com")));
logger.info("Inserting -> {}", employeeRepository.insert(new Employee(10012 L, "John", "Cena", "john@gmail.com")));
logger.info("Inserting -> {}", employeeRepository.insert(new Employee(10013 L, "tony", "stark", "stark@gmail.com")));
logger.info("Employee id 10011 -> {}", employeeRepository.findById(10011 L));
logger.info("Update 10003 -> {}", employeeRepository.update(new Employee(10011 L, "ram", "Stark", "ramesh123@gmail.com")));
employeeRepository.deleteById(10013 L);
logger.info("All users -> {}", employeeRepository.findAll());
}
public static void main(String[] args) {
SpringApplication.run(Application.class, args);
}
}
Comments
Post a Comment
Leave Comment