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.
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=#
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>
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>
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
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>
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.
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.
SELECT is automatically executed in advance to separate INSERT and UPDATE.
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;
}
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
** 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
--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
--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
--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.
--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