Alter PostgreSQL sequence using Hibernate native sql query

Question

My problem seems to be very simple but I have hard time resolving it.
I want to alter an PostgreSQL sequence using Hibernate via native sql query (other solutions are also welcomed) using next code:

    Query query = getSession()
        .createSQLQuery("ALTER SEQUENCE users_id_seq RESTART WITH ?")
        .setInteger(0, 1);
    query.executeUpdate();

But I am getting this error:

Hibernate: 
    ALTER SEQUENCE users_id_seq RESTART WITH ?
hibernate.engine.jdbc.spi.SqlExceptionHelper - SQL Error: 0, SQLState: 42601
hibernate.engine.jdbc.spi.SqlExceptionHelper - ERROR: syntax error at or near "$1"

Denis de Bernardy · Accepted Answer · 2013-10-22 12:06:29Z

1

Best I'm aware, you cannot prepare that statement.

If hibernate allows it, emulate the prepared statement, instead of sending it to the server. If not, sanitize the variable and issue the final statement directly.

Alternatively, wrap it in a function with dynamic SQL:

http://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

answered Oct 22, 2013 at 12:06

Denis de Bernardy

79.1k14 gold badges138 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

vadim Over a year ago

I know is not a good practice, but probably I will go with the final statement directly, of course after sanitizing the input parameter.

Denis de Bernardy Over a year ago

Fwiw, altering a sequence is a bit weird too. :-)

vadim Over a year ago

Actually I perform some DBUnit testing, that's why I need it for.

Collectives™ on Stack Overflow

Alter PostgreSQL sequence using Hibernate native sql query

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related