Почему при записи в json возникает ошибка?
2020-03-11 17:49:44.191 WARN 3436 --- [nio-8080-exec-2] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 335544652, SQLState: 21000
2020-03-11 17:49:44.192 ERROR 3436 --- [nio-8080-exec-2] o.h.engine.jdbc.spi.SqlExceptionHelper : GDS Exception. 335544652. multiple rows in singleton select
2020-03-11 17:49:44.310 ERROR 3436 --- [nio-8080-exec-2] 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.DataIntegrityViolationException: could not execute query; SQL [select cancelledc0_.id as id1_1_, cancelledc0_.REG_CAPTION as reg_capt2_1_, cancelledc0_.REG_CODE as reg_code3_1_, cancelledc0_.VERSION as version4_1_, (select (case when lic.changing_doc_date is null then lic.action_date else lic.changing_doc_date end) from LAWYER_IDENTITY_CARD lic, common_base_doc where lic.id = common_base_doc.id) as formula1_, (select lic.identity_card_number from LAWYER_IDENTITY_CARD lic, common_base_doc where lic.id = common_base_doc.id) as formula2_, (select lic.is_not_returned from LAWYER_IDENTITY_CARD lic, common_base_doc where lic.id = common_base_doc.id) as formula3_, (select (case when secondsel.lawyer_status = 4 then 30 when secondsel.lawyer_status = 2 then 20 when secondsel.lawyer_status = 3 then 10 else secondsel.lawyer_status end) from lawyers secondsel) as formula4_, (select l.PERSON_FIO from LAWYERS l where l.lawyer_status not in(1, 5)) as formula5_ from LAWYERS cancelledc0_ where cancelledc0_.VERSION=(select max(cancelledc1_.VERSION) from LAWYERS cancelledc1_ where cancelledc0_.id=cancelledc1_.id)]; nested exception is org.hibernate.exception.DataException: could not execute query] with root cause
org.firebirdsql.gds.GDSException: multiple rows in singleton select
at org.firebirdsql.gds.impl.wire.AbstractJavaGDSImpl.readStatusVector(AbstractJavaGDSImpl.java:2103) ~[jaybird-jdk18-2.2.15.jar:2.2.15-JDK_1.8 (build: tag=v2.2.15 date=201904061030)]
at org.firebirdsql.gds.impl.wire.AbstractJavaGDSImpl.receiveResponse(AbstractJavaGDSImpl.java:2053) ~[jaybird-jdk18-2.2.15.jar:2.2.15-JDK_1.8 (build: tag=v2.2.15 date=201904061030)]
at org.firebirdsql.gds.impl.wire.AbstractJavaGDSImpl.iscDsqlFetch(AbstractJavaGDSImpl.java:1318) ~[jaybird-jdk18-2.2.15.jar:2.2.15-JDK_1.8 (build: tag=v2.2.15 date=201904061030)]При помощи hql запроса создаю одну таблицу из нескольких, но при запуске приложения возникает ошибка.
import java.util.List;
@Transactional
public interface CancelledCertificatesRepository extends JpaRepository<CancelledCertificates, Integer> {
List<CancelledCertificates> findAll();
@Query("select e from Lawyers e where e.version = (select max(b.version) from Lawyers b where e.id = b.id)")
List<CancelledCertificates> findByCancelledCertificates();
}@Service
public class CancelledCertificatesServiceImpl implements CancelledCertificatesService {
private CancelledCertificatesRepository cancelledCertificatesRepository;
@Autowired
public void setCancelledCertificatesRepository(CancelledCertificatesRepository cancelledCertificatesRepository) {
this.cancelledCertificatesRepository = cancelledCertificatesRepository;
}
@Override
@Transactional(readOnly = true)
public List<CancelledCertificates> findAll() {
return cancelledCertificatesRepository.findAll();
}
@Override
@Transactional(readOnly = true)
public List<CancelledCertificates> findForExport() {
List<CancelledCertificates> cancelled_certificates = cancelledCertificatesRepository.findByCancelledCertificates();
return cancelled_certificates;
}
}public interface CancelledCertificatesService {
List<CancelledCertificates> findAll();
List<CancelledCertificates> findForExport();
}@JsonAutoDetect(fieldVisibility = JsonAutoDetect.Visibility.ANY)
@Entity
@Table(name = "LAWYERS")
public class CancelledCertificates {
public CancelledCertificates() {
}
@Id
@Formula("(select cdb.id from common_base_doc cdb where cbd.lawyer_id = lawyer_id)")
private Long id;
@Formula("(select (case when lic.changing_doc_date is null then lic.action_date else lic.changing_doc_date end)" +
" from LAWYER_IDENTITY_CARD lic, common_base_doc where lic.id = common_base_doc.id)")
private Date changing_doc_date;
@Formula("(select lic.identity_card_number from LAWYER_IDENTITY_CARD lic, common_base_doc where lic.id = common_base_doc.id)")
private String identity_card_number;
// @Formula("(select PERSON_FIO from LAWYERS where STATUS_ON_PANEL = 'Прекращен')")
@Formula("(select l.PERSON_FIO from LAWYERS l where l.lawyer_status not in(1, 5))")
private String person_fio;
@Column(name = "LAWYER_ID", insertable = false, updatable = false)
private Long lawyer_id;
@Formula("(select lic.is_not_returned from LAWYER_IDENTITY_CARD lic, common_base_doc where lic.id = common_base_doc.id)")
private Integer is_not_returned;
@Formula("(select (case when secondsel.lawyer_status = 4 then 30 when secondsel.lawyer_status = 2 then 20 " +
"when secondsel.lawyer_status = 3 then 10 else secondsel.lawyer_status end) from lawyers secondsel)")
private Integer lawyer_status;
@Column(name = "REG_CAPTION", insertable = false, updatable = false)
private String reg_caption;
@Column(name = "REG_CODE", insertable = false, updatable = false)
private String reg_code;
/*"select cbd.id,
(case
when secondsel.lawyer_status = 4 then 30
when secondsel.lawyer_status = 2 then 20
when secondsel.lawyer_status = 3 then 10
else secondsel.lawyer_status end) as reason_id,
lic.is_not_returned as not_returned,secondsel.reg_caption,secondsel.reg_code,lic.identity_card_number as cert_number,
(case
when lic.changing_doc_date is null then lic.action_date
else lic.changing_doc_date end) as cert_date,
secondsel.id as lawyer_id,secondsel.person_fio as fio
from lawyers secondsel, (select id, max(version) as version from lawyers group by id) firstsel
join common_base_doc cbd on cbd.lawyer_id = firstsel.id
join LAWYER_IDENTITY_CARD lic on lic.id = cbd.id
where
secondsel.id = firstsel.id
AND secondsel.version= firstsel.version
and secondsel.lawyer_status not in (1,5)"*/
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public Date getChanging_doc_date() {
return changing_doc_date;
}
public void setChanging_doc_date(Date changing_doc_date) {
this.changing_doc_date = changing_doc_date;
}
public String getIdentity_card_number() {
return identity_card_number;
}
public void setIdentity_card_number(String identity_card_number) {
this.identity_card_number = identity_card_number;
}
public String getPerson_fio() {
return person_fio;
}
public void setPerson_fio(String person_fio) {
this.person_fio = person_fio;
}
public Integer getIs_not_returned() {
return is_not_returned;
}
public void setIs_not_returned(Integer is_not_returned) {
this.is_not_returned = is_not_returned;
}
public Integer getLawyer_status() {
return lawyer_status;
}
public void setLawyer_status(Integer lawyer_status) {
this.lawyer_status = lawyer_status;
}
public String getReg_caption() {
return reg_caption;
}
public void setReg_caption(String reg_caption) {
this.reg_caption = reg_caption;
}
public String getReg_code() {
return reg_code;
}
public void setReg_code(String reg_code) {
this.reg_code = reg_code;
}
}В сущности есть sql запрос
@Controller
public class MainController {
@Autowired
private LawyersService lawyerService;
// @Autowired
// private OOrderChangeFIOService oOrderChangeFIOService;
@Autowired
private ForeignLawyerService foreignLawyerService;
@Autowired
private CancelledCertificatesService cancelledCertificatesService;
@Value("${C:\\Users\\Senko_KL\\IdeaProjects\\converttabletojson}")
private String exportDirPath;
//@GetMapping(value = "/run")
@PostMapping("/run")
public String export() {
try {
Date date = new Date();
SimpleDateFormat sdf = new SimpleDateFormat("dd-MM-yyyy-Hms");
final String dbFile = sdf.format(date) + "-lawyers.json";
ObjectMapper objectMapper = new ObjectMapper();
objectMapper.enable(SerializationFeature.INDENT_OUTPUT);
final JsonNodeFactory factory = JsonNodeFactory.instance;
final ObjectNode node = factory.objectNode();
List<Lawyers> lawyers = lawyerService.findForExport();
// List<OOrderChangeFIO> changeFioFilters = oOrderChangeFIOService.findForExport();
List<ForeignLawyer> foreignLawyers = foreignLawyerService.findForExport();
List<CancelledCertificates> cancelledCertificates = cancelledCertificatesService.findForExport();
ArrayNode nodeLawyers = objectMapper.valueToTree(lawyers);
// ArrayNode nodeChangeFioFilters = objectMapper.valueToTree(changeFioFilters);
ArrayNode nodeForeignLawyers = objectMapper.valueToTree(foreignLawyers);
ArrayNode nodeCancelledCertificates = objectMapper.valueToTree(cancelledCertificates);
node.set(Lawyers.class.getAnnotation(Table.class).name(), nodeLawyers);
// node.set(OOrderChangeFIO.class.getAnnotation(Table.class).name(), nodeChangeFioFilters);
node.set(ForeignLawyer.class.getAnnotation(Table.class).name(), nodeForeignLawyers);
node.set(CancelledCertificates.class.getAnnotation(Table.class).name(), nodeCancelledCertificates);
objectMapper.writeValue(new File(exportDirPath, dbFile), node);
} catch (JsonGenerationException e) {
e.printStackTrace();
} catch (JsonMappingException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return "redirect:/";
}
}Ошибка
Error starting ApplicationContext. To display the conditions report re-run your application with 'debug' enabled.
2020-03-11 16:17:16.572 ERROR 6232 --- [ main] o.s.boot.SpringApplication : Application run failed
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'entityManagerFactory' defined in class path resource [com/github/steed777/config/DataSourceConfig.class]: Invocation of init method failed; nested exception is org.hibernate.cfg.CannotForceNonNullableException: Identifier property [private java.lang.Long com.github.steed777.model.CancelledCertificates.id] cannot contain formula mapping [(select cdb.id from common_base_doc cdb where cbd.lawyer_id = lawyer_id)]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1803) ~[spring-beans-5.2.1.RELEASE.jar:5.2.1.RELEASE]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:595) ~[spring-beans-5.2.1.RELEASE.jar:5.2.1.RELEASE]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:517) ~[spring-beans-5.2.1.RELEASE.jar:5.2.1.RELEASE]"select cbd.id,
(case
when secondsel.lawyer_status = 4 then 30
when secondsel.lawyer_status = 2 then 20
when secondsel.lawyer_status = 3 then 10
else secondsel.lawyer_status end) as reason_id,
lic.is_not_returned as not_returned,secondsel.reg_caption,secondsel.reg_code,lic.identity_card_number as cert_number,
(case
when lic.changing_doc_date is null then lic.action_date
else lic.changing_doc_date end) as cert_date,
secondsel.id as lawyer_id,secondsel.person_fio as fio
from lawyers secondsel, (select id, max(version) as version from lawyers group by id) firstsel
join common_base_doc cbd on cbd.lawyer_id = firstsel.id
join LAWYER_IDENTITY_CARD lic on lic.id = cbd.id
where
secondsel.id = firstsel.id
AND secondsel.version= firstsel.version
and secondsel.lawyer_status not in (1,5)"Как изменил сущность:
@JsonAutoDetect(fieldVisibility = JsonAutoDetect.Visibility.ANY)
@Entity
@Table(name = "LAWYERS")
public class CancelledCertificates {
public CancelledCertificates() {
}
@Id
// @Formula("(select cdb.id from common_base_doc cdb where cbd.lawyer_id = lawyer_id)")
private Long id;
@Formula("(select (case when lic.changing_doc_date is null then lic.action_date else lic.changing_doc_date end)" +
" from LAWYER_IDENTITY_CARD lic, common_base_doc where lic.id = common_base_doc.id)")
private Date changing_doc_date;
@Formula("(select lic.identity_card_number from LAWYER_IDENTITY_CARD lic, common_base_doc where lic.id = common_base_doc.id)")
private String identity_card_number;
// @Formula("(select PERSON_FIO from LAWYERS where STATUS_ON_PANEL = 'Прекращен')")
@Formula("(select l.PERSON_FIO from LAWYERS l where l.lawyer_status not in(1, 5))")
private String person_fio;
@Column(name = "ID", insertable = false, updatable = false)
private Long lawyer_id;
@Formula("(select lic.is_not_returned from LAWYER_IDENTITY_CARD lic, common_base_doc where lic.id = common_base_doc.id)")
private Integer is_not_returned;
@Formula("(select (case when secondsel.lawyer_status = 4 then 30 when secondsel.lawyer_status = 2 then 20 " +
"when secondsel.lawyer_status = 3 then 10 else secondsel.lawyer_status end) from lawyers secondsel)")
private Integer lawyer_status;
@Column(name = "REG_CAPTION", insertable = false, updatable = false)
private String reg_caption;
@Column(name = "REG_CODE", insertable = false, updatable = false)
private String reg_code;
@Column(name = "VERSION", insertable = false, updatable = false)
private Integer version;