值'0000-00-00'不能表示为java.sql.Date [英] Value '0000-00-00' can not be represented as java.sql.Date
问题描述
我正在研究一些需要从数据库中提取数据的项目，我使用Spring MVC从数据库中构建模型来选择数据。
这里是问题使用我的 JSP页面：
< form action =resultmethod =get> ;
< table>
< thead>
< tr>
th要选择的日期：< / th>
< th>要选择的名称：< / th>
< th>类型以选择：< / th>
< / tr>
< / thead>
< tbody>
< tr>
< td>< form：select path =listOfDates>
< form：option value =NONE> - 选择 - < /形式：选项>
< form：options items =$ {listOfDates}>< / form：options>
< / form：select>
< / td>
< td>< form：select path =listOfInstitutionsNames>
< form：option value =NONE> - 选择 - < /形式：选项>
< form：options items =$ {listOfInstitutionsNames}>< / form：options>
< / form：select>
< / td>
< td>
< form：select path =listOfInstitutionsTypes>
< form：option value =NONE> - 选择 - < /形式：选项>
< form：options items =$ {listOfInstitutionsTypes}>< / form：options>
< / form：select>
< / td>
< / tr>
< / tbody>
< tfoot>
< tr>
< td>< input type =submitvalue =Извлечь/>< / td>
< / tr>
< / tfoot>
< / table>
< / form>
正如您所看到的，我尝试使用< form：select> from Spring 标记库。
问题：<但是当它准备好model 控制器：
@Controller
公共类HomeController {
@Autowired
private ControllerSupportClass controllerSupportClass;
@RequestMapping（value =/ search，method = RequestMethod.GET）
public String search（Model model）{
List< Date> listOfDates = controllerSupportClass.findAllDatesForm（）;
列表< String> listOfInstitutionsNames = controllerSupportClass.findAllInstitutionsForm（）;
列表< String> listOfInstitutionsTypes = controllerSupportClass.findAllTypesForm（）;
model.addAttribute（listOfInstitutionsTypes，listOfInstitutionsTypes）;
model.addAttribute（listOfInstitutionsNames，listOfInstitutionsNames）;
model.addAttribute（listOfDates，listOfDates）;
返回搜索;
$ b @RequestMapping（value =/ result，method = RequestMethod.GET）
public String SecondActionPage（@RequestParam String particularDate，
@RequestParam String nameOfInstitution，
@RequestParam String typeName，
模型模型）抛出异常{
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新的例外（通过选择所有参数搜索不是很好）;
} else {
抛出新的异常（您没有放置任何搜索参数）;
}
返回search;
}
}
它给了我一个像这样的错误：
WARN：org.springframework.web.servlet.PageNotFound - 找不到映射
for带有
名称'appServlet'的Hibernate：选择不同的creationda0_.DATE_ID作为
DATE1_0_，creationda0_.CHILD_ADMISSION_DATE作为CHILD2_0_，
creationda0_.CHILD_GO_SCHOOL_DATE作为CHILD3_0_，
creationda0_.PARTICULAR_DATE as PARTICULAR4_0_，creationda0_.VERSION
as VERSION0_ from CREATION_DATE creationda0_ WARN：
org.hibernate.util.JDBCExceptionReporter - SQL错误：0，SQLState：
S1009错误： org.hibernate.util.JDBCExceptionReporter - 值
'0000-00-00'不能表示为java.sql.Date 2013年6月19日
3:26:53 PM org.apache.catalina。 core.StandardWrapperValve调用
SEVERE：servlet [appServlet]的Servlet.service（）与
路径[/控制器]抛出异常[请求处理失败;嵌套的
异常是org.hibernate.exception.GenericJDBCException：根本原因不能
执行查询] java.sql.SQLException：Value
'0000-00-00'不能表示为java.sql.Date
如果你需要我的实体类，在这里我使用 Date java.util ，但是我使用 @Temporal 注解来将它从SQL转换为单元 Date 据我所知：
@Entity
@Table name =CREATION_DATE）
public class CreationDate实现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;
}
私人日期particularDate;
@Temporal（TemporalType.DATE）
@DateTimeFormat（pattern =yyyy-MM-dd）
@Column（name =PARTICULAR_DATE）
public Date getParticularDate（）{
return specialDate;
}
public void setParticularDate（Date particularDate）{
this.particularDate = particularDate;
}
私人日期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;
}
私人日期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使用 String ，但实际类型是 Date 。
'0000-00-00'被认为是一个有效的日期，但它可以如果日期为'，则可以使用返回NULL的查询。 '，否则为实际值： SELECT
' ='0000-00-00'THEN`date` END new_date
FROM
yourtable
zeroDateTimeBehavior = convertToNull
和日期为'0000-00-00'会自动转换为NULL。
I'm working on some project which needs to extract data from DB and I use Spring MVC to build model from DB for selecting data.
Here is the problem with my JSP page:
<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>
As you can see I try to use <form:select> from Spring tag library.
Question:
but when it's preparing my model with this controller:
@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";
}
}
It gives me an error like this:
WARN : org.springframework.web.servlet.PageNotFound - No mapping found for HTTP request with URI [/controller/] in DispatcherServlet with name 'appServlet' Hibernate: select distinct creationda0_.DATE_ID as DATE1_0_, creationda0_.CHILD_ADMISSION_DATE as CHILD2_0_, creationda0_.CHILD_GO_SCHOOL_DATE as CHILD3_0_, creationda0_.PARTICULAR_DATE as PARTICULAR4_0_, creationda0_.VERSION as VERSION0_ from CREATION_DATE creationda0_ WARN : org.hibernate.util.JDBCExceptionReporter - SQL Error: 0, SQLState: S1009 ERROR: org.hibernate.util.JDBCExceptionReporter - Value '0000-00-00' can not be represented as java.sql.Date Jun 19, 2013 3:26:53 PM org.apache.catalina.core.StandardWrapperValve invoke SEVERE: Servlet.service() for servlet [appServlet] in context with path [/controller] threw exception [Request processing failed; nested exception is org.hibernate.exception.GenericJDBCException: could not execute query] with root cause java.sql.SQLException: Value '0000-00-00' can not be represented as java.sql.Date
If you need my Entity class it's here I'm using Date from java.util, but I go with @Temporal annotation to convert it from SQL to unit Date as I understand:
@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;
}
}
Assuming that the problem with data type conversion is because MVC uses String, but the actual type is Date.
In MySql '0000-00-00' is considered a valid date, but it can't be repesented as java.sql.Date.
You could use a query that returns NULL in case date is '0000-00-00', or the actual value otherwise:
SELECT
CASE WHEN `date`!='0000-00-00' THEN `date` END new_date
FROM
yourtable
or you can add to your datasource connection string this:
zeroDateTimeBehavior=convertToNull
and dates as '0000-00-00' will be automatically converted to NULL.
这篇关于值'0000-00-00'不能表示为java.sql.Date的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
