How to insert into db in spring-data?

Question

I want to make a request that inserts data into my database. The table has 4 columns: ID_DOCUMENT (PK), ID_TASK, DESCRIPTION, FILEPATH

Entity

... 
@Column(name = "ID_TASK")
private Long idTask;


@Column(name = "DESCRIPTION")
private String description;

@Column(name = "FILEPATH")
private String filepath;
...

Repository

@Modifying
@Query("insert into TaskDocumentEntity c (c.idTask, c.description, c.filepath) values (:id,:description,:filepath)")
public void insertDocumentByTaskId(@Param("id") Long id,@Param("description") String description,@Param("filepath") String filepath);

Controller

@RequestMapping(value = "/services/tasks/addDocument", method = RequestMethod.POST)
@ResponseBody
public void set(@RequestParam("idTask") Long idTask,@RequestParam("description") String description,@RequestParam("filepath") String filepath){

    //TaskDocumentEntity document = new TaskDocumentEntity();
    taskDocumentRepository.insertDocumentByTaskId(idTask,descriere,filepath);
}

When I run my test, I get this error: Caused by: org.hibernate.hql.ast.QuerySyntaxException: expecting OPEN, found 'c' near line 1, column 32 [insert into TaskDocumentEntity c (c.idTask, c.descriere, c.filepath) values (:id,:descriere,:filepath)] I tried to remove the alias c, and still doesn`t work.

Remove your `insertDocumentByTaskId`. In your controller create an instance of `TaskDocumentEntity` set the properties and call the `save` method. The whole point of JPA is that you don't have to write your queries anymore... — M. Deinum, Oct 07 '15 at 10:23
Please post the code for your repository class not only the method declaration and also post your spring configuration class/xml. — dubonzi, Oct 07 '15 at 14:32

score 11 · Accepted Answer · answered Oct 07 '15 at 10:53

Spring data provides out of the box save method used for insertion to database - no need to use @Query. Take a look at core concepts of springData (http://docs.spring.io/spring-data/jpa/docs/current/reference/html/#repositories.core-concepts)

thus in your controller just create object TaskDocumentEntity and pass it to repository

@RequestMapping(value = "/services/tasks/addDocument", method = RequestMethod.POST)
@ResponseBody
public void set(@RequestParam("idTask") Long idTask,@RequestParam("description") String description,@RequestParam("filepath") String filepath){

// assign parameters to taskDocumentEntity by constructor args or setters
        TaskDocumentEntity document = new TaskDocumentEntity(idTask,descriere,filepath);
        taskDocumentRepository.save(document);
    }

I tried this also, but doesn`t persist, no changes the database (succesful test) — que1326, Oct 07 '15 at 11:12
It works now, I didn`t inject the service in the test class, and didn`t pass any params for the request, anyway I `ve rewritten the controller, thanks a lot guys !!! — que1326, Oct 07 '15 at 14:41

score 5 · Answer 2 · answered Aug 17 '17 at 22:42

There is a way to do this 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);
}

So in your case, it would be (if Oracle):

@Modifying
@Query("insert into TaskDocumentEntity (idTask,description,filepath) select :idTask,:description,:filepath from Dual")
public void insertDocumentByTaskId(@Param("idTask") Long id,@Param("description") String description,@Param("filepath") String filepath)

I'm not sure which db you're using, 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

How to insert into db in spring-data?

2 Answers2

Linked