Reputation: 712
I have simple procedure which lists out users. I am using @NamedStoredProcedureQueries
for procedure declaration and used EntityManager.createNamedStoredProcedureQuery
for StoredProcedureQuery
.
It returns the result properly but I need column name so that I will know which value is for which column.
My code goes something like this
Entity Class
@Entity
@NamedStoredProcedureQueries({ @NamedStoredProcedureQuery(name =
"sGetUserList", procedureName = "sGetUserList", parameters = {
@StoredProcedureParameter(mode = ParameterMode.IN, name = "user_id", type =
Integer.class) })
})
public class User {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Integer id;
private String name;
private String email;
//getters and setters
}
Custom Repositoty
public interface UserRepositoryCustom {
List<?> testProc() ;
}
Repository
public interface UserRepository extends JpaRepository<User, Long>,
UserRepositoryCustom{
}
Repository Implementation
public class UserRepositoryImpl implements UserRepositoryCustom{
@PersistenceContext
EntityManager em;
public List<Object> testProc() {
StoredProcedureQuery q = em.createNamedStoredProcedureQuery("sGetUserList");
q.setParameter("user_id", 1);
List<Object> res = q.getResultList();
return res;
}
}
I need result with column names.
Upvotes: 3
Views: 11319
Reputation: 11
To maintain the column name order, you can build your own AliasedTupleSubsetResultTransformer
with LinkedHashMap
like:
public class AliasToEntityLinkedHashMapResultTransformer extends AliasedTupleSubsetResultTransformer {
public static final AliasToEntityLinkedHashMapResultTransformer INSTANCE = new AliasToEntityLinkedHashMapResultTransformer();
@Override
public Object transformTuple(Object[] tuple, String[] aliases) {
LinkedHashMap result = new LinkedHashMap(tuple.length);
for (int i = 0; i < tuple.length; i++) {
String alias = aliases[i];
if (alias != null) {
result.put(alias, tuple[i]);
}
}
return result;
}
@Override
public boolean isTransformedValueATupleElement(String[] aliases, int tupleLength) {
return false;
}
private Object readResolve() {
return INSTANCE;
}
}
and use it in:
nativeQuery.setResultTransformer(AliasToEntityLinkedHashMapResultTransformer.INSTANCE);
Upvotes: 0
Reputation: 61
You can get the column names along with their values in a Map. i.e Map<'column-name', value>
.
Query query = entityManager.createNativeQuery("{call <<Your procedure>>}");
NativeQueryImpl nativeQuery = (NativeQueryImpl) query;
nativeQuery.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
List<Map<String,Object>> result = nativeQuery.getResultList();
This will be very helpful in a places where you want to use column names as a placeholder in HTML where value will replace it in runtime.
Upvotes: 6
Reputation: 360
Here , I have written a method from you can get JSON from you can get key value pair of column and value
@Transactional
@Component
public class CustomRepository<T> {
@Autowired
private EntityManager em;
private ObjectMapper mapper = new ObjectMapper();
public List<T> getResultOfQuery(String argQueryString,Class<T> valueType) {
try {
Query query = em.createNativeQuery(argQueryString);
NativeQueryImpl nativeQuery = (NativeQueryImpl) query;
nativeQuery.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
List<Map<String,Object>> result = nativeQuery.getResultList();
List<T> resultList = result.stream()
.map(o -> {
try {
return mapper.readValue(mapper.writeValueAsString(o),valueType);
} catch (Exception e) {
ApplicationLogger.logger.error(e.getMessage(),e);
}
return null;
}).collect(Collectors.toList());
return resultList;
} catch (Exception ex) {
ApplicationLogger.logger.error(ex.getMessage(),ex);
throw ex;
}
}
}
The Only condition is that your query and pojo attribute name should be same
Upvotes: 4
Reputation: 9612
I'm not sure I understand what you are trying to do here. If you want to get all the users using Spring data you shouldn't be implementing your UserRepository. Spring Data does this for you.
In fact the JpaRepository already has the method you need.
List<User> findAll();
You can just call this to get a list of all your users and won't need to worry about the column names.
Just inject your repository where you need it and call the method to get all users:
@Autowire
UserRepository userRepository;
List<Users> allUsers = userRepository.findAll();
EDIT: If there is a particular reason you want to use stored procedures though there is a Spring Data way of doing this without implementing UserRepository yourself. You can do this by defining the following method:
public interface UserRepository extends JpaRepository<User, Long>{
@Procedure(name = "sGetUserList")
List<User> sGetUserList(@Param("user_id") Integer userId);
}
Again there shouldn't be any issue with resolving column names with this method.
Upvotes: 1