When Java JDBC is connected to the MySQL database, cannot convert value '2017-00-00 00:00:00 'from column appears for the datetime type.

Document directory

• Answer

Http://blog.csdn.net/pjchen/archive/2008/04/19/2308245.aspx

 

Use hibernate to connect to the database of mysql5. Since the table uses a datetime time period, the following

Java. SQL. sqlexception: cannot convert value '2017-00-00 00:00:00 'from Column 9 to timestamp.
At com. MySQL. JDBC. resultset. gettimestampfrombytes (resultset. Java: 6864)
At com. MySQL. JDBC. resultset. gettimestampinternal (resultset. Java: 6899)
At com. MySQL. JDBC. resultset. gettimestamp (resultset. Java: 6218)
At com. MySQL. JDBC. resultset. gettimestamp (resultset. Java: 6256)
At org. hibernate. type. timestamptype. Get (timestamptype. Java: 30)
At org. hibernate. type. nullabletype. nullsafeget (nullabletype. Java: 113)
At org. hibernate. type. nullabletype. nullsafeget (nullabletype. Java: 102)

The help document of mysql5 explains datetime as follows:

Datetimes with all-zero components (0000-00-00 ...) -These values can not be represented: for all data of the datetime type consisting of 0, these values cannot be reliably expressed in Java.
Reliably in Java.
Connector/J 3.0.x always converted them to null when being read from a resultset.
When these values are being read from the resultset container, connector/J 3.0.x always converts them to null values.

Connector/J 3.1 throws an exception by default when these values are encountered as this is the most correct behavior according to the JDBC and SQL standards.
According to JDBC and SQL standards, the most correct way to handle these values is to generate exceptions by default.
This behavior can be modified using the zerodatetimebehavior configuration property. The allowable values are:
JDBC allows the following values to be used to set these processing methods for the zerodatetimebehavior attribute,

Exception (the default), which throws an sqlexception with an sqlstate of s1009.
An exception (default) is thrown by an error number of the sqlstate, which is the same as the error number of the sqlstate.
Converttonull, which returns NULL instead of the date.
Set to converttonull. Use null to replace this date type.
Round, which rounds the date to the nearest closest value which is 0001-01-01.
If it is set to round, it is replaced by the closest value of this date (0001-01-01 ).

You can modify your JDBC connection

JDBC: mysql: // localhost/schoolmis? Useunicode = true & amp; characterencoding = utf8 & amp; zerodatetimebehavior = converttonull

//////////////////////////////////////// ///

Http://www.javaeye.com/problems/11339

Java code

1. <Resource Name = "JDBC/sqlds" auth = "Container" type = "javax. SQL. datasource" maxidle = "30"
2. Maxwait = "10000" maxactive = "10" username = "root" Password = "123456"
3. Driverclassname = "com. MySQL. JDBC. Driver"
4. Url = "JDBC: mysql: // localhost: 3306/test? Zerodatetimebehavior = converttonull "/>
6. <Resource Name = "JDBC/sqlds" auth = "Container" type = "javax. SQL. datasource" maxidle = "30"
7. Maxwait = "10000" maxactive = "10" username = "root" Password = "123456"
8. Driverclassname = "com. MySQL. JDBC. Driver"
9. Url = "JDBC: mysql: // localhost: 3306/test? Zerodatetimebehavior = converttonull "/>

<Resource name = "jdbc/sqlds" auth = "Container" type = "javax.sql.DataSource" maxIdle = "30"    maxWait = "10000" maxActive = "10" username = "root" password = "123456"  driverClassName = "com.mysql.jdbc.Driver"  url = "jdbc:mysql://localhost:3306/test?zeroDateTimeBehavior=convertToNull" />  <Resource name = "jdbc/sqlds" auth = "Container" type = "javax.sql.DataSource" maxIdle = "30" maxWait = "10000" maxActive = "10" username = "root" password = "123456"driverClassName = "com.mysql.jdbc.Driver"url = "jdbc:mysql://localhost:3306/test?zeroDateTimeBehavior=convertToNull" />

The configuration in applicationcontext. XML is as follows:

Java code

1. <Bean id = "datasource"Class= "Org. springframework. JNDI. jndiobjectfactorybean">
2. <Property name = "jndiname" value = "Java: COMP/ENV/jdbc/sqlds"> </property>
3. </Bean>
4. <Bean id = "sessionfactory"
5. Class= "Org. springframework. Orm. hibernate3.localsessionfactorybean">
6. <Property name = "datasource">
7. <Ref bean = "datasource"/>
8. </Property>
10. <Bean id = "datasource"Class= "Org. springframework. JNDI. jndiobjectfactorybean">
11. <Property name = "jndiname" value = "Java: COMP/ENV/jdbc/sqlds"> </property>
12. </Bean>
13. <Bean id = "sessionfactory"
14. Class= "Org. springframework. Orm. hibernate3.localsessionfactorybean">
15. <Property name = "datasource">
16. <Ref bean = "datasource"/>
17. </Property>

<bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryBean">           <property name="jndiName" value="java:comp/env/jdbc/sqlds"></property>       </bean>       <bean id="sessionFactory"          class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">           <property name="dataSource">               <ref bean="dataSource" />           </property>  <bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryBean">        <property name="jndiName" value="java:comp/env/jdbc/sqlds"></property>    </bean>    <bean id="sessionFactory"        class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">        <property name="dataSource">            <ref bean="dataSource" />        </property>

The reason is that MySQL will forcibly close connections that have been used for more than 8 hours, but the database connection pool does not know and still holds invalid connections, an error occurs when the connection pool is retrieved and used. How can this problem be solved? Thank you!

Answer lucaslee (intermediate programmer) settings
<Resource...
Validationquery = select 1
Testonborrow = true
...>
In this way, you can verify whether the connection is valid every time you obtain a connection from the connection pool.
Or you can consider using testwhileidle = true

Reference DBCP reference: http://commons.apache.org/dbcp/configuration.html

The comments of the questioner on the answer:
Good. hroger is also very good. Thank you. ////////////////////////////////////////

Post from http://www.blogjava.net/hilor/articles/164814.html

When MySQL is used, the field type in the database is timestamp. The default value is 0000-00-00, and an exception occurs: Java. SQL. sqlexception: Value '2014-00-00' can not be represented as Java. SQL. timestamp

Solution:

Add the zerodatetimebehavior parameter to the jdbc url:

Datasource. url = JDBC: mysql: // localhost: 3306/testdb? Useunicode = true & characterencoding = UTF-8 & zerodatetimebehavior = converttonull & transformedbitisboolean = true

Zerodatetimebehavior = round is used to specify the default value of the datetime field in MySQL for query. By default, an exception is thrown,

For records with a value of 0000-00-00 00:00:00 (default), the following two types of configuration return different results:

Zerodatetimebehavior = round 0001-01-01 00:00:00. 0

Zerodatetimebehavior = converttonull null