Edit: I think it would be helpful to explain my goal here first. My goal is to reduce and avoid model/@Entity bloat when using stored procedures with Hibernate. You can get raw data back from the persistent EntityManager when using a stored procedure, but that data will not be mapped. If you send in a stripped down model to Hibernate, Hibernate will only send you back the columns which are annotated as @Column on the @Entity model (almost forcing you to create a new @Entity for every stored procedure!) You can attempt to map this data with a DTO that has more properties, but they won't map to anything because all the fields which were not included on the model will return null.
I've been struggling to find an answer to this in my research. We use an MSSQL database and Spring with JPA/javax/Hibernate persistence, but do not rely on Hibernate for its ORM. All CRUD operations are done using stored procedures. We have several models (Spring @Entity) which work well for retrieving and mapping data. For example, a basic user model.
import javax.persistence;
@Entity
public class User {
@Id
@Column
private int userID;
@Column
private String userName;
@Column
private Date userDOB;
public User(UserDTO userDTO){
userName = userDTO.getUserName();
userID = userDTO.getUserID();
// Extra column userDOB, and no way to map accountDetails from the DTO
}
public int getUserID(){...}
public String getUserName(){...}
public Date getUserDOB(){...}
}
This works well when the stored procedure selects columns in a way that matches up with the model, however in cases where we want to selectively query data using joins, the columns (names and number of columns) often don't match up with the models. In this case, it makes sense to have DTOs to actually map the receiving data from the database using a constructor in the related Model. However, Spring doesn't like injecting the DTO directly (because it complains it isn't an entity), and injecting the model directly into the stored procedure query will fail, since the columns (number of columns!) don't match up.
public class UserDTO {
private String accountDetails;
private int userID;
private String userName;
public int getUserID(){...}
public String getUserName(){...}
}
I've tried using ModelMapper, but the first argument is an Object (the data), which isn't obtainable, since the data can't be mapped. I can of course call StoredProcedureQuery without a Model hint, and receive the raw data, but it will not be mapped.
public class UserRepo {
import javax.persistence;
public List<UserDTO> getUserAccountInfo(){
StoredProcedureQuery query = entityManager.createQuery(storedProcedureSelectUserAccount, User.class);
query.execute(); // Will fail here with SQL Server error: Unknown column userDOB.
List<UserDTO> result = query.getResults();
return result;
}
}
Others have suggested using raw Selects or other String based mapping strategies, but I would really appreciate some advice on retrieving and mapping the returned data using a DTO with the already written Stored Procedures. Thank you!
