springboot jpa 使用标准的动态 sql

springboot jpa 使用标准的动态 sql

 

第一种写法。在 service 层使用 @PersistenceUnit 注解，注入一个 EntityManagerFactory
    @PersistenceUnit
    private EntityManagerFactory emFactory; 

    @RequestMapping(value = "/sqltest", method = RequestMethod.POST) 
    public List<Car> sqltest(@RequestBody String brand){

        EntityManager em = emFactory.createEntityManager();  // 获得 EntityManager

        String sql = "select * from car where brand = :brand";

        List<Car> re = (List<Car>) em.createNativeQuery(sql, Car.class)
                .setParameter("brand", brand).getResultList();

        // 操作完数据库，需要显式关闭 entityManager，否则 entityManager 会一直保持这个数据库连接

        em.close();

        return re;

    }

 

查询结果集为包含 Map 的 List 的写法：

String sql = "select * from car where brand = :brand";

EntityManager em = emFactory.createEntityManager();

Query query = em.createNativeQuery(sql);    // import javax.persistence.Query;

query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);

List<Map<String, Object>> re = query.setParameter("brand", brand).getResultList();

 

注意，这种写法如果不关闭entityManager，em.close(); ，项目运行时间长了就会报错。

因为 entityManager 会一直保持数据库连接，不断开。

随着项目不断运行，mysql 的连接数(Threads_connected)会一直上涨：

javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: Unable to acquire JDBC Connection

Caused by: org.apache.tomcat.jdbc.pool.PoolExhaustedException: [pool-9-thread-1] Timeout: Pool empty. Unable to fetch a connection in 30 seconds, none available[size:100; busy:100; idle:0; lastwait:30000].

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data source rejected establishment of connection,  message from server: "Too many connections"

---------------------------------------------------

第二种写法。在 service 层使用 @PersistenceContext 注解注入 EntityManager：

这种写法，不需要操作完数据库显式的用 em.close() 关闭 entityManager。推荐使用这种写法。

    @PersistenceContext
    private EntityManager em;

    @RequestMapping(value = "/sqltest", method = RequestMethod.POST)
    public List<Car> sqltest(@RequestBody String brand){
        String sql = "select * from car where brand = :brand";

        List<Car> re = (List<Car>) em.createNativeQuery(sql, Car.class)
                .setParameter("brand", brand).getResultList();

        return re;
    }

---------------------------------------------------

第三种写法。在 dao 层使用 @Query 注解，不过这种方法只能获得 List<Object[]>

@Query( value = "SELECT p.shop_id, sum(p.price) FROM product p GROUP BY p.shop_id", nativeQuery=true)
List<Object[]> getSumPriceGroupByShopId();