postgresql Jooq自定义删除方法抛出错误：无法在只读事务中执行删除

whhtz7ly  于 2023-11-18  发布在  PostgreSQL
关注(0)|答案(2)|浏览(188)

我使用Springboot 3和Jooq创建我的项目，并定义了一个自定义删除方法deleteByUsername follow jooq delete guide

@Repository
public class UserRepository extends UserDao {

  @Autowired
  public UserRepository(Configuration configuration) {
    super(configuration);
  }

  /* --- some query method can run sucessful in run and test---
   ........
  */

  public void deleteByUsername(String username) {
    ctx().delete(USER).where(USER.USERNAME.eq(username)).execute();
  }
}

字符串
UserRepository是扩展UserDao，所以ctx()返回一个DSLContext示例。

@Repository
public class UserDao extends AbstractSpringDAOImpl<UserRecord, jooq.tables.pojos.User, Long> {

    /**
     * Create a new UserDao without any configuration
     */
    public UserDao() {
        super(User.USER, jooq.tables.pojos.User.class);
    }

    /**
     * Fetch records that have <code>id IN (values)</code>
     */
    public List<jooq.tables.pojos.User> fetchById(Long... values) {
        return fetch(User.USER.ID, values);
    }

    /**
     * Fetch a unique record that has <code>id = value</code>
     */
    public jooq.tables.pojos.User fetchOneById(Long value) {
        return fetchOne(User.USER.ID, value);
    }

    .....more
}

型
UserDao是由jooq代码生成插件生成的，如果你设置了

generate.apply {
   isDeprecated = false
   isRecords = true
   isImmutablePojos = false
   isFluentSetters = true
   isDaos = true // will create dao layer by jooq
   isSpringDao = true
   isSpringAnnotations = true
}

型
All *Dao是对DAOImpl的扩展，ctx（）是在dao层获取DSLContext的方法

public abstract class DAOImpl<R extends UpdatableRecord<R>, P, T> implements DAO<R, P, T> {

    private final Table<R>     table;
    private final Class<P>     type;
    private RecordMapper<R, P> mapper;
    private Configuration      configuration;

    /**
     * Inject a configuration.
     * <p>
     * This method is maintained to be able to configure a <code>DAO</code>
     * using Spring. It is not exposed in the public API.
     */
    public /* non-final */ void setConfiguration(Configuration configuration) {
        this.mapper = ((FieldsImpl<R>) table.recordType()).mapper(this.configuration = Tools.configuration(configuration), type);
    }

    public /* non-final */ DSLContext ctx() {
        return configuration().dsl();
    }

}

型

问题

当我在springboot测试中运行deleteByUsername时，它失败了，并在下面抛出异常。
jooqDeleteRunSuccess中定义的userRepository.delete方法是jooq生成的dao层的方法，不是自定义方法。

@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT)
@AutoConfigureWebTestClient
public class SignE2ETest {

  @Autowired private WebTestClient webClient;

  @Autowired private UserRepository userRepository;

  @Test
  void jooqDeleteRunSuccess() {
    User stubUser = new User();
    stubUser.setUsername("test_5fab32c22a3e");
    stubUser.setPassword("test_eab28b939ba1");
    userRepository.insert(stubUser);
    userRepository.delete(userRepository.fetchOneByUsername("test_5fab32c22a3e"));
  }

  @Test
  void myDeleteRunFailed() {
    User stubUser = new User();
    stubUser.setUsername("test_5fab32c22a3e");
    stubUser.setPassword("test_eab28b939ba1");
    userRepository.insert(stubUser);
    userRepository.deleteByUsername("test_5fab32c22a3e");
  }
}
org.jooq.exception.DataAccessException: SQL [delete from "mjga"."user" where "mjga"."user"."username" = ?]; ERROR: cannot execute DELETE in a read-only transaction
 at org.jooq_3.18.6.POSTGRES.debug(Unknown Source)
 at org.jooq.impl.Tools.translate(Tools.java:3470)
 at org.jooq.impl.Tools.translate(Tools.java:3458)
 at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:801)
 at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:360)
 at org.jooq.impl.AbstractDelegatingQuery.execute(AbstractDelegatingQuery.java:115)
 at com.mjga.repository.UserRepository.deleteByUsername(UserRepository.java:61)
 at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
 at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
 at java.base/java.lang.reflect.Method.invoke(Method.java:568)
.....

提问

正如你所看到的，没有明确的配置来设置postgresql到只读模式，我不知道为什么这个只读异常发生？
为什么myDeleteRunFailed抛出READ-ONLY异常，而jooqDeleteRunSuccess没有？
怎么修？

环境

  • 简体中文（zh_cn）
  • 下载jooq-3.18.6
  • Postgresql-15.4
dependencies {
    implementation("org.springframework.boot:spring-boot-starter-actuator")
    implementation("org.springframework.boot:spring-boot-starter-jooq")
    implementation("org.springframework.boot:spring-boot-starter-mail")
    implementation("org.springframework.boot:spring-boot-starter-quartz")
    implementation("org.springframework.boot:spring-boot-starter-security")
    implementation("org.springframework.boot:spring-boot-starter-validation")
    implementation("org.springframework.boot:spring-boot-starter-web")
    implementation("org.springframework.boot:spring-boot-starter-websocket")
    implementation("org.springframework.boot:spring-boot-starter-aop")
    implementation("org.apache.commons:commons-lang3:3.13.0")
    implementation("org.apache.commons:commons-collections4:4.4")
    implementation("org.springdoc:springdoc-openapi-starter-webmvc-ui:2.2.0")
    implementation("org.jooq:jooq-codegen:3.18.6")
    implementation("org.jooq:jooq-meta:3.18.6")
    implementation("com.auth0:java-jwt:4.4.0")
    implementation("org.testcontainers:junit-jupiter:1.19.0")
    implementation("org.testcontainers:postgresql:1.19.0")
    implementation("org.testcontainers:testcontainers-bom:1.19.0")
    runtimeOnly("org.postgresql:postgresql")
    compileOnly("org.projectlombok:lombok")
    developmentOnly("org.springframework.boot:spring-boot-devtools")
    developmentOnly("org.springframework.boot:spring-boot-docker-compose")
    testImplementation("org.springframework.boot:spring-boot-testcontainers:3.1.2")
    testImplementation("org.springframework.boot:spring-boot-docker-compose")
    testImplementation("org.springframework.boot:spring-boot-starter-webflux")
    testImplementation("org.springframework.boot:spring-boot-starter-test")
    testImplementation("org.springframework.security:spring-security-test")
    jooqGenerator("org.postgresql:postgresql")
    annotationProcessor("org.springframework.boot:spring-boot-configuration-processor")
    annotationProcessor("org.projectlombok:lombok")
}
services:
  postgres:
    container_name: postgres
    image: 'postgres:15.4'
    environment:
      POSTGRES_DB: postgres
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
    ports:
      - ${DATABASE_PORT}:5432
    volumes:
      - ./db.d/init:/docker-entrypoint-initdb.d
      - ${DATABASE_STORE}:/var/lib/postgresql/data
    restart: on-failure

我已检查我的数据库只读状态

SHOW default_transaction_read_only;
SHOW transaction_read_only;

off
off

型

postgresql

2条答案

按热度按时间
euoag5mw

euoag5mw1#

错误“无法在只读事务中执行MySQL”表示您正试图在只读事务中执行MySQL操作，该事务不允许修改数据库。
所以我猜jooq方法“jooqDeleteRunSuccess”在执行MySQL操作之前启动了一个可写事务
但是您的个人方法“myDeleteRunFailed”只获取一个DSLContext示例，而不发起一个可写事务
您可以在方法之前使用此代码，然后重试

DSLContext dslContext = DSL.using(connection, SQLDialect.MYSQL);

字符串

赞(0）分享  回复(0）举报  2023-11-18
z9ju0rcb

z9ju0rcb2#

正如Frisk和Lukas Eder所提到的，这个问题是由于Sping Boot 的一些设置使事务行为默认为“只读”。
在Spring中使用JOOQ时，DAO层扩展了AbstractSpringDAOImpl，它在class级别上有@Transactional(readOnly = true)注解。这导致UserRepository对其所有方法都被视为只读。
为了解决这个问题，我在方法级别添加了@Transactional注解来覆盖类级别的注解，如下所示：

@Transactional
public void deleteByUsername(String username) {
    ctx().delete(USER).where(USER.USERNAME.eq(username)).execute();
}

字符串

赞(0）分享  回复(0）举报  2023-11-18
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com