关于JPA中使用原生SQL语句
importorg.springframework.context.ApplicationContext;import org.springframework.orm.jpa.support.JpaDaoSupport;
import java.util.*;
import javax.persistence.*;
public class CustomerCourseDAO extends JpaDaoSupport implementsICustomerCourseDAO {
public String findTeacherNameByCourseId(Integer courseid) {
String sql = "select concat(c.Given_Name,' ',c.Family_Name) asteachername from customer c,customer_course cc"
+ " where c.Customer_ID = cc.Customer_ID and cc.Related_Code = 2and cc.Course_ID = " + courseid.intValue();
try {
EntityManagerFactory emf =getJpaTemplate().getEntityManagerFactory();
EntityManager newEm = emf.createEntityManager();
EntityTransaction newTx = newEm.getTransaction();
newTx.begin();
List result = newEm.createNativeQuery(sql).getResultList();
String teachername = "";
if(result.size()==1){
teachername = (String)result.get(0);
}
newEm.getDelegate();
newTx.commit();
newEm.close();
emf.close();
return teachername;
} catch (RuntimeException re) {
logger.error("find Teacher's Name by courseId failed", re);
throw re;
}
}
//add byedwin， 2007-11-08 测试通过
@SuppressWarnings(value = "unchecked")
publicList<CustomerCourse>findTeaStuByCourseId(Integer id) {
String sql = "select model.* from customer_course model " + "wheremodel.Related_Code = 1 and model.Course_ID = " +id.intValue();
try {
EntityManagerFactory emf =getJpaTemplate().getEntityManagerFactory();
EntityManager newEm = emf.createEntityManager();
EntityTransaction newTx = newEm.getTransaction();
newTx.begin();
@SuppressWarnings(value = "unchecked")
List<CustomerCourse> stu =newEm.createNativeQuery(sql,CustomerCourse.class).getResultList();
newTx.commit();
newEm.close();
emf.close();
return stu;
} catch (RuntimeException re) {
logger.error("find Teacher's students by courseId failed",re);
throw re;
}
}
}
页:
[1]