The code has been put into github, demo2 in test corresponds to sql mode, demo3 corresponds to hql mode, demo1 is paging query, another article will talk about {% post_link paging query paging query%}
github address

Preface

When we usually use jpa query, there are two situations: one is to query all fields, the other is to query some fields. When we query some fields according to the usual sql statement, jpa can not automatically parse the type, such as this kind of error.

org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute query; SQL [ SELECT sa.name FROM student sa ]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query

Solution

There are two solutions for hql and sql respectively

I.

In the case of hql, we can solve this problem in this way. It is important to note that there must be corresponding constructor classes in Student.

//demo-hql for TODO query partial fields
@Query(value = " SELECT new Student(s.name) FROM Student s")
List<Student> temp03();

Two.

In the case of sql, we can solve it in this way. Firstly, we parse the data collar jpa into map, and then solve it by our own map-to-entity class method.

//demo-sql for TODO query partial fields
@Query(value = " SELECT sa.name FROM student sa ",
    nativeQuery = true)
List<Map<String,Object>> temp02();

Here's a tool I wrote for map to entity class

/**Convert map to entity class, which is used when jpa queries some fields
* Note that since the int type is initialized and cannot be ignored by FASTJSON, the returned json may have an additional number of 0
* Because it matches by attribute names, if the database field names and parameter names are inconsistent, some fields will not map to entities, which should be written as follows
* @Query(value = " select id,bar_code01 barCode01,bar_code02 barCode02,bar_code03 barCode03,name,comment from library_good ",nativeQuery=true)
* When querying, aliases are used to match the class's attribute names. */
public static <T>T mapToEntity(Map<String,Object> map,Class<T> targetClass) throws IllegalAccessException, InstantiationException {
    Class superClass;
    Field[] fields;
    
    T target = targetClass.newInstance();
    //Field that receives targetClass
    List<Field> targetfieldList = new LinkedList<>();

    superClass = targetClass;
    while(superClass!=null&&superClass!=Object.class){
        //Because this method can only get superClass parameters (private,protect,public, etc.), but can not get the parameters of the parent class, here we iterate a wave.
        fields = superClass.getDeclaredFields();
        targetfieldList.addAll(Arrays.asList(fields));
        superClass = superClass.getSuperclass();
    }
    //Matching and assigning
    for (Field targetfield : targetfieldList) {
        for (Map.Entry<String, Object> mapEntry : map.entrySet()) {
            if (targetfield.getName().equals(mapEntry.getKey())){
                //Temporary save permission
                boolean targetFlag = targetfield.isAccessible();
                //Grant authority
                targetfield.setAccessible(true);
                //assignment
                targetfield.set(target,mapEntry.getValue());
                //Restore original authority
                targetfield.setAccessible(targetFlag);
                break;
            }
        }
    }
    return target;
}

One thing to note is that because the underlying layer uses reflection, it requires setAccessible(true) to retrieve or store data in this way, otherwise IllegalAccessException will occur.