I am using Spring Boot 2 with thymeleaf to display data from an existing Oracle 12 database.
The workflow entity is one of my main entities which I would like to display:
@Entity
@Immutable
@Table(name = "V_AV_WFPROC", schema="avuser")
public class Workflow {
@Id
@Column(name = "proc_id")
private Long id;
private String name;
@Column(name = "proc_ref")
private String procRef;
private String description;
@OneToOne(mappedBy = "workflow")
private WorkflowCategory workflowCategory;
public Long getId() {
return id;
}
public String getName() {
return name;
}
public String getProcRef() {
return procRef;
}
public String getDescription() {
return description;
}
public WorkflowCategory getWorkflowCategory() {
return workflowCategory;
}
}
workflowCategory holds some information about the workflow like the type of the workflow:
@Entity
@Immutable
@Table(name = "V_AV_WFPROC_CATEGORIES", schema = "avuser")
public class WorkflowCategory {
@Id
@Column(name = "cat_id")
private Long id;
private String name;
@OneToOne
@JoinColumn(name="proc_id", referencedColumnName = "proc_id")
private Workflow workflow;
public Long getId() {
return id;
}
public String getName() {
return name;
}
public Workflow getWorkflow() {
return workflow;
}
I have a custom query for the workflow category, as only some information is important for me. I need to decode the category name as I don't have access to the table where the information is stored.
@Repository
public interface WorkflowCategoryRepository extends CrudRepository<WorkflowCategory, String>{
@Query(value = "SELECT PROC_ID || CAT_ID AS CAT_ID, PROC_ID, DECODE(CAT_ID, 4, 'Request Workflow', 5, 'Approval Workflow', 6, 'Escalation Workflow', 7, 'Fulfillment Workflow', 51, 'Custom Tasks Workflow', '?') AS NAME FROM WorkflowCategory WHERE CAT_ID IN (4, 5, 6, 7, 51)",
nativeQuery = true)
public Iterable<WorkflowCategory> findAll();
}
If I am trying to access the data I get the following error message.
More than one row with the given identifier was found: 41, for class: de.elementity.runbook.workflow.WorkflowCategory
The table contains more information per workflow but only the ones from my query are important for me. How can I specify a query for the @OneToOne mapping?
I also tried to use a @ManyToMany mapping, but there I get the following error message:
2020-02-10 14:48:14.057 DEBUG 35572 --- [nio-8080-exec-1] org.hibernate.SQL : select wfcats0_.workflows_proc_id as workflows_proc_id2_12_0_, wfcats0_.wfCats_cat_id as wfCats_cat_id1_12_0_, wfcat1_.cat_id as cat_id1_11_1_, wfcat1_.name as name2_11_1_ from V_AV_WFPROC_CATEGORIES_V_AV_WFPROC wfcats0_ inner join avuser.V_AV_WFPROC_CATEGORIES wfcat1_ on wfcats0_.wfCats_cat_id=wfcat1_.cat_id where wfcats0_.workflows_proc_id=?
2020-02-10 14:48:14.221 WARN 35572 --- [nio-8080-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 972, SQLState: 42000
2020-02-10 14:48:14.221 ERROR 35572 --- [nio-8080-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper : ORA-00972: idnetifier too long
2020-02-10 14:48:14.229 ERROR 35572 --- [nio-8080-exec-1] org.thymeleaf.TemplateEngine : [THYMELEAF][http-nio-8080-exec-1] Exception processing template "workflows": An error happened during template parsing (template: "class path resource [templates/workflows.html]")
org.thymeleaf.exceptions.TemplateInputException: An error happened during template parsing (template: "class path resource [templates/workflows.html]")
This also happens when I remove the DECODE part from the query.
