- Introduction to JPQL (JPA)
- Write UPDATE and DELETE statements
- Do a LIKE search
- JPQL has many limitations.
- Use the in clause
- entity
- Primary Key
- Error occurs if the Select clause is not associated with an entity
- When constructor expressions are not used
- @EmbeddedId
- Note in compound primary key of @Embeddable annotation
- Specify the composite primary key as the argument of findById
- Generate JPQL from method names
- Using @EmbeddedId complicates naming conventions.
- How to use native functions
- Sort class in org.springframework.data.domain.
- Fetching large amounts of data using @QueryHints
- @EntityListeners(AuditingEntityListener.class) to set registrant, registration time, updater and update time
- Configure on DB side
- Describe JPQL in properties using the jpa-named-queries.properties file
- Use the deleteInBatch method rather than the delete method
- Use the deleteAllInBatch method rather than the deleteAll method
- jpql performance
- Paging process using org.springframework.data.domain.Page class
- Union,Union All not supported
- No identifier specified for entity
- Not supported for DML operations
- column “xxx” is of type timestamp without time zone but expression is of type bytea
- Related
Introduction to JPQL (JPA)
SQL to retrieve data from a DB (or more precisely, an entity) in JPA is called JPQL.
In SQL, what was written in the from clause is the table name, but in JPQL, the entity is written in the from clause. (This is important.)
JPQL is written with @Query annotation, but the syntax is a little different from the usual SQL syntax.
The following is a simple JPQL example.
@Query("select m from EmpMaster where m.empId = :id")
@Query(value = "select m from EmpMaster wher m.empId = :id")
In the above, m is the alias; select m instead of select *. value = is optional.
In JPQL, it is important to note that the table name should be the name of the entity class, and the column name should be the field name of the entity class.
Even if the actual table name is emp_master, if the entity class corresponding to the table is the EmpMaster class, the JPQL statement should be written as EmpMaster.
Write UPDATE and DELETE statements
To write UPDATE and DELETE statements in JPQL statements, there are some more annotations.
| annotation | meaning |
|---|---|
| @Transactional | Rollback if class or method terminates abnormally |
| @Modifying | attached to update, insert, and delete statements |
A coding example is shown below.
@Modifying
@Query("update ～")
Do a LIKE search
JPQL allows LIKE search. When you add % to a bind variable, for example, use the following description.
@Query(value = "select m EmpMaster from m.empId like :id%")
List<EmpMaster> findBySample(@Param("id") Long aaa)
JPQL has many limitations.
JPQL has many restrictions, so the only way to do this is to write SQL statements.
When writing SQL, set nativeQuery=true. Here is an example
@Query(nativeQuery=true, value="select ～")
Incidentally, interval, DAY, MONTH, YEAR, etc. used in MySQL date calculations cannot be used in JPQL, so you need to set nativeQuery=true.
You can use the FUNCTION expression described below to avoid this problem.
Use the in clause
If the condition is only an in clause, the findByXXIn method can be used, but if nativeQuery=true and an in clause is present, a List is passed as a parameter.
@Repository
public interface HogeRepository extends JpaRepository<HogeEntity, Integer> {
@Query(value = "select * from hoge_tbl "
+ " where id in :id ", nativeQuery = true)
List<HogeEntity> findById(@Param("id") List<Integer> id);
}
entity
Create an entity class, but there are some rules.
- Grant @Entity annotation
- Assign @Id to any of the fields
- Implement a constructor with no arguments
The @Table annotation is not mandatory, and the @Table annotation is used to set the table name when table name ≠ entity name.
@Column is also not required, and @Column is granted when column name ≠ field name.
Primary Key
Add the @Id annotation to the primary key of the entity.
If the id field is the primary key, the entity class specifies @Id as follows
@Table(name="auto_increment")
@Entity
@Getter
@Setter
public class AutoIncrementtEntity {
@Id
@GeneratedValue
private Integer id;
@Column(name="name")
private String name;
}
If the primary key is a surrogate key, add the @GeneratedValue annotation.
However, MySQL raises the error java.sql.SQLSyntaxErrorException: Table ‘schema.hibernate_sequence’ doesn’t exist.
これを回避するには@GeneratedValueアノテーションのstrategy属性にGenerationType.IDENTITYを指定します. (This is a case of MySQL’s AUTO_INCREMENT.)
If @GeneratedValue’s strategy attribute is unspecified, it will be GenerationType.AUTO.
| strategy value | meaning |
|---|---|
| GenerationType.AUTO | Default automatic generation method (not for MySQL) |
| GenerationType.IDENTITY | MYSQL |
| GenerationType.SEQUENCE | ORACLE or PostgreSQL |
| TABLE | ORACLE |
@Table(name="auto_increment")
@Entity
@Getter
@Setter
public class AutoIncrementtEntity {
@Id
@GeneratedValue(GenerationType.IDENTITY)
private Integer id;
@Column(name="name")
private String name;
}
If you are using PostgreSQL and using a Sequence object, you must specify the Sequence object name in the @SequenceGenerator sequenceName attribute.
/** ID. */ @Id @GeneratedValue(strategy = GenerationType.SEQUENCE,generator = "id_seq") @SequenceGenerator(name = "id_seq",sequenceName = "id_seq",allocationSize = 1) @Column(name = "id") private Integer id;
Error occurs if the Select clause is not associated with an entity
If JPQL is easy to use, it is fine, but when using a group that requires aggregation by year and month, for example, it can be complicated in many ways.
Entity Classes
@Table
@Entity
@Getter
@Setter
public class Sample {
@Id
@Column("date")
private LocalDate date;
}
If there is such an entity, JPQL will generate an error if the year and month are entered in the Select clause instead of the year and month.
@Query(value = "select data_format(date, '%Y-%m') from Sample")
This is an error because the value is formatted as a year and month for the LocalDate.
The workaround is to create a new class and insert it as a string in the constructor when creating the instance.
Note that nativeQuery cannot be used when using select new (constructor expression).
@Query(value = "select NEW jp.co.confrage.xxx.domain.entity.SampleEntity"
+ " ( "
+ " date AS DT"
+ " ) "
+ " from Sample ")
SampleEntity is just a class like this.
@AllArgsConstructor
@Data
public class SampleEntity {
private String DT;
}
This is OK. If you want to include @Param arguments (named parameters) in the constructor expression, you need to CAST them.
Note that even if @Param is a String type, you need to CAST it to a String type with CAST. (Confirmed by MySQL)
@Query(value = "SELECT NEW jp.co.confrage.xxx.domain.entity.SampleEntity"
+ " ( "
+ " CAST(:name as java.lang.String)" // こんな感じ
+ " ) "
+ " from Sample ")
List<SampleEntity> findByXXXX(@Param("name") String name); // この引数をコンストラクタ式に入れる場合
The name is a String type, but must be CAST when used as an argument to a constructor expression.
It must be fully qualified.
When constructor expressions are not used
It is not always necessary to use constructor expressions. For example, the following methods can be written
@Query(value="select m.aaa.accountNumber,m.branchNumber from AccountEntity m where m.aaa.id = 1") List<Object[]> findXXX();
Just make it a List<Object[]>.
We can create the following method in a class such as DTO.
import java.math.BigDecimal;
import java.time.LocalDateTime;
import lombok.AllArgsConstructor;
import lombok.Data;
@AllArgsConstructor
@Data
public class HogeResource {
private Integer id;
private BigDecimal version;
private LocalDateTime date;
/**
* Let's create a method like this. Generate resource class.
*/
public static HogeResource toResource(Object[] obj) {
return new HogeResource(
(Integer)obj[0],
(BigDecimal)obj[1],
(LocalDateTime)obj[2]);
}
}
The method caller side of the repository can be neatly coded using stream as follows
List<SampleDto> obj = accountRepository.findXXX()
.stream()
.map(SampleDto::toResource)
.collect(Collectors.toList());
This allows you to write without using a constructor expression.
@EmbeddedId
If there is only one primary key, @Id is sufficient, but for compound primary keys, @IdClass or @EmbeddedId should be used.
There are cases where @IdClass is used, but it tends to be avoided because it is redundant.
Entities are defined as follows
package jp.co.confrage;
import java.io.Serializable;
import javax.persistence.Column;
import javax.persistence.Embeddable;
import javax.persistence.EmbeddedId;
import javax.persistence.Entity;
import javax.persistence.Table;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@Entity
@Table(name="employee_master")
public class Employee {
@EmbeddedId
private PrimaryK id; // Class of composite primary key
@Column(name="empname")
private String empname;
@Embeddable
@Data
@AllArgsConstructor
@NoArgsConstructor
public static class PrimaryK implements Serializable { // Define PrimaryK class as static
private static final long serialVersionUID = -2523459362991270288L;
@Column(name="id")
private String id; // pk
@Column(name="empno")
private String empno; // pk
}
}
If it does not implements Serializable, an error will occur. Therefore, the @Embeddable annotation is added to this compound primary key. The repository class is defined as follows
package jp.co.confrage;
import java.util.List;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
import jp.co.confrage.Employee.PrimaryK;
@Repository
public interface EmployeeRepository extends JpaRepository<Employee, PrimaryK> { // ここ注意
@Query(value="select m.id.empno from Employee m ")
public List<Integer> findByPk();
}
For composite primary keys, you have to be careful how you write @Query, and if you use @Embedded, the JPQL will be like m.id.empno.
Note in compound primary key of @Embeddable annotation
It is convenient, but an error occurred when this key was NEW as a constructor expression, so use @IdClass if you want to NEW it as a constructor expression.
Specify the composite primary key as the argument of findById
Let’s specify a composite primary key as an argument to the findById method that JpaRepository provides by default. We will use the entity we wrote earlier.
DI the repository from the service class and call it.
EmployeeRepository repository;
empRepository.findById(new PrimaryK("1","1")); // New compound primary key like this
Generate JPQL from method names
There are certain rules for method names, and JPQL can be generated from those names. Suppose we have an entity named Employee, and the repository is as follows
public Employee findByName(String name);
If you create a method called findByName, it will look like this
from Employee where name = :name
If there are two conditions, findByNameAndEmpno.
public Employee findByNameAndEmpno(String name,String empno);
This method implies the following
from Employee where name = :name and empno = :empno;
The following have special meaning when specified in the method name
| 単語 | メソッド名 | JPQL |
|---|---|---|
| By | findByXX | where XX = :XX |
| And | findByXXAndYY | where XX = :XX and YY = :YY |
| Or | findByOr | where XX = :XX or YY = :YY |
| Like | findByXXLike | where XX like :XX |
| NotLike | findByXXNotLike | where XX not like :XX |
| Containing | findByXXContaining | where XX like ‘%:XX%’ |
| IsNull | findByXXIsNull | where XX is null |
| IsNotNull | findByXXIsNotNull | where XX is not null |
| NotNull | findByXX | where XX is not null |
| Between | findByXXBetween | where XX between :XX1 and :XX2 |
| LessThan | findByXXLessThan | where XX < :XX(数値) |
| GreaterThan | findByXXGreaterThan | where XX > :XX(数値) |
| After | findByXXAfter | where XX > :XX(日時) |
| Before | findByXXBefore | where XX < :XX(日時) |
| OrderBy | findByXXOrderByYYAsc | where XX = :XX order by YY asc |
| Not | findByXXNot | where XX <> :XX |
| In | findByXXIn | where XX in (?,?…) |
| NotIn | findByXXNotIn | where XX not in (?,?…) |
| True | findByXXTrue | where XX = true |
| False | findByXXFalse | where XX = false |
| StartingWith | findByXXStartingWith | where XX like ‘:XX%’ |
| EndingWith | findByXXEndingWith | where XX like ‘%:XX’ |
Using @EmbeddedId complicates naming conventions.
If you are using @EmbeddedId (compound primary key), the method name is a bit tricky.
If you want to use PK in an entity with a compound primary key like the following, you will have to add a field name with @EmbeddedId to the method name.
package jp.co.confrage;
import java.io.Serializable;
import javax.persistence.Column;
import javax.persistence.Embeddable;
import javax.persistence.EmbeddedId;
import javax.persistence.Entity;
import javax.persistence.Table;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@Entity
@Table(name="employee_master")
public class Employee {
@EmbeddedId
private PrimaryK id;
@Column(name="empname")
private String empname;
@Embeddable
@Data
@AllArgsConstructor
@NoArgsConstructor
public static class PrimaryK implements Serializable{
private static final long serialVersionUID = -2523459362991270288L;
@Column(name="id")
private String id;
@Column(name="empno")
private String empno;
}
}
For example, if you want to search by empno
public Employee findByEmpno(String empno); // error public Employee findByIdEmpno(String empno); // correct
How to use native functions
If you want to use a function that is not provided in JPQL or depends on each RDB in JPQL, do the following
SELECT FUNCTION('function name', Argument 1, Argument 2…) ～
Sort class in org.springframework.data.domain.
It is possible to sort by passing the class org.springframework.data.domain.Sort without sorting by order by in the query statement. There is an enum named org.springframework.data.domain.sort.Direction, so ascending order for ASC and descending order for DESC. The second argument is the field name of the entity.
// caller List<AccountEntity> list = accountRepository.findList(new Sort(Direction.ASC, "depositAmount"));
Below is the repository class, passing an instance of the Sort class without sorting by JPQL.
@Query(value="select m from AccountEntity m") List<AccountEntity> findList(Sort sort);
Fetching large amounts of data using @QueryHints
The @QueryHints annotation can be used to adjust the fetch size.
The fetch size is specified as a string in the value attribute of @QueryHint.
For name, specify org.hibernate.jpa.QueryHints.HINT_FETCH_SIZE.
The following is an example of setting the fetch size to 1000.
@QueryHints(value=@QueryHint(name = org.hibernate.jpa.QueryHints.HINT_FETCH_SIZE, value="1000")) @Query(value = "select m.empname from UserEntity m ") public List<String> findByPk();
@EntityListeners(AuditingEntityListener.class) to set registrant, registration time, updater and update time
By annotating entities with the @EntityListeners(AuditingEntityListener.class) annotation, you can set the registrant, updater, registration time, and update time.
Annotate the property you wish to set.
| annotation | meaning | class |
|---|---|---|
| @Version | Version | Integer and so on |
| @CreatedBy | Registrant | String |
| @CreatedDate | Registration Date | LocalDateTime |
| @LastModifiedBy | Updated By | String| |
| @LastModifiedDate | ModifiedDate | LocalDateTime |
The above annotations are assigned to each property. The following is an example of an entity.
import java.io.Serializable;
import java.time.LocalDateTime;
import javax.persistence.Column;
import javax.persistence.Embeddable;
import javax.persistence.EmbeddedId;
import javax.persistence.Entity;
import javax.persistence.EntityListeners;
import javax.persistence.Table;
import org.springframework.data.annotation.CreatedBy;
import org.springframework.data.annotation.CreatedDate;
import org.springframework.data.annotation.LastModifiedBy;
import org.springframework.data.annotation.LastModifiedDate;
import org.springframework.data.jpa.domain.support.AuditingEntityListener;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Table(name="account")
@Entity
@AllArgsConstructor
@NoArgsConstructor
@Data
@EntityListeners(AuditingEntityListener.class)
public class AccountEntity {
@EmbeddedId
private Pk aaa;
@Column(name="branch_number")
private Integer branchNumber;
@Column(name="deposit_amount")
private Integer depositAmount;
@CreatedBy
@Column(name="register_user")
private String registerUser;
@CreatedDate
@Column(name="register_date")
private LocalDateTime registerDate;
@LastModifiedBy
@Column(name="update_user")
private String updateUser;
@LastModifiedDate
@Column(name="update_date")
private LocalDateTime updateDate;
@Embeddable
@Data
@AllArgsConstructor
@NoArgsConstructor
public static class Pk implements Serializable {
private static final long serialVersionUID = 624797775027966843L;
@Column(name="id")
private String id;
@Column(name="account_number")
private String accountNumber;
}
}
This alone will not register any audit information when inserted in the save method.
We need to create a configuration class org.springframework.data.domain.AuditorAware interface and create the following class.
Here, we use testuser as a sticky note.
import java.util.Optional;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.domain.AuditorAware;
import org.springframework.data.jpa.repository.config.EnableJpaAuditing;
@Configuration
@EnableJpaAuditing
public class AuditConfig {
@Bean
public AuditorAware<String> auditorAware() {
return new AuditorAware<String>() {
@Override
public Optional<String> getCurrentAuditor() {
return Optional.of("testuser");
}
};
}
}
Now when you save, testuser will be automatically set as the registrant or updater, and the time will be automatically set as the registration time or update time.
If nativeQuery=true, they will not be set automatically.
Since audit information is information that any table has, it is better to create a parent class and write it there.
Here, we give the parent class the @MappedSuperclass annotation and extend it with each entity.
Here is an example of the parent class AbstractEntity class.
package jp.co.confrage.domain.entity;
import java.time.LocalDateTime;
import javax.persistence.Column;
import javax.persistence.Convert;
import javax.persistence.EntityListeners;
import javax.persistence.MappedSuperclass;
import javax.persistence.Version;
import org.springframework.data.annotation.CreatedBy;
import org.springframework.data.annotation.CreatedDate;
import org.springframework.data.annotation.LastModifiedBy;
import org.springframework.data.annotation.LastModifiedDate;
import org.springframework.data.jpa.domain.support.AuditingEntityListener;
@MappedSuperclass
@EntityListeners(AuditingEntityListener.class)
public abstract class AbstractEntity {
/** version. */
@Version
@Column(name = "version")
private Integer version;
/** author. */
@CreatedBy
@Column(name = "creator")
private String creatorCode;
/** creation-time. */
@CreatedDate
@Column(name = "create_time")
private LocalDateTime createdTim;
/** updater. */
@LastModifiedBy
@Column(name = "updater")
private String updaterCode;
/** Update Date. */
@LastModifiedDate
@Column(name = "update_time")
private LocalDateTime updatedTim;
}
Configure on DB side
In the case of MySQL, it is also possible to set this up automatically in the table.
How to automatically set registered and modified dates for MySQL tables
Describe JPQL in properties using the jpa-named-queries.properties file
Queries can be described in the properties file.
By default, the queries are described in META-INF/jpa-named-queries.properties.
The JPQL that describes the repository interface @Query(name=Sample.find) and maps to the value specified in this name attribute is described in META-INF/jpa-named-queries.properties.
jpa-named-queries.properties
Sample.find=\ SELECT m FROM Employee
Now you can write in META-INF/jpa-named-queries.properties.
Use the deleteInBatch method rather than the delete method
Spring Data JPA provides a delete method in advance, but when deleting multiple rows, a delete statement is issued for each row.
Therefore, it is better to use the deleteInBatch method for bulk delete.
Use the deleteAllInBatch method rather than the deleteAll method
The deleteAll method is also a pre-prepared method, but it deletes all records, but it only executes the delete statement for the number of records.
Therefore, it requires a lot of communication.
If you want to use the deleteAll method, there is a deleteAllInBatch method that allows bulk deletion.
jpql performance
There are various perspectives when it comes to performance, such as speed and memory consumption, but jpql consumes significantly less memory with nativeQuery=true.
With the default (nativeQuery=false), a Java instance is created, so if a large amount of data is retrieved, memory will be consumed all at once.
In the case of too much data, OOME will occur, so nativeQuery=true is recommended.
Here is a speed test. Reference Site
Paging process using org.springframework.data.domain.Page class
When retrieving large amounts of data (such as findAll), it is more convenient to use the Page class for paging, which limits the number of items displayed and allows page-by-page processing.
From a performance standpoint, memory usage is also considerably reduced.
| argument | meaning |
|---|---|
| first | argument|0 as base value|pages |
| secondargument | data units per page |
Page<"ENTITTY"> paging = "REPOSITORY".findAll(PageRequest.of(0, 3));
The PageRequest class is instantiated using the of method, not new.
It seems that instantiation with new is deprecated.
Instances of the Page<entity> class have the following methods
| return value | method | meaning |
|---|---|---|
| boolean | hasContent | Whether content is available or not |
| List | getContent | Content per page |
| int | getNumber | Current page number |
| int | getSize | Size per page |
| int | getNumberOfElements | Number of items currently retrieved by content |
| int | getTotalElements | Number of all records regardless of page |
| int | getTotalPages | Total number of pages |
If you want to retrieve pages incrementally, you can write the following.
int i = 0; // Page Base Value
int size = 5; // Number of data per page
Page<AccountEntity> paging = accountRepository.findAll(PageRequest.of(i, size));
while(paging.hasContent()) {
List<AccountEntity> list = paging.getContent();
list.stream().forEach(e -> System.out.println(e.getAaa().getAccountNumber()));
paging = accountRepository.findAll(PageRequest.of(++i, size));
}
Union,Union All not supported
Union,Union All is not supported by JPQL. if you set nativeQuery=true, you can execute SQL, but you have to do List.add on the program side.
No identifier specified for entity
This error occurs when @Id (javax.persistence.Id) is not specified for an entity.
One @Id is required for an entity.
Not supported for DML operations
Not supported for DML operations” error occurs if @Modifying annotation is not attached when SQL for updating is issued.
@Modifying @Query(value="update User m set m.name = 'takahashi'") public void updateBySample();
limit,offset Although limit, offset, etc. can be used in PostgreSQL, JPQL does not support them, so the setFirstResult and setMaxResults methods of the TypedQuery interface can be used instead.
Or use the top,first keyword.
column “xxx” is of type timestamp without time zone but expression is of type bytea
In PostgreSQL, native query update statement may cause “column “xxx” is of type timestamp without time zone but expression is of type bytea” error.
This is because the update statement is issued with a column of type timestamp(6) without time zone called “hoge” set to null, and if nativeQuery=false, the update is successfully performed with null.
コメント