How to use JPA Query to insert data into db?

Question

I have a problem with my prepared statement but i can not figure out where the error is. I am trying to insert a URI link into the database.

 @Repository
 public interface LoggerDao extends CrudRepository<Logger, Long> {
 @Query("select t from Logger t where t.user.id=?#{principal.id}")
 List<Logger> findAll();

@Modifying
@Query(value = "insert into Logger t (t.redirect, t.user.id) VALUES (:insertLink,?#{principal.id})", nativeQuery = true)
@Transactional
void logURI(@Param("insertLink") String insertLink);

Error

    2017-03-11 19:52:59.157  WARN 65154 --- [nio-8080-exec-8] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 42001, SQLState: 42001
2017-03-11 19:52:59.157 ERROR 65154 --- [nio-8080-exec-8] o.h.engine.jdbc.spi.SqlExceptionHelper   : Syntax error in SQL statement "INSERT INTO LOGGER T[*] (T.REDIRECT, T.USER.ID) VALUES (?,?) "; expected "., (, DIRECT, SORTED, DEFAULT, VALUES, SET, (, SELECT, FROM"; SQL statement:
insert into Logger t (t.redirect, t.user.id) VALUES (?,?) [42001-190]
2017-03-11 19:52:59.181 ERROR 65154 --- [nio-8080-exec-8] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessResourceUsageException: could not prepare statement; SQL [insert into Logger t (t.redirect, t.user.id) VALUES (?,?)]; nested exception is org.hibernate.exception.SQLGrammarException: could not prepare statement] with root cause

org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "INSERT INTO LOGGER T[*] (T.REDIRECT, T.USER.ID) VALUES (?,?) "; expected "., (, DIRECT, SORTED, DEFAULT, VALUES, SET, (, SELECT, FROM"; SQL statement:
insert into Logger t (t.redirect, t.user.id) VALUES (?,?) [42001-190]
    at org.h2.engine.SessionRemote.done(SessionRemote.java:624) ~[h2-1.4.190.jar:1.4.190]
    at org.h2.command.CommandRemote.prepare(CommandRemote.java:68) ~[h2-1.4.190.jar:1.4.190]
    at org.h2.command.CommandRemote.<init>(CommandRemote.java:45) ~[h2-1.4.190.jar:1.4.190]
    at org.h2.engine.SessionRemote.prepareCommand(SessionRemote.java:494) ~[h2-1.4.190.jar:1.4.190]
    at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1188) ~[h2-1.4.190.jar:1.4.190]
    at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:72) ~[h2-1.4.190.jar:1.4.190]
    at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:276) ~[h2-1.4.190.jar:1.4.190]
    at org.apache.tomcat

You cannot use a WHERE clause directly with INSERT statement. What are you trying to achieve? To me the statement doesn't make sense. — VimalKumar
– VimalKumar, Commented Mar 11, 2017 at 19:39
@JackFlamp I have an update statement but since there is nothing in the field to update it does not execute. — Arthur Decker
– Arthur Decker, Commented Mar 11, 2017 at 19:46
@VimalKumar i am trying to save a URI name into the database — Arthur Decker
– Arthur Decker, Commented Mar 11, 2017 at 19:47
@ARTHURDECKER Since INSERT INTO adds new records (rows) into the table it doesn't make sense to use the WHERE clause. Either you want to add a new record, or update one. — Jack Flamp
– Jack Flamp, Commented Mar 11, 2017 at 20:13

Arthur Decker · Accepted Answer · 2017-03-11 22:23:37Z

34

I managed to solve the issue. I added an id to the parameters so that i can pass in the id of the user, using Principal in the controller.

@Repository
public interface LoggerDao extends CrudRepository<Logger, Long> {
    @Query("select t from Logger t where t.user.id=?#{principal.id}")
    List<Logger> findAll();

    @Modifying
    @Query(value = "insert into Logger (redirect,user_id) VALUES (:insertLink,:id)", nativeQuery = true)
    @Transactional
    void logURI(@Param("insertLink") String insertLink, @Param("id") Long id);

answered Mar 11, 2017 at 22:23

Arthur Decker

1,2313 gold badges19 silver badges47 bronze badges

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

3 Comments

Stunner Over a year ago

if we are writing the whole query with @query tag , then why use JPA?. Plain jdbc will also do the same. Is there any specific reason you used spring jpa instead of plain jdbc or hibernate?

jones-chris Over a year ago

@Stunner JPA doesn't support Insert statements: baeldung.com/spring-data-jpa-query#3-inserts. That's a fair point about just using plan JDBC if you have to write an insert statement in native SQL. However, IMO downside to that is that it would result in a separate JDBC class so you would have your database operations split between a CrudRepsitory interface and a JDBC DAO class, which IMO would be confusing to developers working on the code base.

Book Of Zeus Over a year ago

Much cleaner than @SqlInsert imo.

user3448327 · Accepted Answer · 2017-08-17 22:45:36Z

3

There is a way to do inserts using obj (not native) queries (using @Query & @Modifying) but it depends on the db you're using. Below worked for me in Oracle (using Dual table):

@Repository
public interface DualRepository extends JpaRepository<Dual,Long> {
    @Modifying
    @Query("insert into Person (id,name,age) select :id,:name,:age from Dual")
    public int modifyingQueryInsertPerson(@Param("id")Long id, @Param("name")String name, @Param("age")Integer age);
}

Here's a link which shows at the bottom which db's support select stmts without a from clause : http://modern-sql.com/use-case/select-without-from

answered Aug 17, 2017 at 22:45

user3448327

2 Comments

Tigran Babajanyan Over a year ago

you can not use insert statement if nativeQuery is enabled(default enabled)

Bill Naylor Over a year ago

This helped me, it was the first time I had been using @Query with an insert, I was getting a pretty useless java.lang.NoSuchMethodException. Adding the @Modifying annotation solved this issue. (The database I'm using is Oracle 12c).

Collectives™ on Stack Overflow

How to use JPA Query to insert data into db?

2 Answers 2

3 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related