gpt4 book ai didi

java - jOOQ + Spring : PSQLException: current transaction is aborted, 命令被忽略直到事务结束

转载 作者:行者123 更新时间:2023-11-29 12:05:15 24 4
gpt4 key购买 nike

我正在开发一个使用 Spring 和 jOOQ 的网络应用。

考虑以下用例:

  1. 我打开呈现数据库查询结果的 URL:select * from contract_ref,一切正常。

  2. 我打开 URL,它试图运行触发 Postgres 错误的查询,即 select * from users_ref(表 users_ref 不存在),并得到错误:

    Servlet.service() for servlet [dispatcher] in context with path [/astra] throw exception [请求处理失败;嵌套异常是 org.springframework.jdbc.BadSqlGrammarException: jOOQ;错误的 SQL 语法 [select * from "users_ref"];嵌套异常是 org.postgresql.util.PSQLException: ОШИБКА: отношение "users_ref"не существует

  3. 当我尝试从第 1 步打开页面时,出现错误 PSQLException: current transaction is aborted, commands ignored until end of transaction block,但我看到 webapp 尝试执行第 1 步的声明。

在我看来,情况就像 Postgres 没有关闭事务,但我只做了 select,所以不需要任何事务。

当我为 ReferenceController.view 添加 @Transactional 注释时,上述行为就会消失。

完整日志:http://pastebin.com/t3UmbeCy

应用程序上下文.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>

引用 Controller :

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 block )与隐式交易,其中未声明但隐含交易。

还有 autocommit,它会在每个语句之后为您自动结束事务——即如果成功则为 COMMIT,否则为 ROLLBACK。这不允许在同一事务下执行多个单独的命令,但这通常是可取的。

jOOQ 根本不管理事务,并遵从现有的方法来管理它们(Spring TransactionAwareDataSourceProxy 在您的例子中)。添加 @TransactionalSpring 定义事务的属性(您可以执行诸如设置隔离级别等操作)。 p>

因此,如果您使用该注释获得预期的行为,我认为这很好并且可以预料,因为正如我上面提到的,Postgres 始终是事务性的。如果没有该注释,Spring 不会将与 Postgres 的交互视为事务性的,即使在 Postgres 级别它是事务性的,因此您会得到诸如尝试的行为对中止的事务进行额外的查询,从而导致异常。

关于java - jOOQ + Spring : PSQLException: current transaction is aborted, 命令被忽略直到事务结束,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/25951491/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com