值'0000-00-00'不能表示为java.sql.Date

我正在从事一些需要从DB中提取数据的项目,我使用Spring MVC从DB构建模型以选择数据。

这是我的JSP页面的问题:

<form action="result" method="get" >
<table>
<thead>
<tr>
<th>Date to select:</th>
<th>Name to select:</th>
<th>Type to select:</th>
</tr>
</thead>

<tbody>
<tr>
<td><form:select  path="listOfDates">
<form:option value="NONE"> --SELECT--</form:option>
<form:options items="${listOfDates}"></form:options>
</form:select>
</td>  
<td><form:select  path="listOfInstitutionsNames">
<form:option value="NONE"> --SELECT--</form:option>
<form:options items="${listOfInstitutionsNames}"></form:options>
</form:select>
</td>
<td>
<form:select  path="listOfInstitutionsTypes">
<form:option value="NONE"> --SELECT--</form:option>
<form:options items="${listOfInstitutionsTypes}"></form:options>
</form:select>
</td>
</tr>
</tbody>

<tfoot>
<tr>
<td><input type="submit" value="Извлечь"/></td>
</tr>
</tfoot>

</table>
</form>

如您所见,我尝试从标签库中使用。
问题:
但是当它使用此控制器准备我的模型时<form:select>Spring

@Controller
public class HomeController{ 

    @Autowired
    private ControllerSupportClass controllerSupportClass; 


        @RequestMapping(value="/search", method=RequestMethod.GET)
        public String search(Model model) {
            List<Date> listOfDates = controllerSupportClass.findAllDatesForm();
            List<String> listOfInstitutionsNames = controllerSupportClass.findAllInstitutionsForm();
            List<String> listOfInstitutionsTypes = controllerSupportClass.findAllTypesForm();
            model.addAttribute("listOfInstitutionsTypes", listOfInstitutionsTypes);
            model.addAttribute("listOfInstitutionsNames", listOfInstitutionsNames);
            model.addAttribute("listOfDates", listOfDates);
            return "search";    
        }


        @RequestMapping(value ="/result", method=RequestMethod.GET)
        public String SecondActionPage(@RequestParam String particularDate, 
                                       @RequestParam String nameOfInstitution, 
                                       @RequestParam String typeName,
                                       Model model) throws Exception {


                if(particularDate !="" && nameOfInstitution.trim() !="" && typeName.trim()=="") {                   
                    controllerSupportClass.findWithDateAndName(nameOfInstitution, particularDate, model);                   
                } else if(particularDate.trim() !="" && nameOfInstitution.trim() =="" && typeName.trim() !="") {                    
                    controllerSupportClass.findWithAddedDateAndType(typeName, particularDate, model);                   
                } else if(particularDate.trim() !="" && nameOfInstitution.trim() =="" && typeName.trim() ==""){         
                    controllerSupportClass.findWithAddedDate(particularDate, model);    
                } else if(particularDate.trim() !="" && nameOfInstitution.trim() !="" && typeName.trim() !="") {
                    throw new Exception("Search by choose all parameters is not exceptable");   
                } else {    
                    throw new Exception("You didn't put any search parameters");    
                }           
            return "search";
        }

}

它给了我一个这样的错误:

WARN : org.springframework.web.servlet.PageNotFound - 在 DispatcherServlet 中找不到带有 URI [/controller/] 的 HTTP 请求的映射,名称为'appServlet' Hibernate:选择不同的creationda0_。DATE_ID为DATE1_0_,creationda0_。CHILD_ADMISSION_DATE CHILD2_0_,creationda0_。CHILD_GO_SCHOOL_DATE CHILD3_0_,creationda0_。PARTICULAR_DATE为PARTICULAR4_0_,creationda0_。版本VERSION0_来自 CREATION_DATE creationda0_ WARN : org.hibernate.util.JDBCExceptionReporter - SQL Error: 0, SQLState: S1009 ERROR: org.hibernate.util.JDBCExceptionReporter - Value '0000-00-00' 不能表示为 java.sql.Date Jun 19, 2013 3:26:53 PM org.apache.catalina.core.StandardWrapperValve invoke SEVERE: Servlet.service() for servlet [appServlet] 在上下文中与路径 [/controller] 抛出异常 [请求处理失败;嵌套异常是 org.hibernate.exception。GenericJDBCException: can can execute query] ,根本原因 java.sql.SQLException: Value '0000-00-00' 不能表示为 java.sql.Date

如果你需要我的实体类,我在这里使用,但我使用注释将其从SQL转换为单位,因为我的理解:Datejava.util@TemporalDate

@Entity
@Table(name="CREATION_DATE")
public class CreationDate implements Serializable {


    private int dateId;

        @Id
        @GeneratedValue(strategy=IDENTITY)
        @Column(name="DATE_ID")
        public int getDateId() {
            return dateId;
        }

        public void setDateId(int dateId) {
            this.dateId = dateId;
        }


    private int version;

        @Version
        @Column(name="VERSION")
        public int getVersion() {
            return version;
        }

        public void setVersion(int version) {
            this.version = version;
        }


    private Date particularDate;

        @Temporal(TemporalType.DATE)
        @DateTimeFormat(pattern="yyyy-MM-dd")
        @Column(name="PARTICULAR_DATE")
        public Date getParticularDate() {
            return particularDate;
        }

        public void setParticularDate(Date particularDate) {
            this.particularDate = particularDate;
        }


    private Date childGoSchoolDate;

        @Temporal(TemporalType.DATE)
        @DateTimeFormat(pattern="yyyy-MM-dd")
        @Column(name="CHILD_GO_SCHOOL_DATE")
        public Date getChildGoSchoolDate() {
            return childGoSchoolDate;
        }

        public void setChildGoSchoolDate(Date childGoSchoolDate) {
            this.childGoSchoolDate = childGoSchoolDate;
        }


    private Date childAdmissionDate;

        @Temporal(TemporalType.DATE)
        @DateTimeFormat(pattern="yyyy-MM-dd")
        @Column(name="CHILD_ADMISSION_DATE")
        public Date getChildAdmissionDate() {
            return childAdmissionDate;
        }

        public void setChildAdmissionDate(Date childAdmissionDate) {
            this.childAdmissionDate = childAdmissionDate;
        }


}

假设数据类型转换的问题是因为 MVC 使用 ,但实际类型是 。StringDate


答案 1

在MySql中,它被认为是一个有效的日期,但它不能被重新命名为java.sql.Date。'0000-00-00'

您可以使用在 date 为 的情况下返回 NULL 的查询,否则返回实际值:'0000-00-00'

SELECT
  CASE WHEN `date`!='0000-00-00' THEN `date` END new_date
FROM
  yourtable

或者,您可以向数据源连接字符串中添加以下内容:

zeroDateTimeBehavior=convertToNull

和日期,因为将自动转换为 NULL。'0000-00-00'


答案 2

将以下语句与 JDBC-mysql 协议附加:

"?zeroDateTimeBehavior=convertToNull&autoReconnect=true&characterEncoding=UTF-8&characterSetResults=UTF-8"

例如:

jdbc:mysql://localhost/infra?zeroDateTimeBehavior=convertToNull&autoReconnect=true&characterEncoding=UTF-8&characterSetResults=UTF-8

推荐