The effective way for Hibernate Stateless Session batch inserting with associated instance

Question

I am trying to use Hibernate stateless session to do batch insertion

class Book {
    String title;
    String author;
    Double price;
    Publisher publisher;

    static constraints = {
        publisher nullable: true
    }

    static mapping = {
        id generator: 'assigned'
    }
}


class Publisher {

    String name

    static hasMany = [book: Book] // add one line here

    static constraints = {

    }

    static mapping = {
        id generator: 'assigned'
    }
}

The batch inserting test code:

class BatchController {
    SessionFactory sessionFactory;

    def testBatchInsert() {
        StatelessSession session = sessionFactory.openStatelessSession()
        Transaction tx = session.beginTransaction();
        int count = 100;
        for (int i = 0; i < count; i++) {
            def publisher = ["publisher": (i % 1000)] // make publisher id
            //The above code have to load publisher from db, or "save the transient instance before flushing" exception will throw.
            Book book = new Book()
            bindData(book, publisher) // use data binding here to set publisher id for the instance
            book.setId(i)
            book.setTitle("title $i")
            book.setAuthor("author $i")
            book.setPrice(123.456)
            session.insert(book)
        }
        tx.commit()
        render "finished!"
    }
}

Any way to skip loading publisher from db and improve the processing performance? (All publishers exist in the table already).

Can't you load all publishers at once and then assign them to `Book` from your memory? — saw303, Dec 08 '17 at 06:42
If the number of publishers is very large, it may throw OOM exception and more time is need. It's not a effective way. — yang wang, Dec 08 '17 at 07:29
well, I fear you will need to lead each publisher as your listing does or you work directly using SQL and perform your inserts on your datasource. In that case you don't have to read the publisher since the publisher id is calculated in your example — saw303, Dec 08 '17 at 07:37
Although using native sql(PrepareStatement/addBatch/executeBatch) can archive the best performance, but it's not my favorite and first choice. — yang wang, Dec 08 '17 at 08:05

Nic Olas · Answer 1 · 2017-12-08T11:36:47.297

0

You can try to use bindData as below

    StatelessSession session = sessionFactory.openStatelessSession()
    for (int i = 0; i < count; i++) {
        def publisher = ["publisher": (i % 1000)] // make publisher id
        //The above code have to load publisher from db, or "save the transient instance before flushing" exception will throw.
        Book book = new Book()
        bindData(book, publisher) // use data binding here to set publisher id for the instance
        book.setId(start + i)
        book.setTitle("title $i")
        book.setAuthor("author $i")
        book.setPrice(123.456) 
        session.insert(book)
    }

// Add a relation between publisher and book in domain publisher

class Publisher {

    String name

    static hasMany = [book: Book] // add one line here

    static constraints = {

    }

    static mapping = {
        id generator: 'assigned'
    }
}

Ref: http://docs.grails.org/latest/ref/Controllers/bindData.html
Hope it can help.

edited Dec 08 '17 at 11:36

answered Dec 08 '17 at 10:27

Nic Olas

451
2
10

bindData will trigger a sql like below. Hibernate: select publisher0_.id as id1_1_0_, publisher0_.version as version2_1_0_, publisher0_.name as name3_1_0_ from publisher publisher0_ where publisher0_.id=? – yang wang Dec 08 '17 at 10:45
Dear, you may need to add a relation in table Publisher as above to make no query every binding. I edit my post to the change. – Nic Olas Dec 08 '17 at 11:33
What grails version you ae using? I am using grails 3.2. – Nic Olas Dec 08 '17 at 14:53
You are right! i am confused at my side. Apology for this. :D – Nic Olas Dec 08 '17 at 14:59

The effective way for Hibernate Stateless Session batch inserting with associated instance

1 Answers1