JPA下のオリジナルsqlクエリー
3563 ワード
private @Autowired EntityManager em;
//private @PersistenceContext EntityManager defaultEm;
@SuppressWarnings("unchecked")
public List findByName1(String bookName, String expressName) {
String sql = "select * from t_book b left join t_express e on b.exp_id = e.id where "
+ "b.name like '" + bookName + "' and e.name like '" + expressName +"'";
Query query = em.createNativeQuery(sql, Book.class);
//query executeUpdate、getFirstResult , sql
List bookList = query.getResultList();
return bookList;
}
//@Query(value="select * from t_book b left join t_express e on b.exp_id = e.id where "
// + "b.name like ?1 and e.name like ?2", nativeQuery=true)
//List findByName(String bookName, String expressName);
public List findByName2(String bookName, String expressName) {
return this.repository.findByName(bookName, expressName);
}
@SuppressWarnings("unchecked")
public List findBookExpress1(String bookName, String expressName) {
String sql = "select b.id as bookId,b.name as bookName, e.id as expressId, e.name as expressName from t_book b left join t_express e "
+ "on b.exp_id = e.id where b.name like '" + bookName + "' and e.name like '" + expressName +"'";
// ResultTransformer , Hibernate Object 。
// , addScalar
Query query = em.createNativeQuery(sql);
List result = query.unwrap(SQLQuery.class)
.addScalar("bookId", LongType.INSTANCE).addScalar("bookName").addScalar("expressId", LongType.INSTANCE).addScalar("expressName")
.setResultTransformer(Transformers.aliasToBean(BookExpress.class)).list();
return result;
}
// sql
@SuppressWarnings("unchecked")
public List findBookExpress2(String bookName, String expressName) throws IllegalAccessException, InvocationTargetException {
String sql = "select b.id as \"bookId\",b.name as \"bookName\", e.id as \"expressId\", e.name as \"expressName\" from t_book b left join t_express e "
+ "on b.exp_id = e.id where b.name like '" + bookName + "' and e.name like '" + expressName +"'";
Query query = em.createNativeQuery(sql);
List