Executing an SQL command with EntityManager

Question

Is it possible to run an SQL script that doesn't return a resultSet or is not an INSERT or UPDATE type of script?

I'm trying the following:

Query q = entityManager.createNativeQuery("DECLARE @max int;\n" +
        "SELECT @max = MAX(customer_number)+1\n" +
        "FROM organisation\n" +
        "\n" +
        "exec('ALTER SEQUENCE organisation_customer_number_seq RESTART WITH ' + @max)");

...but of course that just creates the query, how to execute it? I can only find getters for different types of resultSets and executeUpdate method for Query. How to just run that query I created above to restart a sequence?

Arnaud · Accepted Answer · 2017-03-02T10:13:42.880

2

Some of the methods you see in Query actually execute the query, e.g Query.getResultList :

getResultList()

Execute a SELECT query and return the query results as an untyped List.

List<Object[]> results = q.getResultList();

UPDATE : in your case the query ends up being an ALTER so as you figured it out, the relevant method will be executeUpdate() which returns no list or value, but the number of affected rows.

int executeUpdate()

Execute an update or delete statement.

int altered =  q.executeUpdate();

edited Mar 02 '17 at 10:13

answered Mar 02 '17 at 09:25

Arnaud

17,229
3
31
44

Yes, but in this case it throws an exception: com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set. – Steve Waters Mar 02 '17 at 09:42
Could you try with `getSingleResult()` instead ? – Arnaud Mar 02 '17 at 09:47
1

That didn't work either. q.executeUpdate(); worked :) – Steve Waters Mar 02 '17 at 10:06
Oh yes I didn't get that your final query will be an `ALTER` not a `SELECT` . – Arnaud Mar 02 '17 at 10:09
You can modify your answer and I'll set it as correct – Steve Waters Mar 02 '17 at 10:09

Executing an SQL command with EntityManager

1 Answers1