29.08.2021, 17:14. Показов 1251. Ответов 0
Небольшой проект Spring MVC JpaRepository (MySQL) не работает delete
При сохранении рекорда в таблицу TEST одновременно пишется рекорд в таблицу FILE_UPLOAD
Конфигурация
| Java |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
| @Configuration
@EnableTransactionManagement
@ComponentScan({ "ru.javawebinar.topjavaout.configuration" })
@PropertySource(value = { "classpath:db/mysql.properties" })
//@PropertySource(value = {"classpath:db/postgres.properties"})
@EnableJpaRepositories(basePackages = "ru.javawebinar.topjavaout.repository")
public class JpaConfiguration {
@Autowired
private Environment environment;
@Bean
public DataSource dataSourceJpa() {
DriverManagerDataSource dataSourceJpa = new DriverManagerDataSource();
dataSourceJpa.setDriverClassName(environment.getRequiredProperty("jpa.driverClassName"));
dataSourceJpa.setUrl(environment.getRequiredProperty("jpa.url"));
dataSourceJpa.setUsername(environment.getRequiredProperty("jpa.username"));
dataSourceJpa.setPassword(environment.getRequiredProperty("jpa.password"));
return dataSourceJpa;
}
@Bean
public LocalSessionFactoryBean sessionFactory() {
LocalSessionFactoryBean sessionFactory = new LocalSessionFactoryBean();
sessionFactory.setDataSource(dataSourceJpa());
sessionFactory.setPackagesToScan(new String[]{"ru.javawebinar.topjavaout.model"});
sessionFactory.setHibernateProperties(hibernateProperties());
return sessionFactory;
}
@Bean
public LocalContainerEntityManagerFactoryBean entityManagerFactory() throws NamingException {
LocalContainerEntityManagerFactoryBean factoryBean = new LocalContainerEntityManagerFactoryBean();
factoryBean.setDataSource(dataSourceJpa());
factoryBean.setPackagesToScan(new String[] { "ru.javawebinar.topjavaout.model" });
factoryBean.setJpaVendorAdapter(jpaVendorAdapter());
return factoryBean;
}
/*
* Provider specific adapter.
*/
@Bean
public JpaVendorAdapter jpaVendorAdapter() {
HibernateJpaVendorAdapter hibernateJpaVendorAdapter = new HibernateJpaVendorAdapter();
return hibernateJpaVendorAdapter;
}
private Properties hibernateProperties() {
Properties hibernateProperties = new Properties();
hibernateProperties.put("hibernate.dialect", environment.getRequiredProperty("hibernate.dialect"));
hibernateProperties.put("hibernate.show_sql", environment.getRequiredProperty("hibernate.show_sql"));
hibernateProperties.put("hibernate.format_sql", environment.getRequiredProperty("hibernate.format_sql"));
hibernateProperties.put("hibernate.hbm2ddl.auto", environment.getRequiredProperty("hibernate.hbm2ddl.auto"));
return hibernateProperties;
}
@Bean
@Autowired
public PlatformTransactionManager transactionManager(EntityManagerFactory emf) {
JpaTransactionManager txManagerJpa = new JpaTransactionManager();
txManagerJpa.setEntityManagerFactory(emf);
return txManagerJpa;
}
} |
|
Две сущности
| Java |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
| @Entity
@Table(name = "TEST")
public class Test implements Serializable {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer id;
@Column(name = "NAME", unique = true, nullable = false)
private String name;
@OneToMany(targetEntity = FileUpload.class, mappedBy = "test", cascade = CascadeType.ALL, fetch = FetchType.LAZY)
private Set<FileUpload> fileUploadSet = new HashSet<>();
...
} |
|
| Java |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
| @Entity
@Table(name = "FILE_UPLOAD")
public class FileUpload implements Serializable {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer id;
@Column(name = "TITLE")
private String title;
@Column(name = "LOCATION")
private String location;
@Column(name = "SIZE")
private Integer size;
@Column(name = "TYPE")
private String type;
@Column(name = "FILE_NAME")
private String file_name;
@Column(name = "TEST_ID")
private Integer test_id;
@ManyToOne(fetch = FetchType.LAZY, cascade = CascadeType.ALL)
@JoinColumn(name = "document_id", referencedColumnName = "id", insertable = false, updatable = false)
private Document document;
@ManyToOne(fetch = FetchType.LAZY, cascade = CascadeType.ALL)
@JoinColumn(name = "test_id", referencedColumnName = "id", insertable = false, updatable = false)
private Test test;
} |
|
Таблицы в базе
| SQL |
1
2
3
4
5
| CREATE TABLE TEST
(
id BIGINT PRIMARY KEY NOT NULL AUTO_INCREMENT,
name VARCHAR(30)
); |
|
| SQL |
1
2
3
4
5
6
7
8
9
10
11
| CREATE TABLE FILE_UPLOAD
(
id BIGINT PRIMARY KEY NOT NULL AUTO_INCREMENT,
title VARCHAR(30),
location VARCHAR(30),
SIZE BIGINT,
TYPE VARCHAR(30),
file_name VARCHAR(100),
test_id BIGINT,
FOREIGN KEY (test_id) REFERENCES TEST(id) ON DELETE CASCADE
); |
|
В контроллере
| Java |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
| @RequestMapping(value = {"/delete-test/{id}"}, method = RequestMethod.GET)
public String deleteTest(@PathVariable Integer id,
HttpServletRequest request,
RedirectAttributes redirectAttributes) {
List<FileUpload> fileUploadList = fileUploadService.listFiles();
String rootDirectory = request.getSession().getServletContext().getRealPath(userMethood.configProper().get("file.path"));
for (int i = 0; i < fileUploadList.size(); i++)
if (id.equals(fileUploadList.get(i).getTest_id())) {
String fileName = fileUploadList.get(i).getFile_name();
deleteFile(rootDirectory, fileName);
}
Test test = testService.findById(id);
testService.deleteTestById(id);
return "redirect:/tests";
} |
|
Далее сервисы и репозиторий
| Java |
1
2
3
4
5
6
7
8
| public interface TestServiceRepo {
List<Test> listTests();
Test findById(Integer id);
void saveTest(Test test);
void deleteTestById(Integer id);
void updateTest(Test test);
} |
|
| Java |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
| @Service("testServiceRepo")
@Transactional
public class TestServiceImplRepo implements TestServiceRepo {
@Autowired
private TestRepository testRepository;
public Test findById(Integer id) {
return testRepository.findById(id);
}
public List<Test> listTests() {
return testRepository.findAll();
}
public void deleteTestById(Integer id) {
testRepository.deleteById(id);
}
public void saveTest(Test test) {
testRepository.save(test);
}
public void updateTest(Test test) {
Test entity = testRepository.findById(test.getId());
entity.setName(test.getName());
entity.setFile_name(test.getFile_name());
}
} |
|
| Java |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
| @Repository("TestRepository")
@Transactional(readOnly = true)
public interface TestRepository extends JpaRepository<Test, Long> {
@Query("select b from Test b where b.id = :id")
Test findById(@Param("id") Integer id);
@Modifying
@Transactional
@Query("delete from Test b where b.id = :id")
void deleteById(@Param("id") Integer id);
@Modifying(clearAutomatically = true)
@Transactional
@Query("update Test u set u.name = :name where u.id = :id")
void updateById(String name, Integer id);
} |
|
При сохранении рекорда в таблицу TEST одновременно пишется рекорд в таблицу FILE_UPLOAD
Редактируется нормально
Не работает delete если в таблице FILE_UPLOAD имеется рекорд привязанный к таблице TEST через test_id.
Я полагал что должно работать каскадное удаление при удалении рекорда из таблицы TEST. Но оно не работает.
Ошибка типа
| Java |
1
| 17:03:25.595 [http-nio-8080-exec-9] ERROR o.h.e.jdbc.spi.SqlExceptionHelper - Cannot delete or update a parent row: a foreign key constraint fails (`topjavaout`.`file_upload`, CONSTRAINT `FKt15myxnh372qu5mwcqtln9ufe` FOREIGN KEY (`test_id`) REFERENCES `test` (`id`)) |
|
Интересно то что при переходе на базу Postgres такой проблемы нет.
Подскажите пожалуйста что я делаю не так?
(
