java - 无法使用 SpringMVC 通过 Hibernate 将 ItemDetail 列表 ie.List (OBJECT) 插入 MySQL 数据库字段

Question

我正在尝试创建一个名为“invoicedto”的表，其中有一个包含 ItemDetail(POJO 对象)列表的字段。

我需要澄清以下内容:1.我们可以在MySQL数据库的字段中插入一个对象列表吗？ 如果可以的话怎么办？ MYSQL 中列的数据类型应该是什么？2.如果我们不能插入列表？有没有其他方法可以实现这种场景？

如果在下面附上我的代码:

Controller :

@Controller
public class InvoiceController {

@Autowired
InvoiceService invoiceService;

public void setInvoiceService(InvoiceService invoiceService) {
    this.invoiceService=invoiceService;
}


@RequestMapping("/invoice/add")
public ModelAndView addInvoice(){
    Map<String,Object> businessDataMap=new HashMap<>();
    ModelAndView modelAndView=new ModelAndView();
    String invoiceNumber = invoiceService.generateInvoiceNumber();
    businessDataMap.put("invoiceNumber", invoiceNumber);
    modelAndView.addAllObjects(businessDataMap);
    modelAndView.setViewName("add_invoice");
    return modelAndView;
}
}

服务:

package com.company.greeninvoice.service;

import java.sql.Date;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

import org.apache.commons.lang3.StringUtils;
import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.company.greeninvoice.dao.InvoiceDao;
import com.company.greeninvoice.dto.Customer;
import com.company.greeninvoice.dto.Invoice;
import com.company.greeninvoice.entity.ItemDetail;

@Service
public class InvoiceServiceImpl implements InvoiceService{

public static final String EMPTY_STRING = "";

public static final String INVOICE_CODE = "INV";

public static final String SEPERATOR = "-";

public static final String DEFAULT_INVOICE_NUMBER = "0000";

public static final String INVOICE_FORMATTER = "%04d";

@Autowired
InvoiceDao invoiceDao;

public void setInvoiceDao(InvoiceDao invoiceDao){
     this.invoiceDao = invoiceDao;
  }

@Transactional
@Override
public String generateInvoiceNumber() {
    insertRecord();
    String invoiceNumber = EMPTY_STRING;
    return invoiceNumber;
}

private void insertRecord() {
    Invoice invoice=new Invoice();
    LocalDate todayDate=LocalDate.now();
    String yearCode = String.valueOf(todayDate.getYear()%1000);
    String monthCode = LocalDate.now().getMonth().name().substring(0, 3);
    String invoiceNumber=INVOICE_CODE+SEPERATOR+yearCode+monthCode+SEPERATOR+"0001";
    invoice.setInvoiceNumber(invoiceNumber);
    Customer customer=new Customer();
    customer.setCustomerName("VENKAT");
    customer.setMale(true);
    invoice.setCustomerDetails(customer);
    invoice.setTotalAmount((float) 25.02);
    invoice.setInvoiceDate(Date.valueOf(todayDate));
    List<ItemDetail> itemdetailsList=new ArrayList<>();
    ItemDetail itemDetail=new ItemDetail();
    itemDetail.setSerialNumber("1");
    itemDetail.setItemDescription("Pencil");
    itemDetail.setItemQuantity(2);
    itemDetail.setItemRate(25);
    itemDetail.setAmount(50);
    itemdetailsList.add(itemDetail);
    invoice.setItemdetailsList( itemdetailsList);
    invoiceDao.addInvoice(invoice);
}
}

DAO:

package com.company.greeninvoice.dao;

import java.util.List;

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.company.greeninvoice.dto.Invoice;

@Repository
public class InvoiceDaoImpl implements InvoiceDao{

private static final Logger logger = 
LoggerFactory.getLogger(InvoiceDaoImpl.class);

@Autowired
SessionFactory sessionFactory;

public void setSessionFactory(SessionFactory sessionFactory){
     this.sessionFactory = sessionFactory;
  }

@Override
public void addInvoice(Invoice invoice) {
    Session session = this.sessionFactory.getCurrentSession();
    session.persist(invoice);
    logger.info("Invoice saved successfully, Invoice Details="+invoice);
}

}

型号:

1.InvoiceModel:[我应该使用,ElementCollection注解吗？]

问题:列表 未插入。

package com.company.greeninvoice.dto;

import java.io.Serializable;
import java.sql.Date;
import java.util.ArrayList;
import java.util.List;

import javax.persistence.CollectionTable;
import javax.persistence.ElementCollection;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

import org.hibernate.annotations.CollectionId;

import com.company.greeninvoice.entity.ItemDetail;

import lombok.Data;
import lombok.Setter;

@Data
@Entity
@Setter
@Table(name="invoiceDto")
public class Invoice implements Serializable{

@Id
private String invoiceNumber;

private Date invoiceDate;

private Customer customerDetails;

@ElementCollection
private List<ItemDetail> itemdetailsList;

private float totalAmount;
}

实体 POJO 类:

package com.company.greeninvoice.entity;

import java.io.Serializable;

import javax.persistence.Embeddable;
import javax.persistence.Entity;
import javax.persistence.Id;
import lombok.Data;

@Data
@Entity
public class ItemDetail  implements Serializable{
@Id
 private String serialNumber;

 private String itemDescription;

 private int itemQuantity;

 private float itemRate;

 private float amount;

 }

Eclipse 中的 Tomcat 控制台:

Hibernate: insert into invoiceDto (customerDetails, invoiceDate, totalAmount, invoiceNumber) values (?, ?, ?, ?)
Hibernate: insert into Invoice_itemdetailsList (Invoice_invoiceNumber, itemdetailsList) values (?, ?)
Jan 21, 2018 5:50:40 PM org.apache.catalina.core.StandardWrapperValve invoke
SEVERE: Servlet.service() for servlet [dispatcher] in context with path [/greeninvoice] threw exception [Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute statement; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute statement] with root cause
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'itemdetailsList' in 'field list'
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
	at java.lang.reflect.Constructor.newInstance(Unknown Source)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
	at com.mysql.jdbc.Util.getInstance(Util.java:387)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:939)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3878)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3814)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2478)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2625)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2551)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861)
	at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2073)
	at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2009)
	at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5094)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1994)
	at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
	at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
	at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:187)
	at org.hibernate.engine.jdbc.batch.internal.NonBatchingBatch.addToBatch(NonBatchingBatch.java:62)
	at org.hibernate.persister.collection.AbstractCollectionPersister.recreate(AbstractCollectionPersister.java:1311)
	at org.hibernate.action.internal.CollectionRecreateAction.execute(CollectionRecreateAction.java:67)
	at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:463)
	at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:349)
	at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:350)
	at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:56)
	at org.hibernate.internal.SessionImpl.flush(SessionImpl.java:1222)
	at org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:425)
	at org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.beforeTransactionCommit(JdbcTransaction.java:101)
	at org.hibernate.engine.transaction.spi.AbstractTransactionImpl.commit(AbstractTransactionImpl.java:177)
	at org.springframework.orm.hibernate4.HibernateTransactionManager.doCommit(HibernateTransactionManager.java:555)
	at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:757)
	at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:726)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:478)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:272)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:95)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)
	at com.sun.proxy.$Proxy26.generateInvoiceNumber(Unknown Source)
	at com.company.greeninvoice.controller.InvoiceController.addInvoice(InvoiceController.java:35)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	at org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:215)
	at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:132)
	at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104)
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:749)
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:689)
	at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:83)
	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:938)
	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:870)
	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:961)
	at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:852)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:635)
	at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:837)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:742)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:198)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:504)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:81)
	at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:650)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342)
	at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:803)
	at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
	at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:790)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1459)
	at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
	at java.lang.Thread.run(Unknown Source)

Folder Structure of my project

InvoiceDto Table structure in MySQL

Answer 1

自 Invoice和 ItemDetail注释为 @Entity ，您应该使用 JPA 关系注释(包括 @ManyToMany, @ManyToOne, @OneToMany, @OneToOne )而不是使用 @ElementCollection .我假设你的发票包括很多项目，所以我会使用 @OneToMany关于 List<ItemDetail> 的注释像这样:

@OneToMany
private List<ItemDetail> itemdetailsList;

如果你还想用@ElementCollection在 List<ItemDetail>你必须使用 @Embeddable关于 ItemDetail 的注释

@Data
@Embeddable
public class ItemDetail  implements Serializable{
 @Id
 private String serialNumber;

 private String itemDescription;

 private int itemQuantity;

 private float itemRate;

 private float amount;

 }