我正在开发一个使用Spring和jOOQ的Web应用程序。
考虑以下用例：
-
我打开了呈现数据库查询结果的URL：
select * from contract_ref，一切正常 -
我打开了URL，它试图运行触发Postgres错误的查询，即
select * from users_ref（表users_ref不存在），得到了错误：Servlet.service() for servlet [dispatcher] in context with path [/astra] threw exception [Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: jOOQ; bad SQL grammar [select * from "users_ref"]; nested exception is org.postgresql.util.PSQLException: ОШИБКА: отношение "users_ref" не существует。 -
当我试图从步骤1打开页面时，我得到错误
PSQLException: current transaction is aborted, commands ignored until end of transaction block，但我看到webapp试图执行步骤1中的语句。
在我看来，这种情况就像Postgres没有关闭事务，但我只关闭了select，所以不需要任何事务。
当我添加@Transactional时，上述ReferenceController.view行为的注释消失。
完整日志：http://pastebin.com/t3UmbeCy
applicationContext.xml：
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:cache="http://www.springframework.org/schema/cache"
xmlns:bean="http://www.springframework.org/schema/mvc"
xmlns:tx="http://www.springframework.org/schema/tx"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd http://www.springframework.org/schema/cache http://www.springframework.org/schema/cache/spring-cache.xsd http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd">
<cache:annotation-driven/>
<tx:annotation-driven transaction-manager="transactionManager" proxy-target-class="true" />
<context:component-scan base-package="net.kerba"/>
<context:property-placeholder location="classpath:config.properties"/>
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="localDbDataSource"/>
</bean>
<bean id="jacksonMessageConverter"
class="org.springframework.http.converter.json.MappingJackson2HttpMessageConverter"/>
<bean:annotation-driven>
<bean:message-converters>
<ref bean="jacksonMessageConverter"/>
</bean:message-converters>
</bean:annotation-driven>
<bean id="cacheManager" class="org.springframework.cache.support.SimpleCacheManager">
<property name="caches">
<set>
<bean class="org.springframework.cache.concurrent.ConcurrentMapCacheFactoryBean">
<property name="name" value="user-page"/>
</bean>
</set>
</property>
</bean>
<bean id="viewResolver" class="org.springframework.web.servlet.view.tiles3.TilesViewResolver">
<property name="requestContextAttribute" value="requestContext"/>
<property name="viewClass" value="org.springframework.web.servlet.view.tiles3.TilesView"/>
</bean>
<bean class="org.springframework.web.servlet.handler.SimpleMappingExceptionResolver">
<property name="exceptionMappings">
<props>
<prop key="java.lang.NullPointerException">exception</prop> <!-- map exception to view name -->
<prop key="org.springframework.jdbc.UncategorizedSQLException">exception</prop> <!-- map exception to view name -->
</props>
</property>
</bean>
<bean id="tilesConfigurer" class="org.springframework.web.servlet.view.tiles3.TilesConfigurer">
<property name="definitions">
<list>
<value>/WEB-INF/tiles/tiles-common.xml</value>
<value>/WEB-INF/tiles/tiles-admin.xml</value>
<value>/WEB-INF/tiles/tiles-requests.xml</value>
</list>
</property>
<property name="preparerFactoryClass"
value="org.springframework.web.servlet.view.tiles3.SpringBeanPreparerFactory"/>
</bean>
<bean id="liquibase" class="liquibase.integration.spring.SpringLiquibase">
<property name="dropFirst" value="true"/>
<property name="dataSource" ref="localDbDataSource"/>
<property name="changeLog" value="classpath:db.astra.index.xml"/>
</bean>
<bean id="localDbDataSource"
class="org.apache.tomcat.jdbc.pool.DataSource">
<property name="driverClassName" value="${db.driverClassName}"/>
<property name="url" value="${db.url}"/>
<property name="username" value="${db.username}"/>
<property name="password" value="${db.password}"/>
<property name="maxIdle" value="${db.maxIdle}"/>
<property name="minIdle" value="${db.minIdle}"/>
<property name="maxActive" value="${db.maxActive}"/>
<property name="timeBetweenEvictionRunsMillis" value="${db.timeBetweenEvictionRunsMillis}"/>
<property name="minEvictableIdleTimeMillis" value="${db.minEvictableIdleTimeMillis}"/>
<property name="testWhileIdle" value="${db.testWhileIdle}"/>
<property name="validationQuery" value="${db.validationQuery}"/>
<property name="removeAbandoned" value="${db.removeAbandoned}"/>
<property name="logAbandoned" value="${db.logAbandoned}"/>
<property name="initialSize" value="${db.initialSize}"/>
<property name="defaultAutoCommit" value="false" />
</bean>
<bean id="transactionAwareDataSource"
class="org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy">
<constructor-arg ref="localDbDataSource"/>
</bean>
<bean class="org.jooq.impl.DataSourceConnectionProvider" name="connectionProvider">
<constructor-arg ref="transactionAwareDataSource"/>
</bean>
<bean id="exceptionTranslator"
class="net.kerba.astra.exception.SpringExceptionTranslator"/>
<bean class="org.jooq.impl.DefaultConfiguration" name="jooqConfig">
<constructor-arg index="0" ref="connectionProvider"/>
<constructor-arg index="1">
<null/>
</constructor-arg>
<constructor-arg index="2">
<null/>
</constructor-arg>
<constructor-arg index="3">
<list>
<bean class="org.jooq.impl.DefaultExecuteListenerProvider">
<constructor-arg index="0" ref="exceptionTranslator"/>
</bean>
</list>
</constructor-arg>
<constructor-arg index="4">
<null/>
</constructor-arg>
<constructor-arg index="5">
<value type="org.jooq.SQLDialect">POSTGRES</value>
</constructor-arg>
<constructor-arg index="6">
<null/>
</constructor-arg>
<constructor-arg index="7">
<null/>
</constructor-arg>
</bean>
<bean id="dsl" class="org.jooq.impl.DefaultDSLContext">
<constructor-arg ref="jooqConfig"/>
</bean>
<bean id="patientDao" class="net.kerba.astra.jooq.tables.daos.PatientDao">
<property name="configuration" ref="jooqConfig" />
</bean>
<bean id="patientStatusDao" class="net.kerba.astra.jooq.tables.daos.PatientStatusRefDao">
<property name="configuration" ref="jooqConfig" />
</bean>
<bean id="requestUrgencyRefDao" class="net.kerba.astra.jooq.tables.daos.RequestUrgencyRefDao">
<property name="configuration" ref="jooqConfig" />
</bean>
<bean id="divisionRefDao" class="net.kerba.astra.jooq.tables.daos.DivisionRefDao">
<property name="configuration" ref="jooqConfig" />
</bean>
<bean id="requestStateRefDao" class="net.kerba.astra.jooq.tables.daos.RequestStateRefDao">
<property name="configuration" ref="jooqConfig" />
</bean>
</beans>
参考控制器：
package net.kerba.astra.controller;
@Controller
@RequestMapping("references")
public class ReferencesController {
private static final Logger logger = LoggerFactory.getLogger(ReferencesController.class);
@Autowired
private ReferenceService referenceService;
@Autowired
private DSLContext dsl;
private static final Map<String,Object> REFERENCES_CONFIG = loadReferencesList();
private static Map<String,Object> loadReferencesList() {
final InputStream resourceAsStream = ReferencesController.class.getResourceAsStream("ReferencesController.data.json");
Objects.requireNonNull(resourceAsStream, "resourceAsStream must not be null");
final InputStreamReader json;
try {
json = new InputStreamReader(resourceAsStream, "utf8");
Objects.requireNonNull(json, "json must not be null");
} catch (UnsupportedEncodingException e) {
throw new RuntimeException("Encoding not supported: utf8", e);
}
final Map map = new Gson().fromJson(json, Map.class);
Objects.requireNonNull(map, "map must not be null");
SortedMap<String,Object> sortedMap = new TreeMap(new Comparator() {
@Override
public int compare(Object o1, Object o2) {
String key1 = (String) o1;
String key2 = (String) o2;
if (key1 != null && key2 != null) {
return key1.compareTo(key2);
} else {
return 0;
}
}
});
sortedMap.putAll(map);
return sortedMap;
}
/**
* Индексная страница справочников
* @param model
* @return
*/
@RequestMapping(value = "", method = RequestMethod.GET)
public String index(Model model) {
model.addAttribute("referencesConfig", REFERENCES_CONFIG);
model.addAttribute("pageTitle", "Справочники");
return "references.index";
}
@RequestMapping(value = "view/{referenceName}", method = RequestMethod.GET)
public String view(Model model,@PathVariable("referenceName") String referenceName) {
model.addAttribute("referencesConfig", REFERENCES_CONFIG);
if (!REFERENCES_CONFIG.containsKey(referenceName)) {
model.addAttribute("pageTitle", "Справочник не найден");
model.addAttribute("message", "Справочник не найден!");
return "error";
} else {
final Map currentRef = (Map) REFERENCES_CONFIG.get(referenceName);
model.addAttribute("pageTitle", "Справочник: «" + currentRef.get("name") + "»");
model.addAttribute("selectedReferenceConfig", currentRef);
model.addAttribute("selectedReferenceConfigKey", referenceName);
SelectQuery selectQuery = dsl.selectQuery();
final String tableName = currentRef.get("tableName").toString();
Objects.requireNonNull(tableName, "tableName must not be null");
selectQuery.addFrom(DSL.tableByName(tableName));
final Result result = selectQuery.fetch();
logger.info("result: {}", result.intoMaps());
model.addAttribute("referenceData", result.intoMaps());
return "references.index";
}
}
}
事务始终与Postgres一起存在。有各种选项可以控制它们，例如显式事务（纯SQL中的BEGIN-END块）与隐式事务，在这些事务中，它们不是声明的，而是隐含的。
还有自动提交，其中事务在每条语句之后自动结束，即如果成功则为COMMIT，如果不成功则为ROLLBACK。然而，这不允许在同一事务下有多个单独的命令，这通常是可取的。
jOOQ根本不管理事务，并遵循现有的方法来管理它们（在您的案例中，SpringTransactionAwareDataSourceProxy）。添加@Transactional为Spring定义事务的属性（您可以执行设置隔离级别等操作）。
因此，如果你在注释到位的情况下得到了预期的行为，我认为这是可以预期的，因为正如我上面提到的，Postgres总是事务性的。如果没有该注释，Spring不会将与Postgres的交互视为事务性的，即使在Postgres