PSQLException：错误：尝试基于类的投影时，列"person"中的空值违反了非空约束

Question

尽管遵循了关于基于类的投影的文档，但我无法解决我的问题。我总是收到以下错误消息：

PSQLException: ERROR: null value in column "person" violates not-null constraint

这是我的实体类

@Entity
@Table(name="incomeoutgo", schema = "public")
public class IncomeOutgo extends AbstractPersistable<Long> {
@NotNull
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name ="id")
private Long id;
@NotNull
@Column(name="dayofweek")
private Date dayofweek;
@NotNull
@Size(min= 2, max= 100)
@Column(name="position")
private String position;
@NotNull
@Size(min = 5, max = 50)
@Column(name ="person")
private String person;
@NotNull
@Size(min= 1)
@Column(name="income")
private double income;
@NotNull
@Size(min= 2)
@Column(name="outgo")
private double outgo;
}

我的存储库是这样的吗：

@Repository
public interface ChooseMonthRepository extends JpaRepository<IncomeOutgo, Date> {
@Query(value = "SELECT dayofweek, person, position, income, outgo FROM IncomeOutgo WHERE dayofweek >= :start_dayofmonth AND dayofweek <= :end_dayofmonth", nativeQuery = true)
List<DateChoiceDTO> findAllByDate(@Param("start_dayofmonth") Date start_dayofmonth, @Param("end_dayofmonth") Date end_dayofmonth);
}

这是我的基于类的投影

@AllArgsConstructor
@NoArgsConstructor
@Data
public class DateChoiceDTO {
Date dayofweek;
String person;
String position;
double income;
double outgo;
}

服务类

@RequiredArgsConstructor
@Service
@Transactional(readOnly=true)
public class DateChoiceService {
private final ChooseMonthRepository incomeOutgoDateChoice;
public List<?> getAllForDateChoice(Date start_dayofmonth) {
Date lastDayOfMonth=java.util.Calendar.getInstance().getTime();
return incomeOutgoDateChoice.findAllByDate(start_dayofmonth, lastDayOfMonth);
}
}

最后但同样重要的是我的控制器

@RequiredArgsConstructor
@Controller
@Validated
@RequestMapping(value = "/")
public class DateChoiceController {
private static final String DATE_CHOICE_VIEW = "DateChoice";
private final DateChoiceService dateChoiceService;
@GetMapping("/")
public String homeInit(Model model) {
return DATE_CHOICE_VIEW;
}
@PostMapping(value = "/")
public String addUser(@ModelAttribute("incomeoutgo") @Valid DateChoiceDTO dateChoice, Model model, @NotNull BindingResult bindingResult) {
if(bindingResult.hasErrors()) {
return DATE_CHOICE_VIEW;
}
List<?> incomeOutgoList = dateChoiceService.getAllForDateChoice(dateChoice.getDayofweek());
model.addAttribute(DATE_CHOICE_VIEW, incomeOutgoList);
return DATE_CHOICE_VIEW;
}
}

我不明白为什么人物栏突然发挥作用？

也许有人能告诉我我在这里做错了什么？

Answer 1

由于框架不确定如何处理元组，您将得到以下异常。

No converter found capable of converting from type AbstractJpaQuery$TupleConverter$TupleBackedMap to type DateChoiceDTO 

当您在查询中使用构造结果集映射时，您必须使用类的完全限定名称，如下所示：

select new a.b.cSomeDto(t.property1, t.property2) from table t

以下是我如何修复它：

@Query(value = "SELECT new com.example.samplejdbctemplatecall.DateChoiceDTO" +
"(table.dayofweek, table.person, table.position, table.income, table.outgo) " +
"FROM IncomeOutgo table WHERE table.dayofweek >= :start_dayofmonth AND table.dayofweek <= :end_dayofmonth")
List<DateChoiceDTO> findAllByDateSecond(@Param("start_dayofmonth") Date start_dayofmonth, @Param("end_dayofmonth") Date end_dayofmonth);

但是，正如Simon所提到的，如果您从其他地方触发了插入查询，则需要首先修复此问题，因为这是一个完整的检索操作，您面临的错误与数据插入有关。整个工作样本(+界面投影(：

@RequestMapping("/test")
@RestController
class IncomeOutgoController {
private final ChooseMonthRepository chooseMonthRepository;
private int days = 0;
@Autowired
IncomeOutgoController(ChooseMonthRepository chooseMonthRepository) {
this.chooseMonthRepository = chooseMonthRepository;
}
@GetMapping("/interface-projection")
public Object interfaceProjection() {
Calendar calendar = Calendar.getInstance();
calendar.add(Calendar.MONTH, -1);
Date date = calendar.getTime();
calendar = Calendar.getInstance();
return composeResponse("interface-projection", chooseMonthRepository.findAllByDate(date, calendar.getTime()));
}
@GetMapping("/constructor-projection")
public Object constructorProjection() {
Calendar calendar = Calendar.getInstance();
calendar.add(Calendar.MONTH, -1);
Date date = calendar.getTime();
calendar = Calendar.getInstance();
return composeResponse("constructor-projection", chooseMonthRepository.findAllByDateSecond(date, calendar.getTime()));
}

@GetMapping("/store")
public Object addRandomData() {
String randomUuid = UUID.randomUUID().toString();
chooseMonthRepository.save(new IncomeOutgo(null, Calendar.getInstance().getTime(), "random-position: " + randomUuid,
randomUuid, new Random(5000).nextDouble(), new Random(500).nextDouble()));
return "success";
}
private Map<String, Object> composeResponse(String key, Object value) {
final Map<String, Object> response = new HashMap<>();
response.put(key, value);
return response;
}
}
@AllArgsConstructor
@NoArgsConstructor
@Entity
@Table(name = "incomeoutgo", schema = "public")
class IncomeOutgo extends AbstractPersistable<Long> {
@NotNull
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "id")
private Long id;
@NotNull
@Column(name = "dayofweek")
private Date dayofweek;
@NotNull
@Size(min = 2, max = 100)
@Column(name = "position")
private String position;
@NotNull
@Size(min = 5, max = 50)
@Column(name = "person")
private String person;
@NotNull
@Size(min = 1)
@Column(name = "income")
private double income;
@NotNull
@Size(min = 2)
@Column(name = "outgo")
private double outgo;
}
@Repository
interface ChooseMonthRepository extends JpaRepository<IncomeOutgo, Date> {
@Query(value = "SELECT dayofweek, person, position, income, outgo FROM IncomeOutgo WHERE dayofweek >= :start_dayofmonth AND dayofweek <= :end_dayofmonth", nativeQuery = true)
List<DateChoiceDTOInterface> findAllByDate(@Param("start_dayofmonth") Date start_dayofmonth, @Param("end_dayofmonth") Date end_dayofmonth);

@Query(value = "SELECT new com.example.samplejdbctemplatecall.DateChoiceDTO" +
"(table.dayofweek, table.person, table.position, table.income, table.outgo) " +
"FROM IncomeOutgo table WHERE table.dayofweek >= :start_dayofmonth AND table.dayofweek <= :end_dayofmonth")
List<DateChoiceDTO> findAllByDateSecond(@Param("start_dayofmonth") Date start_dayofmonth, @Param("end_dayofmonth") Date end_dayofmonth);
}
@AllArgsConstructor
@NoArgsConstructor
@Getter
@Setter
class DateChoiceDTO {
Date dayofweek;
String person;
String position;
Double income;
Double outgo;
}
interface DateChoiceDTOInterface {
Date getDayOfWeek();
String getPerson();
String getPosition();
Double getIncome();
Double getOutgo();
}

application.properties

spring.jpa.hibernate.ddl-auto=update
spring.datasource.url=jdbc:postgresql://172.17.0.2:5432/postgres
spring.datasource.username=postgres
spring.datasource.password=mysecretpassword
spring.jpa.database-platform=org.hibernate.dialect.PostgreSQLDialect
spring.jpa.show-sql=true