vendredi 31 août 2018

@SQLInsert dynamic database schema

I have a need to call a stored procedure on insert of an entity. To do this, I am using @SQLInsert, which is working well. The problem I'm facing is that I need to be able to change part of the string during integration tests, as we use a different database schema.

Entity (getters/setters removed for space savings):

@Entity
@Table(name = "SNIPPET")
@SQLInsert(sql = "{ call MY_SCHEMA.INSERT_SNIPPET (?, ?, ?, ?, ?, ?) }", callable = true)
public class Snippet extends BaseDomain {

@Lob
@Column(name = "SNIPPET_TEXT_V2", nullable = false)
private String snippetText;

@Column(name = "ORDER", nullable = false)
private int snippetOrder; //1 based.

@Column(name = "NOTES", nullable = true)
private String snippetNotes;

@Column(name = "STATUS", nullable = false)
@Enumerated(EnumType.STRING)
private Status status;

@ManyToOne
@JoinColumn(name = "QUERY_ID")
private Query query;

}

This is the line I want to be able to change at runtime/test startup: @SQLInsert(sql = "{ call MY_SCHEMA.INSERT_SNIPPET (?, ?, ?, ?, ?, ?) }", callable = true)

I need to make MY_SCHEMA variable/adjustable at runtime. Is there a way to do this via reflection or other means?

A couple of things to note -

  • Setting the default schema on my connection doesn't work. We use USER_SCHEMA to connect to the DB and keep objects stored in MY_SCHEMA. Changing the default schema to MY_SCHEMA and using { call INSERT_SNIPPET (?, ?, ?, ?, ?, ?) } would have adverse effects on other parts of our application.
  • Versions - oracle 11g, java 8, hibernate 5.2




Aucun commentaire:

Enregistrer un commentaire