Basic CRUD comparison between Mybatis and Hibernate

Introduction

I wanted to compare the implementation of Mybatis and Hibernate (only basic CRUD processing), so I tried it a little. I'm using Spring boot for the base FW.

environment

I'm adding the required dependencies in the Spring starter project. It's really easy because you just check it with the STS GUI.

DB I used Postgres. Below, from DB creation to test data INSERT.

command prompt


postgres=# create database test01;← test01 Create database
postgres=# \l
Database list
name|owner|encoding|Collation| Ctype(Conversion operator)  |Access right
-------------+----------+------------------+--------------------+--------------------+-----------------------
 postgres    | postgres | UTF8             | Japanese_Japan.932 | Japanese_Japan.932 |
 test01      | postgres | UTF8             | Japanese_Japan.932 | Japanese_Japan.932 |
(Omitted below)
postgres=# \c test01 ← Select test01 database
Database"test01"To the user"postgres"Connected as.
test01=# create table emp (id integer,department character varying(10), name character varying(30));
CREATE TABLE
test01=# \d emp
table"public.emp"
Column|Mold|Modifier
------------+-----------------------+--------
 id         | integer               |
 department | character varying(10) |
 name       | character varying(30) |
test01=# insert into emp values(101,'Human Resources Department','Nico');
INSERT 0 1

(~ Omitted ~)

test01=# select * from emp;
 id  | department |    name
-----+------------+------------
 101 |Human Resources Department|Nico
 102 |Development department|Dante
 103 |Development department|Nero
 104 |General Affairs Department|Trish
 105 |Development department|Vergil
(5 lines)
test01=# ALTER TABLE emp ADD PRIMARY KEY(id);
ALTER TABLE
test01=# 

[Implementation] For Mybatis

POM Only dependencies are excerpted.

pom.xml


	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-batch</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>

		<dependency>
			<groupId>org.postgresql</groupId>
			<artifactId>postgresql</artifactId>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<optional>true</optional>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
		<dependency>
			<groupId>org.springframework.batch</groupId>
			<artifactId>spring-batch-test</artifactId>
			<scope>test</scope>
		</dependency>
	</dependencies>

Property

In the case of Mybatis, it was only properties related to connection information.

application.properties


spring.datasource.driver-class-name=org.postgresql.Driver
spring.datasource.url=jdbc:postgresql://localhost:5432/test01
spring.datasource.username=postgres
spring.datasource.password=postgres
#Output SQL log
logging.level.jp.demo.mapper.EmpMapper=DEBUG

SpringBootApplication

MybatisApplication.java


package jp.demo;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.ConfigurableApplicationContext;

@SpringBootApplication
public class MybatisApplication {

	public static void main(String[] args) {
		try(ConfigurableApplicationContext ctx = SpringApplication.run(MybatisApplication.class, args)){
			DemoService app = ctx.getBean(DemoService.class);
            app.execute(args);
		} catch (Exception e) {
            e.printStackTrace();
        }
	}
}

Service

DemoService.java


package jp.demo;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import jp.demo.domain.Emp;
import jp.demo.mapper.EmpMapper;

@Service
public class DemoService {
	@Autowired
	private EmpMapper mapper;

	public void execute(String[] args) {
		// select all
		System.out.println("### select all ###");
		List<Emp> list = mapper.selectAll();
		list.forEach(System.out::println);
		// insert
		System.out.println("### insert ###");
		Emp insEmp = new Emp(106, "Human Resources Department", "Lady");
		mapper.insert(insEmp);
		System.out.println(mapper.select(106));
		// update
		System.out.println("### update ###");
		Emp updEmp = new Emp(106, "Accounting department", null);
		mapper.update(updEmp);
		System.out.println(mapper.select(106));
		// delete
		System.out.println("### delete ###");
		mapper.delete(106);
		System.out.println(mapper.select(106));
	}
}

Mapper It is linked with the SQL id defined in the mapper XML described later. However, if it is simple SQL, you can fill in the annotation without preparing XML. In this example, SQL is defined by annotation at the time of selectAll.

EmpMapper.java


package jp.demo.mapper;
import java.util.List;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import jp.demo.domain.Emp;

@Mapper
public interface EmpMapper {
	@Select("select id,department,name from emp")
	List<Emp> selectAll();
	Emp select(int id);
	void insert(Emp emp);
	void update(Emp emp);
	void delete(int id);
}

Entity The following annotations are all Lombok, so in the case of Mybatis, no annotation is required for the Entity class.

EmpMapper.java


package jp.demo.domain;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Emp {
	private int id;
	private String department;
	private String name;
}

Mapper.xml It is linked with the method name and id of the mapper interface.

EmpMapper.xml


<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
  PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="jp.demo.mapper.EmpMapper">
	<resultMap id="empResultMap" type="jp.demo.domain.Emp">
		<result property="id" column="id" />
		<result property="department" column="department" />
		<result property="name" column="name" />
	</resultMap>
	<select id="select" resultMap="empResultMap">
		select id,department,name from emp
		where id=#{id};
	</select>
	<update id="update" parameterType="jp.demo.domain.Emp">
		update emp
		set department=#{department}
		where id=#{id};
	</update>
	<insert id="insert" parameterType="jp.demo.domain.Emp">
		insert into emp
		values (#{id},#{department},#{name});
	</insert>
 	<delete id="delete">
		delete from emp where id=#{id};
	</delete>
</mapper>

Execution result

Since SQL log is set in properties, SQL and parameters are also output.

console


### select all ###
2019-08-08 02:22:04.214  INFO 12776 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
2019-08-08 02:22:04.539  INFO 12776 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
2019-08-08 02:22:04.553 DEBUG 12776 --- [           main] jp.demo.mapper.EmpMapper.selectAll       : ==>  Preparing: select id,department,name from emp 
2019-08-08 02:22:04.585 DEBUG 12776 --- [           main] jp.demo.mapper.EmpMapper.selectAll       : ==> Parameters: 
2019-08-08 02:22:04.628 DEBUG 12776 --- [           main] jp.demo.mapper.EmpMapper.selectAll       : <==      Total: 5
Emp(id=101, department=Human Resources Department, name=Nico)
Emp(id=102, department=Development department, name=Dante)
Emp(id=103, department=Development department, name=Nero)
Emp(id=104, department=General Affairs Department, name=Trish)
Emp(id=105, department=Development department, name=Vergil)
### insert ###
2019-08-08 02:22:04.645 DEBUG 12776 --- [           main] jp.demo.mapper.EmpMapper.insert          : ==>  Preparing: insert into emp values (?,?,?); 
2019-08-08 02:22:04.648 DEBUG 12776 --- [           main] jp.demo.mapper.EmpMapper.insert          : ==> Parameters: 106(Integer),Human Resources Department(String),Lady(String)
2019-08-08 02:22:04.686 DEBUG 12776 --- [           main] jp.demo.mapper.EmpMapper.insert          : <==    Updates: 1
2019-08-08 02:22:04.687 DEBUG 12776 --- [           main] jp.demo.mapper.EmpMapper.select          : ==>  Preparing: select id,department,name from emp where id=?; 
2019-08-08 02:22:04.688 DEBUG 12776 --- [           main] jp.demo.mapper.EmpMapper.select          : ==> Parameters: 106(Integer)
2019-08-08 02:22:04.690 DEBUG 12776 --- [           main] jp.demo.mapper.EmpMapper.select          : <==      Total: 1
Emp(id=106, department=Human Resources Department, name=Lady)
### update ###
2019-08-08 02:22:04.691 DEBUG 12776 --- [           main] jp.demo.mapper.EmpMapper.update          : ==>  Preparing: update emp set department=? where id=?; 
2019-08-08 02:22:04.691 DEBUG 12776 --- [           main] jp.demo.mapper.EmpMapper.update          : ==> Parameters:Accounting department(String), 106(Integer)
2019-08-08 02:22:04.701 DEBUG 12776 --- [           main] jp.demo.mapper.EmpMapper.update          : <==    Updates: 1
2019-08-08 02:22:04.702 DEBUG 12776 --- [           main] jp.demo.mapper.EmpMapper.select          : ==>  Preparing: select id,department,name from emp where id=?; 
2019-08-08 02:22:04.702 DEBUG 12776 --- [           main] jp.demo.mapper.EmpMapper.select          : ==> Parameters: 106(Integer)
2019-08-08 02:22:04.704 DEBUG 12776 --- [           main] jp.demo.mapper.EmpMapper.select          : <==      Total: 1
Emp(id=106, department=Accounting department, name=Lady)
### delete ###
2019-08-08 02:22:04.705 DEBUG 12776 --- [           main] jp.demo.mapper.EmpMapper.delete          : ==>  Preparing: delete from emp where id=?; 
2019-08-08 02:22:04.706 DEBUG 12776 --- [           main] jp.demo.mapper.EmpMapper.delete          : ==> Parameters: 106(Integer)
2019-08-08 02:22:04.708 DEBUG 12776 --- [           main] jp.demo.mapper.EmpMapper.delete          : <==    Updates: 1
2019-08-08 02:22:04.708 DEBUG 12776 --- [           main] jp.demo.mapper.EmpMapper.select          : ==>  Preparing: select id,department,name from emp where id=?; 
2019-08-08 02:22:04.709 DEBUG 12776 --- [           main] jp.demo.mapper.EmpMapper.select          : ==> Parameters: 106(Integer)
2019-08-08 02:22:04.710 DEBUG 12776 --- [           main] jp.demo.mapper.EmpMapper.select          : <==      Total: 0
null

[Implementation] For Hibernate

POM Only dependencies are excerpted. The only difference from the above Mybatis is the dependency "mybatis-spring-boot-starter".

pom.xml


	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-batch</artifactId>
		</dependency>
		<dependency>
			<groupId>org.mybatis.spring.boot</groupId>
			<artifactId>mybatis-spring-boot-starter</artifactId>
			<version>2.1.0</version>
		</dependency>

		<dependency>
			<groupId>org.postgresql</groupId>
			<artifactId>postgresql</artifactId>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<optional>true</optional>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
		<dependency>
			<groupId>org.springframework.batch</groupId>
			<artifactId>spring-batch-test</artifactId>
			<scope>test</scope>
		</dependency>
	</dependencies>

Property

application.properties


spring.datasource.driver-class-name=org.postgresql.Driver
spring.datasource.url=jdbc:postgresql://localhost:5432/test01
spring.datasource.username=postgres
spring.datasource.password=postgres
#Output SQL log
logging.level.org.hibernate.SQL=debug
logging.level.org.hibernate.type.descriptor.sql.BasicBinder=trace
logging.level.org.hibernate.EnumType=trace

In the case of Hibernate, it seems better to add hibernate.properties to prevent unnecessary logging in addition to the connection information.

hibernate.properties


hibernate.jdbc.lob.non_contextual_creation=true

If you do not set hibernate.properties above, the following error will be logged when you start Spring boot app. (Can be executed)

console


java.lang.reflect.InvocationTargetException: null
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_25]
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_25]
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_25]
	at java.lang.reflect.Method.invoke(Method.java:483) ~[na:1.8.0_25]

~~ Omitted ~~

	at jp.demo.HibernateApplication.main(HibernateApplication.java:11) [classes/:na]
Caused by: java.sql.SQLFeatureNotSupportedException: org.postgresql.jdbc.PgConnection.createClob()The method has not been implemented yet.
	at org.postgresql.Driver.notImplemented(Driver.java:688) ~[postgresql-42.2.5.jar:42.2.5]
	at org.postgresql.jdbc.PgConnection.createClob(PgConnection.java:1269) ~[postgresql-42.2.5.jar:42.2.5]
	... 44 common frames omitted

SpringBootApplication

HibernateApplication.java


package jp.demo;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.ConfigurableApplicationContext;

@SpringBootApplication
public class HibernateApplication {
	public static void main(String[] args) {
		try(ConfigurableApplicationContext ctx = SpringApplication.run(HibernateApplication.class, args)){
			DemoService app = ctx.getBean(DemoService.class);
            app.execute(args);
		} catch (Exception e) {
            e.printStackTrace();
        }
	}
}

Service

DemoService.java


package jp.demo;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import jp.demo.domain.Emp;
import jp.demo.domain.EmpRepository;

@Service
public class DemoService {
	@Autowired
	EmpRepository repository;

	public void execute(String[] args) {
		// select all
		System.out.println("### select all ###");
		List<Emp> list = repository.findAll();
		list.forEach(System.out::println);
		// insert
		System.out.println("### insert ###");
		Emp insEmp = new Emp(106, "Human Resources Department", "Lady");
		repository.save(insEmp);
		System.out.println(repository.findById(106).get());
		// update
		System.out.println("### update ###");
		Emp updEmp = repository.findById(106).get();
		repository.save(updEmp);
		System.out.println(repository.findById(106).get());
		// delete
		System.out.println("### delete ###");
		repository.deleteById(106);
		System.out.println(repository.findById(106).isPresent());
	}
}

It seems that INSERT and UPDATE are grouped in CrudRepository.save (). As for how to separate it, before updating, it seems that data extraction is performed first with the attribute to which ID annotation is attached in the entity class described later, UPDATE if it exists, and INSERT if it does not exist. So using CrudRepository.save () gives you the following benefits instead of having to create your own SQL.

  1. Since all attributes are to be updated, it is necessary to SELECT the current record and then pass the entity with the required columns rewritten to the save method.

  2. SELECT is automatically executed in advance to separate INSERT and UPDATE.

  3. may not be helped, but 2. seems to be able to control not to issue SELECT by setting? Or rather, I want you to be able to do it. ..

Repository No method definition required. It's amazing because it doesn't require SQL. However, it seems that the degree of freedom is considerably reduced if you just use JpaRepository as described in Service above.

EmpRepository.java


package jp.demo.domain;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface EmpRepository extends JpaRepository<Emp, Integer> {

}

Entity In the case of Mybatis, annotation was unnecessary, but since Hibernate uses JPA, Various JPA annotations are added.

Emp.java


package jp.demo.domain;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@NoArgsConstructor
@AllArgsConstructor
@Entity
@Table(name="emp")
public class Emp {
	@Id
	@Column(name="id")
	private int id;
	@Column(name="department")
	private String department;
	@Column(name="name")
	private String name;
}

Execution result

This one is also logged. As I wrote in Service, in the case of INSERT, pre-SELECT for judging whether it is INSERT or UPDATE, In the case of UPDATE, in addition to the SELECT for judgment, a SELECT for acquiring columns other than the update column is issued.

console


### select all ###
2019-08-08 01:13:02.862  INFO 10828 --- [           main] o.h.h.i.QueryTranslatorFactoryInitiator  : HHH000397: Using ASTQueryTranslatorFactory
2019-08-08 01:13:03.146 DEBUG 10828 --- [           main] org.hibernate.SQL                        : select emp0_.id as id1_0_, emp0_.department as departme2_0_, emp0_.name as name3_0_ from emp emp0_
Emp(id=101, department=Human Resources Department, name=Nico)
Emp(id=102, department=Development department, name=Dante)
Emp(id=103, department=Development department, name=Nero)
Emp(id=104, department=General Affairs Department, name=Trish)
Emp(id=105, department=Development department, name=Vergil)
### insert ###
2019-08-08 01:13:03.206 DEBUG 10828 --- [           main] org.hibernate.SQL                        : select emp0_.id as id1_0_0_, emp0_.department as departme2_0_0_, emp0_.name as name3_0_0_ from emp emp0_ where emp0_.id=?
2019-08-08 01:13:03.225 TRACE 10828 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [INTEGER] - [106]
2019-08-08 01:13:03.285 DEBUG 10828 --- [           main] org.hibernate.SQL                        : insert into emp (department, name, id) values (?, ?, ?)
2019-08-08 01:13:03.286 TRACE 10828 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [VARCHAR] - [Human Resources Department]
2019-08-08 01:13:03.286 TRACE 10828 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [2] as [VARCHAR] - [Lady]
2019-08-08 01:13:03.287 TRACE 10828 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [3] as [INTEGER] - [106]
2019-08-08 01:13:03.314 DEBUG 10828 --- [           main] org.hibernate.SQL                        : select emp0_.id as id1_0_0_, emp0_.department as departme2_0_0_, emp0_.name as name3_0_0_ from emp emp0_ where emp0_.id=?
2019-08-08 01:13:03.315 TRACE 10828 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [INTEGER] - [106]
Emp(id=106, department=Human Resources Department, name=Lady)
### update ###
2019-08-08 01:13:03.338 DEBUG 10828 --- [           main] org.hibernate.SQL                        : select emp0_.id as id1_0_0_, emp0_.department as departme2_0_0_, emp0_.name as name3_0_0_ from emp emp0_ where emp0_.id=?
2019-08-08 01:13:03.339 TRACE 10828 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [INTEGER] - [106]
2019-08-08 01:13:03.343 DEBUG 10828 --- [           main] org.hibernate.SQL                        : select emp0_.id as id1_0_0_, emp0_.department as departme2_0_0_, emp0_.name as name3_0_0_ from emp emp0_ where emp0_.id=?
2019-08-08 01:13:03.344 TRACE 10828 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [INTEGER] - [106]
2019-08-08 01:13:03.348 DEBUG 10828 --- [           main] org.hibernate.SQL                        : update emp set department=?, name=? where id=?
2019-08-08 01:13:03.348 TRACE 10828 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [VARCHAR] - [Accounting department]
2019-08-08 01:13:03.349 TRACE 10828 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [2] as [VARCHAR] - [Lady]
2019-08-08 01:13:03.349 TRACE 10828 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [3] as [INTEGER] - [106]
2019-08-08 01:13:03.354 DEBUG 10828 --- [           main] org.hibernate.SQL                        : select emp0_.id as id1_0_0_, emp0_.department as departme2_0_0_, emp0_.name as name3_0_0_ from emp emp0_ where emp0_.id=?
2019-08-08 01:13:03.354 TRACE 10828 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [INTEGER] - [106]
Emp(id=106, department=Accounting department, name=Lady)
### delete ###
2019-08-08 01:13:03.359 DEBUG 10828 --- [           main] org.hibernate.SQL                        : select emp0_.id as id1_0_0_, emp0_.department as departme2_0_0_, emp0_.name as name3_0_0_ from emp emp0_ where emp0_.id=?
2019-08-08 01:13:03.359 TRACE 10828 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [INTEGER] - [106]
2019-08-08 01:13:03.371 DEBUG 10828 --- [           main] org.hibernate.SQL                        : delete from emp where id=?
2019-08-08 01:13:03.371 TRACE 10828 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [INTEGER] - [106]
2019-08-08 01:13:03.376 DEBUG 10828 --- [           main] org.hibernate.SQL                        : select emp0_.id as id1_0_0_, emp0_.department as departme2_0_0_, emp0_.name as name3_0_0_ from emp emp0_ where emp0_.id=?
2019-08-08 01:13:03.376 TRACE 10828 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [INTEGER] - [106]
false

This summary

** ManyToOne ** I can understand the greatness (terribleness) of Hibernate by preparing a combination. .. So next time I will try it in the case with binding. I also have to try HQL. .. By the way, what I thought at this point ↓

Mybatis

merit

--Because you need to implement SQL, it is clear what you are extracting / updating --It's also good for studying SQL --No extra queries can be thrown

Demerit

--Hard work for newcomers who do not know SQL --Since SQL is required, the amount of implementation will increase even with simple CRUD processing.

Hibernate

merit

--No need to write SQL --Can be implemented as if it were an object without being aware of DB --There is a unique data access language that describes on an object basis instead of a table called HQL.

Demerit

--Many settings and complicated --I can't study SQL --A SELECT statement is issued before updating instead of a small amount of implementation ――It's much black boxed compared to Mybatis, so it's hard to find where the problem is occurring.

Recommended Posts

Basic CRUD comparison between Mybatis and Hibernate
[Note] Cooperation between Java and DB (basic)
Accuracy comparison between Pivot Gaussian elimination and Gaussian elimination
correspondence between mybatis table column name and field name
About the relationship between HTTP methods, actions and CRUD
Differences between Fetch Type LAZY and EAGER in Hibernate
Hello World comparison between Spark Framework and Spring Boot
[Java] String comparison and && and ||
Switch between JDK 7 and JDK 8
Difference between vh and%
Difference between i ++ and ++ i
Basic operators and operations
[Java] Difference between assignment of basic type variable and assignment of reference type variable