How to View the SQL Queries Issued by JPA

How can I log SQL statements in Spring Boot?

Try using this in your properties file:

logging.level.org.hibernate.SQL=DEBUG
logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE

Can I get the SQL string from a JPA Query object?

If you only want to know how your JPQL or Criteria Query gets translated to the SQL dialect of your database you can enable fine grained logging in the persistence xml and then look into your log files.

The property name and value depends on your JPA implementation. Here is an example of the relevant part of persistence.xml for EclipseLink:

<properties>
    <property name="eclipselink.logging.level" value="FINEST"/>
</properties>

How to print a query string with parameter values when using Hibernate

You need to enable logging for the the following categories:

• org.hibernate.SQL   - set to debug to log all SQL DML statements as they are executed
• org.hibernate.type - set to trace to log all JDBC parameters

So a log4j configuration could look like:

# logs the SQL statements
log4j.logger.org.hibernate.SQL=debug 

# Logs the JDBC parameters passed to a query
log4j.logger.org.hibernate.type=trace 

The first is equivalent to hibernate.show_sql=true legacy property, the second prints the bound parameters among other things.

Another solution (non hibernate based) would be to use a JDBC proxy driver like P6Spy.

How to setup JPA bi-directional one to one while avoiding n+1 queries

You can optimize this with EntityGraph and lazy loading. I presume VehicleEntity is the owning entity here. In that case make fetch = FetchType.LAZY and cascade = CascadeType.ALL should be on the owning entity. And EntityGraph can be defined 2 ways

1. Named Entity Graph
2. AD-HOC or Unnamed Entity Graph

Named Entity Graph:

you can define NamedEntityGraph on owning class and reference it in VehicleEntityRepository interface

@NamedEntityGraph(name = "VehicleEntity.trackerEntity", attributeNodes = @NamedAttributeNode("trackerEntity"))
public class VehicleEntity {
}

VehicleEntityRepository.java

public interface VehicleEntityRepository extends JpaRepository<VehicleEntity, UUID> {
     @EntityGraph(value = "VehicleEntity.trackerEntity", type = EntityGraphType.LOAD)
    List<VehicleEntity> findAll();
}

AD-HOC Entity Graph:

You don't have to define any EntityGraph annotation VehicleEntity class but can directly use it Repository interface

VehicleEntityRepository.java

public interface VehicleEntityRepository extends JpaRepository<VehicleEntity, UUID> {
    @EntityGraph(attributePaths = { "trackerEntity" })
    List<VehicleEntity> findAll();
}

VehicleEntity.java

@AllArgsConstructor(access = AccessLevel.PRIVATE)
@NoArgsConstructor
@EqualsAndHashCode
@Getter
@Builder

@Entity
@Table(name = "vehicle")
@NamedEntityGraph(name = "VehicleEntity.trackerEntity", attributeNodes = @NamedAttributeNode("trackerEntity"))
public class VehicleEntity {
    @Id
    private UUID id;

    @Column(name = "vehiclename")
    private String vehicleName;

 
@OneToOne(fetch = FetchType.LAZY, cascade = CascadeType.ALL, orphanRemoval = true, mappedBy = "vehicleEntity")
    @JsonManagedReference
    private TrackerEntity trackerEntity;
}

TrackerEntity.java

package ...;
import ...;

@AllArgsConstructor(access = AccessLevel.PRIVATE)
@NoArgsConstructor
@EqualsAndHashCode
@Getter
@Builder

@Entity
@Table(name = "tracker")
public class TrackerEntity {
    @Id
    @Column(name = "id")
    private UUID id;

    @Column(name = "trackerid")
    private String trackerId;

    @OneToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "vehicleid", referencedColumnName = "id")
@JsonBackReference
    private VehicleEntity vehicleEntity;
}
    

You can also use @JsonManagedReference and @JsonBackReference to prevent nested fetch or infinite recursion when using Jackson.

---

Related Topics