关于JPA中使用原生SQL语句

allen413

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;
       }
    }

}