jpa原生query_JPA使用原生SQL查询

一、Spring3.1之前版本的方法

import org.springframework.context.ApplicationContext;

import org.springframework.orm.jpa.support.JpaDaoSupport;

import java.util.*;

import javax.persistence.*;

public class CustomerCourseDAO extends JpaDaoSupport implements ICustomerCourseDAO {

public String findTeacherNameByCourseId(Integer courseid) {

String sql = "select concat(c.Given_Name,' ',c.Family_Name) as teachername from customer c,customer_course cc"

+ " where c.Customer_ID = cc.Customer_ID and cc.Related_Code = 2 and 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 by edwin， 2007-11-08 测试通过

@SuppressWarnings(value = "unchecked")

public List findTeaStuByCourseId(Integer id) {

String sql = "select model.* from customer_course model " + "where model.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 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;

}

}

}

二、Spring 3.1之后版本方法

1、在spring配置文件中配置entityManagerFactory

false

update

org.hibernate.dialect.MySQL5InnoDBDialect

2、以下测试方法测试通过

@Override

@SuppressWarnings("unchecked")

public List findByTaskName(String name) {

EntityManagerFactory emf= (EntityManagerFactory) SpringUtils.getBean("entityManagerFactory"); //SpringUtils需要自己指定了....

EntityManager em=emf.createEntityManager();

String querysql="select * from prism_task where taskname=?";

Query query=em.createNativeQuery(querysql);

query.setParameter(1, name);

List tasklist=(List)query.getResultList();

em.close();

return tasklist;

}