1. package com.dean.app.service.system;  
2.   
3. import java.beans.Introspector;  
4. import java.beans.PropertyDescriptor;  
5. import java.lang.reflect.Method;  
6. import java.math.BigInteger;  
7. import java.util.ArrayList;  
8. import java.util.List;  
9. import java.util.Map;  
10.   
11. import javax.persistence.EntityManager;  
12. import javax.persistence.PersistenceContext;  
13. import javax.persistence.Query;  
14. import javax.transaction.Transactional;  
15.   
16. import org.apache.commons.beanutils.ConvertUtils;  
17. import org.hibernate.SQLQuery;  
18. import org.hibernate.Session;  
19. import org.hibernate.transform.Transformers;  
20. import org.springframework.stereotype.Repository;  
21. import org.springframework.util.CollectionUtils;  
22.   
23. @Transactional  
24. @Repository  
25. public class CommonDao {  
26.       
27.     @PersistenceContext  
28.     EntityManager entityManager;  
29.       
30.     /** 
31.      * * Query Data Collection 
32.      * @param sql Query parameters in sql SQL using: name format 
33.      * @param params Query parameter map format, key corresponds to:name 
34.      * @param clazz Empty entity type converts directly to map format 
35.      * @return 
36.      */  
37.     @SuppressWarnings("unchecked")  
38.     public List<?> queryListEntity(String sql,Map<String, Object> params, Class<?> clazz){  
39.         Session session = entityManager.unwrap(org.hibernate.Session.class);  
40.         SQLQuery query = session.createSQLQuery(sql);  
41.         if (params != null) {  
42.             for (String key : params.keySet()) {  
43.                 query.setParameter(key, params.get(key));  
44.             }  
45.         }  
46.         query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
47.         List<Map<String, Object>> result =  query.list();  
48.         if (clazz != null) {  
49.             List<Object>  entityList = convert(clazz, result);  
50.             return entityList;  
51.         }  
52.         return result;  
53.     }  
54.   
55.     private List<Object> convert(Class<?> clazz, List<Map<String, Object>> list) {  
56.         List<Object> result;  
57.         if (CollectionUtils.isEmpty(list)) {  
58.             return null;  
59.         }  
60.         result = new ArrayList<Object>();  
61.         try {  
62.              PropertyDescriptor[] props = Introspector.getBeanInfo(clazz).getPropertyDescriptors();  
63.              for (Map<String, Object> map : list) {  
64.                  Object obj = clazz.newInstance();  
65.                  for (String key:map.keySet()) {  
66.                      String attrName = key.toLowerCase();  
67.                      for (PropertyDescriptor prop : props) {  
68.                          attrName = removeUnderLine(attrName);  
69.                          if (!attrName.equals(prop.getName())) {  
70.                              continue;  
71.                          }  
72.                          Method method = prop.getWriteMethod();  
73.                          Object value = map.get(key);  
74.                          if (value != null) {  
75.                              value = ConvertUtils.convert(value,prop.getPropertyType());  
76.                          }  
77.                          method.invoke(obj,value);  
78.                      }  
79.                  }  
80.                  result.add(obj);  
81.              }  
82.         } catch (Exception e) {  
83.             throw new RuntimeException("Data conversion error");  
84.         }  
85.         return result;  
86.     }  
87.   
88.     private String removeUnderLine(String attrName) {  
89.         //Remove underscores from database fields  
90.          if(attrName.contains("_")) {  
91.             String[] names = attrName.split("_");  
92.             String firstPart = names[0];  
93.             String otherPart = "";  
94.             for (int i = 1; i < names.length; i++) {  
95.                 String word = names[i].replaceFirst(names[i].substring(0, 1), names[i].substring(0, 1).toUpperCase());  
96.                 otherPart += word;  
97.             }  
98.             attrName = firstPart + otherPart;  
99.          }  
100.         return attrName;  
101.     }  
102.       
103.     /** 
104.      * Get Number of Records 
105.      * @param sql 
106.      * @param params 
107.      * @return 
108.      */  
109.     public Integer getCountBy(String sql,Map<String, Object> params){  
110.         Query query =  entityManager.createNativeQuery(sql);  
111.         if (params != null) {  
112.             for (String key : params.keySet()) {  
113.                 query.setParameter(key, params.get(key));  
114.             }  
115.         }  
116.         BigInteger bigInteger  = (BigInteger) query.getSingleResult();  
117.         return bigInteger.intValue();  
118.     }  
119.       
120.     /** 
121.      * Add or delete 
122.      * @param sql 
123.      * @param params 
124.      * @return 
125.      */  
126.     public Integer deleteOrUpDate(String sql,Map<String, Object> params){  
127.         Query query =  entityManager.createNativeQuery(sql);  
128.         if (params != null) {  
129.             for (String key : params.keySet()) {  
130.                 query.setParameter(key, params.get(key));  
131.             }  
132.         }  
133.         return query.executeUpdate();  
134.     }  
135. }  

1. The integer returned by the getCountBy() method in the code above needs to be transformed because the database returns BigInteger

2. There is also data where the underlined field does not correspond to the entity, so the underline needs to be removed and the first letter of the string after the underline needs to be converted to uppercase, such as login_name for the field in the database and loginName for the entity object defined by the field itself, where the conversion is only to be universal.When not converting, you only need to write SQL with an alias and an entity.

3. If you don't need to convert to a specific entity, you can either use the List <Map <String, Object> collection directly or use query.setResultTransformer(Transformers.toBean(clazz)) directly to convert to the specified entity, but if some fields in the database are bigint (I'm using it) MySQL Database) Then the corresponding attributes of the entity you define need to be of type BigInterger, otherwise type conversion errors will be reported.Other databases have not been tested, so just change them.