@Entity
@Table(name = "t_hotel")
@Data
public class THotel {
    @Id
    private int id;
    private String name;
    private String address;
    /**
     * 城市id
     */
    private String city;
}

@Entity
@Table(name = "t_city")
@Data
public class TCity {
    @Id
    private int id;
    private String name;
    private String state;
    private String country;
    private String map;
}

public interface TCityRepository extends JpaRepository<TCity, Integer>, JpaSpecificationExecutor<TCity> {? 
}

@RunWith(SpringRunner.class)
@SpringBootTest
public class TCityRepositoryTest{
? ? ?@Autowired
? ? private TCityRepository tCityRepository;
}

/**
?* 查找出Id小于3,并且名稱(chēng)帶有`shanghai`的記錄.
?*
?* @param id ? id
?* @param name 城市名稱(chēng)
?* @return 城市列表
?*/
List<TCity> findByIdLessThanAndNameLike(int id, String name);

@Test
public void findByIdLessThanAndNameLike() throws Exception {
? ? List<TCity> shanghai = tCityRepository.findByIdLessThanAndNameLike(3, "%shanghai%");
? ? Assert.assertTrue(shanghai.size() > 0);
}

/**
?* 通過(guò)旅店名稱(chēng)分頁(yè)查詢(xún)旅店以及城市的信息
?*
?* @param name ? ? 旅店名稱(chēng)
?* @param pageable 分頁(yè)信息
?* @return Page<Object[]>
?*/
@Query(value = "select t1.name as cityName,t2.name as hotelName\n" +
? ? ? ? "from t_city t1\n" +
? ? ? ? " ?left join t_hotel t2 on t2.city = t1.id\n" +
? ? ? ? "where t2.name = :name",
? ? ? ? countQuery = "select count(*)" +
? ? ? ? ? ? ? ? "from t_city t1 \n" +
? ? ? ? ? ? ? ? " ?left join t_hotel t2 on t2.city = t1.id\n" +
? ? ? ? ? ? ? ? "where t2.name = :name"
? ? ? ? , nativeQuery = true)
Page<Object[]> findCityAndHotel(@Param("name") String name, Pageable pageable);

 @Test
public void findCityAndHotel() throws Exception {
? ? Page<Object[]> cityAndHotel = tCityRepository.findCityAndHotel("酒店", new PageRequest(0, 10));
? ? Assert.assertTrue(cityAndHotel.getTotalElements() > 0);
} ? ?

/**
?* HQL通過(guò)旅店名稱(chēng)查詢(xún)旅店以及城市的所有信息
?*
?* @return
?*/
@Query(value = "select new map(t1,t2) from ?TCity t1 left ?join THotel t2 on t1.id=t2.city where t2.name =:name")
List<Map<String, Object>> findCityAndHotelByHQL(@Param("name") String name);

/**
?* 關(guān)聯(lián)查詢(xún)
?*
?* @return
?*/
@Query(value = "select new pers.zpw.domain.CityHohel(t1.name AS cityName,t2.name AS hotelName) from ?TCity t1 left ?join THotel t2 on t1.id=t2.city where t2.name =:name")
List<CityHohel> findCityAndHotelByHQLResultObj(@Param("name") String name);

/**
* Created by ZhuPengWei on 2018/5/12.
*/
@Data
public class CityHohel {
? ? ? ? private String cityName;
? ? ? ? private String hotelName;
? ? ? ??
? ? ? ? public CityHohel(String cityName, String hotelName) {
? ? ? ? ? ? this.cityName = cityName;
? ? ? ? ? ? this.hotelName = hotelName;
? ? ? ? }
}

@Test
public void findCityAndHotelByHQLResultObj() throws Exception {
? ? List<CityHohel> cityAndHotelByHQLResultObj = tCityRepository.findCityAndHotelByHQLResultObj("酒店");
? ? Assert.assertTrue(cityAndHotelByHQLResultObj.size() > 0);
}

/**
?* 關(guān)聯(lián)查詢(xún)
?*
?* @return
?*/
@Query(value = "select new pers.zpw.domain.CityHohel(t1.name AS cityName,t2.name AS hotelName) from ?TCity t1 left ?join THotel t2 on t1.id=t2.city where t2.name =:name",
? ? ? ? countQuery = "select count(*) from ?TCity t1 left ?join THotel t2 on t1.id=t2.city where t2.name =:name")
Page<CityHohel> findCityAndHotelAllSelf(@Param("name") String name, Pageable pageable);

@Test
public void findCityAndHotelAllSelf() throws Exception {
? ? Page<CityHohel> cityAndHotelAllSelf = tCityRepository.findCityAndHotelAllSelf("酒店", new PageRequest(0, 10));
? ? Assert.assertTrue(cityAndHotelAllSelf.getTotalElements() > 0);
}

@Autowired
@PersistenceContext
private EntityManager entityManager;
@Test
public void testDynamic() throws Exception {
? ? ? ? String sql = "select new pers.zpw.domain.CityHohel(t1.name AS cityName,t2.name AS hotelName) from ?TCity t1 left ?join THotel t2 on t1.id=t2.city where t2.name ='酒店'";
? ? ? ? Query query = entityManager.createQuery(sql);
? ? ? ? List resultList = query.getResultList();
? ? ? ? Assert.assertTrue(resultList.size() > 0);
}

? /**
?* 自定義查詢(xún)
?* @param sql
?* @param entityManager
?* @return
?*/
default List customQuery(String sql, EntityManager entityManager) {
? ? return entityManager.createQuery(sql).getResultList();
}

public class StudentRepositoryImpl implements StudentCoustomRepository {
     
    // 這里可以寫(xiě)很復(fù)雜的SQL,作為演示之用,就不弄那么復(fù)雜
    private static final String SQL = "select * from t_student where name like :name ";     
    @PersistenceContext
    private EntityManager em; 
    @SuppressWarnings({ "rawtypes", "unchecked" })
    @Override
    public List<StudentVO> findByName(String name) {
        Query query = em.createNativeQuery(SQL).setParameter("name", "%"+name+"%");
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        List queryList = query.getResultList();
         
        if (queryList.size() == 0) {
            System.out.println("找不到Student name為" + name + "的記錄");
            return null;
        }         
        List<StudentVO> retVal = new ArrayList<>();
        for(Object o : queryList) {
            Map student = (Map)o;
            StudentVO vo = new StudentVO();
            try {
                // org.apache.commons.beanutils.BeanUtils;
                // 使用apaches的beanutil,直接吧map轉(zhuǎn)為實(shí)例
                BeanUtils.populate(vo, student);
                retVal.add(vo);
            } catch (IllegalAccessException | InvocationTargetException e) {
                System.out.println("解析StudentVO bean時(shí)發(fā)生異常：" + e.getMessage());
            }
        }
        return retVal;
    }
}