java.sql.SQLException: Value '0000-00-00' can not be represented as java.sql.Date

3

I have had a problem when returning a value of BD mysql null in '0000-00-00'

format

The error that occurs is:

  

exception

     

javax.servlet.ServletException: java.sql.SQLException: Value '0000-00-00'> can not be represented as java.sql.Date   root cause

     

java.sql.SQLException: Value '0000-00-00' can not be represented as> java.sql.Date

My return code is a .getDate();

Even so, it generates an error ... could you guide me what I did wrong?

//          <!--MOSTRAR LA CONSULTA  DE  BD-->
ResultSet lista = (ResultSet)request.getAttribute( "lista" );
if(lista != null){
    if( lista.first())
    {
        lista.beforeFirst();
            out.println("<form method='post' action=''>"
            + "<div style='overflow-x:auto;'>"
            + "<table border ='2px' align='center' name='tableedit'>"
            + "<caption align='center'> OFICINAS EN BD </caption>"
            + "<tr>"
            + "<th>ID</th>"
            + "<th>APELLIDO</th>"
            + "<th>NOMBRE</th>"        
            + "<th>CORREO</th>" 
            + "<th>CIUDAD</th>"
            + "<th>FECHA NACIMIENTO</th>"
            + "<th>SEXO</th>"
            + "<th>DIRECCION</th>"
            + "<th>TELEFONO</th>"
            + "<th>TIPO</th>"      
            + "<th>EDITAR</th>"
            + "<th>ELIMINAR</th>"
            + "</tr>");

      while( lista.next( ) ){

          //Modificamos el ID de la ciudad por el nombre para la vista
          String ciudad = lista.getString( 3 );                                                                                  
          String nc = "";

          if(!ciudad.equals("")){
          int c = Integer.parseInt(ciudad);   
          ResultSet droplst4 = s.consultarCiudades(c);
              if (droplst4.first()){//Primera coincidencia
                    nc = droplst4.getString( 2 ); 
                  }else{
                  nc = "";   
                  }
          }

          //Modificamos el ID del tipos de usuario por el nombre para la vista
          String tipo = lista.getString( 12 );
          String nt = "";

          if (!tipo.equals("")){
          int t = Integer.parseInt(tipo);
          ResultSet droplst3 = s.tiposUsuarios( 1, t );
              if (droplst3.first()){//Primera coincidencia
                   nt = droplst3.getString( 2 ); 
                   }else{
                       nt = "";    
                       } 
          }

          out.println("<tr>"
                  + "<td >" + lista.getString( 1 ) + "</td>" 
                  + "<td >" + lista.getString( 4 ) + "</td>" 
                  + "<td >" + lista.getString( 5 ) + "</td>" 
                  + "<td >" + lista.getString( 2 ) + "</td>"
                  + "<td >" + nc + "</td>"       
                  + "<td >" + lista.getDate( 6 ) + "</td>" 
                  + "<td >" + lista.getString( 7 ) + "</td>"
                  + "<td >" + lista.getString( 8 ) + "</td>"
                  + "<td >" + lista.getString( 9 ) + "</td>" 
                  + "<td >" + lista.getString( 12 ) + "</td>"         
                  + "<td >" + nt + "</td>"        
                  + "<td><a href='editarOficina.jsp?IdO="+ lista.getInt( 1 ) +" &nomO="+ lista.getString( 2 ) +" &IdZ="+ lista.getString( 3 ) +"&IdC="+ lista.getString( 4 ) +"'>"
                          + "<img src='IMG/editarCampo.png' width='25' height='25'></td>"
                  + "<td><a href='eliminarOficina.jsp?IdO="+ lista.getInt( 1 ) +" &nomO="+ lista.getString( 2 ) +" &IdZ="+ lista.getString( 3 ) +"&IdC="+ lista.getString( 4 ) +"'>"
                          + "<img src='IMG/eliminarCampo.png' width='25' height='25'></td>"         
                  + "</tr>"); 
      } 


      out.println("</table>");  
      out.println("</div>"); 
      out.println("</form>");
    }else
        {
            out.println("<h2> No se encontraron Registros</h2>");
        }
}

The exception is presented here:

 + "<td >" + lista.getDate( 6 ) + "</td>" 

How should I correct it?

As always, THANK YOU !!

    
asked by Andres Felipe Diaz 31.03.2017 в 23:05
source

2 answers

1

MySQL allows you (depending on the version) to save a "zero" value, '0000-00-00' as if it were a "dummy date". And, by default, the driver JDBC of MySQL throws an exception to find a date with these characteristics.

To modify this behavior, change the default value of the property zeroDateTimeBehavior to convertToNull in the connection parameters to MySQL. For example, if you add it in the URL:

jdbc:mysql://{host}:{port}/{dbname}?zeroDateTimeBehavior=convertToNull

In this way the driver , instead of throwing an exception, will return NULL when you hit a date like this.

    
answered by 31.03.2017 / 23:32
source
0

It is necessary to take into account that a Java.util.Date ! = Java.sql.Date . That is, a java.sql.date can be a java.util.date but not the other way around, for this you would have to format the Date you want to send in the query

    
answered by 03.04.2017 в 13:20