SpringDataJPA原生sql查询方式的封装操作

Spring Data JPA提供了多种查询方式，包括基于方法名的查询、@Query注解查询、@NamedQuery查询等。但是在特定情况下，我们可能需要使用原生SQL查询。Spring Data JPA也提供了封装好的方式来实现原生SQL查询。

封装原生SQL查询方式

Spring Data JPA提供了EntityManager接口来进行JPA操作，该接口提供了createQuery()方法来构建原生SQL查询。我们可以在该方法中传入一个String类型的参数，表示查询SQL语句。

但是，直接使用EntityManager来进行原生SQL查询操作，需要手动打开和释放连接，还需要手动进行SQL注入过滤等工作，较为繁琐。因此，Spring Data JPA提供了更高层次的封装，用于简化原生SQL查询操作。

声明方法

Spring Data JPA提供了几种方法声明的方式用于实现原生SQL查询：

1. findBySql()方法：用于查询结果集，返回值类型为List。
2. countBySql()方法：用于查询记录数，返回值类型为Long。
3. executeBySql()方法：用于执行更新操作，返回值类型为int。

这些方法的声明方式与基于方法名的查询声明方式类似，不同之处在于查询SQL语句需要使用@Query注解声明，同时需要指定nativeQuery=true，表示使用原生SQL语句。

public interface UserRepository extends JpaRepository<User, Integer> {

    @Query(value = "SELECT * FROM user WHERE name = ?1", nativeQuery = true)
    List<User> findBySql(String name);

    @Query(value = "SELECT count(*) FROM user WHERE name = ?1", nativeQuery = true)
    Long countBySql(String name);

    @Query(value = "UPDATE user SET name = ?1 WHERE id = ?2", nativeQuery = true)
    int executeBySql(String name, Integer id);
}

注意：在原生SQL查询中，参数的占位符使用?，而不是JPA查询中的:。

使用方法

使用上述方法进行原生SQL查询时，直接调用相应的方法即可。比如：

@Autowired
private UserRepository userRepository;

@Test
public void testFindBySql() {
    List<User> userList = userRepository.findBySql("Tom");
    Assert.assertEquals(1, userList.size());
}

@Test
public void testCountBySql() {
    Long count = userRepository.countBySql("Tom");
    Assert.assertEquals(Long.valueOf(1), count);
}

@Test
@Transactional
public void testExecuteBySql() {
    Integer id = 1;
    int rows = userRepository.executeBySql("Jerry", id);
    Assert.assertEquals(1, rows);

    User user = userRepository.findById(id).get();
    Assert.assertEquals("Jerry", user.getName());
}

注意：当使用executeBySql()方法进行更新操作时，需要添加@Transactional注解，以保证事务的完整性。

示例

下面为两个示例，分别演示如何通过原生SQL查询方式查询订单信息和插入订单信息。

示例1：查询订单信息

假设有一个订单表，包含以下字段：

• id：订单编号，主键
• name：订单名称
• price：订单价格
• user_id：用户编号，外键

我们需要查询所有用户的订单总价，SQL语句如下：

SELECT u.name, SUM(o.price) as total_price FROM `order` o INNER JOIN user u ON o.user_id = u.id GROUP BY u.name

使用SpringDataJPA的EntityManager进行查询：

@PersistenceContext
private EntityManager entityManager;

public List<Object[]> findOrderTotalPrice() {
    String sql = "SELECT u.name, SUM(o.price) as total_price FROM `order` o INNER JOIN user u ON o.user_id = u.id GROUP BY u.name";
    Query query = entityManager.createNativeQuery(sql);
    List<Object[]> resultList = query.getResultList();
    return resultList;
}

使用SpringDataJPA的@Query注解进行查询：

public interface OrderRepository extends JpaRepository<Order, Integer> {

    @Query(value = "SELECT u.name, SUM(o.price) as total_price FROM `order` o INNER JOIN user u ON o.user_id = u.id GROUP BY u.name", nativeQuery = true)
    List<Object[]> findOrderTotalPrice();
}

示例2：插入订单信息

假设要往订单表中插入一条记录，SQL语句如下：

INSERT INTO `order`(name, price, user_id) VALUES('order_1', 100, 1)

使用SpringDataJPA的EntityManager进行插入操作：

@PersistenceContext
private EntityManager entityManager;

@Transactional
public void insertOrder() {
    String sql = "INSERT INTO `order`(name, price, user_id) VALUES('order_1', 100, 1)";
    Query query = entityManager.createNativeQuery(sql);
    query.executeUpdate();
}

使用SpringDataJPA的@Query注解进行插入操作：

@Modifying
@Transactional
@Query(value = "INSERT INTO `order`(name, price, user_id) VALUES('order_1', 100, 1)", nativeQuery = true)
void insertOrder();

注意：当使用@Query注解进行插入、更新、删除操作时，需要同时添加@Modifying和@Transactional注解。