wen jpa用原生SQL查询数据库
一、提前准备有2张表student,和teacher表
对应要联合查询的DTO实例，创建表SQL在上个博客页面里有， 数据内容如下：
学生表student
老师表teacher
这里使用的SQL语句是：
select a.id as id, a.name as studentName, b.name as teacherName, a.grade as grade, b.course as course from student as a, teacher as b where a.id = b.id
先来个DTO，没有@entity哦
package com.msxf.monitor.dto;
import java.io.Serializable;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import lombok.Data;
@Data
public class StudentTeacherDTO implements Serializable{
private static final long serialVersionUID = 1L;
private Integer id;
private String studentName;
private String teacherName;
private Integer grade;
private String course;
}
二、创建EntityManagerDAO工具类
package com.msxf.monitor.repository;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import javax.transaction.Transactional;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;
import com.msxf.monitor.dto.StudentTeacherDTO;
@Repository
public class EntityManagerDAO {
@PersistenceContext
private EntityManager entityManager;
@Transactional
public List<StudentTeacherDTO> list(){
String sql = "select a.id as id, a.name as studentName, b.name as teacherName, a.grade as grade, b.course as course from student as a, teacher as b where a.id = b.id";
SQLQuery sqlQuery = entityManager.createNativeQuery(sql).unwrap(SQLQuery.class);
Query query = sqlQuery.setResultTransformer(Transformers.aliasToBean(StudentTeacherDTO.class));
List<StudentTeacherDTO> list = query.getResultList();
entityManager.clear();
return list;
}
@Transactional
public List<StudentTeacherDTO> list1(){
String sql = "select a.id as id, a.name as studentName, b.name as teacherName, a.grade as grade, b.course as course from student as a, teacher as b where a.id = b.id";
Query nativeQuery = entityManager.createNativeQuery(sql, StudentTeacherDTO.class);
List<StudentTeacherDTO> list = nativeQuery.getResultList();
entityManager.clear();
return list;
}
@Transactional
public List<Object[]> list2(){
String sql = "select a.id as id, a.name as studentName, b.name as teacherName, a.grade as grade, b.course as course from student as a, teacher as b where a.id = b.id";
Query nativeQuery = entityManager.createNativeQuery(sql);
List rows = nativeQuery.getResultList();
for (Object row : rows) {
Object[] cells = (Object[]) row;
for (int i = 0; i < cells.length; i++) {
System.out.println("cells[" + i + "]: " + cells[i]);
}
System.out.println("----------");
}
return rows;
}
@Transactional
public List<Map> list3(){
String sql = "select a.id as id, a.name as studentName, b.name as teacherName, a.grade as grade, b.course as course from student as a, teacher as b where a.id = b.id";
Query query = entityManager.createNativeQuery(sql);
query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
List rows = query.getResultList();
for (Object obj : rows) {
Map row = (Map) obj;
System.out.println("id = " + row.get("id"));
System.out.println("studentName = " + row.get("studentName"));
System.out.println("teacherName = " + row.get("teacherName"));
System.out.println("grade = " + row.get("grade"));
System.out.println("course = " + row.get("course"));
System.out.println("------------------");
}
return rows;
}
}
上面对应4个list() 方法
list()说明：
将查询出来的结果通过 Hibernate 来转换生成对应DTO，默认jpa就是调用的 Hibernate 来实现的，但是有出现对应方法过期，应该算是最好的选择，毕竟DTO对象可以根据自己的需求随意编写。
list1()说明：
这里用的全是jpa的接口完成的，是新的方法，只是能编译过，不能进行调用，一调用就报对应的DTO不是entity如果在对应DTO加上@entity就会生产到对应数据库里，不是要的目标，反正我是没有调用成功。
list2()说明：
这里这个返回值Object[]，其实这个方法返回的是一个List的下包含的数组，每一行返回值都是一个数组，数组值是Object可以强转为对应的数据类型
list3()说明：
这里这个返回值Map，其实这个方法返回的是一个List的下包含的Map，每一行返回值都是一个Map对象，Map对象的Key为SQL语句查询的属性，Map对象对应Key的值为 查询得到该行的对应属性的值。
有个对应的controller来调用他们
package com.msxf.monitor.controller;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import com.msxf.monitor.dto.StudentTeacherDTO;
import com.msxf.monitor.repository.EntityManagerDAO;
@Controller
@RequestMapping("/t1")
public class T1Controller {
private static final Logger logger = LoggerFactory.getLogger(T1Controller.class);
@Autowired
private EntityManagerDAO entityManagerDAO;
@ResponseBody
@RequestMapping("/getAll")
public String getAll() {
List<StudentTeacherDTO> list = entityManagerDAO.list();
logger.info("list: " + list);
//List<StudentTeacherDTO> list1 = entityManagerDAO.list1();
//logger.info("list1: " + list1);
List<Object[]> list2 = entityManagerDAO.list2();
logger.info("list2: " + list2);
List<Map> list3 = entityManagerDAO.list3();
logger.info("list3MAP: " + list3);
return "t1/getAll";
}
}
list1()方法不能调。
三、还有就是TestRepository extends JpaRepository然后在接口对应方法里使用@Query注解
接口对应方法里使用@Query注解，使用nativeQuery为true，实现原生的SQL查询。
@Query注解的用法(Spring Data JPA)
1. 一个使用@Query注解的简单例子
@Query(value = "select name,author,price from Book b where b.price>?1 and b.price<?2")
List<Book> findByPriceRange(long price1, long price2);
2. Like表达式
@Query(value = "select name,author,price from Book b where b.name like %:name%")
List<Book> findByNameMatch(@Param("name") String name);
3. 使用Native SQL Query
所谓本地查询，就是使用原生的sql语句（根据数据库的不同，在sql的语法或结构方面可能有所区别）进行查询数据库的操作。
@Query(value = "select * from book b where b.name=?1", nativeQuery = true)
List<Book> findByName(String name);
4. 使用@Param注解注入参数
@Query(value = "select name,author,price from Book b where b.name = :name AND b.author=:author AND b.price=:price")
List<Book> findByNamedParam(@Param("name") String name, @Param("author") String author,
@Param("price") long price);
5. SPEL表达式(使用时请参考最后的补充说明)
"#{#entityName}"值为"Book"对象对应的数据表名称(book)。
public interface BookQueryRepositoryExample extends Repository<Book, Long>{
@Query(value = "select * from #{#entityName} b where b.name=?1", nativeQuery = true)
List<Book> findByName(String name);
}
四、一点课外知识
EntityManager接口有三种方法来建立原生查询
9.3.原生查询
EJB QL中富有大量的查询语句并且基本上能符合你的绝大多数的查询需求.有时,你想要使用特定厂商提供的数据库上的专有能力.
实体管理服务提供了一个方法来建立原生的SQL查询并且映射他们到你的对象上.原生查询能反回实体,栏位值,或者两者的组合.EntityManager接口有三种方法来建立原生查询:一种返回标量值,一种是返回实体类型,最后一种是定义一个复杂的结果集,它能映射到多个实体的混合和标量值.
你可以进行JDBC的连接通过javax.sql.DataSource,使用@Resource注入和执行你的SQL语句.要意识到你所做的改变不会被当前的持久化上下文所反映.
9.3.1. 标量原生查询
Query createNativeQuery(String sql)
这将建立一个原生查询返回一个标量结果.它需要一个参数:你的原生SQL.它执行并且返回结果集同EJB QL相同的形式,返回标量值.
9.3.2.简单的实体原生查询
Query createNativeQuery(String sql, Class entityClass)
一个简单的原生查询通过一个SQL语句和隐式的映像到一个实体,映射元数据为基础的一个实体.它认为原生查询的结果集中的栏将完全匹配实体的O/R映射.原生SQL查询的映射实体的确定通过entityClass 参数:
Query query = manager.createNativeQuery(
“SELECT p.phone_PK, p.phone_number, p.type
FROM PHONE AS p”, Phone.class
);
实体的所有属性被列出:
9.3.3.复杂的原生查询
这个实体管理方法允许你有一个复杂的映射为原生SQL.你可以同时返回多个实体和标量栏.mappingName 参数参考@javax.persistence.SqlResultSetMapping定义.这个批注用来定义一个怎能样查询原生结果的钓子到O/R模型.如果返回的栏位名与批注映射的属性不匹配,你可以提代一个字段到栏位的映射为他们,使用@javax.persistence.FieldResult :
package javax.persistence;
public @interface SqlResultSetMapping {
String name( );
EntityResult[] entities( ) default {};
ColumnResult[] columns( ) default {};
}
public @interface EntityResult {
Class entityClass( );
FieldResult[] fields( ) default {};
String discriminatorColumn( ) default “”;
}
public @interface FieldResult {
String name( );
String column( );
}
public @interface ColumnResult {
String name( );
}
原来如此，list1()方法为什么不行的原因在ci。
