Spring-JPA碰到的一些问题记录

freekaiQaQ

已于 2022-09-26 11:24:05 修改

阅读量912

点赞数

分类专栏： spring-jpa 文章标签： jpa

于 2020-12-01 16:57:13 首次发布

本文链接：https://blog.csdn.net/freekaiQaQ/article/details/110439843

版权

spring-jpa 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

1、xxRepository.findAllByNameAndAge(null, null)

形如这种的利用jpa的原生写法的sql，当入参是null值时，会匹配到相关字段是null的记录。使用上应留意。项目中碰到了根据入参查询到了一些本不应该查询到的数据。

2、批量删除引起的stackOverFlow

3、实体类增加字段后，项目本应执行sql，xxx column的

实体类增加字段后，项目启动会执行sql语句。类似于下方：

alter table spare-parts.borrow_apply add column grade integer

报错信息：

Hibernate: 
    
    alter table spare-parts.borrow_apply 
       add column grade integer
2021-08-04 13:40:43 [restartedMain] WARN  m o.h.t.schema.internal.ExceptionHandlerLoggedImpl - GenerationTarget encountered exception accepting command : Error executing DDL "
    alter table spare-parts.borrow_apply 
       add column grade integer" via JDBC Statement
org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL "
    alter table spare-parts.borrow_apply 
       add column grade integer" via JDBC Statement
	at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:67)
	at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.applySqlString(AbstractSchemaMigrator.java:559)
	at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.applySqlStrings(AbstractSchemaMigrator.java:580)
	at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.migrateTable(AbstractSchemaMigrator.java:297)
	at org.hibernate.tool.schema.internal.GroupedSchemaMigratorImpl.performTablesMigration(GroupedSchemaMigratorImpl.java:75)
	at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.performMigration(AbstractSchemaMigrator.java:207)
	at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.doMigration(AbstractSchemaMigrator.java:114)
	at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:183)
	at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:72)
	at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:310)
	at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:467)
	at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:939)
	at org.springframework.orm.jpa.vendor.SpringHibernateJpaPersistenceProvider.createContainerEntityManagerFactory(SpringHibernateJpaPersistenceProvider.java:57)
	at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.createNativeEntityManagerFactory(LocalContainerEntityManagerFactoryBean.java:365)
	at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:390)
	at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.afterPropertiesSet(AbstractEntityManagerFactoryBean.java:377)
	at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.afterPropertiesSet(LocalContainerEntityManagerFactoryBean.java:341)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1837)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1774)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:593)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:515)
	at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:320)
	at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222)
	at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:318)
	at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:199)
	at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1105)
	at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:867)
	at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:549)
	at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:142)
	at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:775)
	at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:397)
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:316)
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:1260)
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:1248)
	at com.ajmd.spare.SparePartsManageApplication.main(SparePartsManageApplication.java:25)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.springframework.boot.devtools.restart.RestartLauncher.run(RestartLauncher.java:49)
Caused by: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-parts.borrow_apply 
       add column grade integer' at line 1
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
	at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:764)
	at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:648)
	at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:95)
	at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java)
	at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:54)
	... 39 common frames omitted

spare-parts是数据库名称，因为其中包含中横线，导致执行sql报错。

后来修改库名，问题解决。

4.Jpa @OneToOne 配置了fetch=FetchType.LAZY ，但是不生效(查询时，还是会有级联查询的sql，导致列表查询加载很慢)

示例代码如下：

@Entity
@Table(name = "ledger_detail")
@DynamicUpdate
public class LedgerDetail{
    
    // 其他字段省略..

    @OneToOne(fetch = FetchType.LAZY, mappedBy = "ledgerDetail")
    private HospitalEquipment equipment;

}


@Entity
@Table(name = "hospital_equipment")
@DynamicUpdate
public class HospitalEquipment{
    // 其他字段省略..

    // 这里是相当于在 hospital_equipment 配置了ledger_detail的主键当做外键
    @OneToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "ledger_id", foreignKey = @ForeignKey(name = "fk_equ_ledger"))
    private LedgerDetail ledgerDetail;

}

LedgerDetail、HospitalEquipment 其中他俩是一对一的关系
描述：当查询LedgerDetail时，虽然在LedgerDetail类中配置了fetch=FetchType.LAZY，但是控制台打印的sql中，紧接着会打印一条查询 HospitalEquipment的sql -->可发现延迟加载是失效的；反之去查询HospitalEquipment时，不会发出去查询LedgerDetail的sql。

原因：在LederDetail中没有维护HospitalEquiment的主键作为外键，无法定位到具体的一条HospitalEquipment, 是需要select * from HospitalEquipment t where t.ledfer_id=? （这里的?是LedgerDetail表的主键）

参考：

hibernate注解（三）_wangpeng047的博客-CSDN博客我们继续介绍hibernate注解的相关内容1. OneToOne懒加载问题一对一注解时，若采用外键列进行实体的关联的话，懒加载问题是需要注意下的。如下：Student表：id int not nullname varchar(50) not nullcard_id int not nullCard表：id int not nullcard_no vhttps://blog.csdn.net/wangpeng047/article/details/8992477 Hibernate @OneToOne懒加载实现解决方案_wangpeng047的博客-CSDN博客_onetoone 懒加载在hibernate注解（三）中，我提高过一对一（@OneToOne）懒加载失效的问题。虽然给出了解决方法，但并没有给出完整的解决方案。今天我专门针对该问题进行讨论。至于懒加载失效的原因，在之前的文章中已经我已经叙述过了，就不再重复了，不明白的可以去看看。https://blog.csdn.net/wangpeng047/article/details/19624795

解决方案：

1>可参考上方中的链接中提供的几种解决方案
2>这里从实际业务出发，编写原生sql，通过entityManager进行查询，然后封装成对应的对象，来实现业务。示例代码如下：

 StringBuilder sql = new StringBuilder();
        String sql = " select t.a, t.b from ledger_detail t where ... ";
        final Query nativeQuery = entityManager.createNativeQuery(sql.toString());
        final List<Object[]> resultList = nativeQuery.getResultList();
        // 转化为leaderDetail
        List<LedgerDetail> details = new ArrayList<>();
        for (Object[] objects : resultList) {
            int i = 0;
            String leaderId = objects[0]==null?"":objects[0].toString();
            // 具体逻辑根据实际情况处理
        }

5.多数据源配置下 jpa Specification 复杂查询时，报找不到xxx字段

    @Bean("entityManagerPrimary")
    @Primary
    LocalContainerEntityManagerFactoryBean entityManagerPrimary(EntityManagerFactoryBuilder builder){

        // 解决某些实体类上没有加@Column注解，导致用jpa Specification 复杂查询时，报找不到xxx字段
        Map<String, String> properties = jr.getProperties();
        //要设置这个属性，实现 CamelCase -> UnderScore 的转换
        properties.put("hibernate.physical_naming_strategy",
                "org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy");

        return builder.dataSource(haisDb)
                .properties(jr.getProperties())
                .packages("com.ajmd.hais.common.entity","com.ajmd.hais.common.module", "com.ajmd.hais.common.entity.audit")
                .properties(properties)
                .persistenceUnit("haisPersistenceUnit")
                .build();

    }