Modifying MySql db while Streaming query is being executed

Question

I'm trying to use spring data "Streaming query" feature, but having problems with executing updates at the same time that I'm streaming.

This is the error I get:

SQL Error: 0, SQLState: S1000
Streaming result set com.mysql.cj.protocol.a.result.ResultsetRowsStreaming@483b7dc4 is still active. No statements may be issued when any streaming result sets are open and in use on a given connection. Ensure that you have called .close() on any active streaming result sets before attempting more queries.

I have two repositories - one is for reading and one is for writing data.

@Repository
public interface ArticleRepository extends JpaRepository<Article, Integer> {
    @QueryHints(value = @QueryHint(name = HINT_FETCH_SIZE, value = "" + Integer.MIN_VALUE))
    @Query("select a from Article a")
    Stream<Article> streamAll();
}

public interface WordRepository extends JpaRepository<Word, Integer> {}

The problem with this is that MYSQL does not allow to use same connection for writing to DB when Streaming ResultSet is open for reading. If I use new connection for writing while the Streaming ResultSet is open, everything works OK:

String cs = "jdbc:mysql:...";
        try(Connection c = DriverManager.getConnection(cs, "root", "123456");
            Statement st1 = c.createStatement();) {
            int res = st1.executeUpdate("INSERT INTO words (word) VALUES ('asd')");
        }

Now quesiton is - how to make WordRepository to always use new connection and not reuse the same one that is being used by ArticleRepository? I wouldn't like to talk directly to JDBC for this, but use same high-level JPA functionality already there. I'm using spring boot and Hikari.

UPDATE

After some testing, I found out that it also works If I manually inject new EntityManager into another repo as this:

public class WordRepositoryCstmImpl implements WordRepositoryCstm {
  private final EntityManager entityManager;

  WordRepositoryCstmImpl(EntityManagerFactory entityManagerFactory) {
      this.entityManager = entityManagerFactory.createEntityManager();
  }
...

The problem here is that I have to implement all the methods that are auto-generated by spring data (e.g. save must use this.entityManager.merge etc.)

So redefined question - how to inject new Entity manager into public interface WordRepository?

score 0 · Answer 1 · answered Jun 29 '18 at 13:52

0

Have you tried to create a custom Interface that extends the JPARepository and make a implementation where you set a new DataSource (in your case, maybe a new connection) for the EntityManager. Something like this: https://www.javabullets.com/access-entitymanager-spring-data-jpa/

Also, see this question: Spring Boot, Spring Data JPA with multiple DataSources

answered Jun 29 '18 at 13:52

Daniel C.

42
1
1
8

Yes, I updated question with this new findings - If I implement new repo, I can get new EntityManager and it works, but the problem with this is that I have to create all method manually (even save method must be manually created in java). – Bojan Vukasovic Jun 29 '18 at 14:10

Modifying MySql db while Streaming query is being executed

1 Answers1