Issue
I have one very annoying issue, i have read all existing documentation online and read all stackoverflow questions and answers related to this topic, but simply can not make this to work!
I am really desperate and i do not know what i am missing so i will try to give you all what i have so far. Basically what i am trying to do is to save a lot of data with one query instead of multiple queries for each object. As you can suspect i am using Spring Boot, Hibernate and MySql.
So basic facts that i have learned so far based on what i read related to "batch insert using mysql + hibernate" is next:
- Mysql does not support Sequence ID, so i can not use it, like i could use it for PostgreSql
- Hibernate does not support batch insert out of the box, there are couple of app properties that needs to be added
And this is what i have so far:
Application properties that i added:
spring.datasource.url=jdbc:mysql://localhost:32803/db?rewriteBatchedStatements=true
spring.jpa.properties.hibernate.jdbc.batch_size=50
spring.jpa.properties.hibernate.jdbc.lob.non_contextual_creation=true
spring.jpa.open-in-view=false
spring.jpa.properties.hibernate.show_sql=true
spring.jpa.properties.hibernate.use_sql_comments=true
spring.jpa.properties.hibernate.format_sql=true
spring.jpa.properties.hibernate.order_updates=true
spring.jpa.properties.hibernate.order_inserts=true
spring.jpa.properties.hibernate.batch_versioned_data=true
spring.jpa.properties.hibernate.id.new_generator_mappings=false
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.type=trace
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5Dialect
spring.jpa.properties.hibernate.current_session_context_class=org.springframework.orm.hibernate5.SpringSessionContext
@Entity
data class Person (
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
val id: Long?,
var firstName: String,
var lastName: String,
var country: String,
var org: Int
)
What i want is to save a lot of Persons at once, as you can see i added batch size 50, if i understood correctly that means i will do one database hit per 50 persons while saving. (correct me if i am wrong)
And at the end i have Repository where i execute that batch insert:
@Repository
class PersonRepositoryCustomImpl : PersonRepositoryCustom {
@PersistenceContext
private val entityManager: EntityManager? = null
@Transactional
override fun batchSave2(persons: Set<Person>) {
val session = entityManager!!.unwrap(Session::class.java)
persons.forEachIndexed { index, person ->
if ( index % 50 == 0 ) {
session!!.flush()
session.clear()
}
session!!.save(person)
}
session.close()
}
@Transactional
override fun <T : Person?> batchSave(entities: Collection<T>): Collection<T>? {
val savedEntities: MutableList<T> = ArrayList(entities.size)
var i = 0
for (t in entities) {
savedEntities.add(persistOrMerge(t))
i++
if (i % 50 == 0) { // Flush a batch of inserts and release memory.
entityManager!!.flush()
entityManager.clear()
}
}
return savedEntities
}
private fun <T : Configuration?> persistOrMerge(t: T): T {
return if (t!!.id == null) {
entityManager!!.persist(t)
t
} else {
entityManager!!.merge(t)
}
}
}
So here you can see that i have tried to make this works on 2 almost the same ways, but of course both of them seems not to work.
In order to confirm that i am actually doing batch insert i am looking at this:
https://tableplus.com/blog/2018/10/how-to-show-queries-log-in-mysql.html
so basically that should show me queries that are being executed on DB, and there i can see that for each person object i have one insert statement.
Basically that result of this query:
SELECT
*
FROM
mysql.general_log;
And there i can clearly see that i have multiple insert statements that do one query per object (person).
Edit: https://blog.arnoldgalovics.com/configuring-a-datasource-proxy-in-spring-boot/
I have also implemented datasource proxy, which proved me that i am not doing batch insert:
Name:, Time:1, Success:True, Type:Prepared, Batch:False, QuerySize:1, BatchSize:0, Query:["insert into person(firstName, lastName, country, org) values (?, ?, ?, ?)"], Params:[(10,John,Johny,USA,ORG)]
i have multiple of records like this one.
Thanks in advance for any kind of help!
Solution
Just to give an answer in case someone needs it:
So long story short i was not able to make MySql + hibernate batch processing work, for sake of testing i actually was able to make it work with PostgreSQL.
But anyway if anyone needs this with MySql there is a way using JDBC batch processing, and code more or less is very straight forward:
private String INSERT_SQL_PARAMS = "INSERT INTO item_params(p_key, p_value, item_id) values (?,?,?)"
override fun saveParams(configParams: Set<ItemParam>) {
jdbcTemplate!!.batchUpdate(INSERT_SQL_PARAMS , configParams, 3000) { ps, argument ->
ps.setLong(1, argument.pKey)
ps.setString(2, argument.pValue)
ps.setString(3, argument.itemId)
}
}
Answered By - Sahbaz
Answer Checked By - Marie Seifert (JavaFixing Admin)