How to Execute a Native SQL Script in JPA/Hibernate

How can I execute a native SQL script in JPA/Hibernate?

Wrap your query with begin end block. Like

EntityManager manager = getEntityManager(); 
Query q = manager.createNativeQuery("BEGIN " + sqlScript + " END;");
q.executeUpdate();

Execute sql native query from two entity using jpa or hibernate

In SQL should be something like:

update salesOrder

set text_review = true where customer_id = ?

and id in (select salesorder_id from salesorderline where product_id = ?)

In JPA I don't think you need the select statement.

How to run an imported sql script in JPA using H2?

I parsed the UploadedFile file's InputStream with a Scanner, which messed up the SQL.

Execute native sql with hibernate

This should help you.

Session session = Hibernate.util.HibernateUtil.getSessionFactory().openSession();
session.beginTransaction();
String sql = String.format("INSERT INTO products (name,cost) VALUES('%s',%s);",product.getName(), product.getCost());
session.createSQLQuery(sql).executeUpdate();
session.getTransaction().commit();
session.close();

How to run a native SQL query in Spring without an entity and a JPA Repository?

You should be using JDBC instead of an Entity Manager. Under the JPA uses JDBC but it requires defined entites to work. JDBC allows you to manage the connection and run the raw SQL queries.

Here's a link for how to do it in Spring:
https://spring.io/guides/gs/relational-data-access/#_store_and_retrieve_data

How to execute Native SQL server query without resultset using Spring Data JPA

By default @Query is considered to be a select query. To make it be treated as a modifying query, use @Modifying annotation:

Indicates a method should be regarded as modifying query.

So, you should write your repository the following way:

public interface PartRepo extends PagingAndSortingRepository<Part,Long> {
    @Modifying
    @Query(value = "SET IDENTITY_INSERT PART ON", nativeQuery = true)
    public void enableInsertIdentity();

    @Modifying
    @Query(value = "SET IDENTITY_INSERT PART OFF", nativeQuery = true)
    public void disableInsertIdentity();
}

How to execute a native SQL query having INTERVAL clause through Spring Boot JPA?

I believe, if you restructured the query like so you will get the correct behavior using hibernate:

@Repository
public interface MoneyRepository extends PagingAndSortingRepository<Money, MoneyPK> {
    List<Money> findAllBySenderIs(String senderAppName);

    @Query(value = "SELECT * FROM Money a WHERE a.sender=:sender AND a.last_ts between (SYSTIMESTAMP - INTERVAL 1 DAY * :minusDays) AND SYSTIMESTAMP ORDER BY a.last_ts DESC", nativeQuery = true)
    List<Money> findAllMessagesForLastXDays(String sender, int minusDays);
}

The added bonus is that this would also work in MariaDB if you exchanged SYSTIMESTAMP for CURRENT_TIMESTAMP.

---

Related Topics