I'm trying to create a table named 'invoicedto' in which,I have a field consisting a list of ItemDetail (POJO Object).
我正在尝试创建一个名为'invoicedto'的表,其中包含一个包含ItemDetail(POJO对象)列表的字段。
I need clarification on following: 1.Can we Insert a List of Object in a field in MySQL database? If possible then how to do? What should be my datatype of column in MYSQL? 2.If we cannot insert a list?Is there any other method to achieve this scenario?
我需要澄清以下内容:1。我们可以在MySQL数据库的字段中插入对象列表吗?如果可能那怎么办? MYSQL中我的数据类型应该是什么? 2.如果我们无法插入列表?还有其他方法可以实现这种情况吗?
If have attached my code below:
如果我在下面附上了我的代码:
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;
}
}
Service:
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);
}
}
Model:
1.InvoiceModel:[Should I use,ElementCollection Annotation?]
1.InvoiceModel:[我应该使用ElementCollection Annotation吗?]
Problem:List<ItemDetail> not Inserted.
问题:列表
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;
}
Entity POJO Class:
实体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;
}
Tomcat Console in Eclipse:
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
MySQL中的InvoiceDto表结构
1 个解决方案
#1
2
Since Invoice and ItemDetail are annoatated with @Entity,You should use JPA relationship annotations (which including @ManyToMany, @ManyToOne, @OneToMany, @OneToOne) instead of using @ElementCollection. I assume your invoice includes many items, So i would use @OneToMany annoation on List<ItemDetail> like this:
由于Invoice和ItemDetail是使用@ Entity进行的,因此您应该使用JPA关系注释(包括@ManyToMany,@ ManyToOne,@ OneToMany,@ OneToOne)而不是使用@ElementCollection。我假设你的发票包含很多项目,所以我会在List
@OneToMany
private List<ItemDetail> itemdetailsList;
If you still want to use @ElementCollection on List<ItemDetail> you have to use @Embeddable annoation on ItemDetail
如果您仍想在List
@Data
@Embeddable
public class ItemDetail implements Serializable{
@Id
private String serialNumber;
private String itemDescription;
private int itemQuantity;
private float itemRate;
private float amount;
}
#1
2
Since Invoice and ItemDetail are annoatated with @Entity,You should use JPA relationship annotations (which including @ManyToMany, @ManyToOne, @OneToMany, @OneToOne) instead of using @ElementCollection. I assume your invoice includes many items, So i would use @OneToMany annoation on List<ItemDetail> like this:
由于Invoice和ItemDetail是使用@ Entity进行的,因此您应该使用JPA关系注释(包括@ManyToMany,@ ManyToOne,@ OneToMany,@ OneToOne)而不是使用@ElementCollection。我假设你的发票包含很多项目,所以我会在List
@OneToMany
private List<ItemDetail> itemdetailsList;
If you still want to use @ElementCollection on List<ItemDetail> you have to use @Embeddable annoation on ItemDetail
如果您仍想在List
@Data
@Embeddable
public class ItemDetail implements Serializable{
@Id
private String serialNumber;
private String itemDescription;
private int itemQuantity;
private float itemRate;
private float amount;
}