CODEWITHSUNDEEP
javaspringhibernatejpa
Nikesh Kedlaya
Nikesh Kedlaya

Reputation: 712

How to get column names in result set in Spring data JPA

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: 11322

Answers (4)

Ogosoka
Ogosoka

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

Pranav Kadam
Pranav Kadam

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

Thakkar Darshan
Thakkar Darshan

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

Plog
Plog

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

Related Questions