How to use this driver in a Spring Boot application?

[aDramaQueen]

Hi,

I don't quiet understand how the pragma jdbc.explicit_readonly=true should work. If I run my Spring Boot application with this driver, the appropriate exception is thrown. But what now? Do I have to handle the exception by myself? This would mean I have to wrap each time I want to save something in a try-catch block. Is this meant to be like that?

Below a snapshot from the exception with some SQL queries, that show Hibernate is initializing the necessary tables. But when my application is executing the first call to a JPARepository, the java.sql.SQLException is thrown.

...

Hibernate: create table mbus_meter (auto_start boolean not null, close_meter_connection_after_readout boolean not null, close_timeseries_connection_after_readout boolean not null, meter_time_stamp boolean not null, repeat_interval_inms bigint not null check (repeat_interval_inms>=0), id blob not null, database_table varchar(63) not null, database_name varchar(127) not null, description varchar(127), tokenid varchar(127) not null, protocol varchar(255) not null check (protocol in ('MODBUS_TCP','MODBUS_RTU','MODBUS_ASCII','BACNET_IP','BACNET_MSTP','MBUS','EKM_REALTIME','EKM_SUMMARY')), primary key (id))
Hibernate: create table meter_model_impl (auto_start boolean not null, close_meter_connection_after_readout boolean not null, close_timeseries_connection_after_readout boolean not null, meter_time_stamp boolean not null, repeat_interval_inms bigint not null check (repeat_interval_inms>=0), id blob not null, database_table varchar(63) not null, database_name varchar(127) not null, description varchar(127), tokenid varchar(127) not null, protocol varchar(255) not null check (protocol in ('MODBUS_TCP','MODBUS_RTU','MODBUS_ASCII','BACNET_IP','BACNET_MSTP','MBUS','EKM_REALTIME','EKM_SUMMARY')), primary key (id))
Hibernate: create table modbusasciimeter_model (auto_start boolean not null, close_meter_connection_after_readout boolean not null, close_timeseries_connection_after_readout boolean not null, meter_time_stamp boolean not null, repeat_interval_inms bigint not null check (repeat_interval_inms>=0), id blob not null, database_table varchar(63) not null, database_name varchar(127) not null, description varchar(127), tokenid varchar(127) not null, protocol varchar(255) not null check (protocol in ('MODBUS_TCP','MODBUS_RTU','MODBUS_ASCII','BACNET_IP','BACNET_MSTP','MBUS','EKM_REALTIME','EKM_SUMMARY')), primary key (id))
Hibernate: create table modbus_meter_model (auto_start boolean not null, close_meter_connection_after_readout boolean not null, close_timeseries_connection_after_readout boolean not null, meter_time_stamp boolean not null, unitid smallint not null check ((unitid>=0) and (unitid<=255)), repeat_interval_inms bigint not null check (repeat_interval_inms>=0), id blob not null, database_table varchar(63) not null, database_name varchar(127) not null, description varchar(127), tokenid varchar(127) not null, protocol varchar(255) not null check (protocol in ('MODBUS_TCP','MODBUS_RTU','MODBUS_ASCII','BACNET_IP','BACNET_MSTP','MBUS','EKM_REALTIME','EKM_SUMMARY')), primary key (id))
Hibernate: create table modbusrtumeter_model (auto_start boolean not null, close_meter_connection_after_readout boolean not null, close_timeseries_connection_after_readout boolean not null, meter_time_stamp boolean not null, repeat_interval_inms bigint not null check (repeat_interval_inms>=0), id blob not null, database_table varchar(63) not null, database_name varchar(127) not null, description varchar(127), tokenid varchar(127) not null, protocol varchar(255) not null check (protocol in ('MODBUS_TCP','MODBUS_RTU','MODBUS_ASCII','BACNET_IP','BACNET_MSTP','MBUS','EKM_REALTIME','EKM_SUMMARY')), primary key (id))
Hibernate: create table modbustcpmeter_model (auto_start boolean not null, close_meter_connection_after_readout boolean not null, close_timeseries_connection_after_readout boolean not null, meter_time_stamp boolean not null, port integer not null check ((port<=65535) and (port>=1)), secure boolean not null, unitid smallint not null check ((unitid>=0) and (unitid<=255)), repeat_interval_inms bigint not null check (repeat_interval_inms>=0), id blob not null, database_table varchar(63) not null, database_name varchar(127) not null, description varchar(127), tokenid varchar(127) not null, ip_address varchar(255) not null, protocol varchar(255) not null check (protocol in ('MODBUS_TCP','MODBUS_RTU','MODBUS_ASCII','BACNET_IP','BACNET_MSTP','MBUS','EKM_REALTIME','EKM_SUMMARY')), primary key (id))
Hibernate: create table object_properties (objects_id bigint not null, bacnet_properties_data_type varchar(255) not null, bacnet_properties_property varchar(255) not null, primary key (objects_id, bacnet_properties_data_type, bacnet_properties_property))
Hibernate: create table register_group_model (start_address integer not null, stop_address integer not null check (stop_address>=1), modbus_register_group_meter_relationship blob not null, primary key (start_address, modbus_register_group_meter_relationship))
Hibernate: create table register_model (correction_factor float not null check (correction_factor>=0), number_of_registers integer not null check (number_of_registers>=1), start_address integer not null, modbus_register_meter_relationship blob not null, byte_order varchar(255) check (byte_order in ('BIG_ENDIAN','LITTLE_ENDIAN')), measurement varchar(255) not null, register_format varchar(255) check (register_format in ('BIT','UNSIGNED_BYTE','SIGNED_BYTE','TWO_UNSIGNED_BYTES','TWO_SIGNED_BYTES','UNSIGNED_SHORT','SIGNED_SHORT','UNSIGNED_INTEGER','SIGNED_INTEGER','FLOAT','UNSIGNED_LONG','SIGNED_LONG','DOUBLE')), register_type varchar(255) check (register_type in ('INPUT_DISCRETE','COIL','INPUT','HOLDING')), value_type varchar(255) check (value_type in ('VALUE','MANTISSA','EXPONENT')), primary key (start_address, modbus_register_meter_relationship, measurement))
Hibernate: create table settings (dark_mode boolean not null, i3d_user_id bigint not null, primary key (i3d_user_id))
Hibernate: create table token_model (database_name varchar(255) not null, database_type varchar(255) not null check (database_type in ('APACHE_DRUID','APACHE_IOT_DB','GRAPHITE','INFLUX_DB','MONGO_DB','PROMETHEUS','TIMESCALE')), influx_organization_name varchar(255), token varchar(255) not null, tokenid varchar(255) not null, url varchar(255) not null, primary key (tokenid))
Hibernate: create table user_permission (user_user_id bigint not null, domain varchar(255) not null check (domain in ('USER','STATISTIC','SYSTEM','TIME_SERIES_DATABASE','ONLINE','METER','MODBUS','BACNET','MBUS')), primary key (user_user_id, domain))
Hibernate: create table user_permission_operations (user_permission_user_user_id bigint not null, operations varchar(255) check (operations in ('CREATE_UPDATE','READ','DELETE')), user_permission_domain varchar(255) not null)
06-09-2024 15:58:55.751  INFO --- [  restartedMain] j.LocalContainerEntityManagerFactoryBean : Initialized JPA EntityManagerFactory for persistence unit 'default'
06-09-2024 15:58:56.252  INFO --- [  restartedMain] r$InitializeUserDetailsManagerConfigurer : Global AuthenticationManager configured with UserDetailsService bean with name userService
06-09-2024 15:58:56.280  INFO --- [  restartedMain] o.s.d.j.r.query.QueryEnhancerFactory     : Hibernate is in classpath; If applicable, HQL parser will be used.
06-09-2024 15:58:57.094  INFO --- [  restartedMain] o.s.b.a.e.web.EndpointLinksResolver      : Exposing 16 endpoints beneath base path '/actuator'
06-09-2024 15:58:57.180  INFO --- [  restartedMain] o.s.b.a.w.s.WelcomePageHandlerMapping    : Adding welcome page template: index
06-09-2024 15:58:57.883  INFO --- [  restartedMain] o.s.b.d.a.OptionalLiveReloadServer       : LiveReload server is running on port 35729
06-09-2024 15:58:57.958  INFO --- [  restartedMain] o.a.coyote.http11.Http11NioProtocol      : Starting ProtocolHandler ["http-nio-127.0.0.1-8080"]
06-09-2024 15:58:57.966  INFO --- [  restartedMain] o.a.coyote.http11.Http11NioProtocol      : Starting ProtocolHandler ["http-nio-8081"]
06-09-2024 15:58:57.967  INFO --- [  restartedMain] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat started on ports 8080 (http), 8081 (http) with context path '/'
06-09-2024 15:58:57.976  INFO --- [  restartedMain] de.i3denergy.communication.Main          : Started Main in 5.922 seconds (process running for 6.486)
Hibernate: select ac1_0.id,ac1_0.jwt_key,ac1_0.time_zone from application_config ac1_0 where ac1_0.id=?
06-09-2024 15:58:58.007  WARN --- [  restartedMain] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: null
06-09-2024 15:58:58.007 ERROR --- [  restartedMain] o.h.engine.jdbc.spi.SqlExceptionHelper   : A statement has already been executed on this connection; cannot upgrade to write transaction
06-09-2024 15:58:58.014  INFO --- [  restartedMain] .s.b.a.l.ConditionEvaluationReportLogger : 

Error starting ApplicationContext. To display the condition evaluation report re-run your application with 'debug' enabled.
06-09-2024 15:58:58.024 ERROR --- [  restartedMain] o.s.boot.SpringApplication               : Application run failed

org.springframework.orm.jpa.JpaSystemException: JDBC exception executing SQL [select ac1_0.id,ac1_0.jwt_key,ac1_0.time_zone from application_config ac1_0 where ac1_0.id=?] [A statement has already been executed on this connection; cannot upgrade to write transaction] [n/a]
	at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:341)
	at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:241)
	at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:550)
	at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61)
	at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:335)
	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:160)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
	at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:165)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
	at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:223)
	at jdk.proxy3/jdk.proxy3.$Proxy256.findById(Unknown Source)
	at de.i3denergy.communication.ApplicationConfiguration$DatabaseOperator.getOldConfig(ApplicationConfiguration.java:48)
	at de.i3denergy.communication.ApplicationConfiguration.setup(ApplicationConfiguration.java:73)
	at de.i3denergy.communication.ApplicationConfiguration.initializeStaticRepository(ApplicationConfiguration.java:69)
	at de.i3denergy.communication.runners.SetupConfiguration.run(SetupConfiguration.java:136)
	at org.springframework.boot.SpringApplication.lambda$callRunner$4(SpringApplication.java:786)
	at org.springframework.util.function.ThrowingConsumer$1.acceptWithException(ThrowingConsumer.java:83)
	at org.springframework.util.function.ThrowingConsumer.accept(ThrowingConsumer.java:60)
	at org.springframework.util.function.ThrowingConsumer$1.accept(ThrowingConsumer.java:88)
	at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:798)
	at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:786)
	at org.springframework.boot.SpringApplication.lambda$callRunners$3(SpringApplication.java:774)
	at java.base/java.util.stream.ForEachOps$ForEachOp$OfRef.accept(ForEachOps.java:184)
	at java.base/java.util.stream.SortedOps$SizedRefSortingSink.end(SortedOps.java:357)
	at java.base/java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:510)
	at java.base/java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:499)
	at java.base/java.util.stream.ForEachOps$ForEachOp.evaluateSequential(ForEachOps.java:151)
	at java.base/java.util.stream.ForEachOps$ForEachOp$OfRef.evaluateSequential(ForEachOps.java:174)
	at java.base/java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)
	at java.base/java.util.stream.ReferencePipeline.forEach(ReferencePipeline.java:596)
	at org.springframework.boot.SpringApplication.callRunners(SpringApplication.java:774)
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:342)
	at org.springframework.boot.builder.SpringApplicationBuilder.run(SpringApplicationBuilder.java:149)
	at de.i3denergy.communication.Main.main(Main.java:75)
	at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
	at java.base/java.lang.reflect.Method.invoke(Method.java:580)
	at org.springframework.boot.devtools.restart.RestartLauncher.run(RestartLauncher.java:50)
Caused by: org.hibernate.exception.GenericJDBCException: JDBC exception executing SQL [select ac1_0.id,ac1_0.jwt_key,ac1_0.time_zone from application_config ac1_0 where ac1_0.id=?] [A statement has already been executed on this connection; cannot upgrade to write transaction] [n/a]
	at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:63)
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:108)
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:94)
	at org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.executeQuery(DeferredResultSetAccess.java:264)
	at org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.getResultSet(DeferredResultSetAccess.java:167)
	at org.hibernate.sql.results.jdbc.internal.JdbcValuesResultSetImpl.advanceNext(JdbcValuesResultSetImpl.java:265)
	at org.hibernate.sql.results.jdbc.internal.JdbcValuesResultSetImpl.processNext(JdbcValuesResultSetImpl.java:145)
	at org.hibernate.sql.results.jdbc.internal.AbstractJdbcValues.next(AbstractJdbcValues.java:19)
	at org.hibernate.sql.results.internal.RowProcessingStateStandardImpl.next(RowProcessingStateStandardImpl.java:67)
	at org.hibernate.sql.results.spi.ListResultsConsumer.consume(ListResultsConsumer.java:189)
	at org.hibernate.sql.results.spi.ListResultsConsumer.consume(ListResultsConsumer.java:33)
	at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.doExecuteQuery(JdbcSelectExecutorStandardImpl.java:211)
	at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.executeQuery(JdbcSelectExecutorStandardImpl.java:83)
	at org.hibernate.sql.exec.spi.JdbcSelectExecutor.list(JdbcSelectExecutor.java:76)
	at org.hibernate.sql.exec.spi.JdbcSelectExecutor.list(JdbcSelectExecutor.java:65)
	at org.hibernate.loader.ast.internal.SingleIdLoadPlan.load(SingleIdLoadPlan.java:145)
	at org.hibernate.loader.ast.internal.SingleIdLoadPlan.load(SingleIdLoadPlan.java:117)
	at org.hibernate.loader.ast.internal.SingleIdEntityLoaderStandardImpl.load(SingleIdEntityLoaderStandardImpl.java:75)
	at org.hibernate.persister.entity.AbstractEntityPersister.doLoad(AbstractEntityPersister.java:3726)
	at org.hibernate.persister.entity.AbstractEntityPersister.load(AbstractEntityPersister.java:3715)
	at org.hibernate.event.internal.DefaultLoadEventListener.loadFromDatasource(DefaultLoadEventListener.java:604)
	at org.hibernate.event.internal.DefaultLoadEventListener.loadFromCacheOrDatasource(DefaultLoadEventListener.java:590)
	at org.hibernate.event.internal.DefaultLoadEventListener.load(DefaultLoadEventListener.java:560)
	at org.hibernate.event.internal.DefaultLoadEventListener.doLoad(DefaultLoadEventListener.java:544)
	at org.hibernate.event.internal.DefaultLoadEventListener.load(DefaultLoadEventListener.java:207)
	at org.hibernate.event.internal.DefaultLoadEventListener.loadWithRegularProxy(DefaultLoadEventListener.java:290)
	at org.hibernate.event.internal.DefaultLoadEventListener.proxyOrLoad(DefaultLoadEventListener.java:242)
	at org.hibernate.event.internal.DefaultLoadEventListener.doOnLoad(DefaultLoadEventListener.java:111)
	at org.hibernate.event.internal.DefaultLoadEventListener.onLoad(DefaultLoadEventListener.java:68)
	at org.hibernate.event.service.internal.EventListenerGroupImpl.fireEventOnEachListener(EventListenerGroupImpl.java:138)
	at org.hibernate.internal.SessionImpl.fireLoadNoChecks(SessionImpl.java:1225)
	at org.hibernate.internal.SessionImpl.fireLoad(SessionImpl.java:1213)
	at org.hibernate.loader.internal.IdentifierLoadAccessImpl.load(IdentifierLoadAccessImpl.java:209)
	at org.hibernate.loader.internal.IdentifierLoadAccessImpl.doLoad(IdentifierLoadAccessImpl.java:160)
	at org.hibernate.loader.internal.IdentifierLoadAccessImpl.lambda$load$1(IdentifierLoadAccessImpl.java:149)
	at org.hibernate.loader.internal.IdentifierLoadAccessImpl.perform(IdentifierLoadAccessImpl.java:112)
	at org.hibernate.loader.internal.IdentifierLoadAccessImpl.load(IdentifierLoadAccessImpl.java:149)
	at org.hibernate.internal.SessionImpl.find(SessionImpl.java:2429)
	at org.hibernate.internal.SessionImpl.find(SessionImpl.java:2400)
	at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
	at java.base/java.lang.reflect.Method.invoke(Method.java:580)
	at org.springframework.orm.jpa.SharedEntityManagerCreator$SharedEntityManagerInvocationHandler.invoke(SharedEntityManagerCreator.java:319)
	at jdk.proxy3/jdk.proxy3.$Proxy221.find(Unknown Source)
	at org.springframework.data.jpa.repository.support.SimpleJpaRepository.findById(SimpleJpaRepository.java:321)
	at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
	at java.base/java.lang.reflect.Method.invoke(Method.java:580)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:354)
	at org.springframework.data.repository.core.support.RepositoryMethodInvoker$RepositoryFragmentMethodInvoker.lambda$new$0(RepositoryMethodInvoker.java:277)
	at org.springframework.data.repository.core.support.RepositoryMethodInvoker.doInvoke(RepositoryMethodInvoker.java:170)
	at org.springframework.data.repository.core.support.RepositoryMethodInvoker.invoke(RepositoryMethodInvoker.java:158)
	at org.springframework.data.repository.core.support.RepositoryComposition$RepositoryFragments.invoke(RepositoryComposition.java:516)
	at org.springframework.data.repository.core.support.RepositoryComposition.invoke(RepositoryComposition.java:285)
	at org.springframework.data.repository.core.support.RepositoryFactorySupport$ImplementationMethodExecutionInterceptor.invoke(RepositoryFactorySupport.java:628)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
	at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:168)
	at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:143)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
	at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:70)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
	at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:392)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:138)
	... 33 common frames omitted
Caused by: java.sql.SQLException: A statement has already been executed on this connection; cannot upgrade to write transaction
	at org.sqlite.jdbc3.JDBC3Connection.tryEnforceTransactionMode(JDBC3Connection.java:64)
	at org.sqlite.jdbc3.JDBC3PreparedStatement.executeQuery(JDBC3PreparedStatement.java:84)
	at org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.executeQuery(DeferredResultSetAccess.java:246)
	... 93 common frames omitted