Note that Insert could not be done with Spring Data JDBC

I was addicted to Spring Data JDBC, which I was reviewing somehow, so I made a note. (Although the base Spring boot is a little old ...)

environment

What I tried to do

I just tried to insert an object (insert a record) in a table with Spring Data JDBC

Cause of addiction

Apparently, Spring Data JDBC issues SQL when the field with @ID is an object type, Insert when it is null, and update when it is not null. The PK of the table must be Auto Generated.

reference https://stackoverflow.com/questions/50371775/why-does-spring-data-jdbc-not-save-my-car-object

Try running with sample code

object

Users.java


public class Users {
  @Id
  private Integer user_id;
  private String password;
  private String notes;

service

SampleService.java


@Transactional
@Service
public class SampleService {
  @Autowired
  private UserRepository dao;
//Abbreviation
//It's a very crude code, but ...
  public void add(Integer id) {
    LocalDateTime time = LocalDateTime.now();
    Users user = new Users(id, "password", time.toString());
    logger.info(user.toString());
    Users inserted = dao.save(user);
  }

For test execution

SampleServiceTest.java


@RunWith(SpringRunner.class)
@SpringBootTest
public class SampleServiceTest {
  
  @Autowired
  private SampleService service;
  
//Update when you run this
  @Test
  public void test1() {
    service.add(3);
  }
  
//When you execute this, Insert
  @Test
  public void test2() {
    service.add(null);
  }
}

test1 () runtime

2019-11-19 18:41:47.275  INFO 37316 --- [           main] org.example.test.service.SampleService   : userid: 3, note: 2019-11-19T18:41:47.274

Postgre SQL trace at that time

2019-11-19 09:41:47.326 UTC [167] LOG:  execute <unnamed>: BEGIN
2019-11-19 09:41:47.334 UTC [167] LOG:  execute <unnamed>: UPDATE users SET user_id = $1, password = $2, notes = $3 WHERE user_id = $4
2019-11-19 09:41:47.334 UTC [167] DETAIL:  parameters: $1 = '3', $2 = 'password', $3 = '2019-11-19T18:41:47.274', $4 = '3'

test2 () runtime

2019-11-19 18:41:47.355  INFO 37316 --- [           main] org.example.test.service.SampleService   : userid: null, note: 2019-11-19T18:41:47.355

Postgre SQL trace at that time

2019-11-19 09:41:47.368 UTC [167] LOG:  execute <unnamed>: BEGIN
2019-11-19 09:41:47.368 UTC [167] LOG:  execute <unnamed>: INSERT INTO users (password, notes) VALUES ($1, $2)
	RETURNING *
2019-11-19 09:41:47.368 UTC [167] DETAIL:  parameters: $1 = 'password', $2 = '2019-11-19T18:41:47.355'

at the end

For the time being, let's change the DDL and raise the version of Spring boot. With Postgres 10, you can make it Auto Generated. https://qiita.com/nuko_yokohama/items/7d0d5525bcefaa3332ce

Oh, I referred to here for the SQL trace settings of PostgreSQL. https://www.kakiro-web.com/postgresql/postgresql-sql-log-system.html

Recommended Posts

Note that Insert could not be done with Spring Data JDBC
Story when migration could not be done
Please note that Spring Boot + Tomcat 8.5.8 cannot be used!
Compatibility of Spring JDBC and MyBatis with Spring Data JDBC (provisional)
Solved the problem that the test could not be executed from the command line with spring-boot-starter-test
Spring Data JDBC Preview
The story that the forced update could not be implemented
The story that the request parameter from the iPhone application could not be obtained successfully with the Servlet
Problem that yarn install could not be done from npm repository created by Azure DevOps
The case that @Autowired could not be used in JUnit5
Use Spring JDBC with Spring Boot
The story of stopping because the rails test could not be done
Correspond to "error that basic authentication does not pass" in the test code "The story that could not be done"
The case that "apt-get update" defined in "Dockerfile" could not be done at the time of "docker-compose build"
How to use Spring Data JDBC
About the problem that the server can not be started with rails s
Settings that should be done when operating a production environment with Rails
The story that the Servlet could not be loaded in the Java Web application
JSESSIONID could not be assigned to the URL when using Spring Security