Mybatis is different from Jpa
preface
These days to listen to friends say that JPA is very easy to use, do not have to write SQL. I wonder if a programmer can be called a programmer without writing SQL? Besides, the more advanced tools package more tools, the scalability and efficiency are very low. Besides, I don’t like things that are too encapsulated. I like handwritten SQL at ordinary times, so I always use Mybatis to write business. Then I found that jPA’s saveAll() batch insert batch update speed is too slow, resulting in some things imported with Excel are very slow, so that the things that can be solved by synchronization have to open an asynchronous each time imported, I feel this practice is very bad. In fact, asynchrony means that the current service is not affected to another time period to do, such as running scheduled tasks, asynchronously update incremental information, etc. There are a lot of asynchronous packages in the code, that is to say, excel import is asynchronous, and jPA is slow, and asynchronous includes asynchronous, the whole link is very long, and it takes a long time to troubleshoot possible problems.
Install JPA and MyBatis
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
Copy the codeAll these things do is introduce a SpringBoot XML as a parent class to create a class
@Data
public class TestMybatis {
private Long id;
/** * Domain account */
private String userId;
/** * main metric */
private String mainMetric;
/** ** submetric */
private String subMetric;
/** * Measure entries */
private String metricItem;
}
@SuppressWarnings("serial")
@javax.persistence.Entity
@javax.persistence.Table(name = "test")
@lombok.Data
public class TestJpa {
@javax.persistence.Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
/** * Domain account */
private String userId;
/** * main metric */
private String mainMetric;
/** ** submetric */
private String subMetric;
/** * Measure entries */
private String metricItem;
}
/ * * *@author Kakki
* @version 1.0
* @create* This is used for Jpa and Mapper */
@Repository
public interface TestRee extends JpaRepository<TestRe.String> {}Copy the codeThis is the XML for Mybatis<insert id="insertList">
insert into test(user_id,main_metric, sub_metric, metric_item) values
<foreach collection="param" item="item" separator=",">
(#{item.userId}, #{item.mainMetric}, #{item.subMetric}, #{item.metricItem})
</foreach>
</insert>
Copy the codeNow let’s look at the velocity
@Slf4j
@RunWith(SpringRunner.class)
@SpringBootTest(classes = {ColaDemoApplication.class})
class ColaDemoApplicationTests {
@Autowired
private TestRee testRee;
@Autowired
private MetricMapper metricMapper;
@Test
void contextLoads(a) {
List<TestJpa> jpaList = new ArrayList<>(1000);
List<com.kakki.colademo.gatewayimpl.database.dataobject.TestMybatis> mybatisList = new ArrayList<>(1000);
for (int i = 0; i < 1000; i++) {
TestJpa testJpa = new TestJpa();
testJpa.setMainMetric(String.format("mainMetric%d", i));
testJpa.setSubMetric(String.format("subMetric%d", i));
testJpa.setUserId(String.format("userId%d", i));
testJpa.setMetricItem(String.format("metricItem%d", i));
jpaList.add(testRe);
com.kakki.colademo.gatewayimpl.database.dataobject.TestMybatis testMybatis = new com.kakki.colademo.gatewayimpl.database.dataobject.TestMybatis();
testMybatis.setMainMetric(String.format("mainMetric%d", i));
testMybatis.setSubMetric(String.format("subMetric%d", i));
testMybatis.setUserId(String.format("userId%d", i));
testMybatis.setMetricItem(String.format("metricItem%d", i));
mybatisList.add(testR);
}
StopWatch jpa = new StopWatch();
jpa.start();
testRee.saveAll(jpaList);
jpa.stop();
log.info("[jpa]{}ms", jpa.getTotalTimeMillis());
StopWatch m = new StopWatch();
m.start();
metricMapper.insertList(mybatisList);
m.stop();
log.info("[m]{}ms", m.getTotalTimeMillis()); }}Copy the code22:35:10. 708. [the main] INFO C.E.C.C olaDemoApplicationTests - 10576 ms 22:35:31 [jpa]. [the main] 366 INFO c.e.c.ColaDemoApplicationTests - [m]138msCopy the code- I would say it’s about 10 times different, right? That’s just 1,000 pieces of data. Let’s try 10,000
22:36:48. 505. [the main] INFO C.E.C.C olaDemoApplicationTests - 8081 ms 22:37:05 [jpa]. [the main] 005 INFO C.E.C.C olaDemoApplicationTests - [m] 613 ms # try article 10 w 22:38:49. [the main] 085 INFO C.E.C.C olaDemoApplicationTests - [jpa] 65710 ms 22:39:09. 844. [the main] INFO C.E.C.C olaDemoApplicationTests - [m] 9448 msCopy the code- So that’s a big difference, right? Why the big difference? Take a look at the saveAll() source code
@Transactional
@Override
public <S extends T> List<S> saveAll(Iterable<S> entities) {
Assert.notNull(entities, "Entities must not be null!");
List<S> result = new ArrayList<S>();
for (S entity : entities) {
result.add(save(entity));
}
return result;
}
@Transactional
@Override
public <S extends T> S save(S entity) {
if (entityInformation.isNew(entity)) {
em.persist(entity);
return entity;
} else {
returnem.merge(entity); }}Copy the code- As can be seen from the above, the primary key is saved one by one and the primary key is checked to determine whether the primary key is empty, that is to say, n loops and n if judgments, so the performance must be very attenuated pull
conclusion
I read on the Internet that adding the following parameters can be made batch, but I have tried it and it doesn’t work at all. Maybe if you want to solve this problem, you need to rewrite his saveAll() method and fragment it to insert or update so that the performance is much better.
spring.jpa.properties.hibernate.jdbc.batch_size=10000
spring.jpa.properties.hibernate.jdbc.batch_versioned_data=true
spring.jpa.properties.hibernate.order_inserts=true
spring.jpa.properties.hibernate.order_updates=true
Copy the codeOf course, today I just use the performance of JPA and Mybatis comparison, but as a code farmers know that technology is for business services. Jpa also has its advantages, such as creating methods findAllByIdIn(List ids) that can directly retrieve the List queried by this condition. FindAllByOrderIdAndOrderType (String orderId, String orderType) as this can also, very convenient, also don’t have to write SQL, he will fully automated complete your query operation.
summary
Developing a small project, Jpa efficiency is certainly higher than Mybatis, but because the business needs are updated faster and faster, Jpa obviously can not meet a lot of things, and the maintenance of Sql is also more difficult than Mybatis. Therefore, I prefer Mybatis, and the Sql written is more concise and easier to maintain.