Hibernate StatementInspector cannot work on the @OneToMany relationship
Hiberante version: 6.1
spring-boot: 3.0.2
StatementInspector :
@Slf4j
@Component
@ConditionalOnProperty(prefix = "athena", name ="statement_inspector", havingValue = "true", matchIfMissing = true)
public class GlobalSqlInjectInterceptor implements StatementInspector {
@Override
public String inspect(String sql) {
log.debug("before inject sql is : {} " , sql);
// try {
// sql = sqlInjectAdapter.inject(sql);
// } catch (Exception e) {
// log.error("sql inject error", e);
// }
log.debug("after inject sql is : {} " , sql);
return sql;
}
}
config :
...
@Bean
public HibernatePropertiesCustomizer hibernateCustomizer(StatementInspector statementInspector) {
return (properties) -> properties.put(AvailableSettings.STATEMENT_INSPECTOR, statementInspector);
}
...
entity:
@Getter
@Setter
@MappedSuperclass
@EntityListeners(AuditingEntityListener.class)
public abstract class AbstractEntity implements Serializable {
@Id
@Tsid
private Long id;
@CreatedDate
@Temporal(value = TemporalType.TIMESTAMP)
@Column(name = "create_at", updatable = false )
private Date createAt;
@LastModifiedDate
@Temporal(value = TemporalType.TIMESTAMP)
@Column(name = "update_at" )
private Date updateAt;
@CreatedBy
@Column(name = "create_by", updatable = false )
private String createBy;
@LastModifiedBy
@Column(name = "update_by" )
private String updateBy;
@Column(name = "c_user_id", updatable = false)
private String userId;
@Column(name = "c_user_name", updatable = false)
private String username;
@Column(name = "c_dept_id", updatable = false)
private String deptId;
@Column(name = "c_dept_name", updatable = false)
private String deptName;
@Column(name = "c_org_id", updatable = false)
private String orgId;
@Column(name = "c_deleted", insertable = false, updatable = false)
private Integer deleted;
@Column(name = "c_org_name")
private String orgName;
@Override
public boolean equals(Object o) {
if (this == o) {
return true;
}
if (!(o instanceof AbstractEntity that)) {
return false;
}
return getId().equals(that.getId());
}
@Override
public int hashCode() {
return getId().hashCode();
}
}
@Table(name = "t_question")
@Entity
@Getter
@Setter
public class Question extends AbstractEntity{
/**
* order
*/
@Column(name = "c_order")
private Integer order;
/**
* question parent id
*/
@Column(name = "c_parent_id")
private Long parentId;
/**
* score
*/
@Column(name = "c_score")
private Double score;
/**
* title
*/
@Column(name = "c_title")
private String title;
/**
* content
*/
@Column(name = "c_content")
private String content;
/**
* type
* 0 single choice
* 1 mutli choice
* 2 juge
* 3 fill
* 4 question
*/
@Column(name = "c_type")
@Convert(converter = QuestionType.QuestionTypeConverter.class)
private QuestionType type;
/**
* standard answer
*/
@Column(name = "c_answer")
private String answer;
/**
* question library id
*/
@Column(name = "c_lib_id" )
private Long libId;
/**
* sub questions
*/
@OneToMany(mappedBy = "parentId", cascade = CascadeType.ALL)
@BatchSize(size = 10)
private List<Question> children;
the request parameter :
{
"order": 0,
"parentId": 0,
"score": 0,
"title": "string",
"content": "string",
"type": 0,
"answer": "string",
"libId": 0,
"children":[{
"order": 0,
"parentId": 0,
"score": 0,
"title": "string",
"content": "string",
"type": 0,
"answer": "string",
"libId": 0
}]
}
the log output:
2023-08-12 11:40:44,907 TRACE [http-nio-9090-exec-1] o.s.t.i.TransactionAspectSupport: Getting transaction for [com.temple.athena.service.impl.QuestionServiceImpl.add]
2023-08-12 11:40:44,923 TRACE [http-nio-9090-exec-1] o.s.t.i.TransactionAspectSupport: Getting transaction for [com.temple.athena.repository.base.BaseRepositoryImpl.save]
2023-08-12 11:40:44,970 TRACE [http-nio-9090-exec-1] o.s.t.i.TransactionAspectSupport: Completing transaction for [com.temple.athena.repository.base.BaseRepositoryImpl.save]
2023-08-12 11:40:44,970 TRACE [http-nio-9090-exec-1] o.s.t.i.TransactionAspectSupport: Completing transaction for [com.temple.athena.service.impl.QuestionServiceImpl.add]
2023-08-12 11:40:45,189 DEBUG [http-nio-9090-exec-1] c.t.a.c.i.GlobalSqlInjectInterceptor: before inject sql is : insert into t_question (c_answer, c_content, create_at, create_by, c_dept_id, c_dept_name, c_lib_id, c_order, c_org_id, c_org_name, c_parent_id, c_score, c_title, c_type, update_at, update_by, c_user_id, c_user_name, id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
2023-08-12 11:40:45,189 DEBUG [http-nio-9090-exec-1] c.t.a.c.i.GlobalSqlInjectInterceptor: after inject sql is : insert into t_question (c_answer, c_content, create_at, create_by, c_dept_id, c_dept_name, c_lib_id, c_order, c_org_id, c_org_name, c_parent_id, c_score, c_title, c_type, update_at, update_by, c_user_id, c_user_name, id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
2023-08-12 11:40:45,189 DEBUG [http-nio-9090-exec-1] o.h.e.j.s.SqlStatementLogger: insert into t_question (c_answer, c_content, create_at, create_by, c_dept_id, c_dept_name, c_lib_id, c_order, c_org_id, c_org_name, c_parent_id, c_score, c_title, c_type, update_at, update_by, c_user_id, c_user_name, id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
2023-08-12 11:40:45,189 TRACE [http-nio-9090-exec-1] o.h.t.d.JdbcBindingLogging: binding parameter [1] as [VARCHAR] - [string]
2023-08-12 11:40:45,189 TRACE [http-nio-9090-exec-1] o.h.t.d.JdbcBindingLogging: binding parameter [2] as [VARCHAR] - [string]
2023-08-12 11:40:45,189 TRACE [http-nio-9090-exec-1] o.h.t.d.JdbcBindingLogging: binding parameter [3] as [TIMESTAMP] - [Sat Aug 12 11:40:44 GMT+08:00 2023]
2023-08-12 11:40:45,189 TRACE [http-nio-9090-exec-1] o.h.t.d.JdbcBindingLogging: binding parameter [4] as [VARCHAR] - []
2023-08-12 11:40:45,189 TRACE [http-nio-9090-exec-1] o.h.t.d.JdbcBindingLogging: binding parameter [5] as [VARCHAR] - [2]
2023-08-12 11:40:45,189 TRACE [http-nio-9090-exec-1] o.h.t.d.JdbcBindingLogging: binding parameter [6] as [VARCHAR] - [2]
2023-08-12 11:40:45,189 TRACE [http-nio-9090-exec-1] o.h.t.d.JdbcBindingLogging: binding parameter [7] as [BIGINT] - [0]
2023-08-12 11:40:45,189 TRACE [http-nio-9090-exec-1] o.h.t.d.JdbcBindingLogging: binding parameter [8] as [INTEGER] - [0]
2023-08-12 11:40:45,189 TRACE [http-nio-9090-exec-1] o.h.t.d.JdbcBindingLogging: binding parameter [9] as [VARCHAR] - [1]
2023-08-12 11:40:45,189 TRACE [http-nio-9090-exec-1] o.h.t.d.JdbcBindingLogging: binding parameter [10] as [VARCHAR] - [1]
2023-08-12 11:40:45,189 TRACE [http-nio-9090-exec-1] o.h.t.d.JdbcBindingLogging: binding parameter [11] as [BIGINT] - [0]
2023-08-12 11:40:45,189 TRACE [http-nio-9090-exec-1] o.h.t.d.JdbcBindingLogging: binding parameter [12] as [FLOAT] - [0.0]
2023-08-12 11:40:45,189 TRACE [http-nio-9090-exec-1] o.h.t.d.JdbcBindingLogging: binding parameter [13] as [VARCHAR] - [string]
2023-08-12 11:40:45,189 TRACE [http-nio-9090-exec-1] o.h.t.d.JdbcBindingLogging: binding parameter [14] as [INTEGER] - [1]
2023-08-12 11:40:45,189 TRACE [http-nio-9090-exec-1] o.h.t.d.JdbcBindingLogging: binding parameter [15] as [TIMESTAMP] - [2023-08-12 11:40:44.939]
2023-08-12 11:40:45,189 TRACE [http-nio-9090-exec-1] o.h.t.d.JdbcBindingLogging: binding parameter [16] as [VARCHAR] - []
2023-08-12 11:40:45,189 TRACE [http-nio-9090-exec-1] o.h.t.d.JdbcBindingLogging: binding parameter [17] as [VARCHAR] - [3]
2023-08-12 11:40:45,189 TRACE [http-nio-9090-exec-1] o.h.t.d.JdbcBindingLogging: binding parameter [18] as [VARCHAR] - [3]
2023-08-12 11:40:45,189 TRACE [http-nio-9090-exec-1] o.h.t.d.JdbcBindingLogging: binding parameter [19] as [BIGINT] - [478045148090785648]
2023-08-12 11:40:45,392 DEBUG [http-nio-9090-exec-1] o.h.e.j.s.SqlStatementLogger: insert into t_question (c_answer, c_content, create_at, create_by, c_dept_id, c_dept_name, c_lib_id, c_order, c_org_id, c_org_name, c_parent_id, c_score, c_title, c_type, update_at, update_by, c_user_id, c_user_name, id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
2023-08-12 11:40:45,392 TRACE [http-nio-9090-exec-1] o.h.t.d.JdbcBindingLogging: binding parameter [1] as [VARCHAR] - [string]
2023-08-12 11:40:45,392 TRACE [http-nio-9090-exec-1] o.h.t.d.JdbcBindingLogging: binding parameter [2] as [VARCHAR] - [string]
2023-08-12 11:40:45,392 TRACE [http-nio-9090-exec-1] o.h.t.d.JdbcBindingLogging: binding parameter [3] as [TIMESTAMP] - [Sat Aug 12 11:40:44 GMT+08:00 2023]
2023-08-12 11:40:45,392 TRACE [http-nio-9090-exec-1] o.h.t.d.JdbcBindingLogging: binding parameter [4] as [VARCHAR] - []
2023-08-12 11:40:45,392 TRACE [http-nio-9090-exec-1] o.h.t.d.JdbcBindingLogging: binding parameter [5] as [VARCHAR] - [2]
2023-08-12 11:40:45,392 TRACE [http-nio-9090-exec-1] o.h.t.d.JdbcBindingLogging: binding parameter [6] as [VARCHAR] - [2]
2023-08-12 11:40:45,392 TRACE [http-nio-9090-exec-1] o.h.t.d.JdbcBindingLogging: binding parameter [7] as [BIGINT] - [0]
2023-08-12 11:40:45,392 TRACE [http-nio-9090-exec-1] o.h.t.d.JdbcBindingLogging: binding parameter [8] as [INTEGER] - [0]
2023-08-12 11:40:45,392 TRACE [http-nio-9090-exec-1] o.h.t.d.JdbcBindingLogging: binding parameter [9] as [VARCHAR] - [1]
2023-08-12 11:40:45,392 TRACE [http-nio-9090-exec-1] o.h.t.d.JdbcBindingLogging: binding parameter [10] as [VARCHAR] - [1]
2023-08-12 11:40:45,392 TRACE [http-nio-9090-exec-1] o.h.t.d.JdbcBindingLogging: binding parameter [11] as [BIGINT] - [0]
2023-08-12 11:40:45,392 TRACE [http-nio-9090-exec-1] o.h.t.d.JdbcBindingLogging: binding parameter [12] as [FLOAT] - [0.0]
2023-08-12 11:40:45,392 TRACE [http-nio-9090-exec-1] o.h.t.d.JdbcBindingLogging: binding parameter [13] as [VARCHAR] - [string]
2023-08-12 11:40:45,392 TRACE [http-nio-9090-exec-1] o.h.t.d.JdbcBindingLogging: binding parameter [14] as [INTEGER] - [1]
2023-08-12 11:40:45,392 TRACE [http-nio-9090-exec-1] o.h.t.d.JdbcBindingLogging: binding parameter [15] as [TIMESTAMP] - [2023-08-12 11:40:44.97]
2023-08-12 11:40:45,392 TRACE [http-nio-9090-exec-1] o.h.t.d.JdbcBindingLogging: binding parameter [16] as [VARCHAR] - []
2023-08-12 11:40:45,392 TRACE [http-nio-9090-exec-1] o.h.t.d.JdbcBindingLogging: binding parameter [17] as [VARCHAR] - [3]
2023-08-12 11:40:45,392 TRACE [http-nio-9090-exec-1] o.h.t.d.JdbcBindingLogging: binding parameter [18] as [VARCHAR] - [3]
2023-08-12 11:40:45,392 TRACE [http-nio-9090-exec-1] o.h.t.d.JdbcBindingLogging: binding parameter [19] as [BIGINT] - [478045148157891694]
when inserting the parent entity the SQL will be injected and the output looks great; but the children entity is not injected, and I debug the code, the GlobalSqlInjectInterceptor only execute once.
Q1: why this? I can't understand, does the word statement in the StatementInspector means prepareStatement and this result suggests the prepareStatement have been reused, so does the GlobalSqlInjectInterceptor not work twice?
Q2: I want to check when hql(or SQL) executes, and add some additional SQL, e.g. add c_deleted = 0' on selectandinsert` SQL. how can I do this?
Q1: Why does the GlobalSqlInjectInterceptor only execute once for the parent entity and not for its children entities when inserting?
The behavior you're observing is due to Hibernate's batching mechanism. When you use @BatchSize(size=10) on the children collection in your Question entity, Hibernate is optimizing the SQL execution by batching the inserts. This means that even though you are inserting multiple child entities, Hibernate might choose to batch those inserts into a single SQL statement. Since the StatementInspector works at the statement level, it inspects and modifies individual SQL statements that Hibernate generates.
In your log output, you can see that the interceptor is only called once for the parent entity's insert statement, and the subsequent batched child entities are not invoking the interceptor again. This behavior is expected because batching combines multiple inserts into a single statement for performance reasons.
Q2: How can I modify HQL/SQL statements before execution?
If you want to modify the generated SQL statements, such as adding conditions like c_deleted = 0 for select and insert queries, you can use Hibernate's Interceptor. The Interceptor interface provides methods like onPrepareStatement which you can override to modify the SQL before it's executed.
Here's how you can implement an Interceptor to modify SQL statements:
import org.hibernate.EmptyInterceptor;
public class CustomInterceptor extends EmptyInterceptor {
@Override
public String onPrepareStatement(String sql) {
// Modify the SQL as needed, e.g., adding conditions
if (sql.startsWith("insert into t_question")) {
sql = sql.replace("values (", "values (c_deleted = 0, ");
} else if (sql.startsWith("select")) {
sql += " where c_deleted = 0";
}
return super.onPrepareStatement(sql);
}
}
Then, you can configure this Interceptor in your Spring Boot
import org.hibernate.Interceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
@Configuration
public class HibernateConfig {
@Bean
public Interceptor hibernateInterceptor() {
return new CustomInterceptor();
}
}
Remember that this approach affects all the SQL statements generated by Hibernate. Be cautious when modifying SQL statements, as it can have unintended consequences. Always thoroughly test your application after implementing such custom modifications.
Additionally, consider whether you really need to modify SQL in this way. Hibernate provides mechanisms like filters and entity listeners that can help you achieve similar goals without directly manipulating SQL statements.