Java 如何使用 JPA Query 将数据插入数据库？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/42739566/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to use JPA Query to insert data into db?
提问by Arthur Decker
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.
我的准备好的语句有问题，但我不知道错误在哪里。我正在尝试将 URI 链接插入到数据库中。
@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
采纳答案by Arthur Decker
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.
我设法解决了这个问题。我在参数中添加了一个 id，以便我可以在控制器中使用 Principal 传入用户的 id。
@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);
回答by A.P
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):
有一种方法可以使用 obj（不是本机）查询（使用 @Query 和 @Modifying）进行插入，但这取决于您使用的数据库。下面在 Oracle 中为我工作（使用双表）：
@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
这是一个链接，它在底部显示了哪些数据库支持 select stmts 没有 from 子句：http://modern-sql.com/use-case/select-without-from
